LargeXlsx

You can merge a rectangle of cells using AddMergedCell.

Important Rules:

1. Memory Warning: Merged cell ranges must be accumulated in RAM and written at the end of the worksheet. Using a large number of merged cells may cause high memory consumption.
2. Content Placement: Content must be written to the top-left cell of the merged range. The spreadsheet application will not display content in the other cells of the range.
3. Manual Skipping: After writing to the top-left cell, you must explicitly skip the remaining columns/rows in the range using SkipColumns or SkipRows to ensure the insertion point moves correctly for subsequent data.

Methods:

• AddMergedCell(int fromRow, int fromColumn, int rowCount, int columnCount): Specifies an arbitrary rectangle.
• AddMergedCell(int rowCount, int columnCount): Uses the current insertion point as the top-left corner.
• Shortcut: Using columnSpan > 1 in a Write method is a shortcut for creating a 1-row-high merged cell and advancing the insertion point.

// Example: Merging A7:C8 (2 rows, 3 columns)
xlsxWriter.AddMergedCell(7, 1, 2, 3);

// 1. Write content to the top-left cell (A7)
xlsxWriter.Write("Merged Content");

// 2. Skip the rest of the columns in row 7 (B7 and C7)
xlsxWriter.SkipColumns(2);

// 3. Move to the next row (row 8)
xlsxWriter.BeginRow();

// 4. Skip the columns in row 8 (A8, B8, C8)
xlsxWriter.SkipColumns(3);

// Now writing starts at D8

Styling allows you to apply colors and formatting to cells. A style (XlsxStyle) is composed of five elements:

1. Font (XlsxFont): Face, size, and color.
2. Fill (XlsxFill): Background color.
3. Border (XlsxBorder): Style and color.
4. Number Format (XlsxNumberFormat): How numbers appear (e.g., decimals, percentages, dates).
5. Alignment (XlsxAlignment): Horizontal/vertical alignment and rotation.

Usage Patterns:

• Create from scratch: Use the XlsxStyle constructor with all five components.
• Modify existing styles: Use the .With(...) methods to clone an existing style while replacing one specific component.
• Apply to cells: Use the Write method on a cell, SetDefaultStyle on the XlsxWriter, or specify styles for entire columns/rows.

Performance Note: The library deduplicates styles in constant time. While you shouldn't worry about performance for many styles, a very large number of unique styles will increase RAM usage as the stylesheet is held in memory until the XlsxWriter is disposed.

// class XlsxStyle
public XlsxStyle(XlsxFont font, XlsxFill fill, XlsxBorder border, XlsxNumberFormat numberFormat, 
                 XlsxAlignment alignment);
public XlsxStyle With(XlsxFont font);
public XlsxStyle With(XlsxFill fill);
public XlsxStyle With(XlsxBorder border);
public XlsxStyle With(XlsxNumberFormat numberFormat);
public XlsxStyle With(XlsxAlignment alignment);

To support streamed writes, content must be written strictly from top to bottom and left to right. The 'insertion point' advances as you write.

• CurrentRowNumber: Returns the one-based index of the next cell to be written.
• CurrentColumnNumber: Returns the one-based index of the next cell to be written. Note that this may be 0 if BeginRow has not been called yet (attempting to write in this state will throw an exception).
• CurrentColumnName: Returns the column name (e.g., "A") at the current insertion point.
• GetRelativeColumnName(int offsetFromCurrentColumn): Returns the column name relative to the current position.
• GetColumnName(int columnIndex): Returns the column name for an absolute column index (1-16384).

// Accessing current position
int row = writer.CurrentRowNumber;
int col = writer.CurrentColumnNumber;
string colName = writer.CurrentColumnName;

Version 2.x introduces significant performance improvements by using native UTF-8 strings and reducing memory allocations. Key changes include:

Breaking Changes

• SharpCompress: On .NET Core 3.1+, SharpCompress is no longer used. If you relied on it as a transitive dependency, you must now reference it explicitly. (Note: It is still used for .NET Standard 2.0 targets).
• XlsxWriter Constructor: The compressionLevel parameter now uses a custom enum instead of SharpCompress types. Update your arguments to use the new enum equivalents.
• ZIP64: The useZip64 parameter has been removed from the XlsxWriter constructor; ZIP64 is now enabled by default for very large files.

New Features

• Asynchronous API: XlsxWriter now implements IAsyncDisposable. Use await using to ensure final writes are committed asynchronously.
• Manual Commits: New methods Commit, TryCommit, CommitAsync, and TryCommitAsync allow you to manually flush the internal buffer to the underlying stream. The commitThreshold (default 56 KiB) controls when TryCommit methods trigger a write.
• Async Overloads: BeginWorksheet and BeginRow now have Async overloads.

You can apply complex styling using XlsxStyle, XlsxFont, XlsxFill, XlsxBorder, and XlsxAlignment. Styles can be composed using the .With() method to create variations of a base style. You can also define column formatting during BeginWorksheet and use methods like AddMergedCell, WriteFormula, and SetAutoFilter.

