Skip to main content

Excel upload/export integrator

Started 2026-08-25 — a generic .xlsx export/import engine, proven end-to-end against Countries as the first entity. Legacy had no reusable precedent: its one prior export was wired to a single screen, and its one prior import used an obsolete OleDb driver with manual column mapping — neither was ported.

:::caution Not yet rolled out beyond Countries The remaining ~19 Reference CRUD entities each need their own <Entity>ExcelColumns.cs plus the three controller actions and page wiring below, copying the Countries shape. It's also not live-smoke-tested — build and tsc --noEmit are clean, but no actual browser round-trip (export a file, edit it, upload it) has run yet. Do that before treating a newly-rolled-out entity as verified. :::

The generic contract​

IExcelExportService/IExcelImportService (Application/Common/Excel/). ExcelColumn<T> (same folder) is the per-entity column spec — one list drives both directions:

  • Header — the export column header, and the import's expected header name, so a downloaded template's headers always match what import accepts.
  • GetValue — the export-only projection.
  • Width.

ExcelImportResult/ExcelImportRow deliberately stop at raw string cell values per row rather than rehydrating a caller's DTO — DTOs across the codebase are immutable records with required init-only properties, so there's no reflection-safe generic way to construct one from parsed cells. The caller (a controller) maps each row's values to its own DTO and validates it with that entity's existing FluentValidation validator, reusing validation instead of re-implementing it.

ClosedXmlExcelService (Infrastructure/Excel/) is the one implementation of both interfaces, registered AddScoped under each interface separately. ClosedXML (MIT), deliberately not EPPlus — EPPlus's current versions require a commercial license for non-personal use.

Per-entity column spec​

Application/Modules/<Module>/Excel/<Entity>ExcelColumns.cs (e.g. CountryExcelColumns.cs) — a static All list. Copy this shape for the next entity. Exclude system-managed fields (UpdatedBy/UpdatedDt) and any read-only field the entity's manage SP has no parameter for.

Controller wiring​

Three actions added to the entity's existing controller (CountriesController is the reference):

  • GET .../export — the whole list as .xlsx, synchronous (no job queue) — the same "the whole list is small" assumption the Mas* in-memory cache already relies on.
  • GET .../import-template — a header-only workbook, the exact shape import expects, so download-template → fill in → upload always round-trips.
  • POST .../import — IFormFile, [RequestSizeLimit].

Each import row is validated independently via the entity's own IValidator<TDto> and created independently — one bad row doesn't abort the batch: a DbUpdateException (e.g. duplicate key) is caught per-row into ExcelImportSummary.Errors rather than surfacing as a 500. Gate export/template with the entity's View/Create [RequiresMenuPermission] action, same as its existing GET/POST.

Frontend​

ExcelImportExportButtons (src/components/) — a reusable export/download-template/import trio; only the three callbacks (that entity's own <entity>Api.ts-style export/import functions, e.g. countryApi.ts) are entity-specific. Shows a modal with the per-row error list after import. Every label goes through useTranslation() (common.export/common.import/etc.) per the Translatable Labels pattern.

Decisions made, not yet revisited​

Export is synchronous — no background job; revisit only if a genuinely large, non-Mas* entity needs this later. Import is a fixed-template bulk-create shape — no update-existing-row-by-key import, no column-remapping UI for a workbook with different headers.

Reference files​

IExcelExportService.cs, IExcelImportService.cs, ExcelColumn.cs, ExcelImportResult.cs, ExcelImportSummary.cs, ClosedXmlExcelService.cs, CountryExcelColumns.cs, CountriesController.cs (the Excel actions), ExcelImportExportButtons.tsx, countryApi.ts (the Excel functions), CountriesPage.tsx.