Bug 65520 - evaluate result is 0 when a cell type is mistook as string
Summary: evaluate result is 0 when a cell type is mistook as string
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2021-08-26 10:33 UTC by Syl
Modified: 2021-09-08 18:25 UTC (History)
0 users

file and debug img (555.36 KB, image/png)
2021-08-26 10:33 UTC, Syl
screenshot showing excel treating text formatted numbers as zero when summing (27.79 KB, image/png)
2021-08-29 12:53 UTC, PJ Fanning

Note You need to log in before you can comment on or make changes to this bug.
Description Syl 2021-08-26 10:33:20 UTC
Created attachment 37989 [details]
file and debug img

i am reading a xls file with cell value("=SUM(J15:CX15)"),
in excel the result is ok,but when i read it with poi,the result is 0.
i tried to find the problem and i see it maybe caused by the celltype of J15 to CX15,most of the type is string.
my poi version is 3.17,and i tried to change that to 4.1.2,but that not work.how can i get the correct result?
Comment 1 PJ Fanning 2021-08-26 11:57:59 UTC
Before using the formula evaluator, couldn't you correct the cell types on the cells using POI cell setCellType?
Comment 2 Syl 2021-08-26 12:37:06 UTC
actually i can't.before my project evaluator the cell,i can't know which area need to be change.
and i think i can't change all cells .
Comment 3 PJ Fanning 2021-08-26 13:02:31 UTC
you can search for cells that have sum formulas and work out the cells that are part of the summed range - someone might have time to look at this issue but the fix won't be released for a while
Comment 4 Syl 2021-08-26 13:33:09 UTC
ok,thank you for your answer!
Comment 5 PJ Fanning 2021-08-26 13:36:25 UTC
No harm leaving this open in case someone has the time to look into it.
Comment 6 PJ Fanning 2021-08-29 12:52:50 UTC
I could not reproduce this problem. In my sharepoint hosted version of excel, the text formatted strings were treated as zero in the autosum. POI should match this behaviour.

I will attach a screenshot.
Comment 7 PJ Fanning 2021-08-29 12:53:57 UTC
Created attachment 38001 [details]
screenshot showing excel treating text formatted numbers as zero when summing
Comment 8 Syl 2021-08-29 13:10:47 UTC
i'm so sorry for this ,yestoday i found that another member in my project changed the cell's type to STRING in some place.so in excel it's ok because cell type is still standard.

by the way ,could you tell me the relationship of cell type in excel and code ?
now i just know in code there are STRING,NUM,FORMULA,BLANK,ERR... and in excel there are STANDARD,TEXT,DATE,TIME...
may i have a list for the corresponding relationship?