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
操作类：
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; } }
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;
}
// 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;
}
}
//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;
}
}
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 定义：
public enum AdvertiseType:Int32
{
/// <summary>
/// Search
/// </summary>
[Display(Name = "Search")]//important
Search = 1,
/// <summary>
/// Display
/// </summary>
[Display(Name = "Display")]
Display = 2,
}
type T 的定义
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; }
}
应用：
//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);
