Zongsoft.Externals.ClosedXml 4.5.0

Zongsoft.Externals.ClosedXml Extension Library

English | 简体中文

Overview

Zongsoft.Externals.ClosedXml integrates ClosedXML and ClosedXML.Report with the data archiving and template rendering abstractions provided by Zongsoft. It supports:

• Exporting model data to .xlsx workbooks;
• Extracting strongly typed records from .xlsx workbooks;
• Creating enum and Boolean drop-down lists from model property metadata;
• Rendering Excel report templates with data and parameters;
• Discovering .xlsx templates from a directory tree;
• Localized validation and operation errors in English and Simplified Chinese.

The archive format is named Spreadsheet, uses the .xlsx extension, and has the MIME type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.

Installation

Install the NuGet package:

dotnet add package Zongsoft.Externals.ClosedXml

The package targets the same supported frameworks as Zongsoft Framework and currently uses ClosedXML 0.105.1 and ClosedXML.Report 0.2.12.

Workbook Convention

The data boundary is an Excel Table, not a Defined Name or the worksheet's used range. The table name must be the model descriptor's Name.

For a model whose name is User, the workbook therefore contains an Excel Table named User. This convention lets Zongsoft.Data and the Import/ImportAsync endpoints in Zongsoft.Web locate the dataset directly from the current model without requiring a generated internal name or extra configuration.

The worksheet name is only a display or grouping concern. The generator uses model.Title ?? model.Name for it, while the extractor searches all worksheets by default. Set DataArchiveExtractorOptions.Source to a worksheet name only when the search must be restricted to that worksheet; the table inside it must still be named after the model.

The generated layout is:

For non-empty exports, the generated Table contains exactly the exported records. When no records are exported, it contains one or more blank data rows for manual entry; the extractor ignores those completely empty rows.

Each generated header cell also has a worksheet-scoped Defined Name matching its model field. The extractor uses these field names for stable column mapping and accepts property-name headers as a fallback for manually created tables.

The generated Table keeps its header formatting separate and uses conditional formatting for the data area's alternating gray background and row separators. Excel automatically expands these rules when a user resizes the Table, so newly added rows receive the same data-area appearance without a custom Table theme.

Enum properties receive an Excel data-validation drop-down whose entries are the enum member names. Boolean properties use a TRUE/FALSE drop-down. Nullable enum and Boolean properties additionally contain one selectable empty entry. Lists that require a real blank or typed Boolean values are stored on a VeryHidden internal worksheet and referenced directly as validation ranges; this keeps validation infrastructure out of the editable data sheet and prevents Excel from treating native Boolean cells as invalid text-list values. Dates before 1900-01-01, which Excel's default date system cannot display correctly, are written as readable yyyy-MM-dd text; supported dates remain native Excel date values.

Simplex metadata also drives native Excel input validation. Character fields with a positive Length reject longer input, and non-nullable character fields additionally reject empty input; Byte through UInt32 fields require whole numbers within their type range; Decimal, Currency, VarNumeric, Single, and Double fields require numeric input. DateTime fields use Excel's native date validation and accept dates from 1900-01-01 through 9999-12-31; earlier dates are unsupported. These rules use localized Stop-style error messages and respect nullable metadata. Int64/UInt64, Guid, binary, object, XML, and JSON values intentionally remain import-validated because Excel precision or reliable native validation is insufficient. Excel validation is an early-entry aid and can be bypassed by paste, macros, or external writers, so extraction and model validation remain authoritative.

Generated column widths follow simplex-property DataType and semantic Role. Character columns also use their declared Length, with practical minimum/default widths and a maximum width of 50. Properties whose role is Currency use Excel's locale-aware built-in currency format. Primary-key data columns are centered and use bold Maroon text. Center alignment for keys, enums, dates, Boolean values, identifiers and applicable semantic roles is also stored as the worksheet-column default, so values entered into rows added by resizing the Table retain the same alignment.

Edited workbook recovery

Excel users sometimes append records below a table without expanding it. When the table does not use a totals row, the extractor keeps the table's column boundary but extends the last data row to the last non-empty cell below those columns. Empty rows are ignored. This recovers common edits without treating unrelated columns as model data.

Keep notes and unrelated content outside the table's column band: content below those columns can intentionally be interpreted as an appended record. When a totals row is enabled, only the table's declared data range is extracted.

Files that contain only a model-level Defined Name, an invalid reference, or merely a worksheet named after the model are intentionally rejected. Create an actual Excel Table whose name is the model name.

Exporting Data

SpreadsheetGenerator creates a workbook and its model table in one operation:

using Zongsoft.Data;
using Zongsoft.Externals.ClosedXml;

var model = Model.GetDescriptor<User>();
var users = GetUsers();

await using var output = File.Create("users.xlsx");
await new SpreadsheetGenerator().GenerateAsync(output, model, users);

