In a large data export with 60 columns in total the speed is averaging 2,500 records per second. When formatting callback function is on, the speed can get slower - depending on the number of columns and formatting options.
XLSXTemplate.zip
file, which is now part of the XFRX download package, must be distributed with your application for the data export to work correctly.
Run XFRX_CopyToXLSX or XFRX_CopyToODS procedure inside XFRX.PRG/FXP with the following parameters:
OPEN DATABASE "C:\Program Files (x86)\Microsoft Visual FoxPro 9\Samples\Northwind\Northwind" USE "Invoices" SET PROCEDURE TO xfrx.prg m.liErr=XFRX_CopyToXLSX("Invoices", "test.xlsx", , "Invoices") IF m.liErr<>0 ?"ERROR",m.liErr ENDIF |
The last parameter (loProgress) is an optional XFRX progress bar object reference. If specified, the export process will call the UpdateProgress() method in this object with export progress information. See more at Running XFRX#Displaying progress bar in VFP 8.0.
The export process checks the value of m.gnStopXFRX public variable. See more at Running XFRX#Canceling report generation in progress.
The data export process uses the following settings to format the data output:
Additional formatting can be achieved by a callback function, which name you specify as the 4th parameter of the XFRX_CopyToXLSX/XFRX_CopyToODS methods. When the callback function is specified, it is called during the export, with an array of all columns with their properties as a parameter. The parameter is an array - one item per column. Each item is again an array with 12 items, which contains attributes for each column.
RowNo | Content |
---|---|
1 | Field name |
2 | Field type |
3 | Field width |
4 | Field decimals |
5 | NULL indicator |
6 | NOCPTRANS indicator |
7 | Header caption |
8 | XLS format |
9 | Column Width in px |
10 | Header cell format - Object with attributes |
11 | Trim Mode 0 - none, 1 - LTRIM, 2 - RTRIM, 3 - ALLTRIM |
12 | Data cell format - Object with attributes |
#DEFINE XFRX_FORMATCOL_FNAME 1 && Field name #DEFINE XFRX_FORMATCOL_FTYPE 2 && Field type #DEFINE XFRX_FORMATCOL_FWIDTH 3 && Field width #DEFINE XFRX_FORMATCOL_FDEC 4 && Field decimals #DEFINE XFRX_FORMATCOL_FNULL 5 && NULL indicator #DEFINE XFRX_FORMATCOL_FNOCPT 6 && NOCPTRANS indicator #DEFINE XFRX_FORMATCOL_CAPTION 7 && Header caption #DEFINE XFRX_FORMATCOL_XLSFORMAT 8 && XLS format #DEFINE XFRX_FORMATCOL_WIDTH 9 && Column Width in px #DEFINE XFRX_FORMATCOL_HCELLF 10 && Header cell format - Object with attributes #DEFINE XFRX_FORMATCOL_TMODE 11 && Trim Mode 0 - none, 1 - LTRIM, 2 - RTRIM, 3 - ALLTRIM #DEFINE XFRX_FORMATCOL_DCELLF 12 && Data cell format - Object with attributes ... m.liErr=XFRX_CopyToXLSX("test", "test.xlsx", "copytoxlsxfccallback", "test") ... PROCEDURE copytoxlsxfccallback(m.laFields) LOCAL ARRAY m.laColor(3) FOR m.lii=1 TO ALEN(m.laFields,1) m.laFields(m.lii,XFRX_FORMATCOL_CAPTION)="Description "+LTRIM(STR(m.lii,11))+" "+m.laFields(m.lii,XFRX_FORMATCOL_FNAME) && Caption * Header cell format WITH m.laFields(m.lii,XFRX_FORMATCOL_HCELLF) .align="center" && horizontal align .font="Tahoma" .fontsize=12 .bold=.T. .ital=.T. .underline=.T. .lstriked=.T. && StrikeThru __C2RGB(RGB(255,255,255),@m.laColor) && BackColor .fillred=m.laColor(1) .fillgreen=m.laColor(2) .fillblue=m.laColor(3) __C2RGB(RGB(192,192,192),@m.laColor) && ForeColor .penred=m.laColor(1) .pengreen=m.laColor(2) .penblue=m.laColor(3) ENDWITH * Data cell format WITH m.laFields(m.lii,XFRX_FORMATCOL_DCELLF) .lstriked=.T. .font="Courier New" .DynamicFontBold=[IIF(UNITPRICE>50,.T.,.F.)] .DynamicForeColor=[IIF(UNITPRICE>50,RGB(255,0,0),0)] .DynamicFontItalic=[IIF(UNITPRICE>50,.T.,.F.)] .DynamicFontUnderline=[IIF(UNITPRICE>50,.T.,.F.)] .DynamicFontName=[IIF(UNITPRICE>50,'Courier New','Arial')] .DynamicFontSize=[IIF(UNITPRICE>50,12,10)] .DynamicAlignment=[IIF(UNITPRICE>50,'center','')] .DynamicBackColor=[IIF(UNITPRICE>50,RGB(192,192,192),RGB(255,255,255))] .DynamicFontStrikeThru=[IIF(UNITPRICE>50,.T.,.F.)] ENDWITH *m.laFields(m.lii,XFRX_FORMATCOL_XLSFORMAT)="" && XLS cell format m.laFields(m.lii,XFRX_FORMATCOL_WIDTH)=100 && Width in pixels m.laFields(m.lii,XFRX_FORMATCOL_TMODE)=3 && Trim Mode 0 - none, 1 - LTRIM, 2 - RTRIM, 3 - ALLTRIM NEXT |