如何快速将数据导出成excel文件格式

闲思暇想 2010-09-25 10:44:14

我在服务器上运行一个程序，用C#写的，定期将oracle数据库中的数据导出成excel文件，但是发现数据导出很慢。大约7秒钟才100条数据。而经常需要导出的数据总量达到50000条，如此算来，导出文件需要约1个小时。

而在同一台服务器上运行PL/SQL工具，查询同样的数据，并另存成excel文件，整个操作一般5分钟左右，不超过10分钟。
请问有什么办法加快数据导出的速度。

附上C#程序中导出数据为excel文件部分的代码



        private void ReportOut(string filename,string strsql)

        {

            //第一步：导出数据表

            oracomm.CommandText = strsql;

            orareader = oracomm.ExecuteReader();



            //第二步：创建excel文件

            GC.Collect();



            Microsoft.Office.Interop.Excel.Application excel;

            

            Workbook xBk;

            Worksheet xSt = null;

            excel = new ApplicationClass();

            xBk = excel.Workbooks.Add(true);



            //第三步：填充excel文件



            //定义循环中要使用的变量

            int sheetIndex = 1;

            int rowIndex = 1;

            int colIndex = 1;



            while (orareader.Read())

            {

                //首行时，添加新的工作表，添加标题栏

                if (rowIndex == 1)

                {

                    //创建一个Sheet 

                    if (null == xSt)

                    {

                        //第一个工作表创建位置

                        xSt = (Worksheet)xBk.Worksheets[1];

                    }

                    else

                    {

                        //新的工作表的创建位置

                        xSt = (Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);

                    }



                    //设置工作表名

                    xSt.Name = "数据明细" + sheetIndex.ToString();



                    //填充标题栏

                    xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, orareader.FieldCount + 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐 

                    xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, orareader.FieldCount + 1]).Font.Bold = true;//设置标题为粗体 



                    for (colIndex = 1; colIndex <= orareader.FieldCount; colIndex++)

                    {

                        xSt.Cells[1, colIndex + 1] = orareader.GetName(colIndex - 1);

                    }

                    rowIndex++;

                }



                //填充数据



                xSt.Cells[rowIndex, 1] = rowIndex - 1;



                for (colIndex = 1; colIndex <= orareader.FieldCount; colIndex++)

                {

                    if (orareader.GetFieldType(colIndex - 1) == System.Type.GetType("System.String"))

                    {

                        xSt.Cells[rowIndex, colIndex + 1] = "'" + orareader[colIndex - 1];

                    }

                    else

                    {

                        xSt.Cells[rowIndex, colIndex + 1] = orareader[colIndex - 1];

                    }

                }



                if ((rowIndex - 1) % 100 == 0)

                {

                    txtInfo.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff ") + "已导入数据" + (rowIndex - 1).ToString() + "条！\r\n");

                    xBk.Save();

                }



                //如果超过限定行数，添加新表

                if (rowIndex >= 60000)

                {

                    //使用最佳宽度 

                    Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex + 1]);

                    allDataWithTitleRange.Select();

                    allDataWithTitleRange.Columns.AutoFit();

                    allDataWithTitleRange.Borders.LineStyle = 1;//将导出Excel加上边框 

                    sheetIndex++;

                    rowIndex = 1;

                }

                else

                {

                    rowIndex++;

                }



                

            }



           



            //第四步：保存excel文件

            xBk.SaveCopyAs("D:\\" + filename + ".xls");

            xBk.Close(false, null, null);

            excel.Quit();



            orareader.Dispose();

            oracomm.Dispose();

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);



            xBk = null;

            excel = null;

            xSt = null;

            GC.Collect();

        }

...全文

806 22 打赏收藏转发到动态举报

写回复

用AI写文章

22 条回复

切换为时间正序

请发表友善的回复…

发表回复

Study_Knowledge 2012-07-19

打赏
举报

谢谢。学习了

Jauntily5 2011-11-08

打赏
举报

java写后台，Winfrom写前台，然后我们前台需要把数据导出成带格式的Excel，百十条数据都需要一分多钟，肿么能解决一下啊...

yangquanlaohou 2010-09-25

打赏
举报

学习一下，好好

wuyq11 2010-09-25

打赏
举报

