ASP.NET 開源匯入匯出庫Magicodes.IE 多Sheet匯入教程

HueiFeng發表於2020-07-02

多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,主要注意事項如下:

    1. Excel的Dto類上面不用匯入相關的加特性。
    2. 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 匯入程式碼

IExcelImporter Importer = new ExcelImporter();

var filePath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "Import", "學生基礎資料及繳費流水號匯入.xlsx");

//獲取到的匯入結果為一個字典型別,Key為Sheet名,Value為Sheet對應的資料
var importDic = await Importer.ImportMultipleSheet<ImportStudentAndPaymentLogDto>(filePath);

//遍歷字典,獲取每個Sheet的資料
foreach (var item in importDic)
{
    var import = item.Value;
    //匯入的Sheet資料,
    if (item.Key == "1班匯入資料")
    {
        //多個不同型別的Sheet返回的值為object,需要進行型別轉換
        ImportStudentDto dto = (ImportStudentDto) import.Data.ElementAt(0);
    }
    if (item.Key == "繳費資料")
    {
        ImportPaymentLogDto dto = (ImportPaymentLogDto)import.Data.ElementAt(0);
    }
}

來自:tanyongzheng

Reference

https://github.com/dotnetcore/Magicodes.IE

相關文章