Producing excel file using Custom headers

Dear Forum members,

I am creating a dataset for which i have headers within the dataset, I want to add additional two rows as additional header information on top of the dataset headers and produce an excel file.

Example:
Dataset contains the below:
image

Header files will have the below:
image

Output:

I will attach an excel example just to present what is needed.

Kind regards,
Sample_Query.xlsx (10.9 KB)

Hi,

You can achieve the desired Excel output format with two custom header rows above your dataset headers and the formatting using the template file option in the SpreadsheetDataWriter component.

While using template files can be limited, this specific use case can be handled using two template files along with two SpreadsheetDataWriter components.

In our example, we’ll be reading both the dataset and the header rows from the file you provided, and writing them to a new output Excel file using the approach described below.

Here’s a step-by-step guide to set it up:

1. Prepare the Template Files

Create two separate Excel template files.

  • Template A: Contains the formatting of your dataset.

  • Template B: Contains the formatting of the custom header rows.

2. Prepare the Header Rows

In your CloverDX graph, prepare/read the two additional header rows you want to appear above the dataset.

3. Write the Dataset Using Template File A

Use the first SpreadsheetDataWriter component to write the dataset using Template A. To do so, set the Template File URL property to point to your template file, select the template file’s sheet in the Sheet property, and set the writing mode to Insert into sheet. Additionally, set the Write header property in the component’s Mapping to true so the dataset headers are included.

4. Write the Header Rows Using Template File B

Use the second SpreadsheetDataWriter to write the two custom header rows using Template B. Similarly, to the first SpreadsheetDataWriter component, set the Template File URL property to point to your template file, select the template file’s sheet in the Sheet property, and set the writing mode to Insert into sheet. However, this time, set the Write header to false and Data offset to 0 for mapped cells.

Kindly see the attached project for further details:
example_template_spreadsheets.zip (34.2 KB)

Kind regards,
Ladislav

Thank you very much, really appreciated, I will try.