Available in Community Designer

Cells in the preview area highlighted in various colours to identify whether and how they are mapped.

Data offsets determines the position where data is written relative to the leading cell position.

Basically, its value represents 'a number of rows (in vertical mode) or columns (in horizontal mode) to be skipped before the first record is written (relative to the leading cell)'.

Data offset 0 does not skip anything and data is written right at the leading cell position (Write header option does not work for this setting).

Data offset 1 is typically used when header is to be written at the leading cell position – so you need to shift the actual data by one row down (or column to the right).

Click arrow buttons in the Data offsets (global) control to adjust data offsets for the whole spreadsheet.

Additionally, you can use the spinner in Properties → Selected cells → Data offset of each leading cell (orange) to adjust data offset locally, i.e. for a particular column only. Notice how modifying data offset is visualised in the sheet preview – the 'omitted' rows change colour. By following dashed cells, which appear when you click a leading cell, you can quickly check where your record will be written.

	Tip
	The arrow buttons in Data offsets (global) only shift the data offset property of each cell either up or down. So mixed offsets are retained, just shifted as desired. To set all data offsets to a single value, enter the value into the number field of Data offsets (global). Note that if there are some mixed offsets, the value is displayed in gray.

Figure 54.19. The difference between global data offsets set to 1 (default) and 3. In the right hand figure, writing would start at row 4 with no data written to rows 2 and 3.

Figure 54.20. Global data offsets is set to 1. In the last column, it is locally changed to 4. In the output file, the initial rows of this column would be blank, data would start at D5.

Rows per record is a Global property specifying a gap between rows. Default value is 1 (i.e. there is no gap). Useful when mapping multiple cells above each other (for a single record) or when you need to print blank rows in between your data. Best imagined if you look at the figure below:

Figure 54.21.  With Rows per record set to 2 in leading cells Name and Address, the component always writes one data row, skips one and then writes again. This way various data does not get mixed (overwritten by the other one). For a successful output, make sure Data offsets is set to 2.

Combination of Data offsets (global and local) and Rows per record – you can put the settings described in preceding bullet points together. See example:

Figure 54.22. Rows per record is set to 3. Data in the first and third column will start in their first row (because of their data offsets being 1). The second and fourth columns have data offsets 2 and 4, respectively. The output will, thus, be formed by 'zig-zagged' cells (the dashed ones – follow them to make sure you understand this concept clearly).

Max number of records is a Global property which you can specify via component attributes, too (see SpreadsheetDataWriter Attributes). If you reduce it, you will notice the number of dashed cells in the spreadsheet preview reduces as well (highlighting only the cells which will be written out in fact).

In a spreadsheet, every single cell can have its own format (in Excel, right-click on a cell -> Format cells; Number tab). This format is represented by a format string (not Clover format string, but Excel-specific format string). Since format in Clover is defined globally for a field in metadata, not per record, writing formats to Excel can be tricky. SpreadsheetDataWriter offers two ways of writing Excel-specific format to cells:

	Note
	This unleashes the full power of Excel where formats are set per-cell rather than per-column. You pass the format in the data as an extra 'string' value. Remember, the format is specified in Excel terms, not Clover. Use Field with format in Properties → Selected cells of the leading (orange) cell to specify the input field containing the format (string).

Which format is used if both are set?

You can use excel:General format – either in Field with format or in metadata Format – the output will be set to general format (Excel terms).

Note

When Excel format is specified in Metadata → Format it MUST be prefixed by excel: so that Clover can know that the format string is specific to Excel-only use. Example: "excel:0.000%" When Excel format is passed in data, as the aforementioned fieldFormat, it MUST NOT be prefixed in any way. Example: "0.000%" Note that the excel: format string matters when reading the output back with spreadsheet readers - SpreadsheetDataReader. Common readers (such as FlatFileReader) completely ignore excel:. They consider it an empty format string.

SpreadsheetDataWriter lets you write hyperlinks into particular cells. Each hyperlink is defined using two input fields. One input field defines text of the link, other field defines target of the link.

Links can be of several types: Document, E-mail, File or URL.

Link is created in Properties pane. Map the field with link text to desired cell, change Hyperlink type (in Properties) to desired type and select field with target in Field with hyperlink address.

Hyperlinks are persisted to file along with font and style (blue and underline).

