Excel to HTML in Java - conditional formatting is lost

Hello,

It seems that conditional formats are omitted when a workbook is saved in HTML format. This can be reproduced by using the attached “ConditionalFmt.xlsx” workbook and the following code:

try {
    final String xlFile = "[PATH]\\ConditionalFmt.xlsx";
    final String sheetName = "Sheet1";

    Workbook wb = new Workbook(xlFile);
    Worksheet sheet = wb.getWorksheets().get(sheetName);
    wb.getWorksheets().setActiveSheetIndex(sheet.getIndex());

    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.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
    options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);
    //merge the areas of conditional formatting and validation??
    options.setMergeAreas(true);

    // Save as HTML
    String htmlFile = xlFile.replace(".xlsx", "-" + sheetName + ".html");
    Files.deleteIfExists(Paths.get(htmlFile));
    wb.save(htmlFile, options);

    System.out.println("Saved file: " + htmlFile);

} catch (Exception ex) {
    System.out.println("Unexpected EXCEPTION: "  + ex.getMessage());
    ex.printStackTrace();
}

Additional Notes:

  • Attachment: ConditionalFmt.zip (76.3 KB)
  • The attached workbook has two conditional formats on cells H4:H28, which put either a green or red border on cells that match a simple criteria.
  • Using Aspose Cells version 18.3, the output file “ConditionalFmt-Sheet1.html” was produced which does not show the expected conditional formats as shown in the image “ConditionalFmt.png”.
  • Exporting the workbook to PDF retains the conditional formats. However, we need the conditional formats to be applied to HTML output.

Thanks.

@oraspose

Thanks for using Aspose APIs.

We were able to observe this issue as per your description and logged it in our database for investigation and resolution. Once, the issue is fixed or we have some other news for you, we will update you asap.

This issue has been logged as

  • CELLSJAVA-42578 - Conditional formatting is lost while saving Excel to HTML

Java

Workbook wb = new Workbook(dirPath + "ConditionalFmt.xlsx");
wb.calculateFormula();

wb.save(dirPath + "output.html");

@oraspose,

This is to inform you that we have fixed your issue CELLSJAVA-42578 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

@oraspose

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.

Looks good - thank you :grinning:

@oraspose

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

Hi Shakeel,

After additional testing on Cells 18.3.7 - it seems this issue is only partially fixed. When we modify the conditional format rules, we are still seeing that the conditional formats being ignored in the HTML output.

For example, using the attached workbook “ConditionalFmt2.xlsx” the conditional formatting is ignored in the HTML output. Note that this version of the workbook uses “formula based” conditional formatting rules, as opposed to "cell value based’ rules from the original “ConditionalFmt.xlsx” workbook.

Please do let us know if there is a code workaround to circumvent this issue.

ConditionalFmt2.zip (10.1 KB)

@oraspose

Thanks for using Aspose APIs.

We were able to observe this issue as per your description and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will update you asap.

This issue has been logged as

  • CELLSJAVA-42590 - Formula Based Conditional Formatting is lost while saving Excel to HTML

@oraspose

Thanks for using Aspose APIs.

We have looked into this issue further and we think, this issue could be because of some old version as the most recent version (i.e. 18.3.9) is working fine. Please see the following code and its output html and screenshot for a reference.

Once, you will execute this code, it will generate the html file named 18.3.9_output.html as shown inside the screenshot.

Download Link:
18.3.9_output.zip (5.4 KB)

Java

Workbook wb = new Workbook(dirPath + "ConditionalFmt2.xlsx"); 
wb.calculateFormula(); 
wb.save(dirPath + CellsHelper.getVersion() + "_output.html");

Screenshot:

Thanks for the prompt response Shakeel.

We tried using Cells version 18.3.9. Using the code you provided - we can indeed see the same output as you provided. However, if you use the original code provided above, you should see that the conditional formats are omitted.

After additional investigation, it seems that the culprit seems to be the following save options:

options.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);

If we use HIDDEN instead of REMOVE - the conditional formats are applied to the HTML output. But using REMOVE will cause the conditional formats to be omitted.

While it might seem easy to simply use “HIDDEN” - we would prefer to use REMOVE because HIDDEN will include the hidden cells within the generated HTML (even though they should not be displayed). In our case, the process that processes the HTML (generated by Cell) tries to consume the hidden cells.

Please consider fixing this so that it works with the REMOVE option. This is a subtle bug that other Cells users may hit.
Thank you.

@oraspose

Thanks for your posting and using Aspose APIs.

We were able to observe this issue after adding your mentioned lines and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will update you asap.

Java

Workbook wb = new Workbook(dirPath + "ConditionalFmt2.xlsx"); 
wb.calculateFormula(); 

HtmlSaveOptions options = new HtmlSaveOptions(SaveFormat.HTML);
options.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
options.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);

wb.save(dirPath + CellsHelper.getVersion() + "_output.html", options);

@oraspose

We are afraid, this issue cannot be fixed because if you delete Column “J” manually in MS-Excel, you will discover that Formula Based Conditional Formatting is lost too.

The issues you have found earlier (filed as CELLSJAVA-42578) have been fixed in this update. This message was posted using BugNotificationTool from <a href=“https://#{request.env[“HTTPS_HOST”]}”>Downloads module by Amjad_Sahi

The issues you have found earlier (filed as CELLSJAVA-42578) have been fixed in Aspose.Cells for Java 18.4. Please also see the document for your reference: