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]

Handling file upload in java using servlet

In this post we will see how to handle file upload in java using servlet. File upload is one of the very extensively used functionality in any application. There are many ways how you can achieve this. However we will be focusing on how it can be achieved via java servlet.

So we will start by writing a Jsp page where user can browse any file from his machine and then click on Upload File button to  upload the file to the destination.

index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>File upload form</title>
</head>
<body>
<h3>File Upload:</h3>
Select a file to upload: <br />
<form action="UploadServlet" method="post"
                       enctype="multipart/form-data">
<input type="file" name="file" size="50" />
<br />
<input type="submit" value="Upload File" />
</form>
</body>

</html>

So in the index.jsp file we could see we are calling UploadServlet and the action is post on the form submission. So lets now write our servlet class which will handle the upload functionality. We will be uploading the file to a location which is defined in web.xml as a parameter.

UploadServlet.java


import java.io.*;
import java.util.Iterator;
import java.util.List;

import javax.servlet.*;
import javax.servlet.http.*;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

public class UploadServlet extends HttpServlet { 
    /**
* 
*/
private static final long serialVersionUID = 1L;

public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException,IOException {
System.out.println("Starting to Upload the file...");
        File file ;
        int maxFileSize = 5000 * 1024;
        int maxMemSize = 5000 * 1024;
        ServletContext context = getServletContext();
        //Getting the upload location from web.xml
        String filePath = context.getInitParameter("file-upload");

        // Verify the content type
        String contentType = req.getContentType();
        if ((contentType.indexOf("multipart/form-data") >= 0)) {
        DiskFileItemFactory factory = new DiskFileItemFactory();
        // maximum size that will be stored in memory
        factory.setSizeThreshold(maxMemSize);
        // Location to save data that is larger than maxMemSize.
        factory.setRepository(new File("c:\\temp"));

        // Create a new file upload handler
        ServletFileUpload upload = new ServletFileUpload(factory);
        // maximum file size to be uploaded.
        upload.setSizeMax( maxFileSize );
        try{ 
        // Parse the request to get file items.
        List fileItems = upload.parseRequest(req);

        // Process the uploaded file items
        Iterator i = fileItems.iterator();

        while ( i.hasNext () ) 
        {
        FileItem fi = (FileItem)i.next();
        if ( !fi.isFormField () )
        {
        // Get the uploaded file parameters
        String fieldName = fi.getFieldName();
        String fileName = fi.getName();
        boolean isInMemory = fi.isInMemory();
        long sizeInBytes = fi.getSize();
        // Write the file
        if( fileName.lastIndexOf("\\") >= 0 )
        {
        file = new File( filePath +"\\"+fileName.substring( fileName.lastIndexOf("\\"))) ;
        }else{
        file = new File( filePath +"\\"+ fileName.substring(fileName.lastIndexOf("\\")+1)) ;
        }
        fi.write( file ) ;
        System.out.println("Uploaded Filename: " + filePath +"\\"+fileName);
        }
        }
        }catch(Exception ex) {
        System.out.println(ex);
        }
        }
}
}


Add the UploadServlet mapping in web.xml and also the location where you want to upload the files to (Upload location). You can notice the file-upload parameter in the web.xml which corresponds to the upload location.

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>FileUpload</display-name>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  
  <context-param> 
    <description>Location to store uploaded file</description> 
    <param-name>file-upload</param-name> 
    <param-value>
         D:\apache-tomcat-6.0.35\webapps\data
     </param-value> 
 </context-param>

    <servlet>
<servlet-name>UploadServlet</servlet-name>
<servlet-class>UploadServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>UploadServlet</servlet-name>
<url-pattern>/UploadServlet</url-pattern>
</servlet-mapping>

</web-app>

We need to add two jars also to our project that are commons-fileupload-1.2.2.jar and commons-io-2.4.jar. Now you are ready with the file upload functionality and should be able to get it working fine. I hope this post would have helped you.