Use Formulas in Excel
22 Dec 20226 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 = 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 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