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

多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