EPPlus实战篇——Excel写入
.net core 项目
可以向excel写入任何类型(T)的数据,只要T中的field的[Display(Name = "1233", Description = "#,##0.00")]:name==excel column header's name ,dicription==excel cell's formate
引用的nuget包:
1.EPPlus.Core
2. System.ComponentModel.Annotations
操作类:
1 2 3 4 5 6 7 8 9 10 | public class ExcelWriteReadAccordingDisplayService<T> : IExcelWriteService<T> where T : class { ILogBase _logger; Dictionary< int , PropertyInfo> _columnIndexDicForProperInfo; Dictionary< int , DisplayAttribute> _columnIndexDicForDisplayAttr; public ExcelWriteReadAccordingDisplayService(ILogBase logBase) { _logger = logBase; } } |
class ExcelWriteReadAccordingDisplayService 中的方法:
main method:
public bool WriteData(List<T> data, string excelPath, string sheetName) { try { if (!WriteRequestCheck(excelPath)) { _logger.Warn($"WriteData Request not valid.excelPath :{excelPath},sheetName:{sheetName}"); return false; } if (string.IsNullOrWhiteSpace(sheetName)) { sheetName = DateTime.Now.ToString("yyyyMM"); } //set sheet style Func<ExcelWorksheet, Color, bool> SetHeadStyle = (targetSheet, backgroundColor) => { using (ExcelRange rng = targetSheet.Cells[1, 1, 1, targetSheet.Dimension.Columns]) { rng.Style.Font.Bold = true; rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rng.Style.Fill.PatternType = ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(backgroundColor); } targetSheet.Row(1).Height = targetSheet.Row(1).Height * 1.4f; return true; }; Func<ExcelWorksheet, bool> SetAllCellsStyle = (targetSheet) => { using (ExcelRange rng = targetSheet.Cells) { rng.AutoFitColumns(); } return true; }; using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet sheet = AddSheet(package, sheetName); //write data to excel GetColumnIndexDic(sheet); WriteContent(data, sheet); //set style for excel SetHeadStyle(sheet, Color.FromArgb(255, 242, 204)); SetAllCellsStyle(sheet); //save package.SaveAs(new FileInfo(excelPath)); } return true; }catch(Exception ex) { _logger.Error($"ExcelWrite data exception :{ex.ToString()},excel:{excelPath},data:{JsonConvert.SerializeObject(data)}"); throw ex; } }
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 | public byte [] WriteData(List<T> data, string sheetName) { try { if ( string .IsNullOrWhiteSpace(sheetName)) { sheetName = DateTime.Now.ToString( "yyyyMM" ); } if (data == null || data.Count() == 0) { _logger.Warn($ "WriteData Request not valid. request.data:{JsonConvert.SerializeObject(data)}" ); return null ; } return SaveData(data, sheetName); } catch (Exception ex) { _logger.Error($ "ExcelWrite data exception :{ex.ToString()},sheetName:{sheetName},data:{JsonConvert.SerializeObject(data)}" ); throw ex; } } private bool WriteRequestCheck( string excelPath) { Func< string , bool > pathValidCheck = (path) => { if ( string .IsNullOrWhiteSpace(path) || !Path.IsPathRooted(path)) return false ; return true ; }; if (!pathValidCheck(excelPath)) { _logger.Warn($ "excelPath not valid,path :{excelPath}" ); return false ; } return true ; } private ExcelWorksheet AddSheet(ExcelPackage package, string sheetName) { if (package.Workbook.Worksheets[sheetName] != null ) { package.Workbook.Worksheets.Delete(sheetName); } var sheet = package.Workbook.Worksheets.Add(sheetName); return sheet; } private byte [] SaveData(List<T> data, string sheetName) { byte [] excelContent = new byte [] { }; using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet sheet = AddSheet(package, sheetName); //write data to excel GetColumnIndexDic(sheet); WriteContent(data, sheet); //set style for excel SetHeadStyle(sheet, Color.FromArgb(255, 242, 204)); SetAllCellsStyle(sheet); //save using (System.IO.MemoryStream outStream = new System.IO.MemoryStream()) { package.SaveAs(outStream); excelContent = outStream.ToArray(); } } return excelContent; } |
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 | // type T => init excel's header && get some setting info to write excel cell private void GetColumnIndexDic(ExcelWorksheet sheet, bool writeHeader= true ) { try { var typeOfObject = typeof (T); var pds = typeOfObject.GetProperties(); if (pds == null ) { _logger.Warn($ "no PropertyInfos can get from class Type:{typeOfObject.FullName} " ); return ; } //Dictionary<excel column index,T's PropertyInfo> _columnIndexDicForProperInfo = new Dictionary< int , PropertyInfo>(); //Dictionary<excel column index,T's PropertyInfo's DisplayAttribute(its name=excle header name,its discription =excel cell style formate)> _columnIndexDicForDisplayAttr = new Dictionary< int , DisplayAttribute>(); int column = 1; int row = 1; foreach ( var p in pds) { var attr = p.GetCustomAttribute( typeof (DisplayAttribute)) as DisplayAttribute; if (attr != null ) { _columnIndexDicForDisplayAttr.Add(column, attr); } else { _logger.Warn($ "no DisplayAttribute can get from PropertyInfo:(class:{typeOfObject.FullName},property:{p.Name})" ); } if (writeHeader) { sheet.Cells[row, column].Value = attr == null ? p.Name : attr.Name; } _columnIndexDicForProperInfo.Add(column, p); column++; } if (_columnIndexDicForProperInfo.Count == 0) { _logger.Warn($ "no _columnIndexDicForProperInfo can get from type:{typeOfObject.FullName}" ); } if (_columnIndexDicForDisplayAttr.Count == 0) { _logger.Warn($ "no _columnIndexDicForDisplayAttr can get from type:{typeOfObject.FullName}" ); } } catch (Exception ex) { _logger.Error($ "ExcelWrite-GetColumnIndexDic exception :{ex.ToString()}" ); throw ex; } } |
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 | //fill sheet content according list data private void WriteContent(List<T> data,ExcelWorksheet sheet, int startRow=2) { try { PropertyInfo propertyTemp = null ; DisplayAttribute displayAttrTemp = null ; int column = 1; int row = startRow; Dictionary< string , Dictionary< string , DisplayAttribute>> enumDic = new Dictionary< string , Dictionary< string , DisplayAttribute>>(); foreach ( var eachData in data) { column = 1; foreach ( var eachColumn in _columnIndexDicForProperInfo) { if (!_columnIndexDicForProperInfo.ContainsKey(column)) { _logger.Warn($ "no PropertyInfos can get from _columnIndexDic. current column:{column},_columnIndexDic:{JsonConvert.SerializeObject(_columnIndexDicForProperInfo)} " ); continue ; } propertyTemp = _columnIndexDicForProperInfo[column]; var cellValue = propertyTemp.GetValue(eachData); if (cellValue != null ) { FormatCellValue( ref enumDic, ref cellValue, propertyTemp.PropertyType); } sheet.Cells[row, column].Value = cellValue == null ? "" : cellValue; if (_columnIndexDicForDisplayAttr.ContainsKey(column)) { displayAttrTemp = _columnIndexDicForDisplayAttr[column]; var styleFormate = displayAttrTemp.Description; if (! string .IsNullOrWhiteSpace(styleFormate)) { sheet.Cells[row, column].Style.Numberformat.Format = styleFormate; } } column++; } row++; } } catch (Exception ex) { _logger.Error($ "ExcelWrite-WriteContent exception :{ex.ToString()},data:{JsonConvert.SerializeObject(data)}" ); throw ex; } } // formate cell value according type T‘s property’s DisplayAttribute private void FormatCellValue( ref Dictionary< string , Dictionary< string , DisplayAttribute>> enumDic, ref object cellValue, Type propertyTypeOfCell) { if (cellValue == null ) return ; if (propertyTypeOfCell.IsEnum) { Dictionary< string , DisplayAttribute> enumDicTemp; if (enumDic.ContainsKey(propertyTypeOfCell.FullName)) { enumDicTemp = enumDic[propertyTypeOfCell.FullName]; } else { enumDicTemp = GetEnumNameDicForDisplayAttr(propertyTypeOfCell); enumDic.Add(propertyTypeOfCell.FullName, enumDicTemp); } if (enumDicTemp != null ) { if (enumDicTemp.ContainsKey(cellValue.ToString())) { cellValue = enumDicTemp[cellValue.ToString()].Name; return ; } else { _logger.Warn($ "no enum value can get from enum dictionary:{JsonConvert.SerializeObject(enumDicTemp.Keys)} , enum Type:{propertyTypeOfCell.FullName},cell value:{cellValue}" ); } } else { _logger.Warn($ "no enum dictionary can get from enum Type:{propertyTypeOfCell.FullName} " ); } return ; } /*if (propertyTypeOfCell == typeof(int)) { cellValue = Convert.ToInt32(cellValue); return; } if (propertyTypeOfCell == typeof(long)) { cellValue = Convert.ToInt64(cellValue); return; } if (propertyTypeOfCell == typeof(DateTime)) { cellValue = Convert.ToDateTime(cellValue); return; } if (propertyTypeOfCell == typeof(string)) { cellValue = cellValue.ToString(); return; }*/ return ; } // get enum property Dic<enum value,DisplayAttribute> =>show in excel cell private Dictionary< string , DisplayAttribute> GetEnumNameDicForDisplayAttr(Type enumClassType) { try { var result = new Dictionary< string , DisplayAttribute>(); if (enumClassType.IsEnum) { var enumValues = enumClassType.GetEnumValues(); foreach ( var value in enumValues) { MemberInfo memberInfo = enumClassType.GetMember(value.ToString()).First(); var descriptionAttribute = memberInfo.GetCustomAttribute<DisplayAttribute>(); if (descriptionAttribute != null ) { var enumString = Enum.GetName(enumClassType, value); result.Add(value.ToString(), descriptionAttribute); } } if (result == null || result.Count() == 0) { _logger.Warn($ "no EnumDic can get from enum Type:{enumClassType.FullName} " ); } } return result; } catch (Exception ex) { _logger.Error($ "ExcelWrite-GetEnumNameDicForDisplayAttr exception :{ex.ToString()},Type:{enumClassType.FullName}" ); throw ex; } } |
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 | private bool WriteRequestCheck( string excelPath) { Func< string , bool > pathValidCheck = (path) => { if ( string .IsNullOrWhiteSpace(path) || !Path.IsPathRooted(path)) return false ; return true ; }; if (!pathValidCheck(excelPath)) { _logger.Warn($ "excelPath not valid,path :{excelPath}" ); return false ; } return true ; } private ExcelWorksheet AddSheet(ExcelPackage package, string sheetName) { if (package.Workbook.Worksheets[sheetName] != null ) { package.Workbook.Worksheets.Delete(sheetName); } var sheet = package.Workbook.Worksheets.Add(sheetName); return sheet; } |
enum 定义:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | public enum AdvertiseType:Int32 { /// <summary> /// Search /// </summary> [Display(Name = "Search" )] //important Search = 1, /// <summary> /// Display /// </summary> [Display(Name = "Display" )] Display = 2, } |
type T 的定义
1 2 3 4 5 6 7 8 9 10 11 12 13 | public class FinancialBillEntity { [Display(Name = "类型" )] public AdvertiseType AdvertiseType{ get ; set ; } [Display(Name = "总金额" , Description = "#,##0.00" )] //name==excel header name;discription=cell style formate public decimal TotalAdivitisingCost{ get ; set ; } [Display(Name = "赠送" , Description = "#,##0.00" )] public decimal PromotionAmountUSD { get ; set ; } } |
应用:
1 2 3 4 5 6 7 8 9 | //register interface services.RegisterServiceR<IExcelWriteService<FinancialBillEntity>, ExcelWriteReadAccordingDisplayService<FinancialBillEntity>>(lifeStyle); //get interface instance var excelWriteService= services.GetInstance<IExcelWriteService<FinancialBillEntity>>(); //execute interface method bool result=_excelWriteService.WriteData(financeBills,cmdOptions.OutputFinanceBillExcelPath,cmdOptions.OutputFinanceBillSheetName); |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?