Main points

  • Use Magicodes.IE.Excel to complete the Excel data import
  • Create a Dto for import data.
  • Magicodes.IE.Excel can automatically generate imported Excel templates, data validation (including duplicate validation), template validation, read settings, value constraints and mapping, and output Excel validation markup based on Dto and feature settings.

Main steps

Installation package - Magicodes.IE.Excel
In this tutorial, we will only demonstrate the use of Excel to complete the import of student data. We need to install the following packages in the prepared project with the following reference commands,
Install-Package Magicodes.IE.Excel
Create Import Dto
The main code is shown below,
Student Data Dto
  1. /// <summary>
  2. /// Importing Student Data Dto
  3. /// IsLabelingError:Whether to mark data errors
  4. /// </summary>
  5. [ExcelImporter(IsLabelingError = true)]
  6. public class ImportStudentDto {
  7. /// <summary>
  8. /// Serial No.
  9. /// </summary>
  10. [ImporterHeader(Name = "Serial No.")]
  11. public long SerialNumber {
  12. get;
  13. set;
  14. }
  15. /// <summary>
  16. /// Student Registration No.
  17. /// </summary>
  18. [ImporterHeader(Name = "Student Registration No.")]
  19. [MaxLength(30, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  20. public string StudentCode {
  21. get;
  22. set;
  23. }
  24. /// <summary>
  25. /// Name
  26. /// </summary>
  27. [ImporterHeader(Name = "Name")]
  28. [Required(ErrorMessage = "Name cannot be empty")]
  29. [MaxLength(50, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  30. public string Name {
  31. get;
  32. set;
  33. }
  34. /// <summary>
  35. /// ID number
  36. /// </summary>
  37. [ImporterHeader(Name = "ID number", IsAllowRepeat = false)]
  38. [Required(ErrorMessage = "ID number cannot be empty")]
  39. [MaxLength(18, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  40. public string IdCard {
  41. get;
  42. set;
  43. }
  44. /// <summary>
  45. /// Gender
  46. /// </summary>
  47. [ImporterHeader(Name = "Gender")]
  48. [Required(ErrorMessage = "Gender cannot be empty")]
  49. [ValueMapping("Male", 0)]
  50. [ValueMapping("Female", 1)]
  51. public Genders Gender {
  52. get;
  53. set;
  54. }
  55. /// <summary>
  56. /// Home Address
  57. /// </summary>
  58. [ImporterHeader(Name = "Home Address")]
  59. [Required(ErrorMessage = "Home Address cannot be empty")]
  60. [MaxLength(200, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  61. public string Address {
  62. get;
  63. set;
  64. }
  65. /// <summary>
  66. /// Parent's name
  67. /// </summary>
  68. [ImporterHeader(Name = "Parent's name")]
  69. [Required(ErrorMessage = "Parent's name cannot be empty")]
  70. [MaxLength(50, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  71. public string Guardian {
  72. get;
  73. set;
  74. }
  75. /// <summary>
  76. /// Parental contact number
  77. /// </summary>
  78. [ImporterHeader(Name = "Parental contact number")]
  79. [MaxLength(20, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  80. public string GuardianPhone {
  81. get;
  82. set;
  83. }
  84. /// <summary>
  85. /// Student ID
  86. /// </summary>
  87. [ImporterHeader(Name = "Student ID")]
  88. [MaxLength(30, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  89. public string StudentNub {
  90. get;
  91. set;
  92. }
  93. /// <summary>
  94. /// Dormitory number
  95. /// </summary>
  96. [ImporterHeader(Name = "Dormitory number")]
  97. [MaxLength(20, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  98. public string DormitoryNo {
  99. get;
  100. set;
  101. }
  102. /// <summary>
  103. /// QQ
  104. /// </summary>
  105. [ImporterHeader(Name = "QQ number")]
  106. [MaxLength(30, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  107. public stringQQ {
  108. get;
  109. set;
  110. }
  111. /// <summary>
  112. /// Ethnic
  113. /// </summary>
  114. [ImporterHeader(Name = "Ethnic")]
  115. [MaxLength(2, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  116. public string Nation {
  117. get;
  118. set;
  119. }
  120. /// <summary>
  121. /// Household
  122. /// </summary>
  123. [ImporterHeader(Name = "Household")]
  124. [MaxLength(10, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  125. public string HouseholdType {
  126. get;
  127. set;
  128. }
  129. /// <summary>
  130. /// Contact number
  131. /// </summary>
  132. [ImporterHeader(Name = "Contact number")]
  133. [MaxLength(20, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  134. public string Phone {
  135. get;
  136. set;
  137. }
  138. /// <summary>
  139. /// Status
  140. /// Test for nullable enumeration types
  141. /// </summary>
  142. [ImporterHeader(Name = "Status")]
  143. public Student Status ? Status {
  144. get;
  145. set;
  146. }
  147. /// <summary>
  148. /// Remarks
  149. /// </summary>
  150. [ImporterHeader(Name = "Remarks")]
  151. [MaxLength(200, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
  152. public string Remark {
  153. get;
  154. set;
  155. }
  156. /// <summary>
  157. /// Whether live on campus (dormitory)
  158. /// </summary>
  159. [ImporterHeader(IsIgnore = true)]
  160. public bool ? IsBoarding {
  161. get;
  162. set;
  163. }
  164. /// <summary>
  165. /// Class id
  166. /// </summary>
  167. [ImporterHeader(IsIgnore = true)]
  168. public GuidClassId {
  169. get;
  170. set;
  171. }
  172. /// <summary>
  173. /// School Id
  174. /// </summary>
  175. [ImporterHeader(IsIgnore = true)]
  176. public Guid ? SchoolId {
  177. get;
  178. set;
  179. }
  180. /// <summary>
  181. /// Campus Id
  182. /// </summary>
  183. [ImporterHeader(IsIgnore = true)]
  184. public Guid ? CampusId {
  185. get;
  186. set;
  187. }
  188. /// <summary>
  189. /// MajorsId
  190. /// </summary>
  191. [ImporterHeader(IsIgnore = true)]
  192. public Guid ? MajorsId {
  193. get;
  194. set;
  195. }
  196. /// <summary>
  197. /// GradeId
  198. /// </summary>
  199. [ImporterHeader(IsIgnore = true)]
  200. public Guid ? GradeId {
  201. get;
  202. set;
  203. }
  204. }
As shown in the above code, we defined the above student data Dto with the following main considerations :
  1. ExcelImporterfeature allows you to set some global settings for importing, such as whether to mark errors, the name of the imported Sheet (if not set, the first one is automatically obtained), the number of columns to be read, and the position of the sheet header.
  2. Support common data validation settings, such as required and maximum length.
  3. Support data duplicate verification, such as ID numbers. Refer ImporterHeader features of IsAllowRepeat setting.
  4. Supports column header settings,For example ImporterHeader - Name Properties?Other than that,ImporterHeader also supports automatic space filtering (enabled by default), disposing off all spaces, column indexing, etc.?
  5. Ignore setting is enabled for data columns,For example SchoolId "[ImporterHeader(IsIgnore = true)]"?
  6. Value mapping is used, For example “Gender” attribute. When value mapping is enabled, the value mapping will not be retrieved from the enumeration definition?
  7. Enumeration support, support for enumeration from the Display?Description Get value mapping?See below for enumeration definitions?
  8. Gender Enumeration
    The definition is as follows:
    1. /// <summary> /// Gender /// </summary> public enum Genders
    2. {
    3. /// <summary> /// Man /// </summary> Man = 0,
    4. /// <summary> /// Female /// </summary> Female = 1
    5. }
    Note point 7 above.

  9. Student Status Enumeration
    1. /// <summary> /// Student Status: Normal, Attrition, Suspended, Work-Study, Internship, Graduation, Military /// </summary> public enum StudentStatus
    2. {
    3. /// <summary> /// Normal /// </summary>
    4. [Display(Name = "Normal")] Normal = 0,
    5. /// <summary> /// Attrition /// </summary>
    6. [Description("Attrition")] PupilsAway = 1,
    7. /// <summary> /// Suspended /// </summary>
    8. [Display(Name = "Suspended")] Suspension = 2,
    9. /// <summary> /// Work-Study /// </summary>
    10. [Display(Name = "Work-Study")] WorkStudy = 3,
    11. /// <summary> /// Internship /// </summary>
    12. [Display(Name = "Internship")] PostPractice = 4,
    13. /// <summary> /// Graduation /// </summary>
    14. [Display(Name = "Graduation")] Graduation = 5,
    15. /// <summary> /// Military /// </summary>
    16. [Display(Name = "Military")] JoinTheArmy = 6
    17. }
    Note point 7 above
3.Generate import templates and populate them with data
Do you have to prepare a template before importing? Handwritten template?Not required! Magicodes.IE.Excel Provided the basis for DTO Automatic generation Excel Methods of importing templates,We can call it directly. Here we look at the relevant methods for importing:
  1. /// <summary>
  2. /// Import
  3. /// </summary>
  4. public interface IImporter {
  5. /// <summary>
  6. /// Generate the Excel import template
  7. /// </summary>
  8. /// <typeparamname="T"></typeparam>
  9. /// <returns></returns>
  10. Task < TemplateFileInfo > GenerateTemplate < T > (stringfileName) whereT: class, new();
  11. /// <summary>
  12. /// Generate the Excel import template
  13. /// </summary>
  14. /// <typeparamname="T"></typeparam>
  15. /// <returns>Binary byte</returns>
  16. Task < byte[] > GenerateTemplateBytes < T > () whereT: class, new();
  17. /// <summary>
  18. /// Import model validation data
  19. /// </summary>
  20. /// <typeparamname="T"></typeparam>
  21. /// <paramname="filePath"></param>
  22. /// <returns></returns>
  23. Task < ImportResult < T >> Import < T > (stringfilePath) whereT: class, new();
  24. }
By using GenerateTemplate in the above method, we can get the required import template. The specific use can be found in the following unit tests.
  1. public IImporter Importer = new ExcelImporter();
  2. [Fact(DisplayName = "Generate Student Data Import Template (Test Enumeration Generate Template)")]
  3. public asyncTaskGenerateStudentImportTemplate_Test() {
  4. var filePath = Path.Combine(Directory.GetCurrentDirectory(), nameof(GenerateStudentImportTemplate_Test) + ".xlsx");
  5. if (File.Exists(filePath)) File.Delete(filePath);
  6. var result = awaitImporter.GenerateTemplate < ImportStudentDto > (filePath);
  7. result.ShouldNotBeNull();
  8. File.Exists(filePath).ShouldBeTrue();
  9. //TODO:??Excel???????
  10. }
The above DTO gets the template and populates the data as shown below,
.NET Importing Student Data
Note
The enumeration will automatically generate a drop-down selection, and the required column headers will be marked in red
4. Get student import verification errors and data
After populating the data according to the template, we are ready to perform the data import. Typically, we have the following steps.
Validate imported data
Importing data through Magicodes.IE.Excel will automatically perform validation and output the validation results for frontend display. Specifically, we can look through its imported result classes to see.
  1. /// <summary>
  2. /// Import Results
  3. /// </summary>
  4. public class Import Result < T > whereT: class {
  5. /// <summary>
  6. /// </summary>
  7. public Import Result() {
  8. RowErrors = newList < DataRowErrorInfo > ();
  9. }
  10. /// <summary>
  11. /// Importing Data
  12. /// </summary>
  13. public virtual I Collection < T > Data {
  14. get;
  15. set;
  16. }
  17. /// <summary>
  18. /// Validation error
  19. /// </summary>
  20. public virtual I List < DataRowErrorInfo > RowErrors {
  21. get;
  22. set;
  23. }
  24. /// <summary>
  25. /// Template error
  26. /// </summary>
  27. public virtual I List < TemplateErrorInfo > TemplateErrors {
  28. get;
  29. set;
  30. }
  31. /// <summary>
  32. /// Import exception information
  33. /// </summary>
  34. public virtualException Exception {
  35. get;
  36. set;
  37. }
  38. /// <summary>
  39. /// Is there an import error
  40. /// </summary>
  41. public virtual boolHasError => Exception != null || (TemplateErrors?.Count(p => p.ErrorLevel == ErrorLevels.Error) ?? 0) > 0 || (RowErrors?.Count ?? 0) > 0;
  42. }
Among them,
  • Data is the data result
  • RowErrors is validation errors, such as required, duplicate validation, text length, etc. will give the line number, field and the set of field errors
.NET Importing Student Data
  • TemplateErrors is template errors, such as missing mandatory columns and other error messages. Support error level (warning, error)
  • Exception is import exception information
  • HasError is existence of errors (without warnings)
Through ImportResult, we can easily get the import validation error without writing additional code. Usually, we need to determine the HasError property during import and return a specific error result to the frontend.
The data import reference code is shown below,
  1. [Fact(DisplayName = "Student base data import")]
  2. public async TaskStudentInfoImporter_Test() {
  3. var filePath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "Import", "????????.xlsx");
  4. var import = awaitImporter.Import < ImportStudentDto > (filePath);
  5. import.ShouldNotBeNull();
  6. if (
  7. import.Exception != null) _testOutputHelper.WriteLine(
  8. import.Exception.ToString());
  9. if (
  10. import.RowErrors.Count > 0) _testOutputHelper.WriteLine(JsonConvert.SerializeObject(
  11. import.RowErrors));
  12. import.HasError.ShouldBeFalse();
  13. import.Data.ShouldNotBeNull();
  14. import.Data.Count.ShouldBe(16);
  15. }
Get verification markup
The customer said although you hinted, but I still do not know what is wrong! What to do?!!!
.NET Importing Student Data
We have thoughtfully prepared for you the markup of the Excel file for importing data,

.NET Importing Student Data.NET Importing Student Data
How to open this [Epic Plot]? Simply by one step,
  1. [ExcelImporter(IsLabelingError=true)]
Once turned on, we will automatically save the markup file "{target file name}_.xlsx" to the target location.
Get imported data
No more errors? That is, HasError is false, then we can just get the Data and do whatever we want!
Finally
This concludes the entire tutorial on importing student data. ** Related libraries will be updated all the time, and there may be slight differences in functional experience with this tutorial, please refer to the relevant specific code, version logs, and unit test examples. **
github:https://github.com/dotnetcore/Magicodes.IE