how to use Formula class in jexcel

Java APIS

The following example will show you how you can create a Formula based cell in an excel sheet using the JExcel Api.

To create a Formula based cell in an excel worksheet you require to import the jxl.write.Formula class.

 In this example we have created a constructor of the Formula class, in which we have passed the value of column, row and the formula which have to be applied to the sheet. 
Formula(int c, int r, String formula)
This constructor constructs the formula, as you can see it contains three parameters which imply the following,
int c-> the column of the sheet
int r-> the row of the sheet
String formula-> the formula to created in the string form

The following code describes how can you add the formula cells in your worksheet  of the current workbook. In this code we have created a label named sum and then we entered two numbers in a number cell and then we displayed their sum in the formula cell. This cell will display the sum of the two numbers.

 
package r4r.co.in;

import java.io.IOException;
import java.io.File;
import jxl.Workbook;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.Label;
import jxl.format.*;
import jxl.write.Formula;
import jxl.write.Number;

public class FormulaInWorkbook {

	/**
	 * @param args
	 */
	public static void main(String[] args) throws IOException, WriteException{
		// TODO Auto-generated method stub

	try
	{
		WritableWorkbook workbook=Workbook.createWorkbook(new File("F:/JAVA PROJECTS/Jexcel/src/r4r/co/in/firstsheet.xls"));
	    WritableSheet sheet=workbook.createSheet("mysheet", 0);
       /* Code given below will set the font properties and alignment of data in the sheet */
		WritableFont wfobj=new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,UnderlineStyle.SINGLE);
	    WritableCellFormat cfobj=new WritableCellFormat(wfobj);
	    cfobj.setBackground(Colour.PINK);
	    cfobj.setAlignment(Alignment.RIGHT);
	    cfobj.setWrap(true);
	   /* Code given above will set the font properties and alignment of data in the sheet */ 
	    
	    Label lbl=new Label(0,0,"sum", cfobj);   // create a new label named sum
	    sheet.addCell(lbl);
	    Number n1=new Number(0,1,10);             // using Number class to enter numerical values in the sheet
	    sheet.addCell(n1);
	    n1=new Number(0,2,15);
	    sheet.addCell(n1);
	    Formula f1=new Formula(0,3,"SUM(A2:A3)");       // creating Formula for calculating the sum of the cells using Formula class
	    sheet.addCell(f1);
		
	    workbook.write();
	    workbook.close();
	}
	catch(IOException e)
	{
		e.printStackTrace();
	}
	catch(WriteException e)
	{
		e.printStackTrace();
	}
	}

}