...
| Info |
|---|
This feature is since XFRX 19.0.0 |
Use The use of static XLS Formula
You can download the result for XLSX or ODS.
| Code Block |
|---|
#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 |
Use The use of dynamic XLS Formula
You can download the result for XLSX or ODS.
| Code Block |
|---|
#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
|
...