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继续存储数据，关于如何使用，上边我修改过的代码中已经有详细的使用，请大家自己去看，这里不再重复。

转载于:https://www.cnblogs.com/freedom831215/archive/2009/10/03/1577614.html