...
Info |
---|
XFRX 19.0.0 has method CreateFACTURXMetadata() |
Use static XLS FormulaWorkaround for XFRX 17.3-18.2
Code Block |
---|
#INCLUDELOCAL xfrx.h IF INLIST(VAL(STRTRAN(SUBS(VERSION(),LEN("Visual FoxPro ")+1,2),"0","")),5,6) SET LIBRARY TO foxtools.fll ENDIF LOCAL m.lcOUTm.lcXFRX, m.lnRetVal, m.lcPathlcXMLMD, m.lcAFormulalcRDFMD, m.lcOUT=PROGRAM()lcXMLFile m.lcPath=JUSTPATH(SYS(16))+"\lcXMLFile="c:\temp\Factur-X.xml" m.lcAFormulaloXFRX=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 BEVALUATE([xfrx("XFRX#INIT")]) m.lnRetVal = loXFRX.SetParams("Factur-X.pdf",,,,.T.,,"PDF") IF m.lnRetVal = 0 m.loXFRX.setpdfa(.T.,"3B") && Must version 3 m.lcXMLMD=[ <rdf:Description rdf:about="" xmlns:fx="urn:factur-x:pdfa:CrossIndustryDocument:invoice:1p0#">]+CHR(13)+CHR(10)+; C [ <fx:ConformanceLevel>BASIC</fx:ConformanceLevel>]+CHR(13)+CHR(10)+; D [ <fx:DocumentFileName>]+JUSTFNAME(m.lcXMLFile)+[</fx:DocumentFileName>]+CHR(13)+CHR(10)+; E [ <fx:DocumentType>INVOICE</fx:DocumentType> ]+CHR(13)+CHR(10)+; 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=DATE(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] [ <fx:Version>1.0</fx:Version>]+CHR(13)+CHR(10)+; [ </rdf:Description>]+CHR(13)+CHR(10) m.lcRDFMD=[ <rdf:li rdf:parseType="Resource">]+CHR(13)+CHR(10)+; [ <pdfaSchema:schema>Factur-X PDFA Extension Schema</pdfaSchema:schema>]+CHR(13)+CHR(10)+; [ <pdfaSchema:namespaceURI>urn:factur-x:pdfa:CrossIndustryDocument:invoice:1p0#</pdfaSchema:namespaceURI>]+CHR(13)+CHR(10)+; [ - R1C1 Notation * C%R%+D%R%-E%R% <pdfaSchema:prefix>fx</pdfaSchema:prefix>]+CHR(13)+CHR(10)+; [ <pdfaSchema:property>]+CHR(13)+CHR(10)+; [ - A1 Notation with macros * STRTRAN("C%R%+D%R%-E%R%","%R%",LTRIM(STR(RECNO()+1,11))) - A1 Notation<rdf:Seq>]+CHR(13)+CHR(10)+; 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 |
Czech error messages
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<rdf:li rdf:parseType="Resource">]+CHR(13)+CHR(10)+; [ <pdfaProperty:name>DocumentFileName</pdfaProperty:name>]+CHR(13)+CHR(10)+; [ <pdfaProperty:valueType>Text</pdfaProperty:valueType>]+CHR(13)+CHR(10)+; [ <pdfaProperty:category>external</pdfaProperty:category>]+CHR(13)+CHR(10)+; [ <pdfaProperty:description>name of the embedded XML invoice file</pdfaProperty:description>]+CHR(13)+CHR(10)+; [ </rdf:li>]+CHR(13)+CHR(10)+; [ <rdf:li rdf:parseType="Resource">]+CHR(13)+CHR(10)+; [ <pdfaProperty:name>DocumentType</pdfaProperty:name>]+CHR(13)+CHR(10)+; [ <pdfaProperty:valueType>Text</pdfaProperty:valueType>]+CHR(13)+CHR(10)+; [ D <pdfaProperty:category>external</pdfaProperty:category>]+CHR(13)+CHR(10)+; E [ F<pdfaProperty:description>INVOICE</pdfaProperty:description>]+CHR(13)+CHR(10)+; 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=DATE(2019,01,01) STORE 0 TO m.lnISTATE, m.lnCREDIT, m.lnDEBIT =RAND(-1) FOR m.lii=1 TO 12 </rdf:li>]+CHR(13)+CHR(10)+; 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)<rdf:li rdf:parseType="Resource">]+CHR(13)+CHR(10)+; REPLACE PF WITH CREDIT-DEBIT [ m.ldXDATE=GOMONTH(m.ldXDATE, 1) m.lnISTATE=m.lnISTATE+CREDIT-DEBIT<pdfaProperty:name>Version</pdfaProperty:name>]+CHR(13)+CHR(10)+; 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", '+; <pdfaProperty:valueType>Text</pdfaProperty:valueType>]+CHR(13)+CHR(10)+; [ <pdfaProperty:category>external</pdfaProperty:category>]+CHR(13)+CHR(10)+; [ <pdfaProperty:description>The actual version of the Factur-X XML schema</pdfaProperty:description>]+CHR(13)+CHR(10)+; [ </rdf:li>]+CHR(13)+CHR(10)+; [ <rdf:li rdf:parseType="Resource">]+CHR(13)+CHR(10)+; [ <pdfaProperty:name>ConformanceLevel</pdfaProperty:name>]+CHR(13)+CHR(10)+; [ <pdfaProperty:valueType>Text</pdfaProperty:valueType>]+CHR(13)+CHR(10)+; [ <pdfaProperty:category>external</pdfaProperty:category>]+CHR(13)+CHR(10)+; '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 <pdfaProperty:description>The conformance level of the embedded Factur-X data</pdfaProperty:description>]+CHR(13)+CHR(10)+; [ 'IIF(XX000="FOOTERC", "SUM(R[-%LR%+1]C[2]:R[-3]C[2])", '</rdf:li>]+CHR(13)+CHR(10)+; [ 'IIF(XX000="FOOTERD", "SUM(R[-%LR%+1]C[3]:R[-4]C[3])", ' </rdf:Seq>]+CHR(13)+CHR(10)+; [ 'IIF(XX000="FOOTERFS", "R[-5]C[4]", '</pdfaSchema:property>]+CHR(13)+CHR(10)+; [ '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>DATE(2019,01,01) OR XX000<>"DATA"] .DynamicXLSFormula='IIF(XX000="DATA" AND XDATE>DATE(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</rdf:li>]+CHR(13)+CHR(10) m.loXFRX.AddAttachment(m.lcXMLFile, .T., "XML-Format", "text/XML", "Data", m.lcXMLMD) m.loXFRX.AttachmentsList.cExtensiondata=m.lcRDFMD m.loXFRX.ProcessReport("FActur-X.frx") ENDIF |
Use method CreateFACTURXMetadata()
Code Block |
---|
LOCAL m.lcXFRX, m.lnRetVal, m.lcXMLFile, m.loFile
m.lcXMLFile="c:\temp\Factur-X.xml"
m.loXFRX=EVALUATE([xfrx("XFRX#INIT")])
m.lnRetVal = loXFRX.SetParams("Factur-X.pdf",,,,.T.,,"PDF")
IF m.lnRetVal = 0
m.loXFRX.setpdfa(.T.,"3B") && Must version 3
m.loFile=m.loXFRX.AddAttachment(m.lcXMLFile, .T., "XML-Format", "text/XML", "Data")
m.loFile.CreateFACTURXMetadata("BASIC", "INVOICE", "1.0")
m.loXFRX.ProcessReport("FActur-X.frx")
ENDIF
|