SwiftExcel.Pro Pro

SwiftExcel.Pro is the commercial edition. It keeps the same streaming engine and performance profile as the free edition, but adds multi-sheet workbooks, full cell styling (fonts, colors, borders, fills, alignment), number formats (currency, percentage, dates, custom), custom Excel formulas (including hyperlinks), merged cells, auto filters, freeze panes and custom row heights.

Installation

dotnet add package SwiftExcel.Pro

Bring the namespaces into scope:

using SwiftExcel.Pro;
using SwiftExcel.Pro.Styles; // for Style, Color, NumberFormat, FontName, etc.

The library targets netstandard2.0 and depends on SharpCompress.

Licensing & activation

Set your serial number once at process startup, before instantiating any ExcelWriter:

LicenseInfo.SerialNumber = "YOUR-LICENSE-KEY";

Without a valid serial the workbook is still produced, but the first sheet is renamed to "Evaluation Period". See pricing for available plans.

Quick start

The minimal working example from the official sandbox:

LicenseInfo.SerialNumber = "YOUR-LICENSE-KEY";

using (var ew = new ExcelWriter("C:/temp/test.xlsx"))
{
    for (var row = 1; row <= 100; row++)
    {
        for (var col = 1; col <= 10; col++)
        {
            ew.Write($"row:{row}-col:{col}", col, row);
        }
    }
}

Streaming model — what you must know

• Cells must be written in increasing (row, col) order within a sheet. Going backwards throws SwiftExcelException.
• col, row, sheetNumber and styleNumber indices are 1-based for cells/rows/cols/sheets and 0-based for styles (style 0 = the implicit default style).
• When writing to multiple sheets, you must finish (or skip) earlier sheets before moving to a later sheet number. Going back to a lower sheet number throws.
• One ExcelWriter per file, single-threaded. Not thread-safe.
• Output is to a file path only (the Free edition has a Stream constructor; the Pro edition currently does not).

API reference

Namespace: SwiftExcel.Pro (styling types in SwiftExcel.Pro.Styles).

ExcelWriter — 8 constructor overloads

Implements IDisposable. All constructors take a file path plus any combination of one/many sheets and one/many styles:

Style numbers are 1-based in Write(), with 0 reserved for the implicit no-styling default. So when you pass a single Style to the constructor, you reference it as styleNumber: 1.

Write()

WriteFormula()

Save() and Dispose()

DataType (enum)

FormulaType (enum)

Sheet

MergeRange

FilterRange

FreezeType (enum)

Style

From SwiftExcel.Pro.Styles. Pass one or many Style objects to the ExcelWriter constructor; reference them in Write() via the 1-based styleNumber argument (0 = no style).

Color

Static helper class with hex string constants. Predefined values: Red, DarkRed, Orange, Yellow, LightGreen, Green, LightBlue, Blue, DarkBlue, Purple, Black, White, Grey. For anything else use Color.Custom("RRGGBB").

var style = new Style
{
    FillColor  = Color.LightBlue,
    FontColor  = Color.Custom("3366CC")
};

NumberFormat

Static helper class. The cell's NumberFormat only takes effect on cells written with DataType.Number.

FontName / FontSize (enums)

FontName contains 200+ Office fonts. Common values: Arial, ArialBlack, ArialNarrow, Calibri, CalibriLight, Cambria, Candara, Consolas, Constantia, Corbel, CourierNew, Garamond, Georgia, Impact, SegoeUI, Tahoma, TimesNewRoman, TrebuchetMS, Verdana, …

FontSize covers Font1…Font36 plus Font48 and Font72.

AlignHorizontal / AlignVertical (enums)

AlignHorizontal: Left (default), Right, Center.

AlignVertical: Bottom (default), Center, Top.

LicenseInfo (static)

Configuration (static)

Data & structure — Basic write

using (var ew = new ExcelWriter("C:/temp/test.xlsx"))
{
    for (var row = 1; row <= 100; row++)
    {
        for (var col = 1; col <= 10; col++)
        {
            ew.Write($"row:{row}-col:{col}", col, row);
        }
    }
}

