Gooseberry.ExcelStreaming
1.5.5
dotnet add package Gooseberry.ExcelStreaming --version 1.5.5
NuGet\Install-Package Gooseberry.ExcelStreaming -Version 1.5.5
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Gooseberry.ExcelStreaming" Version="1.5.5" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Gooseberry.ExcelStreaming --version 1.5.5
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: Gooseberry.ExcelStreaming, 1.5.5"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Gooseberry.ExcelStreaming as a Cake Addin #addin nuget:?package=Gooseberry.ExcelStreaming&version=1.5.5 // Install Gooseberry.ExcelStreaming as a Cake Tool #tool nuget:?package=Gooseberry.ExcelStreaming&version=1.5.5
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
Gooseberry.ExcelStreaming
Create Excel files with high performance and low memory allocations.
Features
- Extremely fast streaming write (100 columns * 100 000 rows in 1 second, 30Kb allocated memory)
- Most basic excel column types are supported (incl. hyperlinks)
- Shared strings and utf8 binary strings
- Cell formatting, styling and merging
- Basic pictures support
- Asynchronous compression
- Used in heavy-load production environment
Create Excel file
await using var file = new FileStream("myExcelReport.xlsx", FileMode.Create);
await using var writer = new ExcelWriter(file, token: cancellationToken);
// optional sheet configuration
var sheetConfig = new SheetConfiguration(
Columns: [new Column(Width: 10m), new Column(Width: 13m)], // column width
FrozenColumns: 1, // freeze pane: colums count
FrozenRows: 3, // freeze pane: rows count
ShowGridLines: true);
await writer.StartSheet("First sheet", sheetConfig);
writer.AddEmptyRows(3); // three empty rows
await foreach(var record in store.GetRecordsAsync(cancellationToken))
{
await writer.StartRow();
writer.AddEmptyCell(); // empty
writer.AddEmptyCells(5); // five empty cells
writer.AddCell(42); // int
writer.AddCell(DateTime.Now.Ticks); // long
writer.AddCell(DateTime.Now); // DateTime
writer.AddCell(123.765M); // decimal
writer.AddCell("string"); // string
writer.AddCell('#'); // char
writer.AddUtf8Cell("string"u8); // utf8 string
writer.AddCellWithSharedString("shared"); // shared string
// hyperlink
writer.AddCell(new Hyperlink("https://[address]", "Label text"));
// cell with picture
writer.AddCellPicture(someStreamOrByteArray, PictureFormat.Jpeg, new Size(100, 130));
}
// Adding picture from stream to "First sheet" placed to
// cell (column 4, row 2, values are zero-based) with fixed size
writer.AddPicture(someStream, PictureFormat.Jpeg, new AnchorCell(3, 1), new Size(100, 130));
// Adding picture from byte array or ReadOnlyMemory<byte> to "First sheet"
// with top left corner placed in the cell (column 11:row 2, values are zero-based)
// and right bottom corner is placed in another cell (column 16:row 11)
writer.AddPicture(someByteArray, PictureFormat.Jpeg, new AnchorCell(10, 1), new AnchorCell(15, 10));
await writer.StartSheet("Second sheet");
for (var row = 0; row < 100; row++)
{
... //write rows
}
await writer.Complete(); // DisposeAsync method also will call Complete
More examples
Write Excel file to Http response
The data isn't accumulated in memory. It is flushed to Http response streamingly.
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Headers.Add("Content-Disposition", $"attachment; filename=fileName.xlsx");
await Response.StartAsync();
await using var writer = new ExcelWriter(Response.BodyWriter.AsStream(), token: cancellationToken);
await writer.StartSheet("Sheet name");
await writer.StartRow();
writer.AddCell(123);
await writer.Complete();
Using styles
// 1. Define styles
var styleBuilder = new StylesSheetBuilder();
staticSomeStyle = styleBuilder.GetOrAdd(
new Style(
Format: "0.00%",
Font: new Font(Size: 24, Color: Color.DodgerBlue),
Fill: new Fill(Color: Color.Crimson, FillPattern.Gray125),
Borders: new Borders(
Left: new Border(BorderStyle.Thick, Color.BlueViolet),
Right: new Border(BorderStyle.MediumDashed, Color.Coral)),
Alignment: new Alignment(HorizontalAlignment.Center, VerticalAlignment.Center, false)));
// 2. Build styles. We can reuse single style sheet many times to increase performance.
// Style sheet is immutable and thread safe.
staticStyleSheet = styleBuilder.Build();
// 3. Using styles
await using var writer = new ExcelWriter(file, staticStyleSheet);
await writer.StartSheet("First sheet");
await writer.StartRow(15); //optional row height specified
writer.AddCell(123, staticSomeStyle); // all cells support style reference
await writer.Complete();
Shared strings
Shared strings decrease the size of the resulting file when it contains repeated strings. It's implemented as unique list of strings, and cell contains only reference to the list index.
// 1. We can use global shared strings table
var tableBuilder = new SharedStringTableBuilder();
staticSharedStringRef1 = tableBuilder.GetOrAdd("String");
staticSharedStringRef2 = tableBuilder.GetOrAdd("Another String");
// 2. Build table
staticSharedStringTable = sharedStringTableBuilder.Build();
// 3. Using shared strings
await using var writer = new ExcelWriter(stream, sharedStringTable: staticSharedStringTable);
await writer.StartSheet("First sheet");
await writer.StartRow();
// using refernce from global table
writer.AddCell(staticSharedStringRef1);
writer.AddCell(staticSharedStringRef2);
// using local dictionary automatically maintained in the ExcelWriter instance
writer.AddCellWithSharedString("Some string from exteranal store");
writer.AddCellWithSharedString("Some string from exteranal store");
await writer.Complete();
Benchmarks
Benchmarks results and source code
Real world report
100 columns: numbers, dates, strings
Method | RowsCount | Mean | Error | StdDev | Allocated |
---|---|---|---|---|---|
RealWorldReport | 100 | 1.387 ms | 0.0200 ms | 0.0178 ms | 14.65 KB |
RealWorldReport | 1000 | 11.347 ms | 0.2221 ms | 0.1969 ms | 14.43 KB |
RealWorldReport | 10000 | 105.855 ms | 1.5015 ms | 1.2538 ms | 15.82 KB |
RealWorldReport | 100000 | 1,028.135 ms | 19.5208 ms | 20.8870 ms | 29.59 KB |
RealWorldReport | 500000 | 5,142.961 ms | 73.5973 ms | 65.2421 ms | 92.4 KB |
OpenXml comparison
Method | RowsCount | Mean | Error | StdDev | Gen 0 | Allocated |
---|---|---|---|---|---|---|
ExcelWriter | 10 | 407.7 us | 7.08 us | 12.94 us | 3.4180 | 15 KB |
OpenXml | 10 | 556.5 us | 6.20 us | 5.50 us | 21.4844 | 89 KB |
ExcelWriter | 100 | 1,746.2 us | 33.69 us | 31.51 us | 1.9531 | 15 KB |
OpenXml | 100 | 4,411.5 us | 83.48 us | 74.00 us | 78.1250 | 338 KB |
ExcelWriter | 1000 | 15,604.7 us | 167.38 us | 156.56 us | - | 18 KB |
OpenXml | 1000 | 45,436.9 us | 826.08 us | 1,236.43 us | 666.6667 | 2,817 KB |
ExcelWriter | 10000 | 163,471.9 us | 1,875.97 us | 1,754.79 us | - | 46 KB |
OpenXml | 10000 | 437,536.5 us | 3,856.09 us | 3,606.99 us | 6000.0000 | 27,613 KB |
ExcelWriter | 100000 | 1,556,695.5 us | 26,935.45 us | 23,877.57 us | - | 463 KB |
OpenXml | 100000 | 4,239,805.0 us | 57,404.41 us | 50,887.52 us | 67000.0000 | 275,596 KB |
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net6.0 is compatible. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.
-
net6.0
- No dependencies.
-
net8.0
- No dependencies.
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
1.5.5 | 97 | 9/19/2024 |
1.5.4 | 109 | 6/23/2024 |
1.5.3 | 100 | 6/3/2024 |
1.5.2 | 93 | 5/30/2024 |
1.5.1 | 85 | 5/10/2024 |
1.5.0 | 100 | 5/3/2024 |
1.4.1 | 105 | 4/27/2024 |
1.4.0 | 106 | 4/26/2024 |
1.3.0 | 331 | 12/25/2022 |
1.2.0 | 443 | 3/31/2022 |
1.1.0 | 423 | 2/25/2022 |
1.0.2 | 413 | 2/18/2022 |
1.0.1 | 365 | 12/15/2021 |
1.0.0 | 356 | 12/13/2021 |