This feature allows for exporting cursor data into Excel or OpenOffice spreadsheets, similarly to the COPY TO … TYPE XL5 command, with the following enhancements:
- The number of records is not limited to 65K
- Formatting options, including dynamic formatting, column width, header names and styles and text trimming
Running the export
Run XFRX_CopyToXLSX or XFRX_CopyToODS procedure inside XFRX.PRG/FXP with the following parameters:
- lcAlias - Source ALIAS (required)
- lcFile - Output file name (required)
- lcFCCallBack - Callback function name - please see Formatting options below (optional)
- lcSheetName - Excel sheet name (optional)
- llOnlyData - Export only data - no headers (optional)
Example
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
Formatting options
The process uses the following settings to format the data output:
- Date values
- SET DATE
- SET CENTURY
- SET MARK TO
- Datetime values
- SET DATE
- SET CENTURY
- SET MARK TO
- SET SECONDS
- SET HOURS TO
- Numbers
- SET DECIMALS TO
- SET POINT TO
Additional formatting can be achieved by a callback function, which name you can specify in 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 |
Example
#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