基于NPOI+ExcelReport实现的导入与导出EXCEL类库:ExcelUtility

jopen 5年前

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>

来自: http://www.cnblogs.com/zuowj/p/5113812.html