多Sheet導(dǎo)入教程說(shuō)明
本教程主要說(shuō)明如何使用Magicodes.IE.Excel完成多個(gè)Sheet數(shù)據(jù)的Excel導(dǎo)入。
要點(diǎn)
主要步驟
1. 多個(gè)相同格式的Sheet數(shù)據(jù)導(dǎo)入
1.1 創(chuàng)建導(dǎo)入Sheet的Dto
主要代碼如下所示：
/// <summary>/// 導(dǎo)入學(xué)生數(shù)據(jù)Dto/// IsLabelingError：是否標(biāo)注數(shù)據(jù)錯(cuò)誤/// </summary>
[ExcelImporter(IsLabelingError = true)]public class ImportStudentDto
{/// <summary>/// 序號(hào)/// </summary>[ImporterHeader(Name = "序號(hào)")]public long SerialNumber { get; set; }/// <summary>/// 學(xué)籍號(hào)/// </summary>[ImporterHeader(Name = "學(xué)籍號(hào)", IsAllowRepeat = false)][MaxLength(30, ErrorMessage = "學(xué)籍號(hào)字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string StudentCode { get; set; }/// <summary>/// 姓名/// </summary>[ImporterHeader(Name = "姓名")][Required(ErrorMessage = "學(xué)生姓名不能為空")][MaxLength(50, ErrorMessage = "名稱字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string Name { get; set; }/// <summary>/// 身份證號(hào)碼/// </summary>[ImporterHeader(Name = "身份證號(hào)", IsAllowRepeat = false)][Required(ErrorMessage = "身份證號(hào)不能為空")][MaxLength(18, ErrorMessage = "身份證字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]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 = "家庭地址字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string Address { get; set; }/// <summary>/// 家長(zhǎng)姓名/// </summary>[ImporterHeader(Name = "家長(zhǎng)姓名")][Required(ErrorMessage = "家長(zhǎng)姓名不能為空")][MaxLength(50, ErrorMessage = "家長(zhǎng)姓名數(shù)超出最大限制,請(qǐng)修改!")]public string Guardian { get; set; }/// <summary>/// 家長(zhǎng)聯(lián)系電話/// </summary>[ImporterHeader(Name = "家長(zhǎng)聯(lián)系電話")][MaxLength(20, ErrorMessage = "家長(zhǎng)聯(lián)系電話字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string GuardianPhone { get; set; }/// <summary>/// 學(xué)號(hào)/// </summary>[ImporterHeader(Name = "學(xué)號(hào)")][MaxLength(30, ErrorMessage = "學(xué)號(hào)字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string StudentNub { get; set; }/// <summary>/// 宿舍號(hào)/// </summary>[ImporterHeader(Name = "宿舍號(hào)")][MaxLength(20, ErrorMessage = "宿舍號(hào)字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string DormitoryNo { get; set; }/// <summary>/// QQ/// </summary>[ImporterHeader(Name = "QQ號(hào)")][MaxLength(30, ErrorMessage = "QQ號(hào)字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string QQ { get; set; }/// <summary>/// 民族/// </summary>[ImporterHeader(Name = "民族")][MaxLength(2, ErrorMessage = "民族字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string Nation { get; set; }/// <summary>/// 戶口性質(zhì)/// </summary>[ImporterHeader(Name = "戶口性質(zhì)")][MaxLength(10, ErrorMessage = "戶口性質(zhì)字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string HouseholdType { get; set; }/// <summary>/// 聯(lián)系電話/// </summary>[ImporterHeader(Name = "學(xué)生聯(lián)系電話")][MaxLength(20, ErrorMessage = "手機(jī)號(hào)碼字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string Phone { get; set; }/// <summary>/// 狀態(tài)/// 測(cè)試可為空的枚舉類型/// </summary>[ImporterHeader(Name = "狀態(tài)")]public StudentStatus? Status { get; set; }/// <summary>/// 備注/// </summary>[ImporterHeader(Name = "備注")][MaxLength(200, ErrorMessage = "備注字?jǐn)?shù)超出最大限制,請(qǐng)修改!")]public string Remark { get; set; }/// <summary>/// 是否住校(宿舍)/// </summary>[ImporterHeader(IsIgnore = true)]public bool? IsBoarding { get; set; }/// <summary>/// 所屬班級(jí)id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid ClassId { get; set; }/// <summary>/// 學(xué)校Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? SchoolId { get; set; }/// <summary>/// 校區(qū)Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? CampusId { get; set; }/// <summary>/// 專業(yè)Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? MajorsId { get; set; }/// <summary>/// 年級(jí)Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? GradeId { get; set; }
}
1.2 創(chuàng)建導(dǎo)Excel入Dto
主要代碼如下所示：
public class ImportClassStudentDto
{[ExcelImporter(SheetName = "1班導(dǎo)入數(shù)據(jù)")]public ImportStudentDto Class1Students { get; set; }[ExcelImporter(SheetName = "2班導(dǎo)入數(shù)據(jù)")]public ImportStudentDto Class2Students { get; set; }}
如上述代碼所示，我們定義了班級(jí)學(xué)生數(shù)據(jù)Dto，主要注意事項(xiàng)如下：
Excel的Dto類上面不用導(dǎo)入相關(guān)的加特性。
Excel的Dto類里面的屬性未Sheet的Dto類型，ExcelImporter特性離的SheetName參數(shù)來(lái)設(shè)置具體某一個(gè)Sheet名。
1.3 Excel模板
注意：Excel里的多個(gè)Sheet列名必須一致(對(duì)應(yīng)同一個(gè)Sheet的Dto類型)
模板目錄：src\Magicodes.ExporterAndImporter.Tests\TestFiles\Import\班級(jí)學(xué)生基礎(chǔ)數(shù)據(jù)導(dǎo)入.xlsx
第一個(gè)Sheet:
第二個(gè)Sheet:
1.4 導(dǎo)入代碼
IExcelImporter Importer = new ExcelImporter();var filePath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "Import", "班級(jí)學(xué)生基礎(chǔ)數(shù)據(jù)導(dǎo)入.xlsx");//獲取到的導(dǎo)入結(jié)果為一個(gè)字典類型，Key為Sheet名，Value為Sheet對(duì)應(yīng)的數(shù)據(jù)
var importDic = await Importer.ImportSameSheets<ImportClassStudentDto, ImportStudentDto>(filePath);//遍歷字典，獲取每個(gè)Sheet的數(shù)據(jù)
foreach (var item in importDic)
{var import = item.Value;//導(dǎo)入的Sheet數(shù)據(jù)var studentList = import.Data.ToList();
}
2. 多個(gè)不同格式的Sheet數(shù)據(jù)導(dǎo)入
2.1 創(chuàng)建導(dǎo)入Sheet的Dto
主要代碼如下所示：
/// <summary>/// 繳費(fèi)日志導(dǎo)入Dto/// </summary>/// <autogeneratedoc />
[ExcelImporter(IsLabelingError = true)]public class ImportPaymentLogDto
{/// <summary>/// 學(xué)生姓名/// </summary>[ImporterHeader(Name = "學(xué)生姓名")][Required(ErrorMessage = "學(xué)生姓名為必填項(xiàng)")][MaxLength(30, ErrorMessage = "學(xué)生姓名不能超過(guò)15位")]public string Name { get; set; }/// <summary>/// 身份證號(hào)碼/// </summary>[ImporterHeader(Name = "身份證號(hào)碼")][Required(ErrorMessage = "身份證號(hào)碼為必填項(xiàng)")][MaxLength(18, ErrorMessage = "身份證號(hào)碼不能超過(guò)18位")][MinLength(18, ErrorMessage = "身份證號(hào)碼不能小于18位")]public string IdCard { get; set; }/// <summary>/// 繳費(fèi)類型/// </summary>[ImporterHeader(Name = "繳費(fèi)類型")][Required(ErrorMessage = "繳費(fèi)類型為必填項(xiàng)")]public string CostType { get; set; }/// <summary>/// 金額/// </summary>[ImporterHeader(Name = "金額")][Range(0.01, 1000000, ErrorMessage = "收費(fèi)金額區(qū)間為1~100萬(wàn)")][Required(ErrorMessage = "金額為必填項(xiàng)")]public decimal Amount { get; set; }/// <summary>/// 繳費(fèi)日期/// </summary>[ImporterHeader(Name = "繳費(fèi)日期")][MaxLength(8, ErrorMessage = "繳費(fèi)日期不能超過(guò)8位")][RegularExpression("\\d{6,8}", ErrorMessage = "繳費(fèi)日期只能輸入6到8位數(shù)字例如201908/20190815")]public string PayDate { get; set; }/// <summary>/// 收據(jù)編號(hào)/// 多個(gè)使用逗號(hào)分隔，僅線下收據(jù)/// </summary>[ImporterHeader(Name = "收據(jù)編號(hào)")][MaxLength(200, ErrorMessage = "收據(jù)編號(hào)不能超過(guò)200位")]public string ReceiptCodes { get; set; }/// <summary>/// 備注/// </summary>[ImporterHeader(Name = "備注")][MaxLength(500, ErrorMessage = "備注不能超過(guò)500位")]public string Remarks { get; set; }/// <summary>/// 創(chuàng)建時(shí)間/// </summary>[ImporterHeader(IsIgnore = true)]public DateTime? CreationTime { get; set; }/// <summary>/// 收費(fèi)項(xiàng)目id/// </summary>[ImporterHeader(IsIgnore = true)]public int? ChargeProjectId { get; set; }/// <summary>/// 班級(jí)Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? ClassId { get; set; }/// <summary>/// 班級(jí)名稱/// </summary>[ImporterHeader(IsIgnore = true)]public string ClassName { get; set; }/// <summary>/// 年級(jí)Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? GradeId { get; set; }/// <summary>/// 年級(jí)信息/// </summary>[ImporterHeader(IsIgnore = true)]public string GradeName { get; set; }/// <summary>/// 專業(yè)Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? MajorId { get; set; }/// <summary>/// 專業(yè)信息/// </summary>[ImporterHeader(IsIgnore = true)]public string MajorName { get; set; }/// <summary>/// 校區(qū)Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? CampusId { get; set; }/// <summary>/// 校區(qū)名稱/// </summary>[ImporterHeader(IsIgnore = true)]public string CampusName { get; set; }/// <summary>/// 學(xué)校Id/// </summary>[ImporterHeader(IsIgnore = true)]public Guid? SchoolId { get; set; }/// <summary>/// 學(xué)校信息/// </summary>[ImporterHeader(IsIgnore = true)]public string SchoolName { get; set; }
} ?
2.2 創(chuàng)建導(dǎo)Excel入Dto
主要代碼如下所示：
public class ImportStudentAndPaymentLogDto
{[ExcelImporter(SheetName = "1班導(dǎo)入數(shù)據(jù)")]public ImportStudentDto Class1Students { get; set; }[ExcelImporter(SheetName = "繳費(fèi)數(shù)據(jù)")]public ImportPaymentLogDto Class2Students { get; set; }
}
2.3 Excel模板
模板目錄：src\Magicodes.ExporterAndImporter.Tests\TestFiles\Import\學(xué)生基礎(chǔ)數(shù)據(jù)及繳費(fèi)流水號(hào)導(dǎo)入.xlsx
學(xué)生基礎(chǔ)數(shù)據(jù)Sheet:
繳費(fèi)流水號(hào)Sheet:
2.4 導(dǎo)入代碼
IExcelImporter Importer = new ExcelImporter();var filePath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "Import", "學(xué)生基礎(chǔ)數(shù)據(jù)及繳費(fèi)流水號(hào)導(dǎo)入.xlsx");//獲取到的導(dǎo)入結(jié)果為一個(gè)字典類型，Key為Sheet名，Value為Sheet對(duì)應(yīng)的數(shù)據(jù)
var importDic = await Importer.ImportMultipleSheet<ImportStudentAndPaymentLogDto>(filePath);//遍歷字典，獲取每個(gè)Sheet的數(shù)據(jù)
foreach (var item in importDic)
{var import = item.Value;//導(dǎo)入的Sheet數(shù)據(jù)，if (item.Key == "1班導(dǎo)入數(shù)據(jù)"){//多個(gè)不同類型的Sheet返回的值為object，需要進(jìn)行類型轉(zhuǎn)換ImportStudentDto dto = (ImportStudentDto) import.Data.ElementAt(0);}if (item.Key == "繳費(fèi)數(shù)據(jù)"){ImportPaymentLogDto dto = (ImportPaymentLogDto)import.Data.ElementAt(0);}
}
Reference
https://github.com/dotnetcore/Magicodes.IE
轉(zhuǎn)載是一種動(dòng)力 分享是一種美德
如果喜歡作者的文章，請(qǐng)關(guān)注【麥扣聊技術(shù)】訂閱號(hào)以便第一時(shí)間獲得最新內(nèi)容。本文版權(quán)歸作者和湖南心萊信息科技有限公司共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面明顯位置給出原文連接，否則保留追究法律責(zé)任的權(quán)利。
原文作者：tanyongzheng
文檔官網(wǎng)：docs.xin-lai.com
QQ群：
編程交流群<85318032>?
產(chǎn)品交流群<897857351>
總結(jié)
以上是生活随笔為你收集整理的开源导入导出库Magicodes.IE 多sheet导入教程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。
