关于NPOI的相关信息,我想博客园已经有很多了,而且NPOI导出Execl的文章和例子也很多,但导出多表头缺蛮少的;今天要讲的通过自己画html表格;通过html表格来导出自定义的多表头;
先来看要实现的多表头格式:
第一步:画html表格(备注有一定的格式要求)
//td需要4个属性,rowspan(跨行数)、colspan(跨列数)、row(所在行)、col(所在列);备注:其实除了跨行和跨列数外,后面只需要所在列都可以了;
第二步,解析html表格
1、正则遍历tr
string rowContent = string.Empty;
MatchCollection rowCollection = Regex.Matches(html, @"
]*>[\s\S]*?",RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对tr进行筛选
2、循环tr正则遍历td
MatchCollection columnCollection = Regex.Matches(rowContent, @"
]*>[\s\S]*?",RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对td进行筛选
3、解析td原属
var match = Regex.Match(columnCollection[j].Value, "
.*?)\".*?colspan=\"(?.*?)\".*?row=\"(?.*?)\".*?col=\"(?.*?)\">(?.*?)", 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, "
.*?)\".*?colspan=\"(?.*?)\".*?row=\"(?.*?)\".*?col=\"(?.*?)\">(?.*?)", 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();
这样都保存在服务器上了,可以通过下载自行下载下来;这里不复制代码了;
如果有什么问题,请指教,谢谢!