poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tracey Gates" <tga...@yoursummit.com>
Subject RE: Text field read in as numeric sometimes and text others times
Date Thu, 05 Feb 2004 14:03:12 GMT
So then how does it know that what is in the cell is text and not numeric?
It took some of the SSN fields as text but others as numeric even without
the single quote.

If this sounds like a dumb question, I apologize but I've just started to
use the POI API.

-----Original Message-----
From: Kais Dukes [mailto:kd@kaisdukes.com]
Sent: Wednesday, February 04, 2004 1:50 PM
To: POI Developers List; tgates@yoursummit.com
Subject: RE: Text field read in as numeric sometimes and text others
times


Formatting a cell as text will NOT generate a cell with text in the output
XLS file. This only sets the display format.

-----Original Message-----
From: Tracey Gates [mailto:tgates@yoursummit.com]
Sent: 04 February 2004 19:41
To: 'POI Developers List'
Subject: RE: Text field read in as numeric sometimes and text others
times


I've deleted the text in the cells then saved. Then I re-entered the text
with a ' (single quote) and saved.  That seemed to fix it but still have the
question...

Why would it recognize the cell as being text on some and numeric on others
when I have formatted the fields as "text"??


-----Original Message-----
From: avik.sengupta@itellix.com [mailto:avik.sengupta@itellix.com]
Sent: Wednesday, February 04, 2004 7:17 PM
To: POI Developers List
Subject: Re: Text field read in as numeric sometimes and text others
times


My standard response would be to try and put ' (single quote) in front to
force
it to be a string, but since you've tried that, i must admit i am stumped :)

Can you send an example sheet over? Better to attach it to bugzilla to
ensure it
wont get lost. I could have a play around with it.

Regards
-
Avik


Quoting Tracey <tgates@yoursummit.com>:

> I have a spreadsheet that contains cells with employee's name and SSN.  I
am
>
> reading this spreadsheet into a Vector and then populating a MySQL
database
> with the data by enumeration through the Vector and doing a toString() on
the
>
> element.  The problem that I'm having is that some of the SSN cells are
being
>
> read in as text and others as numeric.
>
> INPUT DATA:
> Anderson   Patricia   443219876
> Andrews    Randy      442615243
> Barker     Marshal    123456789
> Mathews    Mark       123443212
> Thompson   Lucille    987654323
>
>
> JAVA CLASS CODE SNIPPET:
> FileInputStream in = new FileInputStream( filename );
> try
> {
>    POIFSFileSystem fs = new POIFSFileSystem( in );
>    HSSFWorkbook wb = new HSSFWorkbook( fs );
>    HSSFSheet sheet = wb.getSheetAt( 0 );
>    Iterator it = sheet.rowIterator();
>    HSSFCellStyle cellStyle = wb.createCellStyle();
>    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat( "text" ));
>
>    int rows = sheet.getPhysicalNumberOfRows();
>    for ( int r = 9; r < rows; r++ )
>    {
>      Vector currentRow = new Vector();
>      HSSFRow row = sheet.getRow( r );
>      int cells = row.getPhysicalNumberOfCells();
>      for ( short c = 0; c < cells; c++ )
>      {
>        HSSFCell cell = row.getCell( c );
>        cell.setCellStyle(cellStyle);
>        String s = new String();
>        try
>        {
>          s = cell.getStringCellValue();
>        }
>        catch (NumberFormatException ne)
>        {
>          s = "" + cell.getNumericCellValue();
>        }
>        currentRow.addElement(s);
>      }
>      v.addElement(currentRow);
>    }
> }
> catch (Exception e)
> {
>    System.out.println(e);
>    e.printStackTrace();
>    return false;
> }
> .....
>
> OUPUT IN DATABASE:
> Anderson   Patricia   443219876
> Andrews    Randy      4.426152E  (not read in as text)
> Barker     Marshal    123456789
> Mathews    Mark       1.344321E  (not read in as text)
> Thompson   Lucille    987654323
>
> I have checked the cell Formatting in Excel to make sure that it says that
it
>
> is Text.  I've tried to put a ' in front of the numbers to indicate text.
> I've
> tried a custom format of "0".  None of these make the cells read in
> consistently as text.
>
> I need the SSNs to read in as Text.  Please help.
> Thanks.
> Tracey
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-dev-help@jakarta.apache.org
>
>





---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org


Mime
View raw message