I am trying to generate a HTML table from a specified range of an Excel worksheet, but not the entire sheet using sheetJS

我正在尝试从Excel工作表的指定范围生成一个HTML表，但不是使用SheetJS生成整个工作表

SheetJS has the function, XLSX.utils.sheet_to_html(ws, opts), that will convert the sheet to HTML, and maintains merged cells, etc, which is great, but not sure how to limit the range converted. For example, I have a worksheet with two ranges:

SheetJS有一个函数，XLSX.utils.Sheet_to_html(ws，opts)，它可以将表格转换成HTML，并维护合并的单元格等，这很棒，但不确定如何限制转换的范围。例如，我有一个包含两个区域的工作表：

• A1:C5


• E6:G10

I want to create a HTML table from the values within the E6:G10, and ignore the A1:C5. The E6 and E7 are merged. Do I need to first move the E6:G10 range to a new worksheet, and then use sheet_to_html() to create the HTML table? If so, what's the best approach, unless there is a built-in method?

我想从E6：G10中的值创建一个HTML表，并忽略A1：C5。E6和E7被合并。我是否需要首先将E6：G10区域移动到新的工作表，然后使用Sheet_to_html()来创建HTML表？如果是这样，除非有内置的方法，否则最好的方法是什么？

Inspired by Moshen Robatjazi's comment.

灵感来自Moshen Robatjazi的评论。

SheetJS does not provide a built-in method to convert a specific range of cells to HTML. However, you can achieve this by creating a new worksheet with only the desired range and then converting that worksheet to HTML. Here's a step-by-step guide on how to do this:

SheetJS不提供将特定范围的单元格转换为HTML的内置方法。但是，您可以通过创建一个仅具有所需范围的新工作表，然后将该工作表转换为HTML来实现这一点。以下是如何做到这一点的逐步指南：

1. Read the workbook:

var workbook = XLSX.readFile('yourfile.xlsx');

2. Get the worksheet:

var worksheet = workbook.Sheets[workbook.SheetNames[0]];

3. Get the range of cells you want to convert to HTML:

var range = XLSX.utils.decode_range("E6:G10");

4. Create a new worksheet:

var new_worksheet = {};

5. Copy the desired range from the original worksheet to the new one:

for(var R = range.s.r; R <= range.e.r; ++R) {
  for(var C = range.s.c; C <= range.e.c; ++C) {
    /* Find the cell object in the original worksheet */
    var cell = worksheet[XLSX.utils.encode_cell({r:R, c:C})];
    /* Copy the cell from the original worksheet to the new worksheet */
    new_worksheet[XLSX.utils.encode_cell({r:R-range.s.r, c:C-range.s.c})] = cell;
  }
}

6. Set the range for the new worksheet:

new_worksheet['!ref'] = XLSX.utils.encode_range({s:{r:0, c:0}, e:{r:range.e.r-range.s.r, c:range.e.c-range.s.c}});

7. Convert the new worksheet to HTML:

var html_str = XLSX.utils.sheet_to_html(new_worksheet);

This will give you an HTML string of the desired range. Note that this will not maintain the merged cells. If you want to maintain the merged cells, you will need to copy the '!merges' array from the original worksheet to the new one, and adjust the ranges accordingly.

这将为您提供所需范围的HTML字符串。请注意，这不会保留合并的单元格。如果要保留合并的单元格，则需要复制“！将原始工作表中的“merge”数组添加到新工作表中，并相应地调整范围。

If you'd like to add support for merged cells within the ranges, it's relatively straightforward.

如果您想在范围内添加对合并单元格的支持，则相对简单。

To maintain the merged cells, you need to copy the '!merges' array from the original worksheet to the new one, and adjust the ranges accordingly. Here's how you can do it:

要维护合并的单元格，您需要将“！Merges”数组从原始工作表复制到新工作表中，并相应地调整范围。以下是你如何做到这一点：

1. Get the merges from the original worksheet:

var merges = worksheet['!merges'];

2. Create a new merges array for the new worksheet:

var new_merges = [];

3. Copy the merges from the original worksheet to the new one, adjusting the ranges:

for(var i = 0; i < merges.length; ++i) {
  var merge = merges[i];
  /* Check if the merge is within the desired range */
  if(merge.s.r >= range.s.r && merge.e.r <= range.e.r && merge.s.c >= range.s.c && merge.e.c <= range.e.c) {
    /* Adjust the range and add to the new merges array */
    new_merges.push({
      s: {r: merge.s.r - range.s.r, c: merge.s.c - range.s.c},
      e: {r: merge.e.r - range.s.r, c: merge.e.c - range.s.c}
    });
  }
}

4. Set the merges for the new worksheet:

new_worksheet['!merges'] = new_merges;

Now, when you convert the new worksheet to HTML, the merged cells should be maintained. Note that this will only maintain the merges that are completely within the desired range. If a merge starts or ends outside the desired range, it will not be included. This should be done prior to step 7. in the first set of instructions.

现在，当您将新工作表转换为HTML时，合并的单元格应该保持不变。请注意，这将仅保持完全在所需范围内的合并。如果合并的开始或结束超出所需范围，则不会包括该合并。这应该在第一组指令中的步骤7之前完成。