[Java] Importing / Exporting Data to Excel

In the engineering world, we often gather lots of data that we need to store and display in a spreadsheet format. Microsoft’s Excel is probably the most popular spreadsheet format there are out there. In order to read and write to an excel file from Java, we need the Apache POI library. The POI library actually handle all kinds of Microsoft document format, however, we will only deal with Excel files today.

Apache POI Library Homepage
http://poi.apache.org/

Apache POI’s Detailed Programming Guide
http://poi.apache.org/spreadsheet/quick-guide.html

So here’s a simple application that I wrote to demonstrate how to export data to an excel file and read back into the program from it.

Basic POI Library Workflow
Open New Workbook => Create New Sheets => Create New Rows => Create New Cells

*Make sure to add both the poi and the poi-ooxml jar file to the libraries folder in Netbeans. The Class WorkbookFactory used to import the data is located in poi-ooxml.


//Yu Hin Hau
//Importing and Exporting to Excel
//June 14, 2012

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Main {

    public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException
    {
        //Create 2D Array of Data
        double[][] value = new double[5][5];

        for(int i = 0; i < value.length; i++)
            for(int j = 0; j < value[i].length; j++)
                value[i][j] = i+j;

        //Export Data to Excel File
        exportData("data.xls","i+j",value);

        //Import Data from Excel File
        double[][] data = importData("data.xls", 0);

        //Display Data from File
        for(int i = 0; i < data.length; i++)
        {
            for(int j = 0; j < data[i].length; j++)
                System.out.print(data[i][j]+"\t");

            System.out.println();
        }

    }

    public static void exportData(String fileName, String tabName, double[][] data) throws FileNotFoundException, IOException
    {
        //Create new workbook and tab
        Workbook wb = new HSSFWorkbook();
        FileOutputStream fileOut = new FileOutputStream(fileName);
        Sheet sheet = wb.createSheet(tabName);

        //Create 2D Cell Array
        Row[] row = new Row[data.length];
        Cell[][] cell = new Cell[row.length][];

        //Define and Assign Cell Data from Given
        for(int i = 0; i < row.length; i ++)
        {
            row[i] = sheet.createRow(i);
            cell[i] = new Cell[data[i].length];

            for(int j = 0; j < cell[i].length; j ++)
            {
                cell[i][j] = row[i].createCell(j);
                cell[i][j].setCellValue(data[i][j]);
            }

        }

        //Export Data
        wb.write(fileOut);
        fileOut.close();

    }

    public static double[][] importData(String fileName, int tabNumber) throws FileNotFoundException, IOException, InvalidFormatException
    {

        double[][] data;

        //Create Workbook from Existing File
        InputStream fileIn = new FileInputStream(fileName);
        Workbook wb = WorkbookFactory.create(fileIn);
        Sheet sheet = wb.getSheetAt(tabNumber);

        //Define Data & Row Array and adjust from Zero Base Numer
        data = new double[sheet.getLastRowNum()+1][];
        Row[] row = new Row[sheet.getLastRowNum()+1];
        Cell[][] cell = new Cell[row.length][];

        //Transfer Cell Data to Local Variable
        for(int i = 0; i < row.length; i++)
        {
            row[i] = sheet.getRow(i);

            //Note that cell number is not Zero Based
            cell[i] = new Cell[row[i].getLastCellNum()];
            data[i] = new double[row[i].getLastCellNum()];

            for(int j = 0; j < cell[i].length; j++)
            {
                cell[i][j] = row[i].getCell(j);
                data[i][j] = cell[i][j].getNumericCellValue();
            }

        }

        fileIn.close();
        return data;
    }

}
Advertisements

9 thoughts on “[Java] Importing / Exporting Data to Excel

  1. You can also use Aspose.Cells for Java Library for importing/exporting data to/from excel file. You can find code sample on their documentation page:

    http://www.aspose.com/java/excel-component.aspx

    Below is the link to Export data to excel file:

    http://www.aspose.com/docs/display/cellsjava/Exporting%20Data%20from%20Worksheets

    Below is the link to Import data to excel file:

    http://www.aspose.com/docs/display/cellsjava/Importing%20Data%20to%20Worksheets

  2. I have done the program to wite into the excel file from java… i want that the name of the output file is the systems currentdate_time and make a new file everytime i run it.. plz tell

  3. Hi, I’m a java beginner in South Korea. I need to export numerical computation results to Excel. Your code looks very helpful to me. I’ve used your code without any modification and linked libraries you mentioned to the Netbeans and succeeded running them. But the problem is that the generated excel file is not opening at all. Judging that the generated file has a file size, the data (value of ‘i+j’ on your code) seems to be successfully exported to the file. I tried it on several computers in my college library. But the results were the same. While running the code, there hasn’t been error at all. Could you tell me how to solve this problem? Again, I haven’t made any modification to your code and libraries.

    • hi, I actually haven’t used that library in a long long time. So maybe the updated version was changed? it should work, at least at the time I wrote this article. btw, can you upload and post the excel file that’s generated?

  4. hi, i am currently learning java and i am working on netbeans 6.5. I have downloaded the packages you specified earlier. Please where am i suppose to copy them in the netbeans folder. I cant seem to locate the libraries folder in netbeans.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s