You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Matt Tucker <mt...@thoughtbot.com> on 2005/06/03 17:40:43 UTC
Reading XLS zip code +4 cells
Hello,
I'm reading data from an XLS sheet using POI. One of the columns
contains data in the Excel Zip Code + 4 format. However, the values that
are being returned are not in the correct format. For example, a zip
code value of 00009-4612 is being returned as 94612. After trying a
bunch of different things, here's a snippet of what I'm doing now:
HSSFCellStyle zipStyle = hssfworkbook.createCellStyle();
zipStyle.setDataFormat(format.getFormat("00000-0000"));
...
HSSFCell c = row.getCell(zipCol);
c.setCellStyle(zipStyle);
...
if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
{
Double D = new Double(c.getNumericCellValue());
Integer I = new Integer(D.intValue());
double d = D.doubleValue();
int i = I.intValue();
if(d > i)
{
returnString = D.toString();
}
returnString = I.toString();
}
else if (cellType == HSSFCell.CELL_TYPE_STRING) returnString =
c.getStringCellValue();
else if (cellType == HSSFCell.CELL_TYPE_FORMULA) returnString =
c.getCellFormula();
I know I'm probably doing something very, very wrong so if someone could
point me in the right direction, it would be much appreciated. Thanks.
--
Matt Tucker
thoughtbot
cell: 617 775 0742
office: 617 876 4780
www.thoughtbot.com
RE: AW: Reading XLS zip code +4 cells
Posted by Michael Neale <mi...@internode.on.net>.
Set the format to "Text" in excel. That ensures that values are always
interpreted as a string, regardless of what you type in.
I have a similar problem with date formats, with no easy solution as yet.
-----Original Message-----
From: Matt Tucker [mailto:mtucker@thoughtbot.com]
Sent: Saturday, 4 June 2005 2:04 AM
To: POI Users List
Subject: Re: AW: Reading XLS zip code +4 cells
Thanks for the response Karl-Heinz,
How do I read a numeric column as a String?
Matt Tucker
thoughtbot
cell: 617 775 0742
office: 617 876 4780
www.thoughtbot.com
KHZ (SAW) wrote:
>Hi Matt.
>
>Why don't you use strings?
>
>Your format obviously is interpreted as a numeric format. So leading
>zeros get lost. If you want to keep on with your solution you'll have
>to format the resulting number to a string corresponding to your format
>again.
>
>Regards, Karl-Heinz.
>
>
>-----Ursprüngliche Nachricht-----
>Von: Matt Tucker [mailto:mtucker@thoughtbot.com]
>Gesendet: Freitag, 3. Juni 2005 17:41
>An: poi-user@jakarta.apache.org
>Betreff: Reading XLS zip code +4 cells
>
>Hello,
>
>I'm reading data from an XLS sheet using POI. One of the columns
>contains data in the Excel Zip Code + 4 format. However, the values
>that
>
>are being returned are not in the correct format. For example, a zip
>code value of 00009-4612 is being returned as 94612. After trying a
>bunch of different things, here's a snippet of what I'm doing now:
>
>HSSFCellStyle zipStyle = hssfworkbook.createCellStyle();
>zipStyle.setDataFormat(format.getFormat("00000-0000"));
>...
>HSSFCell c = row.getCell(zipCol);
>c.setCellStyle(zipStyle);
>...
>if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
> Double D = new Double(c.getNumericCellValue());
> Integer I = new Integer(D.intValue());
>
> double d = D.doubleValue();
> int i = I.intValue();
>
> if(d > i)
> {
> returnString = D.toString();
> }
>
> returnString = I.toString();
>}
>else if (cellType == HSSFCell.CELL_TYPE_STRING) returnString =
>c.getStringCellValue(); else if (cellType ==
>HSSFCell.CELL_TYPE_FORMULA) returnString = c.getCellFormula();
>
>I know I'm probably doing something very, very wrong so if someone
>could
>
>point me in the right direction, it would be much appreciated. Thanks.
>
>
>
---------------------------------------------------------------------
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/
AW: AW: Reading XLS zip code +4 cells
Posted by "KHZ (SAW)" <ka...@sawag.com>.
Hi Matt.
Just reformat that column in Excel so that it's nonnumeric.
You also might run a VB macro adding the cell content to an empty string
possibly having to use CStr() and storing it to the cell again. I hope
this already is sufficient. Worst case you add it to a ' (the character
indicating text in Excel).
Regards, Karl-Heinz.
-----Ursprüngliche Nachricht-----
Von: Matt Tucker [mailto:mtucker@thoughtbot.com]
Gesendet: Freitag, 3. Juni 2005 18:04
An: POI Users List
Betreff: Re: AW: Reading XLS zip code +4 cells
Thanks for the response Karl-Heinz,
How do I read a numeric column as a String?
Matt Tucker
thoughtbot
cell: 617 775 0742
office: 617 876 4780
www.thoughtbot.com
KHZ (SAW) wrote:
>Hi Matt.
>
>Why don't you use strings?
>
>Your format obviously is interpreted as a numeric format. So leading
>zeros get lost. If you want to keep on with your solution you'll have
to
>format the resulting number to a string corresponding to your format
>again.
>
>Regards, Karl-Heinz.
>
>
>-----Ursprüngliche Nachricht-----
>Von: Matt Tucker [mailto:mtucker@thoughtbot.com]
>Gesendet: Freitag, 3. Juni 2005 17:41
>An: poi-user@jakarta.apache.org
>Betreff: Reading XLS zip code +4 cells
>
>Hello,
>
>I'm reading data from an XLS sheet using POI. One of the columns
>contains data in the Excel Zip Code + 4 format. However, the values
that
>
>are being returned are not in the correct format. For example, a zip
>code value of 00009-4612 is being returned as 94612. After trying a
>bunch of different things, here's a snippet of what I'm doing now:
>
>HSSFCellStyle zipStyle = hssfworkbook.createCellStyle();
>zipStyle.setDataFormat(format.getFormat("00000-0000"));
>...
>HSSFCell c = row.getCell(zipCol);
>c.setCellStyle(zipStyle);
>...
>if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
>{
> Double D = new Double(c.getNumericCellValue());
> Integer I = new Integer(D.intValue());
>
> double d = D.doubleValue();
> int i = I.intValue();
>
> if(d > i)
> {
> returnString = D.toString();
> }
>
> returnString = I.toString();
>}
>else if (cellType == HSSFCell.CELL_TYPE_STRING) returnString =
>c.getStringCellValue();
>else if (cellType == HSSFCell.CELL_TYPE_FORMULA) returnString =
>c.getCellFormula();
>
>I know I'm probably doing something very, very wrong so if someone
could
>
>point me in the right direction, it would be much appreciated. Thanks.
>
>
>
---------------------------------------------------------------------
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/
Re: AW: Reading XLS zip code +4 cells
Posted by Matt Tucker <mt...@thoughtbot.com>.
Thanks for the response Karl-Heinz,
How do I read a numeric column as a String?
Matt Tucker
thoughtbot
cell: 617 775 0742
office: 617 876 4780
www.thoughtbot.com
KHZ (SAW) wrote:
>Hi Matt.
>
>Why don't you use strings?
>
>Your format obviously is interpreted as a numeric format. So leading
>zeros get lost. If you want to keep on with your solution you'll have to
>format the resulting number to a string corresponding to your format
>again.
>
>Regards, Karl-Heinz.
>
>
>-----Ursprüngliche Nachricht-----
>Von: Matt Tucker [mailto:mtucker@thoughtbot.com]
>Gesendet: Freitag, 3. Juni 2005 17:41
>An: poi-user@jakarta.apache.org
>Betreff: Reading XLS zip code +4 cells
>
>Hello,
>
>I'm reading data from an XLS sheet using POI. One of the columns
>contains data in the Excel Zip Code + 4 format. However, the values that
>
>are being returned are not in the correct format. For example, a zip
>code value of 00009-4612 is being returned as 94612. After trying a
>bunch of different things, here's a snippet of what I'm doing now:
>
>HSSFCellStyle zipStyle = hssfworkbook.createCellStyle();
>zipStyle.setDataFormat(format.getFormat("00000-0000"));
>...
>HSSFCell c = row.getCell(zipCol);
>c.setCellStyle(zipStyle);
>...
>if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
>{
> Double D = new Double(c.getNumericCellValue());
> Integer I = new Integer(D.intValue());
>
> double d = D.doubleValue();
> int i = I.intValue();
>
> if(d > i)
> {
> returnString = D.toString();
> }
>
> returnString = I.toString();
>}
>else if (cellType == HSSFCell.CELL_TYPE_STRING) returnString =
>c.getStringCellValue();
>else if (cellType == HSSFCell.CELL_TYPE_FORMULA) returnString =
>c.getCellFormula();
>
>I know I'm probably doing something very, very wrong so if someone could
>
>point me in the right direction, it would be much appreciated. Thanks.
>
>
>
AW: Reading XLS zip code +4 cells
Posted by "KHZ (SAW)" <ka...@sawag.com>.
Hi Matt.
Why don't you use strings?
Your format obviously is interpreted as a numeric format. So leading
zeros get lost. If you want to keep on with your solution you'll have to
format the resulting number to a string corresponding to your format
again.
Regards, Karl-Heinz.
-----Ursprüngliche Nachricht-----
Von: Matt Tucker [mailto:mtucker@thoughtbot.com]
Gesendet: Freitag, 3. Juni 2005 17:41
An: poi-user@jakarta.apache.org
Betreff: Reading XLS zip code +4 cells
Hello,
I'm reading data from an XLS sheet using POI. One of the columns
contains data in the Excel Zip Code + 4 format. However, the values that
are being returned are not in the correct format. For example, a zip
code value of 00009-4612 is being returned as 94612. After trying a
bunch of different things, here's a snippet of what I'm doing now:
HSSFCellStyle zipStyle = hssfworkbook.createCellStyle();
zipStyle.setDataFormat(format.getFormat("00000-0000"));
...
HSSFCell c = row.getCell(zipCol);
c.setCellStyle(zipStyle);
...
if (cellType == HSSFCell.CELL_TYPE_NUMERIC)
{
Double D = new Double(c.getNumericCellValue());
Integer I = new Integer(D.intValue());
double d = D.doubleValue();
int i = I.intValue();
if(d > i)
{
returnString = D.toString();
}
returnString = I.toString();
}
else if (cellType == HSSFCell.CELL_TYPE_STRING) returnString =
c.getStringCellValue();
else if (cellType == HSSFCell.CELL_TYPE_FORMULA) returnString =
c.getCellFormula();
I know I'm probably doing something very, very wrong so if someone could
point me in the right direction, it would be much appreciated. Thanks.
--
Matt Tucker
thoughtbot
cell: 617 775 0742
office: 617 876 4780
www.thoughtbot.com
---------------------------------------------------------------------
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/