You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Daniel Noll <da...@nuix.com.au> on 2006/09/01 01:36:27 UTC
Re: AW: Reading numeric values as strings from a cell
Alex Mayorga Adame wrote:
> In short, there's a need to extract the contents of the cells as they show
> on the worksheet, extracted as a string no matter what the Cell number
> format is.
>
> Any help would be highly appreciated.
We do this the trivial way, i.e. String.valueOf(doubleValue);
Daniel
--
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://www.nuix.com.au/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
---------------------------------------------------------------------
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[2]: AW: Reading numeric values as strings from a cell
Posted by Yegor Kozlov <ye...@dinom.ru>.
Hi,
For numerical cells the code is like this:
HSSFWorkbook wb = ...;
HSSFDataFormat dataformat = wb.createDataFormat();
double value = cell.getNumericCellValue(); //get the cell value
HSSFCellStyle style = cell.getCellStyle();
short idx = style.getDataFormat();
//get format pattern for this cell
String format = dataformat.getFormat(idx);
//try to create java formatter. Warning: This MAY not work in all cases!
NumberFormat fmt = new DecimalFormat(format);
String fmtvalue = fmt.format(value); //compare it with what you have in Excel.
Regards, Yegor
---------------------------------------------------------------------
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 numeric values as strings from a cell
Posted by Anthony Andrews <py...@yahoo.com>.
Mary-Ann
You will have to forgive me if this does not provide you with any new ideas at all but here goes.
I mostly use JExcel - it had support for images before HSSF and my client required that so we ran with JExcel - and it allows you to get at the value stored in a cell a copule of different ways. Firstly, there is a utility method that simply returns the value stored in any type of cell as a String. Secondly, there are methods that are specific to the cell type - Number, Date, Label, etc - that you can call to recover the value of a cell as an instance of a specific class - Date for instance. Have you looked through HSSF's javadoc to see if it mimics this sort of capability?
"Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com> wrote:
Not sure. I posted an email about this, and got one response that
seemed to indicate that I'd need to write code to apply a format. I'll
see what I can find out about HSSFDataFormat.
Thanks! I'd love to be able to read the xls directly.
Maffy
-----Original Message-----
From: deshmol-lists@yahoo.com [mailto:deshmol-lists@yahoo.com]
Sent: Wednesday, September 06, 2006 2:54 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Is this something that cannot be handled using HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
>
> We're doing a medical application, and we need the numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT tests for
> verification and validation before we get to the next stage in the
> delivery of our products.
>
> Right now, I am just saving the xls as text, and parsing the text into
> XML. I was hoping to get rid of a step by using POI, but I only seem
> to be able to get the double value and not the value that was entered
> into the spreadsheet (which represents the output produced by the
> module being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:alex_mayorga@yahoo.com]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: poi-user@jakarta.apache.org
> Subject: Re: AW: Reading numeric values as strings from a cell
>
>
> Maybe I didn't stated my question correctly. But by
> doing that on a Cell
> formatted as Date you'd probably end up with
> something like: 38966 even
> if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen
> in Excel on a String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
---------------------------------
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2ยข/min or less.
Dealing with Error Type cells
Posted by Madhav Sadhu <Ma...@saama.com>.
Hello All,
I have a call whose cell type is error(came to know by exporting the xls into xml and checking that column type), how ever the value inside that cell is a string value. Since the type is set to error, POI is reading this cell as error type cell. Is there any way to read a value that is contained in error type cells ?
Or, is there any easy way to set the cell type to string from error ?
Thanks
Madhav
Re: AW: Reading numeric values as strings from a cell
Posted by Avik Sengupta <av...@itellix.com>.
The setCellType I think should be deprecated, and should not be used in newer
versions of poi (2.0 onwards). the setCellValue methods automatically set the
correct cell type. You cannot change the type of a cell without changing its
value. Anyone has seen any valid use case for the setCellType method?? I
think we should mark the method as deprecated.
In general, all this dicussion about formatted numbers is because excel stores
the numeric cell values as a IEEE floating point number. This is irrespective
of how the number is formatted on screen. POI tries to provide a close
mapping of the excel file format (and not excel the application), and
therefore returns the value as a double.
Excel stores the format of the cell separately, and the applicaton applies the
format to the number before displaying it on screen. That is exactly what you
need to do replicate yourself, as discussed in this thread and elsewhere...
That said, given that this is seems to be a common request, if someone can
provide an useful implementation, we can stick in into HSSFCellUtil
Regards
-
Avik
On Wednesday 13 September 2006 23:09, Finnerty, Mary Ann (GE Healthcare)
wrote:
> I was surprised about the NFE, too. We already know it's a number, but
> I tried it because I also knew that we declared that column in the xls
> file as text format, so I thought I could easily just set the cell type,
> but I guess excel is just being "helpful". :)
>
> I've been pulled off to work on something else, but will get back to
> this either tomorrow, or on my own this weekend. Thanks for helping so
> much!!
>
> Maffy
>
>
>
> -----Original Message-----
> From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
> Sent: Tuesday, September 12, 2006 11:43 PM
> To: POI Users List
> Subject: RE: AW: Reading numeric values as strings from a cell
>
> Not quite, with the hack, you should get exactly what was entered into
> the cell in the first instance. I may have missed something when reading
> the API documentation but I could not find anyway to get at the
> formatting that was applied to the cell. So, when the contents of the
> cell are read into a primitive double value Java makes certain
> assumptions - adding decimal points and trailing zeros etc. The
> DecimalFormat object goes some way to reverting the number back to the
> value the user entered into the sheets cells. I tested the code very
> quickly with values like '100', '0.987' and some ridiculous values just
> to see what happened and it did reflect back what I entered into the
> cells.
>
> Have you experiemented with the formatting String? The hashs and zeros
> have special meanings and you may be able to come up with something that
> will work for you.
>
> Just as an aside, I did try converting the numeric cells to String type
> celss using the setCellType() method. Interesting, I saw a
> NumberFormatException thrown when I tried to perform this operation! Not
> what I expected or believe should happen.
>
> "Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com> wrote:
> Looks like I would still have to know in advance what the format of the
> number in the cell is.
> I don't. Sometimes it's a multi-position decimal number; sometimes it's
> a whole number. I just want Whatever is there without having it
> adjusted in anyway.
>
> When I save the xls as a txt file, the "number" is just fine, which is
> why we've been handling it that way.
> The problem is that it's difficult to automate the series of tasks on an
> ever-widening number of modules when there's a manual step in there to
> save the xls as a txt file.
>
> I'll play around with it, though, and see if I can come up with a
> general purpose format.
>
> Thanks for trying!
> Maffy
>
> -----Original Message-----
> From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
> Sent: Tuesday, September 12, 2006 10:51 AM
> To: POI Users List
> Subject: RE: AW: Reading numeric values as strings from a cell
>
> Well, it's a bodge but it will work - and it may even do what you need;
>
> HSSFWorkbook wb = null;
> HSSFSheet sheet = null;
> HSSFRow row = null;
> HSSFCell cell = null;
> FileInputStream fis = null;
> double value = 0.0;
> java.text.DecimalFormat formatter = null; java.text.FieldPosition
> fPosition = null; String formattingString = null; String resultString =
> null; StringBuffer buffer = null;
>
>
> try {
> // This is the String that will perform the formatting of the
> result
> // It is best to look at the java.text.DecimalFormat class for an
> // explanation of how the String works. It is not necessary to
> // split this into two operations I do it here for clarity only.
> formattingString = "#,##0.#####";
> formatter = new java.text.DecimalFormat(formattingString);
> fPosition = new java.text.FieldPosition(0);
> buffer = new StringBuffer();
>
> // Open a workbook
> fis = new FileInputStream(new
> java.io.File("C:\\temp\\Book1.xls"));
> wb = new HSSFWorkbook(fis);
> // Get a sheet
> sheet = wb.getSheetAt(0);
> // Get a row
> row = sheet.getRow((short)0);
> // Get a cell
> cell = row.getCell((short)0);
>
> // Check the type of the cell and if numeric format for display
> if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
> // The StringBuffer is used to hold the result of formatting
> // the number into a String. A new instance will be required
> // each time.
> buffer = new StringBuffer();
>
> // Recover the numeric value from the cell
> value = cell.getNumericCellValue();
>
> // Format that number for display
> formatter.format(value, buffer, fPosition);
>
> // Not strictly necessary but I copy the result from the
> // StringBuffer into a String - leave this out for performance
> // reasons in production code
> resultString = buffer.toString();
>
> // Simply display the result to screen
> System.out.println(resultString);
> }
>
> }
> catch(Exception ex) {
> System.out.println("Caught: " + ex.getClass().getName());
> System.out.println("Message: " + ex.getMessage());
> System.out.println("Stack Trace Follows......");
> ex.printStackTrace(System.out);
> }
>
> Of course, you will need to modify the code - take out the bit that does
> the formatting and create a method that you can call each time you read
> a cell from the sheet, take out the formatting string and place it into
> a properties file so that you can play around with it and not need to
> re-compile the code, etc, etc.
>
> Hope this helps.
>
> Anthony Andrews
> wrote: Have just had a play
> around and answered my own question - that approach is not supported by
> HSSF.
>
> However, I think that the answer could be found in another 'pattern' I
> use in JEXcel. It is possible to get at the formatting object that is
> used to determine how a value appears in the cell. That formatter - that
> is an instance of the java.text.NumberFormat class - can be applied to
> values read from cells to determine how they appear. I reckon you will
> need to do something similar for your application; get at the format of
> the cell, create a NumberFormat or similar using the formatting String
> and then apply it to the value recovered from the cell. If I get the
> chance today - that is if I can take another 'quiet' coffee break - I
> will have a look at this.
>
> "Finnerty, Mary Ann (GE Healthcare)" wrote:
> Not sure. I posted an email about this, and got one response that
> seemed to indicate that I'd need to write code to apply a format. I'll
> see what I can find out about HSSFDataFormat.
>
> Thanks! I'd love to be able to read the xls directly.
>
> Maffy
>
> -----Original Message-----
> From: deshmol-lists@yahoo.com [mailto:deshmol-lists@yahoo.com]
> Sent: Wednesday, September 06, 2006 2:54 PM
> To: POI Users List
> Subject: RE: AW: Reading numeric values as strings from a cell
>
> Is this something that cannot be handled using HSSFDataFormat?
>
> ~ amol
>
>
> --- "Finnerty, Mary Ann (GE Healthcare)"
>
> wrote:
> > This is similar to what we're trying to do.
> >
> > When I read the spreadsheet, it has numbers like:
> > 100, 10000, 0.967,
> > etc.
> > When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
> >
> > We're doing a medical application, and we need the numbers eXACTLY as
> > entered into the spreadsheet to create our JUNIT tests for
> > verification and validation before we get to the next stage in the
> > delivery of our products.
> >
> > Right now, I am just saving the xls as text, and parsing the text into
> >
> > XML. I was hoping to get rid of a step by using POI, but I only seem
> > to be able to get the double value and not the value that was entered
> > into the spreadsheet (which represents the output produced by the
> > module being tested).
> >
> > Maffy
> >
> > -----Original Message-----
> > From: Alex Mayorga Adame
> > [mailto:alex_mayorga@yahoo.com]
> > Sent: Wednesday, September 06, 2006 1:21 PM
> > To: poi-user@jakarta.apache.org
> > Subject: Re: AW: Reading numeric values as strings from a cell
> >
> >
> > Maybe I didn't stated my question correctly. But by doing that on a
> > Cell formatted as Date you'd probably end up with something like:
> > 38966 even if in your Worksheet you see: 6-Sep-06
> >
> > I would like to store the latter, the data as seen in Excel on a
> > String.
> >
> > Hope this clears up the confusion.
> >
> > Thanks in advance,
> > Alex
> >
> > Daniel Noll wrote:
> > > Alex Mayorga Adame wrote:
> > >> In short, there's a need to extract the contents
> >
> > of the cells as they
> >
> > >> show on the worksheet, extracted as a string no
> >
> > matter what the Cell
> >
> > >> number format is.
> > >>
> > >> Any help would be highly appreciated.
> > >
> > > We do this the trivial way, i.e.
> >
> > String.valueOf(doubleValue);
> >
> > > Daniel
> > >
> > >
> > > --
> > > Daniel Noll
> > > ...
> >
> > --
> > View this message in context:
>
> http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
>
> > 26130.html#a6179056
> > Sent from the POI - User forum at Nabble.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/
>
> ---------------------------------------------------------------------
>
> > 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/
>
> ---------------------------------------------------------------------
> 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/
>
>
> ---------------------------------------------------------------------
> 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/
>
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Get on board. You're invited to try the new Yahoo! Mail.
>
>
> ---------------------------------
> Do you Yahoo!?
> Everyone is raving about the all-new Yahoo! Mail.
>
> ---------------------------------------------------------------------
> 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/
>
>
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Get on board. You're invited to try the new Yahoo! Mail.
>
> ---------------------------------------------------------------------
> 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/
---------------------------------------------------------------------
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 numeric values as strings from a cell
Posted by "Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com>.
I was surprised about the NFE, too. We already know it's a number, but
I tried it because I also knew that we declared that column in the xls
file as text format, so I thought I could easily just set the cell type,
but I guess excel is just being "helpful". :)
I've been pulled off to work on something else, but will get back to
this either tomorrow, or on my own this weekend. Thanks for helping so
much!!
Maffy
-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
Sent: Tuesday, September 12, 2006 11:43 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Not quite, with the hack, you should get exactly what was entered into
the cell in the first instance. I may have missed something when reading
the API documentation but I could not find anyway to get at the
formatting that was applied to the cell. So, when the contents of the
cell are read into a primitive double value Java makes certain
assumptions - adding decimal points and trailing zeros etc. The
DecimalFormat object goes some way to reverting the number back to the
value the user entered into the sheets cells. I tested the code very
quickly with values like '100', '0.987' and some ridiculous values just
to see what happened and it did reflect back what I entered into the
cells.
Have you experiemented with the formatting String? The hashs and zeros
have special meanings and you may be able to come up with something that
will work for you.
Just as an aside, I did try converting the numeric cells to String type
celss using the setCellType() method. Interesting, I saw a
NumberFormatException thrown when I tried to perform this operation! Not
what I expected or believe should happen.
"Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com> wrote:
Looks like I would still have to know in advance what the format of the
number in the cell is.
I don't. Sometimes it's a multi-position decimal number; sometimes it's
a whole number. I just want Whatever is there without having it
adjusted in anyway.
When I save the xls as a txt file, the "number" is just fine, which is
why we've been handling it that way.
The problem is that it's difficult to automate the series of tasks on an
ever-widening number of modules when there's a manual step in there to
save the xls as a txt file.
I'll play around with it, though, and see if I can come up with a
general purpose format.
Thanks for trying!
Maffy
-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
Sent: Tuesday, September 12, 2006 10:51 AM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Well, it's a bodge but it will work - and it may even do what you need;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
FileInputStream fis = null;
double value = 0.0;
java.text.DecimalFormat formatter = null; java.text.FieldPosition
fPosition = null; String formattingString = null; String resultString =
null; StringBuffer buffer = null;
try {
// This is the String that will perform the formatting of the
result
// It is best to look at the java.text.DecimalFormat class for an
// explanation of how the String works. It is not necessary to
// split this into two operations I do it here for clarity only.
formattingString = "#,##0.#####";
formatter = new java.text.DecimalFormat(formattingString);
fPosition = new java.text.FieldPosition(0);
buffer = new StringBuffer();
// Open a workbook
fis = new FileInputStream(new
java.io.File("C:\\temp\\Book1.xls"));
wb = new HSSFWorkbook(fis);
// Get a sheet
sheet = wb.getSheetAt(0);
// Get a row
row = sheet.getRow((short)0);
// Get a cell
cell = row.getCell((short)0);
// Check the type of the cell and if numeric format for display
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
// The StringBuffer is used to hold the result of formatting
// the number into a String. A new instance will be required
// each time.
buffer = new StringBuffer();
// Recover the numeric value from the cell
value = cell.getNumericCellValue();
// Format that number for display
formatter.format(value, buffer, fPosition);
// Not strictly necessary but I copy the result from the
// StringBuffer into a String - leave this out for performance
// reasons in production code
resultString = buffer.toString();
// Simply display the result to screen
System.out.println(resultString);
}
}
catch(Exception ex) {
System.out.println("Caught: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stack Trace Follows......");
ex.printStackTrace(System.out);
}
Of course, you will need to modify the code - take out the bit that does
the formatting and create a method that you can call each time you read
a cell from the sheet, take out the formatting string and place it into
a properties file so that you can play around with it and not need to
re-compile the code, etc, etc.
Hope this helps.
Anthony Andrews
wrote: Have just had a play
around and answered my own question - that approach is not supported by
HSSF.
However, I think that the answer could be found in another 'pattern' I
use in JEXcel. It is possible to get at the formatting object that is
used to determine how a value appears in the cell. That formatter - that
is an instance of the java.text.NumberFormat class - can be applied to
values read from cells to determine how they appear. I reckon you will
need to do something similar for your application; get at the format of
the cell, create a NumberFormat or similar using the formatting String
and then apply it to the value recovered from the cell. If I get the
chance today - that is if I can take another 'quiet' coffee break - I
will have a look at this.
"Finnerty, Mary Ann (GE Healthcare)" wrote:
Not sure. I posted an email about this, and got one response that
seemed to indicate that I'd need to write code to apply a format. I'll
see what I can find out about HSSFDataFormat.
Thanks! I'd love to be able to read the xls directly.
Maffy
-----Original Message-----
From: deshmol-lists@yahoo.com [mailto:deshmol-lists@yahoo.com]
Sent: Wednesday, September 06, 2006 2:54 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Is this something that cannot be handled using HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
>
> We're doing a medical application, and we need the numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT tests for
> verification and validation before we get to the next stage in the
> delivery of our products.
>
> Right now, I am just saving the xls as text, and parsing the text into
> XML. I was hoping to get rid of a step by using POI, but I only seem
> to be able to get the double value and not the value that was entered
> into the spreadsheet (which represents the output produced by the
> module being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:alex_mayorga@yahoo.com]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: poi-user@jakarta.apache.org
> Subject: Re: AW: Reading numeric values as strings from a cell
>
>
> Maybe I didn't stated my question correctly. But by doing that on a
> Cell formatted as Date you'd probably end up with something like:
> 38966 even if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen in Excel on a
> String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
---------------------------------
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
---------------------------------------------------------------------
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/
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
---------------------------------------------------------------------
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 numeric values as strings from a cell
Posted by Anthony Andrews <py...@yahoo.com>.
Not quite, with the hack, you should get exactly what was entered into the cell in the first instance. I may have missed something when reading the API documentation but I could not find anyway to get at the formatting that was applied to the cell. So, when the contents of the cell are read into a primitive double value Java makes certain assumptions - adding decimal points and trailing zeros etc. The DecimalFormat object goes some way to reverting the number back to the value the user entered into the sheets cells. I tested the code very quickly with values like '100', '0.987' and some ridiculous values just to see what happened and it did reflect back what I entered into the cells.
Have you experiemented with the formatting String? The hashs and zeros have special meanings and you may be able to come up with something that will work for you.
Just as an aside, I did try converting the numeric cells to String type celss using the setCellType() method. Interesting, I saw a NumberFormatException thrown when I tried to perform this operation! Not what I expected or believe should happen.
"Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com> wrote: Looks like I would still have to know in advance what the format of the
number in the cell is.
I don't. Sometimes it's a multi-position decimal number; sometimes it's
a whole number. I just want
Whatever is there without having it adjusted in anyway.
When I save the xls as a txt file, the "number" is just fine, which is
why we've been handling it that way.
The problem is that it's difficult to automate the series of tasks on an
ever-widening number of modules when there's a manual step in there to
save the xls as a txt file.
I'll play around with it, though, and see if I can come up with a
general purpose format.
Thanks for trying!
Maffy
-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
Sent: Tuesday, September 12, 2006 10:51 AM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Well, it's a bodge but it will work - and it may even do what you need;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
FileInputStream fis = null;
double value = 0.0;
java.text.DecimalFormat formatter = null; java.text.FieldPosition
fPosition = null; String formattingString = null; String resultString =
null; StringBuffer buffer = null;
try {
// This is the String that will perform the formatting of the
result
// It is best to look at the java.text.DecimalFormat class for an
// explanation of how the String works. It is not necessary to
// split this into two operations I do it here for clarity only.
formattingString = "#,##0.#####";
formatter = new java.text.DecimalFormat(formattingString);
fPosition = new java.text.FieldPosition(0);
buffer = new StringBuffer();
// Open a workbook
fis = new FileInputStream(new
java.io.File("C:\\temp\\Book1.xls"));
wb = new HSSFWorkbook(fis);
// Get a sheet
sheet = wb.getSheetAt(0);
// Get a row
row = sheet.getRow((short)0);
// Get a cell
cell = row.getCell((short)0);
// Check the type of the cell and if numeric format for display
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
// The StringBuffer is used to hold the result of formatting
// the number into a String. A new instance will be required
// each time.
buffer = new StringBuffer();
// Recover the numeric value from the cell
value = cell.getNumericCellValue();
// Format that number for display
formatter.format(value, buffer, fPosition);
// Not strictly necessary but I copy the result from the
// StringBuffer into a String - leave this out for performance
// reasons in production code
resultString = buffer.toString();
// Simply display the result to screen
System.out.println(resultString);
}
}
catch(Exception ex) {
System.out.println("Caught: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stack Trace Follows......");
ex.printStackTrace(System.out);
}
Of course, you will need to modify the code - take out the bit that does
the formatting and create a method that you can call each time you read
a cell from the sheet, take out the formatting string and place it into
a properties file so that you can play around with it and not need to
re-compile the code, etc, etc.
Hope this helps.
Anthony Andrews
wrote: Have just had a play
around and answered my own question - that approach is not supported by
HSSF.
However, I think that the answer could be found in another 'pattern' I
use in JEXcel. It is possible to get at the formatting object that is
used to determine how a value appears in the cell. That formatter - that
is an instance of the java.text.NumberFormat class - can be applied to
values read from cells to determine how they appear. I reckon you will
need to do something similar for your application; get at the format of
the cell, create a NumberFormat or similar using the formatting String
and then apply it to the value recovered from the cell. If I get the
chance today - that is if I can take another 'quiet' coffee break - I
will have a look at this.
"Finnerty, Mary Ann (GE Healthcare)" wrote:
Not sure. I posted an email about this, and got one response that
seemed to indicate that I'd need to write code to apply a format. I'll
see what I can find out about HSSFDataFormat.
Thanks! I'd love to be able to read the xls directly.
Maffy
-----Original Message-----
From: deshmol-lists@yahoo.com [mailto:deshmol-lists@yahoo.com]
Sent: Wednesday, September 06, 2006 2:54 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Is this something that cannot be handled using HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
>
> We're doing a medical application, and we need the numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT tests for
> verification and validation before we get to the next stage in the
> delivery of our products.
>
> Right now, I am just saving the xls as text, and parsing the text into
> XML. I was hoping to get rid of a step by using POI, but I only seem
> to be able to get the double value and not the value that was entered
> into the spreadsheet (which represents the output produced by the
> module being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:alex_mayorga@yahoo.com]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: poi-user@jakarta.apache.org
> Subject: Re: AW: Reading numeric values as strings from a cell
>
>
> Maybe I didn't stated my question correctly. But by doing that on a
> Cell formatted as Date you'd probably end up with something like:
> 38966 even if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen in Excel on a
> String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
---------------------------------
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
---------------------------------------------------------------------
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/
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
RE: AW: Reading numeric values as strings from a cell
Posted by "Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com>.
Looks like I would still have to know in advance what the format of the
number in the cell is.
I don't. Sometimes it's a multi-position decimal number; sometimes it's
a whole number. I just want
Whatever is there without having it adjusted in anyway.
When I save the xls as a txt file, the "number" is just fine, which is
why we've been handling it that way.
The problem is that it's difficult to automate the series of tasks on an
ever-widening number of modules when there's a manual step in there to
save the xls as a txt file.
I'll play around with it, though, and see if I can come up with a
general purpose format.
Thanks for trying!
Maffy
-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
Sent: Tuesday, September 12, 2006 10:51 AM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Well, it's a bodge but it will work - and it may even do what you need;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
FileInputStream fis = null;
double value = 0.0;
java.text.DecimalFormat formatter = null; java.text.FieldPosition
fPosition = null; String formattingString = null; String resultString =
null; StringBuffer buffer = null;
try {
// This is the String that will perform the formatting of the
result
// It is best to look at the java.text.DecimalFormat class for an
// explanation of how the String works. It is not necessary to
// split this into two operations I do it here for clarity only.
formattingString = "#,##0.#####";
formatter = new java.text.DecimalFormat(formattingString);
fPosition = new java.text.FieldPosition(0);
buffer = new StringBuffer();
// Open a workbook
fis = new FileInputStream(new
java.io.File("C:\\temp\\Book1.xls"));
wb = new HSSFWorkbook(fis);
// Get a sheet
sheet = wb.getSheetAt(0);
// Get a row
row = sheet.getRow((short)0);
// Get a cell
cell = row.getCell((short)0);
// Check the type of the cell and if numeric format for display
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
// The StringBuffer is used to hold the result of formatting
// the number into a String. A new instance will be required
// each time.
buffer = new StringBuffer();
// Recover the numeric value from the cell
value = cell.getNumericCellValue();
// Format that number for display
formatter.format(value, buffer, fPosition);
// Not strictly necessary but I copy the result from the
// StringBuffer into a String - leave this out for performance
// reasons in production code
resultString = buffer.toString();
// Simply display the result to screen
System.out.println(resultString);
}
}
catch(Exception ex) {
System.out.println("Caught: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stack Trace Follows......");
ex.printStackTrace(System.out);
}
Of course, you will need to modify the code - take out the bit that does
the formatting and create a method that you can call each time you read
a cell from the sheet, take out the formatting string and place it into
a properties file so that you can play around with it and not need to
re-compile the code, etc, etc.
Hope this helps.
Anthony Andrews <py...@yahoo.com> wrote: Have just had a play
around and answered my own question - that approach is not supported by
HSSF.
However, I think that the answer could be found in another 'pattern' I
use in JEXcel. It is possible to get at the formatting object that is
used to determine how a value appears in the cell. That formatter - that
is an instance of the java.text.NumberFormat class - can be applied to
values read from cells to determine how they appear. I reckon you will
need to do something similar for your application; get at the format of
the cell, create a NumberFormat or similar using the formatting String
and then apply it to the value recovered from the cell. If I get the
chance today - that is if I can take another 'quiet' coffee break - I
will have a look at this.
"Finnerty, Mary Ann (GE Healthcare)" wrote:
Not sure. I posted an email about this, and got one response that
seemed to indicate that I'd need to write code to apply a format. I'll
see what I can find out about HSSFDataFormat.
Thanks! I'd love to be able to read the xls directly.
Maffy
-----Original Message-----
From: deshmol-lists@yahoo.com [mailto:deshmol-lists@yahoo.com]
Sent: Wednesday, September 06, 2006 2:54 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Is this something that cannot be handled using HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
>
> We're doing a medical application, and we need the numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT tests for
> verification and validation before we get to the next stage in the
> delivery of our products.
>
> Right now, I am just saving the xls as text, and parsing the text into
> XML. I was hoping to get rid of a step by using POI, but I only seem
> to be able to get the double value and not the value that was entered
> into the spreadsheet (which represents the output produced by the
> module being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:alex_mayorga@yahoo.com]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: poi-user@jakarta.apache.org
> Subject: Re: AW: Reading numeric values as strings from a cell
>
>
> Maybe I didn't stated my question correctly. But by doing that on a
> Cell formatted as Date you'd probably end up with something like:
> 38966 even if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen in Excel on a
> String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
---------------------------------
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
---------------------------------------------------------------------
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 numeric values as strings from a cell
Posted by Anthony Andrews <py...@yahoo.com>.
Well, it's a bodge but it will work - and it may even do what you need;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
FileInputStream fis = null;
double value = 0.0;
java.text.DecimalFormat formatter = null;
java.text.FieldPosition fPosition = null;
String formattingString = null;
String resultString = null;
StringBuffer buffer = null;
try {
// This is the String that will perform the formatting of the result
// It is best to look at the java.text.DecimalFormat class for an
// explanation of how the String works. It is not necessary to
// split this into two operations I do it here for clarity only.
formattingString = "#,##0.#####";
formatter = new java.text.DecimalFormat(formattingString);
fPosition = new java.text.FieldPosition(0);
buffer = new StringBuffer();
// Open a workbook
fis = new FileInputStream(new java.io.File("C:\\temp\\Book1.xls"));
wb = new HSSFWorkbook(fis);
// Get a sheet
sheet = wb.getSheetAt(0);
// Get a row
row = sheet.getRow((short)0);
// Get a cell
cell = row.getCell((short)0);
// Check the type of the cell and if numeric format for display
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
// The StringBuffer is used to hold the result of formatting
// the number into a String. A new instance will be required
// each time.
buffer = new StringBuffer();
// Recover the numeric value from the cell
value = cell.getNumericCellValue();
// Format that number for display
formatter.format(value, buffer, fPosition);
// Not strictly necessary but I copy the result from the
// StringBuffer into a String - leave this out for performance
// reasons in production code
resultString = buffer.toString();
// Simply display the result to screen
System.out.println(resultString);
}
}
catch(Exception ex) {
System.out.println("Caught: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stack Trace Follows......");
ex.printStackTrace(System.out);
}
Of course, you will need to modify the code - take out the bit that does the formatting and create a method that you can call each time you read a cell from the sheet, take out the formatting string and place it into a properties file so that you can play around with it and not need to re-compile the code, etc, etc.
Hope this helps.
Anthony Andrews <py...@yahoo.com> wrote: Have just had a play around and answered my own question - that approach is not supported by HSSF.
However, I think that the answer could be found in another 'pattern' I use in JEXcel. It is possible to get at the formatting object that is used to determine how a value appears in the cell. That formatter - that is an instance of the java.text.NumberFormat class - can be applied to values read from cells to determine how they appear. I reckon you will need to do something similar for your application; get at the format of the cell, create a NumberFormat or similar using the formatting String and then apply it to the value recovered from the cell. If I get the chance today - that is if I can take another 'quiet' coffee break - I will have a look at this.
"Finnerty, Mary Ann (GE Healthcare)" wrote:
Not sure. I posted an email about this, and got one response that
seemed to indicate that I'd need to write code to apply a format. I'll
see what I can find out about HSSFDataFormat.
Thanks! I'd love to be able to read the xls directly.
Maffy
-----Original Message-----
From: deshmol-lists@yahoo.com [mailto:deshmol-lists@yahoo.com]
Sent: Wednesday, September 06, 2006 2:54 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Is this something that cannot be handled using HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
>
> We're doing a medical application, and we need the numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT tests for
> verification and validation before we get to the next stage in the
> delivery of our products.
>
> Right now, I am just saving the xls as text, and parsing the text into
> XML. I was hoping to get rid of a step by using POI, but I only seem
> to be able to get the double value and not the value that was entered
> into the spreadsheet (which represents the output produced by the
> module being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:alex_mayorga@yahoo.com]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: poi-user@jakarta.apache.org
> Subject: Re: AW: Reading numeric values as strings from a cell
>
>
> Maybe I didn't stated my question correctly. But by
> doing that on a Cell
> formatted as Date you'd probably end up with
> something like: 38966 even
> if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen
> in Excel on a String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
---------------------------------
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
RE: AW: Reading numeric values as strings from a cell
Posted by Anthony Andrews <py...@yahoo.com>.
Have just had a play around and answered my own question - that approach is not supported by HSSF.
However, I think that the answer could be found in another 'pattern' I use in JEXcel. It is possible to get at the formatting object that is used to determine how a value appears in the cell. That formatter - that is an instance of the java.text.NumberFormat class - can be applied to values read from cells to determine how they appear. I reckon you will need to do something similar for your application; get at the format of the cell, create a NumberFormat or similar using the formatting String and then apply it to the value recovered from the cell. If I get the chance today - that is if I can take another 'quiet' coffee break - I will have a look at this.
"Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com> wrote:
Not sure. I posted an email about this, and got one response that
seemed to indicate that I'd need to write code to apply a format. I'll
see what I can find out about HSSFDataFormat.
Thanks! I'd love to be able to read the xls directly.
Maffy
-----Original Message-----
From: deshmol-lists@yahoo.com [mailto:deshmol-lists@yahoo.com]
Sent: Wednesday, September 06, 2006 2:54 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Is this something that cannot be handled using HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
>
> We're doing a medical application, and we need the numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT tests for
> verification and validation before we get to the next stage in the
> delivery of our products.
>
> Right now, I am just saving the xls as text, and parsing the text into
> XML. I was hoping to get rid of a step by using POI, but I only seem
> to be able to get the double value and not the value that was entered
> into the spreadsheet (which represents the output produced by the
> module being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:alex_mayorga@yahoo.com]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: poi-user@jakarta.apache.org
> Subject: Re: AW: Reading numeric values as strings from a cell
>
>
> Maybe I didn't stated my question correctly. But by
> doing that on a Cell
> formatted as Date you'd probably end up with
> something like: 38966 even
> if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen
> in Excel on a String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
RE: AW: Reading numeric values as strings from a cell
Posted by "Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com>.
Not sure. I posted an email about this, and got one response that
seemed to indicate that I'd need to write code to apply a format. I'll
see what I can find out about HSSFDataFormat.
Thanks! I'd love to be able to read the xls directly.
Maffy
-----Original Message-----
From: deshmol-lists@yahoo.com [mailto:deshmol-lists@yahoo.com]
Sent: Wednesday, September 06, 2006 2:54 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Is this something that cannot be handled using HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
<ma...@ge.com> wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
>
> We're doing a medical application, and we need the numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT tests for
> verification and validation before we get to the next stage in the
> delivery of our products.
>
> Right now, I am just saving the xls as text, and parsing the text into
> XML. I was hoping to get rid of a step by using POI, but I only seem
> to be able to get the double value and not the value that was entered
> into the spreadsheet (which represents the output produced by the
> module being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:alex_mayorga@yahoo.com]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: poi-user@jakarta.apache.org
> Subject: Re: AW: Reading numeric values as strings from a cell
>
>
> Maybe I didn't stated my question correctly. But by
> doing that on a Cell
> formatted as Date you'd probably end up with
> something like: 38966 even
> if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen
> in Excel on a String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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 numeric values as strings from a cell
Posted by de...@yahoo.com.
Is this something that cannot be handled using
HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
<ma...@ge.com> wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0,
> 0.97, etc.
>
> We're doing a medical application, and we need the
> numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT
> tests for verification
> and validation before we get to the next stage in
> the delivery of our
> products.
>
> Right now, I am just saving the xls as text, and
> parsing the text into
> XML. I was hoping to get rid of a step by using
> POI, but I only seem to
> be able to get the double value and not the value
> that was entered into
> the spreadsheet (which represents the output
> produced by the module
> being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:alex_mayorga@yahoo.com]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: poi-user@jakarta.apache.org
> Subject: Re: AW: Reading numeric values as strings
> from a cell
>
>
> Maybe I didn't stated my question correctly. But by
> doing that on a Cell
> formatted as Date you'd probably end up with
> something like: 38966 even
> if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen
> in Excel on a String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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 numeric values as strings from a cell
Posted by "Finnerty, Mary Ann (GE Healthcare)" <ma...@ge.com>.
This is similar to what we're trying to do.
When I read the spreadsheet, it has numbers like: 100, 10000, 0.967,
etc.
When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
We're doing a medical application, and we need the numbers eXACTLY as
entered into the spreadsheet to create our JUNIT tests for verification
and validation before we get to the next stage in the delivery of our
products.
Right now, I am just saving the xls as text, and parsing the text into
XML. I was hoping to get rid of a step by using POI, but I only seem to
be able to get the double value and not the value that was entered into
the spreadsheet (which represents the output produced by the module
being tested).
Maffy
-----Original Message-----
From: Alex Mayorga Adame [mailto:alex_mayorga@yahoo.com]
Sent: Wednesday, September 06, 2006 1:21 PM
To: poi-user@jakarta.apache.org
Subject: Re: AW: Reading numeric values as strings from a cell
Maybe I didn't stated my question correctly. But by doing that on a Cell
formatted as Date you'd probably end up with something like: 38966 even
if in your Worksheet you see: 6-Sep-06
I would like to store the latter, the data as seen in Excel on a String.
Hope this clears up the confusion.
Thanks in advance,
Alex
Daniel Noll wrote:
>
> Alex Mayorga Adame wrote:
>> In short, there's a need to extract the contents of the cells as they
>> show on the worksheet, extracted as a string no matter what the Cell
>> number format is.
>>
>> Any help would be highly appreciated.
>
> We do this the trivial way, i.e. String.valueOf(doubleValue);
>
> Daniel
>
>
> --
> Daniel Noll
> ...
>
--
View this message in context:
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
26130.html#a6179056
Sent from the POI - User forum at Nabble.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/
---------------------------------------------------------------------
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 numeric values as strings from a cell
Posted by Alex Mayorga Adame <al...@yahoo.com>.
Maybe I didn't stated my question correctly. But by doing that on a Cell
formatted as Date you'd probably end up with something like: 38966
even if in your Worksheet you see: 6-Sep-06
I would like to store the latter, the data as seen in Excel on a String.
Hope this clears up the confusion.
Thanks in advance,
Alex
Daniel Noll wrote:
>
> Alex Mayorga Adame wrote:
>> In short, there's a need to extract the contents of the cells as they
>> show
>> on the worksheet, extracted as a string no matter what the Cell number
>> format is.
>>
>> Any help would be highly appreciated.
>
> We do this the trivial way, i.e. String.valueOf(doubleValue);
>
> Daniel
>
>
> --
> Daniel Noll
> ...
>
--
View this message in context: http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf1126130.html#a6179056
Sent from the POI - User forum at Nabble.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/