基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility
1. ExcelUtility功能：
1.将数据导出到EXCEL(支持XLS,XLSX，支持多种类型模板，支持列宽自适应)
类名： ExcelUtility. Export
2.将EXCEL数据导入到数据对象中（DataTable、Dataset，支持XLS,XLSX）
类名： ExcelUtility. Import
类库项目文件结构如下图示：
2. ExcelUtility依赖组件：
1.NPOI 操作EXCEL核心类库
2.NPOI.Extend NPOI扩展功能
3. ExcelReport 基于NPOI的二次扩展，实现模板化导出功能
4. System.Windows.Forms 导出或导入时，弹出文件选择对话框（如果用在WEB中可以不需要，但我这里以CS端为主）
3.使用环境准备：
1.通过NUGet引用NPOI包、ExcelReport 包；（ExcelReport 存在BUG,可能需要用我项目中修得过后的DLL）
2.引用ExcelUtility类库；
4 .具体使用方法介绍（示例代码 ，全部为测试方法 ）：
导出方法测试：
/// <summary> /// 测试方法：测试将DataTable导出到EXCEL，无模板 /// </summary> [TestMethod] public void TestExportToExcelByDataTable() { DataTable dt = GetDataTable(); string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果"); Assert.IsTrue(File.Exists(excelPath)); } 结果如下图示：
/// <summary> /// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出的列名 /// </summary> [TestMethod] public void TestExportToExcelByDataTable2() { DataTable dt = GetDataTable(); string[] expColNames = { "Col1", "Col2", "Col3", "Col4", "Col5" }; string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, expColNames); Assert.IsTrue(File.Exists(excelPath)); } 结果如下图示：
/// <summary> /// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出的列名，以及导出列名的重命名 /// </summary> [TestMethod] public void TestExportToExcelByDataTable3() { DataTable dt = GetDataTable(); string[] expColNames = { "Col1", "Col2", "Col3", "Col4", "Col5" }; Dictionary<string, string> expColAsNames = new Dictionary<string, string>() { {"Col1","列一"}, {"Col2","列二"}, {"Col3","列三"}, {"Col4","列四"}, {"Col5","列五"} }; string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, expColNames,expColAsNames); Assert.IsTrue(File.Exists(excelPath)); } 结果如下图示：
/// <summary> /// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出列名的重命名 /// </summary> [TestMethod] public void TestExportToExcelByDataTable4() { DataTable dt = GetDataTable(); Dictionary<string, string> expColAsNames = new Dictionary<string, string>() { {"Col1","列一"}, {"Col5","列五"} }; string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, null, expColAsNames); Assert.IsTrue(File.Exists(excelPath)); } 结果如下图示：
/// <summary> /// 测试方法：测试依据模板+DataTable来生成EXCEL /// </summary> [TestMethod] public void TestExportToExcelWithTemplateByDataTable() { DataTable dt = GetDataTable();//获取数据 string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //获得EXCEL模板路径 SheetFormatterContainer<DataRow> formatterContainers = new SheetFormatterContainer<DataRow>(); //实例化一个模板数据格式化容器 PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//实例化一个局部元素格式化器 partFormatterBuilder.AddFormatter("Title", "跨越IT学员");//将模板表格中Title的值设置为跨越IT学员 formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效 CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//实例化一个单元格格式化器 cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期 formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效 //实例化一个表格格式化器，dt.Select()是将DataTable转换成DataRow[]，name表示的模板表格中第一行第一个单元格要填充的数据参数名 TableFormatterBuilder<DataRow> tableFormatterBuilder = new TableFormatterBuilder<DataRow>(dt.Select(), "name"); tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{ {"name",r=>r["Col1"]},//将模板表格中name对应DataTable中的列Col1 {"sex",r=>r["Col2"]},//将模板表格中sex对应DataTable中的列Col2 {"km",r=>r["Col3"]},//将模板表格中km对应DataTable中的列Col3 {"score",r=>r["Col4"]},//将模板表格中score对应DataTable中的列Col {"result",r=>r["Col5"]}//将模板表格中result对应DataTable中的列Co5 }); formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效 string excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath, "table", formatterContainers); Assert.IsTrue(File.Exists(excelPath)); } 模板如下图示：
结果如下图示：
/// <summary> /// 测试方法：测试依据模板+List来生成EXCEL /// </summary> [TestMethod] public void TestExportToExcelWithTemplateByList() { List<Student> studentList = GetStudentList();//获取数据 string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //获得EXCEL模板路径 SheetFormatterContainer<Student> formatterContainers = new SheetFormatterContainer<Student>(); //实例化一个模板数据格式化容器 PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//实例化一个局部元素格式化器 partFormatterBuilder.AddFormatter("Title", "跨越IT学员");//将模板表格中Title的值设置为跨越IT学员 formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效 CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//实例化一个单元格格式化器 cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期 formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效 //实例化一个表格格式化器，studentList本身就是可枚举的无需转换，name表示的模板表格中第一行第一个单元格要填充的数据参数名 TableFormatterBuilder<Student> tableFormatterBuilder = new TableFormatterBuilder<Student>(studentList, "name"); tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<Student, object>>{ {"name",r=>r.Name},//将模板表格中name对应Student对象中的属性Name {"sex",r=>r.Sex},//将模板表格中sex对应Student对象中的属性Sex {"km",r=>r.KM},//将模板表格中km对应Student对象中的属性KM {"score",r=>r.Score},//将模板表格中score对应Student对象中的属性Score {"result",r=>r.Result}//将模板表格中result对应Student对象中的属性Result }); formatterContainers.AppendFormatterBuilder(tableFormatterBuilder); string excelPath = ExcelUtility.Export.ToExcelWithTemplate<Student>(templateFilePath, "table", formatterContainers); Assert.IsTrue(File.Exists(excelPath)); } 结果如下图示：（模板与上面相同）
/// <summary> /// 测试方法：测试依据模板+DataTable来生成多表格EXCEL（注意：由于NPOI框架限制，目前仅支持模板文件格式为：xls） /// </summary> [TestMethod] public void TestExportToRepeaterExcelWithTemplateByDataTable() { DataTable dt = GetDataTable();//获取数据 string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel2.xls"; //获得EXCEL模板路径 SheetFormatterContainer<DataRow> formatterContainers = new SheetFormatterContainer<DataRow>(); //实例化一个模板数据格式化容器 //实例化一个可重复表格格式化器，dt.Select()是将DataTable转换成DataRow[]，rpt_begin表示的模板表格开始位置参数名，rpt_end表示的模板表格结束位置参数名 RepeaterFormatterBuilder<DataRow> tableFormatterBuilder = new RepeaterFormatterBuilder<DataRow>(dt.Select(), "rpt_begin", "rpt_end"); tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{ {"sex",r=>r["Col2"]},//将模板表格中sex对应DataTable中的列Col2 {"km",r=>r["Col3"]},//将模板表格中km对应DataTable中的列Col3 {"score",r=>r["Col4"]},//将模板表格中score对应DataTable中的列Col {"result",r=>r["Col5"]}//将模板表格中result对应DataTable中的列Co5 }); PartFormatterBuilder<DataRow> partFormatterBuilder2 = new PartFormatterBuilder<DataRow>();//实例化一个可嵌套的局部元素格式化器 partFormatterBuilder2.AddFormatter("name", r => r["Col1"]);//将模板表格中name对应DataTable中的列Col1 tableFormatterBuilder.AppendFormatterBuilder(partFormatterBuilder2);//添加到可重复表格格式化器中，作为其子格式化器 CellFormatterBuilder<DataRow> cellFormatterBuilder = new CellFormatterBuilder<DataRow>();//实例化一个可嵌套的单元格格式化器 cellFormatterBuilder.AddFormatter("rptdate", r => DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期 tableFormatterBuilder.AppendFormatterBuilder(cellFormatterBuilder);//添加到可重复表格格式化器中，作为其子格式化器 formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效 string excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath, "multtable", formatterContainers); Assert.IsTrue(File.Exists(excelPath)); } 模板如下图示：（注意：该模板仅支持XLS格式文件，XLSX下存在问题）
结果如下图示：
以下是模拟数据来源所定义的方法（配合测试）：
private DataTable GetDataTable() { DataTable dt = new DataTable(); for (int i = 1; i <= 6; i++) { if (i == 4) { dt.Columns.Add("Col" + i.ToString(), typeof(double)); } else { dt.Columns.Add("Col" + i.ToString(), typeof(string)); } } for (int i = 1; i <= 10; i++) { dt.Rows.Add("Name" + i.ToString(), (i % 2) > 0 ? "男" : "女", "科目" + i.ToString(), i * new Random().Next(1, 5), "待定", Guid.NewGuid().ToString("N")); } return dt; } private List<Student> GetStudentList() { List<Student> studentList = new List<Student>(); for (int i = 1; i <= 10; i++) { studentList.Add(new Student { Name = "Name" + i.ToString(), Sex = (i % 2) > 0 ? "男" : "女", KM = "科目" + i.ToString(), Score = i * new Random().Next(1, 5), Result = "待定" }); } return studentList; } class Student { public string Name { get; set; } public string Sex { get; set; } public string KM { get; set; } public double Score { get; set; } public string Result { get; set; } } 导入方法测试：
/// <summary> /// 测试方法：测试将指定的EXCEL数据导入到DataTable /// </summary> [TestMethod] public void TestImportToDataTableFromExcel() { //null表示由用户选择EXCEL文件路径，data表示要导入的sheet名,0表示数据标题行 DataTable dt= ExcelUtility.Import.ToDataTable(null, "data", 0); Assert.AreNotEqual(0, dt.Rows.Count); } 数据源文件内容如下图示：
下面贴出该类库主要源代码：
ExcelUtility.Export类：
using ExcelReport; using ExcelUtility.Base; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Windows.Forms; namespace ExcelUtility { public sealed class Export { /// <summary> /// 由DataSet导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="filePath">导出路径，可选</param> /// <returns></returns> public static string ToExcel(DataSet sourceDs, string filePath = null) { if (string.IsNullOrEmpty(filePath)) { filePath = Common.GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; bool isCompatible = Common.GetIsCompatible(filePath); IWorkbook workbook = Common.CreateWorkbook(isCompatible); ICellStyle headerCellStyle = Common.GetCellStyle(workbook,true); ICellStyle cellStyle = Common.GetCellStyle(workbook); for (int i = 0; i < sourceDs.Tables.Count; i++) { DataTable table = sourceDs.Tables[i]; string sheetName = string.IsNullOrEmpty(table.TableName) ? "result" + i.ToString() : table.TableName; ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in table.Columns) { ICell headerCell = headerRow.CreateCell(column.Ordinal); headerCell.SetCellValue(column.ColumnName); headerCell.CellStyle = headerCellStyle; sheet.AutoSizeColumn(headerCell.ColumnIndex); } // handling value. int rowIndex = 1; foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) { ICell cell = dataRow.CreateCell(column.Ordinal); cell.SetCellValue((row[column] ?? "").ToString()); cell.CellStyle = cellStyle; Common.ReSizeColumnWidth(sheet, cell); } 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> /// <param name="colAliasNames">导出的列名重命名数组</param> /// <param name="sheetName">工作薄名称，可选</param> /// <param name="filePath">导出路径，可选</param> /// <returns></returns> public static string ToExcel(DataTable sourceTable, string[] colAliasNames, string sheetName = "result", string filePath = null) { if (sourceTable.Rows.Count <= 0) return null; if (string.IsNullOrEmpty(filePath)) { filePath = Common.GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; if (colAliasNames == null || sourceTable.Columns.Count != colAliasNames.Length) { throw new ArgumentException("列名重命名数组与DataTable列集合不匹配。", "colAliasNames"); } bool isCompatible = Common.GetIsCompatible(filePath); IWorkbook workbook = Common.CreateWorkbook(isCompatible); ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true); ICellStyle cellStyle = Common.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(colAliasNames[column.Ordinal]); headerCell.CellStyle = headerCellStyle; sheet.AutoSizeColumn(headerCell.ColumnIndex); } // handling value. int rowIndex = 1; foreach (DataRow row in sourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { ICell cell = dataRow.CreateCell(column.Ordinal); cell.SetCellValue((row[column] ?? "").ToString()); cell.CellStyle = cellStyle; Common.ReSizeColumnWidth(sheet, cell); } 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">要导出的DataGridView对象</param> /// <param name="sheetName">工作薄名称，可选</param> /// <param name="filePath">导出路径，可选</param> /// <returns></returns> public static string ToExcel(DataGridView grid, string sheetName = "result", string filePath = null) { if (grid.Rows.Count <= 0) return null; if (string.IsNullOrEmpty(filePath)) { filePath = Common.GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; bool isCompatible = Common.GetIsCompatible(filePath); IWorkbook workbook = Common.CreateWorkbook(isCompatible); ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true); ICellStyle cellStyle = Common.GetCellStyle(workbook); ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < grid.Columns.Count; i++) { ICell headerCell = headerRow.CreateCell(i); headerCell.SetCellValue(grid.Columns[i].HeaderText); headerCell.CellStyle = headerCellStyle; sheet.AutoSizeColumn(headerCell.ColumnIndex); } int rowIndex = 1; foreach (DataGridViewRow row in grid.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); for (int n = 0; n < grid.Columns.Count; n++) { ICell cell = dataRow.CreateCell(n); cell.SetCellValue((row.Cells[n].Value ?? "").ToString()); cell.CellStyle = cellStyle; Common.ReSizeColumnWidth(sheet, cell); } rowIndex++; } FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook.Write(fs); fs.Dispose(); sheet = null; headerRow = null; workbook = null; return filePath; } /// <summary> /// 由DataTable导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="sheetName">工作薄名称，可选</param> /// <param name="filePath">导出路径，可选</param> /// <param name="colNames">需要导出的列名,可选</param> /// <param name="colAliasNames">导出的列名重命名，可选</param> /// <returns></returns> public static string ToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null, string[] colNames = null, IDictionary<string, string> colAliasNames = null) { if (sourceTable.Rows.Count <= 0) return null; if (string.IsNullOrEmpty(filePath)) { filePath = Common.GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; bool isCompatible = Common.GetIsCompatible(filePath); IWorkbook workbook = Common.CreateWorkbook(isCompatible); ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true); ICellStyle cellStyle = Common.GetCellStyle(workbook); ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); if (colNames == null || colNames.Length <= 0) { colNames = sourceTable.Columns.Cast<DataColumn>().OrderBy(c => c.Ordinal).Select(c => c.ColumnName).ToArray(); } // handling header. for (int i = 0; i < colNames.Length; i++) { ICell headerCell = headerRow.CreateCell(i); if (colAliasNames != null && colAliasNames.ContainsKey(colNames[i])) { headerCell.SetCellValue(colAliasNames[colNames[i]]); } else { headerCell.SetCellValue(colNames[i]); } headerCell.CellStyle = headerCellStyle; sheet.AutoSizeColumn(headerCell.ColumnIndex); } // handling value. int rowIndex = 1; foreach (DataRow row in sourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); for (int i = 0; i < colNames.Length; i++) { ICell cell = dataRow.CreateCell(i); cell.SetCellValue((row[colNames[i]] ?? "").ToString()); cell.CellStyle = cellStyle; Common.ReSizeColumnWidth(sheet, cell); } rowIndex++; } FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook.Write(fs); fs.Dispose(); sheet = null; headerRow = null; workbook = null; return filePath; } /// <summary> ///由SheetFormatterContainer导出基于EXCEL模板的文件 /// </summary> /// <param name="templatePath">模板路径</param> /// <param name="sheetName">模板中使用的工作薄名称</param> /// <param name="formatterContainer">模板数据格式化容器</param> /// <param name="filePath">导出路径，可选</param> /// <returns></returns> public static string ToExcelWithTemplate<T>(string templatePath, string sheetName, SheetFormatterContainer<T> formatterContainer, string filePath = null) { if (!File.Exists(templatePath)) { throw new FileNotFoundException(templatePath + "文件不存在！"); } if (string.IsNullOrEmpty(filePath)) { filePath = Common.GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; string templateConfigFilePath = Common.GetTemplateConfigFilePath(templatePath, false); var workbookParameterContainer = new WorkbookParameterContainer(); workbookParameterContainer.Load(templateConfigFilePath); SheetParameterContainer sheetParameterContainer = workbookParameterContainer[sheetName]; ExportHelper.ExportToLocal(templatePath, filePath, new SheetFormatter(sheetName, formatterContainer.GetFormatters(sheetParameterContainer))); return filePath; } } } ExcelUtility.Import类：
using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using ExcelUtility.Base; namespace ExcelUtility { public sealed class Import { /// <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 ToDataTable(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible) { IWorkbook workbook = Common.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 = Common.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 ToDataTable(string excelFilePath, string sheetName, int headerRowIndex) { if (string.IsNullOrEmpty(excelFilePath)) { excelFilePath = Common.GetOpenFilePath(); } if (string.IsNullOrEmpty(excelFilePath)) { return null; } using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { bool isCompatible = Common.GetIsCompatible(excelFilePath); return ToDataTable(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 ToDataSet(Stream excelFileStream, int headerRowIndex, bool isCompatible) { DataSet ds = new DataSet(); IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream); for (int i = 0; i < workbook.NumberOfSheets; i++) { ISheet sheet = workbook.GetSheetAt(i); DataTable table = Common.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 ToDataSet(string excelFilePath, int headerRowIndex) { if (string.IsNullOrEmpty(excelFilePath)) { excelFilePath = Common.GetOpenFilePath(); } if (string.IsNullOrEmpty(excelFilePath)) { return null; } using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { bool isCompatible = Common.GetIsCompatible(excelFilePath); return ToDataSet(stream, headerRowIndex, isCompatible); } } } } Common类根据单元格内容重新设置列宽ReSizeColumnWidth
/// <summary> /// 根据单元格内容重新设置列宽 /// </summary> /// <param name="sheet"></param> /// <param name="cell"></param> public static void ReSizeColumnWidth(ISheet sheet, ICell cell) { int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 5) * 256; const int maxLength = 255 * 256; if (cellLength > maxLength) { cellLength = maxLength; } int colWidth = sheet.GetColumnWidth(cell.ColumnIndex); if (colWidth < cellLength) { sheet.SetColumnWidth(cell.ColumnIndex, cellLength); } } 注意这个方法中，列宽自动设置最大宽度为255个字符宽度。
/// <summary> /// 创建表格头单元格 /// </summary> /// <param name="sheet"></param> /// <returns></returns> public static ICellStyle GetCellStyle(IWorkbook workbook, bool isHeaderRow = false) { ICellStyle style = workbook.CreateCellStyle(); if (isHeaderRow) { style.FillPattern = FillPattern.SolidForeground; style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; IFont f = workbook.CreateFont(); f.Boldweight = (short)FontBoldWeight.Bold; style.SetFont(f); } style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; return style; } 发文时，部份方法代码已经更新了，所以实际效果以GIT项目中的为准。
该类库源码已分享到该路径中： http://git.oschina.net/zuowj/ExcelUtility GIT Repository路径：git@git.oschina.net:zuowj/ExcelUtility.git
</div> 本文由用户 jopen 自行上传分享，仅供网友学习交流。所有权归原作者，若您的权利被侵害，请联系管理员。
转载本站原创文章，请注明出处，并保留原始链接、图片水印。
本站是一个以用户分享为主的开源技术平台，欢迎各类分享！