34
- 35 -
14 Excel specific features
14.1 General notes
The output to the Excel format is similar to the flow layout formats. The other formats
use absolutely positioned textboxes for each report label or textbox, containing the
generated text output. The XLS output, on the other hand, puts the generated text
directly into the cells on the Excel sheet and sets the height of the rows and width of the
columns to achieve the desired layout. Lines and rectangles, too, are added as cells'
borders, rather than graphics over the sheet.
There are many advantages in this approach: the generated documents are smaller and
much easier to be modified - all numeric fields can be used in calculations, it's no
problem to add rows or columns, change cell attributes, etc.
There are, however, downsides, too: The fields cannot overlap, so something like this:
in the report won't convert correctly, and, as each Excel cell has a margin inside that
cannot be suppressed, some fields might have to be made a little wider to accommodate
the whole content.
With XFRX, the reports will probably need some tweaking, especially the complicated
ones, but the result will be a normal Excel document, as if someone created it manually.
14.2 How it works
XFRX makes use of the possibility to merge more Excel cells together. Wherever a label
or textbox should start or finish, XFRX creates a row and a column. To achieve the best
looking results, it is a good thing to align the labels and textboxes both vertically and
horizontally - result of which is a clearer document with fewer rows and columns. (Please
see more about this below, in How to achieve the best results? paragraph.)
14.3 Handling page breaks
By default, XFRX does not break pages in the XLS output the same way as in other
output formats – it is run in the plain mode instead, which means the output is one sheet
- as long as it needs to be, with a page header on the top and a page footer at the bottom.
If more reports are processed, each report creates one sheet in the output document.
There are two options you can use to modify the way the page breaks are handled:
14.3.1 Generating sheet per page
To enable this option, call SetOtherParams method to set the “SHEET_PER_PAGE”
parameter to .T.:
loSession.SetOtherParams("SHEET_PER_PAGE",.t.)
34
- 36 -
This will switch off the “plain” mode and the page breaks will correspond to other output
options. Each page will be generated as a new sheet in the Excel document.
Please see more information about the SetOtherParams method in Methods common in
XFRXListener and XFRXSession classes
reference on page 80.
14.3.2 Generating sheet per start-each-group-on-a-new-page groups
To enable this option, call SetOtherParams method to set the
“SHEET_PER_NP_GROUP” parameter to .T.:
loSession.SetOtherParams("SHEET_PER_NP_GROUP",.t.)
This option combines the plain mode and the sheet-per-page mode. The report runs in
the plain mode but a new sheet is generated for each report group with “Start each group
on a new page” flag set to .T.
14.4 Defining sheet names
There are two ways how to define sheet names. You can define a static text via
NEXT_SHEET_NAME parameter, or you can use the NEXT_SHEET_NAME_EXPR
parameter to define an expression, which will be evaluated at the bottom of each sheet
and the result will be used as the new sheet name. The latter option is useful if more
sheets are generated during one report run.
Please see more information about SetOtherParams method in Methods common in
XFRXListener and XFRXSession classes
reference on page 80.
14.5 Hiding the Excel sheet grid
The background grid is visible by default. To hide it, please set DISPLAY_GRID_LINES
parameter to false:
loSession.SetOtherParams("DISPLAY_GRID_LINES",.F.)
14.6 Leaving the fields content in Excel cells
By default, the content of non-stretchable fields is cut according to the size of the field,
but you can optionally leave the full content of the field in the Excel cell. To enable this
option, set the LEAVE_FULL_FIELD_CONTENT parameter to .T.:
loSession.SetOtherParams("LEAVE_FULL_FIELD_CONTENT",.T.)
14.7 How to invoke the XLS output
It is pretty much the same as with the other targets:
local loSession, lnRetval
46
- 37 -
loSession= xfrx("XFRX#INIT")
loSession.initLog()
lnRetVal = loSession.SetParams("output.xls",,,,,,"XLS")
If lnRetVal = 0
loSession.SetOtherParams("NEXT_SHEET_NAME","first") && the name of
the sheet, optional
loSession.ProcessReport("report1")
loSession.SetOtherParams("NEXT_SHEET_NAME","second")
loSession.ProcessReport("report2")
loSession.finalize()
ENDIF
This example creates a two sheet document. As you can see, SetOtherParams() method
can be used to define the sheet names. If it is not called, the default names are "sheet1",
"sheet2", etc.
14.8 XLS cells adjustment
When XLS document is generated, the vertical and horizontal coordinates of objects are
adjusted - if the difference between two coordinates is smaller than a certain value, the
coordinates are 'aligned'. This approach significantly reduces the number of rows and
columns in the generated document.
It is possible to define this minimal difference. The greater the number is, the lesser
number of rows/columns is generated, but if the number is too big, fields might get
overlapped and could be left out.
Call SetOtherParams method with "HORIZONAL_ADJUSTMENT" or
"VERTICAL_ADJUSTMENT" to define the minimal horizontal and/or vertical
difference.
Example:
loSession.SetOtherParams("HORIZONTAL_ADJUSTMENT",1000) && default value = 76
loSession.SetOtherParams("VERTICAL_ADJUSTMENT",1000) && default value = 180
14.9 How to achieve the best results
1.
Align the fields.
Have a look at the following document: (http://www.eqeus.com/xls1.xls
)
Columns B and C are almost invisible (if you make them wider, you can see that
customer names start at column B, "Customer List" starts at column C and
"Customer" starts at column D - which is something that we don't notice in
normal report but have better result in the XLS output if the fields are aligned),
row 4 is very narrow, and between each customer, there's added a very thin row,
too.
"Fixing" this is very simple - we aligned the "Customer", "Customer List" labels
and the customer textbox, moved the line below the header a little bit higher so it
lands on the cells below the "Customer" and "Total" captions. We also aligned the
textboxes vertically.
The resulting document looks much better: (http://www.eqeus.com/xls2.xls
)
22
- 38 -
2.
Problem with label width
The size of a label cannot be modified in the report designer - it always takes the
size of the text entered. However, as we mentioned before, the Excel cells have
little margins inside, so if we create a cell as wide as the label and put the text into
it, the whole text wouldn't fit in - the last character or two disappear!
XFRX takes care of this and makes the cell a bit wider, but this can bring another
problem - if there is another label or a text field near the right edge of the label,
increasing the width can result in overlapping the other label or the text field,
result of which would be that one of the two labels disapper (there can be only one
thing inside the cell). So please be careful about this and make sure there is
enough space between the labels.
3.
Variable labels widths
As the width of the label depends on its content, we cannot align both right and
left edges of more labels and sometimes it might be better to replace labels with
textboxes. For example, if there are many labels in a column, all left aligned like
this:
When creating the Excel document, XFRX will create a column for the right edge
of each label:
However, if the labels are converted to textboxes, we can align them:
37
- 39 -
And the result might look better:
It is actually quite easy to convert all labels to textboxes, just open the report in
FoxPro and replace the object type:
USE report.frx
REPLACE objtype WITH 8 ALL FOR objtype = 5
USE
14.10 Numeric field picture format in Excel
The format definition of numeric cells in Excel is different from the format syntax used
in Foxpro. XFRX is now able to convert the simple format definitions and allows for
user-defined
implicit
and/or
explicit
Excel-type
format
definitions.
In Visual Foxpro, the numeric field is converted to its text representation based on an
explicit format definition (format field in the report expression definition) or field's
decimal places and SET DECIMAL setting.
When a numeric field is transformed to an Excel cell, the following algorithm is used:
1.
If there is an explicit XLS format defined, use it.
To define an explicit XLS format for a numeric field, add:
#UR XLSF= (expression)
to the field's comment.
Example 1:
#UR XLSF="General"
The "General" formatting - no special formatting, the number of decimal places is
determined by the field's value
Example 2:
#UR XLSF="Standard"
The "Standard" formatting - two decimal places, thousand and decimal separators will
be used according to the Excel defaults
Example 3:
#UR XLSF="#0.00"
Two decimal places, no leading zeros
33
- 40 -
Example 4:
#UR XLSF="#0.00;[red]#0.00"
Two decimal places, display negative numbers in red
2.
If the field contains a format definition, try to look up the format in a
conversion table.
A conversion table can be populated programmatically when XFRX is executed so that
the formats that are often used and cannot by converted automatically by XFRX would
not require an explicit definition in each field in the report.
To add an entry to the conversion table, use addXLSFormatConversion method of
XFRXSession class.
Example 5:
loSession.addXLSFormatConversion("@L 999999.99","000000.00")
3.
If the field contains a format definition and it is not listed in the
conversion table, try to convert it.
XFRX is able to convert simple format definitions containing the following characters:
'9', '#', ',', '.' and ' '. For example, 999,999.99 is converted to ###,###.00.
If the format cannot be converted, use the implicit XLS numeric format, if available.
To define the implicit XLS numeric format, call setDefaultXLSFormat method of the
XFRXSession object with the implicit format as a parameter.
Example 6:
loSession.setDefaultXLSFormat("General")
4.
If the implicit XLS numeric format is not defined, XFRX creates a
format definition to display the same number of decimals as in VFP
report output.
If you want to specify that a certain numeric field should be converted as a text cell in the
excel sheet, define "TEXT" as its explicit XLS numeric format:
#UR XLSF='TEXT'
For a live example of formatting the Excel cells, please have a look at the "XLS formatted
numeric cells sample" report in the demo application.
Documents you may be interested
Documents you may be interested
