大数据导出到excel

https://www.oschina.net/code/snippet_2257513_58318

三层架构中在 创建的一个通用的数据导出,可以导出大数据,100M数据导出亲测不超3s

标签： <无>

代码片段(1)[全屏查看所有代码]

1. [代码]这个添加了 进度条,可以去掉     跳至 [1] [全屏预览]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Windows.Forms; using Microsoft.Office.Interop.Excel; using System.IO; using System.Web; namespace GetDatascoreTable {    public  class perferctExcel      {                       public static void DataGridViewToExcel(System.Data.DataTable dt, string fileName,ProgressBar po)        {          long totalcount = dt.Rows.Count;         //将进度条控件显示          po.Maximum = ( int )totalcount; //设置进度条控件的最大值          long rowRead = 0;          float percent = 0;          string saveFileName = "" ;          SaveFileDialog saveDialog = new SaveFileDialog();          saveDialog.DefaultExt = "xls" ;          saveDialog.Filter = "Xls文件|*.xls" ;          saveDialog.FileName = fileName;          saveDialog.ShowDialog();          saveFileName = saveDialog.FileName;          if (saveFileName.IndexOf( ":" ) < 0) return ;          string path = "" ;          po.Visible = true ;          Microsoft.Office.Interop.Excel.Application objExcel = null ;          Microsoft.Office.Interop.Excel.Workbook objWorkbook = null ;          Microsoft.Office.Interop.Excel.Worksheet objsheet = null ;          object missing = System.Reflection.Missing.Value;          try          {              objExcel = new Microsoft.Office.Interop.Excel.Application();              objWorkbook = objExcel.Workbooks.Add(missing);              objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;              objExcel.Visible = false ;              int rowCount = dt.Rows.Count;              int columnCount = dt.Columns.Count;              string [,] datas = new string [rowCount + 1, columnCount];              for ( int i = 0; i < columnCount; i++)              {                  datas[0, i] = dt.Columns[i].ColumnName.Trim();              }              for ( int row = 0; row < rowCount; row++)              {                  for ( int col = 0; col < columnCount; col++)                  {                         try                      {                          datas[row + 1, col] = dt.Rows[row][col].ToString().Trim();                      }                      catch (Exception ex)                      {                          throw new Exception(ex.Message.ToString());                      }                     //进度条控件需要用百分号表示进度时,使用该行代码                       // po.Value = (int)(rowRead);//设置进度条控件的当前值                                        }                  rowRead++;                  percent = (( float )(100 * rowRead)) / totalcount;                  po.Value = ( int )(rowRead);              }              int exportRowCount = rowCount + 1;                int count = dt.Columns.Count;              Range range = objsheet.Range[objsheet.Cells[1, 1], objsheet.Cells[rowCount + 1, columnCount]];                            range.Value = datas;              objsheet.Columns.EntireColumn.AutoFit();                                          path = saveFileName;                           objWorkbook.SaveAs(path, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);              GC.Collect();              MessageBox.Show( "文件导出完成" );              po.Visible = false ;          }          catch (Exception error)          {              throw new Exception(error.Message.ToString());            }          finally          {                 if (objWorkbook != null ) objWorkbook.Close(missing, missing, missing);              if (objExcel.Workbooks != null ) objExcel.Workbooks.Close();              if (objExcel != null ) objExcel.Quit();              objsheet = null ;              objWorkbook = null ;              objExcel = null ;          }         }                } }