C#如何快速高效地匯出10萬以上的大量資料

            string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel檔案|*.xls";
            saveDialog.FileName = "Sheet1";
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被點了取消

            ExcelApp xlApp = new ExcelApp();

            if (xlApp == null)
            {
                strMessage = "無法建立Excel對象，可能您的電腦上未安裝Excel軟體。";
                MessageBox.Show("無法建立Excel對象，可能您的電腦上未安裝Excel軟體。");
                return;
            }

            Workbooks workbooks = xlApp.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1
            //寫入欄位
            for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++)
            {
               worksheet.Cells[1, i + 1] = gridview.GetVisibleColumn(i).Caption;
            }

            //寫入數值
                        int r;
            for (r = 0; r < table.Rows.Count; r++ )
           {
               for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++)
               {
                   if (gridview.GetVisibleColumn(i).ColumnType == typeof(string) || gridview.GetVisibleColumn(i).ColumnType == typeof(Decimal) || gridview.GetVisibleColumn(i).ColumnType == typeof(DateTime))
                  {
                       worksheet.Cells[r + 2, i + 1] = gridview.GetRowCellDisplayText(r, gridview.GetVisibleColumn(i));//dt.Rows[r];
                       object obj = table.Rows[r][gridview.GetVisibleColumn(i).FieldName];
                       worksheet.Cells[r + 2, i + 1] = obj == null ? "": "'"+obj.ToString();
                  }
                }
                System.Windows.Forms.Application.DoEvents();
            }
