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

Question

我在服务器上运行一个程序，用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();
        }

Answer 1

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

Answer 2

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

Answer 3

写excel啊，如果没有格式改变，只是将二维矩阵形式的数据写进excel的话应该不难吧、如果有格式改变的话，那肯定是要设计excel office vba的相关操作了

Answer 4

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

Answer 5

对应楼主的方法不知道你是不是从那个网站上面找到的，这样一条条赋值肯定会慢哈，有个方法是读取值， //创建缓存数据
            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;

Answer 6

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

Answer 7

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

Answer 8

===============================以下是.net导出Excel的通用方法==============================可以适用的C#中的所有数据绑定控件，如repeater,gridView,DataList,还可以用于普通的table显示的数据，即使有分页也不用担心了，该函数可以避免再重新创建另外一个数据绑定控件，函数本身就能解决分页问题。大家可以参考参考！/// <summary>
        /// 得到导出数据
        /// </summary>
        /// <returns></returns>
        protected string GetContent()
        {
            string retVal = "";
            string head = "编号,游戏,公会名,标题,描述,状态,更新时间,创建时间";
            string contentFormat = "";
            int recordCount = 0;
            int pageCount = 0;
            StringBuilder sbContent = new StringBuilder();            DataTable dt = ActivityMgr.GetByCondition(this.gameidx, 0, this.guildName, this.title, this.description,
                 int.Parse(this.Status), this.StartDate, this.EndDate,
                 int.MaxValue,  1,out recordCount, out pageCount);
            sbContent.AppendLine(head);
            if (dt != null && dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    contentFormat = string.Format("{0},{1},{2},{3},{4},{5},{6},{7}",
                                                  dr["IDX"],dr["GameName"], dr["GuildName"], dr["Title"], dr["Description"].ToString().Replace(',', '，'),
                                                  CommonHelper.Get3StatusString(dr["Status"].ToString()), dr["UpdateTime"], dr["RowTime"]);
                    sbContent.AppendLine(contentFormat);//有换行
                }
            }
            retVal = sbContent.ToString();
            return retVal;
        }/// <summary>
        /// 导出Excel 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void BtnExport_Click(object sender, EventArgs e)
        {
            SaveCSV saveFile = new SaveCSV(this.Response);
            string FullPath = string.Format(@"ListActivity_{0}.csv", DateTime.Now.ToShortDateString());            string content = GetContent();
            bool retVal = saveFile.OutFile(FullPath, content);            if (retVal)
            {
                this.Alert(@"数据导出成功!");
            }
            else
            {
                this.Alert(@"数据导出失败!");
            }
        }本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/hongjiaoli/archive/2009/10/23/4716844.aspx

Answer 9

我的目的是在服务器上提前定期运行脚本生成excel文件。然后再用web控制，让相关用户用web下载相应的excel文件。因为数据量大，格式又比较固定，所以想提前生成文件，以免延长用户下载的时间。

Answer 10

经过本人测试，上面的这个方法确实快了很多，甚至比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();
        }

Answer 11

问题解决了，是我代码中有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];
                    }
                }
            }

Answer 12

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