NPOI导入,导出
1、 概述
NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。NPOI 可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写,包含了大部分EXCEL的特性(单元格样式、数据格式、公式等等)。(复制百度百科)
表格导入导出的操作经常会碰到,最近抽空专门封装了一下。
遵循的原则:
1. 一行数据对应一个数据实体,需提供它们二者间的映射关系。
2. 支持合并单元格的导入导出(导出数据只支持竖向合并,导入数据遇到横向合并的单元格会抛出异常),图片未支持
导出的复杂在于样式的控制,当解决这一问题后后续操作就很简单了,而样式解析相关的代码行数也真的是不少,因为对API的不熟悉,连写带测折腾了不少时间。
导入因为行为的多变性,要求程序员调用时将具体的读写逻辑以策略的形式传入。所以代码量相对来说要少得多。其实也是避开了一些难题,比如公式符号,数字的上标,下标的解析(尚不知道怎么解决)。
导出效果
2、 表格导入
2.1、 单元格数据的解析
1. 先依据单元格类的数据类型cell.CellType(枚举类型),读取其内部保存的数据,如果类型是公式的,则依据cell.CachedFormulaResultType再进行一次判别。
2. 从单元格读到数据后还再进一步完成一次类型的转换,将它转换为我们需要的类型。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | /// <summary> /// 扩展 /// </summary> public static class IRowExtension { /// <summary> /// 读取表格中某单元格的值,如果单元格是合并单元格,则竖向合并的正常返回,横向合并的抛出异常 /// </summary> /// <typeparam name="A">要读取的数据类型</typeparam> /// <param name="row">Excel中的某行数据</param> /// <param name="index">行内的列下标</param> /// <returns></returns> public static A GetValue<A>( this IRow row, int index) { try { //获取单元格 ICell cell = row.GetCell(index); //判别是否合并单元格 if (cell.IsMergedCell) { ISheet sheet = cell.Sheet; for ( int i = 0, length = sheet.NumMergedRegions; i < length; i++) { var cellRange = sheet.GetMergedRegion(i); if (cell.ColumnIndex >= cellRange.FirstColumn && cell.ColumnIndex <= cellRange.LastColumn && cell.RowIndex >= cellRange.FirstRow && cell.RowIndex <= cellRange.LastRow) { if (cellRange.FirstColumn != cellRange.LastColumn) { throw new Exception( "不能有横向合并的单元格" ); } cell = sheet.GetRow(cellRange.FirstRow).GetCell(cellRange.FirstColumn); } } } return GetValue<A>(cell); } catch (Exception ex) { //读取单元格信息时出错 throw new Exception( "出错位置的列下标:" + index + ",报错信息:" + ex.Message); } } /// <summary> /// 读取表格中某单元格的值,如果单元格是合并单元格,则竖向合并的正常返回,横向合并的抛出异常 /// </summary> /// <typeparam name="A">要读取的数据类型</typeparam> /// <param name="cell">Excel中的某单元格</param> /// <returns></returns> private static A GetValue<A>(ICell cell) { //依据单元格数据类型读取数据,并强转为目标类型 switch (cell.CellType) { case CellType.Boolean: return cell.BooleanCellValue.ChangeType<A>(); case CellType.Error: return ErrorEval.GetText(cell.ErrorCellValue).ChangeType<A>(); case CellType.Numeric: if (DateUtil.IsCellDateFormatted(cell)) { return cell.DateCellValue.ChangeType<A>(); } else { return cell.NumericCellValue.ChangeType<A>(); } case CellType.String: string strValue = cell.StringCellValue; if ( string .IsNullOrEmpty(strValue)) { return default (A); } else { return strValue.ChangeType<A>(); } case CellType.Formula: //公式 switch (cell.CachedFormulaResultType) { case CellType.Boolean: return cell.BooleanCellValue.ChangeType<A>(); case CellType.Error: return ErrorEval.GetText(cell.ErrorCellValue).ChangeType<A>(); case CellType.Numeric: if (DateUtil.IsCellDateFormatted(cell)) { return cell.DateCellValue.ChangeType<A>(); } else { return cell.NumericCellValue.ChangeType<A>(); } case CellType.String: string str = cell.StringCellValue; if ( string .IsNullOrEmpty(str)) { return default (A); } else { return str.ChangeType<A>(); } case CellType.Unknown: case CellType.Blank: default : return default (A); } case CellType.Unknown: case CellType.Blank: default : return default (A); } } } |
2.2、 类型转换
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | /// <summary> /// 调用Convert.ChangeType /// </summary> /// <typeparam name="T">要返回的类型</typeparam> /// <param name="token">待转换的值</param> /// <returns>转换后的值</returns> public static T ChangeType<T>( this object token) { object result = ChangeType(token, typeof (T)); if (result == null ) { return default (T); } return (T)result; } /// <summary> /// 把对象类型转换为指定类型 /// </summary> /// <param name="value">要转换的值</param> /// <param name="targetType">目标类型</param> /// <returns> 转化后的指定类型的对象</returns> public static object ChangeType( this object value, Type targetType) { if (value == null ) { return null ; } if (targetType.IsNullableType()) { targetType = targetType.GetUnderlyingType(); } if (targetType.IsEnum) { return Enum.Parse(targetType, value.ToString()); } if (targetType == typeof (Guid)) { return Guid.Parse(value.ToString()); } return Convert.ChangeType(value, targetType); } |
2.3、 导出代码的主体部分
2.3.1、 策略接口
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | /// <summary> /// 提供每行数据的操作策略(不同的实现方式,达成不同的导入目标,存储目标不必一定是数据库) /// 通过GetValue读取某单元格数据 /// </summary> public interface ExcelImportHelper { /// <summary> /// 判断本行数据是否数据库中已存在(有则会进行修改,没有则会新建) /// </summary> /// <param name="row">Excel中的某行数据</param> /// <returns>不存在则返回null,存在则返回该项数据</returns> T IsExist(IRow row); /// <summary> /// 新数据添加到数据库 /// </summary> /// <param name="model">已完成映射的实体类实例</param> void Add(T model); /// <summary> /// 更新数据库中的已有数据 /// </summary> /// <param name="model">已完成映射的实体类实例</param> void Update(T model); /// <summary> /// 提供从表格行数据映射到实体类的方法(解析失败的时候直接要抛出异常) /// </summary> /// <param name="row">Excel中的某行数据</param> /// <param name="model">新建的或刚从数据库中读取到的实体类实例</param> /// <returns>映射失败或数据为空时返回false,则不会录入数据库</returns> bool Map(IRow row, T model); } |
2.3.2、 其他部分代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 | /// <summary> /// 表格导入数据库 /// 也可以是导入DataTable等形式,只要提供不同的策略 /// </summary> /// <typeparam name="T">Model的类型</typeparam> public class ExcelImport<T> where T : new () { private ExcelImportHelper helper; /// <summary> /// 构造方法 /// </summary> /// <param name="helper">操作策略</param> public ExcelImport(ExcelImportHelper helper) { this .helper = helper; } /// <summary> /// 处理结果 /// </summary> public struct Result { private int num; //导入行数 private int num_add; //新增行数 private int num_edit; //修改行数 /// <summary> /// 导入行数 /// </summary> public int Num { get { return num; } set { num = value; } } /// <summary> /// 新增行数 /// </summary> public int Num_add { get { return num_add; } set { num_add = value; } } /// <summary> /// 修改行数 /// </summary> public int Num_edit { get { return num_edit; } set { num_edit = value; } } /// <summary> /// 构造函数 /// </summary> /// <param name="num">导入行数</param> /// <param name="num_add">新增行数</param> /// <param name="num_edit">修改行数</param> internal Result( int num, int num_add, int num_edit) { this .num = num; this .num_add = num_add; this .num_edit = num_edit; } } #region 导入操作 /// <summary> /// 表格导入到数据库 /// </summary> /// <param name="name">上传控件的name</param> /// <param name="maxLength">最大上传的文件大小(MB)</param> /// <param name="headerRowsNum">表格头部所占的高度</param> /// <returns>表中的数据(只读取表中第一个Sheet)</returns> public Result Inport( string name, int maxLength, int headerRowsNum) { Stream stream = null ; try { HttpContext context = HttpContext.Current; HttpRequest request = context.Request; //验证文件格式大小,返回文件流 stream = GetInputStream(request, name, maxLength); //逐行读取文件内容并执行导入 return Operation(stream, headerRowsNum); } catch (Exception ex) { throw ex; } finally { if (stream != null ) { stream.Close(); stream.Dispose(); } } } private Result Operation(Stream stream, int headerRowsNum) { //操作结果 Result result = new Result(0, 0, 0); //读取表格内容 try { using (stream) { //自动识别.xlsx(XSSFWorkbook),.xls(HSSFWorkbook) IWorkbook workbook = WorkbookFactory.Create(stream); if (workbook.NumberOfSheets == 0) throw new Exception( "报错信息:文件无数据" ); //取第一张表 ISheet sheet = workbook.GetSheetAt(0); if (sheet.PhysicalNumberOfRows == 0) throw new Exception( "报错信息:文件无数据" ); //数据行数 int rowCount = sheet.LastRowNum; //读取表中所有数据 for ( int i = sheet.FirstRowNum + headerRowsNum; i <= rowCount; i++) { IRow row = sheet.GetRow(i); if (row != null ) { result.Num++; T model = helper.IsExist(row); if (model == null ) { result.Num_add++; //新建 model = new T(); bool ok = helper.Map(row, model); if (ok) { helper.Add(model); } } else { result.Num_edit++; //修改 bool ok = helper.Map(row, model); if (ok) { helper.Update(model); } } } } } return result; } catch (Exception ex) { //在异常信息中添加出错位置 throw new Exception( "当前读取到第" + result.Num + "行数据,已添加" + result .Num_add+ "条,已更新" + result.Num_edit + "条," + ex.Message); } } #endregion } |
2.3.3、 调用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | //策略 private class helper : ExcelImport<B_NEWS>.ExcelImportHelper { public void Add(B_NEWS model) { bll.Add(model); } public B_NEWS IsExist(IRow row) { return null ; } public bool Map(IRow row, B_NEWS model) { model.id = row.GetValue< int >(0); model.c_info_title = row.GetValue< string >(1); model.c_info_content = row.GetValue< string >(2); model.d_list_date = row.GetValue<DateTime>(3); model.d_add_date = row.GetValue<DateTime>(4); model.n_is_active = (row.GetValue< bool >(5) ? 1 : 0); model.int1 = row.GetValue< int >(6); return true ; } public void Update(B_NEWS model) { bll.Update(model); } } protected void daoru( object sender, EventArgs e) { new ExcelImport<B_NEWS>( new helper()).Inport( "excel" , 10, 2); } |
3、 表格导出
3.1、 调用者直接接触的操作类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 | public class ExcelExport<T> { /// <summary> /// 每列数据的解析方法 /// </summary> private List<Func<T, object >> func = new List<Func<T, object >>(); private IExcelBuilder builder = new ExcelBuilder(); private string headJson; /// <summary> /// 更换表格建造器 /// </summary> /// <param name="builder">自定义的建造器</param> /// <returns></returns> public ExcelExport<T> ChangeBuilder(IExcelBuilder builder) { this .builder = builder; return this ; } /// <summary> /// 定义列 /// </summary> /// <param name="convert">委托</param> /// <returns></returns> public ExcelExport<T> Column(Func<T, object > convert) { func.Add(convert); return this ; } /// <summary> /// 设置Excel属性 /// </summary> /// <param name="ext"></param> /// <returns></returns> public ExcelExport<T> SetExcelProperty(ExcelProperty ext) { builder.SetWorkbook(ext); return this ; } /// <summary> /// 设置表头 /// </summary> /// <param name="headJson">json形式的表头配置,可以配置多行表头</param> /// <returns></returns> public ExcelExport<T> SetExcelHeadJson( string headJson) { this .headJson = headJson; return this ; } /// <summary> /// 设置表头 /// </summary> /// <param name="path">绝对路径,内里保存json形式的表头配置</param> /// <returns></returns> public ExcelExport<T> SetExcelHeadFromFile( string path) { //读取配置信息 this .headJson = File.ReadAllText(path); return this ; } /// <summary> /// 设置表头 /// </summary> /// <param name="headStr">“序号|订单号|订单状态|客户收货情况反馈|商品评价”的形式,仅支持单行表头</param> /// <returns></returns> public ExcelExport<T> SetExcelHeadStr( string headStr) { string [] head = headStr.Split( '|' ); string headJson = "{\"root\":{\"head\":[" ; for ( int i = 0, length = head.Length; i < length; i++) { if (i > 0) { headJson += "," ; } headJson += "{" ; headJson += "\"text\":\"" + head [i]+ "\"," ; headJson += "\"cell_region\":\"0,0," + i + "," + i + "\"" ; headJson += "}" ; } headJson += "]" ; headJson += "}" ; headJson += "}" ; this .headJson = headJson; return this ; } /// <summary> /// 导出WEB /// </summary> /// <param name="list">数据源(分组后的)</param> /// <param name="fileName">将要下载显示的名字</param> public void ExportToWeb(List<T> list, string fileName) { byte [] buffers = ForExport(list, fileName); ExportToWebExcel(buffers, fileName); } /// <summary> /// 导出到本地存储 /// </summary> /// <param name="list">数据源(分组后的)</param> /// <param name="fileName">文件名称,请自己包含路径,例如C:\\test.xls</param> public void ExportToLocal(List<T> list, string fileName) { byte [] buffers = ForExport(list, fileName); ExportToLocalExcel(buffers, fileName); } #region private private byte [] ForExport(List<T> list, string fileName) { Check(list); //构建表格 IWorkbook workbook = builder.BuildExcel<T>(list, headJson, func.ToArray()); return SaveToByteArray(workbook); } private void Check(List<T> list) { //表头不算必填项 //数据却不能为空 if (list.Count == 0) { throw new Exception( "数据源不能为空" ); } } /// <summary> /// 将WorkBook对象转换成内存流 /// </summary> /// <param name="wookbook"></param> /// <returns></returns> private byte [] SaveToByteArray(IWorkbook wookbook) { using (MemoryStream ms = new MemoryStream()) { wookbook.Write(ms); ms.Flush(); ms.Position = 0; return ms.GetBuffer(); } } /// <summary> /// 本地存储到excel /// </summary> /// <param name="buffers">文件二进制流</param> /// <param name="fileName">文件目录例如C:\\test.xls</param> private void ExportToLocalExcel( byte [] buffers, string fileName) { using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { fs.Write(buffers, 0, buffers.Length); fs.Flush(); } } /// <summary> /// web导出excel /// </summary> /// <param name="buffers">文件二进制流</param> /// <param name="fileName">文件名称</param> private void ExportToWebExcel( byte [] buffers, string fileName) { HttpResponse Response = HttpContext.Current.Response; Response.BufferOutput = false ; Response.ContentEncoding = Encoding.UTF8; Response.Charset = "utf-8" ; Response.ContentType = "application/octet-stream" ; Response.AddHeader( "Content-Disposition" , "attachment;filename=" + fileName); Response.AddHeader( "Content-Length" , buffers.Length.ToString()); Response.Clear(); Response.BinaryWrite(buffers); Response.End(); } #endregion } |
3.2、 表格样式配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | /**为方便样式书写,属性统一用小写*/ /// <summary> /// 根节点, 定义Json表头的格式 /// </summary> public class JsonSetting { /// <summary> /// Root类 /// </summary> public HeadInfo root { get ; set ; } } #region HeadInfo /// <summary> /// 报表表格头部信息 /// </summary> public class HeadInfo { /// <summary> /// 表头的样式 /// </summary> public IList<Attribute4Head> head { get ; set ; } /// <summary> /// 每列单元格的样式 /// </summary> public IList<Attribute4Cell> cell { get ; set ; } /// <summary> /// 表名,默认sheet /// </summary> public string sheet_name { get ; set ; } /// <summary> /// 默认单元格宽度(所有列),不设置则自适应宽度 /// </summary> public int ? default_width { get ; set ; } /// <summary> /// 默认行高度,不设置则使用表格的默认单元格高度 /// </summary> public int ? default_height { get ; set ; } /// <summary> /// 表格边框颜色 例如#000000 /// </summary> public string border_color { get ; set ; } /// <summary> /// 边框风格,none,thin,dashed,dotted,double 默认 thin /// </summary> public string border_style { get ; set ; } /// <summary> /// 分组所在的列(索引从0开始:0,1) /// </summary> public string group_column { get ; set ; } } #endregion /// <summary> /// 关于表头单元格设置属性 /// </summary> public class Attribute4Head : CellAttribute { /// <summary> /// 单元格合并位置,(fromRow,toRow,fromColumn,toColumn) /// </summary> public string cell_region { get ; set ; } /// <summary> /// 显示的文字 /// </summary> public string text { get ; set ; } } /// <summary> /// 关于内容单元格设置属性 /// </summary> public class Attribute4Cell : CellAttribute { /// <summary> /// 数据格式(仅支持Excel的内嵌格式) /// </summary> public string data_format { get ; set ; } /// <summary> /// 宽度(内容单元格宽度覆盖默认宽度) /// </summary> public int ? width { get ; set ; } } #region 公共属性 /// <summary> /// 公共属性 /// </summary> public abstract class CellAttribute { /// <summary> /// 高度(限定行高,一行中只要一个单元格给定高度就可以了,多个单元格都有配置高度时,取最后一次赋值) /// </summary> public int ? height { get ; set ; } /// <summary> /// 水平对齐方式 center,left,right /// </summary> public string align { get ; set ; } /// <summary> /// 垂直对齐方式center,top,bottom /// </summary> public string vertical_align { get ; set ; } /// <summary> /// 背景颜色.例如#000000 /// </summary> public string background_color { get ; set ; } /// <summary> /// 前景颜色.例如#000000(单元格的背景色有bgcolor和forecolor两个背景色,一般使用forecolor) /// </summary> public string foreground_color { get ; set ; } /// <summary> /// 背景填充方式 /// </summary> public string fill_pattern { get ; set ; } /// <summary> /// 字体大小 /// </summary> public short ? font_size { get ; set ; } /// <summary> /// 字体颜色 /// </summary> public string font_color { get ; set ; } /// <summary> /// 字体名称,默认微软雅黑 /// </summary> public string font_name { get ; set ; } /// <summary> ///是否是斜体 /// </summary> public bool ? italic { get ; set ; } /// <summary> /// 是否有中间线 /// </summary> public bool ? strike_out { get ; set ; } /// <summary> /// 是否设置下划线 /// </summary> public bool ? underline { get ; set ; } /// <summary> /// 表头文字是否加粗 /// </summary> public bool ? font_weight { get ; set ; } } |
3.3、 样式解析
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | /// <summary> /// 由字符样式读取相应的枚举值 /// </summary> /// <typeparam name="T">枚举类型</typeparam> /// <param name="str">字符串</param> /// <param name="defaultValue">默认值</param> /// <returns></returns> private T ToEnumValue<T>( string str,T defaultValue) { if ( string .IsNullOrEmpty(str)) { return defaultValue; } //不区分大小写 str = str.Trim().ToLower(); if (str == "0" ) { //输入的是数值 return (T)Enum.Parse( typeof (T), "0" ); } else { var fields = typeof (T).GetFields(BindingFlags.Static | BindingFlags.Public); int val = str.ToInt(); if (val != 0 && val < fields.Length) { //输入的是数值 return (T)Enum.Parse( typeof (T), str); } else { //输入的是枚举名 foreach ( var fi in fields) { var value = fi.GetValue( null ); if (value.ToString().ToLower() == str) { return (T)value; } } return defaultValue; } } } /// <summary> /// 根据十六进制颜色获得颜色索引 /// </summary> /// <param name="workbook"></param> /// <param name="color"></param> /// <returns></returns> private short GetColorIndex( string color, HSSFWorkbook workbook) { Color colour = ColorTranslator.FromHtml(color); if ( this .XlPalette == null ) { this .XlPalette = workbook.GetCustomPalette(); } short s = 0; HSSFColor XlColour = XlPalette.FindColor(colour.R, colour.G, colour.B); if (XlColour == null ) { XlColour = XlPalette.FindSimilarColor(colour.R, colour.G, colour.B); s = XlColour.Indexed; } else { s = XlColour.Indexed; } return s; } |
3.4、 涉及到的枚举类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | 涉及到的枚举类型 public enum FillPattern : short { NoFill = 0, SolidForeground = 1, FineDots = 2, AltBars = 3, SparseDots = 4, ThickHorizontalBands = 5, ThickVerticalBands = 6, ThickBackwardDiagonals = 7, ThickForwardDiagonals = 8, BigSpots = 9, Bricks = 10, ThinHorizontalBands = 11, ThinVerticalBands = 12, ThinBackwardDiagonals = 13, ThinForwardDiagonals = 14, Squares = 15, Diamonds = 16, LessDots = 17, LeastDots = 18 } public enum HorizontalAlignment { General = 0, Left = 1, Center = 2, Right = 3, Fill = 4, Justify = 5, CenterSelection = 6, Distributed = 7 } public enum VerticalAlignment { None = -1, Top = 0, Center = 1, Bottom = 2, Justify = 3, Distributed = 4 } public enum BorderStyle : short { None = 0, Thin = 1, Medium = 2, Dashed = 3, Dotted = 4, Thick = 5, Double = 6, Hair = 7, MediumDashed = 8, DashDot = 9, MediumDashDot = 10, DashDotDot = 11, MediumDashDotDot = 12, SlantedDashDot = 13 } |
3.5、 表格建造器
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | /// <summary> /// 表格建造器 /// </summary> public interface IExcelBuilder { /// <summary> /// 更换样式解析器 /// </summary> IExcelHelper Helper { set ; } /// <summary> /// 构建表格 /// </summary> /// <typeparam name="T">泛型实体</typeparam> /// <param name="list">要转换的列表</param> /// <param name="headJson">json形式的表头配置</param> /// <param name="fieldFuncs">字段委托</param> /// <returns></returns> HSSFWorkbook BuildExcel<T>(List<T> list, string headJson, Func<T, object >[] fieldFuncs); /// <summary> /// 设置excel文件基本属性 /// </summary> /// <param name="ep"></param> void SetWorkbook(ExcelProperty ep); } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 | /// <summary> /// 构建表格 /// </summary> public class ExcelBuilder : IExcelBuilder { #region 字段及属性 private const int RowPerSheet = 65530; //每张sheet最多数据行数 private const int MaxColumnWidth = 50; //自适应列宽时的上限 private string SheetName //表名 { get { return Helper.SheetName; } } private int HeadRow //表头占据的行数 { get { return Helper.HeadRow; } } /// <summary> /// 样式解析器 /// </summary> public IExcelHelper Helper { set { this .helper = value; } private get { return helper; } } private HSSFWorkbook Workbook = new HSSFWorkbook(); //要导出的excel private IExcelHelper helper = new ExcelHelper(); //用于解析属性 private List<GroupClass> GroupColumn = new List<GroupClass>(); //分组的列的配置信息 private List<CellRangeAddress> CellRange = new List<CellRangeAddress>(); //要合并的单元格 private ISheet sheet = null ; //要导出的excel对象中的一个表 #endregion #region IExcelBuilder /// <summary> /// 构建表格 /// </summary> /// <typeparam name="T">泛型实体</typeparam> /// <param name="list">要转换的列表</param> /// <param name="headJson">json形式的表头配置</param> /// <param name="fieldFuncs">字段委托</param> /// <returns></returns> public HSSFWorkbook BuildExcel<T>(List<T> list, string headJson, Func<T, object >[] fieldFuncs) { //获取第一行数据 List< object > dataRow = GetRowData(list[0], fieldFuncs); //解析第一行的数据,初步定下每列的默认样式 Helper.GetStyle(headJson,dataRow, fieldFuncs, Workbook, GroupColumn); //写入数据 Process(list, fieldFuncs); return Workbook; } /// <summary> /// 设置excel文件基本属性 /// </summary> /// <param name="ep"></param> public void SetWorkbook(ExcelProperty ep) { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = ep.Company; dsi.Manager = ep.Manager; dsi.Category = ep.Catagory; Workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = ep.Author; //填加xls文件作者信息 si.ApplicationName = ep.ApplicationName; //填加xls文件创建程序信息 si.LastAuthor = ep.LastAuthor; //填加xls文件最后保存者信息 si.Comments = ep.Comments; //填加xls文件作者信息 si.Title = ep.Title; //填加xls文件标题信息 si.Subject = ep.Subject; //填加文件主题信息 si.Keywords = ep.KeyWord; si.CreateDateTime = DateTime.Now; si.Comments = ep.Comments; Workbook.SummaryInformation = si; } #endregion #region 完成从数据到excel的转化 /// <summary> /// 整合数据 /// </summary> /// <typeparam name="T">泛型实体</typeparam> /// <param name="list">要转换的列表</param> /// <param name="fieldFuncs">字段委托</param> private void Process<T>(List<T> list, Func<T, object >[] fieldFuncs) { //内容行 int rowNum = -1; foreach (T item in list) { rowNum++; if ((rowNum % RowPerSheet) == 0) { //新建sheet NewSheet(rowNum); } //创建新行,获取数据 int rowIndex = rowNum % RowPerSheet + HeadRow; IRow row = sheet.CreateRow(rowIndex); if (Helper.CellHeight.HasValue) { //行高 row.HeightInPoints = Helper.CellHeight.Value; } List< object > dataRow = GetRowData(item, fieldFuncs); //写入 int maxIndex = dataRow.Count; for ( int colIndex = 0; colIndex < maxIndex; colIndex++) { //bool,时间,字符串,数字 var val = dataRow[colIndex]; GroupClass groupClass = CheckGroup(colIndex); if (groupClass == null ) { ICell cell = row.CreateCell(colIndex); WriteCell(row, cell, colIndex, val, fieldFuncs); } else { //分组 MergedRegionInColumn(groupClass, val, fieldFuncs, row, rowIndex, colIndex); } } } //单元格合并 foreach ( var cellRange in CellRange) { sheet.AddMergedRegion(cellRange); //设置边框 ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellRange, Helper.WholeBorderStyle, Helper.WholeBorderColor); } } private void NewSheet( int rowNum) { int sheetIndex = (rowNum / RowPerSheet) + 1; sheet = Workbook.CreateSheet(SheetName + sheetIndex); //表头 Helper.SetHeadAndDefaultStyle(Workbook, sheet); //新表新列 for ( int i = 0, length = GroupColumn.Count; i < length; i++) { GroupColumn[i].Value = null ; GroupColumn[i].Count = 0; GroupColumn[i].cellRange = null ; } } private void MergedRegionInColumn<T>(GroupClass groupClass, object val, Func<T, object >[] fieldFuncs, IRow row, int rowIndex, int colIndex) { object LastRowValue = groupClass.Value; //比对上一行的数据 if (LastRowValue != null && LastRowValue.Equals(val)) { //相等则合并单元格,行数累计 //同一个单元格参与多次单元格合并,最终表格样式会出问题,需要先将旧的合并记录清掉 CellRangeAddress lastCellRange = groupClass.cellRange; if (lastCellRange != null ) { CellRange.Remove(lastCellRange); } //新的合并配置 groupClass.Count++; CellRangeAddress cellRange = new CellRangeAddress(rowIndex - groupClass.Count, rowIndex, colIndex, colIndex); CellRange.Add(cellRange); groupClass.cellRange = cellRange; } else { //不等则更新后续分组的信息 for ( int i = groupClass.Index, length = GroupColumn.Count; i < length; i++) { GroupColumn[i].Value = null ; GroupColumn[i].Count = 0; groupClass.cellRange = null ; } groupClass.Value = val; //创建新单元格,普通写入操作 ICell cell = row.CreateCell(colIndex); WriteCell(row, cell, colIndex, val, fieldFuncs); } } private void WriteCell<T>(IRow row, ICell cell, int colIndex, object val,Func<T, object >[] fieldFuncs) { ColumnInfo cellInfo = Helper.GetColumnInfo(colIndex); if (cellInfo == null ) { cellInfo = Helper.AddColumn<T>(val, fieldFuncs, Workbook, sheet); } SetValue(cell, val); cell.CellStyle = cellInfo.Style; //调整列宽度 if (cellInfo.Width == ColumnInfo.Auto) { int len = Encoding.GetEncoding(936).GetByteCount(val + "" ) + 8; if (len > MaxColumnWidth) { len = MaxColumnWidth; } len = len * 256; if (sheet.GetColumnWidth(colIndex) < len) { sheet.SetColumnWidth(colIndex, len); } } } #region 取值与填值 /// <summary> /// 设置单元格的值 /// </summary> /// <param name="cell"></param> /// <param name="val"></param> private void SetValue(ICell cell, object val) { if (val is bool ) { var data = val.ChangeType< bool >(); cell.SetCellValue(data); } else if (val is DateTime) { var data = val.ChangeType<DateTime>(); cell.SetCellValue(data); } else if (val is double || val is float || val is long || val is int || val is short || val is byte || val is decimal ) { var data = val.ChangeType< double >(); cell.SetCellValue(data); } else { var data = val.ToString(); cell.SetCellValue(data); } } /// <summary> /// 解析数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="item"></param> /// <param name="fieldFuncs"></param> /// <returns></returns> private List< object > GetRowData<T>(T item, Func<T, object >[] fieldFuncs) { List< object > dataRow = new List< object >(); if (fieldFuncs.Length > 0) { for ( int i = 0; i < fieldFuncs.Length; i++) { dataRow.Add(fieldFuncs[i](item)); } } else { if (item is DataRow) { var row = item as DataRow; foreach (DataColumn column in row.Table.Columns) { dataRow.Add(row[column]); } } else if (item is IEnumerable<T>) { foreach ( var val in item as IEnumerable<T>) { dataRow.Add(val); } } else { throw new Exception( "请添加从数据实例映射到表格行的方法" ); } } return dataRow; } #endregion /// <summary> /// 判断当前操作的列是不是要分组 /// </summary> /// <param name="colIndex">列下表</param> /// <returns></returns> private GroupClass CheckGroup( int colIndex) { foreach ( var item in GroupColumn) { if (item.Column == colIndex) { return item; } } return null ; } #endregion } |
3.6、 表格样式解析器
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | /// <summary> /// 表格样式解析器 /// </summary> public interface IExcelHelper { /// <summary> /// 内容行的定高 /// </summary> int ? CellHeight { get ; } /// <summary> /// 表头占据的行数 /// </summary> int HeadRow { get ; } /// <summary> /// 表名 /// </summary> string SheetName { get ; } /// <summary> /// 整个表格border样式 /// </summary> short WholeBorderColor { get ; } /// <summary> /// 整个表格border颜色 /// </summary> BorderStyle WholeBorderStyle { get ; } /// <summary> /// 比第一行数据多出的列 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="val"></param> /// <param name="fieldFuncs"></param> /// <param name="workbook"></param> /// <param name="sheet"></param> ColumnInfo AddColumn<T>( object val, Func<T, object >[] fieldFuncs, HSSFWorkbook workbook, ISheet sheet); /// <summary> /// 读取列配置 /// </summary> /// <param name="index">列下标</param> /// <returns></returns> ColumnInfo GetColumnInfo( int index); /// <summary> /// 计算每一列的默认单元格样式 /// </summary> void GetStyle<T>( string headJson, List< object > dataRow, Func<T, object >[] fieldFuncs, HSSFWorkbook workbook, List<GroupClass> groupList); /// <summary> /// 为新sheet设置表头和默认样式 /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> void SetHeadAndDefaultStyle(HSSFWorkbook workbook, ISheet sheet); } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 | /// <summary> /// 解析表格样式 /// </summary> public class ExcelHelper : IExcelHelper { #region 字段及属性 private IList<Attribute4Head> CellStyle4Head = null ; //头部的配置信息 private IList<Attribute4Cell> CellStyle4Cell = null ; //列配置信息 private IList<ColumnInfo> columnInfo = new List<ColumnInfo>(); //记录解析后得到的每列的宽度及样式 private string sheetName; //表名 private int headRow = 1; //表头占据的行数 private int CellDefaultWidth; //单元格宽度(默认值为ColumnInfo.Auto) private int ? RowDefaultHeight; //默认单元格高度(内容多了会自动撑高) private int ? cellHeight; //内容行的定高 private BorderStyle wholeBorderStyle; //整个表格border样式,默认solid(thin) private short wholeBorderColor; //整个表格border颜色 private HSSFPalette XlPalette = null ; //用于自定义颜色的对象(GetSetting中创建) /// <summary> /// 表名 /// </summary> public string SheetName { get { return sheetName; } } /// <summary> /// 表头占据的行数 /// </summary> public int HeadRow { get { return headRow; } } /// <summary> /// 内容行的定高 /// </summary> public int ? CellHeight { get { return cellHeight; } } /// <summary> /// 整个表格border样式 /// </summary> public BorderStyle WholeBorderStyle { get { return wholeBorderStyle; } } /// <summary> /// 整个表格border颜色 /// </summary> public short WholeBorderColor { get { return wholeBorderColor; } } #endregion #region 列配置操作 /// <summary> /// 比第一行数据多出的列 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="val"></param> /// <param name="fieldFuncs"></param> /// <param name="workbook"></param> /// <param name="sheet"></param> public ColumnInfo AddColumn<T>( object val, Func<T, object >[] fieldFuncs, HSSFWorkbook workbook, ISheet sheet) { ColumnInfo info = GetDefaultCellStyle(val, fieldFuncs, workbook); columnInfo.Add(info); int index = columnInfo.Count - 1; UpdateColumn(index, workbook); //设置列宽 if (info.Width != ColumnInfo.Auto) { sheet.SetColumnWidth(index, info.Width * 256); } return info; } /// <summary> /// 依据配置更新列配置 /// </summary> /// <param name="index">列下标</param> /// <param name="workbook"></param> private void UpdateColumn( int index,HSSFWorkbook workbook) { ColumnInfo columnInfo = GetColumnInfo(index); if (columnInfo != null ) { //列宽先赋值一个默认宽度 columnInfo.Width = this .CellDefaultWidth; ICellStyle cellStyle = columnInfo.Style; //设置单元格边框 cellStyle.BorderRight = cellStyle.BorderLeft = cellStyle.BorderBottom = cellStyle.BorderTop = WholeBorderStyle; cellStyle.BottomBorderColor = cellStyle.RightBorderColor = cellStyle.LeftBorderColor = cellStyle.TopBorderColor = WholeBorderColor; //更新样式 Attribute4Cell style = null ; if (CellStyle4Cell != null && CellStyle4Cell.Count > index) { style = CellStyle4Cell[index]; } if (style != null ) { //数据格式 if (! string .IsNullOrEmpty(style.data_format)) { cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(style.data_format); } //列宽 if (style.width.HasValue) { columnInfo.Width = style.width.Value; } //高度 if (style.height.HasValue) { this .cellHeight = style.height.Value; } //其他属性 AdaperCellStyle(cellStyle, style, workbook); } } } /// <summary> /// 读取列配置 /// </summary> /// <param name="index">列下标</param> /// <returns></returns> public ColumnInfo GetColumnInfo( int index) { if (columnInfo.Count > index) { return columnInfo[index]; } return null ; } #endregion #region 解析样式 /// <summary> /// 计算每一列的默认单元格样式 /// </summary> public void GetStyle<T>( string headJson, List< object > dataRow, Func<T, object >[] fieldFuncs, HSSFWorkbook workbook, List<GroupClass> groupList) { // 读取第一行数据,为每列解析默认样式,依序加入cellStyle4Cell GetDefaultCellStyle(dataRow, fieldFuncs, workbook); //解析headJson GetSetting(headJson, workbook, groupList); } /// <summary> /// 依据一行示例数据为每列解析默认样式,依序加入cellStyle4Cell /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataRow"></param> /// <param name="fieldFuncs"></param> /// <param name="workbook"></param> private void GetDefaultCellStyle<T>(List< object > dataRow, Func<T, object >[] fieldFuncs, IWorkbook workbook) { for ( int colIndex = 0; colIndex < dataRow.Count; colIndex++) { var val = dataRow[colIndex]; ColumnInfo info = GetDefaultCellStyle(val, fieldFuncs, workbook); columnInfo.Add(info); } } /// <summary> /// //依据每个单元格对应的数据的数据类型计算出每列的默认样式,列的宽度设为自动 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="val"></param> /// <param name="fieldFuncs"></param> /// <param name="workbook"></param> /// <returns></returns> private ColumnInfo GetDefaultCellStyle<T>( object val, Func<T, object >[] fieldFuncs, IWorkbook workbook) { ICellStyle cellStyle; if (val is bool ) { cellStyle = GetCellStyle(workbook, "默认" ); } else if (val is DateTime) { var data = val.ChangeType<DateTime>(); if (data.Hour == 0 && data.Minute == 0 && data.Second == 0) { cellStyle = GetCellStyle(workbook, "时间" ); } else { cellStyle = GetCellStyle(workbook, "时间2" ); } } else if (val is double || val is float || val is long || val is int || val is short || val is byte || val is decimal ) { var data = val.ChangeType< double >(); if ((val + "" ).IndexOf( "." ) > 0) { cellStyle = GetCellStyle(workbook, "数字" ); } else { cellStyle = GetCellStyle(workbook, "数字2" ); } } else { cellStyle = GetCellStyle(workbook, "默认" ); } return new ColumnInfo() { Style = cellStyle, Width = ColumnInfo.Auto }; } /// <summary> /// 解析json配置 /// </summary> /// <param name="headJson">json字符串</param> /// <param name="workbook"></param> /// <param name="groupList"></param> private void GetSetting( string headJson, HSSFWorkbook workbook, List<GroupClass> groupList) { JsonSetting T = headJson.ToObject<JsonSetting>(); //DebugUtils.Log(new MT.Business.Json().Convert2Json(T)); //========== //分组的列信息 GetGroupCellSetting(T.root.group_column, groupList); //表名 this .sheetName = T.root.sheet_name; if ( string .IsNullOrEmpty( this .sheetName)) { this .sheetName = "sheet" ; } //默认单元格高度 this .RowDefaultHeight = T.root.default_height; //边框样式 this .wholeBorderStyle = ToEnumValue<BorderStyle>(T.root.border_style, BorderStyle.Thin); //边框颜色 this .wholeBorderColor = GetColorIndex(T.root.border_color, workbook); //表头样式 CellStyle4Head = T.root.head; //单元格样式 CellStyle4Cell = T.root.cell; //默认单元格宽度 this .CellDefaultWidth = (T.root.default_width.HasValue ? T.root.default_width.Value : ColumnInfo.Auto); //修正每列的配置信息 for ( int i = 0, length = this .columnInfo.Count; i < length; i++) { UpdateColumn(i, workbook); } } /// <summary> /// 初始化分组统计 /// </summary> private void GetGroupCellSetting( string groupColumn, List<GroupClass> list) { int [] group = ( string .IsNullOrEmpty(groupColumn) ? null : ToIntArray(groupColumn.Split( ',' ))); if ( group != null ) { //依列下标排序 group = group .OrderBy(x => x).ToArray(); for ( int i = 0, length = group .Length; i < length; i++) { list.Add( new GroupClass { Column = group [i], Count = 0, Index = i }); } } } #endregion #region 为新sheet设置表头和默认样式等 /// <summary> /// 设置表头和默认样式 /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> public void SetHeadAndDefaultStyle(HSSFWorkbook workbook, ISheet sheet) { sheet.DisplayGridlines = true ; if ( this .CellDefaultWidth != ColumnInfo.Auto) { //默认单元格宽度 sheet.DefaultColumnWidth = this .CellDefaultWidth; } if ( this .RowDefaultHeight.HasValue) { //设置表格默认行高 sheet.DefaultRowHeight = ( short )( this .RowDefaultHeight.Value * 20); } //给定的列宽 for ( int i = 0, length = this .columnInfo.Count; i < length; i++) { var info = this .columnInfo[i]; if (info.Width != ColumnInfo.Auto) { sheet.SetColumnWidth(i, info.Width * 256); } } //写入表头 SetHead(workbook,sheet); } /// <summary> /// 设置表头 /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> private void SetHead(HSSFWorkbook workbook, ISheet sheet) { List<IRow> rowList = new List<IRow>(); //创建行 for ( int i = 0; i < HeadRow; i++) { IRow temp = sheet.CreateRow(i); rowList.Add(temp); } for ( int i = 0; i < CellStyle4Head.Count; i++) { Attribute4Head attribute4Head = CellStyle4Head[i]; int [] c = ToIntArray(attribute4Head.cell_region.Split( ',' )); //计算title要插入的位置的索引 int txtIndex = -1; int txtRow = -1; if ((c[0] <= c[1] && c[2] <= c[3])) { #region 写入表头 //表头所占的列数 int _headRow = c[1] + 1; if (headRow<= _headRow) { //创建行 for ( int j = headRow; j < _headRow; j++) { IRow temp = sheet.CreateRow(j); rowList.Add(temp); } headRow = _headRow; } //@ 合并单元格 if (c[0] < c[1] || c[2] < c[3]) { //例如1,1,2,2 第二行中的第3列,例如1,1,2,7 第二行中的(第3列到8列),合并列 CellRangeAddress cellRange = new CellRangeAddress(c[0], c[1], c[2], c[3]); sheet.AddMergedRegion(cellRange); //设置边框 ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellRange, WholeBorderStyle, WholeBorderColor); } //@ 填充内容 txtIndex = c[2]; txtRow = c[0]; ICell cell1 = rowList[txtRow].CreateCell(txtIndex); cell1.SetCellValue(attribute4Head.text); cell1.CellStyle = GetHeadCellStyle(workbook, attribute4Head); //高度 if (attribute4Head.height.HasValue) { rowList[txtRow].HeightInPoints = attribute4Head.height.Value; } #endregion } } } private ICellStyle GetHeadCellStyle(HSSFWorkbook workbook,Attribute4Head cellStyle) { ICellStyle headStyle = workbook.CreateCellStyle(); //设置单元格边框 headStyle.BorderRight = headStyle.BorderLeft = headStyle.BorderBottom = headStyle.BorderTop = WholeBorderStyle; headStyle.BottomBorderColor = headStyle.RightBorderColor = headStyle.LeftBorderColor = headStyle.TopBorderColor = WholeBorderColor; //默认样式 //水平对齐 headStyle.Alignment = HorizontalAlignment.Center; //垂直对齐 headStyle.VerticalAlignment = VerticalAlignment.Center; IFont font = workbook.CreateFont(); //字体大小 font.FontHeightInPoints = 12; //字体颜色 font.Color = 8; //默认黑色 //字体名称 font.FontName = "微软雅黑" ; //加粗 font.Boldweight = ( short )FontBoldWeight.Bold; headStyle.SetFont(font); AdaperCellStyle(headStyle, cellStyle, workbook); return headStyle; } #endregion #region 字符串To 样式 /// <summary> /// 由字符样式读取响应的枚举值 /// </summary> /// <typeparam name="T">枚举类型</typeparam> /// <param name="str">字符串</param> /// <param name="defaultValue">默认值</param> /// <returns></returns> private T ToEnumValue<T>( string str,T defaultValue) { if ( string .IsNullOrEmpty(str)) { return defaultValue; } //不区分大小写 str = str.Trim().ToLower(); if (str == "0" ) { //输入的是数值 return (T)Enum.Parse( typeof (T), "0" ); } else { var fields = typeof (T).GetFields(BindingFlags.Static | BindingFlags.Public); int val = str.ToInt(); if (val != 0 && val < fields.Length) { //输入的是数值 return (T)Enum.Parse( typeof (T), str); } else { //输入的是枚举名 foreach ( var fi in fields) { var value = fi.GetValue( null ); if (value.ToString().ToLower() == str) { return (T)value; } } return defaultValue; } } } /// <summary> /// 根据十六进制颜色获得颜色索引 /// </summary> /// <param name="workbook"></param> /// <param name="color"></param> /// <returns></returns> private short GetColorIndex( string color, HSSFWorkbook workbook) { Color colour = ColorTranslator.FromHtml(color); if ( this .XlPalette == null ) { this .XlPalette = workbook.GetCustomPalette(); } short s = 0; HSSFColor XlColour = XlPalette.FindColor(colour.R, colour.G, colour.B); if (XlColour == null ) { XlColour = XlPalette.FindSimilarColor(colour.R, colour.G, colour.B); s = XlColour.Indexed; } else { s = XlColour.Indexed; } return s; } #endregion #region utils /// <summary> /// 依据用户json配置调整样式(公共部分) /// </summary> /// <param name="defaultCellStyle">默认样式</param> /// <param name="cellStyle">json传入的配置信息</param> /// <param name="workbook"></param> private void AdaperCellStyle(ICellStyle defaultCellStyle, CellAttribute cellStyle, HSSFWorkbook workbook) { //水平对齐 if (! string .IsNullOrEmpty(cellStyle.align)) { defaultCellStyle.Alignment = ToEnumValue<HorizontalAlignment>(cellStyle.align, HorizontalAlignment.Center); } //垂直对齐 if (! string .IsNullOrEmpty(cellStyle.vertical_align)) { defaultCellStyle.VerticalAlignment = ToEnumValue<VerticalAlignment>(cellStyle.vertical_align, VerticalAlignment.Center); } //背景颜色 if (! string .IsNullOrEmpty(cellStyle.background_color) || ! string .IsNullOrEmpty(cellStyle.foreground_color)) { //重复设置背景色,有时候颜色会叠加,这里并不是简单的更换新值…… //defaultCellStyle.FillForegroundColor = HSSFColor.White.Index; //defaultCellStyle.FillBackgroundColor = HSSFColor.Maroon.Index; defaultCellStyle.FillPattern = ToEnumValue<FillPattern>(cellStyle.fill_pattern, FillPattern.SolidForeground); if (! string .IsNullOrEmpty(cellStyle.background_color)) { //背景色 defaultCellStyle.FillBackgroundColor = GetColorIndex(cellStyle.background_color, workbook); } if (! string .IsNullOrEmpty(cellStyle.foreground_color)) { //前景色 defaultCellStyle.FillForegroundColor = GetColorIndex(cellStyle.foreground_color, workbook); } } IFont font = defaultCellStyle.GetFont(workbook); //字体大小 if (cellStyle.font_size.HasValue) { font.FontHeightInPoints = cellStyle.font_size.Value; //设置字体大小 } //字体颜色 if (! string .IsNullOrEmpty(cellStyle.font_color)) { font.Color = GetColorIndex(cellStyle.font_color, workbook); } //字体名称 if (! string .IsNullOrEmpty(cellStyle.font_name)) { font.FontName = cellStyle.font_name; } //是否是斜体 if (cellStyle.italic.HasValue) { font.IsItalic = cellStyle.italic.Value ? true : false ; } //是否有中间线 if (cellStyle.strike_out.HasValue) { font.IsStrikeout = cellStyle.strike_out.Value ? true : false ; } //是否设置下划线 if (cellStyle.underline.HasValue) { font.Underline = cellStyle.underline.Value ? FontUnderlineType.Single : FontUnderlineType.None; } //是否加粗 if (cellStyle.font_weight.HasValue) { font.Boldweight = cellStyle.font_weight.Value ? ( short )FontBoldWeight.Bold : ( short )FontBoldWeight.None; } } /// <summary> /// 获取默认样式 /// </summary> /// <param name="wb">IWorkbook</param> /// <param name="str">标识</param> /// <returns></returns> private ICellStyle GetCellStyle(IWorkbook wb, string str) { ICellStyle cellStyle = wb.CreateCellStyle(); IFont font = wb.CreateFont(); //字体 font.FontName = "微软雅黑" ; //居中对齐 cellStyle.Alignment = HorizontalAlignment.Center; //垂直对齐 cellStyle.VerticalAlignment = VerticalAlignment.Center; //自动换行 cellStyle.WrapText = true ; //缩进 cellStyle.Indention = 0; IDataFormat datastyle = wb.CreateDataFormat(); switch (str) { /* HSSFDataFormat.GetBuiltinFormat("0.00") 与 datastyle.GetFormat("yyyy/mm/dd"): 前者调用的是Excel的内嵌格式 */ case "时间" : cellStyle.DataFormat = datastyle.GetFormat( "yyyy/mm/dd" ); cellStyle.SetFont(font); break ; case "时间2" : cellStyle.DataFormat = datastyle.GetFormat( "yyyy/mm/dd HH:mm" ); cellStyle.SetFont(font); break ; case "数字" : cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat( "0.00" ); cellStyle.SetFont(font); break ; case "数字2" : cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat( "0" ); cellStyle.SetFont(font); break ; case "钱" : cellStyle.DataFormat = datastyle.GetFormat( "¥#,##0" ); cellStyle.SetFont(font); break ; case "url" : font.Underline = FontUnderlineType.Single; font.Color = HSSFColor.OliveGreen.Blue.Index; font.IsItalic = true ; cellStyle.SetFont(font); cellStyle.Alignment = HorizontalAlignment.Center; break ; case "百分比" : cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat( "0.00%" ); cellStyle.SetFont(font); break ; case "中文大写" : cellStyle.DataFormat = datastyle.GetFormat( "[DbNum2][$-804]0" ); cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.SetFont(font); break ; case "科学计数法" : cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat( "0.00E+00" ); cellStyle.SetFont(font); break ; case "默认" : cellStyle.SetFont(font); cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat( "@" ); //全部做字符串处理 break ; } return cellStyle; } /// <summary> /// 字符串数组to整型数组 /// </summary> /// <param name="region">字符串数组</param> /// <returns></returns> private int [] ToIntArray( string [] region) { ArrayList aList = new ArrayList(); foreach ( string i in region) { aList.Add(Convert.ToInt32(i)); } return ( int [])aList.ToArray( typeof ( int )); } #endregion } |
3.7、 调用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | public class ExamEntity { public int Id { get ; set ; } /// <summary> /// 学生姓名 /// </summary> public string Name { get ; set ; } public bool Sex { get ; set ; } /// <summary> /// 考试时间,每次考试时间 /// </summary> public DateTime ExamTime { get ; set ; } /*各科成绩,语数英 物化生*/ public int Chinese { get ; set ; } public int English { get ; set ; } public int Math { get ; set ; } public int Physics { get ; set ; } public int Chemistry { get ; set ; } public int Biology { get ; set ; } /// <summary> /// 总分 /// </summary> public int SumCount { get ; set ; } } public List<ExamEntity> exam = new List<ExamEntity> { new ExamEntity{Id=1,Name= "张三" ,ExamTime=Convert.ToDateTime( "2013-03-01 08:30:00" ),Chinese=122,English=111,Math=90,Physics=50,Chemistry=80,Biology=40,SumCount=493}, new ExamEntity{Id=1,Name= "张三" ,ExamTime=Convert.ToDateTime( "2013-04-01 08:30:00" ),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529}, new ExamEntity{Id=1,Name= "张三" ,ExamTime=Convert.ToDateTime( "2013-05-01 08:30:00" ),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529}, new ExamEntity{Id=2,Name= "李四" ,ExamTime=Convert.ToDateTime( "2013-03-01 08:30:00" ),Chinese=122,English=111,Math=90,Physics=50,Chemistry=80,Biology=40,SumCount=493}, new ExamEntity{Id=2,Name= "李四" ,ExamTime=Convert.ToDateTime( "2013-04-01 08:30:00" ),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529}, new ExamEntity{Id=2,Name= "李四" ,ExamTime=Convert.ToDateTime( "2013-05-01 08:30:00" ),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529}, new ExamEntity{Id=3,Name= "王五" ,ExamTime=Convert.ToDateTime( "2013-03-01 08:30:00" ),Chinese=122,English=111,Math=90,Physics=50,Chemistry=80,Biology=40,SumCount=493}, new ExamEntity{Id=3,Name= "王五" ,ExamTime=Convert.ToDateTime( "2013-04-01 08:30:00" ),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529}, new ExamEntity{Id=4,Name= "陈流" ,ExamTime=Convert.ToDateTime( "2013-05-01 08:30:00" ),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529} }; protected void daochu( object sender, EventArgs e) { string json = @"{ 'root':{'sheet_name':'学生成绩','default_width':12,'group_column': '0,1,2,3','head':[ { 'text':'编号', 'height':54, 'cell_region':'0,2,0,0' }, { 'text':'学生姓名', 'cell_region':'0,2,1,1' }, { 'text':'性别', 'cell_region':'0,2,2,2' }, { 'text':'考试时间', 'cell_region':'0,2,3,3' }, { 'text':'主科目', 'height':18, 'cell_region':'0,0,4,6' }, { 'text':'副科目', 'cell_region':'0,1,7,9' }, { 'text':'必修课', 'height':18, 'cell_region':'1,1,4,5' }, { 'text':'选修课', 'cell_region':'1,1,6,6' }, { 'text':'语文', 'height':18, 'cell_region':'2,2,4,4' }, { 'text':'数学', 'cell_region':'2,2,5,5' }, { 'text':'英语', 'cell_region':'2,2,6,6' }, { 'text':'物理', 'cell_region':'2,2,7,7' }, { 'text':'化学', 'cell_region':'2,2,8,8' }, { 'text':'生物', 'cell_region':'2,2,9,9' }, { 'text':'总分', 'cell_region':'0,2,10,10' } ], 'cell':[{},{},{},{'width':30}] } }" ; //处理数据源,对要分组的几列进行排序(order by Name,ExamTime) List<ExamEntity> exams = exam.OrderByDescending(x => x.Name).ThenByDescending(x => x.ExamTime).ToList<ExamEntity>(); new ExcelExport<ExamEntity>() //.SetExcelHeadStr(headStr) .SetExcelHeadJson(json) .Column(a => a.Id) .Column(a => a.Name) .Column(a => a.Sex ? "男" : "女" ) .Column(a => a.ExamTime) .Column(a => a.Chinese) .Column(a => a.Math) .Column(a => a.English) .Column(a => a.Physics) .Column(a => a.Chemistry) .Column(a => a.Biology) .Column(a => a.SumCount) .ExportToWeb(exams, "daochu.xls" ); } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 趁着过年的时候手搓了一个低代码框架
· 推荐一个DeepSeek 大模型的免费 API 项目!兼容OpenAI接口!