How to use XLS formula while using XFRX_CopyToXLSX

This feature is since XFRX 19.0.0

The use of static XLS Formula

You can download the result for XLSX or ODS.

#INCLUDE xfrx.h

IF INLIST(VAL(STRTRAN(SUBS(VERSION(),LEN("Visual FoxPro ")+1,2),"0","")),5,6)
   SET LIBRARY TO foxtools.fll
ENDIF   

LOCAL m.lcOUT, m.lcPath, m.lcAFormula
m.lcOUT=PROGRAM()
m.lcPath=JUSTPATH(SYS(16))+"\"
m.lcAFormula=SYS(2015)
SET PROCEDURE TO (lcPath+"xfrx.prg")

CREATE CURSOR (m.lcAFormula) ;
  (XX000 M, XX001 M, XDATE D, ISTATE N(17,2), CREDIT N(17,2), DEBIT N(17,2), FSTATE M, PF N(17,2))
***************************************************************************************************
*  HIDE     A        B        C               D               E              F         G
***************************************************************************************************
* Field XX000 is column with control codes, will be hidden in XLSX output
LOCAL m.ldXDATE, m.lnISTATE, m.lnCREDIT, m.lnDEBIT, m.lii
m.ldXDATE={^2019.01.01}
STORE 0 TO m.lnISTATE, m.lnCREDIT, m.lnDEBIT
=RAND(-1)
FOR m.lii=1 TO 12
    INSERT INTO (m.lcAFormula) ; && Field FSTATE contains XLS formula (A1 or R1C1 Notation)
       VALUES ("DATA","", m.ldXDATE, m.lnISTATE, RAND()*1000, RAND()*1000, "RC[-3]+RC[-2]-RC[-1]", 0) *
* RC[-4]+RC[-3]-RC[-2]                                     - R1C1 Notation
* C%R%+D%R%-E%R%                                           - A1 Notation with macros
* STRTRAN("C%R%+D%R%-E%R%","%R%",LTRIM(STR(RECNO()+1,11))) - A1 Notation
    REPLACE PF WITH CREDIT-DEBIT   
    m.ldXDATE=GOMONTH(m.ldXDATE, 1)
    m.lnISTATE=m.lnISTATE+CREDIT-DEBIT 
    m.lnCREDIT=m.lnCREDIT+CREDIT
    m.lnDEBIT=m.lnDEBIT+DEBIT 
