Excel function is overwritten with = 0 by file DL function using jxlser
0
Hi, Mx Expert, I am developing the UL / DL function of an Excel file using jxlser, but I am stuck with the following points. Please tell me the solution. 【QA target functional specifications】 UL / DL function of Excel file is implemented using jxlser. Among these, as a specification of the DL file, the Excel function is embedded in the Excel template, and it is retained in the file even after DL and used. 【question】 If you use the above function and DL, the function in the file will be overwritten with "= 0". Please tell me the countermeasure to prevent "= 0" overwriting after DL. As a sample, I will attach the template you are actually using and the file after download. The part in red in the file is the part where the corresponding event occurred. ・Ex Templete（= SUMIF (v! $ B $ 11: $ B $ 20000, B1, v! $ Y $ 11: $ Z $ 20000)） Download File:（= 0） 【Confirmation results so far】 (1) Enclose the template calculation formula in "". ⇒ The inside of "" was displayed as it was. Ex) "= SUMIF (v! $ B $ 11: $ B $ 20000, B1, v! $ Y $ 11: $ Z $ 20000)" (2) Exclude "$" in the following two patterns. 1. Exclude the "$" in front of the column. ⇒ Overwritten with "= 0". Ex) = SUMIF (v! B $ 11: B $ 20000, B1, v! Y $ 11: Z $ 20000) 2. Exclude the "$" before the line. ⇒ After DL, the calculation formula was displayed. Ex) = SUMIF (v! $ B11: $ B20000, B1, v! $ Y11: $ Z20000)
asked
Keiichi Ichikawa
2 answers
0
JXLS sets all formulas referencing removed cells to =0
Your formula is referencing cells that do not exist after processing the template: unless you are really filling 19989 rows of data, row 20000 will not exist, but as long as you keep a $ in front of it, you keep a hard reference to a non-existing cell.
When you remove the $ sign in front of row 20000, that’s not a hard reference anymore, so JXLS does not set the formula to ‘=0’.