将Datatable导出到Excel三种方案以及分析(无NPOI)

以下是未引用NPOI情况下,datatable导出excel的三种方案,并进行简单的分析。

 说明:excelTable 要导出的excel

string filePath, 保存文件的路径

string filename 保存的文件名

方案一,经典导出:

            Microsoft.Office.Interop.Excel.Application app =
                new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false;
                Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(true);
                Microsoft.Office.Interop.Excel.Worksheet wSheet = wBook.Sheets[1];
                wSheet.Name = filename;
                if (excelTable.Rows.Count > 0)
                {
                    int row = excelTable.Rows.Count;
                    int col = excelTable.Columns.Count;
                    for (int i = 0; i < row; i++)
                    {
                        for (int j = 0; j < col; j++)
                        {
                            string str = "-";
                            if (excelTable.Rows[i][j].Equals(float.NaN) == false)
                            {
                                str = excelTable.Rows[i][j].ToString();

                            }
                            wSheet.Cells[i + 2, j + 1] = str;
                        }
                    }
                }

                int size = excelTable.Columns.Count;
                for (int i = 0; i < size; i++)
                {
                    wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
                }
                //设置禁止弹出保存和覆盖的询问提示框   
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                //保存工作簿 
                //wBook.Save();
                wSheet.SaveAs(filePath);
                wBook.Close();
                //保存excel文件   
                //app.Save(filePath);
                //app.SaveWorkspace(filePath);
                app.Quit();
                GC.Collect();
                return true;
            }
            catch (Exception err)//这里还有些问题,比如 对方安装的是WPS 不会提示中文错误,没有安装office 也不会弹出该错误
            {
                MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);

                //强制结束excel进程
                IntPtr t = new IntPtr(app.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(t, out k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();


                return false;
            }
            finally
            {
            }

这个方案是最经典的导出。目前未发现有哪个版本excel(包括wps)不支持该导出方案。

缺点:显而易见,因为是“填空”的方法,所以当数据量比较大的时候非常耗时。(我尝试是5000行,30列。导出时间超过5分钟)

方案二,流导出:

            bool result = false;
            try
            {
                // 实例化流对象,以特定的编码向流中写入字符。  
                StreamWriter sw = new StreamWriter(filePath, false, Encoding.GetEncoding("gb2312"));
                StringBuilder sb = new StringBuilder();
                for (int k = 0; k < excelTable.Columns.Count; k++)
                {
                    // 添加列名称  
                    sb.Append(excelTable.Columns[k].ColumnName.ToString() + "\t");
                }
                sb.Append(Environment.NewLine);
                // 添加行数据  
                for (int i = 0; i < excelTable.Rows.Count; i++)
                {
                    DataRow row = excelTable.Rows[i];
                    for (int j = 0; j < excelTable.Columns.Count; j++)
                    {
                        // 根据列数追加行数据  
                        sb.Append(row[j].ToString() + "\t");
                    }
                    sb.Append(Environment.NewLine);
                }
                sw.Write(sb.ToString());
                sw.Flush();
                sw.Close();
                sw.Dispose();

                // 导出成功后打开  
                //System.Diagnostics.Process.Start(path);  
                result = true;
            }
            catch (Exception err)
            {
                result = false;
                MessageBox.Show("请保存或关闭可能已打开的Excel文件", "系统信息");
            }
            finally
            {
                excelTable.Dispose();
            }
            return result;

这个方法是我目前所发现的最快的方法,将datatable写入流中再输出到excel中,同方案一,5000行30列的数据导出只需要1秒左右!

缺点:就像我在catch中所写的,需要关闭已经打开的excel,否则会发生异常。那么当用户正在使用excel不能随意关闭,而且要导出的话此方案就有很大限制了。

还有一点,此方案只支持excel2003,也就是.xls 格式,如果要导出.xlsx格式会发生文件损坏的。这是因为excel2003和excel2007以上的workbook是不一样的(2003是HSSFWORKBOOK,2007以上是XSSFWORKBOOK,不知道写没写错哈)。


方案三,我推荐的方案:

 Microsoft.Office.Interop.Excel.Application app =
                new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false;
                Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(true);
                Microsoft.Office.Interop.Excel.Worksheet wSheet = wBook.Sheets[1];
                wSheet.Name = filename;

                object[,] objData = new object[excelTable.Rows.Count + 1, excelTable.Columns.Count];
                //首先将数据写入到一个二维数组中  
                for (int i = 0; i < excelTable.Columns.Count; i++)
                {
                    objData[0, i] = excelTable.Columns[i].ColumnName;
                }
                if (excelTable.Rows.Count > 0)
                {
                    for (int i = 0; i < excelTable.Rows.Count; i++)
                    {
                        for (int j = 0; j < excelTable.Columns.Count; j++)
                        {
                            if (excelTable.Rows[i][j].Equals(float.NaN))//查询过来的float.NaN
                                objData[i + 1, j] = "-";
                            //else if (String.IsNullOrEmpty(excelTable.Rows[i][j].ToString()) && excelTable.Rows[i][j].Equals(DBNull.Value))//有dbnull的数据,需要屏蔽掉——在数据源处理了
                            //    objData[i + 1, j] = excelTable.Rows[i][j];
                            else
                                objData[i + 1, j] = excelTable.Rows[i][j];
                        }
                    }
                }

                string startCol = "A";//这里关键,计算要替换的区域
                int iCnt = ((excelTable.Columns.Count - 1) / 26);//当列数是26时 不-1 会出现问题,自己试试就明白了
                string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
                string endCol = endColSignal + ((char)('A' + excelTable.Columns.Count - iCnt * 26 - 1)).ToString();
                Microsoft.Office.Interop.Excel.Range range = wSheet.get_Range(startCol + "1", endCol + (excelTable.Rows.Count + 1).ToString());

                range.Value = objData; //给Exccel中的Range整体赋值  
                range.EntireColumn.AutoFit(); //设定Excel列宽度自适应  
                wSheet.get_Range(startCol + "1", endCol + "1").Font.Bold = 1;//Excel文件列名 字体设定为Bold  


                //设置禁止弹出保存和覆盖的询问提示框   
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                //保存工作簿 
                //wBook.Save();
                wSheet.SaveAs(filePath);
                wBook.Close();
                //保存excel文件   
                //app.Save(filePath);
                //app.SaveWorkspace(filePath);
                app.Quit();
                GC.Collect();
                return true;
            }
            catch (Exception err)//这里还有些问题,比如 对方安装的是WPS 不会提示中文错误,没有安装office 也不会弹出该错误
            {
                MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);

                //强制结束excel进程
                IntPtr t = new IntPtr(app.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(t, out k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();


                return false;
            }
            finally
            {
            }

这个方案是先将datatable中的数据放到一个二维数组object[][]中,然后替换excel中的对应区域。所以关于区域定位的地方是关键,多看看改一改就明白了。(之前上网看了一位大神写的代码,但是发现区域算法写的不太对,就重新写了一下)

该方法目前暂时未发现版本兼容问题。而且导出时间在4-5秒左右,也不需要关闭其它的excel。本人比较推荐。

以上就是我的分析和代码,有啥更好的方案大家一起探讨分享啊

没有更多推荐了,返回首页