Tutorial

Excel reports

Table of contents
  1. cplace Report Generation Basics
  2. Cells
    1. Usage
    2. Supported components
    3. Styling
  3. Tables
    1. Usage
    2. Supported components
    3. Styling
  4. Configuration of worksheets

cplace Report Generation Basics

The Excel report generation takes two major ingredients:

  • a template file which contains the structure and placeholders of the workbook to generate
  • a script which transforms provided data and maps it to the template's placeholders

In the Excel reporting there are two available placeholder prefixes:

  • table
  • cell

Cells

The placeholder cell represents a single cell in an Excel worksheet.

Usage

The placeholder cell is used in the following way: {cell:placeholder}

The string "placeholder" is used to identify the replaced cell in the script: report.put('placeholder', 'Text for the cell');

A cell can contain a maximum of one placeholder and an optional additional text surrounding the placeholder.

Supported components

A cell can be replaced with components of the following types:

  • TextComponent: Components.createText().withText('Text for the cell')
  • NumberComponent: Components.createNumber().withNumber(123)
  • DateComponent: Components.createDate().withDate(new Date())

A replacement with a component works as follows:
report.put('placeholder', Components.createText().withText('Text for the cell'));

Styling

All cells in Excel reporting can be styled by applying the formatting to the cell in the template.
NumberComponents and DateComponents can only be styled with the template.
TextComponents can also be styled in the script. See: TextComponent

Apart from that the method .withConfig(config) can be used. The method gets a configuration in the following structure:

Configuration: {
  text: string,
  limit: number,
  font: {
    family: string, 
    size: string,
    style: string // bold, italic, underline, strikeThrough
  }
}

Styling of components will either be added to the existing style of the cell or overwritten. An existing style of the cell will only be overwritten by the style of the component if it is explicitly set by the script

Tables

The placeholder table represents a table in an Excel worksheet.

Usage

The placeholder table is used in the following way: {table:placeholder}

The table must be created with Components.createTable(). A table consists of multiple rows which contain a number of components. Each component fills an own column. In Excel reporting the components are added to the row by putting a component to a column index. If a column index is used more than once the last component will be used and all previous components are overwritten. The table can then be added to the report. The string "placeholder" is used to identify the replaced cell in the script.

var table = Components.createTable();

var row = table.addRow();
row.put(0, 'Column 1');
row.put(1, 'Column 2');
row.put(2, 'Column 3');

report.put('placeholder', table); 

There are two ways a table can grow. If a table has more than one column, content right of the tableplaceholder will be overwritten by columns. The default behavior for growing vertically is overwriting as well. By calling .allowGrowth() on a table growing by copying existing rows can be enabled. Then every inserted row will be created by copying the last row and replacing its content. This can lead to unintended results if there is content in the rows which does not belong to the table because the whole row will be copied. Therefore .allowGrowth() is only recommended if there is no other content in the same row as the table.

Supported components

Same as for single cells the following components can be added to a table row:

  • TextComponent: Components.createText().withText('Text for the cell')
  • NumberComponent: Components.createNumber().withNumber(123)
  • DateComponent: Components.createDate().withDate(new Date())

Note: It is not possible to create a nested table.

Styling

The styling of a cell in a table differs from the styling of a single cell. The table itself can get a surrounding border. In Excel reporting the border thickness must be between 0 and 3 where 0 results in no border and 3 in a thick border.
See: TableComponent

The defaultstyle for every cell in one row can be set on creation.
var row = table.addRow().withStyle(config);
See: TableRow

A single cell in a row is styled with the optional third parameter of row.put(). The configuration has the same structure as the default one for the rows. The styling of the added component is ignored. In the following example the first column is created with a specific style where the bottom border is blue and thin. Every other cell in the same row has a backgroundcolor, a font size of 35pt and the text is bold and underlined. If a cell style is provided only this style will be applied. It will not be merged with the default style.

var table = Components.createTable();

var defaultStyle = {
  color: [0, 155, 255],
  font: {
    size: 35,
    style: ['bold', 'underline']
  }
}

var cellStyle = {
  bottomBorder: {
    color: 'blue',
    width: 1
  }
}; 
 
var row = table.addRow().withStyle(defaultStyle);
row.put(0, 'Column 1', cellStyle);
row.put(1, 'Column 2');

report.put('placeholder', table);

Merging of cells in tables is only possible within the template. Cell merges in the template will be applied to rows generated by the report. The structure of a merged cell in a template will be copied to all cells of the resulting table.

Configuration of worksheets

In Excel it is possible to change the name and color of a worksheet tab as well as the background of a worksheet. This is done by adding a worksheet configuration for a specific worksheet by index with the function report.setWorksheetConfiguration(index: number, configuration: WorksheetConfiguration). The index starts at 1 and the configuration is an object with the following structure:

WorkSheetConfiguration: {
  name: string,
  color: [r: number, g: number, b: number] | string | number, // Backgroundcolor of the cell. Array with RGB-values, string value of a color, or combined RGB-value
  background: {
    text: string | TextContainer,
    image: WrappedDocument // cplace-image. Only used if no text is available
  }
}

TextContainer: {
  text: string,
  font: {
    family: string,
    size: number
  }
}