How to write merged cells?

[goldbergyoni]

Hi,

I'm tasked to create sheet with data where some cells are merged (have a single single value), see example below, if this is possible - will you kindly refer to the API method/keyword that elaborate on this?

Thanks,
Yoni

Example where column1 has multiple cells merged
Column1, Column2
USA NY
[Merged with row1] San-Fransisco
[Merged with row1] Atlanta

[petunsecngmail]

if this is what you want
#41

[SheetJSDev]

Set the !merges key of a worksheet to an array of ranges. It is expected to be an array of range objects. For each entry, the topleft cell will be merged into the full range. For example, the test suite round-trips merge_cells.xlsx.

This is the file:

There are 5 merges in the file. In the file, they are stored as range objects:

> require('xlsx').readFile('merge_cells.xlsx').Sheets.Merge['!merges']
[ { s: { c: 0, r: 0 }, e: { c: 1, r: 1 } },  // <-- The cell A1 represents the range A1:B2
  { s: { c: 2, r: 0 }, e: { c: 2, r: 1 } },  // <-- The cell C1 represents the range C1:C2
  { s: { c: 0, r: 2 }, e: { c: 1, r: 2 } },  // <-- The cell A3 represents the range A3:B3
  { s: { c: 3, r: 0 }, e: { c: 3, r: 1 } },  // <-- The cell D1 represents the range D1:D2
  { s: { c: 0, r: 3 }, e: { c: 1, r: 3 } } ] // <-- The cell A4 represents the range A4:B4

Here is an example from scratch:

var XLSX = require('xlsx'); 
XLSX.writeFile({
	SheetNames:["Sheet1"],
	Sheets: {
		Sheet1: {
			"!ref": "A1:B2",
			A1:{t:'s', v:"A1:A2"},
			B1:{t:'n', v:1},
			B2:{t:'b', v:true},
			"!merges":[
				{s:{r:0,c:0},e:{r:1,c:0}} /* A1:A2 */
			]
		}
	}
}, 'test.xlsx');

[fritx]

Thanks!

[reviewher]

Browser-based version of the @SheetJSDev example: https://jsfiddle.net/w8t0g8xn/

[mwangxx0129]

Thank you!

[swapna-katturi]

Hi,
This is my code.

exportToExcel(item: any): void {
const workbook = XLSX.utils.book_new();
const worksheet = XLSX.utils.aoa_to_sheet([]);
// Get table 1 content
const table1 = document.getElementById('schedule-table');
const table1Data = this.getTableData(table1);
const table1Headers = this.getTableHeaders(table1);
XLSX.utils.sheet_add_aoa(
worksheet,
table1Headers.map((row) =>
row.map((header: { label: any; colspan: any }) => [
header.label,
{ colspan: header.colspan },
])
),
{ origin: 0 }
);
XLSX.utils.sheet_add_aoa(
worksheet,
table1Data.map((row) => row.map((cell: { label: any }) => cell.label)),
{ origin: -1 }
);
// Add a blank row after table 1
const blankRow1 = [''];
XLSX.utils.sheet_add_aoa(worksheet, [blankRow1], { origin: -1 });
// Get table 2 content
const table2 = document.getElementById('soa-table');
const table2Data = this.getTableData(table2, item);
const table2Headers = this.getTableHeaders(table2, item);
console.log(table2Headers);
const mergedCells: string[] = []; // Array to store merge cell ranges
table2Headers.forEach((row, rowIndex) => {
row.forEach((header: any, colIndex: any) => {
if (header.colspan > 1) {
const startCell = XLSX.utils.encode_cell({
r: rowIndex,
c: colIndex,
});
const endCell = XLSX.utils.encode_cell({
r: rowIndex,
c: colIndex + header.colspan - 1,
});
const mergeRange = ${startCell}:${endCell};
mergedCells.push(mergeRange);
}
});
});

// Prepare the table2 headers data with merged text
const table2HeadersData = table2Headers.map((row) =>
  row.map((header: any) => ({
    label: header.label,
    colspan: header.colspan,
    value: header.label,
  }))
);
console.log(table2HeadersData);

XLSX.utils.sheet_add_aoa(worksheet, table2HeadersData, { origin: -1 });
// Apply cell merging
if (mergedCells.length > 0) {
  console.log(mergedCells);

  worksheet['!merges'] = mergedCells.map((range: string) => ({
    s: XLSX.utils.decode_range(range).s,
    e: XLSX.utils.decode_range(range).e,
  }));
}

XLSX.utils.sheet_add_aoa(
  worksheet,
  table2Data.map((row) => row.map((cell: { label: any }) => cell.label)),
  { origin: -1 }
);
// Add a blank row after table 2
const blankRow2 = [''];
XLSX.utils.sheet_add_aoa(worksheet, [blankRow2], { origin: -1 });
// Get table 3 content
if (
  this.activityFootnotes.length > 0 ||
  this.procedureFootnotes.length > 0
) {
  const table3 = document.getElementById('footnote-table');
  const table3Data = this.getTableData(table3);
  XLSX.utils.sheet_add_aoa(
    worksheet,
    table3Data.map((row) => row.map((cell: { label: any }) => cell.label)),
    { origin: -1 }
  );
}
// Add the worksheet to the workbook
XLSX.utils.book_append_sheet(workbook, worksheet, 'Tables');
// Save the workbook as an Excel file
XLSX.writeFile(workbook, 'tables.xlsx');

}

