Today is Friday, 19th December 2014

Reading MS Excel files (.xslx) in Java

Last weekend, I was working on one of my personal projects. I needed to read a MS Excel spreadsheet. I know that the folks at Apache have a nice API for reading and writing MS Excels files, so I thought I’d download the latest version and give it a try.

The Apache project is named “Apache POI”. You can download it from http://poi.apache.org. Once downloaded, then extract the zip distribution and add the JAR files to your classpath.  You will also need the dependency JAR files. If you’re using Maven then this is automatic. You can also download the dependencies manually. See this page and at the bottom they provide a list of dependencies.

Here is a snippet of code to read a MS Excel 2010 file.

        import java.io.FileInputStream;
        import org.apache.poi.ss.usermodel.Cell;
        import org.apache.poi.ss.usermodel.Row;
        import org.apache.poi.xssf.usermodel.XSSFSheet;
        import org.apache.poi.xssf.usermodel.XSSFWorkbook;

        ...
        ...

        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("foo.xslx"));

        int sheetIndex = 0;
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);

        for (Row tempRow : sheet) {

            // print out the first three columns
            for (int column = 0; column < 3; column++) {
                Cell tempCell = tempRow.getCell(column);

                if (tempCell.getCellType() == Cell.CELL_TYPE_STRING) {
                    System.out.print(tempCell.getStringCellValue() + "  ");
                }
            }
            System.out.println();
        }

The API also provides support for writing to a MS Excel in the native Microsoft format. If you want to get really fancy then you can even create formula fields etc. You can really go town with this one. By using this API it is much more powerful than shipping around CSV files.

Anyways, I hope you find this useful during your coding adventures.

Share

7 Comments

  1. Comments  Kinjan   |  Sunday, 20 November 2011 at 12:36 am

    Hello,
    MY name is Kinjan. I am working on one of my project ..
    My requirement is some thing like that, i want to read excel cell values and update this values to database.
    When excel cell is updated my java spring program should automatically notified that cell is updated and further process will go on..
    If there is any solution to this then please let me know .

    Thanks and Regards
    Kinjan Ajudiya

  2. Comments  mrana   |  Friday, 10 February 2012 at 2:11 am

    Hi,

    I’m wondering if it is possible to create some graphical representation (pie chart) from the excel sheets by using java.
    All i need is program should intake the excel sheet and represent its graphical form.

  3. Comments  Chád Darby   |  Thursday, 15 March 2012 at 4:38 pm

    @mrana: Yes, you can create Charts but you’ll need to use an additional API. Here are the basic steps:

    1. Read Excel spreadsheet using Apache POI
    2. Use the data for generate a chart using JFreeChart: http://www.jfree.org/jfreechart/

    Hope this helps :-)

  4. Comments  Bharadwaj   |  Thursday, 26 April 2012 at 3:59 am

    Will this code work for MS Excel 2003 and 2007 as well?

  5. Comments  Chád Darby   |  Thursday, 26 April 2012 at 8:39 pm

    Hi,

    This code will read .xslx files. If you need to read older files such as .xls then you will need to use the HSSF classes from Apache POI. See this guide here for plenty of examples: http://poi.apache.org/spreadsheet/quick-guide.html

  6. Comments  Bilal Akbar   |  Sunday, 03 November 2013 at 6:25 am

    I am getting the error Multiple Markers on this line..
    on the following line of code.

    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(“foo.xslx”));

  7. Comments  Chad Darby   |  Sunday, 03 November 2013 at 10:58 am

    Hi Bilal,

    You will need to download the JAR files for the Apache POI project. See this link for downloads: http://poi.apache.org/download.html

Leave a Reply





Top