How merge cell dynamically in excel file?
-
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?
-
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 the
mergeCells
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}"/>` }
-
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; }