Gooseberry.ExcelStreaming 1.8.4

Gooseberry.ExcelStreaming

Create Excel files with high performance and low memory allocations.

Features

• Extremely fast streaming write (100 columns * 100 000 rows in 0.58 second, 14Kb allocated memory)
• Most basic excel column types are supported (incl. hyperlinks)
• Shared strings, utf8 binary and interpolated 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,
    AutoFilter: "A1:G1" /* or new((1,1), (7,1)) */); // table headers filter. A1-style string or indexes 

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
        .AddEmptyCells(5) // five empty cells
        .AddCell(42) // int
        .AddCell(999_999_999_999_999) // long 
        .AddCell(DateTime.Now) // DateTime
        .AddCell(DateOnly.FromDateTime(DateTime.Now.Date)) // DateOnly
        .AddCell(123.765M) // decimal
        .AddCell(Math.PI) //double (note: very slow)
        .AddCell("string") // string
        .AddCell('#') // char
        .AddCellUtf8String("string"u8) // utf8 string
        .AddCellSharedString("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

IUtf8SpanFormattable support (.net8+)

Effective way to write standard and custom types to cell without string allocation.

//write any IUtf8SpanFormattable value as string cell or numeric cell
//format, format provider and styles are optional

writer.AddCellString(Guid.NewGuid(), ['N']);  

float value = 0.3f;
writer.AddCellNumber(value);

Interpolated strings (.net8+)

Writing interpolated strings to cells without allocations (benchmark).

writer.AddCell($"{person.FirstName} {person.LastName}, age {person.Age}");
writer.AddCell($"Now is {DateTime.Now}");  

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();

Row Attributes

//add row with specified height
await writer.StartRow(height: 15);

//or specify optional row attributes
await writer.StartRow(new RowAttributes(
    Height: 25, 
    OutlineLevel: 1,
    IsCollapsed: true,
    IsHidden: true)); 

Using styles

// 1. Define styles

var styleBuilder = new StylesSheetBuilder();

staticSomeStyle = styleBuilder.GetOrAdd(
    new Style(
        Format: "0.00%", // or StandardFormat.PercentPrecision2
        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();

Format property can be string, int (any valid numFmtId) or StandardFormat enum value

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.AddCellSharedString("Some string from exteranal store");
writer.AddCellSharedString("Some string from exteranal store");

await writer.Complete();

Benchmarks & performance

Benchmarks results and source code.

Please note .net 9 can be 2x faster than .net 8 for some cases.
Benchmarks below use .net 9.0 runtime.

Real world report

100 columns: numbers, dates, strings

OpenXML and SpreadCheetah comparison

Full results and benchmark code.