Using LoadFilter breaks HTML output

Aspose.Cells Product Family
1

Hi,

Using Aspose Cells for Java, version 25.2.

Sample code:

LoadOptions loadOptions = new LoadOptions();
String sheetName = "Dashboard";
loadOptions.setLoadFilter(new MyLoadFilter(sheetName));

Workbook workbook = new Workbook("input.xlsx", loadOptions);
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.get(sheetName);
worksheets.setActiveSheetIndex(worksheet.getIndex());
HtmlSaveOptions saveOptions = new HtmlSaveOptions(SaveFormat.HTML);
saveOptions.setExportActiveWorksheetOnly(true);
saveOptions.setEncoding(Encoding.getUTF8());
saveOptions.setExportImagesAsBase64(true);

workbook.save("output.html", saveOptions);

static class MyLoadFilter extends LoadFilter {

    private final String sheetName;

    public MyLoadFilter(String sheetName) {
        this.sheetName = sheetName;
    }

    @Override
    public void startSheet(Worksheet sheet) {
        if (sheet.getName().equals(sheetName)) {
            // load everything
            setLoadDataFilterOptions(LoadDataFilterOptions.ALL);
        } else {
            // load nothing except sheet name
            setLoadDataFilterOptions(LoadDataFilterOptions.STRUCTURE);
        }
    }
}

When executing this code, and comparing the results with Excel, you’ll notice that a number of graphical elements are missing.

Removing the line loadOptions.setLoadFilter(new MyLoadFilter(sheetName)); results in the output being much closer to Excel. This seems to be a bug in Aspose Cells, as the load filter in question simply limits the loading to only the sheet being exported.

Kind regards,
Taras
repro.zip (2.7 MB)

2

@TarasTielkes

It seems that you are experiencing issues with missing graphical elements in the HTML output when using a custom LoadFilter in Aspose.Cells for Java. The problem arises when you limit the loading to only the specified sheet, which may inadvertently exclude certain graphical elements that are necessary for a complete representation of the sheet.

When you set the load filter to only load the specified sheet with LoadDataFilterOptions.STRUCTURE, it restricts the loading of other elements that might be required for rendering the sheet accurately in HTML format. This is likely why you notice discrepancies between the output and what you see in Excel.

To address this issue, you might consider the following approaches:

  1. Load All Sheets: If possible, try loading all sheets without applying the custom load filter. This will ensure that all graphical elements are included in the output.

  2. Modify LoadFilter: If you need to keep the load filter, you could modify the startSheet method to load additional data or graphical elements that are necessary for the HTML output. For example, you could check for specific graphical elements and adjust the loading options accordingly.

  3. Check Documentation: Review the Aspose.Cells documentation for any specific notes on how LoadFilter interacts with graphical elements during the export process. This might provide insights into how to adjust your implementation.

Here is a simplified example of how you might adjust your LoadFilter:

@Override
public void startSheet(Worksheet sheet) {
    if (sheet.getName().equals(sheetName)) {
        // Load everything for the specified sheet
        setLoadDataFilterOptions(LoadDataFilterOptions.ALL);
    } else {
        // Load only the structure for other sheets
        setLoadDataFilterOptions(LoadDataFilterOptions.STRUCTURE);
    }
}

By ensuring that the necessary graphical elements are included, you should be able to achieve a closer match to the original Excel output.

If the issue persists, consider reaching out to Aspose support for further assistance, as they may have more specific insights into this behavior.

Sources:
[1]: LoadFilter | Aspose.Cells for Java API Reference
[2]: 使用Aspose.Cells将工作簿转换为HTML|Documentation

3

@TarasTielkes
The data source of chart in “Dashboard” refers to “expenses dashboard calc” worksheet, If you does not load it, we could not find correct data of the chart.
If you only want to export one worksheet, you can load all worksheets and limit exporting as the following :

 Workbook workbook = new Workbook(dir + "Input.xlsx");

		 HtmlSaveOptions saveOptions = new HtmlSaveOptions();
		 saveOptions.setExportActiveWorksheetOnly( true);
		         
		 workbook.save(dir + "dest.html", saveOptions);
4

Hi, @simon.zhao ,

Ideally, we’d only load the sheets that we are interested in, to improve the responsiveness of the application.

Perhaps we could load the sheet we want only, then check if it has dependencies on other sheets, and in that case, perform a new load with the additional required sheets included. Do you think that that would be a reasonable approach?

Thanks in advance,
Taras

5

@TarasTielkes,

Your approach seems ok. If you have any additional questions or feedback, please don’t hesitate to reach out to us.

6

Hi @amjad.sahi

At the moment, the Chart class has the property getChartDataRange, which I assume I could use to implement the suggested solution.

However, it returns a string like ='expenses dashboard calc'!$A$1:$A$500, which I would need to parse to extract the target sheet name.

Perhaps it would make sense to add a method like getChartDataSheet, which would return just the sheet name of the data behind the chart?

Kind regards,
Taras

7

@TarasTielkes

It’s not a good idea. If the referred area refers to other worksheet, you will get stuck in searching for referenced worksheets

8

Can you give an example?
Are you referring to the scenario where the referred area contains formulas to other sheets?

9

@TarasTielkes

Recursive references between cells by formulas is just one of those scenarios that may cause issue. If you are sure you need not to update/re-calculate formulas in your template file, then the references of formulas may not cause issue. However, the chart data itself may have many different data sources, not only references of one cell range. And there are many objects in a chart that may use data references to other sheet, such as x-values, data labels, error bars, …etc. So we think it may be much more complicated than you have thaught to implement one simple solution to solve all such kind of issues.

10

@johnson.shi @simon.zhao thank you both for your detailed feedback!

11

@TarasTielkes,

You are very welcome. If you have any additional questions, comments, or concerns, please do not hesitate to reach out to us again.