POI Cell Data Type

[James Ellis]

I am pulling an unknown amount of columns and rows from a DB and iterating through these to write the results into an excel file using POI (HSSF). Unfortunately when I pull a string from the database and write this string to the .xls file when I then open up the .xls file using excel there is a little green triangle in the corner of the aforementioned cell which (when hovered over) states "The number in this cell is formatted as text or preceded by an apostrophe.".

I understand this is just a "feature" of excel trying to warn me, but is there any work around to this? I don't want to check every cell I am writing to see if it's numeric before I write it bc that would be a big performance hit.

Any ideas?

[Ulf Dittmer]

Could it be that by default a cell has a numeric type in POI, and that you're not changing that to text when storing a string in it?

Or are you writing into an existing spreadsheet? In that case, why are cells being formatted as numbers if you're going to store text into them?

[James Ellis]

I am writing into a new spreadsheet. The data type of the cell is determined by the value you place in it. So if I do

 cell.setCellValue(new String("hello"));
 

The cell type in excel will be text. When I do

 cell.setCellValue(8675309);
 

The cell type will be numeric. The problem arises when I pull from the database a string that contains a number.

 cell.setCellValue(rs.getString("col_with_number"));
 

Excel sees that there is a number in there and gives you a warning that you've got a number in a text field.

Doesn't look like I can do anything about that short of warning users that the green warning triangle is OK or else checking every single value I put into every cell and changing its type if its a number.
[ September 09, 2005: Message edited by: James Ellis ]

[Ulf Dittmer]

If you know that the contents are text, use setCellType(HSSFCell.CELL_TYPE_STRING) to set its type to text.

[cccccccccc zzzzzzzzzzzz]

I'm trying to use the setCellType function in POI, but it doesn't seem to be working, or I may not be understanding it correctly.

When i do cell.setCellValue("Some String") then cell.setCellType(CELL_TYPE_STRING), then after the the excel file is done outputting, i right click on that cell and it shows the type as "General" instead of "text".. I tried cell.setCellValue(14) then cell.setCellType(CELL_TYPE_NUMERIC), and when i right click on that cell, it still shows as "general" instead of "numeric".

[Ulf Dittmer]

Hello "cccccccccc zzzzzzzzzzzz "-

Welcome to JavaRanch.

On your way in you may have missed that we have a policy on screen names here at JavaRanch. Basically, it must consist of a first name, a space, and a last name, and not be obviously fictitious. Since yours does not conform with it, please take a moment to change it, which you can do right here.