分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility

类库项目文件结构如下图示：

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