将数据导出到Excel

青旅半醒 2022-01-28 10:19 455阅读 0赞

最近在做一个项目，涉及到将数据导出到excel，因此花了一天多时间，从网上查询了一些相关方法：下面做一下总结：  
下面这个项目里面的步骤是本人已经成功应用到项目中的。  
第一步骤：将NPOI DLL文件引用到项目中：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb2JpbmdqaQ_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb2JpbmdqaQ_size_16_color_FFFFFF_t_70 1]  
然后在项目引中会出现  
![在这里插入图片描述][20190523223015247.png]

using NPOI.SS.UserModel;
    using NPOI.HSSF.UserModel;
    using NPOI.XSSF.UserModel;

第二步：打开保存文件夹

SaveFileDialog sfd = new SaveFileDialog();
    
                //设置文件类型 
                sfd.Filter = "Excel文件（*.xls）|*.xls";
    
                //设置默认文件类型显示顺序 
                sfd.FilterIndex = 1;
    
                //保存对话框是否记忆上次打开的目录 
                sfd.RestoreDirectory = true;
    
                //设置默认的文件名
                DateTime dataNow = DateTime.Now;
                string fileName = "XXXXXx" + dataNow.ToString("yyyy-MM-dd-HH-mm-ss");
                sfd.FileName = fileName;
                string localFilePath = null;
    
                //点了保存按钮进入 
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    localFilePath = sfd.FileName.ToString(); //获得文件路径 
                    //string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径
                }
                else
                {
                    return;
                }

第三步：

