Excel Student Data Import Tutorial

Overview

This guide demonstrates how to use the Magicodes.IE.Excel library to import student data from Excel spreadsheets into your application.

Key Concepts

• Magicodes.IE.Excel handles all Excel data import operations
• Data transfer objects (DTOs) define the srtucture for imported data
• The library automatically generates Excel templates based on DTO definitions
• Built-in support for data validation including duplicate checking
• Automatic error annotation in exported Excel files
• Value constraint and mapping capabilities

Implementation Steps

1. Installing the Required Package

First, install the Magicodes.IE.Excel NuGet package in your project:

The following DTO class defines the structure for student data import:

/// <summary>
/// Student registration number
/// </summary>
[ImporterHeader(Name = "Registration No.")]
[MaxLength(30, ErrorMessage = "Registration number exceeds maximum length!")]
public string RegistrationNumber { get; set; }

/// <summary>
/// Full name
/// </summary>
[ImporterHeader(Name = "Full Name")]
[Required(ErrorMessage = "Student name is required")]
[MaxLength(50, ErrorMessage = "Name exceeds maximum length!")]
public string FullName { get; set; }

/// <summary>
/// National identification number
/// </summary>
[ImporterHeader(Name = "ID Number", IsAllowRepeat = false)]
[Required(ErrorMessage = "ID number is required")]
[MaxLength(18, ErrorMessage = "ID number exceeds maximum length!")]
public string IdentificationNumber { get; set; }

/// <summary>
/// Gender
/// </summary>
[ImporterHeader(Name = "Gender")]
[Required(ErrorMessage = "Gender is required")]
[ValueMapping("Male", 0)]
[ValueMapping("Female", 1)]
public GenderType Gender { get; set; }

/// <summary>
/// Home address
/// </summary>
[ImporterHeader(Name = "Home Address")]
[Required(ErrorMessage = "Address is required")]
[MaxLength(200, ErrorMessage = "Address exceeds maximum length!")]
public string HomeAddress { get; set; }

/// <summary>
/// Parent/guardian name
/// </summary>
[ImporterHeader(Name = "Guardian Name")]
[Required(ErrorMessage = "Guardian name is required")]
[MaxLength(50, ErrorMessage = "Guardian name exceeds maximum length!")]
public string GuardianName { get; set; }

/// <summary>
/// Guardian contact number
/// </summary>
[ImporterHeader(Name = "Guardian Phone")]
[MaxLength(20, ErrorMessage = "Phone number exceeds maximum length!")]
public string GuardianContact { get; set; }

/// <summary>
/// Student ID
/// </summary>
[ImporterHeader(Name = "Student ID")]
[MaxLength(30, ErrorMessage = "Student ID exceeds maximum length!")]
public string StudentIdNumber { get; set; }

/// <summary>
/// Dormitory number
/// </summary>
[ImporterHeader(Name = "Dormitory No.")]
[MaxLength(20, ErrorMessage = "Dormitory number exceeds maximum length!")]
public string DormitoryNumber { get; set; }

/// <summary>
/// QQ instant messaging number
/// </summary>
[ImporterHeader(Name = "QQ Number")]
[MaxLength(30, ErrorMessage = "QQ number exceeds maximum length!")]
public string QqIdentifier { get; set; }

/// <summary>
/// Ethnicity
/// </summary>
[ImporterHeader(Name = "Ethnicity")]
[MaxLength(2, ErrorMessage = "Ethnicity exceeds maximum length!")]
public string Ethnicity { get; set; }

/// <summary>
/// Household registration type
/// </summary>
[ImporterHeader(Name = "Residency Type")]
[MaxLength(10, ErrorMessage = "Residency type exceeds maximum length!")]
public string ResidencyCategory { get; set; }

/// <summary>
/// Student contact number
/// </summary>
[ImporterHeader(Name = "Student Phone")]
[MaxLength(20, ErrorMessage = "Phone number exceeds maximum length!")]
public string ContactPhone { get; set; }

/// <summary>
/// Enrollment status
/// Nullable enum for testing purposes
/// </summary>
[ImporterHeader(Name = "Status")]
public EnrollmentStatus? CurrentStatus { get; set; }

/// <summary>
/// Additional notes
/// </summary>
[ImporterHeader(Name = "Notes")]
[MaxLength(200, ErrorMessage = "Notes exceed maximum length!")]
public string AdditionalNotes { get; set; }

/// <summary>
/// Boarding status indicator
/// </summary>
[ImporterHeader(IsIgnore = true)]
public bool? IsResidingOnCampus { get; set; }

/// <summary>
/// Associated class identifier
/// </summary>
[ImporterHeader(IsIgnore = true)]
public Guid ClassIdentifier { get; set; }

/// <summary>
/// School identifier
/// </summary>
[ImporterHeader(IsIgnore = true)]
public Guid? SchoolIdentifier { get; set; }

/// <summary>
/// Campus identifier
/// </summary>
[ImporterHeader(IsIgnore = true)]
public Guid? CampusIdentifier { get; set; }

/// <summary>
/// Major/department identifier
/// </summary>
[ImporterHeader(IsIgnore = true)]
public Guid? MajorIdentifier { get; set; }

/// <summary>
/// Grade/year identifier
/// </summary>
[ImporterHeader(IsIgnore = true)]
public Guid? GradeIdentifier { get; set; }

}

</div>Key considerations when defining the DTO:

