Monday, April 30, 2012

Java : Read / Write excel (.xls) file

Lets see how we can create excel (.xls) file in Java. Below example show how to write a xls file.

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 Excel (.xls) file, Even it's cell can be formatted according to our 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

To run below example you will need to Download jxl.jar file, Click here to download.

Assuming you have set jxl.jar in classpath, Lets see a simple self explanatory example.


/* 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 String excelFileName = "C:/Sample.xls";
 
 public void writeXLSFile() {
  
  System.out.println("--- writing excel file ---");
  
  try {
   WritableWorkbook workbook = Workbook.createWorkbook(new File(excelFileName));
  
   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(excelFileName+" created sucessfully");
  }
  catch(Exception ee) {
   System.out.println("Exception :: "+ee);
  }
 }

 public void readXLSFile() {
  
 System.out.println("--- reading excel file ---");
 
  try {
   Workbook workbook = Workbook.getWorkbook(new File(excelFileName));
   
   // 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) {
  
  SimpleJExcelExample obj = new SimpleJExcelExample();

  obj.writeXLSFile();

  obj.readXLSFile();

 }

}


Output :