Gooseberry.ExcelStreaming 1.5.1

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

await writer.StartSheet("First sheet");

await foreach(var record in store.GetRecordsAsync(cancellationToken))
{
    await writer.StartRow();

    writer.AddEmptyCell(); // empty
    writer.AddCell(record.IntValue); // int
    writer.AddCell(DateTime.Now.Ticks); // long
    writer.AddCell(DateTime.Now); // DateTime
    writer.AddCell(123.765M); // decimal
    writer.AddCell("string"); // string
    writer.AddUtf8Cell("string"u8); // utf8 string
    writer.AddCellWithSharedString("shard"); // shared string
    // hyperlink
    writer.AddCell(new Hyperlink("https://[address]", "Label text")); 
}

// 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 16, 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();

More working samples

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

OpenXml comparison