API to convert worksheets with formulas or objects to replace with calculated data and keep only static content

Aspose.Cells Product Family
1

I have a complex workbook with multiple worksheets that contain tables and pivot tables. Data in tables are updated and pivots are recalculated. From the workbook I need to generate multiple workbooks, each contains a subset of worksheets. I need to convert worksheet to static content (replacing formulas with values and remove pivot tables), because I exclude worksheets that contains tables used by pivots.


I guess I can create a new workbook, create new worksheet and copy max display ranges (btw what PasteType should I use in order exclude only formulas?). But in this case I loose workbook/worksheet level data from the original workbook (e.g. page layouts settings), which is important in my scenario?

What approach can you recommend for my case?

2

Hi,


Thanks for your query.

I think you may try to use Cells.RemoveFormulas method to replace the formulas with their respective values in the cells of the worksheet.

Hope,this helps a bit.

Thank you.

3

Yep, I found this method. But it leaves pivot tables. Is there a way to remove a pivot table, but leave it content (kind of similar to what RemoveFormulas does for formulas)?

4

Hi,


Well, I am afraid, there is no such APIs to remove the PivotTable object but not pivot data, so, you have to manually overwrite the PivotTable by its calculated data using Aspose.Cells APIs.

Thank you.