IWorkbook wb = new HSSFWorkbook();//建立一个excel文件
                //创建一个工作簿
                ISheet sh = wb.CreateSheet(captionName);
    
                #region 表头(第1行)
                //合并单元格
                sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, propDictionary.Count - 1));
                //创建第1行
                IRow row0 = sh.CreateRow(0);
                //设置第1行高度
                row0.Height = 20 * 20;
                //创建第1行第1列
                ICell icell1top0 = row0.CreateCell(0);
                //设置第1行第1列格式
                icell1top0.CellStyle = Getcellstyle(wb, "head");
                //设置第1行第1列内容
                icell1top0.SetCellValue(captionName);
                #endregion
    
                #region 抬头(第2行)
                //创建第2行
                IRow row1 = sh.CreateRow(1);
                //设置高度
                row1.Height = 20 * 20;
                //columnt_index是列的索引
                int columnt_index = 0;
                ICellStyle style = Getcellstyle(wb, "");
                foreach (var itemtmp in propDictionary)
                {
                    //获取T的字段名称
                    string name = itemtmp.Key.ToString();
                    //创建第2行的第columnt_index列
                    ICell icell1top = row1.CreateCell(columnt_index);
                    //设置第2行的第columnt_index列的格式
                    icell1top.CellStyle = style;
                    //设置第2行的第columnt_index列的内容
                    icell1top.SetCellValue(name);
                    //设置第2行的第columnt_index列的宽度
                    sh.SetColumnWidth(columnt_index, (int)((15 + 0.72) * 256));
                    columnt_index++;
                }
                #endregion  
    
                
                #region 这里是data具体内容
                //创建第3行
                int rownums = 2;
                foreach (var item in resultToXLS.Items)
                {
                    IRow row_zs = sh.CreateRow(rownums);
                    //创建第1列、格式、字段值
                    ICell icell1top = row_zs.CreateCell(0);
                    icell1top.CellStyle = style;
                    icell1top.SetCellValue(item.ID.ToString());
    
                    ICell icell1top1 = row_zs.CreateCell(1);
                    icell1top1.CellStyle = style;
                    icell1top1.SetCellValue(item.PlateNum);
    
                    ICell icell1top2 = row_zs.CreateCell(2);
                    icell1top2.CellStyle = style;
                    icell1top2.SetCellValue(item.PlateColor);
    
                    ICell icell1top3 = row_zs.CreateCell(3);
                    icell1top3.CellStyle = style;
                    icell1top3.SetCellValue(item.PassTime.ToString("yyyy-MM-dd HH:mm:ss"));
    
                    ICell icell1top4 = row_zs.CreateCell(4);
                    icell1top4.CellStyle = style;
                    icell1top4.SetCellValue(item.Result);
    
                    ICell icell1top5 = row_zs.CreateCell(5);
                    icell1top5.CellStyle = style;
                    icell1top5.SetCellValue(item.CO.ToString());
    
                    ICell icell1top6 = row_zs.CreateCell(6);
                    icell1top6.CellStyle = style;
                    icell1top6.SetCellValue(item.CO2.ToString());
    
                    ICell icell1top7 = row_zs.CreateCell(7);
                    icell1top7.CellStyle = style;
                    icell1top7.SetCellValue(item.NO.ToString());
    
                    ICell icell1top8 = row_zs.CreateCell(8);
                    icell1top8.CellStyle = style;
                    icell1top8.SetCellValue(item.HC.ToString());
    
                    ICell icell1top9 = row_zs.CreateCell(9);
                    icell1top9.CellStyle = style;
                    icell1top9.SetCellValue(item.Opacity.ToString());
    
                    ICell icell1top10 = row_zs.CreateCell(10);
                    icell1top10.CellStyle = style;
                    icell1top10.SetCellValue(item.K.ToString());
    
                    ICell icell1top11 = row_zs.CreateCell(11);
                    icell1top11.CellStyle = style;
                    icell1top11.SetCellValue(item.SF.ToString());
    
                    ICell icell1top12 = row_zs.CreateCell(12);
                    icell1top12.CellStyle = style;
                    icell1top12.SetCellValue(item.Speed.ToString());
    
                    ICell icell1top13 = row_zs.CreateCell(13);
                    icell1top13.CellStyle = style;
                    icell1top13.SetCellValue(item.Acc.ToString());
    
                    ICell icell1top14 = row_zs.CreateCell(14);
                    icell1top14.CellStyle = style;
                    icell1top14.SetCellValue(item.Temperature.ToString());
    
                    ICell icell1top15 = row_zs.CreateCell(15);
                    icell1top15.CellStyle = style;
                    icell1top15.SetCellValue(item.WindSpeed.ToString());
    
                    ICell icell1top16 = row_zs.CreateCell(16);
                    icell1top16.CellStyle = style;
                    icell1top16.SetCellValue(item.WindDirection.ToString());
    
                    ICell icell1top17 = row_zs.CreateCell(17);
                    icell1top17.CellStyle = style;
                    icell1top17.SetCellValue(item.Pressure.ToString());
    
                    ICell icell1top18 = row_zs.CreateCell(18);
                    icell1top18.CellStyle = style;
                    icell1top18.SetCellValue(item.EntireBelieve.ToString());
    
                    ICell icell1top19 = row_zs.CreateCell(19);
                    icell1top19.CellStyle = style;
                    icell1top19.SetCellValue(item.Humidity.ToString());
    
                    ICell icell1top20 = row_zs.CreateCell(20);
                    icell1top20.CellStyle = style;
                    icell1top20.SetCellValue(item.IsUploaded.ToString());
                    rownums++;
                }
                #endregion
    
                //输出内容
                if (string.IsNullOrEmpty(localFilePath))
                {
                    return;
                }
                using (FileStream stm = File.OpenWrite(@localFilePath))
                {
                    wb.Write(stm);
                    MessageBox.Show("导出Excle完成！");
                }

第四步：

/// 格式设置
            /// </summary>
            static ICellStyle Getcellstyle(IWorkbook wb, string type)
            {
                ICellStyle cellStyle = wb.CreateCellStyle();
                //定义字体  
                IFont font = wb.CreateFont();
                font.FontName = "微软雅黑";
                //水平对齐  
                cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                //垂直对齐  
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                //自动换行  
                cellStyle.WrapText = true;
                //缩进
                cellStyle.Indention = 0;
    
                switch (type)
                {
                    case "head":
                        cellStyle.SetFont(font);
                        cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        break;
                    default:
                        cellStyle.SetFont(font);
                        break;
                }
                return cellStyle;
            }

以上就是成功应用的步骤。

--------------------