Use DataArchiveGeneratorOptions to select exported fields:

using Zongsoft.Data.Archiving;

var options = new DataArchiveGeneratorOptions(nameof(User.UserId), nameof(User.Name));
await generator.GenerateAsync(output, model, users, options);

For explicit column presentation, pass DataArchiveField instances. Widths use typographic points (1/72 inch), with zero meaning unspecified; font sizes follow the same zero-as-unspecified convention. Colors are technology-neutral ARGB values from Zongsoft.Components.Color, while a null color means unspecified; and Format is a .NET format specifier rather than an Excel number-format code:

using Zongsoft.Components;
using Zongsoft.Data.Archiving;

var options = new DataArchiveGeneratorOptions(
	new DataArchiveField(nameof(User.UserId))
	{
		Width = 72,
		Alignment = DataArchiveFieldAlignment.Center,
		FontStyle = DataArchiveFontStyle.Bold,
		ForegroundColor = Color.Maroon,
	},
	new DataArchiveField(nameof(User.Balance))
	{
		Width = 90,
		Alignment = DataArchiveFieldAlignment.Right,
		Format = "N2",
	},
	new DataArchiveField(nameof(User.Email))
	{
		Width = 180,
		TextMode = DataArchiveFieldTextMode.Wrap,
	});

Unspecified options continue to use styles inferred from model metadata. None, Wrap, and Shrink map to Excel's native text-display behaviors. An ellipsis mode is intentionally absent because Excel cells cannot display a native trailing ellipsis without changing the stored value.

Because the model name becomes an Excel Table name, it must satisfy Excel's table-name rules. The generator reports a localized validation error when it does not.

Extracting Data

SpreadsheetExtractor obtains the model from the extraction options, locates the table named after that model, and maps its columns back to model properties:

using Zongsoft.Data;
using Zongsoft.Data.Archiving;
using Zongsoft.Externals.ClosedXml;

var model = Model.GetDescriptor<User>();
var options = new DataArchiveExtractorOptions(model);

await using var input = File.OpenRead("users.xlsx");
await foreach(var user in new SpreadsheetExtractor().ExtractAsync<User>(input, options))
	Console.WriteLine($"{user.UserId}: {user.Name}");

To restrict lookup to a specific worksheet:

var options = new DataArchiveExtractorOptions(model)
{
	Source = "Import",
};

The extractor reports a localized error when the worksheet, model table, or required model fields cannot be resolved.

Zongsoft.Web Integration

The generator and extractor are registered as Zongsoft services for IDataArchiveGenerator and IDataArchiveExtractor. Once this extension is loaded by the application, a ServiceController import operation supplies its current model descriptor to the extractor. The default endpoint contract is therefore simple: upload a workbook containing an Excel Table whose name is the current model name.

No private generated table name is required, and a custom worksheet name does not change the model-table convention.

Rendering Templates

SpreadsheetRenderer renders an .xlsx template using ClosedXML.Report variables. SpreadsheetTemplateProvider recursively discovers .xlsx files and indexes each template by its filename without the extension:

using Zongsoft.Externals.ClosedXml;

var provider = new SpreadsheetTemplateProvider("templates");
var template = provider.GetTemplate("invoice")
	?? throw new InvalidOperationException("Template not found.");

var parameters = new Dictionary<string, object>
{
	["GeneratedAt"] = DateTimeOffset.Now,
};

await using var output = File.Create("invoice.xlsx");
await new SpreadsheetRenderer().RenderAsync(output, template, invoice, parameters);

Template variables and expressions follow the ClosedXML.Report syntax.

Localization

English is the neutral resource language, and Simplified Chinese resources are provided for zh-Hans. Error messages follow CultureInfo.CurrentUICulture, so applications should establish the UI culture through their normal request or host localization pipeline.

Sample

The interactive sample project uses Bogus to generate locale-aware fake users, exports data, imports it again, and displays the workbook structure and extracted records for manual verification.

Run it from the repository root:

dotnet run --project externals/closedxml/samples/Zongsoft.Externals.ClosedXml.Samples.csproj -f net10.0

Available commands:

When export or verify has no file argument, its default name includes the effective culture and record count, such as users.zh-CN(10).xlsx or users.en(0).xlsx. The default generated record count is 10; a count of zero creates the generator's blank entry rows. import still defaults to users.xlsx. out and in are aliases for export and import.

For example, export --count:20 --culture:en-US users.en.xlsx generates English titles, labels, and fake user names, while export -c:20 -l:zh-Hans users.zh-Hans.xlsx generates Simplified Chinese content. The selected culture applies only to that command.

Build and Test

dotnet build externals/closedxml/Zongsoft.Externals.ClosedXml.slnx --no-incremental
dotnet test externals/closedxml/test/Zongsoft.Externals.ClosedXml.Tests.csproj -f net10.0

License

This project is licensed under the GNU Lesser General Public License.