Blog Archive

Java EXCEL file upload and parsing example

This is a very common requirement by businesses that their customers or salesrep have Orders filled out in Excel format and they would like to upload them thru their website and create the orders automatically instead of emailing the Excel file and someone manually entering them into the system. In this tutorial we have sample order file in Excel format that we will upload into the server using Java Servlet and then read the Excel file using Apache POI. ExtJs JavaScript framework is used for the front end form design and Ajax file upload request. You can use the same program with some extra programming to parse files such as CSV, etc.


Upload an Excel file in Java and then read the contents
Upload an Excel file in Java and then read the contents
Upload an Excel file in Java and then read the contents

The applications depends on a bunch of Jar file such as
  • Apache commons fileupload
  • Apache commons IO
  • Apache POI
  • Apache XML Beans
  • Google JSON library Gson
  • dom4j


Upload an Excel file in Java and then read the contents

Application Starting point - index.html

<html>
<head>
    <title>Java Excel File Upload and Parsing</title>

    <link rel="stylesheet" type="text/css" href="extjs/resources/css/ext-all.css">
    <style type="text/css">
        .upload-icon {
            background: url('extjs/icons/fam/image_add.png') no-repeat 0 0 !important;
        }
    </style>
   
    <script type="text/javascript" src="extjs/ext-all-debug.js"></script>
    <script type="text/javascript" src="app.js"></script>

</head>
<body>
<div id="fileUpload">
</div>
</body>
</html>

Application JavaScript file - apps.js

Ext.Loader.setConfig({ 
    enabled: true 
    });

Ext.application({
    name: 'ExcelApp',

    appFolder: 'app',
   
    controllers: [
                  'FileUpload'
              ],
   
    launch: function() {
        Ext.widget('fileuploadform');
    }
});

Application Excel file Upload Form View - MyForm.js

Ext.define('ExcelApp.view.MyForm', {
    extend: 'Ext.form.Panel',
    alias : 'widget.fileuploadform',
    width: 500,
    frame: true,
    title: 'Order File Upload Form',
    bodyPadding: '10 10 0',
    renderTo: 'fileUpload',
   
    defaults: {
        anchor: '100%',
        allowBlank: false,
        msgTarget: 'side',
        labelWidth: 75
    },

    items: [{
        xtype: 'textfield',
        name: 'filename',
        fieldLabel: 'File Name'
    },{
        xtype: 'filefield',
        id: 'myFile',
        emptyText: 'Select a File to Upload',
        fieldLabel: 'Select a File',
        name: 'fileSelected',
        buttonText: '',
        buttonConfig: {
            iconCls: 'upload-icon'
        }
    }],

    buttons: [{
        text: 'Upload',
        action: 'uploadFile'
    },
    {
        text: 'Reset Form',
        handler: function() {
            this.up('form').getForm().reset();
        }
    }]
});

ExtJs application controller - FileUpload.js

Ext.define('ExcelApp.controller.FileUpload', {
            extend : 'Ext.app.Controller',

            //define the views
            views : ['MyForm'],

            refs : [{
                ref : 'myForm',
                selector : 'fileuploadform'
            }],
           
            //special method that is called when your application boots
            init : function() {
               
                //control function makes it easy to listen to events on 
                //your view classes and take some action with a handler function
                this.control({
                   
                            //when the viewport is rendered
                            'viewport > panel' : {
                                render : this.onPanelRendered
                            },
                            //when you click Upload file button
                            'fileuploadform button[action=uploadFile]' : {
                                click : this.uploadFile   
                            }
                    });
            },

            onPanelRendered : function() {
                //just a console log to show when the panel is rendered
                console.log('The panel was rendered');
            },
           
            uploadFile : function(button) {
                //just a console log to show when the file Upload starts
                console.log('File Upload in progress');
                var form = button.up('form').getForm();
               
                if(form.isValid()){
                    form.submit({
                        url: 'ExcelOrderFileUpload',
                        waitMsg: 'Uploading your file...',
                        scope: this,
                        success: function(form, action){
                            // server responded with success = true
                            response = Ext.decode(action.response.responseText);
                            if(response.success){
                                this.processResponse(response);
                                form.reset();
                            }
                        },
                        failure: function(form, action){
                            if (action.failureType === CONNECT_FAILURE) {
                                   Ext.Msg.alert('Error', 'Status:'+action.response.status+': '+
                                action.response.statusText);
                            }
                            if (action.failureType === SERVER_INVALID){
                                // server responded with success = false
                                Ext.Msg.alert('Invalid', action.result.errormsg);
                            }
                        }
                    });
                }
            },   
           
            processResponse : function(response){
               
                responseMessage = response.message;
                Ext.MessageBox.show({
                    closable: false,
                    width:450,
                    title:'<b>Excel Order File Upload Successful!</b>',
                    msg: responseMessage,
                    buttons: Ext.MessageBox.OK,
                    fn: this.showResult,
                    icon: Ext.MessageBox.INFO
                });
            },
           
            showResult : function(btn){
                console.log('You clicked the ' + btn + ' button');
            }
       
    });


Upload an Excel file in Java and then read the contents

Java Servlet for File Upload and Excel file parsing - ExcelOrderFileUpload

