Data export to XLSX and ODS

This feature allows for exporting table/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. The current Excel limit is 1,048,576 rows per sheet. If the number of exported rows is larger, the output goes into multiple sheets.
  • The generated file can be up to 2GB in size 
  • Formatting options, including dynamic (conditional) formatting, column width, header names and styles and text trimming

Performance note

Excel: 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.

OpenOffice: exporting to OpenOffice is currently slower than exporting to Excel.

Distribution with your application

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.

Open XML limatiton

Cell's limit for string is 32767 characters. Because LibreOffice Calc open and shows XLSX file with longer string in cell, then XFRX don't trim long string.

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)
    .F. - Don't add header
    .T. - Add header with auto filter
     0 - Don't add header (since XFRX 18.0)
     1 - Add header (since XFRX 18.0)
     3 - Add header with auto filter (since XFRX 18.0)
  • llShowGrid - Show/hide grid (optional), default is .T.
  • loProgress - XFRX progress bar object (optional)
  • lcSheetGroupExpression - Expression for splitting data to more sheets (XFRX 15.3)
  • tlAppendToFile - Specifies whether the generated document will be appended to an existing file. This parameter can be either logical, numeric or a string (XFRX 18.2 - only for XLSX)
  • tnRowHeight -  row height (unit is pixel) for data (XFRX 19.0)
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

Progress bar

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.

Canceling export in progress

The export process checks the value of m.gnStopXFRX public variable. See more at Running XFRX#Canceling report generation in progress.

Formatting options

The data export 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 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.

ColNo

Content

XFRX

1

Field name


2Field type
3Field width
4Field decimals
5NULL indicator
6

NOCPTRANS indicator


7

Header caption


8XLS format
9

Column Width in px


10

Header cell format - Object with attributes


11

Trim Mode 0 - none, 1 - LTRIM, 2 - RTRIM, 3 - ALLTRIM


12Data cell format - Object with attributes
13For internal using
14 Code page 
15Is Formula (.T./.F.) 
16Field source (Alternate source)21.0

Static properties

NameDescription XFRX

Align

Alignemnt: left, right, center
Font Font name 
Fontsize Font size 
BoldStyle bold
Ital Style italic
UnderlineStyle underline
lStriked Style strikethru
FillRedBackColor - red
FillGreenBackColor - green
FillBlueBackColor - Blue
PenRedForeColor - red
PenGreenForeColor - Green
PenBlueForeColor - blue

cXLSFormula 


19.0

ISXLSFormula 


19.0

Dynamic properties

Dynamic propertier are equal to dynamic properties of column class.

NameDescriptionXFRX 
DynamicFontBoldExpression for bold
DynamicForeColorExpression for fore color
DynamicFontItalicExpression for style italic
DynamicFontUnderlineExpression for style underline
DynamicFontNameExpression for font name
DynamicFontSizeExpression for font size
DynamicAlignmentExpression for alignment
DynamicBackColorExpression for back color
DynamicFontStrikeThruExpression for style strike thru
DynamicXLSFormatExpression for XLS format19.0

DynamicISXLSFormula

Expression for XLS formula's flag19.0

DynamicXLSFormula 

Expression for XLS formula19.0

DynamicExpression 

Expression for column source19.0

Formula's macros

NameDescription
%R%Current row
%C%Current column

%LR%

Last row
%FR%First row
%LC%Last column

Examples

Basic example

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
#DEFINE XFRX_FORMATCOL_CP        14 && Code Page (XFRX 18.2.0)
#DEFINE XFRX_FORMATCOL_ISFORMULA 15 && Formula (Yes/No)
#DEFINE XFRX_FORMATCOL_FSRC      16 && Field source (Alternate source) (XFRX 21.0)


#DEFINE _TOXLSX_ERROR_OK                        0 && OK 
#DEFINE _TOXLSX_ERROR_OpenTemplateFailed       -1 && Cannot open template file
#DEFINE _TOXLSX_ERROR_PrepareFilesFailed       -2 && Cannot create files
#DEFINE _TOXLSX_ERROR_SaveToXLSXFailed         -3 && Cannot create xlsx file (zlib.dll)
#DEFINE _TOXLSX_ERROR_SaveToXLSX_ODSFailed   -200 && Cannot create xlsx/ods file (zlib.dll)
 

...
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.)]

      IF INLIST(m.laFields(m.liCol, XFRX_FORMATCOL_FTYPE), "B", "F", "N", "I","Y")
         .DynamicXLSFORMAT=[IIF(UNITPRICE>=1000,'0.00','# ##0.00')] && Since XFRX 19.0.0
         m.laFields(lii,XFRX_FORMATCOL_XLSFORMAT)=""
      ENDIF

      IF m.laFields(m.liCol, XFRX_FORMATCOL_FTYPE)="M"
         m.laFields(lii,XFRX_FORMATCOL_FSRC)="LEFT("+m.laFields(lii,1)+", 50)"
      ENDIF
    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

Sheet Group Expression

If you want split data to more lits, you can use parameter  lcSheetGroupExpression:

* 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); if contains %XFRX_SGV% thne will be replaced with group value
* llAddHeader - Export column names as header  (optional)
* llShowGrid - Show/hide grid (optional), default is .T.
* loProgress - XFRX progress bar object (optional)
 (new) lcSheetGroupExpression - Expression for splitting data to more sheets

OPEN DATABASE "C:\Program Files (x86)\Microsoft Visual FoxPro 9\Samples\Northwind\Northwind"
USE "Invoices"
INDEX ON ORDERDATE TAG "I01" && data must be indexed

SET PROCEDURE TO xfrx.prg
m.liErr=XFRX_CopyToXLSX("Invoices", "test.xlsx", , "Year %XFRX_SGV%", .T.,.T.,,'YEAR(ORDERDATE)')
IF m.liErr<>0
   ?"ERROR",m.liErr
ENDIF