Cells with formula

FLX TMS FlexCel for VCL & FMX
1

Good morning to all,

till now j always used Excel file without formula.

In this file(s) j have on



column 1 the code product,

column 2 produtd name

column 3 produtc selling price

column 4 product vat

column 5 product price - vat



but, from few days ago in this Excel file now the column 5 is present not a number but one formula as



=E12*(1-I12)



Another problem is who j work with the arranged file with the columns as above, but the original files sended to me from custumer/client are not with the columns as above.



Now j can check with column has the formula and replace the result with the currency value (two digit).



In real case this is what j have in column 5 from one file and in column 4 in the other (same value) calculated with this formula =E12*(1-I12)

14.63 with Excel

14.6319545454545 in flexcel grid



how can j show 14.63 ??



J need to set



TFlxFormat f := Xls.GetDefaultFormat;

f.Format := '#,##0.00';



for the column X where j get the formaula =E12*(1-I12)



Excuse me for bad explanation and for this problem, but j'm new in working with formula in cells/columns



Best regard



Daniele



2

Hi,

You just need to do as you wrote:



TFlxFormat f := Xls.GetDefaultFormat;

f.Format := '#,##0.00';



And then

Xls.setcellformat(xls.Addformat(f));



And finally grid.LoadSheet

3

Hi Adrian,

sorry but j missed (for a while) the concept that j must aplly the new format at every cells that contain formula.



Now j can handle cells with formula with this code post in dobule cycle (row/col)



      Value := Xls.GetCellValue(Row, Col, XF);

      if (Value.IsFormula) then

      begin

        Fmt:=Xls.GetDefaultFormat;

        Fmt.Format := '###,###,##0.00';

        Xls.SetCellFormat(Row,Col,xls.Addformat(Fmt));

      end;



But, is not finish here ! unfortunately for me !!!

If the cell contain formula ok, but if the cell contain a currency value only with 8-10 decimal like 20,7545454545455.

How is possible set the new format for that cell ?

J tried with Value.Isnumber but the cells with the date or % are number as well and j need to avoid to change the format.



Thank's again for your help.



Daniele

4

Hi,

Change



Fmt:=Xls.GetDefaultFormat;

By

Fmt:=Xls.GetFormat(XF);



This is so you keep all the existing format in the cell(like font or fill color). But also, you can now do:

if (Value.IsFormula or Value.IsNumber) and (Fmt.Format = '')then



This will only modify cell which didn't had already a format assigned