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