private void Export()
            {
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter = "Excel文件|*.xls";
                saveDialog.FileName = "export";
                saveDialog.DefaultExt = "xlsx";
                saveDialog.AddExtension = true;
                saveDialog.RestoreDirectory = true;
                DialogResult result = saveDialog.ShowDialog();
                if(result==DialogResult.OK)
                {
                    DateTime start = dateEditStart.DateTime;
                    DateTime end = dateEditEnd.DateTime.AddDays(1).AddSeconds(-1);
                    List<StaticTesting> dataList = staticTestingRepository.GetByTime(start, end);
    
                    IWorkbook workbook = new HSSFWorkbook();
                    ISheet sheet = workbook.CreateSheet("sheet");
                    IRow row0 = sheet.CreateRow(0);
                    row0.CreateCell(0).SetCellValue("11111");
                    row0.CreateCell(1).SetCellValue("222222");
                    row0.CreateCell(2).SetCellValue("333333");
                  
                    for (int r = 0; r < dataList.Count; r++)
                    {
                        IRow row = sheet.CreateRow(r+1);
                        row.CreateCell(0).SetCellValue(dataList[r].111);
                        row.CreateCell(1).SetCellValue(dataList[r].222);
                        row.CreateCell(2).SetCellValue(dataList[r].333);
                       
                    }
                    using (FileStream fs = File.OpenWrite(saveDialog.FileName))
                    {
                        workbook.Write(fs);
                        fs.Flush();
                        fs.Close();
                    };
    
                }
            }

--------------------

下面是我统计一些较为有用的方法  
1、使用NPOI处理大数据量EXCEl2007  
excel2003只能存储65536行数据，而excel2007能存储100多万行数据。所以针对日后的数据的一个可预见性增长，所以采用excel2007来处理数据，但是C\#默认的excel

的处理方式只能处理excel2003，所以为了处理大数据的excel2007，我们使用NPOI来进行处理  
需要引入相应的dll文件，具体引入方法就不再多说，具体的实现方式见下方代码。

*生成导出EXCEl文件对话框
                 *同时将导出文件类型定义为Excel
                 *保存时只需指明对应的文件名即可
                 */
                SaveFileDialog sdfexport = new SaveFileDialog();
                sdfexport.Filter = "Excel文件|*.xlsx";
                if (sdfexport.ShowDialog() == DialogResult.No)
                {
                    return;
                }
                string filename = sdfexport.FileName;
     
                //创建Excel2007工作簿
                IWorkbook book = new XSSFWorkbook();
     
                //创建Excel2007工作表
                ISheet sheet = book.CreateSheet("standard_template");
     
                //创建Excel行
                IRow row = sheet.CreateRow(0);
     
                //给单元格赋值
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("大区(区域)");
                row.CreateCell(2).SetCellValue("省(简)");
                row.CreateCell(3).SetCellValue("说明");
                
                /*
                 * 将Excel文件写入相应的Excel文件中
                 */ 
                FileStream fs = File.Create(filename);
                book.Write(fs);
                fs.Close();

--------------------

