How To Read Excel Files in Java

In this post we will present a working solution, that explains how to read and modify a Microsoft Excel Document in Java. The objective for us would be to read an excel file, write some data on a different column based on the input we read and modify the excel document.

As an example, let us assume that we have an Excel document (ReadExcelUsingJava.xls) with the following content in column 'A';

1

2

3

4

5

The output that I'm after should be written to a new Excel document, ModifyExcelUsingJava.xls, with the following content in both column 'A' and 'B'.

1	1MOD
2	2MOD
3	3MOD
4	4MOD
5	5MOD

As I said earlier, we will attempt to present a working example in Java to modify the Excel document. We will be using the JExcel API for this example, as I felt it is very easy to use for a beginner in Java. So, download JExcel and create a folder 'JExcel' and make sure that you have the 'jxl.jar' file available in your CLASSPATH. This is required and must, otherwise, you will not be able to run the code example we are going to show below.We will create a simple CLASS file, ExcelJavaReader.java.  We will show the code that needs to be placed in the JAVA file,and then run through it for you;

import java.io.*;

import java.util.Date;

import jxl.*; /* Import declarations to the JExcel JAR file */

import jxl.write.*; /* Import declarations to the JExcel JAR file */

public class ExcelJavaReader /* The name of our class file */

{

    public static void main(String args[])

    {

        try

        {

            Workbook ReadExcel = Workbook.getWorkbook(new File("ReadExcelUsingJava.xls")); /* Open the excel document in read only mode */

        WritableWorkbook ModifyExcel = Workbook.createWorkbook(new File("ModifyExcelUsingJava.xls"), ReadExcel);

           /*Create a writable version of the Excel Document*/

            Sheet sheet = ReadExcel.getSheet(0); /* Read the first worksheet of the Excel Workbook */

           WritableSheet sheet2 = ModifyExcel.getSheet(0); /* get the first worksheet for writing */

            for (int i=0;i<5;i++) /* Run a for loop to iterate over the CELLS in Excel */

            {

                Cell a1 = sheet.getCell(0,i); /* Get the first cell of Column A , 0 maps to A */

                WritableCell cell = sheet2.getWritableCell(1, i); /* Get column B first cell for writing output */

                String ReadExcelCellData = a1.getContents(); /* Read contents of CELL in Column A to a string variable */

                Label label = new Label(1, i, ReadExcelCellData +"MOD"); /* Append MOD to string and prepare data for stamping in Column B */

                sheet2.addCell(label); /* Write data to Excel worksheet CELL */

            }

            ModifyExcel.write();

            ModifyExcel.close();

            ReadExcel.close();

        }

        catch (Exception i)

        {

            System.out.println(i);

        }

    }

}

If you prepare an Excel document with the input we have given earlier and name it "ReadExcelUsingJava.xls" it will be used by the code to read the data from Excel and create the output Excel document. To compile the code you can use something as shown below

javac -classpath . ExcelJavaReader.java

To run the example, execute the code with the following statement

java -classpath . ExcelJavaReader

If you have done everything correctly, you will find an output file with the name ModifyExcelUsingJava.xls sitting inside the same directory. Give it a try and let us know if you run across any issues.