Monday, August 2, 2010

Java : Read / Write/ Update XLS file using JExcel

Java Excel API - A Java API to read, write, and modify Excel spreadsheets


Java Excel API is a mature, open source java API enabling developers to read, write, and modifiy Excel spreadsheets dynamically.

Lets look at a Simple Example, which can be used for reading, writing and updating Excel (.xls) file, Even it's cell can be formatted according to user requirement.

To know more about Java Excel API click here

In this example we will first create Excel file (Sample.xls) using writeXLSFile() method and then we will read same Excel file (Sample.xls) using readXLSFile() method

For Java Excel API Click here

You will need to Download jxl.jar file


Assuming you have set jxl.jar in classpath


//SimpleJExcelExample.java


import java.io.File;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class SimpleJExcelExample {

public void writeXLSFile()
{
System.out.println("--- writeXLSFile() ---");

try
{
WritableWorkbook workbook = Workbook.createWorkbook(new File("Sample.xls"));

WritableSheet sheet = workbook.createSheet("Sheet1", 0);

WritableFont headerFont = new WritableFont (WritableFont.TIMES, 10,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE );
WritableCellFormat headerCells = new WritableCellFormat(headerFont);
headerCells.setBackground(Colour.TAN);

int column = 0;

for (int row=0;row <5;row++) {

Label label = new Label(row,column,"Header "+row,headerCells);
label.setCellFormat(headerCells);
sheet.addCell(label);
}

column ++;

WritableFont normalFont = new WritableFont(WritableFont.TIMES, 10);
WritableCellFormat normalCell = new WritableCellFormat(normalFont);

for (int col=column;col <5;col++)
{
for (int row=0;row <5;row++)
{

Label label = new Label(row,col,"cell "+row+" "+col,normalCell);
label.setCellFormat(normalCell);
sheet.addCell(label);
}
}

workbook.write();
workbook.close();

System.out.println("Sample.xls created sucessfully");
}
catch(Exception ee)
{
System.out.println("Exception :: "+ee);
}
}

public void readXLSFile()
{
System.out.println("--- readXLSFile() ---");

try
{
Workbook workbook = Workbook.getWorkbook(new File("Sample.xls"));

// Get the first sheet
Sheet sheet = workbook.getSheet(0);

for(int column = 0; column < sheet.getColumns(); column++)
{
for(int row = 0; row < sheet.getRows(); row++)
{
Cell cell = sheet.getCell(row,column);
System.out.print(cell.getContents());
}
System.out.println();
}
}
catch(Exception ee)
{
System.out.println("Exception :: "+ee);
}
}

public static void main(String[] args) throws Exception
{
SimpleJExcelExample obj = new SimpleJExcelExample();

obj.writeXLSFile();

obj.readXLSFile();
}
}



Console Output


--- writeXLSFile() ---
Sample.xls created sucessfully
--- readXLSFile() ---
Header 0Header 1Header 2Header 3Header 4
cell 0 1cell 1 1cell 2 1cell 3 1cell 4 1
cell 0 2cell 1 2cell 2 2cell 3 2cell 4 2
cell 0 3cell 1 3cell 2 3cell 3 3cell 4 3
cell 0 4cell 1 4cell 2 4cell 3 4cell 4 4


Sample.xls file


6 comments:

  1. You are reading. You are writing. You aint updating.

    ReplyDelete
  2. I downloaded the Jxelapi file and copied it to the jdk folder. I had set the class path to D:\jdk1.5\jexcelapi\jxl . But when I executed the file 100 errors are shown. all are
    SimpleJExcelExample.java:71: illegal character: \92
    \{\par What should I do to correct it
    ^

    ReplyDelete
    Replies
    1. Its looks like its a compilation error. Please check for any illegal character at the specified line.

      Delete
  3. So, ωhat on the ԁot іs aurawavе anуwayѕ, and so pay
    for the succeedeг thаt it's had since its liberation- as advantageously as muscleman rawness in other extremities.
    My website http://aurawavereview.com/

    ReplyDelete
  4. Third, distributors are so confident in its skills that they offer you money back guarantees for
    folks who try it out.

    my site; Flex belt reviews

    ReplyDelete
  5. Hey! Do you use Twitter? I'd like to follow you if that would be ok. I'm absolutely enjoying your blog and
    look forward to new posts.

    Feel free to surf to my web site ... what is garcinia cambogia

    ReplyDelete