/// <summary>
/// Excel表头与模板类匹配获取所有过滤器
/// </summary>
/// <typeparam name="TTemplate"></typeparam>
/// <param name="headerRow"></param>
/// <returns></returns>
public static List <IFilter> CreateFilters <TTemplate>(ExcelHeaderRow headerRow)
{
Type templateType = typeof(TTemplate);
var key = templateType;
if (Table[key] != null)
{
return((List <IFilter>)Table[key]);
}
List <IFilter> filters = new List <IFilter>();
List <BaseFilterAttribute> attrs = new List <BaseFilterAttribute>();
TypeFilterInfo typeFilterInfo = TypeFilterInfoFactory.CreateInstance(typeof(TTemplate), headerRow);
typeFilterInfo.PropertyFilterInfos.ForEach(a => a.FilterAttrs.ForEach(f => attrs.Add(f)));
attrs.Distinct(new FilterAttributeComparer()).ToList().ForEach
(a =>
{
var filter = FilterFactory.CreateInstance(a.GetType());
if (filter != null)
{
filters.Add(filter);
}
});
Table[key] = filters;
return(filters);
}
/// <summary>
/// 验证导入模板匹配
/// </summary>
/// <param name="tempType">模板类型</param>
/// <param name="headerRow">导入的列头</param>
/// <returns></returns>
public string validateTemp(ExcelHeaderRow headerRow)
{
string msg = "";
foreach (var headItem in headerRow.Cells)
{
switch (headItem.ColName)
{
case "工号":
case "姓名":
case "*证照类型":
case "*证照号码":
case "*全年一次性奖金额":
case "免税收入":
case "其他":
case "准予扣除的捐赠额":
case "减免税额":
case "已扣缴税额":
case "备注":
break;
default:
msg += "【" + headItem.ColName + "】";
break;
}
}
if (!string.IsNullOrEmpty(msg))
{
msg = "导入失败！" + msg + "列名不正确，请确认导入模板样式！";
}
return(msg);
}
public IDictionary <string, IDictionary <string, string> > ReadTranslations()
{
try
{
var excelFileNames = _des.GetMatchingFileNames(
_excelTranslationsFolder, _excelTranslationsFileSkeleton);
var rows = new List <ExcelDataRow>();
foreach (var excelFileName in excelFileNames)
{
using var xReader = new ExcelReader(_log, excelFileName);
var nbRows = xReader.RowCount;
var header = new ExcelHeaderRow(xReader);
// start at row one to skip the header
for (var r = 1; r < nbRows; r++)
{
var row = new ExcelDataRow(xReader, header.LangMap, r);
rows.Add(row);
}
}
return(rows.ToDictionary(
k => k.Key,
v => v.Translations));
}
catch (Exception e)
{
_log(e);
throw;
}
}
/// <summary>
/// 验证导入模板匹配
/// </summary>
/// <param name="tempType">模板类型</param>
/// <param name="headerRow">导入的列头</param>
/// <returns></returns>
public string validateTemp(ExcelHeaderRow headerRow)
{
string msg = "";
foreach (var headItem in headerRow.Cells)
{
switch (headItem.ColName)
{
case "工号":
case "姓名":
case "奖金":
case "预扣税":
break;
default:
msg += "【" + headItem.ColName + "】";
break;
}
}
if (!string.IsNullOrEmpty(msg))
{
msg = "导入失败！" + msg + "列名不正确，请确认导入模板样式！";
}
return(msg);
}
public string validateHeader(ExcelHeaderRow headerRow)
{
string msg = "";
foreach (ExcelCol headerName in headerRow.Cells)
{
switch (headerName.ColName.Trim())
{
case "工号":
case "姓名":
case "*证照类型":
case "*证照号码":
case "*所得项目":
case "*本期收入":
case "本期免税收入":
case "商业健康保险":
case "税延养老保险":
case "准予扣除的捐赠额":
case "其他":
case "减免税额":
case "备注":
break;
default:
msg += "【" + headerName.ColName + "】" + "错误！";
break;
}
}
return(msg);
}
/// <summary>
/// 将IRow转换为ExcelDataRow
/// </summary>
/// <typeparam name="TTemplate"></typeparam>
/// <param name="row"></param>
/// <param name="headerRow"></param>
/// <returns></returns>
public static ExcelDataRow Convert <TTemplate>(IRow row, ExcelHeaderRow headerRow)
{
Type type = typeof(TTemplate);
var props = type.GetProperties().ToList();
ExcelDataRow dataRow = new ExcelDataRow()
{
DataCols = new List <ExcelDataCol>(),
ErrorMsg = string.Empty,
IsValid = true,
RowIndex = row.RowNum
};
ExcelDataCol dataCol;
string colName;
string propertyName;
string key;
for (int i = 0; i < headerRow.Cells.Count; i++)
{
colName = headerRow?.Cells?.SingleOrDefault(h => h.ColIndex == i)?.ColName;
if (colName == null)
{
continue;
}
key = $"{type.FullName}_{i}";
if (Table[key] == null)
{
//优先匹配ColName特性值
var matchProperty = props.FirstOrDefault(p => p.GetCustomAttribute <ColNameAttribute>()?.ColName == colName);
if (matchProperty == null)
{
//次之匹配属性名
matchProperty = props.FirstOrDefault(p => p.Name.Equals(colName, StringComparison.CurrentCultureIgnoreCase));
}
propertyName = matchProperty?.Name;
Table[key] = propertyName;
}
dataCol = new ExcelDataCol()
{
ColIndex = i,
ColName = colName,
PropertyName = Table[key]?.ToString(),
RowIndex = row.RowNum,
ColValue = row.GetCell(i) == null ? string.Empty : row.GetCell(i).GetStringValue()
};
dataRow.DataCols.Add(dataCol);
}
return(dataRow);
}
public string validateHeader(ExcelHeaderRow headerRow)
{
string errorMsg = "";
foreach (var headerName in headerRow.Cells)
{
switch (headerName.ColName.Trim())
{
case "工号":
case "姓名":
case "证照类型":
case "证照号码":
case "税款所属期起":
case "税款所属期止":
case "所得项目":
case "本期收入":
case "本期费用":
case "本期免税收入":
case "本期基本养老保险费":
case "本期基本医疗保险费":
case "本期失业保险费":
case "本期住房公积金":
case "本期企业(职业)年金":
case "本期商业健康保险费":
case "本期税延养老保险费":
case "本期其他扣除(其他)":
case "累计收入额":
case "累计减除费用":
case "累计专项扣除":
case "累计子女教育支出扣除":
case "累计住房贷款利息支出扣除":
case "累计住房租金支出扣除":
case "累计赡养老人支出扣除":
case "累计继续教育支出扣除":
case "累计其他扣除":
case "累计准予扣除的捐赠":
case "累计应纳税所得额":
case "税率":
case "速算扣除数":
case "累计应纳税额":
case "累计减免税额":
case "累计应扣缴税额":
case "累计已预缴税额":
case "累计应补(退)税额":
case "备注":
break;
default:
errorMsg += "表头错误!请验证【" + headerName.ColName + "】列的表头名称！";
break;
}
}
return(errorMsg);
}
/// <summary>
/// 将IRow转换为ExcelDataRow
/// </summary>
/// <typeparam name="TTemplate"></typeparam>
/// <param name="row"></param>
/// <param name="headerRow"></param>
/// <returns></returns>
public static ExcelDataRow Convert <TTemplate>(IRow row, ExcelHeaderRow headerRow)
{
Type type = typeof(TTemplate);
ExcelDataRow dataRow = new ExcelDataRow()
{
DataCols = new List <ExcelDataCol>(),
ErrorMsg = string.Empty,
IsValid = true,
RowIndex = row.RowNum
};
ExcelDataCol dataCol;
string colName;
string propertyName;
string key;
for (int i = 0; i < headerRow.Cells.Count; i++)
{
colName = headerRow?.Cells?.SingleOrDefault(h => h.ColIndex == i)?.ColName;
if (colName == null)
{
continue;
}
key = $"{type.FullName}_{i}";
if (Table[key] == null)
{
propertyName = type.GetProperties().ToList().FirstOrDefault(p => p.IsDefined(typeof(ColNameAttribute), false) &&
p.GetCustomAttribute <ColNameAttribute>()?.ColName == colName
)?.Name;
Table[key] = propertyName;
}
dataCol = new ExcelDataCol()
{
ColIndex = i,
ColName = colName,
PropertyName = Table[key]?.ToString(),
RowIndex = row.RowNum,
ColValue = row.GetCell(i) == null ? string.Empty : row.GetCell(i).GetStringValue()
};
dataRow.DataCols.Add(dataCol);
}
return(dataRow);
}
private static void SetDictHeader()
{
HeaderRow = new ExcelHeaderRow();
IRow row = sheet.GetRow(0);
ICell cell;
for (int i = 0; i < row.PhysicalNumberOfCells; i++)
{
cell = row.GetCell(i);
HeaderRow.Cells.Add(new ExcelCol()
{
ColIndex = i,
ColName = cell.GetStringValue()
});
}
}
/// <summary>
/// 验证导入模板匹配
/// </summary>
/// <param name="tempType">模板类型</param>
/// <param name="headerRow">导入的列头</param>
/// <returns></returns>
public string validateTemp(ExcelHeaderRow headerRow)
{
string msg = "";
foreach (var headItem in headerRow.Cells)
{
switch (headItem.ColName)
{
case "工号":
case "姓名":
//case "部门":
//case "部门编号":
case "调增项5":
case "调增项6":
case "调增项7":
case "调增项8":
case "调增项9":
case "调增项10":
case "调增项11":
case "调增项12":
case "调增项13":
case "调增项14":
case "调减项5":
case "调减项6":
case "调减项7":
case "调减项8":
case "调减项9":
case "调减项10":
case "调减项11":
case "调减项12":
case "调减项13":
case "调减项14":
case "备注":
break;
default:
msg += "【" + headItem.ColName + "】";
break;
}
}
if (!string.IsNullOrEmpty(msg))
{
msg = "导入失败！" + msg + "列名不正确，请确认导入模板样式！";
}
return(msg);
}
private ExcelHeaderRow GetHeaderRow(ISheet sheet, int headerRowIndex)
{
var headerRow = new ExcelHeaderRow();
IRow row = sheet.GetRow(headerRowIndex);
ICell cell;
for (int i = 0; i < row.PhysicalNumberOfCells; i++)
{
cell = row.GetCell(i);
headerRow.Cells.Add(
new ExcelCol()
{
ColIndex = i,
ColName = cell.GetStringValue()
});
}
return(headerRow);
}
/// <summary>
/// 获取模板类过滤器信息
/// </summary>
/// <param name="importType">导入模板类</param>
/// <param name="excelHeaderRow">Excel表头</param>
/// <returns>过滤器信息</returns>
public static TypeFilterInfo CreateInstance(Type importType, ExcelHeaderRow excelHeaderRow)
{
if (importType == null)
{
throw new ArgumentNullException("importDTOType");
}
if (excelHeaderRow == null)
{
throw new ArgumentNullException("excelHeaderRow");
}
var key = importType;
if (Table[key] != null)
{
return((TypeFilterInfo)Table[key]);
}
TypeFilterInfo typeFilterInfo = new TypeFilterInfo()
{
PropertyFilterInfos = new List <PropertyFilterInfo>()
{
}
};
IEnumerable <PropertyInfo> props = importType.GetProperties().ToList().Where(p => p.IsDefined(typeof(ColNameAttribute)));
props.ToList().ForEach(p =>
{
typeFilterInfo.PropertyFilterInfos.Add(
new PropertyFilterInfo
{
PropertyName = p.Name,
FilterAttrs = p.GetCustomAttributes <BaseFilterAttribute>()?.ToList()
});
});
Table[key] = typeFilterInfo;
return(typeFilterInfo);
}
