Pivot table don't refresh correctly

Aspose.Cells Product Family
1

Hi,

I have a template file S1.xls with a pivot table and some data. The pivot table source is a range name called "RepDataRange". What I need to do is use aspose.cells to open the template and refresh the pivot table. I am able to do that but the result is not correct.

When I open S1.xls with MS Excel after processing the pivot it don't look right , but when I use MS Excel and refresh the pivot table it fixes the problem.

So the code I am using to refresh the pivot in aspose is not working. Here is my code:

Dim wb As Workbook

wb = New Workbook("S1.xls")

Dim pivotable As Pivot.PivotTable

pivotable = wb.Worksheets("RepDataSheet").PivotTables(0)

Dim lastrow As Integer = wb.Worksheets("RepDataSheet").Cells.EndCellInColumn(0).Row

Dim range As Range = wb.Worksheets("RepDataSheet").Cells.CreateRange(0, 0, lastrow + 1, 7)

range.Name = "RepDataRange"

pivotable.RefreshData()

pivotable.CalculateData()

wb.Save("S1.xls")

I will appreciate any help on this

Thanks

2
Hi,

Please try our latest fixed version: v7.0.2.2, if it works fine as I have tested with your sample code.


3

Hi,

I tested with the latest Dll versions and is doing something better but still not exactly what refresh from Excel does.

2 differences:

-Output is not sorted (4,5,3,2) it should be (2,3,4,5)

-First Column have totals displayed

What I do is run the aspose code and then open S1.xls (I took screen shot asposeoutput.JPG)

After is open I refresh pivot using excel ( I took screen shot ExcelOutputAfterRefreshPivot.JPG)

(to refresh pivot in excel I use excel 2007, with mouse select a cell inside pivot , then go to menu Options then Refresh)

How can I get the same output from aspose ?

4

Other differences that I see is that columns are out of order as well

also totals should appear in second column

so instead of 4 Total in first column it should be Biology Total in second column

5

see picture side by side

6

Hi,


Thanks for your screen shots to point out the issue.

I can see your mentioned differences. I have logged a ticket for your issue with an id: CELLSNET-40046. We will look into the issue soon.

Thank you.
7

Thanks for your response,

will this fix take a long time ?

There is not a work around to sort columns and rows in pivot that is the big issue

the totals maybe erase some how.

Of course better if it does it as excel

Thanks

8

Hi,

Generally, the fix takes 2~3 days. If the issue is difficult, then it can take a week or two. We have added your comment in our database. Once the issue is resolved or we have a workaround, we will update you asap.

9

Hi,


Please try the latest version/fix v7.0.2.4. we have fixed your issue now.

Thank you.

10

Hello,

I tested the fix and it works much better.

I found 2 issues that make it different from excel refresh both are illustrated in the screen shot.

First is the collapsable button out of place

Second is the sort order within Sales Goal don't conform with excel way of sorting and I wondering if there is control over the way of sorting

Thanks for the help

11

Sorry the issue with the collapse is really not an issue. It is showing up on my side because I had some extra commands that I forgot to comment it out

'For i As Integer = 0 To pivotable.RowFields.Count - 1

' pivotable.RowFields(i).AutoSortField = 0

'Next

'For o As Integer = 0 To pivotable.ColumnFields.Count - 1

' pivotable.ColumnFields(o).AutoSortField = True

'Next

Once I comment it the code runs perfect

12

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

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

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan