how to modify an excel sheet using jexcel
how to modify an excel sheet using jexcel
| Previous | Home | Next |
This program shows how to create a full fleged excel sheet in an workbook.
In this example we have created a class named CreateModifyWorkbook. In this class we have created a main method which shows how to created a workbook and then we have called a method which modifies the sheet thus created.
We have created a main mehod in which we have created a main method in we have create a workbook, and then we have called a method Modifyexcelsheet which modifies the workbook thus created.
The following code shows how to create a workbook and then how to modify that workbook.
package r4r.co.in;
import java.io.File;
import java.net.URL;
import java.io.IOException;
import java.net.MalformedURLException;
import java.util.Date;
import java.util.Locale;
import jxl.CellType;
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;
import jxl.write.WritableCell;
import java.util.Calendar;
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);
Modifyexcelsheet(workbook);
workbook.write();
workbook.close();
}
catch(IOException e)
{
e.printStackTrace();
}
catch(WriteException e)
{
e.printStackTrace();
}
}
private static void Modifyexcelsheet(WritableWorkbook workbook)
{
WritableSheet sheet2= workbook.getSheet(0);
sheet2.setName("copied");
WritableCell wc=null;
/* Below given code is changing the Label value of sheet to be modified */
wc=sheet2.getWritableCell(0, 0);
if(wc.getType()==(CellType.LABEL))
{
Label lbl=(Label)wc;
lbl.setString("Modified");
}
/* Above code is changing the Label valud of sheet to be modified */
/* Below given code is changing the Number values of the sheet to be modified */
wc=sheet2.getWritableCell(2, 1);
if(wc.getType()==(CellType.NUMBER))
{
Number num=(Number)wc;
num.setValue(400.999);
}
/* Above given code is changing the Number values of the sheet to be modified */
/* Below given code is changing the Date values of the sheet to be modified */
wc=sheet2.getWritableCell(4, 1);
if(wc.getType()==(CellType.DATE))
{
DateTime dt = (DateTime) wc;
//WritableCellFormat scf=new WritableCellFormat(DateFormats.FORMAT12);
Calendar clndr= Calendar.getInstance(Locale.US);
clndr.set(2002, 2, 7);
Date d=clndr.getTime();
dt.setDate(d);
}
/* Above given code is changing the Date values of the sheet to be modified */
/* Below given code is changing the Hyperlinks of the sheet to be modified */
wc=sheet2.getWritableCell(6, 1);
WritableHyperlink hyperlinks[] = sheet2.getWritableHyperlinks();
for (int i = 0; i < hyperlinks.length; i++)
{
WritableHyperlink wh = hyperlinks[i];
if (wh.getColumn() == 6 && wh.getRow() == 1)
{
try
{
wh.setURL(new URL("http://r4r.co.in/"));
}
catch (MalformedURLException e)
{
e.printStackTrace();
}
}
}
/* Above given code is changing the Hyperlinks of the sheet to be modified */
}
}
| Previous | Home | Next |