Magicodes.IE Documentation

The 2.x release line of Magicodes.IE guarantees public API compatibility. This means you can upgrade within the 2.x version range without fear of breaking changes to your existing code.

Stable Surface

• Package Names: Public package names are stable.
• Namespaces: Public namespaces will not change.
• Interfaces: Public interfaces remain source-compatible.
• Method Overloads: Existing overloads are preserved. New functionality is introduced via new overloads or new types rather than modifying existing ones.

What to expect in 2.x updates

Updates within the 2.x line include:

• Additive new APIs.
• Bug fixes that respect the existing contract.
• Internal refactoring and dependency upgrades (that don't affect the public contract).
• Build and CI improvements.

Handling Breaking Changes

Breaking changes are strictly reserved for major version releases. If a public API must change, the project follows a deprecation cycle: a compatibility wrapper is provided in 2.x and marked as [Obsolete] before being removed in a subsequent major version.

Magicodes.IE.Excel uses a double-brace {{ }} syntax within Excel templates to perform data rendering.

Cell Rendering

Use {{PropertyName}} to render simple properties. Supports sub-object properties and is case-sensitive.

Image Rendering

Images can be rendered using the Image:: prefix. Supported formats include:

• {{Image::ImageUrl?Width=50&Height=120&Alt=404}}
• {{Image::ImageUrl?w=50&h=120&Alt=404}}
• {{Image::ImageUrl?Alt=404}}
• {{Image::ImgUrl?w=50&h=50&XOffset=100&YOffset=100}}

Table Rendering

To render a list of items as a table, you must define a start and end marker:

1. Start Marker: {{Table>>ListName|RowNo}} where ListName is the property name of the list in your DTO and RowNo is a field within the list items.
2. End Marker: {{Remark|>>Table}} (The field name before the pipe is arbitrary, but the |>>Table suffix is required).
3. Row Fields: Inside the table area, use {{PropertyName}} to map fields from the list items.

Note: Currently, multiple tables per row are not supported.

Define a Data Transfer Object (DTO) to represent your Excel rows. Use attributes to control how the importer behaves:

• [ExcelImporter]: Apply to the class. Use IsLabelingError = true to automatically generate a marked-up Excel file highlighting errors if validation fails.
• [ImporterHeader]: Apply to properties to map them to Excel columns.
  • Name: The exact string used for the column header.
  • IsAllowRepeat: Set to false to prevent duplicate values for this column (e.g., ID numbers).
  • IsIgnore: Set to true to exclude this property from the Excel template/import process.
• [ValueMapping]: Map specific Excel string values to enumeration members (e.g., [ValueMapping("Male", 0)]).
• [Required] and [MaxLength]: Standard data validation attributes used to enforce rules during import.

[ExcelImporter(IsLabelingError = true)]
public class ImportStudentDto
{
    [ImporterHeader(Name = "Serial No.")]
    public long SerialNumber { get; set; }

    [ImporterHeader(Name = "ID number", IsAllowRepeat = false)]
    [Required(ErrorMessage = "ID number cannot be empty")]
    [MaxLength(18, ErrorMessage = "The number of words exceeds the maximum limit, please modify!")]
    public string IdCard { get; set; }

    [ImporterHeader(Name = "Gender")]
    [ValueMapping("Male", 0)]
    [ValueMapping("Female", 1)]
    public Genders Gender { get; set; }

    [ImporterHeader(IsIgnore = true)]
    public Guid ClassId { get; set; }
}

The template engine allows you to use an existing .xlsx file as a template. It supports:

• Single value placeholders: {{PropertyName}} (case-insensitive). Replaces the cell with the property value. If the property is not found, the placeholder is left as-is.
• List blocks: {{#CollectionName}}...{{/CollectionName}}. This must be placed between <row> elements in the sheet XML. It expands the content as a row template for each item in the collection.
• Sheet name replacement: {{!Sheet:Name=NewName}} in workbook.xml to rename a sheet.

Important Notes:

• It only processes xl/worksheets/*, xl/sharedStrings.xml, and xl/workbook.xml. Styles, images, and charts are preserved.
• It does not support nested list blocks.
• Date and numeric values are converted to invariant culture strings. To preserve Excel number/date formatting, format the value as a string in your DTO before exporting.

To import an Excel file containing multiple sheets that all share the same data structure, follow these steps:

1. Define the Sheet DTO: Create a class representing the data in a single sheet. Use [ExcelImporter(IsLabelingError = true)] on the class to enable error labeling. Use [ImporterHeader(Name = "...")] on properties to map them to Excel column names. Use [ImporterHeader(IsIgnore = true)] for properties that should not be read from the Excel file.
2. Define the Wrapper DTO: Create a container class where each property represents a specific sheet. Apply the [ExcelImporter(SheetName = "...")] attribute to each property, specifying the exact name of the sheet in the Excel file. Note: Do not apply [ExcelImporter] to the wrapper class itself.
3. Execute Import: Use IExcelImporter.ImportSameSheets<TWrapper, TSheet>(filePath).

The result is a dictionary where the Key is the sheet name and the Value contains the imported data.

// 1. The DTO for the individual sheet
[ExcelImporter(IsLabelingError = true)]
public class ImportStudentDto
{
    [ImporterHeader(Name = "姓名")]
    public string Name { get; set; }
    
    [ImporterHeader(IsIgnore = true)]
    public Guid ClassId { get; set; }
}

// 2. The Wrapper DTO for multiple sheets of the same type
public class ImportClassStudentDto
{
    [ExcelImporter(SheetName = "1班导入数据")]
    public ImportStudentDto Class1Students { get; set; }

    [ExcelImporter(SheetName = "2班导入数据")]
    public ImportStudentDto Class2Students { get; set; }
}

// 3. The Import logic
IExcelImporter Importer = new ExcelImporter();
var filePath = "path/to/your/file.xlsx";

// Returns a dictionary: Key = Sheet Name, Value = Import Result
var importDic = await Importer.ImportSameSheets<ImportClassStudentDto, ImportStudentDto>(filePath);

foreach (var item in importDic)
{
    var import = item.Value;
    var studentList = import.Data.ToList();
}