Hello,
A Range containing merged cells that are part of hidden rows or columns generates an HTML table where some cells are offest from their intended location. In other words, some cells appear in rows or columns that should not be part of the table.
This behavior can be seen in the latest Aspose Cells for Java version 21.6, the attached MergedCellsHiddenRowsCols.xlsx file and the following Java code:
final String xlFile = [PATH] + "MergedCellsHiddenRowsCols.xlsx";
final String newName = "-%s-v" + CellsHelper.getVersion() + ".html";
for (String name : Arrays.asList("export_hidden_cols", "export_hidden_rows")) {
final Workbook wb = new Workbook(xlFile);
final Name xlName = wb.getWorksheets().getNames().get(name);
final Range rng = xlName.getRange();
CellArea area = CellArea.createCellArea(rng.getFirstRow(), rng.getFirstColumn(),
rng.getFirstRow() + rng.getRowCount() - 1 ,
rng.getFirstColumn() + rng.getColumnCount() - 1);
wb.getWorksheets().setActiveSheetIndex(xlName.getRange().getWorksheet().getIndex());
final HtmlSaveOptions options = new HtmlSaveOptions(SaveFormat.HTML);
options.setEncoding(Encoding.getUTF8());
options.setHtmlCrossStringType(HtmlCrossType.CROSS);
options.setPresentationPreference(true);
options.setExportHiddenWorksheet(false);
options.setExportActiveWorksheetOnly(true);
options.setExportImagesAsBase64(true);
options.setCreateDirectory(false);
options.setExpImageToTempDir(false);
options.setExcludeUnusedStyles(true);
options.setExportComments(false);
options.setExportDocumentProperties(false);
options.setExportWorksheetProperties(false);
options.setExportBogusRowData(false);
options.setExportFrameScriptsAndProperties(false);
options.setValidateMergedAreas(true);
// NOTE: We are removing hidden ROWS/COLS
options.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);
options.setExportArea(area);
String htmlFile = xlFile.replace(".xlsx", String.format(newName, name));
Files.deleteIfExists(Paths.get(htmlFile));
wb.save(htmlFile, options);
System.out.println("Successfully created HTML file: " + htmlFile);
}
Key Observations:
- The export ranges in the Workbook have merged cells where the merge area spans into hidden rows or columns.
- Running the above code under Cells 21.6, should produce a file similar to the attached
MergedCellsHiddenRowsCols-export_hidden_cols-v21.6.0.htmland `MergedCellsHiddenRowsCols-export_hidden_rows-v21.6.0.html1 files. - If you render the HTML files in a Browser you should see that the HTML tables are uneven and the some cells appear in unexpected coordinates.
- This issue seems to be regressive. If you run the same code as above using Aspose Cells for Java version 19.11, the resulting tables appear as expected. See files
MergedCellsHiddenRowsCols-export_hidden_cols-v19.11.0.htmlandMergedCellsHiddenRowsCols-export_hidden_rows-v19.11.0.html.
Environment Details:
- Aspose Cells for Java 21.6
- Java version 1.8.0_211
- Windows 10 OS (but also reproducible under Linux).
File description in the MergedCellsHiddenRowsCols.zip (22.0 KB) attachment:
- MergedCellsHiddenRowsCols.xlsx: Workbook to be used by the code above.
- MergedCellsHiddenRowsCols-export_hidden_cols-v21.6.0.html: First HTML file generated after running the code above under Cells version 21.6 on our environment.
- MergedCellsHiddenRowsCols-export_hidden_rows-v21.6.0.html: Second HTML file generated after running the code above under Cells version 21.6 on our environment.
- MergedCellsHiddenRowsCols-export_hidden_cols-v19.11.0.html: First HTML file generated after running the code above under Cells version 19.11 on our environment.
- MergedCellsHiddenRowsCols-export_hidden_rows-v19.11.0.html: Second HTML file generated after running the code above under Cells version 19.11 on our environment.
Thank you!