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.
82 lines
3.4 KiB
Markdown
82 lines
3.4 KiB
Markdown
# Strata.Excel.Core
|
|
This provides a library for Strata standard ClosedXML Excel documents
|
|
|
|
|
|
[](https://github.com/stratadecision/excel.core/actions/workflows/build.yaml)
|
|
|
|
|
|
[](https://sonarqube.sdt.local/dashboard?id=Strata.excel.core)
|
|
|
|
[](https://proget.ops.stratanetwork.net/feeds/nuget/Strata.Excel.Core/versions?HidePrerelease=True)
|
|
[](https://proget.ops.stratanetwork.net/feeds/nuget/Strata.Excel.TestUtilities/versions?HidePrerelease=True)
|
|
|
|
|
|
|
|
## 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](https://confluence.sdt.local/display/DOP/Branching+and+Versioning) 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:
|
|
|
|
```csharp
|
|
/// <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
|
|
```csharp
|
|
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.
|
|
|
|
|