1 单元格下拉框
在开发中我们会遇到为单元格设置下拉框。一般可以编写如下：
|
1
2
3
4
5
|
var cellRanges = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(stringArray);HSSFDataValidation validate = new HSSFDataValidation(cellRanges, constraint);validate.ShowProptBox = true;sheet.AddValidationData(validate); |
但是，如果字符串数组存在长度限制，如NPOI导出Excel时下拉列表值超过255的问题（String literals in formulas can't be bigger than 255 characters ASCII）
解决方案
通过额外新建Excel的Sheet页保存下拉内容，并转换为下拉框数据。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
ISheet hidden = workbook.CreateSheet(columnName);IRow row = null;ICell cell = null;for (int i = 0; i < stringArray.Length; i++){ row = hidden.CreateRow(i); cell = row.CreateCell(0); cell.SetCellValue(stringArray[i]);}IName namedCell = workbook.CreateName();namedCell.NameName = column.ColumnName;// 注意下面的语法是Excel的公式，建议不要落掉一个`$`，很多文档都要所遗漏。namedCell.RefersToFormula = $"{columnName}!$A$1:$A${stringArray.Length}";DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(columnName);CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);HSSFDataValidation validate = new HSSFDataValidation(addressList, constraint);sheet.AddValidationData(dataValidate); |
2 添加批注
代码如下：
|
1
2
3
4
5
6
7
|
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();// 这个代码参数不要写成固定的，它用来定位你的批注的位置和大小。HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(new HSSFFClientAnchor(0, 0, 255,255, col1, 0, col1 + 2, 5));comment.Author = "Dison";comment.String = new HSSFRichTextString($"内容");cell.CellComment = comment; |
3 读取数据
如何解析公式的结果
代码如下：
|
1
2
3
4
|
if (row.GetCell(i).CellType.Equals(CellType.Formula)){ var data = row.GetCell(i).RichStringCellValue;} |
如果希望读取公式也可以如下：
|
1
|
var data = row.GetCell(i).ToString(); |
但是需要注意结果没有等号“=”, 这里我是演示，所以写了局部变量。
日期格式 MM-dd-yy 转 yyyy-MM-dd
由于Excel的数字和日期都是Numeric格式，；处理如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
if (row.GetCell(i).CellType.Equals(CellType.Numeric)){ ICell cell = row.GetCell(i); short format = cell.CellStyle.DataFormat; if (format != 0) { var data = cell.DateCellValue.ToString("yyyy-MM-dd"); } else { var data = cell.NumericCellValue; }} |
结语
NPOI还是一个相对成熟的Excel操作库。网上的资料确实写的比较潦草。但是作为程序员，必须学会耐心，尤其是debug。
常见问题解决
NPOI 导出添加批注功能
|
1
2
3
4
5
6
|
//添加批注HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();HSSFComment comment12 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 2, 3));//批注显示定位comment12.String = new HSSFRichTextString("请填写完整部门名称！");HSSFCell cell12 = (HSSFCell)headerRow.CreateCell(12);//将批注给予单元格cell12.CellComment = comment12; |
但是有个比较重要的地方需要澄清下，就是批注的位置和大小，这是由HSSFClientAnchor八个参数控制的，千万不能简单的写HSSFClientAnchor(0, 0, 0, 0, 1, 2, 2, 3)，
因为每个单元格的批注的位置都是不一样的（编辑批注时的位置）。那么怎么办呢，当然是需要了解参数的意思：
简单说来：
关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下：
- dx1：起始单元格的x偏移量；
- dy1：起始单元格的y偏移量；
- dx2：终止单元格的x偏移量；
- dy2：终止单元格的y偏移量；
- col1：起始单元格列序号；
- row1：起始单元格行序号；
- col2：终止单元格列序号；
- row2：终止单元格行序号；
其实主要是前四个是偏移量，后四个关系到批注的位置和大小。
以我自己做的一个例子来说：
|
1
|
HSSFComment comment1 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(255, 125, 1023, 150, colindex + 1, rowIndex - 1, colindex + 2, rowIndex + 4))； |
- rowIndex 是当前单元格是第几行，colindex 是当前单元格是第几列。通过行列是可以定位到当前的单元格的。
- colindex + 1 对应上面的参数是col1 表示批注起始的位置是当前单元格的列数的下一列，即原来是第5列，则批注起在第6列。
- rowIndex - 1 对应上面的参数是row1 表示皮质起始的位置是当前单元格行数的上一行，即原来是第2行，则批注起在第1行。
- colindex + 2, rowIndex + 4 这两个参数则是单元格终止的位置 +2 +4 则是决定了批注的大小，道理同colindex + 1，rowIndex - 1 。
但是NPOI导出有个坑 就是批注大小会随着所在位置的单元格大小变动 这个影响不大 如果想解决这个问题 只能换导出方法了。。。
千万别按照网上人家写的(0, 0, 0, 0, 1, 2, 2, 3)，这会坑死的，批注位置一直不变 任何单元格的批注都在同一个位置，坑死。
POI导出Excel时下拉列表值超过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
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
//创建Excel工作薄对象Workbook workbook = new HSSFWorkbook();//生成一个表格 设置：页签Sheet sheet = workbook.createSheet("sheet1"); //去数据库中查询我们想要的数据List<Product> productList = Ebean.getServer(GlobalDBControl.getDB()) .createQuery(Product.class, "find product where 1 = 1 and status = 0 and producttype is not null ") .findList();//创建一个数组 用来存放 我们取出来的数据String[] productNameArray = new String[productList.size()];//遍历每个peoduct对象，来获取productName属性并添加到数组中for (int i = 0; i < productList.size(); i++){ Product product = productList.get(i);productNameArray[i] = product.getTitle(); }//将下拉框数据放到新的sheet里，然后excle通过新的sheet数据加载下拉框数据Sheet hidden = workbook.createSheet("hidden");//创建单元格对象 Cell cell = null;//遍历我们上面的数组，将数据取出来放到新sheet的单元格中for (int i = 0, length = productNameArray.length; i < length; i++){ //取出数组中的每个元素String name = productNameArray[i];//根据i创建相应的行对象（说明我们将会把每个元素单独放一行）Row row = hidden.createRow(i);//创建每一行中的第一个单元格cell = row.createCell(0);//然后将数组中的元素赋值给这个单元格cell.setCellValue(name); } // 创建名称，可被其他单元格引用Name namedCell = workbook.createName(); namedCell.setNameName("hidden");// 设置名称引用的公式namedCell.setRefersToFormula("hidden!$A$1:$A$" + productNameArray.length);//加载数据,将名称为hidden的sheet中的数据转换为List形式DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");// 设置第一列的3-65534行为下拉列表// (3, 65534, 0, 0) ====> (起始行,结束行,起始列,结束列)CellRangeAddressList regions = new CellRangeAddressList(3, 65534, 0, 0);// 将设置下拉选的位置和数据的对应关系 绑定到一起DataValidation dataValidation = new HSSFDataValidation(regions, constraint);//将第二个sheet设置为隐藏 workbook.setSheetHidden(1, true);//将数据赋给下拉列表 sheet.addValidationData(dataValidation);//最后将文件导出就可以了，后面的代码就不写了，我只写一些这个问题相关的代码/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 如果出现多列情况，可复用下面方法private void ExcelTo255(Workbook workbook,String sheetName,int sheetNameIndex,String[] sheetData,int firstRow,int lastRow,int firstCol,int lastCol){ //将下拉框数据放到新的sheet里，然后excle通过新的sheet数据加载下拉框数据 Sheet hidden = workbook.createSheet(sheetName); //创建单元格对象 Cell cell =null; //遍历我们上面的数组，将数据取出来放到新sheet的单元格中 for (int i = 0, length = sheetData.length; i < length; i++){ //取出数组中的每个元素 String name = sheetData[i]; //根据i创建相应的行对象（说明我们将会把每个元素单独放一行） Row row = hidden.createRow(i); //创建每一行中的第一个单元格 cell = row.createCell(0); //然后将数组中的元素赋值给这个单元格 cell.setCellValue(name); } // 创建名称，可被其他单元格引用 Name namedCell = workbook.createName(); namedCell.setNameName(sheetName); // 设置名称引用的公式 namedCell.setRefersToFormula(sheetName+"!$A$1:$A$" + sheetData.length); //加载数据,将名称为hidden的sheet中的数据转换为List形式 DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName); // 设置第一列的3-65534行为下拉列表 // (3, 65534, 2, 2) ====> (起始行,结束行,起始列,结束列) CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); // 将设置下拉选的位置和数据的对应关系 绑定到一起 DataValidation dataValidation = new HSSFDataValidation(regions, constraint); //将第二个sheet设置为隐藏 workbook.setSheetHidden(sheetNameIndex, true); //将数据赋给下拉列表 workbook.getSheetAt(0).addValidationData(dataValidation);} |
日期格式导入混乱
原因
大概是NPOI导入时会大概判断一下Excel文档里面的单元格是什么格式的内容，
有Blank,Boolean,Numeric,String,Error,Formula 等几种，
但是就是没有日期的，日期的单元格会被判断成Numeric(数字)类型，
所以日期格式的单元格就按数字类型来取其中的值，
所以单元格被判断成数字的之后还要再判断一下是否为日期格式。
|
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
|
/// <summary> /// 获取单元格类型 /// </summary> /// <param name="cell"></param> /// <returns></returns> private static object GetValueType(ICell cell) { if (cell == null) return null; switch (cell.CellType) { case CellType.Blank: //BLANK: return null; case CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case CellType.Numeric: //NUMERIC: short format = cell.CellStyle.DataFormat; if (format != 0) { return cell.DateCellValue; } else { return cell.NumericCellValue; } case CellType.String: //STRING: return cell.StringCellValue; case CellType.Error: //ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: default: return "=" + cell.CellFormula; } } |
注意
使用时Excel里的长数字类型,否则这类数据可能会被误判为日期类型
如：0000123,2017001等这类型的需要处理一下单元格格式->设置成"常规"类型
以上就是C# 基于NPOI操作Excel的详细内容，更多关于C# NPOI操作Excel的资料请关注服务器之家其它相关文章！
原文链接：https://www.cnblogs.com/DisonTangor/p/14890255.html
