Security Warning

Aspose.Cells Product Family
6

Hi,


Please clear the protection in your source file and re-attach it here, We will further investigate your issue.

Thank you.
7

Clear the protection? I don’t know what your refering to.

8

Hi,


I mean your template file is opened in Protected View (in MS Excel 2010) by default as my attached screen shot showed in the previous reply. Could you fix it or do something (e.g you may give us other template file that should be opened fine into MS Excel 2010 and should not be opened in Protected View with warning message etc.) to attach the file here again here, so that we could check your issue soon.

Thank you.
9

There is nothing I’m doing to create that Protected View. I’ve never seen it before and can’t reproduce it in Excel 2007, even when I open the file attached above. It must be a feature within Excel 2010. I have reattached the original file if that helps.

10

Hi,


This file also gives the Protection warning message, please let us know how did you generate this source file? Did you create it manually by using MS Excel or some other software? We need to investigate the issue.

Thank you.

11

It was created manually from nothing more than a new (blank) Excel workbook.

12

As an alternative to setting the pivot tables to refresh on open I’ve tried to update all pivot tables via code just before the save line like this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

For Each xlsWorksheet In xlsWorkbook.Worksheets

For Each xlsPivotTable In xlsWorksheet.PivotTables

xlsPivotTable.RefreshData()

xlsPivotTable.CalculateData()

Next

Next

My problem here is I’m getting this error at the RefreshData method:

Error: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

I can’t figure out what the Index parameter is referring to and I’m having trouble creating sample code to recreate the error.

13

Hi,


We appreciate if you could create a sample project, zip it (with all the files) and post it here to reproduce the issue on our end, we will check your issue soon.

Thank you.
14

Ok, while creating a sample project for you I discovered the object causing the problem. My pivot table has a calculated field in it. On click of the button you get an error “Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index” thrown at the xlsPivotTable.RefreshData() line. If you go into the Excel template file and remove the calculated field from the pivot table, all is fine and dandy. Can you find a solution for this problem?

15

Hi,


I still could not evaluate/check your issue, your provided sample project does not have its source code e.g .VB file, it has only Default.aspx file and a template Excel file in the “Reports” sub-folder. Kindly give us your runnable project/solution, so, that we could evaluate your issue soon.

thank you.
16

Hi,


Thanks for the project.
After an initial test running your project, I have found the error “Inded was out of range…” on PivotTable.RefreshData/CalculateData method. Moreover, If I simply comment out the lines in your project, e.g
’xlsWorkbook.Worksheets.Names.Remove(“Raw_Data”)
'xlsWorksheet.Cells.DeleteRows(4, 4)

'rgeDtaSource = xlsWorksheet.Cells.CreateRange(3, 0, 6, 4)
'rgeDtaSource.Name = "Raw_Data"

still I got the error on the lines:

For Each xlsWorksheet In xlsWorkbook.Worksheets
For Each xlsPivotTable In xlsWorksheet.PivotTables
xlsPivotTable.IsAutoFormat = False
xlsPivotTable.ShowDrill = False
xlsPivotTable.RefreshData()
xlsPivotTable.CalculateData()
Next
Next

I have logged a ticket for the issue with an id: CELLSNET-29876. We will look into your issue and get back to you soon.

Thank you.
17

Hi,

At present, we could not calculate the data if the pivottable has calculatedfield. We will support it later.

Please try this sample code.

C#


xlsPivotTable.RefreshData();

xlsPivotTable.RefreshDataOnOpeningFile = true;

// xlsPivotTable.CalculateData();



18

Well that’s very disappointing. Without supporting calculated fields that just puts more work back on IT (me) to create any calculation an end user may need in the database since they cannot create them their self in the spreadsheet.

Setting RefreshDataOnOpeningFile = True just brings me back to the original problem I had when I started this thread. Microsoft’s overprotective security alert disables the pivot table refresh and someone reading the report will not select the enable option. I need to rethink my entire approach to this project now.

19

Hi,

I have added your comment in the related issue. We will update you asap.

20
Hi,

We support calculating pivot table with calculated fields in it in the latest fixed version(attached), please try it.

Thank you.

21

The issues you have found earlier (filed as 29876) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
22

I’m having this same problem. Can you please provide the code that refreshes the piviot tables?

23

Hi,

Thanks for using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.3.0.5 and use the following code to refresh the pivot tables.

C#


pivotTable.RefreshData();


Let us know if you still face any issue, we will help you asap.


24

Actually I do need a hand. I’ll start a new thread.

25

Hi,

Thanks for your posting.

For your new issues, please create new threads with your sample code, source xls/xlsx files and screenshots.

It will help us to sort out your issue quickly.