Inconsistency in the HTML output of an Excel sheet's range

[Neha_Gautam]

Hello,

I am using Aspose.Cells v8.7.1.4. There is inconsistency in the HTML output of an Excel sheet’s range.

Please refer to the attached Excel file.

Output of Single row, multiple columns:

Let range is ‘Sheet1’!A1:K1, Cells from A1 to E1 have some value, Cells from F1 to K1 are empty. When taking HTML output, it contains Cells from A1 to E1 only (because rest of the cells are empty).

Output of Single column, multiple rows:

Let range is ‘Sheet1’!A1:A10, Cells from A1 to A7 have some value, Cells from A8 to A10 are empty. When taking HTML output, it contains all Cells from A1 to A10. This is not consistent with “Single row, multiple columns” behavior. Expected Output: HTML output should contain cells from A1 to A7 only, because cells from A8 to A10 are empty. Behavior across rows and columns must be consistent.

Currently, when taking HTML output of a range, empty columns are automatically excluded but empty rows are not excluded. If range is ‘Sheet1’!A1:K10 the output will actually contain cells from A1 to E10. Expected output is cells from A1 to E7.

Please find attached the Excel file, HTML output of A1:K1, output of A1:A10 and output of A1:K10, and the sample code used to generate the HTML output.

Can you please analyse this issue?

Thanks,
Neha

[Amjad_Sahi]

Hi,

Thanks for the template files and details.

After an initial test, I observed the issue as you mentioned by using your sample code with your template file. I found an inconsistency in the output HTML of an Excel sheet’s range in Excel to HTML rendering. I confirmed your findings by checking the tag when viewing the source code of the generated HTML as below:
1) Output of Single row, multiple columns:
Let range is ‘Sheet1’!A1:K1, Cells from A1 to E1 have some value, Cells from F1 to K1 are empty. When taking HTML output, it contains Cells from A1 to E1 only (because rest of the cells are empty).

2) Output of Single column, multiple rows:
Let range is ‘Sheet1’!A1:A10, Cells from A1 to A7 have some value, Cells from A8 to A10 are empty. When taking HTML output, it contains all Cells from A1 to A10. This is not consistent with “Single row, multiple columns” behavior. Expected Output: HTML output should contain cells from A1 to A7 only, because cells from A8 to A10 are empty. Behavior across rows and columns must be consistent.
etc.
…
e.g
Sample code:

//String dynamicRange = “‘Sheet1’!A1:K1”;
String dynamicRange = “‘Sheet1’!A1:A10”;
//String dynamicRange = “‘Sheet1’!A1:K10”;
String worksheetName = “Sheet1”;

Workbook workbook = new Workbook(“R1C1Pattern_Test.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(worksheetName);

int rangeIndex = workbook.getWorksheets().getNames().add(“dummyrange”);
Name name = workbook.getWorksheets().getNames().get(rangeIndex);
name.setRefersTo(dynamicRange );
name = workbook.getWorksheets().getNames().get(rangeIndex);
Range sourceRange = name.getRange();

Workbook newWorkbook = new Workbook(FileFormatType.XLSX);
WorksheetCollection targetWsc = newWorkbook.getWorksheets();
Worksheet targetWs = (Worksheet)targetWsc.get(0);

Range targetRange = targetWs.getCells().createRange(0, 0, sourceRange.getRowCount(),
sourceRange.getColumnCount());

PasteOptions options = new PasteOptions();

//Copy column widths
options.setPasteType(PasteType.COLUMN_WIDTHS);
targetRange.copy(sourceRange, options);

// Copy row heights
int rowCount = sourceRange.getRowCount();
int firstRow = sourceRange.getFirstRow();

for (int i = 0; i < rowCount; i++) {
double rowHeight = sourceRange.getWorksheet().getCells().getRowHeight(firstRow++);
targetWs.getCells().setRowHeight(i, rowHeight);
}

//Copy everything else
options.setPasteType(PasteType.ALL);

targetRange.copy(sourceRange, options);

HtmlSaveOptions htmlSaveOptions = new HtmlSaveOptions(SaveFormat.HTML);
htmlSaveOptions.setHiddenColDisplayType(1);
htmlSaveOptions.setHiddenRowDisplayType(1);
htmlSaveOptions.setParseHtmlTagInCell(true);
htmlSaveOptions.setExportBogusRowData(false);

//newWorkbook.save(“f:\files\out1A1K1.html”, htmlSaveOptions);
newWorkbook.save(“f:\files\out1A1A10.html”, htmlSaveOptions);
//newWorkbook.save(“A1K10.html”, htmlSaveOptions);

I have logged a ticket with an id “CELLSJAVA-41793” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.