package com.as400samplecode;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.gson.JsonObject;

public class ExcelOrderFileUpload extends HttpServlet {

    private static final long serialVersionUID = 1L;
    private static final String TMP_DIR_PATH = "/MyTempFiles";
    private File tmpDir;
    private static final String DESTINATION_DIR_PATH ="/MySavedFiles";
    private File destinationDir;

    public ExcelOrderFileUpload() {
        super();
    }

    public void init(ServletConfig config) throws ServletException {

        super.init(config);
        String realPath = getServletContext().getRealPath(TMP_DIR_PATH);
        tmpDir = new File(realPath);
        if(!tmpDir.isDirectory()) {
            throw new ServletException(TMP_DIR_PATH + " is not a directory");
        }

        realPath = getServletContext().getRealPath(DESTINATION_DIR_PATH);
        destinationDir = new File(realPath);
        if(!destinationDir.isDirectory()) {
            throw new ServletException(DESTINATION_DIR_PATH+" is not a directory");
        }

    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        //PrintWriter to send the JSON response back
        PrintWriter out = response.getWriter();

        //set content type and header attributes
        response.setContentType("text/html");
        response.setHeader("Cache-control", "no-cache, no-store");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Expires", "-1");

        DiskFileItemFactory  fileItemFactory = new DiskFileItemFactory ();

        //Set the size threshold, above which content will be stored on disk.
        fileItemFactory.setSizeThreshold(1*1024*1024); //1 MB

        //Set the temporary directory to store the uploaded files of size above threshold.
        fileItemFactory.setRepository(tmpDir);

        ServletFileUpload uploadHandler = new ServletFileUpload(fileItemFactory);
        JsonObject myObj = new JsonObject();

        String fileName = null;
        String fullName = null;
        File file = null;

        try {

            //Parse the request
            List items = uploadHandler.parseRequest(request);
            Iterator iterator = items.iterator();
            while(iterator.hasNext()) {
                FileItem item = (FileItem) iterator.next();

                //Handle Form Fields
                if(item.isFormField()) {
                    System.out.println("Field Name = " + item.getFieldName() + ", Value = " + item.getString());
                    if(item.getFieldName().trim().equalsIgnoreCase("filename")){
                        fileName = item.getString().trim();
                    }
                } 

                //Handle Uploaded files.
                else {
                    System.out.println("Field Name = " + item.getFieldName()+
                            ", File Name = "+ item.getName()+
                            ", Content type = "+item.getContentType()+
                            ", File Size = "+item.getSize());
                    fullName = item.getName().trim();

                    //Write file to the ultimate location.
                    file = new File(destinationDir,item.getName());
                    item.write(file);
                }



            }

            int count = 0;
            String extension = FilenameUtils.getExtension(fullName);
            if(extension.trim().equalsIgnoreCase("xlsx")){
                count = processExcelFile(file);
            }
            else if(extension.trim().equalsIgnoreCase("xls")){
                //process your binary excel file
            }
            if(extension.trim().equalsIgnoreCase("csv")){
                //process your CSV file
            }

            myObj.addProperty("success", true);
            myObj.addProperty("message", count + " item(s) were processed for file " + fileName);
            out.println(myObj.toString());

        }
        catch(FileUploadException ex) {
            log("Error encountered while parsing the request",ex);
            myObj.addProperty("success", false);
            out.println(myObj.toString());
        } catch(Exception ex) {
            log("Error encountered while uploading file",ex);
            myObj.addProperty("success", false);
            out.println(myObj.toString());
        }

        out.close();

    }

    private int processExcelFile(File file){


        int count = 0;

        try{
            // Creating Input Stream 
            FileInputStream myInput = new FileInputStream(file);

            // Create a workbook using the File System 
            XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);

            // Get the first sheet from workbook 
            XSSFSheet mySheet = myWorkBook.getSheetAt(0);

            /** We now need something to iterate through the cells.**/
            Iterator<Row> rowIter = mySheet.rowIterator();
            while(rowIter.hasNext()){

                XSSFRow myRow = (XSSFRow) rowIter.next();
                Iterator<Cell> cellIter = myRow.cellIterator();
                while(cellIter.hasNext()){

                    XSSFCell myCell = (XSSFCell) cellIter.next();
                    //get cell index
                    System.out.println("Cell column index: " + myCell.getColumnIndex());
                    //get cell type
                    System.out.println("Cell Type: " + myCell.getCellType());

                    //get cell value
                    switch (myCell.getCellType()) {
                    case XSSFCell.CELL_TYPE_NUMERIC :
                        System.out.println("Cell Value: " + myCell.getNumericCellValue());
                        break;
                    case XSSFCell.CELL_TYPE_STRING:   
                        System.out.println("Cell Value: " + myCell.getStringCellValue());
                        break;
                    default:   
                        System.out.println("Cell Value: " + myCell.getRawValue());
                        break;   
                    }
                    System.out.println("---");

                    if(myCell.getColumnIndex() == 0 && 
                            !myCell.getStringCellValue().trim().equals("") &&
                            !myCell.getStringCellValue().trim().equals("Item Number")){
                        count++;
                    }

                }

            }
        }
        catch (Exception e){
            e.printStackTrace(); 
        }

        return count;

    }
   
}