C# DataTableToExcel的几种实现方式及遇到的问题

个人总结了几种常用DataTableToExcel的实现方式：

1.StreamWriter写入：缺点，数据类型只能为整数型如：007显示为7；优点，适合大量数据，速度快。

2.Microsoft.Office.Interop.Excel.dll：缺点，需要安装office，对于一些服务器来说有一定的局限性；优点，可满足大部分样式设置。

3.NOPI：这是我个人比较推荐的一种方式，他没有任何版权的限制，读取速度较快，适合多个版本的office。

说了这么多，接下来进入正题，现在对各种方式导入到excel进行一下对比

1.直接通过流写入（基本靠拼接）

/// <summary>
        /// 将数据保存到文件（格式可以是.xls,.txt等;）
        /// 缺点：数据类型只能为整数型如：007显示为7
        /// 优点：适合大量数据，速度快
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="titles">行标题(按从左到右排列)</param>
        /// <param name="file">要保存的文件路径</param>
        public static void DataTableToExcel(DataTable table, string[] titles, string file)
        {
            string title = "";


            FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
            StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);

   //拼接表头
            for (int i = 0; i < table.Columns.Count; i++)
            {
                title += titles[i] + "\t";//自动跳到下一单元格
            }
            title = title.Substring(0, title.Length - 1) + "\n";
            sw.Write(title);

 
            foreach (DataRow row in table.Rows)
            {
                string line = "";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    //line += row[i].ToString().Trim() + "\t"; //内容：自动跳到下一单元格
                    line += row[i].ToString().Trim() + "\t";//自动跳到下一单元格
                }
                line = line.Substring(0, line.Length - 1) + "\n";
                sw.Write(line);
            }
            sw.Close();
            fs.Close();
        }

2.Microsoft.Office.Interop.Excel.dll

/// <summary>
        /// 将datatable保存至excel
        /// Microsoft.Office.Interop.Excel
        /// </summary>
        /// <param name="srcDataTable">数据源</param>
        /// <param name="arryTitle">excel列标题</param>
        /// <param name="excelFilePath">要保存的路径</param>
        public static void DataTableToExcel(System.Data.DataTable srcDataTable, string[] arryTitle, string excelFilePath)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            object missing = System.Reflection.Missing.Value;


            //导出到execl 
            try
            {
                if (xlApp == null)
                {
                    return;
                }


                Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
                //range = (Microsoft.Office.Interop.Excel.Range)xlSheet.get_Range("A1", "K1");
                //xlSheet.Name
                //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
                xlApp.Visible = false;


                object[,] objData = new object[srcDataTable.Rows.Count + 1, srcDataTable.Columns.Count];
                //首先将数据写入到一个二维数组中
                for (int i = 0; i < srcDataTable.Columns.Count; i++)
                {
                    //objData[0, i] = srcDataTable.Columns[i].ColumnName;
                    objData[0, i] = arryTitle[i];
                }
                if (srcDataTable.Rows.Count > 0)
                {
                    for (int i = 0; i < srcDataTable.Rows.Count; i++)
                    {
                        for (int j = 0; j < srcDataTable.Columns.Count; j++)
                        {
                            objData[i + 1, j] = srcDataTable.Rows[i][j];
                        }
                    }
                }
                string startCol = "A";
                int iCnt = (srcDataTable.Columns.Count / 26);
                string endColSignal = (iCnt == 0 ? "" : ((char)('A' + (iCnt - 1))).ToString());
                string endCol = endColSignal + ((char)('A' + srcDataTable.Columns.Count - iCnt * 26 - 1)).ToString();
                Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(startCol + "1", endCol + (srcDataTable.Rows.Count - iCnt * 26 + 1).ToString());
                range.NumberFormatLocal = "@";     //设置单元格格式为文本
                range.Value = objData; //给Exccel中的Range整体赋值
                range.EntireColumn.AutoFit(); //设定Excel列宽度自适应
                xlSheet.get_Range(startCol + "1", endCol + "1").Font.Bold = 1;//Excel文件列名 字体设定为Bold


                //设置禁止弹出保存和覆盖的询问提示框
                xlApp.DisplayAlerts = false;
                xlApp.AlertBeforeOverwriting = false;
                if (xlSheet != null)
                {
                    xlSheet.SaveAs(excelFilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing);


                }
            }
            catch (Exception ex)
            {
                Log.Error(ex);
            }
            finally
            {
                xlApp.Quit(); // 退出 Excel
                xlApp = null; // 将 Excel 实例设置为空
            }
        }

