Formula that returns empty string won't be loaded as formula cell

[eclbnhan]

Please help me overcome this when loading an excel 2007 file. FPSpreadsheet won't load the formula for the cell if the formula of the cell returns an empty string. I really need FPSpreadsheet to load the formula of the cell no matter what.

Here is the example:
A1=RAND()       B1=IF(A1<0.4,"","*")
A2=RAND()       B2=IF(A2<0.4,"","*")
A3=RAND()       B3=IF(A3<0.4,"","*")
A4=RAND()       B4=IF(A4<0.4,"","*")
A5=RAND()       B5=IF(A5<0.4,"","*")
A6=RAND()       B6=IF(A6<0.4,"","*")

Here are what on the Excel when it is saved:
A                      B
0.125219641   
0.065275077   
0.494708522   *
0.07620718   
0.36592127   
0.442791815   *

Here are what FPSpreadsheet loaded the Excel:
A                                     B
0.548813502304256   
0.5928446163889021   
0.715189364971593   *
0.8442657440900803   
0.6027633703779429   
0.8579456198494881   *

You will see that Column B won't display the value correctly because the value of A1, A2, A4, A5 at the time the excel file was saved is less than 0.4. Therefore, value of B1, B2, B4, B5 is an empty string. FPSpreadsheet load B1, B2, B4, B5 as empty cells without formula info.

Now while FPSpreadsheet running, if I change value in A1 to 555, B1 is still an empty cell since no formula is there to react.

Now still with FPSpreadsheet, if I change A3 to 0.1 then B3 will change from * to empty string. Great! However, if now I change A3 to 555 then B3 still stay as an empty cell. Not so good! The reason is B3 no longer has formula.

I use [boReadFormulas] and I used with [boAutoCalc] or without [boAutoCalc] (and did a CalcFormulas)

I am not sure if it is a bug or there would be the other to work around.
Please help! Thank you!

[wp]

Good catch! Please try r5751. The problem was that the worksheet's WriteBlank (which is called by CalcFormula if the result is an empty cell) erased the formula.

[eclbnhan]

Awesome! That's quick! I was excited to try r5751. You fixed the problem. Amazing! Thank you very much!

[wp]

I had to redo the fix because leaving the formula in a empty cell intact would prevent the user from clearing a cell by calling WriteBlank if the formula would return a non-blank cell. But should be ok now. Please test.

There's an issue if I convert the xlsx file with above formulas to xls: the biff8 reader does not accept the IF formula because Excel inserts a special attribute flag into the binary rpn token stream. Unfortunately, this flag makes reading the rpn tokens unnecessarily complicated... So, for the moment, I decided to leave this xls issue alone and wait until somebody complains...

[eclbnhan]

I tested again with xlsx file. I don't see any thing breaking. It's working great, off course! Thank you!