how to create an excel sheet using the jexcel

Java APIS

The following example is for showing you how can you create a full Excel WorkSheet using the JExcel API.

This program shows how to create a full fleged excel sheet in an workbook.

 This program shows how to create a full fleged excel sheet in an workbook and has got all the previous methods which have discussed earlier.

This code snippet shows you how can you create a full fleged excel sheet using the JExcel.

 
package r4r.co.in;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import jxl.Workbook;
import jxl.biff.formula.FormulaException;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Orientation;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


public class CreateWorkbook {

	/**
	 * @param args
	 */
	public static void main(String[] args) throws IOException, WriteException, FormulaException{
		// 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);
	    WritableFont wfobj=new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
	    WritableCellFormat cfobj=new WritableCellFormat(wfobj);
	    cfobj.setBackground(Colour.GREY_25_PERCENT);
	    cfobj.setAlignment(Alignment.LEFT);
	    cfobj.setOrientation(Orientation.HORIZONTAL);
	    cfobj.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM_DASH_DOT,Colour.BLACK);
	    
	    cfobj.setShrinkToFit(true);
	    cfobj.setWrap(true);
	    WritableFont wfobj2=new WritableFont(WritableFont.COURIER, 10, WritableFont.NO_BOLD);
	    WritableCellFormat wcf=new WritableCellFormat(wfobj2);
	    wcf.setAlignment(Alignment.LEFT);
	    wcf.setShrinkToFit(true);
	    wcf.setWrap(true);
	    Label lblName=new Label(0,0,"Names", cfobj);
	    Label lblFloatNum1=new Label(1,0,"Floatnum1",cfobj);
	    Label lblFloatNum2=new Label(2,0,"Floatnum2", cfobj);
	    Label lblFormula=new Label(3,0,"Formulas", cfobj);
	    Label lblDate=new Label(4,0,"Dates", cfobj);
	    Label lblImage= new Label(5,0,"Images", cfobj);
	    Label lblhyprlink=new Label(6,0,"Hyperlink", cfobj);
	    sheet.addCell(lblName);
	    lblName=new Label(0,1,"Ram",wcf);
	    sheet.addCell(lblName);
	    lblName=new Label(0,2,"Krishna", wcf);
	    sheet.addCell(lblName);
	    lblName=new Label(0,3,"Shiva", wcf);
	    sheet.addCell(lblName);
	    sheet.addCell(lblFloatNum1);
	    WritableCellFormat wcf1=new WritableCellFormat(NumberFormats.DEFAULT);
	    wcf1.setAlignment(Alignment.LEFT);
	    wcf1.setShrinkToFit(true);
	    wcf1.setWrap(true);
	    Number num=new Number(1,1,4.567778, wcf1);
	    sheet.addCell(num);
	    num=new Number(1,2, 5.657657, wcf1);
	    sheet.addCell(num);
	    num=new Number(1,3, 14.45456, wcf1);
	    sheet.addCell(num);
	    sheet.addCell(lblFloatNum2);
	    num=new Number(2,1,5.6787, wcf1);
	    sheet.addCell(num);
	    num=new Number(2,2, 39.56576, wcf1);
	    sheet.addCell(num);
	    num=new Number(2, 3, 7.000, wcf1);
	    sheet.addCell(num);
	    sheet.addCell(lblFormula);
	    WritableCellFormat wcfmuls=new WritableCellFormat();
	    wcfmuls.setAlignment(Alignment.LEFT);
	    Formula fmla=new Formula(3, 1, "SUM(B2:C2)", wcfmuls);
	    sheet.addCell(fmla);
	    fmla=new Formula(3, 2, "SUM(B3:C3)", wcfmuls);
	    sheet.addCell(fmla);
	    fmla=new Formula(3, 3, "SUM(B4:C4)", wcfmuls);
	    sheet.addCell(fmla);
	    sheet.addCell(lblDate);
	    WritableCellFormat wcfdate=new WritableCellFormat(DateFormats.FORMAT3);
	    wcfdate.setAlignment(Alignment.LEFT);
	    wcfdate.setWrap(true);
	    DateTime dtobj=new DateTime(4, 1, new Date(), wcfdate);
	    sheet.addCell(dtobj);
	    dtobj=new DateTime(4, 2, new Date(), wcfdate);
	    sheet.addCell(dtobj);
	    dtobj=new DateTime(4, 3, new Date(), wcfdate);
	    sheet.addCell(dtobj);
	    sheet.addCell(lblImage);
	    WritableImage imgobj=new WritableImage(5, 1, 1, 1, new File("F:/JAVA PROJECTS/Jexcel/Sunset.png"));
	    sheet.addImage(imgobj);
	    imgobj=new WritableImage(5, 2, 1, 1, new File("F:/JAVA PROJECTS/Jexcel/Winter.png"));
	    sheet.addImage(imgobj);
	    imgobj=new WritableImage(5, 3, 1, 1, new File("F:/JAVA PROJECTS/Jexcel/Blue hills.png"));
	    sheet.addImage(imgobj);
	    sheet.addCell(lblhyprlink);
	    WritableHyperlink whl=new WritableHyperlink(6, 1, new File("F:/JAVA PROJECTS/Jexcel"), "r4r projects");
	    sheet.addHyperlink(whl);
	    Formula fmula=new Formula(6, 2, "HYPERLINK(\"http://www.r4r.co.in\","+"\"Goto R4R\")");
	    sheet.addCell(fmula);
	    fmula=new Formula(6, 3, "HYPERLINK(\"http://www.r4r.in\","+"\"Goto R4R\")");
	    sheet.addCell(fmula);
	    workbook.write();
	    workbook.close();
	}
	catch(IOException e)
	{
		e.printStackTrace();
	}
	catch(WriteException e)
	{
		e.printStackTrace();
	}
	
	}
	
}