You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by av...@itellix.com on 2004/01/14 12:16:56 UTC
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 <tg...@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
Re: Text field read in as numeric sometimes and text others times
Posted by Niall Pemberton <ni...@blueyonder.co.uk>.
I have a small framework based on POI/HSSF which is specifically designed
for this "Data Capture" scenario. It uses the record listener mechanism to
listen for specified records. An XML file configures what data to pick up,
its location and the type to convert it to. The data is populated into
either "dynamic" Java Beans (DynaBeans from Jakarta Commons) or standard
Java Beans.
Would this be appropriate to donate to POI or does it belong outside POI as
a layer on top?
Niall
----- Original Message -----
From: "Tracey Gates" <tg...@yoursummit.com>
To: "'POI Developers List'" <po...@jakarta.apache.org>
Sent: Thursday, February 05, 2004 2:03 PM
Subject: RE: Text field read in as numeric sometimes and text others times
> 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 <tg...@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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org
RE: Text field read in as numeric sometimes and text others times
Posted by Tracey Gates <tg...@yoursummit.com>.
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 <tg...@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
RE: Text field read in as numeric sometimes and text others times
Posted by Kais Dukes <kd...@kaisdukes.com>.
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 <tg...@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
RE: Text field read in as numeric sometimes and text others times
Posted by Tracey Gates <tg...@yoursummit.com>.
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 <tg...@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