Spreadsheet Export (XLS, XLSX, ODS)reportingdesignerhelpdesk

Spreadsheet export produces Microsoft Excel (XLS, XLSX) or Open Document Spreadsheet (ODS) files. The following options can be set in the export settings dialog or via the report URL parameters.

The dialog is grouped into Layout and Outline.

Under Layout:

• Truncating Behavior: Specifies whether strings which are too long for their cell will be truncated or not. The default can be set in the Configuration.
  • Values:
    • Yes
    • No
    • Default - use server configuration
  • Default value: Default

• Cell Distribution: How text is placed when it does not fit into a single simple cell. The options are described in the Cell Distribution section below; the table there outlines behavioral differences when the text does not fit.
  • Values:
    • Static Layout
    • One Line
    • Line Break
    • Multi-cells
    • Merge Cells
    • Single Cell
    • Merge All Cells
    • Default
  • Default value: Default

• New sheet per top-level group: Specifies whether a new sheet will be created for every instance of the top-most group or not.
  • Default value: No

Under Outline:

• Number of group levels: Specifies how many group levels are to be shown in the outline of the spreadsheet. A number between 0 and 7 is valid.
  • Default value: 0

The Cell Distribution determines how to handle text when it does not fit into a single simple cell. The options are described below. The table at the end of this section outlines behavioral differences that occur when the text does not fit.

• Static Layout - the text is displayed in one cell. Line breaks within the text will lead to new cell in the row below. The Can Grow option is being ignored. Cells will be merged to adhere to the design: e.g. if a text is designed twice the size of the regular cell, the resulting cell will be a merged compound to keep the designed size. The cell will be merged into horizontal as well as vertical direction. If the cell is too small to keep the text, the text will overflow. Adornments, meaning the style of the text cell, will be assigned seamlessly.
• One Line - analogue to Static Layout, except the merging will only be done horizontally over columns.
• Line Break - analogue to One Line but also automatically wraps lines that are too long and would otherwise grow out of the cell. If the design does not allow the full text to be displayed, it will be clipped.
• Multiple Cells - the text is split into multiple spreadsheet cells. The number of required rows equals the number of lines in the text. Cell styles will be applied seamlessly. Horizontal cells will be merged, analogue to One Line. Can Grow is supported for cells that have been designed too narrow.
• Merge Cells - optimum setting to represent the design as closely as possible. Cells will be merged if designed larger than the grid. Line breaks result in new cells, Can Grow is supported and text lines that are too long will be wrapped to fit the design.
• Merge All Cells - same as Merge Cells without creating a new cell when there is a line break inside the data.
• Single Cell - optimum setting for data representation. Text is always represented in a single row, except for line breaks which lead to a new cell in the row below.

The following matrix provides an overview of the settings:

The default value can be set in the Configuration.