原文作者：tanyongzheng
多Sheet匯入教程
說明
本教程主要說明如何使用Magicodes.IE.Excel完成多個Sheet資料的Excel匯入。
要點
-
多個相同格式的Sheet資料匯入
-
多個不同格式的Sheet資料匯入
主要步驟
1. 多個相同格式的Sheet資料匯入
1.1 建立匯入Sheet的Dto
主要程式碼如下所示：
-
學生資料Dto
/// <summary> /// 匯入學生資料Dto /// IsLabelingError：是否標註資料錯誤 /// </summary> [ExcelImporter(IsLabelingError = true)] public class ImportStudentDto { /// <summary> /// 序號 /// </summary> [ImporterHeader(Name = "序號")] public long SerialNumber { get; set; } /// <summary> /// 學籍號 /// </summary> [ImporterHeader(Name = "學籍號", IsAllowRepeat = false)] [MaxLength(30, ErrorMessage = "學籍號字數超出最大限制,請修改!")] public string StudentCode { get; set; } /// <summary> /// 姓名 /// </summary> [ImporterHeader(Name = "姓名")] [Required(ErrorMessage = "學生姓名不能為空")] [MaxLength(50, ErrorMessage = "名稱字數超出最大限制,請修改!")] public string Name { get; set; } /// <summary> /// 身份證號碼 /// </summary> [ImporterHeader(Name = "身份證號", IsAllowRepeat = false)] [Required(ErrorMessage = "身份證號不能為空")] [MaxLength(18, ErrorMessage = "身份證字數超出最大限制,請修改!")] public string IdCard { get; set; } /// <summary> /// 性別 /// </summary> [ImporterHeader(Name = "性別")] [Required(ErrorMessage = "性別不能為空")] [ValueMapping("男", 0)] [ValueMapping("女", 1)] public Genders Gender { get; set; } /// <summary> /// 家庭地址 /// </summary> [ImporterHeader(Name = "家庭住址")] [Required(ErrorMessage = "家庭地址不能為空")] [MaxLength(200, ErrorMessage = "家庭地址字數超出最大限制,請修改!")] public string Address { get; set; } /// <summary> /// 家長姓名 /// </summary> [ImporterHeader(Name = "家長姓名")] [Required(ErrorMessage = "家長姓名不能為空")] [MaxLength(50, ErrorMessage = "家長姓名數超出最大限制,請修改!")] public string Guardian { get; set; } /// <summary> /// 家長聯絡電話 /// </summary> [ImporterHeader(Name = "家長聯絡電話")] [MaxLength(20, ErrorMessage = "家長聯絡電話字數超出最大限制,請修改!")] public string GuardianPhone { get; set; } /// <summary> /// 學號 /// </summary> [ImporterHeader(Name = "學號")] [MaxLength(30, ErrorMessage = "學號字數超出最大限制,請修改!")] public string StudentNub { get; set; } /// <summary> /// 宿舍號 /// </summary> [ImporterHeader(Name = "宿舍號")] [MaxLength(20, ErrorMessage = "宿舍號字數超出最大限制,請修改!")] public string DormitoryNo { get; set; } /// <summary> /// QQ /// </summary> [ImporterHeader(Name = "QQ號")] [MaxLength(30, ErrorMessage = "QQ號字數超出最大限制,請修改!")] public string QQ { get; set; } /// <summary> /// 民族 /// </summary> [ImporterHeader(Name = "民族")] [MaxLength(2, ErrorMessage = "民族字數超出最大限制,請修改!")] public string Nation { get; set; } /// <summary> /// 戶口性質 /// </summary> [ImporterHeader(Name = "戶口性質")] [MaxLength(10, ErrorMessage = "戶口性質字數超出最大限制,請修改!")] public string HouseholdType { get; set; } /// <summary> /// 聯絡電話 /// </summary> [ImporterHeader(Name = "學生聯絡電話")] [MaxLength(20, ErrorMessage = "手機號碼字數超出最大限制,請修改!")] public string Phone { get; set; } /// <summary> /// 狀態 /// 測試可為空的列舉型別 /// </summary> [ImporterHeader(Name = "狀態")] public StudentStatus? Status { get; set; } /// <summary> /// 備註 /// </summary> [ImporterHeader(Name = "備註")] [MaxLength(200, ErrorMessage = "備註字數超出最大限制,請修改!")] public string Remark { get; set; } /// <summary> /// 是否住校(宿舍) /// </summary> [ImporterHeader(IsIgnore = true)] public bool? IsBoarding { get; set; } /// <summary> /// 所屬班級id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid ClassId { get; set; } /// <summary> /// 學校Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? SchoolId { get; set; } /// <summary> /// 校區Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? CampusId { get; set; } /// <summary> /// 專業Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? MajorsId { get; set; } /// <summary> /// 年級Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? GradeId { get; set; } }
1.2 建立導Excel入Dto
主要程式碼如下所示：
-
學生資料Dto
public class ImportClassStudentDto { [ExcelImporter(SheetName = "1班匯入資料")] public ImportStudentDto Class1Students { get; set; } [ExcelImporter(SheetName = "2班匯入資料")] public ImportStudentDto Class2Students { get; set; } }
如上述程式碼所示，我們定義了班級學生資料Dto，主要注意事項如下：
-
Excel的Dto類上面不用匯入相關的加特性。
-
Excel的Dto類裡面的屬性未Sheet的Dto型別，ExcelImporter特性離的SheetName引數來設定具體某一個Sheet名。
-
1.3 Excel模板
注意：Excel裡的多個Sheet列名必須一致(對應同一個Sheet的Dto型別)
模板目錄：src\Magicodes.ExporterAndImporter.Tests\TestFiles\Import\班級學生基礎資料匯入.xlsx
第一個Sheet: 
第二個Sheet: 
1.4 匯入程式碼
IExcelImporter Importer = new ExcelImporter(); var filePath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "Import", "班級學生基礎資料匯入.xlsx"); //獲取到的匯入結果為一個字典型別，Key為Sheet名，Value為Sheet對應的資料 var importDic = await Importer.ImportSameSheets<ImportClassStudentDto, ImportStudentDto>(filePath); //遍歷字典，獲取每個Sheet的資料 foreach (var item in importDic) { var import = item.Value; //匯入的Sheet資料 var studentList = import.Data.ToList(); }
2. 多個不同格式的Sheet資料匯入
2.1 建立匯入Sheet的Dto
主要程式碼如下所示：
-
學生資料Dto同上
-
繳費日誌資料Dto ：
/// <summary> /// 繳費日誌匯入Dto /// </summary> /// <autogeneratedoc /> [ExcelImporter(IsLabelingError = true)] public class ImportPaymentLogDto { /// <summary> /// 學生姓名 /// </summary> [ImporterHeader(Name = "學生姓名")] [Required(ErrorMessage = "學生姓名為必填項")] [MaxLength(30, ErrorMessage = "學生姓名不能超過15位")] public string Name { get; set; } /// <summary> /// 身份證號碼 /// </summary> [ImporterHeader(Name = "身份證號碼")] [Required(ErrorMessage = "身份證號碼為必填項")] [MaxLength(18, ErrorMessage = "身份證號碼不能超過18位")] [MinLength(18, ErrorMessage = "身份證號碼不能小於18位")] public string IdCard { get; set; } /// <summary> /// 繳費型別 /// </summary> [ImporterHeader(Name = "繳費型別")] [Required(ErrorMessage = "繳費型別為必填項")] public string CostType { get; set; } /// <summary> /// 金額 /// </summary> [ImporterHeader(Name = "金額")] [Range(0.01, 1000000, ErrorMessage = "收費金額區間為1~100萬")] [Required(ErrorMessage = "金額為必填項")] public decimal Amount { get; set; } /// <summary> /// 繳費日期 /// </summary> [ImporterHeader(Name = "繳費日期")] [MaxLength(8, ErrorMessage = "繳費日期不能超過8位")] [RegularExpression("\\d{6,8}", ErrorMessage = "繳費日期只能輸入6到8位數字例如201908/20190815")] public string PayDate { get; set; } /// <summary> /// 收據編號 /// 多個使用逗號分隔，僅線下收據 /// </summary> [ImporterHeader(Name = "收據編號")] [MaxLength(200, ErrorMessage = "收據編號不能超過200位")] public string ReceiptCodes { get; set; } /// <summary> /// 備註 /// </summary> [ImporterHeader(Name = "備註")] [MaxLength(500, ErrorMessage = "備註不能超過500位")] public string Remarks { get; set; } /// <summary> /// 建立時間 /// </summary> [ImporterHeader(IsIgnore = true)] public DateTime? CreationTime { get; set; } /// <summary> /// 收費專案id /// </summary> [ImporterHeader(IsIgnore = true)] public int? ChargeProjectId { get; set; } /// <summary> /// 班級Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? ClassId { get; set; } /// <summary> /// 班級名稱 /// </summary> [ImporterHeader(IsIgnore = true)] public string ClassName { get; set; } /// <summary> /// 年級Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? GradeId { get; set; } /// <summary> /// 年級資訊 /// </summary> [ImporterHeader(IsIgnore = true)] public string GradeName { get; set; } /// <summary> /// 專業Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? MajorId { get; set; } /// <summary> /// 專業資訊 /// </summary> [ImporterHeader(IsIgnore = true)] public string MajorName { get; set; } /// <summary> /// 校區Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? CampusId { get; set; } /// <summary> /// 校區名稱 /// </summary> [ImporterHeader(IsIgnore = true)] public string CampusName { get; set; } /// <summary> /// 學校Id /// </summary> [ImporterHeader(IsIgnore = true)] public Guid? SchoolId { get; set; } /// <summary> /// 學校資訊 /// </summary> [ImporterHeader(IsIgnore = true)] public string SchoolName { get; set; } }
2.2 建立導Excel入Dto
主要程式碼如下所示：
-
班級學生基礎資料及繳費流水資料Dto
public class ImportStudentAndPaymentLogDto { [ExcelImporter(SheetName = "1班匯入資料")] public ImportStudentDto Class1Students { get; set; } [ExcelImporter(SheetName = "繳費資料")] public ImportPaymentLogDto Class2Students { get; set; } }
2.3 Excel模板
模板目錄：src\Magicodes.ExporterAndImporter.Tests\TestFiles\Import\學生基礎資料及繳費流水號匯入.xlsx
學生基礎資料Sheet:
繳費流水號Sheet:
2.4 匯入程式碼
public class ImportStudentAndPaymentLogDto { [ExcelImporter(SheetName = "1班匯入資料")] public ImportStudentDto Class1Students { get; set; } [ExcelImporter(SheetName = "繳費資料")] public ImportPaymentLogDto Class2Students { get; set; } }
Reference
https://github.com/dotnetcore/Magicodes.IE