Thom Lamb
0c144f5c66
This commit introduces the `Strata.Excel.Core` project, a .NET library built on ClosedXML for robust Excel document generation and data import. Key features include: - **Export:** Flexible data export to Excel, supporting custom formatting, titles, subtitles, humanized headings, and batched processing for large datasets. Includes `ExcelContentResult` and `ZipFileContentResult` for ASP.NET Core integration. - **Import:** Utilities to easily import data from Excel worksheets into C# objects. - **Test Utilities:** Comprehensive helpers for comparing Excel workbooks in tests, handling resource extraction, and performing load tests. - **Build Infrastructure:** Sets up a Dockerfile for building the library, including SonarQube and Dependency-Check scanning. - **Project Structure:** Establishes `.gitignore`, `.dockerignore`, `nuget.config`, and a `README.md` with usage instructions and versioning guidelines. This foundational commit provides a reusable and well-tested framework for Excel operations within Strata applications.
Strata.Excel.Core
This provides a library for Strata standard ClosedXML Excel documents
Branching and Versioning
| branch | version format | example |
|---|---|---|
| main | #.#.# | 1.2.3 |
| feature/* | #.#+1.0-featureName.# | 1.3.0-newfeat.1 |
| fix/* | #.#.#+1-fixName.# | 1.2.4-bug.1 |
See our confluence page for more information
Usage
Build and Run
docker-compose up -d --build
- https://localhost:8443/index (swagger api)
- https://localhost:8443/hangfire (hangfire dashboard)
- http://localhost:8081 (redis commander)
Extracting nuget package
docker create --name throwaway strataexcel-excel-core
docker cp throwaway:/pack .
docker rm throwaway
Cleanup
docker-compose down
Helpful Features
Adding a drop down list to a worksheet
ClosedXML offers the functionality for adding drop down lists to columns with validation out of the box. However, that implementation has a data length limitation of a formula field. For lists longer than trivial selections this extension method can be used:
/// <param name="items">Drop down list items</param>
/// <param name="hiddenWorksheetName">Name of hidden worksheet that holds data</param>
/// <param name="orderItems">Sort items in ascending order</param>
CreateDataValidation(this IXLColumn column, IEnumerable<string> items, string hiddenWorksheetName, bool orderItems = true)
Example usage
var workbook = new XLWorkbook();
var worksheet= wb.Worksheets.Add("Strata Data Worksheet");
// Illustrating a list longer than standard Data Validation can handle
var dropDownListItems = new List<string>(){"Department A", "Department B", "Department C", "Department D", "Department E", "Department F"
"Department G", "Department H", "Department I", "Department J", "Department K", "Department L"
"Department M", "Department N", "Department O", "Department P", "Department Q", "Department R"};
// Add drop down with list items to all cells in column 1
worksheet.Column(1).CreateDataValidation(dropDownListItems, "Name of Worksheet");
Notes
- Worksheets must have unique names in excel therefore an overload for worksheet name is provided. When adding more than one drop down to a single worksheet it will be necessary to ensure each worksheet has a unique name.
- Data validation is implemented by adding a hidden worksheet to your workbook that contains all of the desired list items, validation is performed against the hidden worksheet's list.