How to Create Formula Cell in Excel Sheet using Java and Apache POI?
In the previous article, we have seen how to read data from the formula cell, here we are going to create the formula cell using Apache POI.
In this article, we are creating an Excel file with three columns consisting of values and the last column is the formula cell which is calculated from the other cells by defining a certain formula, here we are creating the formula cell by adding all other data in the cells.
To work will these, we needed the following software in your systems.
- Make sure your system has Java, if not download the latest java SDK version here.
- Create the Maven project.
For creating the maven project refer to this article on how to create a selenium maven project with eclipse.
- Add the Apache POI dependency into the POM.xml file
Program for creating Formula cell in Excel using Apache POI:
After creating the workbook, we have created the sheet in the workbook as “Numbers”.
Then we have created the first row in that sheet by the createRow() method.
After that, we have created the cells in the first row by row.createCell(cell number).setCellValue(value);
Now, in the fourth cell, we are using the formula to calculate the cell value by using the setCellFormula(formula).
In the end, we are going to write the values into the Excel sheet by creating a file output stream into the defined location
FileOutputStream file = new FileOutputStream(“specify the location for creating the file”);
Writing the cell values into the file by workbook.write(file),
After we run the code, we get the output and the Excel files are created.
Now we can notice that the Excel file is created in the defined location. I have given desktop location, so the file is created on my desktop
If you open the Excel file the cell values are created in the sheet with the formula cell.