A formula performs calculations or other actions on the data in your worksheet.

One of the of Microsoft Excel's compelling features is being able to process data with formulas and functions. MS Office Excel provides a lot of built-in functions and formulas that helps users to perform complex calculations quickly. XsExcel control also provides these functions and formulas that help C# developers compute values easily.

This guide page is showing how to add formula to cells in XLSX and XLS spreedsheet using C# .NET.


// Create a new workbook
Workbook workBook = new Workbook();
// Create a new worksheet
Sheet sheet = workBook.CreateSheet();

// Add 5 row data
for (int i = 0; i < 5; i++)
{
    Row row = sheet.CreateRow(i);
    // Insert 2 numbers separeted in first column and second column
    for (int j = 0; j < 2; j++)
    {                    
        Cell cell = row.CreateCell(j);
        cell.SetValue(i * 10 + j);
    }

    // Create a cell to add formula
    Cell formulaCell = row.CreateCell(2);
    // Add formula to cell, please note: don't input '=' at the beginning of formula
    StringBuilder sbuilder = new StringBuilder();
    sbuilder.AppendFormat("SUM(A{0}:B{0})", (i + 1).ToString());
    formulaCell.SetFormula(sbuilder.ToString());
}

// Save and show the spreedsheet
if (File.Exists("sample.xlsx")) File.Delete("sample.xlsx");  
workBook.Save("sample.xlsx");
Process.Start("sample.xlsx");

Notice - If you used the trial version of XsExcel SDK, can only edit, insert or get the first 300 row data!

More Excel tutorial