[SOLVED] Problem with calc formulas
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
melwinek · 2015-05-03 15:05

Code: [Select]y:=5;
SQLQueryKalkulacjePozycja.First;
while not SQLQueryKalkulacjePozycja.eof do
begin
 
MyWorksheet.WriteNumber(y,2,SQLQueryKalkulacjePozycja['iloscnetto']);
  MyWorksheet.WriteNumber(y,3,SQLQueryKalkulacjePozycja['wydajnosc']);
  MyWorksheet.WriteFormula(y,4,'=IF(D'+Inttostr(y+1)+'=0;
  MyWorksheet.WriteNumber(y,5,SQLQueryKalkulacjePozycja['cena']);
  MyWorksheet.WriteUTF8Text(y,6,SQLQueryKalkulacjePozycja['waluta']);
  MyWorksheet.WriteFormula(y,7,'=IF(G'+Inttostr(y+1)+'="EUR";IF(Q2=0;0
;F'+Inttostr(y+1)+'/Q2);IF(G'+Inttostr(y+1)+'="GBP";IF(Q3=0;0;F'+Intto
str(y+1)+'/Q3);IF(G'+Inttostr(y+1)+'="USD";IF(Q4=0;0;F'+Inttostr(y+1)+
'/Q4);F'+Inttostr(y+1)+')))',True);
  MyWorksheet.WriteFormula(y,8,'=E'+Inttostr(y+1)+'*H'+Inttostr(y+1),T
rue);
  MyWorksheet.WriteNumber(y,9,SQLQueryKalkulacjePozycja['koszt']);
  MyWorksheet.WriteFontSize(cell,8);
  MyWorksheet.WriteFormula(y,10,'(J'+Inttostr(y+1)+'/100)*I'+Inttostr(
y+1),True);
  MyWorksheet.WriteFormula(y,11,'I'+Inttostr(y+1)+'+K'+Inttostr(y+1),T
rue);
  SQLQueryKalkulacjePozycja.Next;
  y:=y+1;
end;

MyWorkbook.Options := MyWorkbook.Options + [boReadFormulas,
boCalcBeforeSaving, boAutoCalc];

After open in Librecalc formulas for example in cells I7, I8, I9, K7,
K8, K9, L7, K8, K9 are not calculated.
I have to manually edit the cell. Only then did cell calculates.

This problem is in sfOOXML In sfExcel8 is OK.

----------------------------------------------------------------------
wp · 2015-05-03 15:41

I am a bit confused because you show fpspreadsheet code but the file
you are posting is not written by fpspreadsheet (it has a formula
attribute 'aca="false"' which is not used by fpspreadsheet).

Please explain more details what you are doing. It would also be nice
you could post a sample project which I can compile and run to find
the error.

----------------------------------------------------------------------
melwinek · 2015-05-03 15:47

This file is written by fpspreadsheet, open with libreoffice and save
with libreoffice.

Now I use sfExcel8. In sfExcel8 is OK.
But it does not work MyWorksheet.PageLayout.ScalingFactor, always is
10%.

----------------------------------------------------------------------
wp · 2015-05-03 16:08

Can you post the file written by fpspreadsheet? I want to check if the
formulas are calculated.

All that PageLayout stuff is very new and not yet fully tested, some
features (fonts in headers/footers) are not even  implemented at all.

----------------------------------------------------------------------
melwinek · 2015-05-03 17:07

Files in both formats.

----------------------------------------------------------------------
wp · 2015-05-03 20:09

Got it. Formula results are written, but sometimes they are not
correctly calculated. Fixed in r4110. Thanks for reporting.

----------------------------------------------------------------------
wp · 2015-05-04 11:04

QuoteBut it does not work MyWorksheet.PageLayout.ScalingFactor, always
is 10%.Could you check if ScalingFactor is still in error? I fixed a
mix-up of lines in r4112.

----------------------------------------------------------------------
melwinek · 2015-05-04 16:30

It's OK.