poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Finnerty, Mary Ann \(GE Healthcare\)" <maryann.finne...@ge.com>
Subject RE: AW: Reading numeric values as strings from a cell
Date Tue, 12 Sep 2006 18:35:08 GMT
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 <pythonaddict@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/


Mime
View raw message