...
...
...
...
...
...
...
- 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.
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)
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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.
...
1
...
Field name
...
NOCPTRANS indicator
...
Column Width in px
...
Header cell format - Object with attributes
...
Trim Mode 0 - none, 1 - LTRIM, 2 - RTRIM, 3 - ALLTRIM
...
Running the export
Run XFRX_CopyToXLSX or XFRX_CopyToODS procedure inside XFRX.PRG/FXP with the following parameters:
lcAlias / loGrid
Source ALIAS or grid (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)
Value | Description | Version |
---|---|---|
.F. | Don't add header | |
.T. | Add header with auto filter | |
0 | Don't add header | 18.0 |
1 | Add header | 18.0 |
3 | Add header with auto filter | 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)
Source types
Alias
Alias of table or cursor. You can change column formatting across callback funtion - see to Formatting options.
Grid
Object reference to grid. XFRX reads formatting options from grid including column's dynamic properties.
Info |
---|
Grid as source was added in XFRX 24.0 |
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 | Version |
---|---|---|
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 | |
13 | For internal using | |
14 | Code page | |
15 | Is Formula (.T./.F.) | |
16 | Field source (Alternate source) | 21.0 |
Static properties
Name | Description | Version |
---|---|---|
Align | Alignemnt: left, right, center | |
Font | Font name | |
Fontsize | Font size | |
Bold | Style bold | |
Ital | Style italic | |
Underline | Style underline | |
lStriked | Style strikethru | |
FillRed | BackColor - red | |
FillGreen | BackColor - green | |
FillBlue | BackColor - Blue | |
PenRed | ForeColor - red | |
PenGreen | ForeColor - Green | |
PenBlue | ForeColor - blue | |
cXLSFormula | 19.0 | |
ISXLSFormula | 19.0 |
Dynamic properties
Dynamic propertier are equal to dynamic properties of column class.
Name | Description | Version |
---|---|---|
DynamicFontBold | Expression for bold | |
DynamicForeColor | Expression for fore color | |
DynamicFontItalic | Expression for style italic | |
DynamicFontUnderline | Expression for style underline | |
DynamicFontName | Expression for font name | |
DynamicFontSize | Expression for font size | |
DynamicAlignment | Expression for alignment | |
DynamicBackColor | Expression for back color | |
DynamicFontStrikeThru | Expression for style strike thru | |
DynamicXLSFormat | Expression for XLS format | 19.0 |
DynamicISXLSFormula | Expression for XLS formula's flag | 19.0 |
DynamicXLSFormula | Expression for XLS formula | 19.0 |
DynamicExpression | Expression for column source | 19.0 |
Formula's macros
Name | Description |
---|---|
%R% | Current row |
%C% | Current column |
%LR% | Last row |
%FR% | First row |
%LC% | Last column |
Examples
Basic example
Code Block | |||||
---|---|---|---|---|---|
|
...
| ||
#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:
Code Block | ||||
---|---|---|---|---|
| ||||
* 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
|
...