Tables
await using var outputStream = File.Create("table-basic.xlsx");
await using var spreadsheet = await Spreadsheet.CreateNewAsync(outputStream);
var worksheetOptions = new WorksheetOptions();
worksheetOptions.Column(2).Width = 18;
await spreadsheet.StartWorksheetAsync("Sheet", worksheetOptions);
var table = new Table(TableStyle.Light9);
spreadsheet.StartTable(table);
string[] headerNames = ["City", "Temperature (°C)"];
DataCell[] paris = [new DataCell("Paris"), new DataCell(14)];
DataCell[] bangkok = [new DataCell("Bangkok"), new DataCell(34)];
await spreadsheet.AddHeaderRowAsync(headerNames);
await spreadsheet.AddRowAsync(paris);
await spreadsheet.AddRowAsync(bangkok);
await spreadsheet.FinishAsync();
await using var outputStream = File.Create("table-total-row.xlsx");
await using var spreadsheet = await Spreadsheet.CreateNewAsync(outputStream);
var worksheetOptions = new WorksheetOptions();
worksheetOptions.Column(2).Width = 18;
await spreadsheet.StartWorksheetAsync("Sheet", worksheetOptions);
var table = new Table(TableStyle.Medium3);
table.Column(1).TotalRowLabel = "Average";
table.Column(2).TotalRowFunction = TableTotalRowFunction.Average;
spreadsheet.StartTable(table);
string[] headerNames = ["City", "Temperature (°C)"];
DataCell[] paris = [new DataCell("Paris"), new DataCell(14)];
DataCell[] bangkok = [new DataCell("Bangkok"), new DataCell(34)];
await spreadsheet.AddHeaderRowAsync(headerNames);
await spreadsheet.AddRowAsync(paris);
await spreadsheet.AddRowAsync(bangkok);
await spreadsheet.FinishAsync();
A worksheet can have multiple tables, but only one table can be active at a time. Before starting a new table, the previous table must be finished by calling FinishTableAsync.
await using var outputStream = File.Create("table-multiple.xlsx");
await using var spreadsheet = await Spreadsheet.CreateNewAsync(outputStream);
var worksheetOptions = new WorksheetOptions();
worksheetOptions.Column(2).Width = 18;
await spreadsheet.StartWorksheetAsync("Sheet", worksheetOptions);
string[] headerNames = ["City", "Temperature (°C)"];
var table1 = new Table(TableStyle.Dark5);
spreadsheet.StartTable(table1);
await spreadsheet.AddHeaderRowAsync(headerNames);
await spreadsheet.AddRowAsync([new DataCell("Paris"), new DataCell(14)]);
await spreadsheet.AddRowAsync([new DataCell("Bangkok"), new DataCell(34)]);
await spreadsheet.FinishTableAsync();
await spreadsheet.AddRowAsync([]);
var table2 = new Table(TableStyle.Dark6);
spreadsheet.StartTable(table2);
await spreadsheet.AddHeaderRowAsync(headerNames);
await spreadsheet.AddRowAsync([new DataCell("Dakar"), new DataCell(21)]);
await spreadsheet.AddRowAsync([new DataCell("Lima"), new DataCell(24)]);
await spreadsheet.FinishTableAsync();
await spreadsheet.FinishAsync();
A calculated column can be created with a formula. Note that formulas must use the Open XML syntax, which in some cases has some differences compared to the formula syntax in Excel. E.g. [#This Row], in the Open XML syntax is equivalent to @ in the Excel syntax.
await using var outputStream = File.Create("table-calculated-column.xlsx");
await using var spreadsheet = await Spreadsheet.CreateNewAsync(outputStream);
var worksheetOptions = new WorksheetOptions();
worksheetOptions.Column(1).Width = 18;
await spreadsheet.StartWorksheetAsync("Sheet", worksheetOptions);
var table = new Table(TableStyle.Medium4, "MyProductTable");
spreadsheet.StartTable(table);
string[] headerNames = ["Product", "Qtr 1", "Qtr 2", "Total"];
var grandTotalFormula = new Formula("SUM(MyProductTable[[#This Row],[Qtr 1]:[Qtr 2]])");
Cell[] chocolate = [new("Chocolate"), new(744), new(162), new Cell(grandTotalFormula)];
Cell[] tomatoes = [new("Tomatoes"), new(345), new(377), new Cell(grandTotalFormula)];
await spreadsheet.AddHeaderRowAsync(headerNames);
await spreadsheet.AddRowAsync(chocolate);
await spreadsheet.AddRowAsync(tomatoes);
await spreadsheet.FinishAsync();
The cells in the main part of a table can be styled like regular cells. Cells in the total row can be styled by using TotalRowStyle on table column options:
await using var outputStream = File.Create("table-total-row.xlsx");
await using var spreadsheet = await Spreadsheet.CreateNewAsync(outputStream);
var worksheetOptions = new WorksheetOptions();
worksheetOptions.Column(2).Width = 18;
await spreadsheet.StartWorksheetAsync("Sheet", worksheetOptions);
var table = new Table(TableStyle.Medium3);
table.Column(1).TotalRowLabel = "Average";
table.Column(2).TotalRowFunction = TableTotalRowFunction.Average;
table.Column(2).TotalRowStyle = new Style { Fill = { Color = Color.Red } };
spreadsheet.StartTable(table);
var greenStyle = new Style { Fill = { Color = Color.Green } };
var greenStyleId = spreadsheet.AddStyle(greenStyle);
string[] headerNames = ["City", "Temperature (°C)"];
Cell[] paris = [new Cell("Paris"), new Cell(14, greenStyleId)];
Cell[] bangkok = [new Cell("Bangkok"), new Cell(34, greenStyleId)];
await spreadsheet.AddHeaderRowAsync(headerNames);
await spreadsheet.AddRowAsync(paris);
await spreadsheet.AddRowAsync(bangkok);
await spreadsheet.FinishAsync();