首页 > 代码库 > 使用NPOI生成Excel级联列表
使用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发博，发布后，排版会出现很多问题，敬请谅解。另外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
磨刀不误砍柴工——先学会Excel中的操作
首先，我们可以参考这个教程（来自百度经验）：
http://jingyan.baidu.com/article/5553fa82035ce565a23934ba.html
这里有一点需要特别注意的，因为开发人员用的Excel版本都比较高，比如我的是2016，估计一般也是2013吧，特别坑爹的是，网上大部分教程是2010或者以下版本的，而在2013或以上版本微软将某个菜单的文字改了，如下图所示的地方：
这个有效性菜单你会发现在高版本无法找到，我找了半天，终于找到了：
就是这个图标！！！现在叫"数据验证"！！！
通过以上教程，我们可以学会配置了Excel级联列表：
数据源如下：
名称管理如下：
级联效果如下：
搞定了Excel，我们学到了以下几个概念：
- 通过名称管理器，我们可以定义序列，或者叫列表和数据源吧
- 通过数据验证，我们可以设置当单元格所绑定的序列
- 通过INDIRECT函数，我们可以实现下拉级联效果：
其实这个级联的实现的思路很有意思，通过INDIRECT获取到关联单元格的值，然后这个值就是关联列表的序列名称。
搞懂了以上理论，然后我们再开车。没有理论，很多时候就是瞎折腾！所以老司机开车不能瞎开，得有理论。
利用NPOI生成导入模板
其实用NPOI还是用Aspose.Cells，这个都没关系。毕竟我们掌握了理论，我们有理由相信，这两位都是好同志。在有RMB的前提下，我们愿意支持商业的，没RMB，开源的也能玩得飞起。好了，至于为什么选择NPOI，很简单，因为我们没钱，而且不喜欢盗版。
现在我来说说思路（思路是高于开发的，很多时候如果做一个东西没有思路，那就很容易"作死"，在开发过程中要有意识的培养自己的思路，一方面是思路的形成可以在很多场景迁移借鉴，另一方面是既保障灵活性、扩展性和严谨性的前提下，又少走弯路。最后，思路是自己的，代码是靠抄的。）：
设置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");//单元格写值
生成数据Sheet，并按规则列好
根据上面的代码，我们根据我们的业务逻辑很容易生成以下内容：
注意这个逻辑！一定要正确，而且对应上！
添加名称，并指定数据范围（绑定数据源）
核心代码为：
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中的名称了，对应如图所示：
写入大类和小类的数据验证
基于我们的理解，然后结合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服务等，有兴趣的小伙伴可以研究研究。
本次开车到此结束。
使用NPOI生成Excel级联列表