NPOI导出真正的电子表格，支持 自定义多行表头（表头风格设置），支持多个sheet页面导出
发布日期：2022-02-01 14:28:14
浏览次数：40
分类：技术文章
本文共 15703 字，大约阅读时间需要 52 分钟。
NPOI导出真正的电子表格，支持自定义多行表头（表头风格设置），支持多个片页面导出，效果如下
DEMO资源下载
代码如下：
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 > dicHeads = new Dictionary >(); DataSet ds = new DataSet(); DataTable dt1 = new DataTable("测试表格1"); ds.Tables.Add(dt1); //你的数据源 dt1 自己补充数据 List heads = new List (); 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); * */ /// /// NPOI导出真正的电子表格，支持 自定义多行表头（表头风格设置），支持多个sheet页面导出 /// /// /// 创建：shunlu 2018-10-24 /// public class NpoiExcelExport { // static ILog log = LogManager.GetLogger(typeof(NpoiExcelExport)); /// /// 导出真正的电子表格 /// /// 数据源 /// 表头设置列表 /// 文件名称 /// 是否从网络下载 true 下载，false 不下载 /// 保存到本地路径(不含文件名称)，savePath为空不保存本地文件 /// /// 创建：shunlu 2018-10-24 /// public static dynamic ExporXSSFExcel(DataSet ds, Dictionary > 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(); } } /// /// 创建 sheet /// /// /// /// /// /// /// 创建：shunlu 2018-10-24 /// private static void CreateSheet(XSSFWorkbook book, int index, DataTable dt, List heads) { //创建该sheet页 ISheet sheet = book.CreateSheet(dt.TableName); //创建 表格头部 CreadHeader(book, ref sheet, dt, heads); //创建 表格数据 CreadDataRows(book, ref sheet, dt, heads); } /// /// 创建表头，支持多行 /// /// /// /// /// /// /// 创建：shunlu 2018-10-24 /// private static void CreadHeader(XSSFWorkbook book, ref ISheet sheet, DataTable dt, List 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 mgs = new Dictionary (); // 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); } } } } /// /// 创建表头单元格，（支持递归调用) /// /// 上一行 /// 开始列索引 /// 行索引 /// 输出最新行索引 /// 表头配置 /// 返回最新的列索引 /// /// 创建：shunlu 2018-10-24 /// 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; for (int i = 0; i < headCfg.Childs.Count; i++) { if (headCfg.Childs[i].Childs.Count > 0) { //Console.Write("递归调用\r\n"); // int _outRowIndex = 0; int old_ColIndex = newColIndex; // int new_ColIndex = CreateHeadCell(curHeadRow, newColIndex, rowIndex, out _outRowIndex, headCfg.Childs[i]);//递归调用 // for (int j = old_ColIndex; j < new_ColIndex; j++) { if (curHeadRow.GetCell(j) == null) { ICell _cell = curHeadRow.CreateCell(j); //创建单元格 _cell.SetCellValue(headCfg.Childs[i].FieldLable); //设置单元格内容 _cell.CellStyle = style; } } //合并单元格 //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列 CellRangeAddress region1 = new CellRangeAddress(curHeadRow.RowNum, curHeadRow.RowNum, (short)old_ColIndex, (short)(new_ColIndex - 1)); sheet.AddMergedRegion(region1); // if (_outRowIndex > outRowIndex) { outRowIndex = _outRowIndex; } newColIndex = new_ColIndex; } else { ICell _cell = curHeadRow.CreateCell(newColIndex); //创建单元格 _cell.SetCellValue(headCfg.Childs[i].FieldLable);//设置单元格内容 _cell.CellStyle = style; // 设置列宽 if (headCfg.Width > 0) { sheet.SetColumnWidth(_cell.ColumnIndex, headCfg.Width * 256); } else { sheet.SetColumnWidth(_cell.ColumnIndex, 13 * 256); } // newColIndex += 1; } } // return newColIndex; } /// /// 创建 表格数据 /// /// /// /// 数据源 /// 表头配置 /// /// 创建：shunlu 2018-10-24 /// private static void CreadDataRows(XSSFWorkbook book, ref ISheet sheet, DataTable dt, List heads) { // if (dt == null || dt.Rows.Count == 0) { return; } if (heads != null && heads.Count > 0) { List curhds = new List (); //遍历所有顶层节点 for (int x = 0; x < heads.Count; x++) { List hds = GetAllLeafNode(heads[x]);//获取所有叶子子节点 curhds.AddRange(hds); } //遍历所有数据行 for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet.CreateRow(sheet.LastRowNum + 1);//创建空行 int colIndex = 0; //遍历所有叶子子节点 for (var y = 0; y < curhds.Count; y++) { var colname = curhds[y].FieldName; if (dt.Columns.Contains(colname)) {//数据源列是否含有配置列 ICell cell = row.CreateCell(colIndex); if (dt.Rows[i][colname] != DBNull.Value) { //所有数据类型统统 ToString() cell.SetCellValue(dt.Rows[i][colname].ToString()); } colIndex++; } } } } else { //遍历行 for (int x = 0; x < dt.Rows.Count; x++) { IRow row = sheet.CreateRow(sheet.LastRowNum + 1);//创建行 //遍历列 for (int i = 0; i < dt.Columns.Count; i++) { var colname = dt.Columns[i].ColumnName; ICell cell = row.CreateCell(i); if (dt.Rows[x][i] != DBNull.Value) { cell.SetCellValue(dt.Rows[x][i].ToString()); } } } } } /// /// 获取当前节点所有的叶子节点 /// /// 配置 /// /// 创建：shunlu 2018-10-28 /// private static List GetAllLeafNode(NpoiHeadCfg headcfg) { List heads = new List (); if (headcfg != null) { if (headcfg.Childs.Count > 0) { for (int i = 0; i < headcfg.Childs.Count; i++) { var hds = GetAllLeafNode(headcfg.Childs[i]);//递归调用 if (hds.Count > 0) { heads.AddRange(hds); } } return heads; } else { heads.Add(headcfg); return heads; } } else { return heads; } } /// /// 判断是否指定名称的叶子节点 /// /// 配置列表 /// 目标列名称 /// /// 创建：shunlu 2018-10-26 /// private static bool HasTargetNode(List heads, string colname) { if (heads != null) { var bhav = heads.Exists(w => w.Childs.Count == 0 && w.FieldName.ToLower() == colname.ToLower()); // 只查叶子节点,忽略大小写 if (bhav) { return bhav; } else { for (int i = 0; i < heads.Count; i++) { bhav = HasTargetNode(heads[i].Childs, colname);//递归调用 if (bhav) { return true; } } return false; } } else { return false; } } /// /// 设置单元格样式 /// 风格请自己按headCfg参数编写 /// /// /// 表头配置 /// /// 创建：shunlu 2018-10-24 /// private static ICellStyle GetCellStyle(XSSFWorkbook book, NpoiHeadCfg headCfg) { ICellStyle style0 = book.CreateCellStyle(); // 2、行高 // row.Height = 30 * 20; //行高为30 // excelRow.Height = 25 * 20; // 单元格 列宽: //if (headCfg.Width > 0) { // cell.Row.Sheet.SetColumnWidth(cell.ColumnIndex, headCfg.Width * 256); //} //三、设置居中: //cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 style0.Alignment = HorizontalAlignment.Left; style0.VerticalAlignment = VerticalAlignment.Center; //四、设置字体: IFont font = book.CreateFont(); font.FontName = "黑体";//.SetFontName("黑体"); font.FontHeightInPoints = (short)11.5;//.SetFontHeightInPoints((short)16);//设置字体大小 style0.SetFont(font);//选择需要用到的字体格式 //大坑，大坑，大坑，shunlu 2018-10-10 //必须设置单元格背景色 FillForegroundColor 和 FillPattern 的值才能正确显示背景色 style0.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index; //(short)1灰色 NPOI.HSSF.Util.HSSFColor.LightBlue.Index; style0.FillPattern = FillPattern.SolidForeground; // CellStyle.SOLID_FOREGROUND //二、设置边框: //cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 三、设置居中: cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 style0.BorderBottom = BorderStyle.Medium;// CellStyle.SOLID_FOREGROUND style0.BorderRight = BorderStyle.Medium; //三、设置居中: //cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 style0.Alignment = HorizontalAlignment.Left; style0.VerticalAlignment = VerticalAlignment.Center; // return style0; } /// /// 保存电子表格 /// /// /// 文件名称 /// 保存物理路径（不含文件名） /// /// 创建：shunlu 2018-10-24 /// private static void SaveExcel(XSSFWorkbook book, string fileName, string savePath = "") { string fullpath = fileName; if (!string.IsNullOrEmpty(savePath)) { if (!savePath.EndsWith("\\")) savePath += "\\"; fullpath = savePath + fileName; } if (File.Exists(fullpath)) { File.Delete(fullpath); } FileStream fsOut = new FileStream(fullpath, FileMode.Create); book.Write(fsOut); fsOut.Close(); fsOut.Dispose(); } /// /// 下载电子表格文件 /// /// /// 文件名称 /// /// 创建：shunlu 2018-10-24 /// private static HttpResponseMessage DownXssfFile(XSSFWorkbook book, string saveFileName) { var exportData = new NpoiMemoryStream(); HttpResponseMessage response = new HttpResponseMessage(System.Net.HttpStatusCode.OK); exportData.AllowClose = false; book.Write(exportData); exportData.Flush(); exportData.Seek(0, SeekOrigin.Begin); exportData.AllowClose = true; response.Content = new StreamContent(exportData); response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = saveFileName }; if (response.Content.Headers.ContentDisposition.Parameters.Count > 0) { response.Content.Headers.ContentDisposition.FileName = saveFileName; var fn = response.Content.Headers.ContentDisposition.Parameters.ElementAt(0).Value;// = saveFileName; } response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/ms-excel"); response.StatusCode = System.Net.HttpStatusCode.OK; return response; } } /// /// 电子表格的表头设置（支持多行表头设置） /// /// /// 创建：shunlu 2018-10-24 /// public class NpoiHeadCfg { /// /// 表头格式设置（支持多行表头） /// public NpoiHeadCfg() { Childs = new List (); } /// /// 重载构造函数 /// /// 数据源列名 /// 电子表格列名 /// 行高 /// 列宽 public NpoiHeadCfg(string FieldName, string FieldLable, int Width = 10, int Height = 13) { this.FieldName = FieldName; this.FieldLable = FieldLable; this.Height = Height; this.Width = Width; this.IsBold = true; // Childs = new List (); } /// /// 字段名称 /// public string FieldName { get; set; } /// /// 字段标签（表格行头标题） /// public string FieldLable { get; set; } /// /// 高度 /// public int Height { get; set; } /// /// 宽度 /// public int Width { get; set; } /// /// 背景颜色 /// public string BackColor { get; set; } /// /// 文本颜色 /// public string FontColor { get; set; } /// /// 是否粗体显示 /// public bool IsBold { get; set; } /// /// 子节点 /// public List Childs { get; set; } } /// /// 内存字节流 /// public class NpoiMemoryStream : MemoryStream { /// /// /// public NpoiMemoryStream() { AllowClose = true; } /// /// /// public bool AllowClose { get; set; } /// /// /// public override void Close() { if (AllowClose) base.Close(); } }}
转载地址：https://blog.csdn.net/shunlu/article/details/83480012 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！
发表评论
最新留言
感谢大佬
[***.8.128.20]2024年03月30日 05时25分44秒
关于作者
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！
-- 愿君每日到此一游！
推荐文章
01 计算机视觉-opencv图像基本操作
2021-06-29
02 计算机视觉-opencv阈值与滤波处理
2021-06-29
03 计算机视觉-opencv图像形态学处理
2021-06-29
04计算机视觉-opencv图像梯度处理
2021-06-29
05计算机视觉-opencv边缘检测
2021-06-29
07 计算机视觉-opencv模板匹配
2021-06-29
Word2vec与logging模块_完整代码_CodingPark编程公园
2021-06-29
Auto-Encoder&VAE_完整代码_CodingPark编程公园
2021-06-29
Debug讲解_CodingPark编程公园
2021-06-29
命名实体识别_利用CRF_CodingPark编程公园
2021-06-29
Pycharm使用-取消pytest的办法_CodingPark编程公园
2021-06-29
OpenCV图像处理基础_CodingPark编程公园
2021-06-29
生成个性二维码_CodingPark编程公园
2021-06-29
读txt文件报错_CodingPark编程公园
2021-06-29
文件读&写_Python_CodingPark编程公园
2021-06-29
list与str的转换&list嵌套问题_CodingPark编程公园
2021-06-29
白红宇的个人博客 - 记录点点滴滴的事 - 您是第 306333144 位访客
访问时间: 2024-04-20 01:49:44
访问IP: 18.117.186.92
Copyright © 2020 - 2023 blog.css8.cn 京ICP备2021015314号-1
手机版