由于XSSFWorkbook类型的Write方法限制，Write完成后就自动关闭流数据，所以无法很好的支持的Web模式，网上目前也未找到好的解决方案。
注意：若直接使用在WinForm项目中，必需先下载并在项目中引用NPOI2.0或以上版本的组件才可以正常使用。
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 私有方法 /// /// 获取要保存的文件名称（含完整路径） /// /// /// 判断是否为兼容模式 /// /// /// /// 创建工作薄 /// /// /// /// 创建工作薄(依据文件流) /// /// /// /// /// 创建表格头单元格 /// /// /// /// 从工作表中生成DataTable /// /// /// /// /// 由DataSet导出Excel /// /// 要导出数据的DataTable /// Excel工作表 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; } /// /// 由DataTable导出Excel /// /// 要导出数据的DataTable /// Excel工作表 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; } /// /// 由List导出Excel /// /// 类型 /// 在导出的List /// sheet名称 /// (List data, IList > 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; } /// /// 由DataGridView导出 /// /// /// /// /// /// 由Excel导入DataTable /// /// Excel文件流 /// Excel工作表名称 /// Excel表头行索引 /// 是否为兼容模式 /// DataTable 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; } /// /// 由Excel导入DataTable /// /// Excel文件路径，为物理路径。 /// Excel工作表名称 /// Excel表头行索引 /// DataTable 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); } } /// /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable /// /// Excel文件流 /// Excel表头行索引 /// 是否为兼容模式 /// DataSet 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; } /// /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable /// /// Excel文件路径，为物理路径。 /// Excel表头行索引 /// DataSet 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 公共转换方法 /// /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B... /// /// 列索引 /// 列名，如第0列为A，第1列为B... 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(); } /// /// 转化日期 /// /// 日期 /// /// 转化数字 /// /// 数字字符串 ///
原文：https://www.cnblogs.com/jimcsharp/p/6634530.html