public static async Task <string> Upload(Stream stream, ExcelMap map, IEmployeeService employeeService = null, IProjectService projectService = null) { IWorkbook workbook = new XSSFWorkbook(stream); ISheet sheet = workbook.GetSheetAt(map.SheetIndex); if (sheet == null) { throw new Exception($"上传的文件没有索引为{map.SheetIndex}的工作表"); } IDictionary <string, int> Indecies = new Dictionary <string, int>(); int startRowIndex; if (map.WithHeader) { IRow header = sheet.GetRow(0); if (header == null) { throw new Exception($"上传的文件第一行必须是符合格式的标题行"); } foreach (var caption in header.Cells) { Indecies.Add(caption.StringCellValue, caption.ColumnIndex); } startRowIndex = 1; } else { IRow firstRow = sheet.GetRow(0); if (firstRow == null) { throw new Exception("上传文件没有数据,必须从第一行开始有数据"); } for (var i = 0; i <= firstRow.LastCellNum; i++) { Indecies.Add(i.ToString(), i); } startRowIndex = 0; } //由于现在仅支持项目和人员导入,因此直接使用一个布尔值来区别类型。 var projectType = typeof(Entity.Project); var staffType = typeof(Entity.Employee); bool isProject; if (map.IsTypeOf(projectType)) { isProject = true; } else if (map.IsTypeOf(staffType)) { isProject = false; } else { throw new Exception("not supported upload entity type"); } int total = 0, success = 0, failure = 0; StringBuilder messageBuilder = new StringBuilder(); for (var rowIndex = startRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++) { total++; IRow data = sheet.GetRow(rowIndex); object entity; if (isProject) { entity = new Entity.Project(); } //{ // ActiveStatus = true, // CreatedOn = DateTime.Now, // CreatedBy = Cnf.CodeBase.Serialize.ValueHelper.DBKEY_NULL //}; else { entity = new Entity.Employee { ActiveStatus = true, CreatedOn = DateTime.Now, CreatedBy = Cnf.CodeBase.Serialize.ValueHelper.DBKEY_NULL, InDate = Cnf.CodeBase.Serialize.ValueHelper.DbDate_Null, InDutyID = Cnf.CodeBase.Serialize.ValueHelper.DBKEY_NULL, InProjectID = Cnf.CodeBase.Serialize.ValueHelper.DBKEY_NULL } }; try { foreach (var propMap in map.Properties) { if (!Indecies.ContainsKey(propMap.ColumnIndex)) { throw new Exception($"上传的文件无法定位到列索引[{propMap.ColumnIndex}]"); } object cellValue = propMap.PropertyType switch { PropertyType.Boolean => data.GetCell(Indecies[propMap.ColumnIndex]).BooleanCellValue, PropertyType.DateTime => data.GetCell(Indecies[propMap.ColumnIndex]).DateCellValue, PropertyType.Int => Convert.ToInt32(data.GetCell(Indecies[propMap.ColumnIndex]).NumericCellValue), PropertyType.Real => data.GetCell(Indecies[propMap.ColumnIndex]).NumericCellValue, PropertyType.Text => data.GetCell(Indecies[propMap.ColumnIndex]).StringCellValue, _ => throw new Exception("not supported property data type"), }; PropertyInfo propertyInfo = isProject ? projectType.GetProperty(propMap.PropertyName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance) : staffType.GetProperty(propMap.PropertyName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance); if (propertyInfo == null) { throw new Exception($"Excel映射配置的属性{propMap.PropertyName}不是实体类的成员"); } propertyInfo.SetValue(entity, cellValue); } } catch (Exception ex) { messageBuilder.AppendLine($"行{rowIndex}读文件错:{ex.Message}<br/>"); failure++; continue; //直接开始处理下一行。 } //保存到数据库 try { if (isProject) { await projectService.SaveProject((Entity.Project) entity); } else { await employeeService.SaveEmployee((Entity.Employee) entity); } success++; } catch (Exception ex) { messageBuilder.AppendLine($"行{rowIndex}写数据库错:{ex.Message}<br/>"); failure++; } } messageBuilder.AppendLine($"共处理了{total}行,导入{success}行,错误{failure}行"); return(messageBuilder.ToString()); }
public static void WriteExcelTemplate(Stream stream, ExcelMap map) { IWorkbook workbook = new XSSFWorkbook(); IDataFormat dataformat = workbook.CreateDataFormat(); ICellStyle dateStyle = workbook.CreateCellStyle(); dateStyle.DataFormat = dataformat.GetFormat("yyyy-MM-dd"); //【Tips】 // 1.yyyy 年份; yy 年份后两位 // 2.MM 月份零起始;M 月份非零起始; mmm[英文月份简写];mmmm[英文月份全称] // 3.dd 日零起始;d 日非零起始 // 4.hh 小时零起始;h 小时非零起始[用于12小时制][12小时制必须在时间后面添加 AM/PM 或 上午/下午] // 5.HH 小时零起始;H 小时非零起始[用于24小时制] // 6.mm 分钟零起始;m 分钟非零起始 // 7.ss 秒数零起始;s 秒数非零起始 // 8.dddd 星期;ddd 星期缩写【英文】 // 9.aaaa 星期;aaa 星期缩写【中文】 ISheet sheet = null; for (int i = 0; i <= map.SheetIndex; i++) { sheet = workbook.CreateSheet($"Sheet{i + 1}"); } IRow header = null; IRow data; if (map.WithHeader) { header = sheet.CreateRow(0); data = sheet.CreateRow(1); } else { data = sheet.CreateRow(0); } int j = 0; foreach (var col in map.Properties) { if (header != null) { header.CreateCell(j).SetCellValue(col.ColumnIndex); if (map.IsTypeOf(typeof(Entity.Project))) { SetProjectPropertyCell(data.CreateCell(j), col, dateStyle); } else if (map.IsTypeOf(typeof(Entity.Employee))) { SetStaffPropertyCell(data.CreateCell(j), col, dateStyle); } else { throw new Exception("not supported importing type."); } } else { if (map.IsTypeOf(typeof(Entity.Project))) { SetProjectPropertyCell(data.CreateCell( Convert.ToInt32(col.ColumnIndex)), col, dateStyle); } else if (map.IsTypeOf(typeof(Entity.Employee))) { SetStaffPropertyCell(data.CreateCell( Convert.ToInt32(col.ColumnIndex)), col, dateStyle); } else { throw new Exception("not supported importing type."); } } j++; } workbook.Write(stream); }