The XLS Renderer is used to generate an Excel file. The generated output is compatible with Excel 97 or later.
The layouts for the renderer are as follows:
- Streamed: Data will be displayed on one continuous worksheet in the Excel file.
- Sectioned: The Sectioned option renders each selected section on a separate worksheet in the Excel file.
- Paged: Selecting the Paged option, will cause the data to be split into multiple pages (based on the page setup) and displayed across multiple worksheets. When Paged is false, page headers and footers will NOT be rendered.
- Tree: The Tree option renders the report into a tree structure, based on the Table of Contents (TOC)
field defined in the group header properties. There should be two nested groups with the same criteria.
The outer group is blank with TOC enabled, while the inner group contains the information. The outer group will be used to split the data such that there will be one set of data on each worksheet. The inner group will then show the header, records and footer on each worksheet.
You can use the following options with any of the layouts defined above.
- Snap: The Snap option may improve (or worsen)
the layout of Excel cells. When Snap is off, the cells are positioned
exactly the same as in the report template. This means if there is a one
pixel gap between two report elements, there will be a one pixel row or
column in the Excel file.
When Snap is on, all coordinates are adjusted to fit on a 4x4 pixel grid. This means that a component at (9,11) will be adjusted to (8,8). This adjustment removes the very small cells used as spacers, but may adjust the layout slightly to accommodate the grid.
You should try both Snap on and Snap off to see which works best for your template. In general, if you use HBox and VBox to perform the alignment, then Snap off usually gives the best results.
- Force numbers as text: The Force numbers as text option allows the full format of the numbers to be displayed. When this option is off, the numbers rendered in Excel output will be displayed in a whole figure format. When this option is on, the numbers rendered in Excel output will be displayed as is, including the decimals.
- Multiplier: XLS allows setting the cell width scale in Excel output. To configure the cell width scale, enter an appropriate value for Multiplier. To allow lengthy words to be displayed fully, enter a larger multiplier value.
XLS will take the Left Margin and Top Margin
settings done in Page Setup and apply it when rendering.
Note
There is a limit to the number of rows that spreadsheets
can display in one sheet. The limit varies between products
and versions, but is around 32,000 rows. Paged option is not
applicable to On Render Begin.