How merge cell dynamically in excel file?

Okh-Petryk P'yatochkin

I am using xlsx recipe for xlsx file generation. I use #each helper to add rows.

{{#each transactions}}
  {{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
     <row><c t="inlineStr"><is><t>{{this}}</t></is></c></row>
  {{/xlsxAdd}}
{{/each}}
{{#xlsxAdd}}

I need to merge cells inside this row. As I see in the example i can do it with xlsxReplace helper:

{{#xlsxReplace "xl/worksheets/sheet1.xml" "worksheet.mergeCells"}}
    <mergeCells>
        <mergeCell ref="A1:A4"/>
        <mergeCell ref="B1:B3"/>
    </mergeCells>
{{/xlsxReplace}}

I need do merging dynamically and I do not know exactly in which rows i should do it.
My question is: How I can do cell merging if this cells generate dynamically?

I can try to implement it through a helper. Collect rows counter and add merge section in the end. But it looks overwhelming. Maybe exists some "legal" method to do it?

jan_blaha

I can try to implement it through a helper. Collect rows counter and add merge section in the end.

Yes, I would add a helper that will generate themergeCells node dynamically. Maybe something like this

{{#xlsxReplace "xl/worksheets/sheet1.xml" "worksheet.mergeCells"}}
    <mergeCells>
        {{{mergeCells @root}}}
    </mergeCells>
{{/xlsxReplace}}

function mergeCells(data) {
  // some calculations
  return `<mergeCell ref="${myStart}:${myEnd}"/>`
}

Okh-Petryk P'yatochkin

My solution:

Template:

{{#each data}}
  {{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
    {{rowCounter}}
    {{#if this.rowShouldTobeMarged}}
      {{rowsForMerging 'mergingType1'}}
    {{/if}}
    <row>...</row>
  {{/xlsxAdd}}
{{/each}}

{{#xlsxReplace "xl/worksheets/sheet1.xml" "worksheet.mergeCells"}}
  <mergeCells>
    {{#mergeRows}}
      <mergeCell ref="{{this}}"/>
    {{/mergeRows}}
  </mergeCells>
{{/xlsxReplace}}

{{{xlsxPrint}}}

Helpers:

function rowCounter(options) {
    if (!options.data.root.rowCounter) { options.data.root.rowCounter = 0; }
    options.data.root.rowCounter = options.data.root.rowCounter + 1;
}
function rowsForMerging(type, options) {
    if (!options.data.root.rowsForMerging) { options.data.root.rowsForMerging = []; }
    options.data.root.rowsForMerging = [
        ...options.data.root.rowsForMerging,
        { type, row: options.data.root.rowCounter }
    ];
}
function mergeRows(options){
    returnData = "";
    const rows = options.data.root.rowsForMerge;
    for (let i = 0; i < rows.length; i++) {
        const { row, type } = rows[i];
        if (type === 'mergingType1') {
            returnData = returnData + options.fn(`B${row}:K${row}`);
        }
       // ....
    }
    return returnData;
}