2、NPOI导入导出EXCEL通用类，供参考，可直接使用在WinForm项目中

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using System.Data;
    using System.Windows.Forms;
    using NPOI.SS.UserModel;
    using NPOI.HSSF.UserModel;
    using NPOI.XSSF.UserModel;
    using Zwj.TEMS.Common;
    
    namespace TEMS.Service
    {
        public static class ExcelHelperForCs
        {
            #region 私有方法
    
            /// <summary>
            /// 获取要保存的文件名称（含完整路径）
            /// </summary>
            /// <returns></returns>
            private static string GetSaveFilePath()
            {
                SaveFileDialog saveFileDig = new SaveFileDialog();
                saveFileDig.Filter = "Excel Office97-2003(*.xls)|.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
                saveFileDig.FilterIndex = 0;
                saveFileDig.OverwritePrompt = true;
                saveFileDig.InitialDirectory = Common.DesktopDirectory;
                string filePath = null;
                if (saveFileDig.ShowDialog() == DialogResult.OK)
                {
                    filePath = saveFileDig.FileName;
                }
    
                return filePath;
            }
    
            /// <summary>
            /// 判断是否为兼容模式
            /// </summary>
            /// <param name="filePath"></param>
            /// <returns></returns>
            private static bool GetIsCompatible(string filePath)
            {
                return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
            }
    
    
    
            /// <summary>
            /// 创建工作薄
            /// </summary>
            /// <param name="isCompatible"></param>
            /// <returns></returns>
            private static IWorkbook CreateWorkbook(bool isCompatible)
            {
                if (isCompatible)
                {
                    return new HSSFWorkbook();
                }
                else
                {
                    return new XSSFWorkbook();
                }
            }
    
            /// <summary>
            /// 创建工作薄(依据文件流)
            /// </summary>
            /// <param name="isCompatible"></param>
            /// <param name="stream"></param>
            /// <returns></returns>
            private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
            {
                if (isCompatible)
                {
                    return new HSSFWorkbook(stream);
                }
                else
                {
                    return new XSSFWorkbook(stream);
                }
            }
    
            /// <summary>
            /// 创建表格头单元格
            /// </summary>
            /// <param name="sheet"></param>
            /// <returns></returns>
            private static ICellStyle GetCellStyle(IWorkbook workbook)
            {
                ICellStyle style = workbook.CreateCellStyle();
                style.FillPattern = FillPattern.SolidForeground;
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
    
                return style;
            }
    
    
            /// <summary>
            /// 从工作表中生成DataTable
            /// </summary>
            /// <param name="sheet"></param>
            /// <param name="headerRowIndex"></param>
            /// <returns></returns>
            private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
            {
                DataTable table = new DataTable();
    
                IRow headerRow = sheet.GetRow(headerRowIndex);
                int cellCount = headerRow.LastCellNum;
    
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                    {
                        // 如果遇到第一个空列，则不再继续向后读取
                        cellCount = i + 1;
                        break;
                    }
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
    
                for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
    
                    if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue))
                    {
                        DataRow dataRow = table.NewRow();
    
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
    
                        table.Rows.Add(dataRow);
                    }
                }
    
                return table;
            }
    
            #endregion
    
            #region 公共导出方法
    
            /// <summary>
            /// 由DataSet导出Excel
            /// </summary>
            /// <param name="sourceTable">要导出数据的DataTable</param>
            /// <returns>Excel工作表</returns>
            public static string ExportToExcel(DataSet sourceDs, string filePath = null)
            {
    
                if (string.IsNullOrEmpty(filePath))
                {
                    filePath = GetSaveFilePath();
                }
    
                if (string.IsNullOrEmpty(filePath)) return null;
    
                bool isCompatible = GetIsCompatible(filePath);
    
                IWorkbook workbook = CreateWorkbook(isCompatible);
                ICellStyle cellStyle = GetCellStyle(workbook);
    
                for (int i = 0; i < sourceDs.Tables.Count; i++)
                {
                    DataTable table = sourceDs.Tables[i];
                    string sheetName = "result" + i.ToString();
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    IRow headerRow = sheet.CreateRow(0);
                    // handling header.
                    foreach (DataColumn column in table.Columns)
                    {
                        ICell cell = headerRow.CreateCell(column.Ordinal);
                        cell.SetCellValue(column.ColumnName);
                        cell.CellStyle = cellStyle;
                    }
    
                    // handling value.
                    int rowIndex = 1;
    
                    foreach (DataRow row in table.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);
    
                        foreach (DataColumn column in table.Columns)
                        {
                            dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString());
                        }
    
                        rowIndex++;
                    }
                }
    
                FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook.Write(fs);
                fs.Dispose();
                workbook = null;
    
                return filePath;
    
            }
    
    
            /// <summary>
            /// 由DataTable导出Excel
            /// </summary>
            /// <param name="sourceTable">要导出数据的DataTable</param>
            /// <returns>Excel工作表</returns>
            public static string ExportToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null)
            {
                if (sourceTable.Rows.Count <= 0) return null;
    
                if (string.IsNullOrEmpty(filePath))
                {
                    filePath = GetSaveFilePath();
                }
    
                if (string.IsNullOrEmpty(filePath)) return null;
    
                bool isCompatible = GetIsCompatible(filePath);
    
                IWorkbook workbook = CreateWorkbook(isCompatible);
                ICellStyle cellStyle = GetCellStyle(workbook);
    
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.
                foreach (DataColumn column in sourceTable.Columns)
                {
                    ICell headerCell = headerRow.CreateCell(column.Ordinal);
                    headerCell.SetCellValue(column.ColumnName);
                    headerCell.CellStyle = cellStyle;
                }
    
                // handling value.
                int rowIndex = 1;
    
                foreach (DataRow row in sourceTable.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
    
                    foreach (DataColumn column in sourceTable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue((row[column]??"").ToString());
                    }
    
                    rowIndex++;
                }
                FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook.Write(fs);
                fs.Dispose();
    
                sheet = null;
                headerRow = null;
                workbook = null;
    
                return filePath;
            }
    
            /// <summary>
            /// 由List导出Excel
            /// </summary>
            /// <typeparam name="T">类型</typeparam>
            /// <param name="data">在导出的List</param>
            /// <param name="sheetName">sheet名称</param>
            /// <returns></returns>
            public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "result", string filePath = null) where T : class
            {
                if (data.Count <= 0) return null;
    
                if (string.IsNullOrEmpty(filePath))
                {
                    filePath = GetSaveFilePath();
                }
    
                if (string.IsNullOrEmpty(filePath)) return null;
    
                bool isCompatible = GetIsCompatible(filePath);
    
                IWorkbook workbook = CreateWorkbook(isCompatible);
                ICellStyle cellStyle = GetCellStyle(workbook);
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow headerRow = sheet.CreateRow(0);
    
                for (int i = 0; i < headerNameList.Count; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(headerNameList[i].Value);
                    cell.CellStyle = cellStyle;
                }
    
                Type t = typeof(T);
                int rowIndex = 1;
                foreach (T item in data)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    for (int n = 0; n < headerNameList.Count; n++)
                    {
                        object pValue = t.GetProperty(headerNameList[n].Key).GetValue(item, null);
                        dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString());
                    }
                    rowIndex++;
                }
                FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook.Write(fs);
                fs.Dispose();
    
                sheet = null;
                headerRow = null;
                workbook = null;
    
                return filePath;
            }
    
            /// <summary>
            /// 由DataGridView导出
            /// </summary>
            /// <param name="grid"></param>
            /// <param name="sheetName"></param>
            /// <param name="filePath"></param>
            /// <returns></returns>
            public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null)
            {
                if (grid.Rows.Count <= 0) return null;
    
                if (string.IsNullOrEmpty(filePath))
                {
                    filePath = GetSaveFilePath();
                }
    
                if (string.IsNullOrEmpty(filePath)) return null;
    
                bool isCompatible = GetIsCompatible(filePath);
    
                IWorkbook workbook = CreateWorkbook(isCompatible);
                ICellStyle cellStyle = GetCellStyle(workbook);
                ISheet sheet = workbook.CreateSheet(sheetName);
    
                IRow headerRow = sheet.CreateRow(0);
    
                for (int i = 0; i < grid.Columns.Count; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(grid.Columns[i].Name);
                    cell.CellStyle = cellStyle;
                }
    
                int rowIndex = 1;
                foreach (DataGridViewRow row in grid.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    for (int n = 0; n < grid.Columns.Count; n++)
                    {
                        dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());
                    }
                    rowIndex++;
                }
    
                FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook.Write(fs);
                fs.Dispose();
    
                sheet = null;
                headerRow = null;
                workbook = null;
    
                return filePath;
            }
    
            #endregion
    
            #region 公共导入方法
    
            /// <summary>
            /// 由Excel导入DataTable
            /// </summary>
            /// <param name="excelFileStream">Excel文件流</param>
            /// <param name="sheetName">Excel工作表名称</param>
            /// <param name="headerRowIndex">Excel表头行索引</param>
            /// <param name="isCompatible">是否为兼容模式</param>
            /// <returns>DataTable</returns>
            public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
            {
                IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
                ISheet sheet = null;
                int sheetIndex = -1;
                if (int.TryParse(sheetName, out sheetIndex))
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                }
                else
                {
                    sheet = workbook.GetSheet(sheetName);
                }
    
                DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
    
                excelFileStream.Close();
                workbook = null;
                sheet = null;
                return table;
            }
    
            /// <summary>
            /// 由Excel导入DataTable
            /// </summary>
            /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
            /// <param name="sheetName">Excel工作表名称</param>
            /// <param name="headerRowIndex">Excel表头行索引</param>
            /// <returns>DataTable</returns>
            public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
            {
                using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
                {
                    bool isCompatible = GetIsCompatible(excelFilePath);
                    return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible);
                }
            }
    
            /// <summary>
            /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
            /// </summary>
            /// <param name="excelFileStream">Excel文件流</param>
            /// <param name="headerRowIndex">Excel表头行索引</param>
            /// <param name="isCompatible">是否为兼容模式</param>
            /// <returns>DataSet</returns>
            public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
            {
                DataSet ds = new DataSet();
                IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
                    ds.Tables.Add(table);
                }
    
                excelFileStream.Close();
                workbook = null;
    
                return ds;
            }
    
            /// <summary>
            /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
            /// </summary>
            /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
            /// <param name="headerRowIndex">Excel表头行索引</param>
            /// <returns>DataSet</returns>
            public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
            {
                using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
                {
                    bool isCompatible = GetIsCompatible(excelFilePath);
                    return ImportFromExcel(stream, headerRowIndex, isCompatible);
                }
            }
    
            #endregion
    
            #region 公共转换方法
    
            /// <summary>
            /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
            /// </summary>
            /// <param name="index">列索引</param>
            /// <returns>列名，如第0列为A，第1列为B...</returns>
            public static string ConvertColumnIndexToColumnName(int index)
            {
                index = index + 1;
                int system = 26;
                char[] digArray = new char[100];
                int i = 0;
                while (index > 0)
                {
                    int mod = index % system;
                    if (mod == 0) mod = system;
                    digArray[i++] = (char)(mod - 1 + 'A');
                    index = (index - 1) / 26;
                }
                StringBuilder sb = new StringBuilder(i);
                for (int j = i - 1; j >= 0; j--)
                {
                    sb.Append(digArray[j]);
                }
                return sb.ToString();
            }
    
    
            /// <summary>
            /// 转化日期
            /// </summary>
            /// <param name="date">日期</param>
            /// <returns></returns>
            public static DateTime ConvertDate(object date)
            {
                string dtStr = (date ?? "").ToString();
    
                DateTime dt = new DateTime();
    
                if (DateTime.TryParse(dtStr, out dt))
                {
                    return dt;
                }
    
                try
                {
                    string spStr = "";
                    if (dtStr.Contains("-"))
                    {
                        spStr = "-";
                    }
                    else if (dtStr.Contains("/"))
                    {
                        spStr = "/";
                    }
                    string[] time = dtStr.Split(spStr.ToCharArray());
                    int year = Convert.ToInt32(time[2]);
                    int month = Convert.ToInt32(time[0]);
                    int day = Convert.ToInt32(time[1]);
                    string years = Convert.ToString(year);
                    string months = Convert.ToString(month);
                    string days = Convert.ToString(day);
                    if (months.Length == 4)
                    {
                        dt = Convert.ToDateTime(date);
                    }
                    else
                    {
                        string rq = "";
                        if (years.Length == 1)
                        {
                            years = "0" + years;
                        }
                        if (months.Length == 1)
                        {
                            months = "0" + months;
                        }
                        if (days.Length == 1)
                        {
                            days = "0" + days;
                        }
                        rq = "20" + years + "-" + months + "-" + days;
                        dt = Convert.ToDateTime(rq);
                    }
                }
                catch
                {
                    throw new Exception("日期格式不正确，转换日期失败！");
                }
                return dt;
            }
    
            /// <summary>
            /// 转化数字
            /// </summary>
            /// <param name="d">数字字符串</param>
            /// <returns></returns>
            public static decimal ConvertDecimal(object d)
            {
                string dStr = (d ?? "").ToString();
                decimal result = 0;
                if (decimal.TryParse(dStr, out result))
                {
                    return result;
                }
                else
                {
                    throw new Exception("数字格式不正确，转换数字失败！");
                }
    
            }
    
            #endregion
        }
    }

