Featured post
java - Problem when using JXL Formula like SUM() AVG() STDEV() returns #VALUE! when it refers values from another sheet -
i want populate values in sheet , use jxl formula values sheet , write sheet...
when try run sample code
string filename = "c:\\input.xls"; workbooksettings ws = new workbooksettings(); ws.setlocale(new locale("en", "en")); writableworkbook workbook = workbook.createworkbook(new file(filename), ws); writablesheet s = workbook.createsheet("input", 0); writablesheet s1 = workbook.createsheet("output", 1); s1.addcell(new number(3, 0, 5)); s1.addcell(new number(3, 1, 6)); s1.addcell(new number(3, 2, 1)); s1.addcell(new number(3, 3, 6)); s1.addcell(new number(3, 4, 1)); formula formula = new formula(3,5,"average(output!d1:output!d5)"); s.addcell(formula);
i getting last value in avg list has output
=average(output!d5)
jar used : jxl 1.0.jar....
solution tried :
1) instead of giving has formula gave has label
label label = new label (3,5,"average(output!d1:output!d5)");
i got entire text in cell , gave '=' before cell.. worked charm. want done jxl api
2) changed jar jxl 2.6.jar
now getting #value! when try run same. cell content
=average(output!d1:output!d5) still #value!.
jar used : jxl 2.6.jar
this error getting solved when go cell , press tab key or f2 key.
please provide solution
regards
n.s.balaji
it looks excel evaluating formula in strange way, hence why seeing #value!
. see why formula failing, click on formula cell , go tools > formula auditing > evaluate formula
. see steps are:
average(output!d1:output!d5) =average(5:output!d5) =average(5:1) =average(#value!) =#value!
but when execute f2+enter
on cell, see excel changes execution plan , gets right answer.
i'm afraid thing can think of fix use comma-separated list, instead of range:
formula formula = new formula(3,5, "average(output!d1,output!d2,output!d3,output!d4,output!d5)");
- Get link
- X
- Other Apps
Comments
Post a Comment