Write different data types to excel using POI

Question

I am trying to export data from a database to Excel. I have the data exported and currently being stored in an ArrayList (this can be changed). I have been able to export the data to excel but all of the values are being exported as Strings, I need them to keep their data type i.e currency/numeric.

I am using Apache POI and am having difficult with setting the data type of the fields to anything other than String. Am I missing something? Can someone please advise me on a better way of doing this? Any assistance on this would be greatly appreciated.

public static void importDataToExcel(String sheetName, ArrayList header, ArrayList data, File xlsFilename, int sheetNumber)
        throws HPSFException, FileNotFoundException, IOException {

    POIFSFileSystem fs = new POIFSFileSystem();
    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(xlsFilename));
    HSSFSheet sheet = wb.createSheet(sheetName);

    int rowIdx = 0;
    short cellIdx = 0;

    // Header
    HSSFRow hssfHeader = sheet.createRow(rowIdx);
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    for (Iterator cells = header.iterator(); cells.hasNext();) {
        HSSFCell hssfCell = hssfHeader.createCell(cellIdx++);
        hssfCell.setCellStyle(cellStyle);
        hssfCell.setCellValue((String) cells.next());
    }
    // Data

    rowIdx = 1;
    for (Iterator rows = data.iterator(); rows.hasNext();) {
        ArrayList row = (ArrayList) rows.next();
        HSSFRow hssfRow = (HSSFRow) sheet.createRow(rowIdx++);
        cellIdx = 0;
        for (Iterator cells = row.iterator(); cells.hasNext();) {
            HSSFCell hssfCell = hssfRow.createCell(cellIdx++);
            hssfCell.setCellValue((String) cells.next());
        }
    }
    Logfile.log("sheetNumber = " + sheetNumber);
    wb.setSheetName(sheetNumber, sheetName);
    try {
        FileOutputStream out = new FileOutputStream(xlsFilename);
        wb.write(out);
        out.close();
    } catch (IOException e) {
        throw new HPSFException(e.getMessage());
    }

}

What do you put into your Lists they're all raw - how do you fill them? — Boris the Spider
– Boris the Spider, Commented Mar 8, 2013 at 15:58
Why not parse the string into a number, and call setCellValue with a double? — Gagravarr
– Gagravarr, Commented Mar 8, 2013 at 16:00
The list is the results which are returned from a database query. header list is just String and the data list is a combination of doubles and Strings. — user1274028
– user1274028, Commented Mar 8, 2013 at 16:01
Doesn't look like it to be as your cast to String here hssfCell.setCellValue((String) cells.next()); - this implies it's all Strings. Otherwise you'd be seeing ClassCastExceptions. — Boris the Spider
– Boris the Spider, Commented Mar 8, 2013 at 16:03
Sorry you are correct I had set this up for String only originally but I am now trying to get it to write out the doubles. I am not sure how to be able to get the data type of the value to be able to parse the different results. Sorry I have been looking at this for a few days am have tried a number of things to correct it. — user1274028
– user1274028, Commented Mar 8, 2013 at 16:12

Boris the Spider · Accepted Answer · 2013-03-08 17:45:35Z

You need to check for the class of your cell value before you cast:

public static void importDataToExcel(String sheetName, List<String> headers, List<List<Object>> data, File xlsFilename, int sheetNumber)
        throws HPSFException, FileNotFoundException, IOException {

    POIFSFileSystem fs = new POIFSFileSystem();
    Workbook wb;
    try {
        wb = WorkbookFactory.create(new FileInputStream(xlsFilename));
    } catch (InvalidFormatException ex) {
        throw new IOException("Invalid workbook format");
    }
    Sheet sheet = wb.createSheet(sheetName);

    int rowIdx = 0;
    int cellIdx = 0;

    // Header
    Row hssfHeader = sheet.createRow(rowIdx);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    for (final String header : headers) {
        Cell hssfCell = hssfHeader.createCell(cellIdx++);
        hssfCell.setCellStyle(cellStyle);
        hssfCell.setCellValue(header);
    }
    // Data

    rowIdx = 1;
    for (final List<Object> row : data) {
        Row hssfRow = sheet.createRow(rowIdx++);
        cellIdx = 0;
        for (Object value : row) {
            Cell hssfCell = hssfRow.createCell(cellIdx++);
            if (value instanceof String) {
                hssfCell.setCellValue((String) value);
            } else if (value instanceof Number) {
                hssfCell.setCellValue(((Number) value).doubleValue());
            } else {
                throw new RuntimeException("Cell value of invalid type " + value);
            }
        }
    }
    wb.setSheetName(sheetNumber, sheetName);
    try {
        FileOutputStream out = new FileOutputStream(xlsFilename);
        wb.write(out);
        out.close();
    } catch (IOException e) {
        throw new HPSFException(e.getMessage());
    }
}

I have also added in generics - this makes the code a lot more readable. Also you need to avoid using the actual class where possible and use the interface, for example List not ArrayList and Row not HSSFRow.

Thank you so much for your help - this is exactly what I have been trying to do for a few days. Thank you again and have a great weekend.

Collectives™ on Stack Overflow

Write different data types to excel using POI

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related