Exporting a selected range of cells to HTML

Aspose.Cells Product Family
1

The setPrintArea() call doesn't work when saving xls as HTML.

Kindly suggest on options for setting HTML print area boundary.

Thanks

2

Hi Suresh,


First of all, we have split the existing thread to create a new one on your behalf for better visibility and tracking of your requests.

Setting the print area will not take effect if you wish to convert a specific range of cells to HTML format. In order to achieve the same goal while exporting the spreadsheets to HTML format, you have to hide the undesired rows & columns and then export the spreadsheet to HTML using the following piece of code.

Java

HtmlSaveOptions option = new HtmlSaveOptions();
option.setHiddenColDisplayType(HtmlHiddenColDisplayType.REMOVE);
option.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.REMOVE);
workbook.save(“D:/output.html”, option);


Here is the detailed article on hiding the rows and columns. Please feel free to contact us back in case you find any difficulty.
3

Hi Thanks for your reply.

The solution that you had provided works, but my requirement is little different.

After generating an XLS, when I need to convert it to HTML the data area should be hidden and only the chart should be shown.

When I hide cells with the cells.hideRow() method, the data goes away and no chart gets generated and the generated HTML shows up blank.

Kindly help on this query on Priority.

Thanks,

Suresh

4

Hi Suresh,


Could you please share your sample spreadsheet along with your current source code for our review?
5

Hi,

Pls find attached the xls that Iam converting to HTML.

I need the range of cells - A1:AD44 alone to be included in the generated HTML and want to ignore the data area (i.e) the range AF49 : AZ54.

Pls find below the code that iam using for the conversion.

----------

Workbook workbook = new Workbook("D:/test/testFile_1.xlsx");
HtmlSaveOptions saveOptions = new HtmlSaveOptions(SaveFormat.HTML);
saveOptions.getImageOptions().setImageFormat(ImageFormat.getPng());
saveOptions.getImageOptions().setTransparent(true);
saveOptions.setExportHiddenWorksheet(false);
saveOptions.setPresentationPreference(true);
saveOptions.getImageOptions().setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
saveOptions.getImageOptions().setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING, RenderingHints.VALUE_TEXT_ANTIALIAS_ON);

PageSetup pgSetup = workbook.getWorksheets().get(0).getPageSetup();
pgSetup.setPrintArea("A1:AD44");
workbook.save("D:/test/testFile_1.html", saveOptions);

----------

6

Hi Suresh,


Thank you for providing the sample spreadsheet.

We have noticed that when we hide the data rows/columns on which chart is based, the resultant HTML & spreadsheet renders the chart blank. We are trying to find a workaround for this situation, and we will shortly get back to you with updates in this regard.
7

Hi Suresh,

I have looked further into this matter, and digging revealed that by default the Excel application and the Aspose.Cells APIs will render the chart empty if its data source is hidden. You may confirm this in Excel as well. In order to avoid this problem you can set the “Show data in hidden rows and columns” option in Excel (please check attached snapshot), whereas the same option is available as Chart.PlotVisibleCellsproperty in Aspose.Cells APIs. Moreover, you cannot remove the hidden rows/columns from the rendering process, instead, you should be setting them as hidden. Please check the following piece of code, and give it a try on your end to feed us back with your results.

Java

Workbook workbook = new Workbook(“D:/testFile_1.xlsx”);

<b>workbook.getWorksheets().get(“Data”).getCells().hideRows(43, 15);
//workbook.getWorksheets().get(“Data”).getCells().hideColumns(32, 20);</b>

HtmlSaveOptions saveOptions = new HtmlSaveOptions(SaveFormat.HTML);
saveOptions.getImageOptions().setImageFormat(ImageFormat.getPng());
saveOptions.getImageOptions().setTransparent(true);
saveOptions.setExportHiddenWorksheet(false);
saveOptions.setPresentationPreference(true);
saveOptions.getImageOptions().setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
saveOptions.getImageOptions().setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING, RenderingHints.VALUE_TEXT_ANTIALIAS_ON);

<b>saveOptions.setHiddenRowDisplayType(HtmlHiddenRowDisplayType.HIDDEN);
//saveOptions.setHiddenColDisplayType(HtmlHiddenColDisplayType.HIDDEN);</b>

<b>workbook.getWorksheets().get(“Data”).getCharts().get(0).setPlotVisibleCells(false);</b>

workbook.save(“D:/testFile_1.html”, saveOptions);
8

Hi Thanks for the solution.

This works fine for charts.

But in case of Tables which refer to data from other cells, when the data cells are hidden, the table is showing up blank.

So hiding the data area cells works for charts and for Table charts its doesnt work. Can you kindly suggest how to handle this.

Thanks,
Suresh
9

Hi Suresh,


Thank you for the confirmation on suggested solution. In order to investigate the matter with tables, could you be kind enough to share a sample spreadsheet for investigation?