开发者社区> 技术小牛人> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

2017-11-22 967

简介：
+关注继续查看

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


本文转自 梦在旅途 博客园博客，原文链接：http://www.cnblogs.com/zuowj/p/5113812.html  ，如需转载请自行联系原作者

版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

测试技术 容器
excelreport使用 excel http请求 ireport组件 finereport系统 ireport模板
开发者社区 > 云原生 > 文章
作者高分内容
更多
子域名爆破&C段查询&调用Bing查询同IP网站 4170 Mifare Classic Tool(MCT)汉化版 16295 Linux中查看socket状态 2932 DHCP服务详解及配置 3120 Linux学习 Unit 7 1652
相关文章
owenzhang
Hyperf结合PhpOffice/PhpSpreadsheet实现Excel&CSV文件导出导入
Hyperf结合PhpOffice/PhpSpreadsheet实现Excel&CSV文件导出导入。PhpSpreadsheet是一个用纯PHP编写的库，它提供了一组类，允许您读取和写入各种电子表格文
0 0
技术小牛人
分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility （续篇）
1087 0
技术小牛人
分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility （续2篇-模板导出综合示例）
1908 0
技术小牛人
分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility （续3篇-导出时动态生成多Sheet EXCEL）
1070 0
老朱教授
c# winform DataSet 导出 Excel
568 0
技术小甜
导入excel
568 0
zting科技
ExcelReport第一篇：使用ExcelReport导出Excel
551 0
zting科技
基于NPOI的报表引擎——ExcelReport
620 0
落雨_
Ireport 报表导出 Poi + ireport 导出pdf, word ,excel ,htm
Ireport 报表导出 Poi + ireport 导出pdf, doc ,excel ,html 格式 下面是报表导出工具类reportExportUtils 需要导出以上格式的报表 只需要调用本工具类的exportmain 方法即可。
1182 0
清山
将HtmlTable内容导出到Excel，使用NPOI组件
1716 0
+关注
技术小牛人
文章
问答
作者高分内容
更多
子域名爆破&C段查询&调用Bing查询同IP网站 4170 Mifare Classic Tool(MCT)汉化版 16295 Linux中查看socket状态 2932 DHCP服务详解及配置 3120 Linux学习 Unit 7 1652
文章排行榜
最热
最新
1 承上启下：基于全域漏斗分析的主搜深度统一粗排 91532
2 复杂链表的复制 90642
3 【Transform3D】转换详解（看完就会） 88190
4 【flexbox弹性布局学习指南】CSS热门布局方案 68854
5 如何构建标签画像工程体系及实现方案 63157
6 DCM：中间件家族迎来新成员 61169
7 系统的混乱并业务本身之复杂，我们并不擅长处理『简单』 48276
8 超越10倍开发者 48152
9 ⚡Three.js-在场景中导入3D动画模型 46113
10 Request&Response 39098
11 《JavaScript高级程序设计》__ 作用域&内存 37896
12 影响成长的关键思考 34911
13 K8S学习-当我们部署应用的时候都发生了什么？（第一篇） 30696
14 【从零开始学习深度学习】12. 什么是模型的训练误差？基于三阶多项式的欠拟合与过拟合训练过程演示 27899
15 阿里云物联网平台设备分发实战 33505
16 记录下考Apsara Clouder云计算专项技能认证：云服务器ECS入门过程 16330
17 ChatGPT 常见错误原因及解决方案：报错、回答不完整等 22211
18 Python3，9行代码，对比两个Excel数据差异，并把差异结果重新保存。 8592
19 深聊性能测试，从入门到放弃之： Windows系统性能监控(一) 性能监视器介绍及使用。 9582
20 学生速看！免费领取阿里云云服务器的详细攻略 3580
1 云时代，好用的数据迁移方案推荐 126
2 浅谈智能语音交互 116
3 【分享】宜搭集成自动化，Groovy节点一般用法，解析JSON数据 81
4 算法提升（二） 异或法 52
5 一封来自开发者社区的邀请函 135
6 【分享】在宜搭JS面板写了死循环如何解决 106
7 如何选择数据集成方式-离线&实时 119
8 网易游戏实时 HTAP 计费风控平台建设 199
9 Apache Tomcat 存在 JsonErrorReportValve 注入漏洞（CVE-2022-45143） 56
10 Grid网格布局学习笔记 53
11 【数据设计与实现】第5章：同步与互斥 79
12 基于钉钉宜搭开发的《连锁门店管理系统》功能演示 82
13 Python库函数pandas读取excel数据 97
14 谈谈《流浪地球2》中如何正确的“重启互联网” 2175
15 《阿里巴巴Java开发手册（终极版）》电子版地址 52
16 2023阿里云学生服务器免费申请流程 275
17 阿里云服务器ECS性能优势详解_选择云服务器的理由 263
18 阿里云服务器ECS介绍_云主机_服务器托管_弹性计算 171
19 阿里云服务器ECS云主机详细介绍说明 78
20 阿里云服务器ECS详解（性能/安全/速度） 1541
推荐文章
2022藏经阁年度电子书榜单出炉！ 参与应用引擎 SAE评测，赢取CHERRY机械键盘等好礼 “第益课”大学生技术公益 乘风者计划邀您入驻社区，精彩权益即刻享
相关电子书
更多
Excelize 基础教程
立即下载
低代码开发师（初级）实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载