.net core 项目
可以从excel读取任何类型(T)的数据，只要T中的field的[Display(Name = "1233")]中的name==excel column header's name
引用的nuget包：
1.EPPlus.Core
2. System.ComponentModel.Annotations
//类定义
public class ExcelReadServiceAccordingDisplayAttr<T> : IExcelReadService<T> where T : new()
{
ILogBase _logger;
static Dictionary<string, PropertyInfo> _displayAttrDic;
public ExcelReadServiceAccordingDisplayAttr(ILogBase logBase)
{
_logger = logBase;
}
}
class ExcelReadServiceAccordingDisplayAttr<T>中方法：
private List<T> GetData(string excelPath, string sheetName = "", int sheetIndex = 0)
{
try
{
FileInfo existingFile = new FileInfo(excelPath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet sheet = GetSheet(package, sheetName, sheetIndex);
if (sheet == null) return null;
//获取不需要读取的column
List<long> excluedeColumns = GetExcludeCloumns(sheet);
//根据excelheader来获取type T数据对象的列字典
Dictionary<int, PropertyInfo> columnIndexDic = GetColumnIndexDicFromExcelHeader(sheet, excluedeColumns);
//读取excel数据，填充List<T>
List<T> result = GetDatesFromContent(sheet, columnIndexDic, excluedeColumns);
return result;
}
}catch(Exception ex)
{
_logger.Error($"get data from excel exception :{ex.ToString()},excel:{excelPath},sheetIndex:{sheetIndex},entityType:{typeof(T).FullName}");
throw ex;
}
}
private List<T> GetDateFromSheet(ExcelWorksheet sheet)
{
if (sheet == null) return null;
List<long> excluedeColumns = GetExcludeCloumns(sheet);
//根据excelheader来获取type T数据对象的列字典
Dictionary<int, PropertyInfo> columnIndexDic = GetColumnIndexDicFromExcelHeader(sheet, excluedeColumns);
//读取excel数据，填充List<T>
List<T> result = GetDatesFromContent(sheet, columnIndexDic);
return result;
}
private List<T> GetData(byte[] excelContent, string sheetName = "", int sheetIndex = 0)
{
try
{
using (Stream stream = new MemoryStream(excelContent))
{
using (ExcelPackage package = new ExcelPackage(stream))
{
ExcelWorksheet sheet = GetSheet(package, sheetName, sheetIndex);
return GetDateFromSheet(sheet);
}
}
}
catch (Exception ex)
{
_logger.Error($"get data from excel exception :{ex.ToString()},excelContent:{excelContent},sheetIndex:{sheetIndex},entityType:{typeof(T).FullName}");
throw ex;
}
}
private ExcelCheckResult CheckDate(byte[] excelContent, string sheetName = "", int sheetIndex = 0)
{
try
{
using (Stream stream = new MemoryStream(excelContent))
{
using (ExcelPackage package = new ExcelPackage(stream))
{
ExcelWorksheet sheet = GetSheet(package, sheetName, sheetIndex);
if (sheet == null) return null;
List<long> excluedeColumns = GetExcludeCloumns(sheet);
//根据excelheader来获取type T数据对象的列字典
Dictionary<int, PropertyInfo> columnIndexDic = GetColumnIndexDicFromExcelHeader(sheet, excluedeColumns);
bool formatResult=FormatSheet(ref sheet, columnIndexDic);
package.Save();
if (!formatResult)
{
return new ExcelCheckResult() { CheckResult = false, CheckMsg = "format error!" };
}
return CheckExcel(ref sheet, columnIndexDic);
}
}
}
catch (Exception ex)
{
_logger.Error($"get data from excel exception :{ex.ToString()},excelContent:{excelContent},sheetIndex:{sheetIndex},entityType:{typeof(T).FullName}");
throw ex;
}
}
/// <summary>
/// 根据 DisplayAttribute 的 Description 来格式化sheet
/// </summary>
/// <param name="sheet"></param>
/// <param name="columnIndexDic"></param>
private bool FormatSheet( ref ExcelWorksheet sheet, Dictionary<int, PropertyInfo> columnIndexDic)
{
try
{
var typeOfObject = typeof(T);
var columnIndexDicForDisplayAttr = new Dictionary<int, DisplayAttribute>();
foreach (var columnInfo in columnIndexDic)
{
int columnKey = columnInfo.Key;
PropertyInfo columnProperty = columnInfo.Value;
var attr = columnProperty.GetCustomAttribute(typeof(DisplayAttribute)) as DisplayAttribute;
if (attr != null && !string.IsNullOrEmpty(attr.Description))
{
var originalFormat = sheet.Column(columnKey).Style.Numberformat.Format;
sheet.Column(columnKey).Style.Numberformat.Format = attr.Description;
_logger.Warn($"change cloumn{columnKey} formate({ originalFormat}=>{attr.Description}):(class:{typeOfObject.FullName},property:{columnProperty.Name})");
}
}
return true;
}
catch (Exception ex)
{
_logger.Error($"excel Format error. columnIndexDic:{JsonConvert.SerializeObject(columnIndexDic.Keys)}", ex);
return false;
}
}
private ExcelCheckResult CheckExcel(ref ExcelWorksheet sheet, Dictionary<int, PropertyInfo> columnIndexDic)
{
var excelCheckResult = new ExcelCheckResult() {
CheckResult=true,
CheckMsg="Succeed!"
};
var columnPropertyDic = columnIndexDic.Values.ToLookup(p=>p.Name).ToDictionary(kp => kp.Key, kp => kp.FirstOrDefault());
Dictionary<string, PropertyInfo> requiredPropertyDic= GetRequireDicFromType();
StringBuilder msg = new StringBuilder();
foreach (var requiredProperty in requiredPropertyDic)
{
var properName = requiredProperty.Key;
if (!columnPropertyDic.ContainsKey(properName))
{
msg.AppendLine($"{properName} is required!");
_logger.Warn($"property:({properName}) is required ! columnIndexDic:{JsonConvert.SerializeObject(columnIndexDic.Keys)}");
}
}
if (!string.IsNullOrEmpty(msg.ToString()))
{
excelCheckResult.CheckResult = false;
excelCheckResult.CheckMsg = msg.ToString();
}
return excelCheckResult;
}
private Dictionary<string, PropertyInfo> GetRequireDicFromType()
{
Type typeOfObject = typeof(T);
var pds = typeOfObject.GetProperties();
if (pds == null) return null;
var propertyDic = pds.ToLookup(p => {
var attr = p.GetCustomAttribute(typeof(RequiredAttribute)) as RequiredAttribute;
if (attr == null) return "";
return p.Name;
}).ToDictionary(kp => kp.Key, kp => kp.FirstOrDefault());
if (propertyDic == null || propertyDic.Count() == 0)
{
_logger.Warn($"no RequireDic can get from class Type:{typeOfObject.FullName} ");
}
else
{
propertyDic.Remove("");
}
return propertyDic;
}
private List<long> GetExcludeCloumns(ExcelWorksheet sheet)
{
List<long> excludeCloumns = new List<long>();
if (sheet.PivotTables == null) return excludeCloumns;
//排除sheet中透视表的列
foreach (var povotTable in sheet.PivotTables)
{
var startCloumn = povotTable.Address.Start.Column;
var endColumn= povotTable.Address.End.Column;
while (startCloumn <= endColumn)
{
excludeCloumns.Add(startCloumn);
startCloumn++;
}
}
return excludeCloumns;
}
private ExcelWorksheet GetSheet(ExcelPackage package, string sheetName,int sheetIndex)
{
if (package == null || package.Workbook == null || package.Workbook.Worksheets == null || package.Workbook.Worksheets.Count == 0) return null;
ExcelWorksheets excelWorksheets = package.Workbook.Worksheets;
if (!string.IsNullOrWhiteSpace(sheetName))
{
var targetSheet = excelWorksheets.Where(s => s.Name.ToLower().Trim() == sheetName.ToLower());
if (targetSheet == null || targetSheet.Count() == 0) return null;
return targetSheet.FirstOrDefault();
}
else if (sheetIndex > 0 && sheetIndex + 1 <= excelWorksheets.Count())
{
return excelWorksheets[sheetIndex + 1];
}
else
{
return excelWorksheets.FirstOrDefault();
}
}
private Dictionary<int, PropertyInfo> GetColumnIndexDicFromExcelHeader(ExcelWorksheet sheet, List<long> excluedeColumns)
{
if (_displayAttrDic == null)
{
//获取 Dictionary<excel column Header text,DisplayAttribute>
_displayAttrDic = GetDisplayDicFromType(typeof(T));
}
Dictionary<int, PropertyInfo> displayOrderDic = new Dictionary<int, PropertyInfo>();
if (sheet == null) return displayOrderDic;
if(_displayAttrDic == null|| _displayAttrDic.Count == 0)
{
_logger.Warn($"no _displayAttrDic can get .");
return displayOrderDic;
}
//获取 Dictionary<column index,PropertyInfo of class T>
var query1 = (from cell in sheet.Cells[1, 1, 1, sheet.Dimension.Columns] where !excluedeColumns.Contains(cell.Start.Column) select cell);
foreach (var cell in query1)
{
var columnName = cell.Value.ToString().ToLower().Trim();
if (_displayAttrDic.ContainsKey(columnName))
{
var propertyInfo = _displayAttrDic[columnName];
displayOrderDic.Add(cell.Start.Column, propertyInfo);
}
}
if (displayOrderDic == null || displayOrderDic.Count() == 0)
{
_logger.Warn($"no ColumnIndexDic can get from ExcelHeader. sheet:{sheet.Name},_displayAttrDic got no data");
}
return displayOrderDic;
}
private Dictionary<string,PropertyInfo> GetDisplayDicFromType(Type typeOfObject)
{
var pds = typeOfObject.GetProperties();
if (pds == null) return null;
//DisplayAttribute 中的Name==excel column Header
var propertyDic = pds.ToLookup(p=> {
var attr = p.GetCustomAttribute(typeof(DisplayAttribute)) as DisplayAttribute;
if (attr == null) return "";
return attr.Name.ToLower().Trim();
}).ToDictionary(kp => kp.Key, kp=> kp.FirstOrDefault());
if(propertyDic==null|| propertyDic.Count() == 0)
{
_logger.Warn($"no DisplayDic can get from class Type:{typeOfObject.FullName} ");
}
return propertyDic;
}
private List<T> GetDatesFromContent(ExcelWorksheet sheet, Dictionary<int, PropertyInfo> columnIndexDic, List<long> excluedeColumns)
{
List<T> result = new List<T>();
//fill list form excel
Dictionary<string, Dictionary<string, object>> enumDic = new Dictionary<string, Dictionary<string, object>>();
var query2 = (from cell in sheet.Cells[2, 1, sheet.Dimension.Rows, sheet.Dimension.Columns] where !excluedeColumns.Contains(cell.Start.Column) select cell);
T temp = default(T);
foreach (var cell in query2)
{
if (cell.Start.Column == 1)
{
if (temp != null) result.Add(temp);
temp = (T)Activator.CreateInstance(typeof(T));
}
if (cell.Value == null || string.IsNullOrWhiteSpace(cell.Value.ToString())) continue;
SetValueAccordingEachCell(cell, temp, columnIndexDic, ref enumDic);
}
if (temp != null) result.Add(temp);
return result;
}
//according cell value to set T's property value
private void SetValueAccordingEachCell(ExcelRangeBase cell, T temp, Dictionary<int, PropertyInfo> columnIndexDic, ref Dictionary<string, Dictionary<string, object>> enumDic)
{
try
{
var columnIndex = cell.Start.Column;
if (columnIndexDic == null || columnIndexDic.Count() == 0)
{
_logger.Warn($"no column Index can get from cell(address:{cell.Start.Address} ,value:{cell.Value})");
return;
}
if (!columnIndexDic.ContainsKey(columnIndex))
{
_logger.Warn($"no column Index can get from cell(address:{cell.Start.Address} ,value:{cell.Value}),columnIndexDic:{JsonConvert.SerializeObject(columnIndexDic.Keys)}");
return;
}
var propertyInfo = columnIndexDic[columnIndex];
Type propertyType = propertyInfo.PropertyType;
if (propertyType.IsEnum)
{
Dictionary<string, object> enumDicTemp;
if (enumDic.ContainsKey(propertyType.FullName))
{
enumDicTemp = enumDic[propertyType.FullName];
}
else
{
enumDicTemp = GetEnumDicFromType(propertyType);
enumDic.Add(propertyType.FullName, enumDicTemp);
}
object enumValue = null;
if (enumDicTemp != null)
{
if (enumDicTemp.ContainsKey(cell.Value.ToString()))
{
enumValue = enumDicTemp[cell.Value.ToString()];
}
else
{
_logger.Warn($"no enum value can get from enum dictionary:{JsonConvert.SerializeObject(enumDicTemp.Keys)} , enum Type:{propertyType.FullName},cell (address:{cell.Start.Address},value:{cell.Value.ToString()})");
}
}
else
{
_logger.Warn($"no enum dictionary can get from enum Type:{propertyType.FullName} ");
}
if (enumValue != null)
{
propertyInfo.SetValue(temp, enumValue);
}
else
{
_logger.Warn($"no enum value can get for cell:{cell.Value} ");
}
return;
}
if (propertyType == typeof(decimal))
{
string cellV = cell.Value.ToString();
decimal multiply = 1;
if (cellV.Contains("%"))
{
multiply = 100;
cellV = cellV.Substring(0, cellV.IndexOf("%") + 1);
}
decimal tempV;
bool convertR = decimal.TryParse(cellV, out tempV);
if (convertR)
{
propertyInfo.SetValue(temp, tempV * multiply);
}
else
{
_logger.Warn($"no decimal value can get for cell:(address:{cell.Address},value:{cell.Value})");
}
return;
}
if (propertyType == typeof(int))
{
propertyInfo.SetValue(temp, Convert.ToInt32(cell.Value));
return;
}
if (propertyType == typeof(long))
{
propertyInfo.SetValue(temp, Convert.ToInt64(cell.Value));
return;
}
if (propertyType == typeof(DateTime))
{
propertyInfo.SetValue(temp, Convert.ToDateTime(cell.Value));
return;
}
if (propertyType == typeof(string))
{
propertyInfo.SetValue(temp, cell.Value.ToString());
return;
}
propertyInfo.SetValue(temp, cell.Value.ToString());
return;
}
catch(Exception ex)
{
_logger.Error($"no property value can set from cell:(address:{cell.Address},value:{cell.Value})");
throw ex;
}
}
// get Dictionary<enumn's display name==excel cell value,emumn value>
private Dictionary<string, object> GetEnumDicFromType(Type propertyType)
{
var result = new Dictionary<string, object>();
if (propertyType.IsEnum)
{
var enumValues = propertyType.GetEnumValues();
foreach (var value in enumValues)
{
MemberInfo memberInfo =
propertyType.GetMember(value.ToString()).First();
var descriptionAttribute =
memberInfo.GetCustomAttribute<DisplayAttribute>();
if (descriptionAttribute != null)
{
result.Add(descriptionAttribute.Name, value);
}
else
{
var enumString = Enum.GetName(propertyType, value);
result.Add(enumString, value);
}
}
if (result == null || result.Count() == 0)
{
_logger.Warn($"no EnumDic can get from enum Type:{propertyType.FullName} ");
}
}
return result;
}
相关辅助类：
enum class
public enum AdvertiseType:Int32
{
/// <summary>
/// Search
/// </summary>
[Display(Name = "Search")]
Search = 1,
/// <summary>
/// Display
/// </summary>
[Display(Name = "Display")]
Display = 2,
}
T class：
public class FinancialBillEntity
{
[Display(Name = "类型")]
public BussinessType AdvertiseType{ get; set; }
[Display(Name = "平台2343")]
public string AdvertisePlantform { get; set; }
[Display(Name = "签约12312")]
public string PlantformSignEntity { get; set; }
}
应用：
//register interface services.RegisterServiceR<ExcelReadServiceAccordingDisplayAttr<FinancialBillEntity>,IExcelReadService<FinancialBillEntity>>(lifeStyle); //get interface instance var excelWriteService= services.GetInstance<IExcelWriteService<FinancialBillEntity>>(); //execute interface method bool result=_excelWriteService.WriteData(financeBills,cmdOptions.OutputFinanceBillExcelPath,cmdOptions.OutputFinanceBillSheetName);