XLS Tutorial

  The output to the Excel format is quite different from the other 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 atributes, 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 accomodate the whole content.

Even though it is possible to create absolute textboxes in Excel the same way it works in Word, we thought it wouldn't make much sense to do this as the user wouldn't benefit from seeing the output in Excel, rather than in Word or PDF.

With XFRX, the reports will probably need some tweaking, expecially the complicated ones, but the result will be a normal Excel document, as if someone created it manually.


Demo

  The following document consists of several sheets. The first sheet displays the list of customers, the subsequent sheets contain detailed information about each customer's invoices.

Demo worksheet

This report is included in the XFRX evaluation version (download).


How does it work?

 XFRX makes use of the possibility to merge more 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.)

How about page breaks?

  In the XLS output, XFRX does not break pages the same way as in Word or PDF - we run it in the plain mode instead, which means the output is one sheet - as long as it needs to be, with page header on the top and page footer at the bottom.
If more reports are processed, each report creates one sheet in the output document.

How to invoke the XLS output?

  It is pretty much the same as with the other targets: 
local loSession, lnRetval
loSession=EVALUATE([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.

XLS cells adjustment

  When XLS document is generated, 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 different. 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

How to achieve the best results?

 
  1. Align the fields.
    Have a look at the following document: 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: xls2.xls.

  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:

    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

Please let us know

  Your feedback if important to us. Please let us know what you think about this feature. Should you have problems with your reports, send it to us (to support@eqeus.com), we will be happy to help you out.