How to Programmatically Add Calculated Columns to C# .NET Excel XLSX

Often data from data sources are not processed and thus do not provide the immediate information business users or analysts are looking for. To derive meaningful insights from the unprocessed data, it is required to perform calculations on the available data. For example, calculating profit/loss, customer retention rate, financial ratios, and many others.

In Microsoft Excel, one of the ways to perform such calculations is using the Calculated Columns in Tables. Calculated columns help to enter and maintain formulas in Excel tables.

Ready to Try It Out? Download Document Solutions Today!

Calculated Columns

A Calculated Column in Excel Table contains a single formula in any row of the column that adjusts for each row and automatically expands to include additional rows in that column. Thus, the formula is immediately and automatically extended to all the rows in a Table without using the Fill or Copy command.

In this blog, we'll see how to programmatically add Calculated Columns to Excel Tables using DsExcel API, previously GcExcel API, for .NET.

Use-case

For every business, the sales data usually contains information directly related to sales, such as unit production & selling cost, quantity produced & sold, buyer & seller details, and so on. However, to analyze the sales situations, business users want calculated information such as the total sales amount, profit/loss, sales margin, and several others.

Let’s see how we can programmatically calculate sales amount and profit/loss in figure & percent in an Excel table as shown below:

Add Calculated Column to Excel Table

Adding a calculated column in Excel Table using DsExcel API for .NET involves the following steps:

1. Create a workbook instance and open the Excel file.

Workbook workbook = new Workbook();
workbook.Open("ProductSales.xlsx");

2. Access the table and add new columns to store the calculated data.

ITable productTable = workbook.Worksheets["data"].Tables["ProductTable"];
productTable.Columns.Add(5).Name = "Sales Amount";
productTable.Columns.Add(6).Name = "Profit/Loss";
productTable.Columns.Add(7).Name = "Profit/Loss Margin";

3. Iterate through each row and use IRange.Formula to add calculation using structured reference formula as depicted in the code snippet below.

foreach (ITableRow row in productTable.Rows)
  {
      row.Range[5].Formula = "=ProductTable[@QtySold]*ProductTable[@SellingPrice]";
      row.Range[6].Formula = "=ProductTable[@[Sales Amount]] - ProductTable[@QtySold]*ProductTable[@ProductionCost]";
      row.Range[7].Formula = "=(ProductTable[@[Profit/Loss]]/ProductTable[@[Sales Amount]])";
  }

4. Format the columns and save the workbook.

productTable.Columns[7].DataBodyRange.NumberFormat = "0.0%";
workbook.Worksheets["data"].Columns.AutoFit();

workbook.Save("ProductSales_Updated.xlsx");

Download the sample.

Check out the DsExcel Online Demos to learn more about the programmatic approach of working with Excel Tables in .NET.

Ready to Try It Out? Download Document Solutions Today!