Use Formulas in Excel

20 May 202610 minutes to read

A formula is an expression that operates on values in a range of cells or a cell. Functions are predefined formulas in Excel.

The following code shows how to use formulas in Excel by adding values in a range of cells using the formula function sum and highlighting the resultant value with Interop and XlsIO for .NET.

Interop

private void ExcelFormulas()
{
    //Instantiate the application object
    var excelApp = new Microsoft.Office.Interop.Excel.Application();

    //Add a workbook
    Workbook workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value);

    //Get the first sheet
    Worksheet worksheet = (Worksheet)workbook.Sheets["Sheet1"];

    //Access cells A1, A2, A3, and A4
    Range cellA1 = worksheet.Range["A1"];
    Range cellA2 = worksheet.Range["A2"];
    Range cellA3 = worksheet.Range["A3"];
    Range cellA4 = worksheet.Range["A4"];

    //Set integer values in cells A1, A2, and A3
    cellA1.Value = 10;
    cellA2.Value = 20;
    cellA3.Value = 30;

    //Add formula in cell A4
    cellA4.Formula = "=Sum(A1:A3)";

    //Set the font bold in cell A4
    cellA4.Font.Bold = true;

    //Set the background color to yellow in cell A4
    cellA4.Interior.Color = XlRgbColor.rgbYellow;

    //Save the Excel file
    workbook.SaveCopyAs("InteropOutput_ExcelFormulas.xlsx");

    //Quit the application
    excelApp.Quit();
}
private void ExcelFormulas()
{
    //Instantiate the application object
    var excelApp = new Microsoft.Office.Interop.Excel.Application();

    //Add a workbook
    Workbook workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value);

    //Get the first sheet
    Worksheet worksheet = (Worksheet)workbook.Sheets["Sheet1"];

    //Access cells A1, A2, A3, and A4
    Range cellA1 = worksheet.Range["A1"];
    Range cellA2 = worksheet.Range["A2"];
    Range cellA3 = worksheet.Range["A3"];
    Range cellA4 = worksheet.Range["A4"];

    //Set integer values in cells A1, A2, and A3
    cellA1.Value = 10;
    cellA2.Value = 20;
    cellA3.Value = 30;

    //Add formula in cell A4
    cellA4.Formula = "=Sum(A1:A3)";

    //Set the font bold in cell A4
    cellA4.Font.Bold = true;

    //Set the background color to yellow in cell A4
    cellA4.Interior.Color = XlRgbColor.rgbYellow;

    //Save the Excel file
    workbook.SaveCopyAs("InteropOutput_ExcelFormulas.xlsx");

    //Quit the application
    excelApp.Quit();
}
Private Sub ExcelFormulas()
  'Instantiate the application object
  Dim excelApp = New Microsoft.Office.Interop.Excel.Application()

  'Add a workbook
  Dim workbook As Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)

  'Get the first sheet
  Dim worksheet As Worksheet = workbook.Sheets("Sheet1")

  'Access cells A1, A2, A3, and A4
  Dim cellA1 As Range = worksheet.Range("A1")
  Dim cellA2 As Range = worksheet.Range("A2")
  Dim cellA3 As Range = worksheet.Range("A3")
  Dim cellA4 As Range = worksheet.Range("A4")

  'Set integer values in cells A1, A2, and A3
  cellA1.Value = 10
  cellA2.Value = 20
  cellA3.Value = 30

  'Add formula in cell A4
  cellA4.Formula = "=Sum(A1:A3)"

  'Set the font bold in cell A4
  cellA4.Font.Bold = True

  'Set the background color to yellow in cell A4
  cellA4.Interior.Color = XlRgbColor.rgbYellow

  'Save the file
  workbook.SaveCopyAs("InteropOutput_ExcelFormulas.xlsx")

  'Quit the application
  excelApp.Quit()
End Sub

XlsIO

private void ExcelFormulas()
{
  using (ExcelEngine excelEngine = new ExcelEngine())
  {
    //Instantiate the application object
    IApplication application = excelEngine.Excel;

    //Create a workbook
    IWorkbook workbook = application.Workbooks.Create(1);

    //Get the first sheet
    IWorksheet worksheet = workbook.Worksheets[0];

    //Access cells A1, A2, A3, and A4
    IRange cellA1 = worksheet.Range["A1"];
    IRange cellA2 = worksheet.Range["A2"];
    IRange cellA3 = worksheet.Range["A3"];
    IRange cellA4 = worksheet.Range["A4"];

    //Set integer values in cells A1, A2, and A3
    cellA1.Value2 = 10;
    cellA2.Value2 = 20;
    cellA3.Value2 = 30;

    //Add formula in cell A4
    cellA4.Formula = "=Sum(A1:A3)";

    //Set the font bold in cell A4
    cellA4.CellStyle.Font.Bold = true;

    //Set the background color to yellow in cell A4
    cellA4.CellStyle.Interior.Color = Color.Yellow;

    //Save the workbook
    workbook.SaveAs("XlsIOOutput_ExcelFormulas.xlsx");
  }
}
private void ExcelFormulas()
{
  using (ExcelEngine excelEngine = new ExcelEngine())
  {
    //Instantiate the application object
    IApplication application = excelEngine.Excel;

    //Create a workbook
    IWorkbook workbook = application.Workbooks.Create(1);

    //Get the first sheet
    IWorksheet worksheet = workbook.Worksheets[0];

    //Access cells A1, A2, A3, and A4
    IRange cellA1 = worksheet.Range["A1"];
    IRange cellA2 = worksheet.Range["A2"];
    IRange cellA3 = worksheet.Range["A3"];
    IRange cellA4 = worksheet.Range["A4"];

    //Set integer values in cells A1, A2, and A3
    cellA1.Value2 = 10;
    cellA2.Value2 = 20;
    cellA3.Value2 = 30;

    //Add formula in cell A4
    cellA4.Formula = "=Sum(A1:A3)";

    //Set the font bold in cell A4
    cellA4.CellStyle.Font.Bold = true;

    //Set the background color to yellow in cell A4
    cellA4.CellStyle.Interior.Color = Color.Yellow;

    //Save the workbook
    workbook.SaveAs("XlsIOOutput_ExcelFormulas.xlsx");
  }
}
Private Sub ExcelFormulas()
  Using excelEngine As ExcelEngine = New ExcelEngine()
    'Instantiate the application object
    Dim application As IApplication = excelEngine.Excel

    'Create a workbook
    Dim workbook As IWorkbook = application.Workbooks.Create(1)

    'Get the first sheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Access cells A1, A2, A3, and A4
    Dim cellA1 As IRange = worksheet.Range("A1")
    Dim cellA2 As IRange = worksheet.Range("A2")
    Dim cellA3 As IRange = worksheet.Range("A3")
    Dim cellA4 As IRange = worksheet.Range("A4")

    'Set integer values in cells A1, A2, and A3
    cellA1.Value2 = 10
    cellA2.Value2 = 20
    cellA3.Value2 = 30

    'Add formula in cell A4
    cellA4.Formula = "=Sum(A1:A3)"

    'Set the font bold in cell A4
    cellA4.CellStyle.Font.Bold = True

    'Set the background color to yellow in cell A4
    cellA4.CellStyle.Interior.Color = Color.Yellow

    'Save as Excel file
    workbook.SaveAs("XlsIOOutput_ExcelFormulas.xlsx")
  End Using
End Sub