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