Monday, November 28, 2011

Create an Excel File using Java and POI

To create an Excel file in Java we can use Apache's POI library. The example file created by the following example code demostrates how to add a String, a Date, a Boolean, and a Number to specified cells in the spreadsheet. First a HSSFWorkbook object is created and a HSSFSheet object is added. A HSSFRow is added to the sheet, and a HSSFCell is added to the row. To add a String to the cell, a HSSFRichTextString is used. To add a Date to the cell, a HSSFCellStyle is used in conjunction with a HSSFDataFormat object appied to a Date object. A Boolean value and a Number value are added directly to the cell. The following sample code was successfully tested with POI versions 3.6 and 3.7.


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

// The following example code demonstrates how to create an Excel  
// file using the org.apache.poi library. In the sample file,
// a String, Date, Boolean, and Number are added to individual cells
// in the spreadsheet.

public class CreateExcelFile {

    public static void main(String[] args) {

        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFSheet firstSheet = workbook.createSheet("Sheet 1");

        // Write a String in Cell 1A
        HSSFRow row1 = firstSheet.createRow(0);
        HSSFCell cell1A = row1.createCell(0);
        cell1A.setCellValue(new HSSFRichTextString("Sample String"));

        // Write a Date in Cell 2B
        HSSFRow row2 = firstSheet.createRow(1);
        HSSFCell cell2B = row2.createCell(1);
        cell2B.setCellValue(new Date());
        // Format the Date so it looks like a date
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell2B.setCellStyle(cellStyle);

        // Write a Boolean in Cell 3C
        HSSFRow row3 = firstSheet.createRow(2);
        HSSFCell cell3C = row3.createCell(2);
        cell3C.setCellValue(true);

        // Write a Number in Cell 4D
        HSSFRow row4 = firstSheet.createRow(3);
        HSSFCell cell4D = row4.createCell(3);
        cell4D.setCellValue(3.14);

        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(new File("/Temp/Test3.xls"));
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.flush();
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}


Here is a screenshot of the generated Excel File:


Piece of cake!!!

Saturday, November 19, 2011

Get Sheet Names from an Excel File Using Java and POI

To get the sheet names from an Excel file in Java we can use Apache's POI library. First a file is imported from disk passing a FileInputStream object to a org.apache.poi.hssf.usermodel.HSSFWorkbook object. Looping over each sheet in the Excel spreadsheet, the sheet name is determined using the workbook.getSheetName() method. The following sample code was successfully tested with POI versions 3.6 and 3.7.

Here is a screenshot of the test file I used for this example:



import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

// The following example code demonstrates how to get the Sheet
// names in an Excel spreadsheet file

public class GetExcelSheetNames {

    public static void main(String[] args) {

        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream("/Temp/Test1.xls");

            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);

            // for each sheet in the workbook
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {

                System.out.println("Sheet name: " + workbook.getSheetName(i));
            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileInputStream != null) {
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}


Here is the output of the program:
Sheet name: Sheet1
Sheet name: Sheet2
Sheet name: Sheet3
Piece of cake!!!