Pivot table issue when refreshing data (Do you wish to replace contents of the destination cells.)

Aspose.Cells Product Family
1

Hello,


We currently have a excel spreadsheet that contains a pivot table, chart and a worksheet with the data in (these are created manually through Excel itself) we take the saved file open it using Aspose and replace the data with new data and resave.

The problem is that when we open the spreadsheet it pops up a message box saying “Do you wish to replace contents of the destination cells.”
How can we stop this appearing?

We do not want to use macos within the spreadsheet but can’t see of a way to get rid of the message.

I think it is appearing because the pivot table has a variable number of rows in it.
So when the spreadsheet was created it could have had only 5 rows
but the new data means that it now has 10 rows and therefore we get the message.
Many thanks
Sarah

2

Hi Sarah,

Thanks for your inquiry.

Well, if you do not use the macros in the template file, you may try to delete the macros using the API i.e…, Workbook.RemoveMacro() method.

If you still find any issue, kindly post your template excel file, created excel file and sample code to reproduce the issue, we will check it soon.

Thank you.

3

I’m not sure you understood. There are no macros in the spreadsheet and if possible we want it to stay that way.


We just want it to stop the message box appearing when the data is refreshed and the file opened.

var workBook = new Workbook();
workBook.Open(template.FileLocation);

workBook.Worksheets.RemoveAt(“DATA”);
var datasheet = workBook.Worksheets.Add(“DATA”);

//Import the datareader object to the sheet cells
vendorRepository.GetReportData(template.StoredProcedureName);

datasheet.Cells.ImportFromDataReader(vendorRepository.ReportDataReader, true, 0, 0, true, “dd mmm yyyy”, false);

workBook.Save(reportName)

This code is working but when we open report the saved report we get the message box, and we don’t want this to appear.

Thanks again
Sarah

4

Hi,

Could you post your generated file here, we will check it soon.

Thank you.

5

Hi,

After further investigation, we found the issue is actually caused that we do not support to read the pivot table from the template file. If the pivot data source is updated, the preserved data range could not contain all data of the pivot table:
1) In MS Excel, if there’s any data or style to be set for the pivot data range, it will appear.
2) In MS Excel 2007, it always appears.


We are working on reading the pivot table from the template file currently, so we might support this feature in our future versions.

Thanks for your patience.

6

Thanks for your quick reply.


Any ideas on when this functionality will be added (rough dates would be good)?
7

Hi,

Hopefully, we will complete the feature in Q1 2010.


Thank you.

8

Hi,


I’ve just downloaded the Aspose.Cells demo and the issue is still there. Do you know when you are going to resolve it?

Thanks,
Ignacio.
9

Hi,

I think, this issue has been resolved in the latest version:
Aspose.Cells for .NET v7.0.1.6
please try it and share the feedback.

Let us know the demo you are particularly referring to. We will look into it and fix the code.

10

Shakeel,

I have the same issue. I just tested our pivot table with Aspose.Cells DotNet 7.0.2 and the issue is not resolved. Please let me know when you have a fix.

Best,

Clifton Sothoron

LogiXML Inc.

11

Hi,

Please try pivottable.RefreshData() to refresh the pivot source.

And if the issue is still there please provide us your sample file and code.