i have an excel sheet which contains a pivottable whose datasource is in another sheet whin the same excel. how can i convert the sheet which contains the pivot table into a html file?
Hi Jimmy,
Thank you for contacting Aspose support.
If you wish to export the complete spreadsheet (containing the PivotTable and data source) to HTML format then please refer to the following code snippet. Please note, the code snippet assumes that the PivotTable is in the second worksheet, so please change it accordingly.
C#
var book = new Workbook(“D:/temp/sample.xlsx”);
var sheet = book.Worksheets[1];
var pivot = sheet.PivotTables[0];
pivot.CalculateData();
book.Save(“D:/temp/output.html”, SaveFormat.Html);
In case you wish to render only the worksheet that contains the PivotTable then you may hide the unwanted worksheets while setting Worksheet.IsVisible property to false before rendering the spreadsheet in HTML format.
Please feel free to write back in case you need our further assistance.
how to copy one pivot table to another sheet?
Hi Jimmy,
I am afraid, Aspose.Cells APIs do not offer any means to copy the Pivot Table to another worksheet. We may log this as a feature request in our database however, providing the implementation may take sometime. Could you please provide the details on why you are required to copy the Pivot Table to another Worksheet so we may suggest you an alternative solution/workaround?
it can be saved to HTML format now. But when it’s saved as HTML format, it also created several folders, so if i copied the HTML file without that folders, i cannot see anything when i opened the html file in the new location? Any way can solve that?
Hi Jimmy,
I believe you have separated the sub HTML pages from it’s main page. Unfortunately, in this scenario the sub HTML pages (for worksheets) will not render at all. This is because due to the following statements in all these aforesaid pages.
You will notice that main file is referenced in all the sub pages with relative path. When you remove the sub pages from its container folder, the path becomes invalided and therefore sub pages cannot be rendered. Moreover, filelist.xml contains all the references to the sub pages that is also essential to render all the pages at one place.
Please note, Aspose.Cells APIs follows MS Excel guidelines & recommendation in its implementation. This is the behavior of MS Excel to save the spreadsheets in such a way as discussed above.
Please elaborate your requirement in more detail so we could suggest you workaround for this situation.
i just wat to save the sheet which contains the pivot table into one html file. then when i only copy the html file to another place, it can be opened without any referred files. based on your words, i need to copy all the files to another place, so when open the html file, it’s ok.
Hi Jimmy,
Thank you for writing back.
As discussed in my previous response, it is currently not possible to tap into the HTML generation process to get required results. This is because Aspose.Cells MS Excel’s guidelines while rendering spreadsheets to HTML format. Anyway, please try the following piece of code on your end.
C#
Aspose.Cells.Workbook book;
book = new Aspose.Cells.Workbook(“D:/sample.Xlsx”);
foreach (Worksheet sheet in book.Worksheets)
{
if (sheet.Name != “pivot”)
{
sheet.IsVisible = false;
}
}
MemoryStream stream = new MemoryStream();
book.Save(stream, new HtmlSaveOptions() );
stream.WriteTo(new FileStream(“D:/output.html”, FileMode.Create));
In above code snippet, I am loading a spreadsheet having two worksheets. One of these worksheets (pivot)contains the Pivot Table whereas it's data-source resides on another worksheet that is not required to be rendered so I am hiding all the worksheets except the one with the name pivot. Moreover, I am saving the results in an instance of MemoryStream first, this allows me to get rid of the additional resource folder so when saved you will get one master/main HTML file with a sub HTML file. You can move both files to any other location but you may not be able to separate them.
Hope it helps a bit.