2020年7月2日星期四

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

ASP.NET 开源导入导出库Magicodes.IE 多Sheet导入教程c79、 ola、 拍怕、 Amazon Business在加拿大发布，还推出Business Prime！、 亚马逊listing的流量来源到底来自哪里？、 跨境电商流量新风口：为什么要做跨境社交电商？、 珠海市拓展训练、 珠海市拓展训练、 珠海市周边旅游景点、
发帖者 时间：

没有评论:

发表评论

订阅： 博文评论 (Atom)