Data export to XLSX and ODS

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

Performance notes

Distribution with your application

XLSXTemplate.zip file, which is now part of the XFRX download package, needs to be distributed with your application for the data export to work correctly.

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). Exporting with the callback function is significantly slower. Please see the performance note above.
lcSheetName - Excel sheet name (optional)
llAddHeader - Export column names as header (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