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?
Before using the formula evaluator, couldn't you correct the cell types on the cells using POI cell setCellType?
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 .
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
ok,thank you for your answer!
No harm leaving this open in case someone has the time to look into it.
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.
Created attachment 38001 [details] screenshot showing excel treating text formatted numbers as zero when summing
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?