BigExcelCreator 2.2.2022.32620

There is a newer version of this package available.
See the version list below for details.
dotnet add package BigExcelCreator --version 2.2.2022.32620                
NuGet\Install-Package BigExcelCreator -Version 2.2.2022.32620                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="BigExcelCreator" Version="2.2.2022.32620" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add BigExcelCreator --version 2.2.2022.32620                
#r "nuget: BigExcelCreator, 2.2.2022.32620"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install BigExcelCreator as a Cake Addin
#addin nuget:?package=BigExcelCreator&version=2.2.2022.32620

// Install BigExcelCreator as a Cake Tool
#tool nuget:?package=BigExcelCreator&version=2.2.2022.32620                

Big Excel Creator

Create Excel files using OpenXML SAX with styling. This is specially useful when trying to output thousands of rows

Nuget Build status Quality Gate Status Lines of Code Coverage

Table of Contents

Usage

  1. Instantiate class BigExcelWriter using either a file path or a stream (MemoryStream is recommended).

  2. Open a new Sheet using CreateAndOpenSheet

  3. For every row, use BeginRow and EndRow

    • If you want to hide a row, pass true when calling BeginRow
  4. Between BeginRow and EndRow, use WriteTextCell to write a cell.

    Alternatively, you can use WriteTextRow to write an entire row at once, using the same format.

    Starting on version 1.1, text cells can be written using the shared strings table, wich should reduce the generated file size. See Shared Strings below

  5. Use WriteFormulaCell or WriteFormulaRow to insert formulas.

  6. Use WriteNumberCell or WriteNumberRow to insert numbers. This is useful if you need to do any calculation later on.

  7. Use CloseSheet to finish.

  8. If needed, repeat steps 2 → 5 to write to another sheet

Shared Strings

If the same text appears across different sheets, using the shared strings table may help reduce the generated file size. In order to do this, simply set to true the useSharedStrings parameter when calling WriteTextCell or WriteTextRow.

Example

using BigExcelCreator;

....

