Search code examples
node.jsexcelreportingkoaexceljs

Insert rows in Excel JS gives error while merging two cells

I am trying to build a report where header and footer part is hard Coded in excel template file and I am trying to fill table data which is in between footer and header.

enter image description here

In Above screen shot I am trying to fill data between 15 and 16, which can be easily achieved by Excel JS. But the issue happens when trying to merge cells of 16 and 17 rows of newly added it gives below error!

Eg:- worksheet.mergeCells(A16:A17); // Gives:- Error: Cannot merge already merged cells

Error: Cannot merge already merged cells

The below screenshot is something I am trying to achieve. Basically a single loop will add two rows and except two columns, I need to merge cells all other cells vertically. enter image description here

Below is the code I have written. I am able to insert data between rows and get a output file, but when trying to merge cells then I am getting same error. Also tried adding empty row and merging before inserting data into row, but it also didn't workout.

const filepath = path.join(__dirname, "./excel/template.xlsx");
const workbook = new Excel.Workbook();
workbook.xlsx.readFile(filepath).then(function () {
var worksheet = workbook.getWorksheet("Sheet1");

let row = 16;
let count = 1;
if (fitupData.length) {
    var borderStyles = {
    top: { style: "thin" },
    left: { style: "thin" },
    bottom: { style: "thin" },
    right: { style: "thin" },
    };

    fitupData.forEach((item) => {
        let firstRow = worksheet.insertRow(row, []);
        let secondRow = worksheet.insertRow(row, []);

        firstRow.border = borderStyles;
        firstRow.alignment = { horizontal: "center" };


        worksheet.getCell(`J${row}`).value = item.A2;
        worksheet.getCell(`K${row}`).value = item.B2;

        secondRow.values = [
            count,
            item.v1,
            item.v2,
            item.v3,
            item.v4,
            item.v5,
            item.v6,
            item.v7,
            item.v8,
            item.A1,
            item.B1,
            "Accepted",
            "-",
            item.v11,
            "-",
            "-",
            item.v12,
        ];


        // Merging cells dynamically
        for (let i = 65; i <= 81; i++) {
            //A to J
            let col = String.fromCharCode(i);
            if (["J", "K"].includes(col)) continue;
            worksheet.mergeCells(`${col}${row}:${col}${row + 1}`);
        }

        row = row + 2;
        count++;
    });
}

const fileName = path.join(__dirname, "./excel/Report.xlsx");
workbook.xlsx
    .writeFile(fileName)
    .then(() => {
    resolve(fileName);
    })
    .catch((err) => {
    console.log(err);
    });

If I am able to achieve merging cells, then it will save lot of time ands effort, where I can use same approach for other reports with different header and footers. Please help figure out the problem, or/and what I am doing wrong

Thank you in advance

Solution

  • I found a work around for the issue.

    const filepath = path.join(__dirname, "./excel/template.xlsx");
const workbook = new Excel.Workbook();

let exportWorkbook = new Excel.Workbook();

workbook.xlsx.readFile(filepath).then(function () {
  /** Get Sheet1 from selected template */
  let worksheet = workbook.getWorksheet("Sheet1");

  /** Create new sheet Report */
  let exportSheet = exportWorkbook.addWorksheet("Report", {
    views: [{ showGridLines: false }],
  });

  /**Get header rows from template*/
  const header = worksheet.getRows(1, 15);
  /**Get Footer rows from template */
  const footer = worksheet.getRows(16, 8);

  /** Add Headers from Template to New WorkSheet */
  for (let index = 0; index < header.length; index++) {
    const element = header[index];
    let r = exportSheet.addRow([]);
    Object.assign(r, element);
  }

  let currentRow = 14;
  if (dataLength > 0) {
    for (let index = 0; index < dataLength; index++) {
      const item = fitupData[index];
      currentRow += 2;

      /** Add first row */
      exportSheet.addRow(
        [
          index + 1,
          item.V1,
          item.V2 || "D",
          item.V3 || "LN",
          item.V4 || "LC",
          item.V5 || "SP",
          item.V6 || "JN",
          item.V7 || "TK",
          item.V8 || "JT",
          item.A2 || "MG",
          item.A1 || "HN",
          "Acce",
          "-",
          item.V9 || "WPS",
          "-",
          "-",
          item.V10 || "TD",
        ],
        "i"
      );

      /** Add second row */
      exportSheet.addRow(
        [
          "",
          "",
          "",
          "",
          "",
          "",
          "",
          "",
          "",
          item.A1 || "",
          item.A2 || "",
          "",
          "",
          "",
          "",
          "",
          "",
        ],
        "i"
      );

      /** Merge rows */
      exportSheet.mergeCells(`A${currentRow}:A${currentRow + 1}`);
      exportSheet.mergeCells(`B${currentRow}:B${currentRow + 1}`);
      exportSheet.mergeCells(`C${currentRow}:C${currentRow + 1}`);
      exportSheet.mergeCells(`D${currentRow}:D${currentRow + 1}`);
      exportSheet.mergeCells(`E${currentRow}:E${currentRow + 1}`);
      exportSheet.mergeCells(`F${currentRow}:F${currentRow + 1}`);
      exportSheet.mergeCells(`G${currentRow}:G${currentRow + 1}`);
      exportSheet.mergeCells(`H${currentRow}:H${currentRow + 1}`);
      exportSheet.mergeCells(`I${currentRow}:I${currentRow + 1}`);
      exportSheet.mergeCells(`L${currentRow}:L${currentRow + 1}`);
      exportSheet.mergeCells(`M${currentRow}:M${currentRow + 1}`);
      exportSheet.mergeCells(`N${currentRow}:N${currentRow + 1}`);
      exportSheet.mergeCells(`O${currentRow}:O${currentRow + 1}`);
      exportSheet.mergeCells(`P${currentRow}:P${currentRow + 1}`);
      exportSheet.mergeCells(`Q${currentRow}:Q${currentRow + 1}`);
    }
  }

  // Get all merge ranges
  const merges = worksheet.model.merges;

  // Fix for the solution
  for (let index = 0; index < footer.length; index++) {
    const footerRow = footer[index];
    const currentRowNum = footerRow.number;
    let newRow = exportSheet.addRow(footerRow.values, "i+");
    const newRowNum = newRow.number;

    //Apply Style
    footerRow.eachCell((cell, num) => {
      const newCell = newRow.getCell(num);
      newCell.style = cell.style;
    });

    // Get Merge Values of footer row
    const rowMerges = merges.filter((range) =>
      range.match(`\\w+${currentRowNum}:\\w+${currentRowNum}`)
    );

    // Apply merge range
    rowMerges
      .map((range) =>
        range.replace(new RegExp(`${currentRowNum}`, "g"), `${newRowNum}`)
      )
      .forEach((range) => exportSheet.mergeCells(range));
  }

  const fileName = path.join(__dirname, "./excel/Fitup-Report.xlsx");
  const pdfOutPut = path.join(__dirname, "./excel/Fitup-Report.pdf");
  /** Create Excel as per the WorkSheet data above */
  exportWorkbook.xlsx
    .writeFile(fileName)
    .then(async () => {
      await convert(fileName, pdfOutPut);
      resolve(pdfOutPut);
    })
    .catch((err) => {
      console.error(err);
      reject(err);
    });
});