把一个table导出成CSV文件，然后把CSV转化成excel
spool csvfilepath;
select * from ..... ; --要导出的结果集
spool off;
直接复赋值数组到EXCEL

xiangma04 2010-09-25

打赏
举报

不知楼主的是 C/S 还是 B/S ，如果是 web 可以先把数据邦在 DataGrid ，然后导出就快了

kj289907795 2010-09-25

打赏
举报

jxj0926 2010-09-25

打赏
举报

对应楼主的方法不知道你是不是从那个网站上面找到的，这样一条条赋值肯定会慢哈，有个方法是读取值，

//创建缓存数据
object[,] ObjData = new object[RowCount + 1, ColCount];

然后将值全部写入这个2维数组

定义 Microsoft.Office.Interop.Excel.Range Range;
Range = XLsheek.get_Range(Xlapp.Cells[1, 1], Xlapp.Cells[RowCount + 1, ColCount]);
Range.Value2 = ObjData;

yunfei5555 2010-09-25

打赏
举报

直接导入啊

kj289907795 2010-09-25

打赏
举报

[Quote=引用 3 楼 gongsun 的回复:]
用oledb去写 excel吧。
[/Quote]
人家是导入EXCEl

sweetjian 2010-09-25

打赏
举报



string fileName,filePath,strLine;

            FileStream objFileStream;

            StreamWriter objStreamWriter;

            fileName = "NewFile.xls";

            filePath =  "c:\\"+fileName;



            objFileStream = new FileStream(filePath,FileMode.Create,FileAccess.ReadWrite);

            objStreamWriter = new StreamWriter(objFileStream,System.Text.Encoding.Default);

            objStreamWriter.AutoFlush=true;



            strLine = "第一列\t第二列\t第三列";

            objStreamWriter.WriteLine(strLine);



            strLine = "";

            DataSet ds = new DataSet();

            ds=....(查询出来的数据)

            for(int j=0;j<=ds.Tables[0].Rows.Count-1;j++)

            {

                strLine += "\r";

                for (int i = 0; i < ds.Tables[0].Columns.Count-1; i++) 

                {

                    strLine = strLine + ds.Tables[0].Rows[j][i].ToString()+"\t";

                }

                strLine += ds.Tables[0].Rows[j][ds.Tables[0].Columns.Count-1].ToString();

                strLine = strLine.Trim();

                objStreamWriter.WriteLine(strLine);

                strLine="";

            }

            objStreamWriter.Flush();

            objFileStream.Flush();

            objStreamWriter.Close();

            objFileStream.Close();

参考 http://topic.csdn.net/u/20091104/14/f7c72172-2bd8-4a1a-b5d1-c0d80878bf27.html

josxhn 2010-09-25

打赏
举报

写excel啊，如果没有格式改变，只是将二维矩阵形式的数据写进excel的话应该不难吧、

如果有格式改变的话，那肯定是要设计excel office vba的相关操作了

xzjxylophone 2010-09-25

打赏
举报

你是一边从数据库读写数据一边写入文件。
你换个把数据全部读出来，然后再写入到excel中

孟子E章 2010-09-25

打赏
举报

导出出xml格式的，数据多的可以分工作表的
参见
http://dotnet.aspx.cc/file/Export-Gridview-To-Excel-With-Multi-Sheet.aspx

gongsun 2010-09-25

打赏
举报

用oledb去写 excel吧。

xzjxylophone 2010-09-25

打赏
举报

先把数据全部给提取出来，然后再写入到文件中，这个应该会很快的吧。。。

liuqilin1987 2010-09-25

打赏
举报

建议在数据处理时，尽量使用存数过程，这样可以提高一下数据库处理的效率。别的方法可能就是通过视图，这样也能提高效率。

闲思暇想 2010-09-25

打赏
举报

问题解决了，是我代码中有bug。



  //填充数据区

            for (j = 1; j <= dtOut.Columns.Count; j++)

            {

                if (dtOut.Columns[j - 1].DataType == System.Type.GetType("System.String"))

                {

                    for (i = 1; i <= dtOut.Rows.Count; i++)

                    {

                        ObjData[i, j] = "'" + dtOut.Rows[i - 1][j - 1].ToString();

                    }

                }

                else if (dtOut.Columns[j - 1].DataType == System.Type.GetType("System.DateTime"))

                {

                    for (i = 1; i <= dtOut.Rows.Count; i++)

                    {

                        ObjData[i, j] = "'" + ((DateTime)dtOut.Rows[i - 1][j - 1]).ToString("yyyy-MM-dd HH:mm:ss");

                    }

                }

                else //这里忘记加上一个else了

                {

                    for (i = 1; i <= dtOut.Rows.Count; i++)

                    {

                        ObjData[i, j] = dtOut.Rows[i - 1][j - 1];

                    }

                }

            }

