Skip to main content
Version: Next

Import and Export

Files Referenced in this Document

Table of Contents​

  1. Introduction
  2. Model Structure Design
  3. Export Feature Implementation
  4. Import Flow Analysis
  5. Optimization Strategies for Large Data Import
  6. Import Template Design Specifications
  7. Common Error Codes Explanation
  8. Summary

Introduction​

This document comprehensively explains the implementation details of the Excel import and export features in the system, focusing on the two core data transfer models MyEntityNamesExportModel and MyEntityNamesImportModel. The document details how these models transform data with the domain entity MyEntityName, analyzes how the export feature uses MiniExcelLibs to generate formatted Excel templates (including headers, data validation rules, and example data), and deeply dissects the file parsing, data validation, error collection, and feedback mechanisms in the import flow. Additionally, the document focuses on memory optimization strategies for processing large data imports, such as stream reading and batch insertion, providing a complete implementation reference for developers.

Model Structure Design​

This section analyzes the structure design and conversion relationships between MyEntityNamesExportModel, MyEntityNamesImportModel, and the domain entity MyEntityName.

MyEntityNamesExportModel Structure​

MyEntityNamesExportModel is the data model used for the export feature, internally defining the nested class WorkSectionExportModel to map each row of data in Excel. This model uses attributes under the MiniExcelLibs.Attributes namespace to define Excel column metadata.

  • ExcelColumn Attribute: Annotate properties with the [ExcelColumn] attribute to specify column names (Name) and widths (Width), ensuring the exported Excel file has clear Chinese headers and appropriate column widths.
  • Property Mapping: Name, Code, and Remark properties correspond to the "Name", "Code", and "Remark" columns respectively, consistent with the properties of the domain entity MyEntityName.

Section sources

MyEntityNamesImportModel Structure​

MyEntityNamesImportModel is the data model used for the import feature, cleverly reusing the structure of the export model.

  • Inheritance Relationship: The MyEntityNameImportModel class inherits from WorkSectionExportModel, implementing model reuse and ensuring the column structure of the import template is completely consistent with the export template.
  • Row Index Tracking: Adds a RowIndex property to record the row number of the data in Excel (starting from row 2), facilitating precise error location and friendly error feedback when data validation fails.
  • Collection Property: The MyEntityNames property is a List<MyEntityNameImportModel>, which automatically assigns row numbers to each record when values are set, simplifying row number management logic.

Section sources

Domain Entity MyEntityName​

MyEntityName is the core entity located in the domain layer, representing an aggregate root in the business.

  • Property Definition: Contains core business properties such as Code, Name, Sort, Remark, and IsDisabled.
  • Constructor and Validation: Uses the Volo.Abp.Check utility class to validate input parameters for non-nullability and length in the constructor and Update method, ensuring the integrity of the domain model.
  • Behavior Methods: Provides methods like Update and AdjustSort to encapsulate business logic, adhering to Domain-Driven Design (DDD) principles.

Section sources

Model Conversion Relationship​

Diagram sources

Export Feature Implementation​

The export feature is implemented via the ExportAsync method in MyEntityNameController, utilizing the MiniExcelLibs library to populate domain data into a predefined Excel template.

Implementation Flow​

  1. Data Preparation: Call _myEntityNameAppService.ExportAsync(input) to get the data to be exported. input.MaxResultCount is set to int.MaxValue to ensure all data is retrieved.
  2. Template Location: The program first looks for a template file named MyEntityName导出模板.xlsx in the Resources/Templates/ path under the application root directory; if not found, it tries to find it in the directory where the assembly is located.
  3. Template Population: Use the memoryStream.SaveAsByTemplateAsync(templatePath, exportData.Sheets) method to populate the retrieved data (exportData.Sheets) into the corresponding positions in the template.
  4. File Return: Return the generated Excel file stream to the client as a FileStreamResult and set the download filename (including a timestamp to avoid duplicates).

Template Design​

The export template (MyEntityName导出模板.xlsx) is a pre-designed Excel file containing:

  • Formatted Headers: Such as "Name", "Code", "Remark", consistent with the definitions in the ExcelColumn attribute of WorkSectionExportModel.
  • Data Validation Rules: May contain uniqueness constraints on the "Code" column or length limits on the "Name" column.
  • Example Data: The template may contain a row of example data to guide users on how to fill it correctly.

Diagram sources

Section sources

Import Flow Analysis​

The import feature is implemented via the ImportAsync method in MyEntityNameController, involving file parsing, data validation, and persistence.