--------------------

3、最近工作导数据失败，找问题原因：ORALCE导出数据，超出5w多条数据，由于数据量过大（加上列数多），无法导出。  
PLSQL工具导出EXCEL失败，卡死白屏。  
使用NPOI第三方类导出EXCEL失败，超出100+M多的数据就抛出异常提示内存溢出。  
最后找到方法，使用OLEDB方式，数据导出到EXCEL文件，数据量大的时候，运行占用内存明显比NPOI和微软组件小（不超过100M）。

/// <summary>
    /// 使用OLEDB导出Excel
    /// </summary>
    /// <param name="dt">数据集</param>
    /// <param name="filepath">文件目录和文件名</param>
    /// <param name="tablename">SHEET页名称</param>
    /// <param name="pagecount">每页记录数</param>
    public static void Export(DataTable dt, string filepath, string tablename, int pagecount)
    {
        //excel 2003格式
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        //Excel 2007格式
        //string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";
        try
        {
            using (OleDbConnection con = new OleDbConnection(connString))
            {
                con.Open();
                
                //开始分页
                if (dt.Rows.Count > pagecount)
                {
                    int page = dt.Rows.Count / pagecount + 1; //总页数
                    for (int i = 0; i < page; i++)
                    {
                        //建新sheet和表头
                        StringBuilder strSQL = new StringBuilder();
                        string tabname = tablename + i.ToString();
                        strSQL.Append("CREATE TABLE ").Append("[" + tabname + "]"); //每60000项建一页
                        strSQL.Append("(");
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            strSQL.Append("[" + dt.Columns[j].ColumnName + "] text,");
                        }
                        strSQL = strSQL.Remove(strSQL.Length - 1, 1);
                        strSQL.Append(")");
     
                        OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
                        cmd.ExecuteNonQuery();
     
                        //准备逐条插入数据
                        for (int j = i * pagecount; j < (i + 1) * pagecount; j++)
                        {
                            if (i == 0 || j < dt.Rows.Count)
                            {
                                StringBuilder tmp = new StringBuilder();
                                StringBuilder strfield = new StringBuilder();
                                StringBuilder strvalue = new StringBuilder();
                                for (int z = 0; z < dt.Columns.Count; z++)
                                {
                                    strfield.Append("[" + dt.Columns[z].ColumnName + "]");
                                    strvalue.Append("'" + dt.Rows[j][z].ToString() + "'");
                                    if (z != dt.Columns.Count - 1)
                                    {
                                        strfield.Append(",");
                                        strvalue.Append(",");
                                    }
                                    else
                                    {
                                    }
                                }
                                cmd.CommandText = tmp.Append(" insert into [" + tabname + "]( ")
                                    .Append(strfield.ToString())
                                    .Append(") values (").Append(strvalue).Append(")").ToString();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                con.Close();
                no = count;
            }
            Console.WriteLine("OK");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        GC.Collect();
    }

--------------------

4、NPOI导出Excel和EPPlus导出Excel比较  
系统中经常会使用导出Excel的功能。

之前使用的是NPOI，但是导出数据行数多就报内存溢出。

最近看到EPPlus可以用来导出Excel，就自己测了下两者导出上的差异。

NPIO官网地址：[http://npoi.codeplex.com/][http_npoi.codeplex.com]

EPPlus官网地址：[http://epplus.codeplex.com/][http_epplus.codeplex.com]

添加NPOI、EPPlus类库dll使用的是NuGet添加。

在类库References右键Manage NuGet Packages…，之后选择添加对应的dll。

测试结果显示，相同数据结构的数据，EPPlus的导出能力比NPOI强。

20列，NPOI能导出4万数据，导出5万数据时报内存溢出。

EPPlus能导出20万以上数据，导出23万测试时内存溢出。  
NPOI导出：

1 private static MemoryStream ExportXlsx(DataTable dt)
     2         {
     3             XSSFWorkbook workbook = new XSSFWorkbook();
     4             ISheet sheet = null;
     5 
     6             int headRowIndex = 0;
     7             string sheetName = "Sheet1";
     8             if (!string.IsNullOrEmpty(dt.TableName))
     9             {
    10                 sheetName = dt.TableName;
    11             }
    12             sheet = workbook.CreateSheet(sheetName);
    13             int rowIndex = 0;
    14 
    15             #region 列头及样式
    16             {
    17                 XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);
    18 
    19                 ICellStyle headStyle = workbook.CreateCellStyle();
    20                 headStyle.Alignment = HorizontalAlignment.Center;
    21                 IFont font = workbook.CreateFont();
    22                 font.FontHeightInPoints = 10;
    23                 font.Boldweight = 700;
    24                 headStyle.SetFont(font);
    25 
    26                 foreach (DataColumn column in dt.Columns)
    27                 {
    28                     headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
    29                     headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
    30                 }
    31             }
    32             #endregion
    33 
    34             #region 填充内容
    35 
    36             foreach (DataRow row in dt.Rows)
    37             {
    38                 rowIndex++;
    39                 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
    40                 foreach (DataColumn column in dt.Columns)
    41                 {
    42                     string drValue = row[column].ToString();
    43                     dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
    44                 }
    45             }
    46             #endregion
    47 
    48 
    49             MemoryStream ms = new MemoryStream();
    50 
    51             workbook.Write(ms);
    52             ms.Flush();
    53 
    54             return ms;
    55         }
    56 
    57         public static void ExportXlsxByWeb(DataTable dt, string strFileName)
    58         {
    59 
    60             HttpContext curContext = HttpContext.Current;
    61 
    62             MemoryStream ms = ExportXlsx(dt);
    63 
    64             curContext.Response.AppendHeader("Content-Disposition",
    65                 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
    66             curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
    67             curContext.Response.ContentEncoding = Encoding.UTF8;
    68 
    69             curContext.Response.BinaryWrite(ms.ToArray());
    70             ms.Close();
    71             ms.Dispose();
    72             curContext.Response.End();
    73 
    74         }

EPPlus导出：

1 /// <summary>
     2         /// 使用EPPlus导出Excel(xlsx)
     3         /// </summary>
     4         /// <param name="sourceTable">数据源</param>
     5         /// <param name="strFileName">xlsx文件名(不含后缀名)</param>
     6         public static void ExportByEPPlus(DataTable sourceTable, string strFileName)
     7         {
     8             using (ExcelPackage pck = new ExcelPackage())
     9             {
    10                 //Create the worksheet
    11                 string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;
    12                 ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
    13 
    14                 //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
    15                 ws.Cells["A1"].LoadFromDataTable(sourceTable, true);
    16 
    17                 //Format the row
    18                 ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
    19                 Color borderColor = Color.FromArgb(155, 155, 155);
    20 
    21                 using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count])
    22                 {
    23                     rng.Style.Font.Name = "宋体";
    24                     rng.Style.Font.Size = 10;
    25                     rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
    26                     rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
    27 
    28                     rng.Style.Border.Top.Style = borderStyle;
    29                     rng.Style.Border.Top.Color.SetColor(borderColor);
    30 
    31                     rng.Style.Border.Bottom.Style = borderStyle;
    32                     rng.Style.Border.Bottom.Color.SetColor(borderColor);
    33 
    34                     rng.Style.Border.Right.Style = borderStyle;
    35                     rng.Style.Border.Right.Color.SetColor(borderColor);
    36                 }
    37 
    38                 //Format the header row
    39                 using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count])
    40                 {
    41                     rng.Style.Font.Bold = true;
    42                     rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    43                     rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
    44                     rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
    45                 }
    46 
    47                 //Write it back to the client
    48                 HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    49                 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
    50                 HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
    51 
    52                 HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
    53                 HttpContext.Current.Response.End();
    54             }
    55         }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb2JpbmdqaQ_size_16_color_FFFFFF_t_70 2]

--------------------

5导出数据到Excel（百万级）保存为csv格式

private void DataTableToCsv(DataTable table, string file)
            {
                StringBuilder titleBuilder = new StringBuilder();
                StringBuilder lineBuilder = new StringBuilder();
                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++)
                {
                    titleBuilder.Append(table.Columns[i].ColumnName + "\t"); //栏位：自动跳到下一单元格
                }
    
                string title = titleBuilder.ToString();
                title = title.Substring(0, title.Length - 1) + "\n";
    
                sw.Write(title);
    
                string line = string.Empty;
                foreach (DataRow row in table.Rows)
                {
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        lineBuilder.Append(row[i].ToString().Trim() + "\t"); //内容：自动跳到下一单元格
                    }
    
                    line = lineBuilder.ToString();
                    line = line.Substring(0, line.Length - 1) + "\n";
                    sw.Write(line);
    
                    lineBuilder.Clear();
                }
    
                sw.Close();
                fs.Close();
            }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb2JpbmdqaQ_size_16_color_FFFFFF_t_70]: /images/20220128/c17c98beff2b45e7adde27fe0da4baf3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb2JpbmdqaQ_size_16_color_FFFFFF_t_70 1]: /images/20220128/a97d77ba27a74f3f955573fc80bbde4c.png
[20190523223015247.png]: /images/20220128/7051f22588fe4489b51b2900be3e6c69.png
[http_npoi.codeplex.com]: http://npoi.codeplex.com/
[http_epplus.codeplex.com]: http://epplus.codeplex.com/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb2JpbmdqaQ_size_16_color_FFFFFF_t_70 2]: /images/20220128/1ee5f51eb6394339941076de4e09c902.png