NPOI导出真正的电⼦表格，⽀持⾃定义多⾏表头（表头风格设置），⽀持多个sheet页⾯导出
NPOI导出真正的电⼦表格，⽀持⾃定义多⾏表头（表头风格设置），⽀持多个⽚页⾯导出，效果如下
代码如下：
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Net.Http;
using System.Net.Http.Headers;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
//using log4net;
namespace Test.Models {
/
*
* 调⽤实例
* Dictionary<string, List<NpoiHeadCfg>> dicHeads = new Dictionary<string, List<NpoiHeadCfg>>();
DataSet ds = new DataSet();
DataTable dt1 = new DataTable("测试表格1");
ds.Tables.Add(dt1); //你的数据源 dt1 ⾃⼰补充数据
List<NpoiHeadCfg> heads = new List<NpoiHeadCfg>();
heads.Add(new NpoiHeadCfg("rownumber", "⾏号"));
heads.Add(new NpoiHeadCfg("dateid", "⽇期"));
NpoiHeadCfg hc3 = new NpoiHeadCfg("", "10⽉1号", 20);
hc3.Childs.Add(new NpoiHeadCfg("day1_zb1", "完⼯总数"));
hc3.Childs.Add(new NpoiHeadCfg("day1_zb2", "回复总数"));
hc3.Childs.Add(new NpoiHeadCfg("day1_zb3", "回复率"));
//
dicHeads.Add(dt1.TableName, heads);
string fileName = "调查_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".xls";
//调⽤，从服务端下载前⾯加 return
NpoiExcelExport.ExporXSSFExcel(ds, dicHeads, fileName);
* */
/// <summary>
/// NPOI导出真正的电⼦表格，⽀持⾃定义多⾏表头（表头风格设置），⽀持多个sheet页⾯导出
/// </summary>
/
// <remarks>
/// 创建：shunlu 2018-10-24
/// </remarks>
/// </remarks>
public class NpoiExcelExport {
// static ILog log = LogManager.GetLogger(typeof(NpoiExcelExport));
/// <summary>
/// 导出真正的电⼦表格
/// </summary>
/// <param name="ds">数据源</param>
/// <param name="heads">表头设置列表</param>
/
// <param name="fileName">⽂件名称</param>
/// <param name="downLoad">是否从⽹络下载 true 下载，false 不下载</param>
/// <param name="savePath">保存到本地路径(不含⽂件名称)，savePath为空不保存本地⽂件</param>
/// <remarks>
/// 创建：shunlu 2018-10-24
/// </remarks>
public static dynamic ExporXSSFExcel(DataSet ds, Dictionary<string, List<NpoiHeadCfg>> heads, string fileName, bool downLoad = true, string savePath = " //创建电⼦表格⽂件
XSSFWorkbook book = new XSSFWorkbook();
try {
for (int i = 0; i < ds.Tables.Count; i++) {
// log.Debug("ExporXSSFExcel " + i + "：表名：" + ds.Tables[i].TableName);
if (heads.ContainsKey(ds.Tables[i].TableName)) {
//
var _heads = heads[ds.Tables[i].TableName];
//
CreateSheet(book, i, ds.Tables[i], _heads);
}
}
//保存⽂件
if (!string.IsNullOrEmpty(savePath)) {
SaveExcel(book, fileName, savePath);
}
//
// 服务端下载⽂件
if (downLoad) {
return DownXssfFile(book, fileName);
} else {
return null;
}
} catch (Exception ex) {
throw ex;
} finally {
book.Close();
}
}
/// <summary>
/// 创建 sheet
/// </summary>
/// <param name="book"></param>
/// <param name="index"></param>
/// <param name="dt"></param>
/// <param name="heads"></param>
/// <remarks>
/
// 创建：shunlu 2018-10-24
/// </remarks>
private static void CreateSheet(XSSFWorkbook book, int index, DataTable dt, List<NpoiHeadCfg> heads) {
//创建该sheet页
ISheet sheet = book.CreateSheet(dt.TableName);
//创建表格头部
//创建表格头部
CreadHeader(book, ref sheet, dt, heads);
//创建表格数据
CreadDataRows(book, ref sheet, dt, heads);
}
/// <summary>
/// 创建表头，⽀持多⾏
/// </summary>
/// <param name="book"></param>
/// <param name="sheet"></param>
/// <param name="dt"></param>
/// <param name="heads"></param>
/// <remarks>
/// 创建：shunlu 2018-10-24
/// </remarks>
private static void CreadHeader(XSSFWorkbook book, ref ISheet sheet, DataTable dt, List<NpoiHeadCfg> heads) {
//创建表格头部
if (heads != null && heads.Count > 0) {
//使⽤⾃定义表头（可以⽀持多⾏表头）
IRow headRow = sheet.CreateRow(0);//创建空⾏
headRow.Height = (short)(heads[0].Height * 20); //设置⾏⾼为25
//遍历⾃定义列头
int maxHeadRowNum = 0;//多⾏最⼤⾏号
//
int newColIndex = 0;
//记录当前列最多变成⼏列
Dictionary<int, int[]> mgs = new Dictionary<int, int[]>();
/
/
for (int i = 0; i < heads.Count; i++) {
if (heads[i].Childs.Count == 0) {
#region ⽆⼦节点
ICell cell = headRow.CreateCell(newColIndex); //创建单元格
cell.SetCellValue(heads[i].FieldLable); //设置单元格内容
var style = GetCellStyle(book, heads[i]);
cell.CellStyle = style;
// 设置列宽
if (heads[i].Width > 0) {
sheet.SetColumnWidth(cell.ColumnIndex, heads[i].Width * 256);
} else {
sheet.SetColumnWidth(cell.ColumnIndex, 13 * 256);
}
//
mgs.Add(i, new int[] { newColIndex, 1 });
newColIndex += 1;
#endregion
} else {
#region 多个⼦节点
int rowIndex = 0;
int outRowIndex = 0;
int old_colIndex = newColIndex;
int new_colIndex = CreateHeadCell(headRow, newColIndex, rowIndex, out outRowIndex, heads[i]); // 返回最⼤列数 //
for (int j = old_colIndex; j < new_colIndex; j++) {
if (headRow.GetCell(j) == null) {
ICell _cell = headRow.CreateCell(j); //创建单元格
_cell.SetCellValue(heads[i].FieldLable); //设置单元格内容
var style = GetCellStyle(book, heads[i]);
_cell.CellStyle = style;
}
}
}
mgs.Add(i, new int[] { old_colIndex, new_colIndex - old_colIndex });
//
//合并单元格
//参数1：起始⾏参数2：终⽌⾏参数3：起始列参数4：终⽌列
CellRangeAddress region1 = new CellRangeAddress(headRow.RowNum, headRow.RowNum, (short)old_colIndex, (short)new_colIndex - 1); headRow.Sheet.AddMergedRegion(region1);
//
newColIndex = new_colIndex;
//
if (outRowIndex > maxHeadRowNum) {
maxHeadRowNum = outRowIndex;//更新多⾏最⼤⾏号
}
#endregion
}
}
var fullstyle = GetCellStyle(book, heads[0]);
//合并列
#region 合并列
if (maxHeadRowNum > 0) {
foreach (var mg in mgs) {
var values = mg.Value;
int cIndex = values[0];
int cCount = values[1];
if (cCount == 1) {
for (int j = headRow.RowNum; j <= maxHeadRowNum; j++) {
ICell cell = sheet.GetRow(j).GetCell(cIndex);
if (cell == null) {
cell = sheet.GetRow(j).CreateCell(cIndex);
cell.CellStyle = fullstyle;
}
}
CellRangeAddress region1 = new CellRangeAddress(headRow.RowNum, maxHeadRowNum, (short)cIndex, (short)cIndex);
headRow.Sheet.AddMergedRegion(region1);
} else {
for (int j = maxHeadRowNum; j >= headRow.RowNum; j--) {
IRow row = sheet.GetRow(j);
ICell cell = row.GetCell(cIndex);
if (cell == null) {
for (int y = 0; y < cCount; y++) {
cell = row.CreateCell(cIndex + y);
cell.CellStyle = fullstyle;
//向上⾏合并
CellRangeAddress region1 = new CellRangeAddress(j - 1, maxHeadRowNum, (short)(cIndex + y), (short)(cIndex + y));
headRow.Sheet.AddMergedRegion(region1);
}
} else {
for (int y = 0; y < cCount; y++) {
cell = row.GetCell(cIndex + y);
if (cell == null) {
cell = row.CreateCell(cIndex + y);
cell.CellStyle = fullstyle;
//判断上⼀⾏是否空
for (int x = j - 1; x >= headRow.RowNum; x--) {
IRow preRow = sheet.GetRow(x);
var precell = preRow.GetCell(cIndex + y);
if (precell == null) {
var newcell = preRow.CreateCell(cIndex + y);
newcell.CellStyle = fullstyle;
} else {
//向下⾏合并
CellRangeAddress region1 = new CellRangeAddress(x, maxHeadRowNum, (short)(cIndex + y), (short)(cIndex + y));
headRow.Sheet.AddMergedRegion(region1);
break;
}
}
}
}
}
break;
}
}
}
}
}
#endregion
} else {
//使⽤数据源列名作表头（只⽀持单⾏表头）
IRow headRow = sheet.CreateRow(0);//创建空⾏
var style = GetCellStyle(book, null);
//遍历列
for (int i = 0; i < dt.Columns.Count; i++) {
ICell cell = headRow.CreateCell(i);
cell.CellStyle = style;
if (!string.IsNullOrEmpty(dt.Columns[i].Caption)) {
cell.SetCellValue(dt.Columns[i].Caption);
} else {
cell.SetCellValue(dt.Columns[i].ColumnName);
getsavefilename}
}
}
}
/// <summary>
/// 创建表头单元格，（⽀持递归调⽤)
/// </summary>
/// <param name="preHeadRow">上⼀⾏</param>
/// <param name="startColIndex">开始列索引</param>
/// <param name="rowIndex">⾏索引</param>
/// <param name="outRowIndex">输出最新⾏索引</param>
/// <param name="headCfg">表头配置</param>
/
// <returns>返回最新的列索引</returns>
/// <remarks>
/// 创建：shunlu 2018-10-24
/// </remarks>
private static int CreateHeadCell(IRow preHeadRow, int startColIndex, int rowIndex, out int outRowIndex, NpoiHeadCfg headCfg) { // int colCount = headCfg.Childs.Count;
int preRowIndex = rowIndex;
rowIndex += 1;
outRowIndex = rowIndex;
var sheet = preHeadRow.Sheet;
XSSFWorkbook book = (XSSFWorkbook)sheet.Workbook;
var style = GetCellStyle(book, headCfg);
//
IRow curHeadRow = null;
if (sheet.LastRowNum >= rowIndex) {
curHeadRow = sheet.GetRow(rowIndex);
} else {
curHeadRow = sheet.CreateRow(rowIndex);//创建空⾏
for (int i = 0; i < startColIndex; i++) {
ICell cell = curHeadRow.CreateCell(i); //创建单元格
cell.CellStyle = style;
ICell mycell = preHeadRow.GetCell(i); //获取单元格
if (mycell != null)
cell.SetCellValue(mycell.StringCellValue);//设置单元格内容
}
}
int newColIndex = startColIndex;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论