根据下面的实体框架类生成一个EXCEL导入模板,并使用EPPlus实现导入代码 using BootstrapBlazor.Components; using…
- User
根据下面的实体框架类生成一个EXCEL导入模板,并使用EPPlus实现导入代码 using BootstrapBlazor.Components; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema;
namespace DBLib { public enum DangerLevelEnum { 低, 中, 高, 危 }
/// <summary> /// 隐患整改表 /// </summary> [Table("RectificationOfHiddenDangers")] public class RectificationOfHiddenDangers: Core.INamedEntity { [Key] [AutoGenerateColumn(Ignore = true)] [Column("RectificationOfHiddenDangersId")] public Guid Id { get; set; } /// <summary> ///隐患标题 /// </summary> [Display(Name = "隐患标题")] [CNRequired] [AutoGenerateColumn(Order = 10)] [StringLength(50)] [Column("HazardTitle")] public string Name { get; set; } = null!; [Display(Name = "所属项目")] [CNRequired] [AutoGenerateColumn(Order = 15, LookupServiceKey = "table#Project")] public Guid ProjectId { get; set; } [Display(Name = "所属项目")] [AutoGenerateColumn(Ignore = true)] public Project? Project { get; set; } /// <summary> /// 被检查单位ID /// </summary> [Display(Name = "被检查单位")] public Guid? UnitId { get; set; } /// <summary> /// 被检查单位 /// </summary> [Display(Name = "被检查单位")] [StringLength(250)] public string? UnitName { get; set; } /// <summary> /// 统一社会信用代码 /// </summary> [Display(Name = "统一社会信用代码")] [StringLength(50)] public string? UnitNum { get; set; } /// <summary> /// 负责人 /// </summary> [Display(Name = "负责人")] [StringLength(50)] public string? UnitPricipal { get; set; } /// <summary> /// 联系电话 /// </summary> [Display(Name = "联系电话")] [StringLength(50)] public string? UnitTel { get; set; } /// <summary> ///检查时间 /// </summary> [Display(Name = "检查时间")] [CNRequired] [AutoGenerateColumn(FormatString = "yyyyMMdd", Order = 30)] public DateTime DiscoveryTime { get; set; } /// <summary> ///检查人 /// </summary> [Display(Name = "检查人")] [AutoGenerateColumn(Order = 18)] [StringLength(50)] public string? CheckPerson { get; set; } /// <summary> ///检查单位 /// </summary> [Display(Name = "检查单位")] [AutoGenerateColumn(Order = 18)] [StringLength(250)] public string? CheckUnit { get; set; } /// <summary> ///检查地点 /// </summary> [Display(Name = "检查地点")] [AutoGenerateColumn(Order = 18)] [StringLength(250)] public string? CheckAddress { get; set; } /// <summary> ///检查情况 /// </summary> [Display(Name = "检查情况")] [AutoGenerateColumn(Order = 18)] [StringLength(250)] public string? CheckQK { get; set; } /// <summary> ///隐患等级 /// </summary> [Display(Name = "隐患等级")] [AutoGenerateColumn(Order = 18)] public DangerLevelEnum DangerLevelEnum { get; set; } /// <summary> ///发现问题 /// </summary> [Display(Name = "发现问题")] [CNRequired] [AutoGenerateColumn(Order = 20, ComponentType = typeof(Textarea), Cols =12)] [StringLength(500)] public string HiddenDangerDescription { get; set; } = null!; /// <summary> ///整改措施 /// </summary> [Display(Name = "整改措施")] [AutoGenerateColumn(Order = 20, ComponentType = typeof(Textarea), Cols = 12)] [StringLength(500)] public string? WorkPlan { get; set; } /// <summary> ///整改意见 /// </summary> [Display(Name = "整改意见")] [AutoGenerateColumn(Order = 20, ComponentType = typeof(Textarea), Cols = 12)] [StringLength(500)] public string? FinishWork { get; set; } /// <summary> ///被检查单位意见 /// </summary> [Display(Name = "被检查单位意见")] [AutoGenerateColumn(Order = 20, ComponentType = typeof(Textarea), Cols = 12)] [StringLength(250)] public string? BJCUnitOpinion { get; set; } /// <summary> ///责任部门 /// </summary> [Display(Name = "责任部门")] [AutoGenerateColumn(Order = 40)] [StringLength(50)] public string? ResponsibleDeptForRectification { get; set; } /// <summary> ///整改责任人 /// </summary> [Display(Name = "整改责任人")] [AutoGenerateColumn(Order = 40)] [StringLength(50)] public string? ResponsiblePersonForRectification { get; set; } /// <summary> ///责任部门验收人 /// </summary> [Display(Name = "责任部门验收人")] [AutoGenerateColumn(Order = 120)] [StringLength(50)] public string? ZRBMAcceptancePerson { get; set; } /// <summary> ///安环部验收人 /// </summary> [Display(Name = "安环部验收人")] [AutoGenerateColumn(Order = 120)] [StringLength(50)] public string? AHBAcceptancePerson { get; set; } /// <summary> ///计划完成整改时间 /// </summary> [Display(Name = "计划完成整改时间")] [AutoGenerateColumn(FormatString = "yyyyMMdd",Order = 60)] public DateTime? RectificationPeriod { get; set; } /// <summary> ///整改情况 /// </summary> [Display(Name = "整改情况")] [AutoGenerateColumn(Order = 70, ComponentType = typeof(Textarea), Cols =12)] [StringLength(500)] public string? RectificationMeasures { get; set; } /// <summary> ///延期完成整改时间 /// </summary> [Display(Name = "延期完成整改时间")] [AutoGenerateColumn(Order = 80)] public DateTime? DelayFinishDate { get; set; } = null!; /// <summary> ///整改情况 /// </summary> [Display(Name = "延期原因")] [AutoGenerateColumn(Order = 90, ComponentType = typeof(Textarea), Cols = 12)] [StringLength(250)] public string? DelayReason { get; set; } /// <summary> ///整改照片 /// </summary> [Display(Name = "整改照片")] [AutoGenerateColumn(Order = 100, ComponentType = typeof(Textarea), Cols =12)] [StringLength(250)] public string? RectificationPhotos { get; set; } /// <summary> ///验收时间 /// </summary> [Display(Name = "验收时间")] [AutoGenerateColumn(FormatString = "yyyyMMdd",Order = 90)] public DateTime? AcceptanceTime { get; set; } /// <summary> ///验收结果 /// </summary> [Display(Name = "验收结果")] [AutoGenerateColumn(Order = 110)] [NotMapped] public bool AcceptanceResult2 { get; set; } /// <summary> ///验收结果 /// </summary> [Display(Name = "验收结果")] [AutoGenerateColumn(Order = 110)] public bool? AcceptanceResult { get; set; } /// <summary> ///验收人 /// </summary> [Display(Name = "验收人")] [AutoGenerateColumn(Order = 120)] [StringLength(50)] public string? AcceptancePerson { get; set; } /// <summary> ///验收意见 /// </summary> [Display(Name = "验收意见")] [AutoGenerateColumn(Order = 130)] [StringLength(500)] public string? AcceptanceOpinion { get; set; } /// <summary> ///复查时间 /// </summary> [Display(Name = "复查时间")] [AutoGenerateColumn(FormatString = "yyyyMMdd", Order = 90)] public DateTime? FcTime { get; set; } /// <summary> ///复查意见 /// </summary> [Display(Name = "复查意见")] [AutoGenerateColumn(Order = 110)] [StringLength(250)] public string? FcResult { get; set; } /// <summary> ///复查人员 /// </summary> [Display(Name = "复查人员")] [AutoGenerateColumn(Order = 120)] [StringLength(50)] public string? FcPerson { get; set; } }}
Assistant
Excel导入模板及EPPlus实现代码
Excel导入模板设计
根据
RectificationOfHiddenDangers类,我设计了以下Excel导入模板结构:列号 字段名 数据类型 是否必填 说明 示例 A 隐患标题 文本 是 最大长度50 "电气线路老化" B 所属项目 文本 是 项目名称或ID "项目A" C 被检查单位 文本 否 最大长度250 "XX建筑公司" D 统一社会信用代码 文本 否 最大长度50 "91310000710990806D" E 负责人 文本 否 最大长度50 "张三" F 联系电话 文本 否 最大长度50 "13800138000" G 检查时间 日期 是 格式: yyyyMMdd "20230515" H 检查人 文本 否 最大长度50 "李四" I 检查单位 文本 否 最大长度250 "安全检查部门" J 检查地点 文本 否 最大长度250 "3号楼2层" K 检查情况 文本 否 最大长度250 "发现多处电线老化" L 隐患等级 文本 否 低/中/高/危 "高" M 发现问题 文本 是 最大长度500 "电线老化严重,存在火灾隐患" N 整改措施 文本 否 最大长度500 "更换所有老化电线" O 整改意见 文本 否 最大长度500 "需在一周内完成整改" P 被检查单位意见 文本 否 最大长度250 "同意整改意见" Q 责任部门 文本 否 最大长度50 "工程部" R 整改责任人 文本 否 最大长度50 "王五" S 计划完成整改时间 日期 否 格式: yyyyMMdd "20230522" T 整改情况 文本 否 最大长度500 "已更换部分电线" U 延期完成整改时间 日期 否 格式: yyyyMMdd "20230529" V 延期原因 文本 否 最大长度250 "材料采购延迟" W 整改照片 文本 否 最大长度250 "photo1.jpg,photo2.jpg" X 验收时间 日期 否 格式: yyyyMMdd "20230525" Y 验收结果 布尔 否 是/否 "是" Z 验收人 文本 否 最大长度50 "赵六" AA 验收意见 文本 否 最大长度500 "整改合格" AB 复查时间 日期 否 格式: yyyyMMdd "20230601" AC 复查意见 文本 否 最大长度250 "无复发" AD 复查人员 文本 否 最大长度50 "钱七" EPPlus实现代码
using OfficeOpenXml; using System; using System.Collections.Generic; using System.Globalization; using System.IO; using System.Linq; public class ExcelImportService { public List<RectificationOfHiddenDangers> ImportFromExcel(Stream excelStream) { var result = new List<RectificationOfHiddenDangers>(); using (var package = new ExcelPackage(excelStream)) { var worksheet = package.Workbook.Worksheets[0]; // 跳过标题行,从第二行开始读取数据 for (int row = 2; row <= worksheet.Dimension.End.Row; row++) { try { var item = new RectificationOfHiddenDangers { Id = Guid.NewGuid(), // 自动生成新GUID Name = GetCellValue(worksheet, row, 1), // 隐患标题 ProjectId = GetProjectId(worksheet, row, 2), // 所属项目 UnitName = GetCellValue(worksheet, row, 3), // 被检查单位 UnitNum = GetCellValue(worksheet, row, 4), // 统一社会信用代码 UnitPricipal = GetCellValue(worksheet, row, 5), // 负责人 UnitTel = GetCellValue(worksheet, row, 6), // 联系电话 DiscoveryTime = GetDateValue(worksheet, row, 7), // 检查时间 CheckPerson = GetCellValue(worksheet, row, 8), // 检查人 CheckUnit = GetCellValue(worksheet, row, 9), // 检查单位 CheckAddress = GetCellValue(worksheet, row, 10), // 检查地点 CheckQK = GetCellValue(worksheet, row, 11), // 检查情况 DangerLevelEnum = GetDangerLevel(worksheet, row, 12), // 隐患等级 HiddenDangerDescription = GetCellValue(worksheet, row, 13), // 发现问题 WorkPlan = GetCellValue(worksheet, row, 14), // 整改措施 FinishWork = GetCellValue(worksheet, row, 15), // 整改意见 BJCUnitOpinion = GetCellValue(worksheet, row, 16), // 被检查单位意见 ResponsibleDeptForRectification = GetCellValue(worksheet, row, 17), // 责任部门 ResponsiblePersonForRectification = GetCellValue(worksheet, row, 18), // 整改责任人 RectificationPeriod = GetNullableDateValue(worksheet, row, 19), // 计划完成整改时间 RectificationMeasures = GetCellValue(worksheet, row, 20), // 整改情况 DelayFinishDate = GetNullableDateValue(worksheet, row, 21), // 延期完成整改时间 DelayReason = GetCellValue(worksheet, row, 22), // 延期原因 RectificationPhotos = GetCellValue(worksheet, row, 23), // 整改照片 AcceptanceTime = GetNullableDateValue(worksheet, row, 24), // 验收时间 AcceptanceResult = GetNullableBoolValue(worksheet, row, 25), // 验收结果 AcceptancePerson = GetCellValue(worksheet, row, 26), // 验收人 AcceptanceOpinion = GetCellValue(worksheet, row, 27), // 验收意见 FcTime = GetNullableDateValue(worksheet, row, 28), // 复查时间 FcResult = GetCellValue(worksheet, row, 29), // 复查意见 FcPerson = GetCellValue(worksheet, row, 30) // 复查人员 }; result.Add(item); } catch (Exception ex) { // 记录错误并继续处理下一行 Console.WriteLine($"处理第{row}行时出错: {ex.Message}"); continue; } } } return result; } private string GetCellValue(ExcelWorksheet worksheet, int row, int col) { var cellValue = worksheet.Cells[row, col].Value; return cellValue?.ToString()?.Trim() ?? string.Empty; } private Guid GetProjectId(ExcelWorksheet worksheet, int row, int col) { var cellValue = worksheet.Cells[row, col].Value?.ToString()?.Trim(); // 这里需要根据实际情况处理项目ID // 如果Excel中存储的是项目名称,需要通过名称查找ID // 这里简化处理,假设Excel中直接存储的是GUID字符串 if (Guid.TryParse(cellValue, out var projectId)) { return projectId; } // 如果不是GUID,可以尝试通过名称查找项目ID // 这里需要实现实际的项目查找逻辑 return Guid.Empty; } private DateTime GetDateValue(ExcelWorksheet worksheet, int row, int col) { var cellValue = worksheet.Cells[row, col].Value; if (cellValue is DateTime dateValue) { return dateValue; } if (cellValue != null && DateTime.TryParseExact(cellValue.ToString(), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out var parsedDate)) { return parsedDate; } throw new Exception($"第{row}行第{col}列不是有效的日期格式"); } private DateTime? GetNullableDateValue(ExcelWorksheet worksheet, int row, int col) { var cellValue = worksheet.Cells[row, col].Value; if (cellValue == null || string.IsNullOrWhiteSpace(cellValue.ToString())) { return null; } if (cellValue is DateTime dateValue) { return dateValue; } if (DateTime.TryParseExact(cellValue.ToString(), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out var parsedDate)) { return parsedDate; } return null; } private bool? GetNullableBoolValue(ExcelWorksheet worksheet, int row, int col) { var cellValue = worksheet.Cells[row, col].Value?.ToString()?.Trim(); if (string.IsNullOrEmpty(cellValue)) { return null; } if (cellValue.Equals("是", StringComparison.OrdinalIgnoreCase) || cellValue.Equals("true", StringComparison.OrdinalIgnoreCase) || cellValue.Equals("1")) { return true; } if (cellValue.Equals("否", StringComparison.OrdinalIgnoreCase) || cellValue.Equals("false", StringComparison.OrdinalIgnoreCase) || cellValue.Equals("0")) { return false; } return null; } private DangerLevelEnum GetDangerLevel(ExcelWorksheet worksheet, int row, int col) { var cellValue = worksheet.Cells[row, col].Value?.ToString()?.Trim(); if (string.IsNullOrEmpty(cellValue)) { return DangerLevelEnum.低; // 默认值 } if (Enum.TryParse(typeof(DangerLevelEnum), cellValue, out var result)) { return (DangerLevelEnum)result; } return DangerLevelEnum.低; // 默认值 } }使用示例
// 使用示例 public void ImportData() { using (var fileStream = new FileStream("隐患整改表导入模板.xlsx", FileMode.Open, FileAccess.Read)) { var importService = new ExcelImportService(); var importedData = importService.ImportFromExcel(fileStream); // 处理导入的数据 foreach (var item in importedData) { // 保存到数据库或其他处理 Console.WriteLine($"导入记录: {item.Name}"); } } }注意事项
-
项目ID处理:在实际应用中,需要根据Excel中的项目名称查找对应的项目ID。代码中简化了这一过程,实际需要实现项目名称到ID的映射逻辑。
-
日期格式:代码假设日期格式为"yyyyMMdd",如果Excel中的日期格式不同,需要调整解析逻辑。
-
枚举值:隐患等级使用了枚举类型,代码中处理了从文本到枚举的转换。
-
错误处理:代码中包含了基本的错误处理,可以根据需要扩展更详细的错误记录和处理机制。
-
必填字段验证:代码没有显式验证必填字段,可以根据需要添加验证逻辑。
-
EPPlus NuGet包:使用此代码需要安装EPPlus NuGet包,可以通过NuGet包管理器安装。
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.