TinyXlsx 0.2.0

TinyXlsx

TinyXlsx is a lightweight and efficient library designed for writing Excel files in the XLSX format. It focuses on optimal performance by avoiding unnecessary overhead at all costs.

1. About
2. Benchmarks
3. Requirements
4. Features
5. Optimization

About

The library is built for .NET 8.0, ensuring compatibility with the latest versions of the framework. It supports two primary modes of writing data:

1. Writing to a MemoryStream for in-memory processing.
2. Writing to a FileStream to save the generated XLSX file directly to disk.

TinyXlsx focuses on simplicity, providing only the necessary functionality to perform basic XLSX file operations with minimal resource usage. Future versions may include more advanced features like reading and manipulating existing XLSX files.

Benchmarks

Writing to a MemoryStream

NA means the library does not support writing to a MemoryStream.

100 records, 12 columns:

10,000 records, 12 columns:

1,000,000 records, 12 columns:

Requirements

• .NET 8.0

Features

Supported:

1. Writing to a MemoryStream for in-memory processing.
2. Writing to a FileStream to save the generated Excel file directly to disk.
3. Precise cell and row positioning.
   1. By default, BeginRow automatically progresses to the next row, and WriteCellValue automatically writes to the cell in the next column.
   2. An index can be specified using BeginRowAt and WriteCellValueAt if a row or column must be skipped.
4. Writing formulas.

Not supported yet:

1. Reading an existing document.
2. Editing an existing document.
3. Images.
4. Charts.
5. Cell merging.
6. Rich text.
7. Conditional formatting.
8. Comments.
9. Hyperlinks.

Writing to a MemoryStream

By default the Workbook writes to a MemoryStream. This method should be used in scenarios where a file does not need to be stored locally but is instead intended to be sent directly to a client via a website or similar service.

using TinyXlsx;

using var workbook = new Workbook();
var worksheet = workbook.BeginSheet();

for (var i = 1; i <= 100; i++)
{
    worksheet
        .BeginRow()
        .WriteCellValue(true)
        .WriteCellValue(123456)
        .WriteCellValue(123.456m)
        .WriteCellValue(123.456)
        .WriteCellValue(DateTime.Now)
        .WriteCellValue(DateTime.Now, "yyyy/MM/dd")
        .WriteCellValue("Text")
        .WriteCellValue(123.456, "0.00")
        .WriteCellValue(123.456, "0.00%")
        .WriteCellValue(123.456, "0.00E+00")
        .WriteCellValue(123.456, "$#,##0.00")
        .WriteCellValue(123.456, "#,##0.00 [$USD]");
}
var stream = workbook.Close();

Writing to a FileStream

By supplying a string parameter to the Workbook constructor, the Workbook writes to a file.

using TinyXlsx;

using var workbook = new Workbook("fileName.xlsx");
var worksheet = workbook.BeginSheet();

for (var i = 1; i <= 100; i++)
{
    worksheet
        .BeginRow()
        .WriteCellValue(true)
        .WriteCellValue(123456)
        .WriteCellValue(123.456m)
        .WriteCellValue(123.456)
        .WriteCellValue(DateTime.Now)
        .WriteCellValue(DateTime.Now, "yyyy/MM/dd")
        .WriteCellValue("Text")
        .WriteCellValue(123.456, "0.00")
        .WriteCellValue(123.456, "0.00%")
        .WriteCellValue(123.456, "0.00E+00")
        .WriteCellValue(123.456, "$#,##0.00")
        .WriteCellValue(123.456, "#,##0.00 [$USD]");
}
workbook.Close();

Precise cell and row positioning

By default, BeginRow automatically progresses to the next row, and WriteCellValue automatically writes to the cell in the next column. A one-based index can be specified using BeginRowAt and WriteCellValueAt, if for example a row or column must be skipped. Going backwards is not supported due to the streaming nature of the library.

using TinyXlsx;

using var workbook = new Workbook();
var worksheet = workbook.BeginSheet();

worksheet.BeginRowAt(10); // Begins row 10.

worksheet.WriteCellValueAt(5, 123.456); // Writes in the fifth cell on row 10.

worksheet.BeginRow(); // Begins row 11.

worksheet.WriteCellValue(DateTime.Now); // Writes in the first cell on row 11.

var stream = workbook.Close();

Writing formulas

Formulas can be added using WriteCellFormula. The library does not validate or calculate any formula, it is written as-is into the cell.

using TinyXlsx;

using var workbook = new Workbook();
var worksheet = workbook.BeginSheet();

var i = 1;
for (; i <= 10; i++)
{
    worksheet
        .BeginRow()
        .WriteCellValue(0.1m)
        .WriteCellValue(0.2m)
        .WriteCellValue(0.3m)
        .WriteCellFormula($"=SUM(A{i}:C{i})");
}
i++;
worksheet
    .BeginRow()
    .WriteCellFormula($"=SUM(A1:A{i})")
    .WriteCellFormula($"=SUM(B1:B{i})")
    .WriteCellFormula($"=SUM(C1:C{i})");

var stream = workbook.Close();

Optimization

For in-memory scenarios the default capacity is set to 64 KB. However, if the document size is known to be much larger in advance, it is recommended to set an initial capacity which more closely aligns with this size. An initial capacity can be given to the Workbook constructor. The default MemoryStream will automatically resize as data is written, but setting a capacity upfront reduces the overhead caused by repeated internal buffer expansions.

using TinyXlsx;

var initialCapacity = 1024 * 1024 * 64; // 64 MB.
using var workbook = new Workbook(initialCapacity);
var worksheet = workbook.BeginSheet();

// Add data here...

var stream = workbook.Close();