If you have worked with Formulas in Excel, you must know the power of using formulas to perform calculations and analysis. Using formulas is essential for creating effective spreadsheets and making informed decisions. Excel has a wide range of formulas including mathematical operators, functions, and cell references to fully automate data calculations and analysis.
In our series of articles on working with NPOI API in .NET, we explained how to install NPOI in .NET and create and save Excel workbooks. We also demonstrated how to add a comment to an Excel file with NPOI using C# in your .NET applications. In this article, we will see how to use NPOI to add a formula to a worksheet in .NET. But let us first see how we add a formula in a Workbook using Microsoft Excel.
Insert Formula in Spreadsheet using Microsoft Excel
Working with formulas in Excel is simple and easy to implement. Excel provides pre-built formulas in a wide range of fields such as Mathematics, Finance, Statistics, and more. You can start with formulas in Excel by writing “=” in a cell and you will start seeing formulas offered by Excel.
Steps to Insert Formula in Spreadsheet with Microsoft Excel
If you want to add two values in cells B1 + B2, you can use the follwoing steps to calculate the sum of these two values.
- Enter first value in B1 cell
- Enter the second value in B2 cell
- Enter the summation formula in destination cell as =sum(B1,B2)
- Press Enter or the Tick Sign in the formula bar
This will calculate the sum of these two values and display result in cell B3.
Use Formula in Excel using NPOI in C#
Now that we are clear about how to use formulas in Worksheet using Microsoft Excel, let’s have a look at how .NET application developers can use NPOI API in C#/VB.NET to achieve the same.
Installing NPOI for .NET
Before you can start using NPOI in your .NET application, you need to install NPOI in your project to get started. You can learn about this in our detailed instructions guide for Installing NPOI for .NET.
Add Formula in Excel Spreadsheet in C#
Now that your development environment is ready, create a console-based project and add the following lines of code.
//Create workbook IWorkbook wb = new XSSFWorkbook(); ISheet ws = wb.CreateSheet("MySheet"); //Create cells IRow row = ws.CreateRow(0); ICell cell1 = row.CreateCell(0); ICell cell2 = row.CreateCell(1); ICell sumCell = row.CreateCell(3); //Set the value of the cells cell1.SetCellValue(10); cell2.SetCellValue(15); //Add formula sumCell.SetCellFormula("sum(B1:B2)"); //Save the file FileStream file = File.Create("ExcelFormula.xlsx"); wb.Write(file, false); file.Close();
When you execute the above code, an XLSX file will be generated that will have the formula set for adding the values.
In this article, we saw how to add a formula in an Excel workbook using NPOI API for .NET. The code is written in C# but can also be re-written in VB.NET and should work equally. Stay tuned for more examples of working with Excel workbooks using NPOI.