poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andrew C. Oliver" <acoli...@apache.org>
Subject Re: HSSF do open using OpenOffice but do not using Excel
Date Mon, 04 Aug 2003 20:06:07 GMT
You are correct.  Its mentioned in the FAQ:
http://jakarta.apache.org/poi/faq.html#faq-N100C2

Maybe we should stick this syntax candy in and make it default while
preserving the existing way for those who need it.  (There are reasons to
create duplicate styles)

-andy


On 8/4/03 3:51 PM, "Leonardo Holanda" <leo_holanda@yahoo.com> wrote:

> 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/
> 

-- 
Andrew C. Oliver
http://www.superlinksoftware.com/poi.jsp
Custom enhancements and Commercial Implementation for Jakarta POI

http://jakarta.apache.org/poi
For Java and Excel, Got POI?


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-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