The XLSX Renderer is used to generate an Excel file. The generated output is compatible with Excel 2007 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.
- Password: Enter a password as desired. The Excel file will be protected with the password entered here. By default, this option is blank, meaning that the Excel file does not have any password protection.
- Multiplier: XLSX 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.
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. The Paged option is not
applicable to On Render Begin.
From Ambience version 4.2.0, you can lock a column from being edited in the XLSX file. Perform the following steps to lock a column.
- In the Report Designer, right click Styles and select Add Style.
- Add a style called as locked. Click Finish to save the style.
Note
The style has to be named locked. Else, the locking will NOT work.
- Select the column that you want to lock and set the style as locked.
- Select the Lock cells option from the XLSX renderer options page.
- The cells are now locked.
