Apart from recording data, Excel also has calculation functions, which make analyzing and processing data efficient and simple. There are two calculation tools in Excel, formulas and functions. Formulas are user-defined statements of calculations, while functions are predefined formulas. Users can either input their own formulas in cells or simply invoke functions to calculate. This article is going to show how to insert or read formulas and functions in Excel workbooks using Spire.XLS for Java.
- Insert Formulas and Functions into an Excel Worksheet
- Read Formulas and Functions from an Excel Worksheet
Install Spire.XLS for Java
First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>14.11.0</version>
</dependency>
</dependencies>
Insert Formulas and Functions into an Excel Workbook
Spire.XLS for Java provides a Worksheet.getCellRange().setFormula() method to add a formula or a function to a specific cell. The detailed steps for inserting a formula or a function into a worksheet are as follows.
- Create an object of Workbook.
- Get the first worksheet using Workbook.getWorksheets().get() method.
- Write data in cells and format the cells.
- Add formulas or functions to specific cells using Worksheet.getCellRange().setFormula() method.
- Save the workbook using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class insertFormulas {
public static void main(String[] args) {
//Create an object of Workbook
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Declare two variables: currentRow、currentFormula
int currentRow = 1;
String currentFormula = null;
//Set the column width
sheet.setColumnWidth(1, 32);
sheet.setColumnWidth(2, 16);
//Write data in cells
sheet.getCellRange(currentRow,1).setValue("Test data:");
sheet.getCellRange(currentRow,2).setNumberValue(1);
sheet.getCellRange(currentRow,3).setNumberValue(2);
sheet.getCellRange(currentRow,4).setNumberValue(3);
sheet.getCellRange(currentRow,5).setNumberValue(4);
sheet.getCellRange(currentRow,6).setNumberValue(5);
//Write text in cells
currentRow += 2;
sheet.getCellRange(currentRow,1).setValue("Formulas") ; ;
sheet.getCellRange(currentRow,2).setValue("result");
//Format cells
CellRange range = sheet.getCellRange(currentRow,1,currentRow,2);
range.getStyle().getFont().isBold(true);
range.getStyle().setKnownColor(ExcelColors.LightGreen1);
range.getStyle().setFillPattern(ExcelPatternType.Solid);
range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);
//Arithmetic operation
currentFormula = "=1/2+3*4";
sheet.getCellRange(++currentRow,1).setText("'"+ currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//Date function
currentFormula = "=TODAY()";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("YYYY/MM/DD");
//Time function
currentFormula = "=NOW()";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");
//IF function
currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//PI function
currentFormula = "=PI()";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//Trigonometric function
currentFormula = "=SIN(PI()/6)";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//Count function
currentFormula = "=Count(B1:F1)";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//Maximum function
currentFormula = "=MAX(B1:F1)";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//Average function
currentFormula = "=AVERAGE(B1:F1)";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//Summation function
currentFormula = "=SUM(B1:F1)";
sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//Save the Workbook
workbook.saveToFile("InsertFormulas.xlsx",FileFormat.Version2013);
}
}
Read Formulas and Functions from an Excel Workbook
To read formulas in Excel worksheets, we can use CellRange.hasFormula() method to detect if a cell contains a formula and then use CellRange.getFormula() method to get the formula if there is. The detailed steps are as follows.
- Create an object of Workbook.
- Load an Excel workbook using Workbook.loadFromFile() method.
- Get a Worksheet using Workbook.getWorksheets().get() method.
- Loop through the cells in the worksheet.
- Detect if a cell contains a formula using CellRange.hasFormula() method. If it does, get the formula using CellRange.getFormula() method and print it out.
- Save the workbook using Workbook.saveToFile() method.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class readFormulas {
public static void main(String[] args) {
//Create an object of Workbook
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("InsertFormulas.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Loop through the cells within B1:B13
for (Object cell: sheet.getCellRange("B1:B13")
) {
CellRange cellRange = (CellRange)cell;
//Detect if a cell has a formula
if (cellRange.hasFormula()){
//Print out the formula
String certainCell = String.format("Cell[%d, %d] has a formula: ",cellRange.getRow(),cellRange.getColumn());
System.out.println(certainCell + cellRange.getFormula());
}
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
