Gooseberry.ExcelStreaming 1.6.0

Gooseberry.ExcelStreaming

Create Excel files with high performance and low memory allocations.

Features

• Extremely fast streaming write (100 columns * 100 000 rows in 0.65 second, 20Kb 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(Math.PI); //double
    writer.AddCell("string"); // string
    writer.AddCell('#'); // char
    writer.AddCellUtf8String("string"u8); // utf8 string
    writer.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);

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

await writer.Complete();

Benchmarks

Benchmarks results and source code

Real world report

100 columns: numbers, dates, strings

OpenXml comparison