星空网 > 软件开发 > ASP.net

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

1. ExcelUtility功能:
   1.将数据导出到EXCEL(支持XLS,XLSX,支持多种类型模板,支持列宽自适应)
     类名:ExcelUtility. Export


   2.将EXCEL数据导入到数据对象中(DataTable、Dataset,支持XLS,XLSX)
     类名:ExcelUtility. Import

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

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


 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));    }

结果如下图示:

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

 

    /// <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));    }

结果如下图示:

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

 

 /// <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));    }

结果如下图示:

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

 

 /// <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));    }

结果如下图示:

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

 

    /// <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));    }

模板如下图示:

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

结果如下图示:

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

 

    /// <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));    }

结果如下图示:(模板与上面相同)

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

 

    /// <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下存在问题)

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

结果如下图示:

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

以下是模拟数据来源所定义的方法(配合测试):

    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);    }

数据源文件内容如下图示:

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

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

 

下面贴出该类库主要源代码:

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




原标题:分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库:ExcelUtility

关键词:excel

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

赴美上市:https://www.goluckyvip.com/tag/12202.html
英国脱欧期限:https://www.goluckyvip.com/tag/12203.html
ESG网红营销:https://www.goluckyvip.com/tag/12204.html
测评真实性:https://www.goluckyvip.com/tag/12205.html
安全测评技巧:https://www.goluckyvip.com/tag/12206.html
真实测评资源:https://www.goluckyvip.com/tag/12207.html
长治婚庆女司仪和主持人:https://www.vstour.cn/a/366176.html
北京丰台区水上乐园哪家好玩?:https://www.vstour.cn/a/366177.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流