Bug 33156

Summary:	Excel does not recognize POI created cells as numbers
Product:	POI	Reporter:	Tammy Walters <tammy_walters>
Component:	HSSF	Assignee:	POI Developers List <dev>
Status:	RESOLVED INVALID
Severity:	major
Priority:	P2
Version:	2.5-FINAL
Target Milestone:	---
Hardware:	PC
OS:	Windows 2000
Attachments:	Example spreadsheet

Description Tammy Walters 2005-01-18 21:02:11 UTC

I created a spreadsheet using POI.  The cells were formatted with a number 
format.  After opening up the created spreadsheet, I tried to sum one of the 
cumeric columns.  The result of that sum is always 0.  I have manually used 
excel to change the formatting of the column to be a number format, but it does 
not change the sum.  The only thing I can do to get the sum to work is to 
manually re-type the numbers in the spreadsheet.

Comment 1 Tammy Walters 2005-01-18 21:04:29 UTC

Created attachment 14041 [details]
Example spreadsheet

Note that the final two columns in the EODStats sheet have a sum formula at the
bottom of the column.  The Qty Problems sum is 0, and the Waiting for Recpt sum
is 8 because I manually retyped the 8 in the column.

Comment 2 Tammy Walters 2005-01-18 21:08:12 UTC

Code used to set the cell type of numeric:

		cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(cellStyle);

Comment 3 Tammy Walters 2005-01-19 16:37:37 UTC

I found the root cause.  Even though I set the cell type as numeric, when I set 
the cell value I passed in a String instead of a double.  Changing my code to 
test for the string being a number and setting the value accordingly has fixed 
the error in the spreadsheet.

Comment 4 Avik Sengupta 2005-01-19 16:57:33 UTC

Indeed.  Actually, in the usual case ,you dont have to set cell type.. its
automatically set based on the value you pass in. Maybe a FAQ?