/// <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); }