NPOI导出多表头Execl(通过html表格遍历表头)
浏览数：27 /
时间：2015年06月09日
关于NPOI的相关信息，我想博客园已经有很多了，而且NPOI导出Execl的文章和例子也很多，但导出多表头缺蛮少的；今天要讲的通过自己画html表格；通过html表格来导出自定义的多表头；
先来看要实现的多表头格式:
第一步：画html表格（备注有一定的格式要求）
//td需要4个属性，rowspan(跨行数)、colspan(跨列数)、row(所在行)、col(所在列)；备注：其实除了跨行和跨列数外，后面只需要所在列都可以了；
<tr>
<td rowspan="2" colspan="1" row="0" col="0">名称1</td>
<td rowspan="2" colspan="1" row="0" col="1">名称2</td>
<td rowspan="2" colspan="1" row="0" col="2">名称3</td>
<td rowspan="1" colspan="4" row="0" col="3">名称4</td>
<td rowspan="1" colspan="4" row="0" col="7">名称5</td>
<td rowspan="1" colspan="4" row="0" col="11">名称6</td>
<td rowspan="1" colspan="4" row="0" col="15">名称7</td>
<td rowspan="2" colspan="1" row="0" col="19">名称8</td>
<td rowspan="2" colspan="1" row="0" col="20">名称9</td>
<td rowspan="2" colspan="1" row="0" col="21">备注</td>
</tr>
<tr>
<td rowspan="1" colspan="1" row="1" col="3">效果1</td>
<td rowspan="1" colspan="1" row="1" col="4">效果2</td>
<td rowspan="1" colspan="1" row="1" col="5">效果3</td>
<td rowspan="1" colspan="1" row="1" col="6">效果4</td>
<td rowspan="1" colspan="1" row="1" col="7">效果5</td>
<td rowspan="1" colspan="1" row="1" col="8">效果6</td>
<td rowspan="1" colspan="1" row="1" col="9">效果7</td>
<td rowspan="1" colspan="1" row="1" col="10">效果8</td>
<td rowspan="1" colspan="1" row="1" col="11">效果9</td>
<td rowspan="1" colspan="1" row="1" col="12">效果10</td>
<td rowspan="1" colspan="1" row="1" col="13">效果11</td>
<td rowspan="1" colspan="1" row="1" col="14">效果12</td>
<td rowspan="1" colspan="1" row="1" col="15">效果13</td>
<td rowspan="1" colspan="1" row="1" col="16">效果14</td>
<td rowspan="1" colspan="1" row="1" col="17">效果15</td>
<td rowspan="1" colspan="1" row="1" col="18">效果16</td>
</tr>
第二步,解析html表格
1、正则遍历tr
string rowContent = string.Empty; MatchCollection rowCollection = Regex.Matches(html, @"<tr[^>]*>[\s\S]*?<\/tr>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对tr进行筛选
2、循环tr正则遍历td
MatchCollection columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[\s\S]*?<\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对td进行筛选
3、解析td原属
var match = Regex.Match(columnCollection[j].Value, "<td.*?rowspan=\"(?<row>.*?)\".*?colspan=\"(?<col>.*?)\".*?row=\"(?<row1>.*?)\".*?col=\"(?<col1>.*?)\">(?<value>.*?)<\\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); if (match.Success) { int rowspan = Convert.ToInt32(match.Groups["row"].Value);//表格跨行 int colspan = Convert.ToInt32(match.Groups["col"].Value);//表格跨列 int rowcount = Convert.ToInt32(match.Groups["row1"].Value);//所在行 int col = Convert.ToInt32(match.Groups["col1"].Value);//所在列 string value = match.Groups["value"].Value;//值
}
通过上面几步，都可以解析出对应的表格原属
使用NPOI
1、创建HSSFWorkbook
HSSFWorkbook hssfworkbook = new HSSFWorkbook();;//创建Workbook对象 HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("测试多表头");//创建工作表
2、在tr循环中创建行
//写在tr循环中
for (int i = 0; i < rowCollection.Count; i++){
HSSFRow row = (HSSFRow)sheet1.CreateRow(i); rowContent = rowCollection[i].Value;
}
3、在td循环中创建列（关键）
//遍历td
for (int j = 0; j < columnCollection.Count; j++) { var match = Regex.Match(columnCollection[j].Value, "<td.*?rowspan=\"(?<row>.*?)\".*?colspan=\"(?<col>.*?)\".*?row=\"(?<row1>.*?)\".*?col=\"(?<col1>.*?)\">(?<value>.*?)<\\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); if (match.Success) { int rowspan = Convert.ToInt32(match.Groups["row"].Value);//表格跨行 int colspan = Convert.ToInt32(match.Groups["col"].Value);//表格跨列 int rowcount = Convert.ToInt32(match.Groups["row1"].Value);//所在行 int col = Convert.ToInt32(match.Groups["col1"].Value);//所在列 string value = match.Groups["value"].Value; if (colspan == 1)//判断是否跨列 { var cell = row.CreateCell(col);//创建列 cell.SetCellValue(value);//设置列的值 if (value.Length > 0) { int width = value.Length * 25 / 6; if (width > 255) width = 250; sheet1.SetColumnWidth(col, width * 256); } } //判断是否跨行、跨列 if (rowspan > 1 || colspan > 1) { int firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 0; if (rowspan > 1)//跨行 { firstRow = rowcount; lastRow = firstRow + rowspan - 1; } else { firstRow = lastRow = i; } if (colspan > 1)//跨列 { firstCol = col; int cols = col + colspan; for (; col < cols; col++) { var cell = row.CreateCell(col); cell.SetCellValue(value); } lastCol = col - 1; } else { firstCol = lastCol = col; }
//关键是这里，设置起始行数，结束行数；起始列数，结束列数 sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); } } }
保存execl
string year = DateTime.Now.Year.ToString(); string ppath = HttpContext.Current.Server.MapPath(DateTime.Now.ToString("yyyyMMddmmss") + ".xls"); FileStream file = new FileStream(ppath, FileMode.Create); hssfworkbook.Write(file); file.Close();
这样都保存在服务器上了,可以通过下载自行下载下来；这里不复制代码了；
如果有什么问题，请指教，谢谢！
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
