to_excel()

Usage

to_excel(nodes, propertiesOrView[, includeHeader = true[, localizeHeader = false [, headerLocalizationDomain[, maxCellLength = 32767 [, overflowMode = 'o']]]]])

Description
Returns a XLSX string representation of the given collection of objects.

Parameters

• nodes: A collection of objects (these objects can be database nodes or javascript objects)
• propertiesOrView: The name of a view (e.g. ui or public) or a collection of property names (e.g. merge('id', 'name') in StructrScript or ['id', 'name'] in JavaScript)
  • If the nodes parameter was a collection of javascript objects this needs to be a collection of property names
  • If the nodes parameter was a collection of database nodes, a collection of property names or a view name can be used
• includeHeader (default true): Switch indicating if a header row should be printed
• localizeHeader (default false): Switch indicating if the column names in the header should be localized
• headerLocalizationDomain: Optional header localization domain
• maxCellLength: The maximum length (affects content-rows only - the header remains untouched)
• overflowMode: Controls how content that is longer than maxCellLength is handled (affects content-rows only - the header remains untouched)
  • t: Truncates the content at maxCellLength
  • o (default): Overflows the remaining text after maxCellLength into a cell comment. (This is restricted to 32767 bytes by Excel)
  • Any other value is used as is as a cell comment. This is useful to display a message like “The content of this cell has been truncated”

Note

• The output of this function is a complete excel file, so the complete data must be contained in the nodes parameter and can not be appended later on
• This function is intended to be used in conjunction with the set_content() function or in a dynamic file
• IMPORTANT: The dynamic file should have the charset ISO-8859-1 specified in its contentType (e.g. application/octet-stream; charset=ISO-8859-1)
• The content of the header row depends on the contents of propertiesOrView and the localization configuration.
  • If a view is given, the (optionally localized) property names of that view are used as header row
  • If a collection of properties is given, these (optionally localised) property names are used as a header row
• IMPORTANT: If you are creating Excel document from a dynamic file, make sure that there are no extraneous whitespaces after the dynamic script content. This is very hard to find and Excel will warn the user that the created file is corrupt and has to be repaired!

Limitations

There are some inherent limits in the Excel file formats. Structr is creating EXCEL2007 spreadsheets. The following limitations are taken directly from the documentation:

• Maximum length of text cell contents is 32767
• Maximum length of text cell comments is 32767
• The total number of available rows is 1M (2^20)
• The total number of available columns is 16K (2^14)
• The maximum number of arguments to a function is 255
• Number of conditional format conditions on a cell is unlimited (actually limited by available memory in Excel)
• Number of cell styles is 64000

Examples

1. Create a new file “new_document.xlsx” containing the list of users.

   ${set_content(create('File', 'name', 'new_document.xlsx'), to_excel(find('User'), 'public'), 'ISO-8859-1')}
   

2. Both of the following examples would results in a downloadable file named user-export.xlsx containing the list of users.
   The scripts would need to be placed in a dynamic file with the charset charset=ISO-8859-1 appended to the content-type.

   ${(
   	set_response_header('Content-Disposition', 'attachment; filename="user-export.xlsx"'),
   	to_excel(find('User'), 'public')
   )}
   
   ${{
   	// JavaScript Example
   	Structr.setResponseHeader('Content-Disposition', 'attachment; filename="user-export.xlsx"');
   	Structr.print(Structr.toExcel(Structr.find('User'), 'public'));
   }}
   

3. The following example would result in a downloadable file named users-truncated.xlsx where all cells are truncated after 1000 characters. The parameters concerning the header row configure that a header is printed which is not localized.
   The scripts would need to be placed in a dynamic file with the charset charset=ISO-8859-1 appended to the content-type.

   ${{
   	Structr.setResponseHeader('Content-Disposition', 'attachment; filename="users-truncated.xlsx"');
   	Structr.print(Structr.toExcel(Structr.find('User'), 'excelView', true, false, '', 1000, 't'));
   }}