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.
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.
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);
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.
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?