Border style does't work on merged cell

[Lilian1331]

Hi all,

I am working on excel that with multiple of merged cell. However, when I work on style, the border property seems not working on the merged cell. It works fine for my other single cell. Below is my code: worksheet.A1 = {t:'s',v:"Employee"}; worksheet["A1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } ,border: { top: { style: 'medium', color: { rgb: "FFFFAA00"}}, left: { style: 'medium', color: { rgb: "FFFFAA00"}}}}; let wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, worksheet, "Sheet Title"); const excelBuffer: any = XLSXStyle.write(wb, { bookType: 'xlsx', type: 'buffer' });

Anyone can help. Thanks!

[RimaCiklum]

For the border to be applied on all the merged cells, you would have to change the border style of every merged cell separately. One issue that I noticed is that for the top left cell, the left and top border has to have style:thick for the border to be visible. Hope the following code helps: worksheet["A1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } },
border: { top: { style: 'thick', color: { rgb: "FFFFAA00" } }, left: { style: 'thick', color: { rgb: "FFFFAA00" } }, bottom: { style: 'medium', color: { rgb: "FFFFAA00" } } } };

worksheet["B1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, border: { top: { style: 'thick', color: { rgb: "FFFFAA00" } }, right: { style: 'medium', color: { rgb: "FFFFAA00" } }, bottom: { style: 'medium', color: { rgb: "FFFFAA00" } } } };

[ngocnguyen2amela]

In my worksheet not has B1, (column after first merge cells) :(

For the border to be applied on all the merged cells, you would have to change the border style of every merged cell separately. One issue that I noticed is that for the top left cell, the left and top border has to have style:thick for the border to be visible. Hope the following code helps: worksheet["A1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, border: { top: { style: 'thick', color: { rgb: "FFFFAA00" } }, left: { style: 'thick', color: { rgb: "FFFFAA00" } }, bottom: { style: 'medium', color: { rgb: "FFFFAA00" } } } };

worksheet["B1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, border: { top: { style: 'thick', color: { rgb: "FFFFAA00" } }, right: { style: 'medium', color: { rgb: "FFFFAA00" } }, bottom: { style: 'medium', color: { rgb: "FFFFAA00" } } } };

[pietersv]

The tricky part with this library is you have to explicitly write borders for all the cells in the merged area -- top borders for top cells, right borders for right cells, both for the top-right cell, etc.

Excel doesn't seem to think about merged cells as a single unitary combined cell, it sees them as a bunch of individual cells and just draws the first cell bigger. I think it should be possible to add a feature to automate that.

[ajith-1628]

no this is not working only worksheet[a1] works . from the next step the excel doesn't exports

[pietersv]

@ajith-1628 Do you have a small example code that reproduces the issue? I no longer support this branch of the js-xlsx library but have been able to draw borders around merged cells.

[ajith-1628]

header = [{ "":"Table Data2", }]

excelExport(): void{

let tabledata = this.items
const sheetname = 'sheet1';

const sheetdata = tabledata

//Had to create a new workbook and then add the header const wb = XLSX.utils.book_new(); const ws = XLSX.utils.json_to_sheet(this.header);

const mergeCell = { s: { r: 1, c: 0 }, e: { r: 2, c: 6 } }; ws['!merges'] = [mergeCell]; const ws = XLSX.utils.json_to_sheet(this.header);

for (var i in ws) { // console.log(ws[i]); if (typeof ws[i] != 'object') continue; let cell = XLSX.utils.decode_cell(i);

 ws["A1"].s =

{ font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, border: { top: { style: 'thick', color: { rgb: "FFFFAA00" } }, left: { style: 'thick', color: { rgb: "FFFFAA00" } }, bottom: { style: 'medium', color: { rgb: "FFFFAA00" } } } };

ws["B1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, border: { top: { style: 'thick', color: { rgb: "FFFFAA00" } }, right: { style: 'medium', color: { rgb: "FFFFAA00" } }, bottom: { style: 'medium', color: { rgb: "FFFFAA00" } } } };

if (cell.r >= 4){
  ws[i].s = {
    font: {
      name: 'Calibri Light',
      italic: true,
      bold:false,

    },
    alignment: {
      vertical: 'center',
      horizontal: 'center', 
    },
    border: {
      right: {style: 'thin'},
      left: {style: 'thin'}, 
      top : {style: 'thin'},
      bottom: {style: 'thin'},
    },
  }
}
here if I do ws b1 the excel doesn't exports the file.