Data & structure — Sheet configuration

var sheet = new Sheet
{
    Name = "Monthly Report",
    RightToLeft = true,
    ColumnsWidth = new List<double> { 10, 12, 8, 8, 35 }
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", sheet))
{
    ew.Write("data", 1, 1);
}

Data & structure — Multiple sheets

Pass an IList<Sheet> and target a sheet via the sheetNumber argument:

var sheets = new List<Sheet>
{
    new Sheet { Name = "Summary"   },
    new Sheet { Name = "Details"   },
    new Sheet { Name = "Details 3" },
    new Sheet { Name = "Details 4" },
    new Sheet { Name = "Details 5" }
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", sheets))
{
    ew.Write("value for the 1st sheet", 1, 1, 1);
    ew.Write("value for the 2nd sheet", 1, 1, 2);
    ew.Write("value for the 4th sheet", 1, 2, 4);
}

You may skip sheets — sheet 3 above is left empty and is auto-finalized when sheet 4 is started. You may not, however, jump back to a lower sheet number.

Formulas — Built-in Average / Count / Max / Sum

using (var ew = new ExcelWriter("C:/temp/test.xlsx"))
{
    const int col = 1;
    var row = 1;
    for (; row <= 20; row++)
    {
        ew.Write(row.ToString(), col, row, 1, 0, DataType.Number);
    }

    ew.WriteFormula(FormulaType.Average, col, ++row, col, 1, 20);
    ew.WriteFormula(FormulaType.Count,   col, ++row, col, 1, 20);
    ew.WriteFormula(FormulaType.Max,     col, ++row, col, 1, 20);
    ew.WriteFormula(FormulaType.Sum,     col, ++row, col, 1, 20);
}

Formulas — Custom (any Excel function)

Write any Excel formula by passing the formula without the leading = and using DataType.CustomFormula:

using (var ew = new ExcelWriter("C:/temp/test.xlsx"))
{
    const int col = 1;
    var row = 1;
    for (; row <= 20; row++)
    {
        ew.Write(row.ToString(), col, row, 1, 0, DataType.Number);
    }

    ew.Write("A20/A10",        col, ++row, 1, 0, DataType.CustomFormula);
    ew.Write("SUM(A1:A20)-A26", col, ++row, 1, 0, DataType.CustomFormula);
}

Special values — Hyperlinks (critical pattern)

SwiftExcel.Pro renders hyperlinks via Excel's HYPERLINK function combined with DataType.CustomFormula. Inside C# you have to escape the inner double-quotes with \":

using (var ew = new ExcelWriter("C:/temp/test.xlsx"))
{
    ew.Write(
        "HYPERLINK(\"https://google.com\", \"google link\")",
        1, 1, 1, 0, DataType.CustomFormula);
}

Excel will render "google link" as a clickable, blue, underlined hyperlink. To use the URL itself as the label, repeat the URL string in both arguments. The same approach works for mailto links (HYPERLINK("mailto:foo@bar.com", "Email Foo")) and in-workbook references (HYPERLINK("#'Sheet 2'!A1", "Go")).

Data types — Dates with .ToOADate() (critical pattern)

Excel stores dates as OLE Automation date doubles. To produce a formatted date cell you need both:

1. A Style with NumberFormat.Custom("dd/mmm/yyyy hh:mm:ss") (or any Excel date format string).
2. The DateTime value converted via .ToOADate() and written as DataType.Number, with the matching styleNumber.

var style = new Style
{
    NumberFormat = NumberFormat.Custom("dd/mmm/yyyy hh:mm:ss")
};

var dateToExport = new DateTime(2023, 8, 21, 10, 51, 18);
using (var ew = new ExcelWriter("C:/temp/test.xlsx", style))
{
    ew.Write($"{dateToExport.ToOADate()}", 1, 2, 1, 1, DataType.Number);
}

Why this is non-obvious: writing the date as a string looks correct in the cell but breaks Excel's sort, filter and arithmetic features. The OADate conversion produces a real numeric date that Excel can compute on, while the matching number format makes it display the way a human expects.

Number formats — Currency

var style = new Style
{
    FontSize     = FontSize.Font11,
    NumberFormat = NumberFormat.Currency
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", style))
{
    ew.Write("2612.11", 1, 2, 1, 1, DataType.Number);
}

Number formats — Percentage

var style = new Style
{
    FontSize     = FontSize.Font11,
    NumberFormat = NumberFormat.Percentage
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", style))
{
    ew.Write("0.3851", 1, 2, 1, 1, DataType.Number);
}

Number formats — Custom (e.g. shares)

var style = new Style
{
    FontSize     = FontSize.Font11,
    NumberFormat = NumberFormat.Custom("#,##0.0000")
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", style))
{
    ew.Write("1357.5712", 1, 2, 1, 1, DataType.Number);
}

Styling — Fonts & alignment

var style = new Style
{
    FontColor          = Color.Black,
    FontName           = FontName.TimesNewRoman,
    FontSize           = FontSize.Font16,
    FontStyleBold      = true,
    FontStyleItalic    = true,
    FontStyleUnderline = true,
    AlignHorizontal    = AlignHorizontal.Left,
    AlignVertical      = AlignVertical.Center,
    WrapText           = true
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", style))
{
    ew.Write("font with\ndecoration", 1, 2, 1, 1);
}

Note that WrapText = true is required for the \n to render as a real line break.

Styling — Borders & fill colors (multiple styles)

var styles = new List<Style>
{
    new Style
    {
        BorderLeft = true, BorderRight = true,
        BorderTop  = true, BorderBottom = true,
        BorderColor = Color.Black
    },
    new Style { FillColor = Color.Grey }
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", styles))
{
    ew.Write("borders",    2, 2, 1, 1);  // styleNumber: 1
    ew.Write("fill color", 3, 3, 1, 2);  // styleNumber: 2
}

Layout — Merging & auto filter

var sheet = new Sheet
{
    Name = "Monthly Report",
    FilterRange = new FilterRange(1, 1, 5),
    MergeRanges = new List<MergeRange>
    {
        new MergeRange(2, 3, 5),
        new MergeRange(3, 2, 7)
    }
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", sheet))
{
    ew.Write("value 1", 1, 2);
    ew.Write("value 2", 2, 2);
}

The filter sets dropdowns on A1:Elast. The merge ranges merge 5 cells horizontally starting at (col=3, row=2) and 7 cells starting at (col=2, row=3).

Layout — Freeze panes

var sheet = new Sheet
{
    Name        = "Monthly Report",
    FreezeType  = FreezeType.Row,
    FreezeCount = 25
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", sheet))
{
    for (var row = 1; row <= 100; row++)
    {
        for (var col = 1; col <= 10; col++)
        {
            ew.Write($"row:{row}-col:{col}", col, row);
        }
    }
}

Layout — Custom row heights

Use null as a list entry to keep Excel's default for that row; non-null values produce a custom height.

var sheet = new Sheet
{
    RowsHeight = new List<double?> { 0, null, 35 }
};

using (var ew = new ExcelWriter("C:/temp/test.xlsx", sheet))
{
    ew.Write("data", 1, 1);  // hidden (height 0)
    ew.Write("data", 1, 2);  // default height
    ew.Write("data", 1, 3);  // 35pt tall
}

Special values — Invalid XML & reserved characters

Configuration.UseEnchancedXmlEscaping = true;

var sheet = new Sheet { Name = "Monthly\"< Report" };

using (var ew = new ExcelWriter("C:/temp/test.xlsx", sheet))
{
    ew.Write("<",  1, 1);
    ew.Write(">",  2, 1);
    ew.Write("&",  3, 1);
    ew.Write("'",  4, 1);
    ew.Write("\"", 5, 1);
}

Both cell values and sheet names are XML-escaped automatically.

FAQ