导出Excel 之优化

来源：互联网发布：php可以做软件吗编辑：程序博客网时间：2024/05/22 03:32

前一篇导出Excel的方法效率较低，测试10000条数据的时候会出现卡死的现象，故需要优化。

考虑前一篇方法：

//写入数值

            for (int r = 0; r < dt.Rows.Count; r++)            {                for (int i = 0; i < dt.Columns.Count; i++)                {                    worksheet.Cells[r + 2, i + 1] = "'" + dt.Rows[r][i];                }                rowRead++;                percent = ((float)(100 * rowRead)) / totalCount;                System.Windows.Forms.Application.DoEvents();            }

每次都需要向Excel.WorkSheet 传入数据，执行效率较低

从网上找到一种方法：

可以采用object[][]数据作为中间桥梁，代码如下：

 public void ExportToExcel(string filename, System.Data.DataTable dt)        {            DateTime startime = DateTime.Now;            TimeSpan timespan;            if (dt == null)            {                return;            }            string saveFilename = "";            bool fileSaved = false;            SaveFileDialog saveDialog = new SaveFileDialog();            saveDialog.DefaultExt = "xls";            saveDialog.Filter = "Excel文件|*.xls";            saveDialog.FileName=filename;            saveDialog.ShowDialog();            saveFilename = saveDialog.FileName;            if (saveFilename.IndexOf("") < 0)                return;            Microsoft.Office.Interop.Excel.Application xlApp=new Microsoft.Office.Interop.Excel.Application();            if (xlApp == null)            {                MessageBox.Show("无法创建");                return;            }            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1                      long totalCount = dt.Rows.Count;            int dtcolumn = dt.Columns.Count;                  object[,] dataArray = new object[totalCount+1, dtcolumn];             //写入字段               for (int i = 0; i < dt.Columns.Count; i++)            {                             dataArray[0, i] = dt.Columns[i].ColumnName;                          }            //写入数值                         for (int r = 0; r < totalCount; r++)            {                          for (int i = 0; i < dtcolumn; i++)                {                               dataArray[r+1,i] = dt.Rows[r][i].ToString();                }            }

             //设置成文本格式            worksheet.Range["A1", worksheet.Cells[totalCount, dtcolumn]].NumberFormatLocal = "@";            worksheet.Range["A1", worksheet.Cells[totalCount, dtcolumn]].Value2 = dataArray;            if (saveFilename != "")            {                try                {                    workbook.Saved = true;                    workbook.SaveCopyAs(saveFilename);                    fileSaved = true;                }                catch (Exception ex)                {                    fileSaved = false;                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);                }            }            else            {                fileSaved = false;            }            xlApp.Quit();            GC.Collect();// 行销毁              if (fileSaved && File.Exists(saveFilename))            {                System.Diagnostics.Process.Start(saveFilename);                MessageBox.Show("导出数据已成功！");            }            else            {                MessageBox.Show("导出数据失败！");            }               }

0 0