Implementation Flow​

  1. File Reception and Reading: Receive IFormFile file and copy its content to MemoryStream for subsequent processing.
  2. Worksheet Identification: Use stream.GetSheetNames() to get all worksheet names and check for the existence of a worksheet named "Configuration".
  3. Data Parsing: If the "Configuration" worksheet exists, use MiniExcel.Query<MyEntityNamesImportModel.MyEntityNameImportModel>(stream, sheetName: "Configuration") to directly map Excel data to a list of MyEntityNameImportModel objects. This process uses the ExcelColumn attribute on the model for column name matching.
  4. Empty Data Check: If the parsed data is empty, throw a UserFriendlyException prompting the user to check the table.
  5. Business Processing: Encapsulate the parsed data into a MyEntityNamesImportModel object and call _myEntityNameAppService.ImportAsync(...) for subsequent business validation and data persistence.

Data Validation Mechanism​

Data validation is mainly implemented in the application service layer (MyEntityNameAppService), possibly using specification patterns like MyEntityNameSpecification.

  • MyEntityNameSpecification: This specification class can be used to build query expressions, such as querying entities by name. During import, this class can be used to check if a "Name" already exists to prevent duplicate data.
  • Error Collection: The validation process collects all errors (such as required items being empty, length exceeded, uniqueness conflicts, etc.) and associates these errors with the corresponding RowIndex.
  • Feedback Mechanism: Upon validation failure, the service returns a result object containing all error details (e.g., "Row 3: Name cannot be empty"), allowing the frontend to display a detailed error report.

Diagram sources

Section sources

Optimization Strategies for Large Data Import​

To address potential memory overflow issues caused by large data imports, the system adopts the following optimization strategies:

Stream Reading​

Although the current code example uses MemoryStream, when handling extra-large files, MiniExcel's streaming API (such as QueryAsync with IAsyncEnumerable) should be used to read and process data row by row instead of loading all data into memory at once. This significantly reduces memory peaks.

Batch Insertion​

In the application service's ImportAsync method, do not call the repository's InsertManyAsync all at once. The correct approach is:

  1. Split the imported data list into smaller batches (e.g., 1000 items per batch).
  2. Traverse each batch and call the repository's batch insert method.
  3. Explicit SaveChanges or transactions can be performed between batches to ensure data consistency while avoiding overly large single database operations.

This batch processing strategy effectively controls database connection load and memory usage, improving import stability and performance.

Section sources

Import Template Design Specifications​

To ensure smooth execution of the import feature, the import template design should follow these specifications:

  1. Worksheet Name: Must contain a worksheet named "Configuration", which is the hardcoded lookup name in the code.
  2. Column Name Consistency: Column headers must exactly match the Name property of the ExcelColumn attribute in WorkSectionExportModel, i.e., "Name", "Code", "Remark".
  3. Data Format:
    • "Code" and "Name" are required and cannot exceed the maximum length defined by the system (defined by MyEntityNameConsts).
    • "Remark" is optional.
  4. Example Data: It is recommended to provide 1-2 rows of example data in the template to help users understand the data format.
  5. Avoid Merged Cells: Avoid using merged cells in the data area to prevent parsing issues.

Common Error Codes Explanation​

Error Code/InfoCauseSolution
Please check the imported tableThe "Configuration" worksheet does not exist in the uploaded file, or there is no data in it.Confirm the file contains a worksheet named "Configuration" and ensure data starts from row 2.
Name cannot be empty / Code cannot be emptyThe "Name" or "Code" column of a row is empty.Check the reported row number and ensure required items are filled.
Name already existsThe "Name" of a row duplicates an existing record in the database.Modify the name to ensure uniqueness, or delete/disable the old record first.
Code already existsThe "Code" of a row duplicates an existing record in the database.Modify the code to ensure uniqueness.
Name length cannot exceed XX charactersThe content of the "Name" column exceeds the system-set maximum length.Shorten the name length.
Code length cannot exceed XX charactersThe content of the "Code" column exceeds the system-set maximum length.Shorten the code length.

Summary​

This document detailed the implementation of Excel import and export features based on DDD architecture. Efficient data conversion with the domain entity MyEntityName is achieved through carefully designed MyEntityNamesExportModel and MyEntityNamesImportModel models. Using the MiniExcelLibs library, the system can generate formatted export templates and parse import files. The import flow uses specification patterns for data validation and combines row index tracking to provide precise error feedback. For large data scenarios, stream reading and batch insertion optimization strategies are recommended to ensure system stability and performance. By following the template design specifications and error handling guidelines in this document, developers can quickly locate and resolve issues encountered during import and export processes.

Last updated on