MemoryStream stream = new MemoryStream();
using (BigExcelWriter excel = new(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
    excel.CreateAndOpenSheet("Sheet Name");
    excel.BeginRow();
    excel.WriteTextCell("Cell content");
    excel.WriteTextCell(123); // write as number. This allows to use formulas.
    excel.WriteTextCell(456);
    excel.WriteFormulaCell("SUM(B1:C1)");
    excel.EndRow();
    excel.BeginRow(true);
    excel.WriteTextCell("This row is hidden");
    excel.EndRow();
    excel.CloseSheet();
}

Data Validation

Use AddListValidator to restrict possible values to be written to a cell by an user.

    excel.CreateAndOpenSheet("Sheet Name");
    
    ...    
    
    // Only allow values included in sheet named "vals" between cells A1 and A6
    // when writing to cells between B2 and B10 of the current sheet.
    string range = "B2:B10";
    string formula = "vals!$A$1:$A$6";
    excel.AddValidator(range, formula);
    
    excel.CloseSheet();

Styling and formatting

Column formatting

When calling CreateAndOpenSheet, pass IList<Column> as second parameter. Each element represents a single column. Only the CustomWidth, Width and Hidden are used.

Width represents the column width in characters (Same unit as when resizing in Excel).

CustomWidth allows the use of Width.

Hidden hides the column.

Example

List<Column> cols = new List<Column> {
    new Column{CustomWidth = true, Width=10},   // A
    new Column{CustomWidth = true, Width=15},   // B
    new Column{CustomWidth = true, Width=18},   // c
};

excel.CreateAndOpenSheet("Sheet Name", cols);

Hide Sheet

CreateAndOpenSheet accepts as third parameter a SheetStateValues variable.

  • SheetStateValues.Visible (default): Sheet is visible
  • SheetStateValues.Hidden: Sheet is hidden
  • SheetStateValues.VeryHidden: Sheet is hidden and cannot be unhidden from Excel's UI.

Merge Cells

In order to merge a range of cells while a sheet is open, use MergeCells with a range.

excel.MergeCells("A1:A5");

Styling

First, the elements that define a style (font, fill, border and, optionally, numbering format) must be created.

font1 = new Font(new Bold(),
            new FontSize { Val = 11 },
            new Color { Rgb = new HexBinaryValue { Value = "000000" } },
            new FontName { Val = "Calibri" });

fill1 = new Fill(
            new PatternFill { PatternType = PatternValues.Gray125 });
fill2 = new Fill(
            new PatternFill (
                new ForegroundColor { Rgb = new HexBinaryValue { Value = "FFFF00" } }
            )
            { PatternType = PatternValues.Solid });

border1 = new Border(
            new LeftBorder(
                new Color { Rgb = new HexBinaryValue { Value = "FFD3D3D3" } }
            )
            { Style = BorderStyleValues.Thin },
            new RightBorder(
                new Color { Rgb = new HexBinaryValue { Value = "FFD3D3D3" } }
            )
            { Style = BorderStyleValues.Thin },
            new TopBorder(
                new Color { Rgb = new HexBinaryValue { Value = "FFD3D3D3" } }
            )
            { Style = BorderStyleValues.Thin },
            new BottomBorder(
                new Color { Rgb = new HexBinaryValue { Value = "FFD3D3D3" } }
            )
            { Style = BorderStyleValues.Thin },
            new DiagonalBorder());

numberingFormat1 = new NumberingFormat { NumberFormatId = 164, FormatCode = "0,.00;(0,.00)" };

After that, a new style list can be created and new styles inserted. Remember to name you styles.

StyleList list = new StyleList();
string name1 = "name1";
string name2 = "name2";

list.NewStyle(font1, fill1, border1, numberingFormat1, name1);
list.NewStyle(font1, fill2, border1, numberingFormat1, name2);

When instantiating BigExcelWriter, use the result of calling GetStylesheet as the stylesheet parameter. Then, when writing a cell, you can use the name given earlier to format it.

MemoryStream stream = new MemoryStream();
using (BigExcelWriter excel = new(stream,
                                    DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook
                                    stylesheet: list.GetStylesheet()))
{
    int index_style_name1 = list.GetIndexByName(name1);
    int index_style_name2 = list.GetIndexByName(name2);
    excel.CreateAndOpenSheet("Sheet Name");
    excel.BeginRow();
    excel.WriteTextCell("This has a gray patterned background", index_style_name1);
    excel.WriteTextCell("This has a yellow background", index_style_name2);
    excel.EndRow();
    excel.CloseSheet();
}

If you're planning to use Conditional Formatting, you must also create differential styles here. To do so, follow the same instructions as above, replacing NewStyle with NewDifferentialStyle.

All parameters of NewDifferentialStyle are optional, except name. Of the optional parameters, at least one must be present.

// place this before calling list.GetStylesheet() and new BigExcelWriter()
list.NewDifferentialStyle("RED", font: new Font(new Color { Rgb = new HexBinaryValue { Value = "FF0000" } }));

Comments

In order to add a note (formerly known as comment) to a cell, while a sheet is open, call the Comment method.

excel.CreateAndOpenSheet("Sheet Name");
excel.BeginRow();

excel.WriteTextCell("This has a gray patterned background", index_style_name1);
excel.WriteTextCell("This has a yellow background", index_style_name2);

excel.Comment("test A1 another sheet", "A1");

excel.EndRow();

excel.Comment("test E2 another sheet", "B1", "Author");

excel.CloseSheet();

Autofilter

In order to add an Autofilter, call AddAutofilter while on a sheet.

excel.BeginRow();
// ...
excel.AddAutofilter(range); // Range's height must be 1. Example: A1:J1
// ...
excel.EndRow();

Conditional Formatting

In order to use conditional formatting, you should define Differential styles (see Styling)

On every case below:

  • reference ⇒ A range of cells to apply the conditional formatting to
  • format ⇒ The id of the Differential style. Obtain it using GetIndexDifferentialByName after creating it with NewDifferentialStyle

Formula

To define a conditional style by formula, use AddConditionalFormattingFormula(string reference, string formula, int format).

  • formula defines the expression to use. Use a fixed range using $ to anchor the reference to a cell. Avoid using $ to make the reference "walk" with the range. This is useful when referencing the current cell.
excel.AddConditionalFormattingFormula("A1:A10", "A1<5", styleList.GetIndexDifferentialByName("RED"));

Cell Is

Format cells based on their contents using AddConditionalFormattingCellIs

  • Operator defines how to compare values.
  • value defines the value to compare the cell to.
  • value2 If the operator requires 2 numbers (eg: Between and NotBetween), the second value goes here.
excel.AddConditionalFormattingCellIs("A1:A20", ConditionalFormattingOperatorValues.LessThan, "5", styleList.GetIndexDifferentialByName("RED"));
excel.AddConditionalFormattingCellIs("A1:A20", ConditionalFormattingOperatorValues.Between, "3", styleList.GetIndexDifferentialByName("RED"), "7");

Duplicated Values

Format duplicated values using AddConditionalFormattingDuplicatedValues

excel.AddConditionalFormattingDuplicatedValues("A1:A10", styleList.GetIndexDifferentialByName("RED"));

Page Layout

Sheet options

Gridlines

While working on a sheet, the property ShowGridLinesInCurrentSheet controls whether the gridlines are shown. Enabled by default.

While working on a sheet, the property PrintGridLinesInCurrentSheet controls whether the gridlines are printed. Disabled by default.

Headings

While working on a sheet, the property ShowRowAndColumnHeadingsInCurrentSheet controls whether the headings (Column letters and row numbers) are shown. Enabled by default.

While working on a sheet, the property PrintRowAndColumnHeadingsInCurrentSheet controls whether the headings (Column letters and row numbers) are printed. Disabled by default.

Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp1.0 was computed.  netcoreapp1.1 was computed.  netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard1.3 is compatible.  netstandard1.4 was computed.  netstandard1.5 was computed.  netstandard1.6 was computed.  netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net35 is compatible.  net40 is compatible.  net403 was computed.  net45 was computed.  net451 was computed.  net452 was computed.  net46 is compatible.  net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 is compatible.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen30 was computed.  tizen40 was computed.  tizen60 was computed. 
Universal Windows Platform uap was computed.  uap10.0 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

# Changelog

## 2.2
### Added
- Show or hide, print or not, Gridlines and headings
### Changed
- Bumped dependencies version to current latest since the reason to lower it no longer applies.

## 2.1
### Changed
- Lowered minimum required version of DocumentFormat.OpenXml. It is still recommended to use the latest version when possible.
### Added
- Ability to merge cells

## 2.0
### Changed
- Renamed class BigExcelWritter to BigExcelWriter.
 Sorry for the typo.
### Added
- Conditional formatting
   - By formula
   - By value (Cell Is)
   - Duplicated values

## 1.1
### Added
- Text cells can now be written as shared strings instead of as value. This should reduce the final file's size when the same text is repeated across sheets

## 1.0
- First version considered to be "stable".
- Moved repository to GitHub (previously was on Azure DevOps)
### Changed
- Renamed `WriteTextCell<int>` to `WriteNumberCell<int>`. `WriteTextCell<string>` is still in use.

## 1.0.265
### Added
- Hide rows and columns
- Write formula to cell

## 0.2022.262
### Added
- Create autofilter
- Ranges are now validated

## 0.2022.261
### Added
- Add comments to cells

## 0.2022.256
### Added
- Styling and formatting

## 0.2022.253
- Initial version