poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Leonardo Holanda" <leo_hola...@yahoo.com>
Subject HSSF do open using OpenOffice but do not using Excel
Date Mon, 04 Aug 2003 19:51:12 GMT
I have created an excel file from scratch using HSSF. I can sucessfully open
the spreadsheet file in OpenOffice, but if I try to open using MSExcel it
does not open and shows an error message with something like "There are too
much different cell formats".
After some tests I notice that I did a bad thing in my code (bad
programming). So it would be nice to have it on a troubleshooting page.
Since cells at the same column would have that same HSSFDataFormat, it's not
good to create a new HSSFCellStyle object for each cell. This is the wrong
way:

        for (int i = 0; i < columnNumber; i++) {
            headerStrings[i]     = metaData.getColumnLabel(i + 1);
            columnSizes[i]       = metaData.getColumnDisplaySize(i + 1);
            typeName             = metaData.getColumnTypeName(i + 1);

            if (typeName.equalsIgnoreCase("datetime"))
                columnFormat[i] = "m/d/yy h:mm";
            else if (typeName.equalsIgnoreCase("tiny"))
                columnFormat[i] = "0";
            else if (typeName.equalsIgnoreCase("long"))
                columnFormat[i] = "0";
            else
                columnFormat[i] = "General";
        }

        HSSFSheet newSheet = this.createSheetWithHeader(sheetName,
                headerStrings, columnSizes);
        for (short rowIndex = 1; rs.next(); rowIndex++) {
            HSSFRow row = newSheet.createRow(rowIndex);

            for (short i = 0; i < columnNumber; i++) {
                HSSFCellStyle cellStyle = wb.createCellStyle();
                cellStyle.setDataFormat(HSSFDataFormat.getFormat(
                        columnFormat[i]));

                HSSFCell newCell = row.createCell(i);
                newCell.setCellStyle(cellStyle);
                newCell.setCellValue(rs.getString(i + 1));
            }
        }


It's better to store each HSSFDataFormat inside an array and then sweep the
resultSet (rs) creating new cells and using the HSSFCellStyle that have
aready been set up. So a better code should look like this:

        for (int i = 0; i < columnNumber; i++) {
            columnStyle[i] = wb.createCellStyle();
            headerStrings[i]     = metaData.getColumnLabel(i + 1);
            columnSizes[i]       = metaData.getColumnDisplaySize(i + 1);
            typeName             = metaData.getColumnTypeName(i + 1);

            if (typeName.equalsIgnoreCase("datetime"))
            	columnStyle[i].setDataFormat(HSSFDataFormat.getFormat("m/d/yy
h:mm"));
            else if (typeName.equalsIgnoreCase("tiny"))
            	columnStyle[i].setDataFormat(HSSFDataFormat.getFormat("0"));
            else if (typeName.equalsIgnoreCase("long"))
            	columnStyle[i].setDataFormat(HSSFDataFormat.getFormat("0"));
            else

columnStyle[i].setDataFormat(HSSFDataFormat.getFormat("General"));
        }

        HSSFSheet newSheet = this.createSheetWithHeader(sheetName,
                headerStrings, columnSizes);

        for (short rowIndex = 1; rs.next(); rowIndex++) {
            HSSFRow row = newSheet.createRow(rowIndex);

            for (short i = 0; i < columnNumber; i++) {
                HSSFCell newCell = row.createCell(i);
                newCell.setCellStyle(columnStyle[i]);
                newCell.setCellValue(rs.getString(i + 1));

            }
        }

I know that there are still some improvements that can be done to this code,
but improvement is very valuable if you're using Excel2000. So, what I've
learned: "Do not use the createCellStyle() method inside a cell loop, you
better do it before sweeping the resultSet.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 10/6/2003


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Mime
View raw message