getTableData(table: any, item?: any): any[] {
const data: any[] = [];
// Extract table rows
const rows = table.getElementsByTagName('tr');
if (table.id === 'soa-table') {
item.scheduleTimelineSoA.orderOfActivities.forEach(
(eachOrderActivity: {
toggleBoolean: boolean;
toggleConcepts: boolean;
isExpanded: boolean;
definedProcedures: any[] | null;
biomedicalConcepts: any[] | null;
activityTimelineName: any;
}) => {
if (
(eachOrderActivity.definedProcedures !== null &&
eachOrderActivity.definedProcedures.length > 0) ||
(eachOrderActivity.biomedicalConcepts !== null &&
eachOrderActivity.biomedicalConcepts.length > 0) ||
(eachOrderActivity.activityTimelineName !== '' &&
eachOrderActivity.activityTimelineName !== null)
) {
eachOrderActivity.isExpanded = true;
eachOrderActivity.toggleConcepts = true;
eachOrderActivity.toggleBoolean = true;
}
this.changeDetect.detectChanges();
const row: any[] = [];
const cells = rows[data.length].getElementsByTagName('td');
for (let j = 0; j < cells.length; j++) {
row.push({
label: cells[j].innerText,
colspan: 1,
isExpanded: eachOrderActivity.isExpanded, // Add the 'expanded' property to each cell
});
}
data.push(row);
}
);
} else {
for (let i = 0; i < rows.length; i++) {
const row: any[] = [];
const cells = rows[i].getElementsByTagName('td');
for (let j = 0; j < cells.length; j++) {
const cellData = {
label: cells[j].innerText,
colspan: 1,
};
row.push(cellData);
}
data.push(row);
}
}
return data;
}

getTableHeaders(table: any, item?: any): any[] {
const headers: any[] = [];
const headerRows = table.getElementsByTagName('tr');
// Handle scenario where headers are in a single row
if (headerRows.length === 1) {
const headerCells = headerRows[0].getElementsByTagName('th');
for (let i = 0; i < headerCells.length; i++) {
headers.push({
label: headerCells[i].innerText,
colspan: 1,
});
}
} else if (table.id === 'soa-table') {
for (let i = 0; i < headerRows.length; i++) {
const headerCells = headerRows[i].getElementsByTagName('th');
let timingsLengths: number[] = [];
if (i == 0) {
timingsLengths = this.getColspan('encounterName', item);
}
let eachRowArray = [];
for (let j = 0; j < headerCells.length; j++) {
const colspan = timingsLengths[j] || 1;
const label = headerCells[j].innerText;
// const startColIndex: number = eachRowArray.length;
// const endColIndex = calculateEndColIndex(startColIndex, colspan);
eachRowArray.push({
label: label,
colspan: colspan,
// s: { r: i, c: startColIndex },
// e: { r: i, c: endColIndex },
});
// for (let k = startColIndex + 1; k <= endColIndex; k++) {
// eachRowArray.push('');
// }
}
headers.push(eachRowArray);
}
}
// Handle scenario where headers are in multiple rows
else {
const numRows = headerRows.length;
const headerCells = headerRows[numRows - 1].getElementsByTagName('th');
for (let i = 0; i < headerCells.length; i++) {
headers.push({
label: headerCells[i].innerText,
colspan: 1,
});
}
}
return headers;
}

getColspan(header: any, item: any): any {
const timingsLengths: any[] = [];
timingsLengths.push(1);
if (header === 'encounterName') {
item.scheduleTimelineSoA.SoA.forEach((content: any) => {
timingsLengths.push(content.timings ? content.timings.length : 0);
});
} else {
timingsLengths.push(1); // Push 1 for other headers
}
return timingsLengths;
}

when i am trying to export, the merged cells data is not appearing in headers. can anyone help me on this please