ASP.NET 开源导入导出库Magicodes.IE 多Sheet导入教程资料

本文主要介绍ASP.NET 开源导入导出库Magicodes.IE 多Sheet导入教程资料 方法和在新技术下所面对的“挑战”,方便大家深入理解ASP.NET 开源导入导出库Magicodes.IE 多Sheet导入教程资料 过程。本文也将分享ASP.NET 开源导入导出库Magicodes.IE 多Sheet导入教程资料 所遇到的问题和应对策略,怎么解决怎么做的问题。
通过深入本文可以理解代码原理,进行代码文档的下载,也可以查看相应 Demo 部署效果。

多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类型)

模板目录:srcMagicodes.ExporterAndImporter.TestsTestFilesImport班级学生基础数据导入.xlsx

第一个Sheet:
ASP.NET 开源导入导出库Magicodes.IE 多Sheet导入教程

第二个Sheet:
ASP.NET 开源导入导出库Magicodes.IE 多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模板

模板目录:srcMagicodes.ExporterAndImporter.TestsTestFilesImport学生基础数据及缴费流水号导入.xlsx

学生基础数据Sheet:
ASP.NET 开源导入导出库Magicodes.IE 多Sheet导入教程

缴费流水号Sheet:
ASP.NET 开源导入导出库Magicodes.IE 多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导入教程资料部分资料来自网络,侵权毕设源码联系删除

区块链毕设网(www.qklbishe.com)全网最靠谱的原创区块链毕设代做网站
部分资料来自网络,侵权联系删除!
资源收费仅为搬运整理打赏费用,用户自愿支付 !
qklbishe.com区块链毕设代做网专注|以太坊fabric-计算机|java|毕业设计|代做平台 » ASP.NET 开源导入导出库Magicodes.IE 多Sheet导入教程资料

提供最优质的资源集合

立即查看 了解详情