使用NPOI生成Excel级联列表

简介: 目录 1    概要    1 2    磨刀不误砍柴工——先学会Excel中的操作    2 3    利用NPOI生成导入模板    7 3.1    设置workbook&sheet    8 3.2    生成数据Sheet,并按规则列好    8 3.3    添加名称,并指定数据范围(绑定数据源)    9 3.4    写入大类和小类的数据验证    11   概要 博客使用Word发博,发布后,排版会出现很多问题,敬请谅解。

目录

1    概要    1

2    磨刀不误砍柴工——先学会Excel中的操作    2

3    利用NPOI生成导入模板    7

3.1    设置workbook&sheet    8

3.2    生成数据Sheet,并按规则列好    8

3.3    添加名称,并指定数据范围(绑定数据源)    9

3.4    写入大类和小类的数据验证    11

 

  1. 概要

    博客使用Word发博,发布后,排版会出现很多问题,敬请谅解。另外Word发博代码格式显示凌乱,因此相关代码均使用图片替代。可加群(.NET 1群:85318032)获取原始文档。

很久没发博客了,因为实在是太忙了(请允许我找个借口)。最近沉淀了很多内容,当然很多都差不多忘记了,不过我还是会在有时间的前提下逐步一一道来吧。最近做了一个批量下单的模板导出,因为订单中有商品大类和小类的概念,而且类型非常多,为了方便用户选择以及确保数据的合法性,因此级联选择势在必行。不过,在此之前,本人就算是在Excel中操作都不会设置下拉,跟别说级联下拉了,并且关于使用代码生成级联下拉这块,网上并没有相关的可以值得借鉴的内容,但是无论如何,Excel小白还是要挑战挑战的。折腾了一下午,总算搞定,而且顺便学会了Excel中的序列和级联。还是挺有成就感的。鉴于网上这块有价值的内容不多,于是在此分享此内容以及相关核心代码。

官方博客:http://www.cnblogs.com/codelove/

相关开源库地址:https://github.com/xin-lai

交流QQ群(.NET 1群):85318032

交流QQ群(Magicodes开源库交流群):346487194

Nuget包地址:https://www.nuget.org/packages?q=magicodes

 

  1. 磨刀不误砍柴工——先学会Excel中的操作

首先,我们可以参考这个教程(来自百度经验):

http://jingyan.baidu.com/article/5553fa82035ce565a23934ba.html

这里有一点需要特别注意的,因为开发人员用的Excel版本都比较高,比如我的是2016,估计一般也是2013吧,特别坑爹的是,网上大部分教程是2010或者以下版本的,而在2013或以上版本微软将某个菜单的文字改了,如下图所示的地方:

这个有效性菜单你会发现在高版本无法找到,我找了半天,终于找到了:

就是这个图标!!!现在叫"数据验证"!!!

通过以上教程,我们可以学会配置了Excel级联列表:

数据源如下:

名称管理如下:

级联效果如下:

搞定了Excel,我们学到了以下几个概念:

  1. 通过名称管理器,我们可以定义序列,或者叫列表和数据源吧
  2. 通过数据验证,我们可以设置当单元格所绑定的序列

  3. 通过INDIRECT函数,我们可以实现下拉级联效果:

    其实这个级联的实现的思路很有意思,通过INDIRECT获取到关联单元格的值,然后这个值就是关联列表的序列名称。

    搞懂了以上理论,然后我们再开车。没有理论,很多时候就是瞎折腾!所以老司机开车不能瞎开,得有理论。

    1. 利用NPOI生成导入模板

其实用NPOI还是用Aspose.Cells,这个都没关系。毕竟我们掌握了理论,我们有理由相信,这两位都是好同志。在有RMB的前提下,我们愿意支持商业的,没RMB,开源的也能玩得飞起。好了,至于为什么选择NPOI,很简单,因为我们没钱,而且不喜欢盗版。

现在我来说说思路(思路是高于开发的,很多时候如果做一个东西没有思路,那就很容易"作死",在开发过程中要有意识的培养自己的思路,一方面是思路的形成可以在很多场景迁移借鉴,另一方面是既保障灵活性、扩展性和严谨性的前提下,又少走弯路。最后,思路是自己的,代码是靠抄的。):

  1. 设置workbook&sheet

NOPI操作Workbook和Sheet的代码网上很多,我这里就不过多搬运了,核心代码为:

HSSFWorkbook workbook = new HSSFWorkbook();//创建workbook

ISheet sheet = workbook.CreateSheet("sheet1");//创建sheet

IRow row = sheet.CreateRow(0);//添加行

row.CreateCell(0).SetCellValue("Test");//单元格写值

  1. 生成数据Sheet,并按规则列好

根据上面的代码,我们根据我们的业务逻辑很容易生成以下内容:

注意这个逻辑!一定要正确,而且对应上!

  1. 添加名称,并指定数据范围(绑定数据源)

核心代码为:

IName range = workbook.CreateName();//创建名称

range.NameName = bigCategory.CategoryName;//设置名称

var colName = GetExcelColumnName(colIndex);//根据序号获取列名,具体代码见下文

range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",

sheetName,

smallList.Count.ToString(),

2,

colName);

//设置引用位置

//参数1为引用的Sheet名称

//参数2为行数(数据行数)

//参数3为起始行数(从第二行开始,忽略列头,列头是给我们看的)

//参数4为列名(比如A、B、AA、AB这种)

 

获取列名的代码为:

/// <summary>

/// 获取Excel列名

/// </summary>

/// <param name="columnNumber">列的序号</param>

/// <returns></returns>

private string GetExcelColumnName(int columnNumber)

