NPOI导出多表头Execl(通过html表格遍历表头)

关于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[^>]*>[sS]*?</tr>",
                    RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对tr进行筛选

 2、循环tr正则遍历td

   MatchCollection columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[sS]*?</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();

这样都保存在服务器上了,可以通过下载自行下载下来；这里不复制代码了；

如果有什么问题，请指教，谢谢！