using var stream = new FileStream("Simple.xlsx", FileMode.Create, FileAccess.Write);
using var xlsxWriter = new XlsxWriter(stream);
var headerStyle = new XlsxStyle(
    new XlsxFont("Segoe UI", 9, Color.White, bold: true),
    new XlsxFill(Color.FromArgb(0, 0x45, 0x86)),
    XlsxStyle.Default.Border,
    XlsxStyle.Default.NumberFormat,
    XlsxAlignment.Default);
var highlightStyle = XlsxStyle.Default.With(new XlsxFill(Color.FromArgb(0xff, 0xff, 0x88)));
var dateStyle = XlsxStyle.Default.With(XlsxNumberFormat.ShortDateTime);
var borderedStyle = highlightStyle.With(
    XlsxBorder.Around(new XlsxBorder.Line(Color.DeepPink, XlsxBorder.Style.Dashed)));

xlsxWriter
    .BeginWorksheet("Sheet 1",
        columns: new[] { XlsxColumn.Unformatted(count: 2), XlsxColumn.Formatted(width: 20) })
    .SetDefaultStyle(headerStyle)
    .BeginRow().AddMergedCell(2, 1).Write("Col1").Write("Top2").Write("Top3")
    .BeginRow().Write().Write("Col2").Write("Col3")
    .SetDefaultStyle(XlsxStyle.Default)
    .BeginRow().Write("Row3").Write(42).WriteFormula(
        $"{xlsxWriter.GetRelativeColumnName(-1)}{xlsxWriter.CurrentRowNumber}*10", highlightStyle)
    .BeginRow().Write("Row4").SkipColumns(1).Write(new DateTime(2020, 5, 6, 18, 27, 0), dateStyle)
    .SkipRows(2)
    .BeginRow().Write("Row7", borderedStyle, columnSpan: 2).Write(3.14159265359)
    .SetAutoFilter(2, 1, xlsxWriter.CurrentRowNumber - 1, 3);

Data validation allows you to add constraints to cell content. You create an XlsxDataValidation object and then apply it to a range using AddDataValidation on the XlsxWriter instance.

Creating Validation Rules

You can use the constructor or the static List helper for string constants.

Key Parameters:

• validationType: Specifies if validation is for numeric, integer, date, text length, or a List.
• formula1: The value or range to compare against. For lists, use a comma-separated string of quoted constants (e.g., "\"item1,item2\"") or a cell range reference.
• showDropDown: Note: In some applications (Excel/LibreOffice), this property appears to work backwards; the dropdown may show when this is false.
• errorStyle: Determines if invalid content is a blocking error, warning, or notice.

Applying Validation

// Apply to a specific range
xlsxWriter.AddDataValidation(fromRow, fromColumn, rowCount, columnCount, dataValidation);

// Apply to the current insertion point range
xlsxWriter.AddDataValidation(rowCount, columnCount, dataValidation);

// Apply to the entire worksheet (applies to all cells in the worksheet)
xlsxWriter.AddDataValidation(dataValidation);

// Example: Create a dropdown list validation
var validation = XlsxDataValidation.List(new[] { "Option1", "Option2", "Option3" });

// Apply it to the next 10 rows and 1 column
xlsxWriter.AddDataValidation(10, 1, validation);

You can control how a worksheet is split for printing using horizontal (row) or vertical (column) page breaks.

Methods:

• AddRowPageBreakBefore(int rowNumber): Places a horizontal break before the specified row.
• AddColumnPageBreakBefore(int columnNumber): Places a vertical break before the specified column.
• AddRowPageBreak(): Places a horizontal break before the current row (at the insertion point).
• AddColumnPageBreak(): Places a vertical break before the current column (at the insertion point).

Constraints:

• Attempting to place a break before row 1 or column 1 (Column A) results in an ArgumentOutOfRangeException.
• Memory Warning: Page breaks are accumulated in RAM and written at the end of the worksheet. Using a very large number of distinct page breaks may cause high memory consumption.

// class XlsxWriter
public XlsxWriter AddRowPageBreakBefore(int rowNumber);
public XlsxWriter AddColumnPageBreakBefore(int columnNumber);
public XlsxWriter AddRowPageBreak();
public XlsxWriter AddColumnPageBreak();

The Write method is the primary way to insert data into cells. It supports several types, each with different memory and file size implications:

• Nothing: A styled empty cell. Use repeatCount to write multiple consecutive empty cells.
• String: An inline literal string. Low memory consumption, but can result in larger files.
• Shared string: A deduplicated string stored in a lookup table. Produces smaller files but high memory consumption if many unique strings are used.
• Number: int, double, or decimal. Note that Excel/LibreOffice treat these as double, so expect potential precision loss.
• Date and time: DateTime values. You must apply a date/time number format via XlsxStyle for them to appear correctly.
• Boolean: bool values, appearing as TRUE or FALSE.
• Formula: A string interpreted as a calculation. Unless you provide a pre-calculated result, the spreadsheet application will calculate it upon opening.

Use the columnSpan parameter to let a cell span multiple columns (creates a merged cell).

// Examples of different Write overloads
xlsxWriter.Write("string value");
xlsxWriter.Write(123.45);
xlsxWriter.Write(DateTime.Now);
xlsxWriter.Write(true);
xlsxWriter.WriteFormula("SUM(A1:A10)");
// Shared string (better for file size, worse for RAM)
xlsxWriter.WriteSharedString("repeated text");