合并两个单元格时，Excel JS中的插入行出错

xsuvu9jc 于 2022-11-18 发布在其他

关注(0)|答案(1)|浏览(128)

我试图建立一个报告，其中页眉和页脚部分是硬编码在Excel模板文件，我试图填写表格数据是在页脚和页眉之间。

在上面的屏幕截图中，我试图填充15和16之间的数据，这可以很容易地通过Excel JS实现。但问题发生时，试图合并16和17行的新添加的单元格，它给出了下面的错误！
例如：-工作表.合并单元格（A16:A17）;//提供：-错误：无法合并已合并的单元格

错误：无法合并已合并的单元格

下面的截图是我正在尝试实现的。基本上一个单一的循环将添加两行，除了两列，我需要合并单元格所有其他单元格垂直。

下面是我写的代码。我能够在行之间插入数据并得到一个输出文件，但当试图合并单元格时，我得到同样的错误。也尝试添加空行并在插入数据到行之前合并，但它也没有工作。

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);
    });

如果我能够实现合并单元格，那么它将保存大量的时间和精力，在那里我可以使用相同的方法与其他报告不同的页眉和页脚。请帮助找出问题，或/和我做错了什么
先谢谢你了

excel

来源：https://stackoverflow.com/questions/74342739/insert-rows-in-excel-js-gives-error-while-merging-two-cells

1条答案

按热度按时间

dldeef671#

我找到了解决这个问题的方法。

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);
    });
});

赞(0）回复(0）举报 2022-11-18

我来回答

合并两个单元格时，Excel JS中的插入行出错

1条答案

相关问题

热门标签

最新问答