专栏首页跟着阿笨一起玩NETC#如何快速高效地导出大量数据?

C#如何快速高效地导出大量数据?

本文转载:http://www.cnblogs.com/herbert/archive/2010/07/28/1787095.html

在C#里面如果对应的把datagridview里面的每个cell的数据一个一个地对应的写入到excel,你会发现非常的慢,这个时候,你可以先把datagrid里面的数据放入到一个二维的string数组里面, 然后利用range对象的value赋值,那样会快很多。代码如下

public static void DataGridViewToExcel(DataGridView dgv)
        {


            #region   Validate the path and file properties
            
            SaveFileDialog dlg = new SaveFileDialog();            
            dlg.DefaultExt = "xlsx ";
            dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx ";
            dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            
            // Open the save file dialog
            if (dlg.ShowDialog() == DialogResult.Cancel) 
                return;

            // Get the filename
            string fileNameString = dlg.FileName;            
            if (fileNameString.Trim() == " ")
                return; 
            
            // Define the rows count and columns count
            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;
            
            if (rowscount <= 0)
            {
                MessageBox.Show("No data to save ", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (colscount <= 0)
            {
                MessageBox.Show("No data to save ", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            
            if (rowscount > 65536)
            {
                MessageBox.Show("Too much records(The number of the records exceeds 65536),CANNOT SAVE! ", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            
            if (colscount > 255)
            {
                MessageBox.Show("Too much columns, CANNOT SAVE! ", "Info ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            // Check if the file exisits
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "Cannot delete the file!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            #endregion

            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 = dgv.RowCount;
                int columnCount = dgv.ColumnCount;

                string[,] datas = new string[rowCount + 1, columnCount];




                for (int i = 0; i < columnCount; i++)
                {
                    if (dgv.Columns[i].Visible == true)
                    {                       
                        datas[0, i] = dgv.Columns[i].HeaderText.Trim();                        
                    }
                }
                
                for (int row = 0; row < rowCount; row++)
                {
                    for (int col = 0; col < columnCount; col++)
                    {
                        if (dgv.Columns[col].Visible == true)
                        {
                            try
                            {
                                datas[row + 1, col] = dgv.Rows[row].Cells[col].Value.ToString().Trim();                                
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show("An exception occured:" + ex.Message, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }

                        }
                    }
                }

                int exportRowCount = rowCount + 1;
                
                Range range = objsheet.get_Range("A1", "C" + exportRowCount);
                range.Value2 = datas;

                objsheet.Columns.EntireColumn.AutoFit(); // Automatically change the column width

                objWorkbook.SaveAs(fileNameString, missing, missing, missing, missing,
                        missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, missing, missing, missing,
                        missing, missing);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "Warning ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            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;
            }
            MessageBox.Show(fileNameString + "\n\n Export finished! ", "Info ", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

前面有一段代码是check 数据的,其实可以根据自己情况再制定。主要是导入的那几句话。这里要注意一个问题了,就是在visual studio里面

Worksheet.get_Range Method 

Range get_Range(
    Object Cell1,
    Object Cell2
)

<!-- .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } -->

这个要注意了,在visual studio 2010以前都可以这样用,但是visual studio 2010里面是下面的形式

Range get_Range(object Cell1, object Cell2 = Type.Missing);

<!-- .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } -->

所以这里选定一个范围的cell需要这样写

Range range = objsheet.get_Range("A1", "C" + exportRowCount);

<!-- .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } -->

参考文章地址:这里的这个示例给出的是一个可以导出大于60000行数据的代码,就是可以分成多个worksheet

http://www.cnblogs.com/Ihaveadream/archive/2009/01/04/1368525.html

          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][i];

                       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

<!-- .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } -->

上导出的代码在网上铺天盖地了已经,它仅适合导出那些最多几百几千的数据量,当使用上述代码导出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继续存储数据,关于如何使用,上边我修改过的代码中已经有详细的使用,请大家自己去看,这里不再重复。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • winform用户输入查询与拼音首字母的结合,提高用户的操作体验

    在Winform界面中,同样也可以做到智能提示,由于Winform中的响应速度比较快,我们可以根据输入的首字母或者部分中文快速更新列表内容即可,如下图所示。

    跟着阿笨一起玩NET
  • winform系统自动登录实现

    转载:http://www.cnblogs.com/wuhuacong/archive/2012/08/21/2648339.html

    跟着阿笨一起玩NET
  • .Net Windows服务安装完成后自动启动

    考虑到部署方便,我们一般都会将C#写的Windows服务制作成安装包。在服务安装完成以后,第一次还需要手动启动服务,这样非常不方便。查阅了网上的一些资料,发现有...

    跟着阿笨一起玩NET
  • 使用jQuery的animate方法制作滑动菜单

    周末看Ziv小威的博客《制作滑动条菜单,如何延时处理滑动效果,避免动画卡顿》,参见地址:http://www.cnblogs.com/zivxiaowei/p/...

    八哥
  • 颜色滤镜

    我们手机上有很多照片处理软件,图片滤镜是里面不可或缺的一部分,我们可以先尝试一些很简单的滤镜的算法,管中窥豹地去认识一下色彩的处理

    异名
  • 利用代码实现自定义圆角+阴影按钮 android-flat-button

    利用代码实现自定义圆角+阴影按钮 android-flat-button android-flat-button效果图: ? 说明:android-flat-b...

    非著名程序员
  • 计算质数和回文数的小程序

    .csharpcode, .csharpcode pre { font-size: small; color: black; font-family:...

    大江小浪
  • ggplot2点图调整顺序

    但是这里有个问题是,本来应该还有一组点,因为这一组点最少,画在最下边被挡住了。我想让这一组点显示出来。一开始我打算修改分组的levels的顺序,将这一组换一下位...

    生信编程日常
  • 简单可视化-送你一双发现美的眼睛

    用PyMOL展示配体和受体相互作用的原子和氢键 为了简化展示过程,我们设计了一个pml脚本 (脚本内有很详细的解释),只需要修改脚本里面受体和配体的名字,然后在...

    生信宝典
  • 分子对接简明教程 (二)

    用PyMOL展示配体和受体相互作用的原子和氢键 为了简化展示过程,我们设计了一个pml脚本 (脚本内有很详细的解释),只需要修改脚本里面受体和配体的名字,然后在...

    生信宝典

扫码关注云+社区

领取腾讯云代金券