NEXT
m.liErr=XFRX_CopyToXLSX(m.lcAFORMULA, lcPath+"_out\"+lcOut+".xlsx","copytoxlsfccallback_sformula", "Credit and Debit", 1,.T.)
IF m.liErr<>0
   ?"ERROR",m.liErr
ENDIF
CLEAR ALL
CLOSE ALL
SET LIBRARY to
RETURN


PROCEDURE copytoxlsfccallback_sformula
LPARAMETERS m.laFields
LOCAL m.lii
* Drop column XX000 - column with control codes
=ADEL(m.laFields, 1, 1)
DIMENSION m.laFields(ALEN(m.laFields,1)-1, ALEN(m.laFields,2))
FOR m.lii=1 TO ALEN(laFields,1)
    WITH m.laFields(m.lii, XFRX_FORMATCOL_HCELLF)
    .align="right" && align
    .font="Tahoma" 
    .fontsize=12
    ENDWITH
NEXT

* First column has not caption
m.laFields(1, XFRX_FORMATCOL_CAPTION)=""

m.laFields(2, XFRX_FORMATCOL_CAPTION)="Month"
m.laFields(2, XFRX_FORMATCOL_XLSFORMAT)="mm.yyyy"

m.laFields(3, XFRX_FORMATCOL_CAPTION)="Initial State"
m.laFields(3, XFRX_FORMATCOL_XLSFORMAT)='# ##0.00'

m.laFields(4, XFRX_FORMATCOL_CAPTION)="Credit"
m.laFields(4, XFRX_FORMATCOL_XLSFORMAT)='# ##0.00'

m.laFields(5, XFRX_FORMATCOL_CAPTION)="Debit"
m.laFields(5, XFRX_FORMATCOL_XLSFORMAT)='# ##0.00'

m.laFields(6, XFRX_FORMATCOL_CAPTION)="Final State"
* This column is text and contains XLS formula
m.laFields(6, XFRX_FORMATCOL_ISFORMULA)=.T. 
m.laFields(6, XFRX_FORMATCOL_XLSFORMAT)='# ##0.00'
m.laFields(6, XFRX_FORMATCOL_DCELLF).Align="right"

m.laFields(7, XFRX_FORMATCOL_CAPTION)="Profit/Loss"
m.laFields(7, XFRX_FORMATCOL_ISFORMULA)=.T. 
m.laFields(7, XFRX_FORMATCOL_XLSFORMAT)='# ##0.00'
* A1 notation
*m.laFields(7, XFRX_FORMATCOL_DCELLF).cXLSFormula='D%R%-E%R%'
* R1C1 Notation
m.laFields(7, XFRX_FORMATCOL_DCELLF).cXLSFormula='RC[-3]-RC[-2]'
*m.laFields(7, XFRX_FORMATCOL_DCELLF).cXLSFormula='D2-E2'
ENDPROC

The use of dynamic XLS Formula

You can download the result for XLSX or ODS.

#INCLUDE xfrx.h
IF INLIST(VAL(STRTRAN(SUBS(VERSION(),LEN("Visual FoxPro ")+1,2),"0","")),5,6)
   SET LIBRARY TO foxtools.fll
ENDIF   

LOCAL m.lcOUT, m.lcPath, m.lcAFormula
m.lcOUT=PROGRAM()
m.lcPath=JUSTPATH(SYS(16))+"\"
m.lcAFormula=SYS(2015)
SET PROCEDURE TO (lcPath+"xfrx.prg")
CREATE CURSOR (m.lcAFormula) ;
  (XX000 M, XX001 M, XDATE D, ISTATE N(17,2), CREDIT N(17,2), DEBIT N(17,2), FSTATE M, PF N(17,2))
***************************************************************************************************
*  HIDE     A        B        C               D               E              F         G
***************************************************************************************************
* Field XX000 is column with control codes, will be hidden in XLSX output
LOCAL m.ldXDATE, m.lnISTATE, m.lnCREDIT, m.lnDEBIT, m.lii
m.ldXDATE={^2019.01.01}
STORE 0 TO m.lnISTATE, m.lnCREDIT, m.lnDEBIT
=RAND(-1)
FOR m.lii=1 TO 12
    INSERT INTO (m.lcAFormula) ; && Field FSTATE contains XLS formula (A1 or R1C1 Notation)
       VALUES ("DATA","", m.ldXDATE, m.lnISTATE, RAND()*1000, RAND()*1000, "C%R%+D%R%-E%R%", 0) 
    REPLACE PF WITH CREDIT-DEBIT   
    m.ldXDATE=GOMONTH(m.ldXDATE, 1)
    m.lnISTATE=m.lnISTATE+CREDIT-DEBIT 
    m.lnCREDIT=m.lnCREDIT+CREDIT
    m.lnDEBIT=m.lnDEBIT+DEBIT 
NEXT
INSERT INTO (m.lcAFormula) VALUES ("EMPTY","", {}, 0, 0, 0, "", 0)
INSERT INTO (m.lcAFormula) VALUES ("FOOTERIS","Initial State", {}, 0, 0, 0, "", 0)
INSERT INTO (m.lcAFormula) VALUES ("FOOTERC","Credit", {},  m.lnCREDIT, 0, 0, "", 0) 
INSERT INTO (m.lcAFormula) VALUES ("FOOTERD","Debit", {},  m.lnDEBIT, 0, 0, "", 0) 
INSERT INTO (m.lcAFormula) VALUES ("FOOTERFS","Final State", {}, m.lnISTATE , 0, 0, "", 0) 
INSERT INTO (m.lcAFormula) VALUES ("FOOTERPL","Profit/Loss", {}, m.lnISTATE-0 , 0, 0, "", 0) 

m.liErr=XFRX_CopyToXLSX(m.lcAFORMULA, lcPath+"_out\"+lcOut+".xlsx","copytoxlsfccallback_formula", "Credit and Debit", 1,.T.)

IF m.liErr<>0
   ?"ERROR",m.liErr
ENDIF
CLEAR ALL
CLOSE ALL
SET LIBRARY to
RETURN

PROCEDURE copytoxlsfccallback_formula
LPARAMETERS m.laFields
LOCAL m.lii
* Drop column XX000 - column with control codes
=ADEL(m.laFields, 1, 1)
DIMENSION m.laFields(ALEN(m.laFields,1)-1, ALEN(m.laFields,2))
FOR m.lii=1 TO ALEN(laFields,1)
    WITH m.laFields(m.lii, XFRX_FORMATCOL_HCELLF)
    .align="right" && align
    .font="Tahoma" 
    .fontsize=12
    ENDWITH

    WITH m.laFields(m.lii, XFRX_FORMATCOL_DCELLF)
    .DynamicFontBold=[XX000<>"DATA"]
    ENDWITH
NEXT

* First column has not caption
m.laFields(1, XFRX_FORMATCOL_CAPTION)=""

m.laFields(2, XFRX_FORMATCOL_CAPTION)="Month"
m.laFields(2, XFRX_FORMATCOL_XLSFORMAT)="mm.yyyy"
WITH m.laFields(2, XFRX_FORMATCOL_DCELLF)
* Dynamic XLS format
.DynamicXLSFormat='IIF(XX000="DATA", "mm.yyyy", '+;
                  'IIF(NOT INLIST(XX000,"DATA","EMPTY"), "# ##0.00", ""))'
* Use XLS formula for records where controls code not is DATA
.DynamicISXLSFormula=[XX000<>"DATA"]
* Define different XLS formula 
.DynamicXLSFormula='IIF(XX000="FOOTERIS", "R[-%LR%+1]C[1]", '+; && %LR% - macro last row
                   'IIF(XX000="FOOTERC", "SUM(R[-%LR%+1]C[2]:R[-3]C[2])", '+;
                   'IIF(XX000="FOOTERD", "SUM(R[-%LR%+1]C[3]:R[-4]C[3])", '+;
                   'IIF(XX000="FOOTERFS", "R[-5]C[4]", '+;
                   'IIF(XX000="FOOTERPL", "R[-1]C-R[-4]C", "")))))'
* Define different column source
* Output file contain value and formula 
.DynamicExpression='IIF(XX000="DATA", XDATE, IIF(XX000="EMPTY", "", ISTATE))'
ENDWITH

m.laFields(3, XFRX_FORMATCOL_CAPTION)="Initial State"
WITH m.laFields(3, XFRX_FORMATCOL_DCELLF)
.DynamicISXLSFormula=[XX000="DATA" AND XDATE>{^2019.01.01} OR XX000<>"DATA"]
.DynamicXLSFormula='IIF(XX000="DATA" AND XDATE>{^2019.01.01}, "R[-1]C[3]", "")'
* Output file contain value and formula 
.DynamicExpression='IIF(XX000="DATA", ISTATE, "")'
ENDWITH

m.laFields(4, XFRX_FORMATCOL_CAPTION)="Credit"
* Output file contain value and formula 
m.laFields(4, XFRX_FORMATCOL_DCELLF).DynamicExpression='IIF(XX000="DATA", CREDIT, "")'

m.laFields(5, XFRX_FORMATCOL_CAPTION)="Debit"
* Output file contain value and formula 
m.laFields(5, XFRX_FORMATCOL_DCELLF).DynamicExpression='IIF(XX000="DATA", DEBIT, "")'

m.laFields(6, XFRX_FORMATCOL_CAPTION)="Final State"
* This column is text and contains XLS formula
m.laFields(6, XFRX_FORMATCOL_ISFORMULA)=.T. 
m.laFields(6, XFRX_FORMATCOL_XLSFORMAT)='# ##0.00'
* Output file contain only formula 
m.laFields(6, XFRX_FORMATCOL_DCELLF).Align="right"

m.laFields(7, XFRX_FORMATCOL_CAPTION)="Profit/Loss"
m.laFields(7, XFRX_FORMATCOL_ISFORMULA)=.T. 
m.laFields(7, XFRX_FORMATCOL_XLSFORMAT)='# ##0.00'

* A1 notation
WITH m.laFields(7, XFRX_FORMATCOL_DCELLF)
.DynamicISXLSFormula=[XX000="DATA"]
.DynamicXLSFormula='IIF(XX000="DATA", "D%R%-E%R%", "")' && %R% macro current row
* Output file contain value and formula 
.DynamicExpression='IIF(XX000="DATA", PF, "")'
ENDWITH
ENDPROC