Writing into Excel file using JExcel API

In this post we will learn how to write data into Excel sheet using JExcel API.

Below is Java code for writing the data into excel sheet:

WriteExcel.java
package com.technsolution;

import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;


public class WriteExcel {
  private WritableCellFormat timesBold;
  private WritableCellFormat times;
  private String outputFile;
  
  String [] name = {"Rahul","Kiran","Shweta","Raj","Sahil"};
  int [] marks = {87,98,89,87,90};
  
  public static void main(String[] args) throws IOException, WriteException {
 WriteExcel we = new WriteExcel();
 we.setOutputFile("D:/Output.xls");//Location of output file
 we.write();
  }
  
  public void setOutputFile(String outputFile) {
 this.outputFile = outputFile;
  }

  public void write() throws IOException, WriteException {
    
 File file = new File(outputFile);
 WorkbookSettings wbSettings = new WorkbookSettings();

 wbSettings.setLocale(new Locale("en", "EN"));

 WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);//creating workbook
 workbook.createSheet("Report", 0);//creating the first sheet of the workbook
 WritableSheet excelSheet = workbook.getSheet(0);
 createLabel(excelSheet);
 createContent(excelSheet,name,marks);

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

  private void createLabel(WritableSheet sheet) throws WriteException {
 // Lets create a times font
 WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
 // Define the cell format
 times = new WritableCellFormat(times10pt);
   
 // Create a bold font
 WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,
 UnderlineStyle.NO_UNDERLINE);
 timesBold = new WritableCellFormat(times10ptBoldUnderline);
 // Lets automatically wrap the cells
 timesBold.setWrap(true);

 CellView cv = new CellView();
 cv.setFormat(times);
 cv.setFormat(timesBold);

 // Write a few headers
 addCaption(sheet, 0, 0, "Names");
 addCaption(sheet, 1, 0, "Marks");
  }

  //Method for adding data to the columns
  private void createContent(WritableSheet sheet, String[] names, int[] marks) throws WriteException,
      RowsExceededException {
 
 for (int i = 1; i <= names.length; i++) {
 // First column
 addLabel(sheet, 0, i, names[i-1]);
 // Second column
 addNumber(sheet, 1, i, marks[i-1]);
 }
  }
  
  //Method for adding Headers of the columns
  private void addCaption(WritableSheet sheet, int column, int row, String s)
throws RowsExceededException, WriteException {
 Label label;
 label = new Label(column, row, s, timesBold);
 sheet.addCell(label);
  }  

  //Method for adding string values to excel sheet
  private void addLabel(WritableSheet sheet, int column, int row, String s)
throws WriteException, RowsExceededException {
 Label label;
 label = new Label(column, row, s, times);
 sheet.addCell(label);
  }

  //Method for adding numeric values to excel sheet
  private void addNumber(WritableSheet sheet, int column, int row, int num)
throws WriteException, RowsExceededException {
 Number number;
 number = new  Number(column, row, num, times);
 sheet.addCell(number);
  }
} 

Here is the output file generated by the above java file:

Output.xls

To use the JExcel API we need to add jxl-2.6.jar which contains all the classes required for reading and writing so don't forget to include it in the project build path.

Reading data from Excel file in Java using JExcel API

In this post we will learn how to read data from Excel file in Java.

Java provides an API for reading the data from excel sheet and it is called JExcel API. Java Excel API is a mature, open source java API enabling developers to read, write, and modify Excel spreadsheets dynamically. 

Here is the excel sheet data which we are going to use for reading:

Example.xls

Below is Java code for reading data, here we have tried to read 3 types of data from excel sheet i.e. String, Number and Date using LabelCell, NumberCell and DateCell:

ReadExcel.java

package com.technsolution;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

  private String inputFile;

  public void setInputFile(String inputFile) {
    this.inputFile = inputFile;
  }

  public void read() throws IOException  {
    File inputWorkbook = new File(inputFile);
    Workbook w = null;
    List<String> nameRowList = null;
    List<Double> marksRowList = null;
    List<Date> dateRowList = null;
    LabelCell lc;//for cell having String data
    NumberCell nc;//for cell having Numeric data
    DateCell dc;//for cell having date
    
    try {
      w = Workbook.getWorkbook(inputWorkbook);
      // Get the first sheet
      Sheet sheet = w.getSheet(0);

      // Loop over columns  
      for (int j = 0; j < sheet.getColumns(); j++) {
     Cell cell = sheet.getCell(j, 0);
     System.out.println("\n"+"Column Name => "+cell.getContents());//To print column values
     
     if(cell.getContents().equalsIgnoreCase("Name")){
     nameRowList = new ArrayList<String>();
     // Loop over rows
     for (int i = 0; i < sheet.getRows()-1; i++) {
     Cell styleColorVal = sheet.getCell(j, i+1);
     lc = (LabelCell)styleColorVal;
     nameRowList.add(i, lc.getString());
     System.out.println(cell.getContents()+(i+1)+" :: "+nameRowList.get(i).toString()); //To print the row values for Name column
     }      
     }
     
     if(cell.getContents().equalsIgnoreCase("Marks")){
     marksRowList = new ArrayList<Double>();
     // Loop over rows
     for (int i = 0; i < sheet.getRows()-1; i++) {
     Cell styleColorVal = sheet.getCell(j, i+1);
     nc = (NumberCell)styleColorVal;
     marksRowList.add(i, nc.getValue());
     System.out.println(cell.getContents()+(i+1)+" :: "+marksRowList.get(i).toString()); //To print the row values for Marks column
     }      
     }
     
     if(cell.getContents().equalsIgnoreCase("Date")){
     dateRowList = new ArrayList<Date>();
     // Loop over rows
     for (int i = 0; i < sheet.getRows()-1; i++) {
     Cell styleColorVal = sheet.getCell(j, i+1);
     dc = (DateCell)styleColorVal;
     dateRowList.add(i, dc.getDate());
     System.out.println(cell.getContents()+(i+1)+" :: "+dateRowList.get(i).toString()); //To print the row values for Date column
     }      
     }
         
     
      }
    } catch (BiffException e) {
    e.printStackTrace();
    } catch (Exception ex){
    ex.printStackTrace();
    }finally{
    w.close();
    }
  }

  public static void main(String[] args) throws IOException {
    ReadExcel rd = new ReadExcel();
    rd.setInputFile("D:/Example.xls");
    rd.read();
  } 
} 

OUTPUT

Column Name => Name
Name1 :: Rahul
Name2 :: Kiran
Name3 :: Shweta
Name4 :: Raj
Name5 :: Sahil

Column Name => Marks
Marks1 :: 87.0
Marks2 :: 98.0
Marks3 :: 89.0
Marks4 :: 87.0
Marks5 :: 90.0

Column Name => Date
Date1 :: Thu Dec 20 05:30:00 IST 2012
Date2 :: Tue Aug 20 05:30:00 IST 2013
Date3 :: Sat Mar 20 05:30:00 IST 2010
Date4 :: Wed Jun 20 05:30:00 IST 2012
Date5 :: Mon Feb 20 05:30:00 IST 2012

To use the JExcel API we need to add jxl-2.6.jar which contains all the classes required for reading and writing so don't forget to include it in the project build path.

[Note: JExcel API works with Excel file having extension .xls only and not with .xlsx]