.NET使用NPOI合并单元格

.NET使用NPOI读取Excel文件，将指定列相同内容单元格合并已关闭评论
1,382 views

A+

今天开发过程中遇到要把数据导出到EXCEL表，并要把EXCEL的某列单元格合并

通过网上查找资料，可以使用NPOI的CellRangeAddress来合并指定列，然后把文本重新赋值

1
2
3
4
5
6
7
8
9

//合并单元格
/**
第一个参数：从第几行开始合并
第二个参数：到第几行结束合并
第三个参数：从第几列开始合并
第四个参数：到第几列结束合并
**/
CellRangeAddress region = new CellRangeAddress(15, 15 , 1, 2);
sheet.AddMergedRegion(region);

通过改良代码，制作出给指定参数就可完成将某列的单元格相同内容合并

下面将代码贴出

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
123
124

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
/// <summary>
/// 读取Excel文件并将指定列相同内容单元格合并
/// </summary>
/// <param name="path">文件地址</param>
/// <param name="keyCell">需要合并的列</param>
/// <param name="errorMsg"></param>
/// <returns></returns>
public bool MergedRegionForExcel(string path, string keyCell, out string errorMsg)
{
FileStream file = null;
errorMsg = "";
try
{
HSSFWorkbook hssfworkbook;
using (file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
if (hssfworkbook == null)
{
errorMsg = "无法读取excel，请检查";
return false;
}
HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
if (sheet == null || sheet.LastRowNum < 0)
{
errorMsg = "excel格式异常，请检查";
return false;
}
string temp = "";string text;
HSSFRow row = null;string rowMsg;
HSSFCell cell; HSSFCell newcell;
Dictionary<string, int> headCellDic = NPOIHelp.GetCellDictNum(sheet, 0); //表头键与索引集合
//样式设置
HSSFCellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;//垂直居中
style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;//水平居中
style.WrapText = true;//自动换行
style.BorderTop = HSSFCellStyle.BORDER_THIN;
style.BorderBottom = HSSFCellStyle.BORDER_THIN;
style.BorderLeft = HSSFCellStyle.BORDER_THIN;
style.BorderRight = HSSFCellStyle.BORDER_THIN;

int titleNum = 1;

int start = 0;//开始相同的行
int end = 0;//结束相同值的行
for (int i = 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
cell = row.GetCell(headCellDic[keyCell]);
text = NPOIHelp.getCellStringValue(row, cell, keyCell, out rowMsg);
if (text == temp)
{
if (i == sheet.LastRowNum)
{
if (start > 0)
{
CellRangeAddress region = new CellRangeAddress(start, i, headCellDic[keyCell], headCellDic[keyCell]);
sheet.AddMergedRegion(region);
newcell = sheet.CreateRow(start).CreateCell(headCellDic[keyCell]);
newcell.CellStyle = style;
newcell.SetCellValue(temp);
}
else
{
CellRangeAddress region = new CellRangeAddress(i - 1, i, headCellDic[keyCell], headCellDic[keyCell]);
sheet.AddMergedRegion(region);
newcell = sheet.CreateRow(i - 1).CreateCell(headCellDic[keyCell]);
newcell.CellStyle = style;
newcell.SetCellValue(temp);
}
}
else
{
if (start == 0)
{
start = i - 1;
}
end = i;
}
}
else
{
if (start > 0 && end > 0)
{
CellRangeAddress region = new CellRangeAddress(start, end, headCellDic[keyCell], headCellDic[keyCell]);
sheet.AddMergedRegion(region);
newcell = sheet.CreateRow(start).CreateCell(headCellDic[keyCell]);
newcell.CellStyle = style;
newcell.SetCellValue(temp);
start = 0;
end = 0;
}
else
{
if(i!= titleNum)//过滤标题栏
{
newcell = sheet.CreateRow(i - 1).CreateCell(headCellDic[keyCell]);
newcell.CellStyle = style;
newcell.SetCellValue((string.IsNullOrEmpty(temp) ? text : temp));
}
}
}
temp = text;
}
}
using (file = new FileStream(path, FileMode.Open, FileAccess.Write))
{
hssfworkbook.Write(file);//写入xls文件中
}
}
catch(Exception ex)
{
errorMsg = ex.Message;
return false;
}
return true;
}

调用方法示例：将111.xls文件的A列合并