{

int dividend = columnNumber;

string columnName = String.Empty;

int modulo;

 

while (dividend > 0)

{

modulo = (dividend - 1) % 26;

columnName = Convert.ToChar(65 + modulo).ToString() + columnName;

dividend = (int)((dividend - modulo) / 26);

}

 

return columnName;

}

通过以上代码,就可以定义Excel中的名称了,对应如图所示:

  1. 写入大类和小类的数据验证

基于我们的理解,然后结合NPOI的API,我们很快就可以写出一下代码了:

//定义Cell范围,参数1:起始行数,参数2:结束行数,参数3:起始列数,参数4:结束列数

CellRangeAddressList regions = new CellRangeAddressList(1, 65535, lastBigIndex, lastBigIndex);

//绑定序列地址

DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("产品大类");

//定义数据验证

HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

//添加数据验证

sheet.AddValidationData(dataValidate);

如上面代码所示,这里是设置大类的数据验证。如图:

效果:

设置级联的数据验证:

var smallColIndex = i - 1;

//与大类关联

var colName = GetExcelColumnName(lastBigIndex + 1);

//这里只设置了该列的500行,可以按自己的情况来写入

for (int j = 1; j <= 500; j++)

{

//定义Cell范围,参数1:起始行数,参数2:结束行数,参数3:起始列数,参数4:结束列数

//这里的范围是单个单元格,因为我们的公式用到了具体的单元格地址

CellRangeAddressList regions = new CellRangeAddressList(j, j, smallColIndex, smallColIndex);

//使用INDIRECT函数,这里指定了具体地址

DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT(${0}${1})", colName, j + 1));

HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

sheet.AddValidationData(dataValidate);

}

效果如图:

好了,整个导出的主要逻辑就是这些。理解了思想,再结合官方API就可以很快搞定了。

整个分享就到这里了,如此看来,Excel还是大有可为的,不通过宏就可以做很多事情。而且Excel还可以直接连接外部数据源,包括OData服务等,有兴趣的小伙伴可以研究研究。

本次开车到此结束。

相关实践学习
基于Hologres轻松玩转一站式实时仓库
本场景介绍如何利用阿里云MaxCompute、实时计算Flink和交互式分析服务Hologres开发离线、实时数据融合分析的数据大屏应用。
阿里云实时数仓实战 - 项目介绍及架构设计
课程简介 1)学习搭建一个数据仓库的过程,理解数据在整个数仓架构的从采集、存储、计算、输出、展示的整个业务流程。 2)整个数仓体系完全搭建在阿里云架构上,理解并学会运用各个服务组件,了解各个组件之间如何配合联动。 3&nbsp;)前置知识要求 &nbsp; 课程大纲 第一章&nbsp;了解数据仓库概念 初步了解数据仓库是干什么的 第二章&nbsp;按照企业开发的标准去搭建一个数据仓库 数据仓库的需求是什么 架构 怎么选型怎么购买服务器 第三章&nbsp;数据生成模块 用户形成数据的一个准备 按照企业的标准,准备了十一张用户行为表 方便使用 第四章&nbsp;采集模块的搭建 购买阿里云服务器 安装 JDK 安装 Flume 第五章&nbsp;用户行为数据仓库 严格按照企业的标准开发 第六章&nbsp;搭建业务数仓理论基础和对表的分类同步 第七章&nbsp;业务数仓的搭建&nbsp; 业务行为数仓效果图&nbsp;&nbsp;
目录
相关文章
|
11月前
|
前端开发 C#
C# 基于NPOI+Office COM组件 实现20行代码在线预览文档(word,excel,pdf,txt,png)
C# 基于NPOI+Office COM组件 实现20行代码在线预览文档(word,excel,pdf,txt,png)
|
29天前
|
存储 数据采集 数据可视化
Python列表到Excel表格第一列的转换技术详解
Python列表到Excel表格第一列的转换技术详解
19 0
|
29天前
|
C#
C#NPOI操作Excel详解
C#NPOI操作Excel详解
74 0
|
29天前
|
数据库
关于用NPOI导入Excel
关于用NPOI导入Excel
|
10月前
|
C#
45【软件技术基础】C#调用NPOI插件对EXCEL进行处理
NPOI插件进行EXCEL表格处理,不依赖第三方软件,功能强大,使用简便。 C#调用NPOI插件进行EXCEL单个表和文件夹中批量表的处理。
154 1
|
29天前
|
JavaScript
vue表格列表导出excel
vue表格列表导出excel
26 0
|
存储 JavaScript easyexcel
easyexcel 2.2.6 新版本以列表的形式读取 Excel
easyexcel 2.2.6 新版本以列表的形式读取 Excel
226 0
|
7月前
|
C#
.net NPOI Excel导入:时间格式2022/5/26导入变成26-5月-2022
​ 1、问题由来 在做一个导入的需求时,测试导入模板,无论导入模板里的日期设置成何种日期格式到代码中都会提示有不正确的格式化数据,加断点调试发现,导入的日期如:Excel表格中是2022/5/26,断点看到的却是26-5月-2022。 2、解决方案 网上查询了几种解决方案,有导入的数据列格式判断转换,日期格式强转等等,都没什么效果,最后解决的方法如下: // NPOI导入日期格式处理 string mytime = dateStr.Trim(); // dateStr为Excel导入的日期值 IFormatProvider culture = new CultureInfo("zh-CN"
47 0
|
开发框架 .NET
NPOI在.net中的操作Excel
NPOI在.net中的操作Excel
93 2
|
24天前
|
前端开发 Java
基于Java爬取微博数据(二) 正文长文本+导出数据Excel
【5月更文挑战第12天】基于Java爬取微博数据,正文长文本+导出数据Excel