Can you set the mergedCells property in Kendo UI for jQuery ooxml.Workbook.sheets without range name?

Question

I am having an issue merging cells that are in an existing sheet in a workbook. My attempt to use mergedCells after the sheet is created has presented a roadblock, I can't seem to get the range name in string format as documented for the mergedCells option in Workbook.sheet:

mergedCells: ["A6:A8"]

All I have are indexes of the cells.

I have the logic to determine which cells' indexes I want to merge, but only the indexes. I can't figure out a way to get the range in an Excel cell name/string like "A1". In the following example, ignoring how I got the index values, I can't merge the cells based on index where indexA is the column and indexB1 and indexB2 are rows.

let indexA = 0;

let indexB1 = 5;

let indexB2 = 7;

sheet.mergedCells = [[{indexA, indexB1},{indexA,indexB2}]]

I have attached a picture of the types of cells I want to merge that are in my workbook, my loop first hits A6 through A8 and determines the indexes are to be merged because all the cells' values equal "Boat". Any help is appreciated, and I will try to provide more detail if needed, but it is difficult to provide much more because of the nature of the system I am on.

Thanks in advance.format of workbook

Answer 1

So you have the R1C1 notation of the cells you need to merge? If so, following the suggestion in this thread you can generate the range in A1 notation - example:

function columnName(index) {
    var cname = String.fromCharCode(65 + ((index - 1) % 26));
    if (index > 26)
      cname = String.fromCharCode(64 + (index - 1) / 26) + cname;
    return cname;
  }

  var r1 = 2;
  var c1 = 3;
  var r2 = 4;
  var c2 = 5;

  var rangeAsString = `${columnName(c1)}${r1}:${columnName(c2)}${r2}`;

  var workbook = new kendo.ooxml.Workbook({
    sheets: [{
      mergedCells: [rangeAsString],
      rows: [
        { cells: [ { value: "Document Title" }  ] },
        { cells: [ { value: 22 }, { value: 33 }, { value: 44 }, {value: 55}  ] }
      ]
    }]
  });

  kendo.saveAs({
    dataURI: workbook.toDataURL(),
    fileName: "Test.xlsx"
  });