java - Apache POI Not Recaclulating -

-

i have workbook large number of formulas, including formulas depend on results of other formulas depend on vlookups, etc. of these lead calculation of single value. when try value via poi 3.13, it's #value!. however, if open workbook in excel, it's calculated correctly.

before getting value need, call:

book.getcreationhelper().createformulaevaluator().evaluateall();

which, according documentation, should resolve these issues. apache's documentation calls out #value! issue , suggests above step resolution, yet can't work. not formula exceptions.

i've tried calling evaluateall() after each time set value (about 100 set scenario), still it's #value!

    public boolean setvalue(final int value, final string tabname, final int rownum, final int colnum) {     boolean returnval;     try {         final cell currentcell = getcell(tabname, rownum, colnum);         currentcell.setcellvalue(value);         currentcell.setcelltype(cell.cell_type_numeric);         book.getcreationhelper().createformulaevaluator().evaluateall();         returnval = true;     } catch (final exception e) {         logger.error("exception setting value " + value + " on " + tabname + " " + rownum + "," + colnum, e);         returnval = false;     }     return returnval; }

the cell in question has formula:

=sum('veh1'!h3+'veh2'!h3+'veh3'!h3+'veh4'!h3)

all of cells, 'veh1'!h3, have similar formulas deal specific sheet:

=if('veh1'!b10>0,sum(h11:w11),0)

where value in 'veh1'!b10 1 through 4. h11 through w11 formula cells, such as: 

=h71+h40

h71 this:

=round(h69*h39,2)

and on down chain of dollar values multipled times factor value. factor values found via lookups:

=if(iserror(vlookup($c26,lookuptable,3,false)),1,vlookup($c26,lookuptable,3,false))

where lookuptable in above named sub table on worksheet. lookups based on values of either a) user (or poi, in case) entered in cell, or b) combination of several cells poi entered, =b1&b2&b3&b4

each of steps repeated dozens of times. basically, sum of 16 values each of 16 sum of 52 rows, half of lookups (many various if conditional logic pieces well), other half pretty simple =round(a1*b1,2) formulas.

it appears last step fails, formula: 

=sum('veh1'!h3+'veh2'!h3+'veh3'!h3+'veh4'!h3)


Comments

Post a Comment

Popular posts from this blog

ruby - Trying to change last to "x"s to 23 -

-
i have been playing around irb , have string looks this: irb(main):072:0> puts ["[\"4354 5432 5432 xxxxx\", \"6547 6547 8543 xxxxx\", \"2344 6543 6674 xxxxx\", \"2346 6236 7543 xxxxx\", \"1273 5585 5587 xxxxx\"]"] => nil irb(main):073:0> what want gsub last 2 "x" s of each 17 digit combination, 23 . what i've tried far (i've been using rubular): a.gsub(/\d[x]/,"23") <= grabs "x" s i'm close a.gsub(/\w[x{2}]/,"23") <= grabs "x" s grabs two digits each combination. is there easier way i'm not understanding? a.gsub(/\d{4} \d{4} \d{4} xxx\kxx/, '23') \d{4} = 4 digits what \k drop matched far. can use don't replace entire number, still able validate have in desired format.
Read more

jquery - Clone last and append item to closest class -

-
this bare-bones code on page: <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <di
Read more

c - Unrecognised emulation mode: elf_i386 on MinGW32 -

-
i'm trying make kernel, , cannot link c output assembly. ld . i'm getting error: unrecognized emulation mode: elf_i386 i'm using windows 10 professional mingw32 , msys. code using: link.ld /* * link.ld */ output_format(elf32-i386) entry(start) sections { . = 0x100000; .text : { *(.text) } .data : { *(.data) } .bss : { *(.bss) } } kernel.c /* * kernel.c */ void kmain(void) { const char *str = "my first kernel"; char *vidptr = (char*)0xb8000; //video mem begins here. unsigned int = 0; unsigned int j = 0; /* loops clears screen * there 25 lines each of 80 columns; each element takes 2 bytes */ while(j < 80 * 25 * 2) { /* blank character */ vidptr[j] = ' '; /* attribute-byte - light grey on black screen */ vidptr[j+1] = 0x07; j = j + 2; } j = 0; /* loop writes string video memory */ while(str[j] != '\0') { /
Read more