A Formula is defined as an equation that performs calculations and returns the result value in cells. It contains several functions, cell references, operators on functions. A formula can be simple or complex based on users needs. A Function can be defined as a built-in operation in Excel that returns a result value in cells. A function contains only cell references and values.
In this article, I’ll show you how to apply formulas and functions in Excel cells by using Free Spire.XLS for Java.
Add Spire.Xls.jar as dependency
Method 1: Download Free Spire.XLS for Java pack, unzip it and you’ll get Spire.Doc.jar file from the “lib” folder. Import the jar file in your project as a dependency.
Method 2: If you are creating a Maven project, you can easily add the jar dependency by adding the following configurations to the pom.xml.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId> e-iceblue </groupId>
<artifactId>spire.xls.free</artifactId>
<version>2.2.0</version>
</dependency>
</dependencies>
Using the code
import com.spire.xls.*;
public class InsertFormulas {
public static void main(String[] args) {
//Create a Workbook object
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 test data into 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 or functions:") ; ;
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.BlueGray);
range.getStyle().setFillPattern(ExcelPatternType.Solid);
range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);
//Formula
currentFormula = "=1/2+3*4";
sheet.getCellRange(++currentRow,1).setText(currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//IF function
currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
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);
//MAX 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);
//SUM function
currentFormula = "=SUM(B1:F1)";
sheet.getCellRange(++currentRow,1).setText(currentFormula);
sheet.getCellRange(currentRow,2).setFormula(currentFormula);
//NOW 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");
//SECOND function
currentFormula = "=SECOND(11)";
sheet.getCellRange(++currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//MINUTE function
currentFormula = "=MINUTE(12)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//MONTH function
currentFormula = "=MONTH(9)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//DAY function
currentFormula = "=DAY(10)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//TIME function
currentFormula = "=TIME(4,5,7)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//DATE function
currentFormula = "=DATE(6,4,2)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//TODAY function
currentFormula = "=TODAY()";
sheet.getCellRange(currentRow,1).setText(currentFormula);
sheet.getCellRange(currentRow++,2).getStyle().setNumberFormat("YYYY/MM/DD");
//RAND function
currentFormula = "=RAND()";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//HOUR function
currentFormula = "=HOUR(12)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//MOD function
currentFormula = "=MOD(5,3)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//WEEKDAY function
currentFormula = "=WEEKDAY(3)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//YEAR function
currentFormula = "=YEAR(23)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//NOT function
currentFormula = "=NOT(true)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//OR function
currentFormula = "=OR(true)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//AND function
currentFormula = "=AND(TRUE)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//VALUE function
currentFormula = "=VALUE(30)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//LEN function
currentFormula = "=LEN(\"world\")";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//MID function
currentFormula = "=MID(\"world\",4,2)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//ROUND function
currentFormula = "=ROUND(7,3)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//SIGN function
currentFormula = "=SIGN(4)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//INT function
currentFormula = "=INT(200)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//ABS function
currentFormula = "=ABS(-1.21)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//LN function
currentFormula = "=LN(15)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//EXP function
currentFormula = "=EXP(20)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//SQRT function
currentFormula = "=SQRT(40)";
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);
//COS function
currentFormula = "=COS(9)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
//SIN function
currentFormula = "=SIN(45)";
sheet.getCellRange(currentRow, 1).setText(currentFormula);
sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
workbook.saveToFile("output/ApplyFormulas.xlsx",ExcelVersion.Version2013);
}
}
Top comments (0)