Create Excel tables

Create a table with calculated columns.

Requirements

  • .NET Framework 4.8 for the released package path.
  • DRIT.Spreadsheet NuGet package.
  • No Microsoft Excel installation is required.

Run the example

dotnet run --project .\DRIT.Spreadsheet.Examples.Console\Tables\Tables.csproj -f net48

Source

View this example on GitHub

Code

using DRIT.Spreadsheet;
using DRIT.Spreadsheet.Examples.Shared;

var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
worksheet.Name = "Sales";
worksheet.GetRange("A1:C4").SetValue(new object[,] {
    { "Product", "Price", "Quantity" },
    { "Apple", 1.5, 4 },
    { "Banana", 0.9, 7 },
    { "Cherry", 2.25, 3 }
});
var table = worksheet.Tables.Add("A1:C4", "SalesTable", true);
table.Columns[2].CalculatedFormula = "=[Price]*[Quantity]";
var path = ExampleSupport.OutputPath("Tables.xlsx");
workbook.SaveAs(path);

var loaded = new Workbook(path);
var savedTable = loaded.Worksheets[0].Tables[0];
ExampleSupport.Require(savedTable.Name == "SalesTable", "The table name was not preserved.");
ExampleSupport.Require(savedTable.Columns.Count == 3, "The table columns were not preserved.");
ExampleSupport.Require(savedTable.Columns[2].CalculatedFormula == "=[Price]*[Quantity]", "The calculated column formula was not preserved.");
ExampleSupport.ReportRoundTrip(path, loaded);
Console.WriteLine($"Table: {savedTable.Name}, columns: {savedTable.Columns.Count}");