分享我基于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.具体使用方法介绍(示例代码,全部为测试方法):

导出方法测试:

1
2
3
4
5
6
7
8
9
10
/// <summary>
        /// 测试方法:测试将DataTable导出到EXCEL,无模板
        /// </summary>
        [TestMethod]
        public void TestExportToExcelByDataTable()
        {
            DataTable dt = GetDataTable();
            string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果");
            Assert.IsTrue(File.Exists(excelPath));
        }

结果如下图示:

 

1
2
3
4
5
6
7
8
9
10
11
/// <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));
}

结果如下图示:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/// <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));
      }

结果如下图示:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/// <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));
      }

结果如下图示:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/// <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));
}

模板如下图示:

结果如下图示:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/// <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));
 
}

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

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/// <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下存在问题)

结果如下图示:

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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; }
}

导入方法测试:

1
2
3
4
5
6
7
8
9
10
/// <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类:

  

ExcelUtility.Import类:

Common类根据单元格内容重新设置列宽ReSizeColumnWidth

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/// <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个字符宽度。

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/// <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

posted @   梦在旅途  阅读(15038)  评论(38编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示