Page 1 of 1
AsFormula cells do not open correctly in Excel 2007
Posted: Thu Apr 09, 2015 3:52 am
by mwhiting
When using AsFormula to create a cell, the formula cell created will have a result of 0 when opened in Excel 2007. When opening in 2010 and 2013 the cell shows the result of the formula as expected.
This appears to only be the case when the formula is not a plain arithmetic formula.
For Instance:
- Correctly shows result: =H88
- Correctly shows result: =H88 * H87
- Shows 0: =MyNamedCell
- Shows 0 however the hyperlink actually works: =HYPERLINK("#'MytabName'!C5","My Pretty Link Name")
If the cell is put in edit mode and exited (F2 then enter) then Excel treats it as a formula cell and shows the result as it should.
Is there something I need to specify in order to have these work correctly in 2007 or did this expose a bug?
I've checked my TXLSReadWriteII5 version property after creation and before writing and in both cases it is xvExcel2007.
Re: AsFormula cells do not open correctly in Excel 2007
Posted: Thu Apr 09, 2015 3:59 am
by mwhiting
Are these being written out as Text cells instead of Formula cells?
Re: AsFormula cells do not open correctly in Excel 2007
Posted: Thu Apr 09, 2015 4:51 pm
by mwhiting
When opening in Excel 2007 and then saving, the formula tag for the cell does not contain the aca nor ca attribute. When I edit the cell and save it, the ca attribute is added.
Code: Select all
<c r="C6" s="6">
<f ref="C6:R6" t="shared" si="0">InputCell</f>
<v>0</v>
</c>
Without an edit E6 shows as:
<c r="E6" s="6">
<f t="shared" si="0"/>
<v>0</v>
</c>
With an edit E6 shows as:
<c r="E6" s="6">
<f ca="1">InputCell</f>
<v>1000</v>
</c>
On
https://msdn.microsoft.com/en-us/librar ... ellformula
I found that ca designates "Calculate Cell" - "Indicates that this formula needs to be recalculated the next time calculation is performed."
aca is "Always Calculate Cell"
With this discovery I need to know how to set this attribute. I found the following in the WriteFormula procedure of TXLSWriteSheetData.OnWriteCell but I haven't been able to see how to set these.
Code: Select all
Cell.F.Aca := (FH.Options and Xc12FormulaOpt_ACA) <> 0;
Cell.F.CA := (FH.Options and Xc12FormulaOpt_CA) <> 0;
For the hyperlinks it appears to be the absence of the t="str" attribute of the cell that is the problem.
Code: Select all
<c r="A10" t="str" s="25">
<f>HYPERLINK("#'Numerics'!A1","Simple Numerics")</f>
<v>Simple Numerics</v>
</c>
Re: AsFormula cells do not open correctly in Excel 2007
Posted: Thu Apr 09, 2015 6:45 pm
by mwhiting
Issue resolved
I verified that if the ca attribute is added to the formulas both the hyperlinks and the formulas with names will calculate in 2007.
Verified by setting this in WriteFormula procedure of TXLSWriteSheetData.OnWriteCell
I then played around until I found the following solution.
Assuming variables are within scope, the following will set the ca (Calculate Cell) formula option for the cell created.
Code: Select all
procedure SetFormulaOptionCalcCell(aColumn, aRow: integer);
var
cellToAlter : TXLSCellMMU;
formulaHelper : TXLSFormulaHelper;
begin
cellToAlter := myXLS[mySheet].Cell[aColumn,aRow].Cells;
formulaHelper := cellToAlter.FormulaHelper;
formulaHelper.Options := Xc12FormulaOpt_CA;
end;
myXLS[mySheet].AsFormula[myColumn,myRow] := myText;
SetFormulaOptionCalcCell(myColumn,myRow);
Re: AsFormula cells do not open correctly in Excel 2007
Posted: Mon Apr 13, 2015 2:46 pm
by larsa
Hello
Your suggested fix will not work as expected. It's not possible to set the CA flag for individual formulas. There is no easy fix for this so it will not be implemented. The next update will have an option, XLS.FormulasUncalced, where you can set the CA flag for all formulas when the file is written.