1. The `ExcelImporter` attribute configures global import settings including error labeling, sheet name specification, maximum column count, and header row position
2. Stendard validation attributes like `Required` and `MaxLength` are supported
3. Duplicate validation is enabled via the `IsAllowRepeat` property on `ImporterHeader`, demonstrated by the ID number field
4. Column headers are configured using the `Name` property. Additional options include automatic whitespace trimming, column index specification, and selective space removal
5. Hidden columns can be defined using `IsIgnore = true`, as shown with the `SchoolIdentifier` field
6. Value mapping allows custom mapping between Excel values and enum values, demonstrated by the `Gender` property
7. Enums are supported with automatic value mapping derived from `Display` and `Description` attributes

#### Gender Enumeration

<div>```
/// <summary>
/// Gender classification
/// </summary>
public enum GenderType
{
    /// <summary>
    /// Male
    /// </summary>
    Male = 0,

    /// <summary>
    /// Female
    /// </summary>
    Female = 1
}

/// <summary>
/// Student transfer
/// </summary>
[Description("Transfer")] Transfer = 1,

/// <summary>
/// Leave of absence
/// </summary>
[Display(Name = "Leave")] LeaveOfAbsence = 2,

/// <summary>
/// Work-study program
/// </summary>
[Display(Name = "Work-Study")] WorkStudyProgram = 3,

/// <summary>
/// Field internship
/// </summary>
[Display(Name = "Internship")] FieldInternship = 4,

/// <summary>
/// Graduation
/// </summary>
[Display(Name = "Graduated")] Graduated = 5,

/// <summary>
/// Military service
/// </summary>
[Display(Name = "Military")] MilitaryService = 6

}

</div>### 3. Generating Import Templates

Instead of manually creating Excel templates, Magicodes.IE.Excel can generate them automatically based on your DTO definitions. The `IImporter` interface provides the necessary methods:

<div>```
/// <summary>
/// Import functionality interface
/// </summary>
public interface IImporter
{
    /// <summary>
    /// Generate Excel import template file
    /// </summary>
    Task<TemplateFileInfo> GenerateTemplate<T>(string fileName) where T : class, new();

    /// <summary>
    /// Generate Excel import template as byte array
    /// </summary>
    Task<byte[]> GenerateTemplateBytes<T>() where T : class, new();

    /// <summary>
    /// Import and validate model data from file
    /// </summary>
    Task<ImportResult<T>> Import<T>(string filePath) where T : class, new();
}

[Fact(DisplayName = "Generate student import template with enum support")] public async Task GenerateStudentTemplate_Test() { var outputPath = Path.Combine(Directory.GetCurrentDirectory(), nameof(GenerateStudentTemplate_Test) + ".xlsx"); if (File.Exists(outputPath)) File.Delete(outputPath);

var result = await ExcelProcessor.GenerateTemplate<StudentImportDto>(outputPath);
result.ShouldNotBeNull();
File.Exists(outputPath).ShouldBeTrue();

}

</div>The generated template displays enum fields as dropdown selections, and required fields are highlighted with red headers.

### 4. Validating Import Data

When data is imported, Magicodes.IE.Excel automatically performs validation and returns detailed results. The `ImportResult` class contains all relevant information:

<div>```
/// <summary>
/// Import operation results
/// </summary>
public class ImportResult<T> where T : class
{
    /// <summary>
    /// Constructor initializing error collections
    /// </summary>
    public ImportResult()
    {
        RowErrors = new List<DataRowErrorInfo>();
    }

    /// <summary>
    /// Successfully imported records
    /// </summary>
    public virtual ICollection<T> Data { get; set; }

    /// <summary>
    /// Row-level validation errors
    /// </summary>
    public virtual IList<DataRowErrorInfo> RowErrors { get; set; }

    /// <summary>
    /// Template structure errors
    /// </summary>
    public virtual IList<TemplateErrorInfo> TemplateErrors { get; set; }

    /// <summary>
    /// Import operation exceptions
    /// </summary>
    public virtual Exception Exception { get; set; }

    /// <summary>
    /// Indicates whether any errors occurred
    /// </summary>
    public virtual bool HasError => Exception != null ||
                                    (TemplateErrors?.Count(p => p.ErrorLevel == ErrorLevels.Error) ?? 0) > 0 ||
                                    (RowErrors?.Count ?? 0) > 0;
}

• Data - The successfully parsed records collection
• RowErrors - Validation failures including row numbers, field names, and specific error messages
• TemplateErrors - Template integrity issues such as missing required columns, with error severity levels
• Exception - Any exceptions encountered during the import process
• HasError - Boolean flag indicating the presence of errors (excluding warnings)

Sample import execution code:

if (result.Exception != null) _testOutputHelper.WriteLine(result.Exception.ToString());
if (result.RowErrors.Count > 0) _testOutputHelper.WriteLine(JsonConvert.SerializeObject(result.RowErrors));

result.HasError.ShouldBeFalse();
result.Data.ShouldNotBeNull();
result.Data.Count.ShouldBe(16);

}

</div>### 5. Generating Error Annotations

To provide users with clear visual feedback about validation errors, the library can annotate the Excel file directly. Enable this feature using the `IsLabelingError` parameter:

<div>```
[ExcelImporter(IsLabelingError = true)]

6. Retrieving Validated Data

When HasError evaluates to false, the validated data is available through the Data property for further processing.