Using Java to process excel file

Java Excel API is a open source project.  We can use it to read, create, update excel file.

1. Read data sheet from a excel file
Step 1. Create a workbook
import java.io.*;
import jxl.*;
… … … …
try
{
//create a Workbook object from an exist excel file
    InputStream is = new FileInputStream(sourcefile);
    jxl.Workbook rwb = Workbook.getWorkbook(is);
}
catch (Exception e)
{
    e.printStackTrace();
}

Step 2. Get the excel sheet with the workbook object.
//get the first sheet.
Sheet rs = rwb.getSheet(0);
We also can get he sheet with its name.

Step 3. Get the cell content from the sheet.
//get the content from first row, first col.
Cell c00 = rs.getCell(0, 0);
String strc00 = c00.getContents();

//get the content from first row, second col.
Cell c10 = rs.getCell(1, 0);
String strc10 = c10.getContents();

//get the content from second row, second col.
Cell c11 = rs.getCell(1, 1);
String strc11 = c11.getContents();

System.out.println("Cell(0, 0)" + " value : " + strc00 + "; type : " + c00.getType());
System.out.println("Cell(1, 0)" + " value : " + strc10 + "; type : " + c10.getType());
System.out.println("Cell(1, 1)" + " value : " + strc11 + "; type : " + c11.getType());
 
The conten can be any type of data, such as numeric, text and date. The return is always a string.
If you know exactly the type of content, you may use the following code.
String strc00 = null;
double strc10 = 0.00;
Date strc11 = null;

Cell c00 = rs.getCell(0, 0);
Cell c10 = rs.getCell(1, 0);
Cell c11 = rs.getCell(1, 1);

if(c00.getType() == CellType.LABEL)
{
    LabelCell labelc00 = (LabelCell)c00;
    strc00 = labelc00.getString();
}
if(c10.getType() == CellType.NUMBER)
{
    NmberCell numc10 = (NumberCell)c10;
    strc10 = numc10.getValue();
}
if(c11.getType() == CellType.DATE)
{
    DateCell datec11 = (DateCell)c11;
    strc11 = datec11.getDate();
}
System.out.println("Cell(0, 0)" + " value : " + strc00 + "; type : " + c00.getType());
System.out.println("Cell(1, 0)" + " value : " + strc10 + "; type : " + c10.getType());
System.out.println("Cell(1, 1)" + " value : " + strc11 + "; type : " + c11.getType());
When you ge the cell object, you may get the type of the cell through getType() method.

When you finish the operation with excel file, you must close all the objects about the excel, for example:
rwb.close();

Method provided by Workbook
1. . int getNumberOfSheets()
Return the number of sheets in workbook.
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
int sheets = rwb.getNumberOfSheets();

2. Sheet[] getSheets()
Return the array of sheet objects in the workbook.
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
Sheet[] sheets = rwb.getSheets();

3. String getVersion()
Get the version the API you are using.
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
String apiVersion = rwb.getVersion();

Method provided by Sheet object.
1. String getName()
Return the sheet name.
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
String sheetName = rs.getName();

2. int getColumns()
Return the number of columns in the sheet
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
int rsColumns = rs.getColumns();

3. Cell[] getColumn(int column)
Return all the cells in a column.
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
Cell[] cell = rs.getColumn(0);

4. int getRows()
Return the rows count in the sheet
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
int rsRows = rs.getRows();

5. Cell[] getRow(int row)
Return all the cells in a row..
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
Cell[] cell = rs.getRow(0);

6. Cell getCell(int column, int row)
Return a specific cell according to the row and col.
jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
Cell cell = rs.getCell(0, 0);

2. Create a new Excel workbook
import java.io.*;
import jxl.*;
import jxl.write.*;
… … … …
try
{
//Create a workbook object
//Method 1：create a writable workbook
    jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));

//Method 2：pub the writable workbook in a output stream
/*
    OutputStream os = new FileOutputStream(targetfile);
    jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
*/
}
catch (Exception e)
{
    e.printStackTrace();
}

//Create sheet object.
jxl.write.WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);

//1.add label object
jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell");
ws.addCell(labelC);

//add a object with fond formatting
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelCF = new jxl.write.Label(1, 0, "This is a Label Cell", wcfF);
ws.addCell(labelCF);

//add a oject with color formatting
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
jxl.write.Label labelCFC = new jxl.write.Label(1, 0, "This is a Label Cell", wcfFC);
ws.addCell(labelCF);

//2.add numeric object
jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
ws.addCell(labelN);

//add numeric object with a formatting.
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF);

//3.add boolean object
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
ws.addCell(labelB);

//4.add datetime object
jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());
ws.addCell(labelDT);

//add a datetime object with a formatting
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF);
ws.addCell(labelDTF);

//write the sheet object
wwb.write();

//close the object
wwb.close();

3. Copy and Update the sheet
//create a workbook object
jxl.Workbook rw = jxl.Workbook.getWorkbook(new File(sourcefile));

jxl.write.WritableWorkbook  wwb = Workbook.createWorkbook(new File(targetfile), rw);
           
//Get the first sheet
jxl.write.WritableSheet ws = wwb.getSheet(0);

//Get the first cell
jxl.write.WritableCell wc = ws.getWritableCell(0, 0);
           
//judget the type of cell
if(wc.getType() == CellType.LABEL)
{
Label l = (Label)wc;
    l.setString("The value has been modified.");
}

//write the workbook
wwb.write();

//close the workbook
wwb.close();
rw.close();