3.NPOI：需要注意的是如果在没有http请求的情况下（如windows服务请求）HttpContext.Current会出现空异常

3.1适合web页面

/// <summary>   

        /// 根据模板生成excel：生成复杂表头的excel，模板中的数据列需要和DataTable中的列一致

        /// 适用于Web页面

        /// </summary>

        /// <param name="ExcelPath">模板文件路径</param>

        /// <param name="dRow">从第几行开始写入</param>

        /// <param name="dColumn">从第几列开始写入</param>

        /// <param name="SourceTable">DataTable数据源</param>

        /// <param name="strFilename">Excel文件名</param>

        public static void ExportToExcelByTemplate(string ExcelPath, int dRow, int dColumn, DataTable SourceTable, string strFilename)

        {

            if ((SourceTable == null) || string.IsNullOrEmpty(ExcelPath))

            {

                return;

            }

            MemoryStream stream = new MemoryStream();

            HSSFWorkbook workbook = new HSSFWorkbook(new FileStream(ExcelPath, FileMode.Open, FileAccess.Read));

            string sheetName = String.IsNullOrEmpty(SourceTable.TableName) ? "Sheet1" : SourceTable.TableName;

            ISheet sheet = workbook.GetSheet(sheetName);

            for (int i = 0; i < SourceTable.Rows.Count; i++)

            {

                IRow row = sheet.CreateRow(i + dRow);

                for (int j = 0; j < SourceTable.Columns.Count; j++)

                {

                    row.CreateCell(j + dColumn).SetCellValue(SourceTable.Rows[i][j].ToString());

                }

            }

            workbook.Write(stream);

            sheet = null;

            workbook = null;

            stream.Flush();

            stream.Position = 0L;

            //return stream;

            System.Web.

            HttpContext curContext = HttpContext.Current;

            curContext.Response.ContentType = "application/vnd.ms-excel";

            curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;

            curContext.Response.Charset = "";

            curContext.Response.AppendHeader("Content-Disposition",

                 "attachment;filename=" + HttpUtility.UrlEncode(strFilename, Encoding.UTF8));

            curContext.Response.BinaryWrite(stream.GetBuffer());

            curContext.Response.End();

        }

3.2适合于无http请求

/// <summary>
        /// 将DataTable保存至excel(不需要模板)
        /// 适用于无需http请求
        /// </summary>
        /// <param name="dRow">从第几行开始写入</param>
        /// <param name="dColumn">从第几列开始写入</param>
        /// <param name="titleCol">table名需要合并单元格的数量</param>
        /// <param name="title">table名</param>
        /// <param name="arryTitle">列表头</param>
        /// <param name="SourceTable">DataTable</param>
        /// <param name="strFilename">保存的路径</param>
        public static void ExportToExcelByNOPI(int dRow, int dColumn,int titleCol,string title, string[] arryTitle, DataTable SourceTable, string strFilename)
        {
            if ((SourceTable == null) || string.IsNullOrEmpty(strFilename))
            {
                return;
            }


            if (File.Exists(strFilename))
            {
                File.Delete(strFilename);
            }
            File.Create(strFilename).Close();


            FileStream stream = new FileStream(strFilename,FileMode.Open,FileAccess.ReadWrite);
            HSSFWorkbook workbook = new HSSFWorkbook();
            string sheetName = String.IsNullOrEmpty(SourceTable.TableName) ? "Sheet1" : SourceTable.TableName;
            ISheet sheet = workbook.CreateSheet(sheetName);
            
            IRow row;
            sheet.CreateRow(0);
            row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue(title);
            ICellStyle style = workbook.CreateCellStyle();
            //style.Alignment = CellStyle.ALIGN_CENTER;
            style.Alignment = HorizontalAlignment.Center;//居中显示
            style.IsLocked = true;//单元格是否锁定
            IFont font = workbook.CreateFont();
            //font.FontHeightInPoints = 20;
            style.SetFont(font);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, titleCol));
            
            row = sheet.CreateRow(1);
            for (int i = 0; i < SourceTable.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(arryTitle[i]);
            }
            for (int i = 0; i < SourceTable.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + dRow);
                for (int j = 0; j < SourceTable.Columns.Count; j++)
                {
                    row.CreateCell(j + dColumn).SetCellValue(SourceTable.Rows[i][j].ToString());
                }
            }
            workbook.Write(stream);


        }

希望对各位有用！