if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    strMessage = "匯出檔案時出錯,檔案可能正被開啟";
                    fileSaved = false;
                    MessageBox.Show("匯出檔案時出錯,檔案可能正被開啟！"n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            GC.Collect();//強行銷毀
if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //開啟EXCEL

以上匯出的代碼在網上鋪天蓋地了已經，它僅適合匯出那些最多幾百幾千的資料量，當使用上述代碼匯出10萬甚至20多萬的海量資料時，至少幾個小時甚至匯出失敗。。。。
經過幾天的研究，本人發現上述匯出的核心代碼存在問題，導致速度緩慢，下邊看看我修改後的程式：

string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel檔案|*.xls";
            saveDialog.FileName = "Sheet1";
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被點了取消

            ExcelApp xlApp = new ExcelApp();

            if (xlApp == null)
            {
                strMessage = "無法建立Excel對象，可能您的電腦上未安裝Excel軟體。";
                MessageBox.Show("無法建立Excel對象，可能您的電腦上未安裝Excel軟體。");
                return;
            }

            Workbooks workbooks = xlApp.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1
            
            long rows = table.Rows.Count;
            /*下邊注釋的兩行代碼當資料行數超過行時，出現異常：異常來自HRESULT:0x800A03EC。因為：Excel 2003每個sheet只支援最大行資料
            //Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count+2, gridview.Columns.View.VisibleColumns.Count+1]);
            //fchR.Value2 = datas;*/
            if (rows > 65535)
            {
                long pageRows = 60000;//定義每頁顯示的行數,行數必須小於
                int scount = (int)(rows / pageRows);
                if (scount * pageRows < table.Rows.Count)//當總行數不被pageRows整除時，經過四捨五入可能頁數不準
                {
                    scount = scount + 1;
                }
                for (int sc = 1; sc <= scount; sc++)
                {
                    if (sc > 1)
                    {
                        object missing = System.Reflection.Missing.Value;
                        worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
                       missing, missing, missing, missing);//添加一個sheet
                    }
                    else
                    {
                        worksheet = (Worksheet)workbook.Worksheets[sc];//取得sheet1
                    }
                    string[,] datas = new string[pageRows + 1, gridview.Columns.View.VisibleColumns.Count + 1];

                    for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++) //寫入欄位
                    {
                        datas[0, i] = gridview.GetVisibleColumn(i).Caption;
                    }
                    Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, gridview.Columns.View.VisibleColumns.Count]);
                    range.Interior.ColorIndex = 15;//15代表灰色
                    range.Font.Bold = true;
                    range.Font.Size = 9;

                    int init = int.Parse(((sc - 1) * pageRows).ToString());
                    int r = 0;
                    int index = 0;
                    int result;
                    if (pageRows * sc >= table.Rows.Count)
                    {
                        result = table.Rows.Count;
                    }
                    else
                    {
                        result = int.Parse((pageRows * sc).ToString());
                    }

                    for (r = init; r < result; r++)
                    {
                        index = index + 1;
                        for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++)
                        {
                            if (gridview.GetVisibleColumn(i).ColumnType == typeof(string) || gridview.GetVisibleColumn(i).ColumnType == typeof(Decimal) || gridview.GetVisibleColumn(i).ColumnType == typeof(DateTime))
                            {
                                object obj = table.Rows[r][gridview.GetVisibleColumn(i).FieldName];
                                datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加單引號是為了防止自動轉化格式
                            }
                        }
                        System.Windows.Forms.Application.DoEvents();
                    }

                    Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 2, gridview.Columns.View.VisibleColumns.Count + 1]);
                    fchR.Value2 = datas;

                    worksheet.Columns.EntireColumn.AutoFit();//列寬自適應。

                    range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, gridview.Columns.View.VisibleColumns.Count]);
                    //15代表灰色
                    range.Font.Size = 9;
                    range.RowHeight = 14.25;
                    range.Borders.LineStyle = 1;
                    range.HorizontalAlignment = 1;
                }
            }
            else
            {
                string[,] datas = new string[table.Rows.Count + 2, gridview.Columns.View.VisibleColumns.Count + 1];
                for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++) //寫入欄位
                {
                    datas[0, i] = gridview.GetVisibleColumn(i).Caption;
                }
                Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, gridview.Columns.View.VisibleColumns.Count]);
                range.Interior.ColorIndex = 15;//15代表灰色
                range.Font.Bold = true;
                range.Font.Size = 9;

                int r = 0;
                for (r = 0; r < table.Rows.Count; r++)
                {
                    for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++)
                    {
                        if (gridview.GetVisibleColumn(i).ColumnType == typeof(string) || gridview.GetVisibleColumn(i).ColumnType == typeof(Decimal) || gridview.GetVisibleColumn(i).ColumnType == typeof(DateTime))
                        {
                            object obj = table.Rows[r][gridview.GetVisibleColumn(i).FieldName];
                            datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加單引號是為了防止自動轉化格式
                        }
                    }
                    System.Windows.Forms.Application.DoEvents();
                }
                Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 2, gridview.Columns.View.VisibleColumns.Count + 1]);
                fchR.Value2 = datas;

                worksheet.Columns.EntireColumn.AutoFit();//列寬自適應。

                range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, gridview.Columns.View.VisibleColumns.Count]);
                //15代表灰色
                range.Font.Size = 9;
                range.RowHeight = 14.25;
                range.Borders.LineStyle = 1;
                range.HorizontalAlignment = 1;
            }

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    strMessage = "匯出檔案時出錯,檔案可能正被開啟";
                    fileSaved = false;
                    MessageBox.Show("匯出檔案時出錯,檔案可能正被開啟！"n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            GC.Collect();//強行銷毀

            if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //開啟EXCEL

著重看上邊藍色代碼的部分，經過前後兩種匯出方式的對比，您會發現，匯出的思想改變了：
原來的程式將資料一個表格一個表格地寫入到EXCEL中；修改後的程式先將資料存入二維數組中，然後再將數組值賦予EXCEL應用程式物件的VALUE屬性
正是匯出思想的改變，使匯出速度提高不少，請參考我的匯出時間資料對照：
2萬條：30分鐘-->2分鐘左右；3萬條：大於60分鐘-->3分鐘;20萬7000多條：匯出失敗--->15分鐘左右
可以明顯看出，效率提高了不少吧。
還有一個要提醒大家，EXCEL中SHEET最多儲存65535條資料，如果資料過多就需要增加sheet繼續儲存資料，關於如何使用，上邊我修改過的代碼中已經有詳細的使用，請大家自己去看，這裡不再重複。