闲思暇想 2010-09-25

打赏
举报

[Quote=引用 10 楼 jxj0926 的回复:]
对应楼主的方法不知道你是不是从那个网站上面找到的，这样一条条赋值肯定会慢哈，有个方法是读取值，

//创建缓存数据
object[,] ObjData = new object[RowCount + 1, ColCount];

然后将值全部写入这个2维数组

定义 Microsoft.Office.Interop.Excel.Range Range;
Range = ……
[/Quote]

经过本人测试，上面的这个方法确实快了很多，甚至比PL/SQL的速度还快。
但是碰上了新的问题：有些字段在数据库中是存成字符串的数字，比如"000123"，
在用上面的方法后，默认都转化成了数字，请问如何保持字符串格式。

另附上新的代码以供高手分析



private void ReportOutC(string filename, string strsql)

        {

            //第一步：导出数据表

            oracomm.CommandText = strsql;

            orareader = oracomm.ExecuteReader();

            System.Data.DataTable dtOut = new System.Data.DataTable();

            dtOut.Load(orareader);

            orareader.Dispose();

            txtInfo.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff ") + "数据已经读取到DataTable中！\r\n");

            txtInfo.ScrollToCaret();



            object[,] ObjData = new object[dtOut.Rows.Count + 1, dtOut.Columns.Count + 1];



            int i,j;



            ObjData[0, 0] = "";



            //填充标题行

            for (j = 1; j <= dtOut.Columns.Count; j++)

            {

                ObjData[0, j] = dtOut.Columns[j - 1].ColumnName;

            }



            //填充行号列

            for (i = 1; i <= dtOut.Rows.Count; i++)

            {

                ObjData[i, 0] = i;

            }

            

            //填充数据区

            for (j = 1; j <= dtOut.Columns.Count; j++)

            {

                if (dtOut.Columns[j - 1].DataType == System.Type.GetType("System.String"))

                {

                    for (i = 1; i <= dtOut.Rows.Count; i++)

                    {

                        ObjData[i, j] = "'" + dtOut.Rows[i - 1][j - 1].ToString();

                    }

                }

                else if (dtOut.Columns[j - 1].DataType == System.Type.GetType("System.DateTime"))

                {

                    for (i = 1; i <= dtOut.Rows.Count; i++)

                    {

                        ObjData[i, j] = "'" + ((DateTime)dtOut.Rows[i - 1][j - 1]).ToString("yyyy-MM-dd HH:mm:ss");

                    }

                }

                {

                    for (i = 1; i <= dtOut.Rows.Count; i++)

                    {

                        ObjData[i, j] = dtOut.Rows[i - 1][j - 1];

                    }

                }

            }



            txtInfo.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff ") + "数据已经填充到数组中！\r\n");

            txtInfo.ScrollToCaret();



            //第二步：创建excel文件

            GC.Collect();



            Microsoft.Office.Interop.Excel.Application excel;



            Workbook xBk;

            Worksheet xSt = null;

            excel = new ApplicationClass();

            xBk = excel.Workbooks.Add(true);



            //第三步：填充excel文件



            //定义循环中要使用的变量



            //填充标题栏

            xSt = (Worksheet)xBk.Worksheets[1];

            xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, dtOut.Columns.Count + 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐 

            xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, dtOut.Columns.Count + 1]).Font.Bold = true;//设置标题为粗体 



            xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[dtOut.Rows.Count + 1, dtOut.Columns.Count + 1]).Value2 = ObjData;





            //第四步：保存excel文件

            xBk.SaveCopyAs("D:\\" + filename + ".xls");

            xBk.Close(false, null, null);

            excel.Quit();



            orareader.Dispose();

            oracomm.Dispose();

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);



            xBk = null;

            excel = null;

            xSt = null;

            GC.Collect();

        }