导出Excel 之优化

前一篇导出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("导出数据失败！");
            }
       
        }