You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by "Andrew C. Oliver" <ac...@apache.org> on 2003/08/04 22:06:07 UTC
Re: HSSF do open using OpenOffice but do not using Excel
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" <le...@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/