poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kais Dukes" ...@kaisdukes.com>
Subject RE: Text field read in as numeric sometimes and text others times
Date Wed, 04 Feb 2004 19:50:11 GMT
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


Mime
View raw message