Sometimes you may want to prepare in advance a nicely formatted template in Excel, maybe including some static headers, footer, etc. and use Clover to just fill in the data for you. And it might be that you will want to reuse the template without overwriting it.

This is where SpreadsheetDataWriter template feature comes in handy. The component can take a previously designed template Excel file (see Template File URL in SpreadsheetDataWriter Attributes ), make a copy of it into the designated output file (see File URL) and write data to it, retaining the rest of the template.

A template can be any Excel file, usually containing three sections: the header, one template row for data and the rest as the footer.

Figure 54.23. Writing into a template. Its original content will not be affected, your data will be written into Name, Surname and Age fields.

Notice the template row. It is a row like any other but in the mapping editor, it is designated as the first row of mapped data. The component duplicates that row each time it writes a new data. This way you can assign arbitrary formatting, colors etc. on this data row and it is applied to all written rows.

The template file is not changed or affected in any other way.

Important

There is only one reasonable setting when using templates, although all other modes work as expected (they do not, however, produce results that you would want). The settings are: Sheet – select the sheet from the template (by number or name, do not create new sheet) Mapping – this is one of the cases where Map by name makes sense. Use the header of the template where applicable. Of course, you can map fields as usual. Write mode – Insert Actions on existing sheets – Do nothing, keep existing sheets and data

You can use the component to write into forms without affecting its original boxes. Use these settings:

Send just one input record to the component's input containing all the form values. Set File URL to the form file to be filled. Then map the input fields explicitly one by one into corresponding form cells using the preview sheet.

Next, use these settings:

If you use Insert, Append or Overwrite modes, formulas and charts that work with the data areas written in Clover will be properly updated when viewed in Excel.

	Note
	Generating formulas, charts or other Excel objects is not currently supported.

You can use multiple sequential writes into a single sheet to produce complex patterns. To do so, set up multiple SpreadsheetDataWriter components writing the same file/sheet and feed them various inputs.

	Important
	Do not forget to put multiple components writing to the same file into different phases. Otherwise the graph will fail on write conflict.

Typically, you will use the Overwrite in sheet (in-memory) write mode for all components in the sequence.

A neat technique is partitioning into individual sheets according to values of a specified key field (or more fields). Thus you can e.g. write data for different countries into different sheets. Simply choose Country as the partitioning key. This is done by editing the Sheet attribute; switch to Partition data into sheets by data fields and select a field (or more fields using Ctrl+click or Shift+click ).

Figure 54.24. Partitioning by one data field

You can partition according to more than one field. In that case, output sheet names will be a compound of field names you have selected. Example: You have customer orders stored in one CSV file. You would like to separate them into sheets according to e.g. a name of the shop and a city. Use SpreadsheetDataWriter in create new file mode while partitioning according to the two fields. It will produce sheets like:

Pete's Grocery,New York

Hoboken Deli,New Jersey

Al's Hardware,New York

etc., each of them containing data just for one shop.

See also Partitioning Output into Different Output Files.

Although Excel format is not primarily designed for big data loads, its processing can easily grow to enormous memory requirements.

The format itself has some limitations:

Tip

Working with larger spreadsheets is memory consuming and although the component does its best to optimize its memory footprint, bear these few tips in mind: When mapping in the Spreadsheet mapping editor, memory consumption for the Designer might temporarily ramp up over a gigabyte of memory – so be sure to set enough heap space for the Designer itself (see Main Tab in Run Configuration). Memory consumption is affected by how Excel organizes the file internally so two files with the same amount of data in it can have significantly different memory requirements. Use streaming mode whenever possible. It is several times faster than other write modes. Switch to DEBUG mode in graph's Run Configurations to detect whether streaming mode is on or off. To learn how to do that, see Main Tab in Run Configuration. When using Overwrite sheet (streaming - XLSX only) write mode the entire spreadsheet file still needs to be loaded before writing which can cause major memory requirements. Make sure to set enough heap space for CloverETL runtime if you are working with larger spreadsheet files (see Chapter 14, Runtime Configuration).

Usually you would use the Create new file (streaming – XLSX only) and Overwrite sheet (streaming - XLSX only) write modes. Other write modes do not support streaming.

In complex mappings with many metadata fields, you might want to check if everything has been mapped properly. Whenever during your work in Spreadsheet Mapping Editor, switch to the Summary tab and observe an overview of leading cells and mappings like this one:

Figure 54.25. Mapping summary