Pivot Table breaks when deleting columns and rows

t1jsw · February 14, 2019, 2:58am

Hi,

I am having an issue with pivot tables when I delete rows and columns.

Attached is the orig.xlsx that I am using to run my test. I have also attached Sample.xlsx that shows what the original sheet looks like, what it looks like after running my aspose code, and what it looks like completing the same steps manually in excel.

Via aspose it seems like upon deleting rows, rather than moving the pivot table it stays put, but is now blank. The details that should be there are in the cells you would have expected the pivot table to have moved to but they are just normal cells now. NOTE: I also have a pivot chart and that moves as expected but in my case is actually now covering the broken pivot table.

Here is the code:
Dim loFileStrm As New System.IO.FileStream(“D:\Temp\Orig.xlsx”, IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.ReadWrite)
Dim loOrigWB = New Aspose.Cells.Workbook(loFileStrm)
Dim loOrigWS As Aspose.Cells.Worksheet = loOrigWB.worksheets(“Sheet1”)
loFileStrm.Close()
loOrigWB.Worksheets.Insert(0, Aspose.Cells.SheetType.Worksheet).Copy(loOrigWS)
Dim loNewWS As Aspose.Cells.Worksheet = loOrigWB.Worksheets.Item(“Sheet2”)
loNewWS.Cells.DeleteRows(0, 3)
loNewWS.Cells.DeleteColumns(0, 3, True)
loOrigWB.save(“D:\Temp\Orig.xlsx”)

Workbooks.zip (31.3 KB)

Any assistance would be greatly appreciated.

Thanks, Julie

ahsaniqbalsidiqui · February 14, 2019, 2:42pm

@t1jsw,

Thanks for your query. We are working on it and will share our feedback soon.

ahsaniqbalsidiqui · February 14, 2019, 4:01pm

@t1jsw,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46587 - Pivot Table breaks when deleting rows and columns

amjad.sahi · February 22, 2019, 9:04am

@t1jsw,

Please try our latest version/fix: Aspose.Cells for .NET v19.2.1:

Aspose.Cells19.2.1 For .Net2_AuthenticodeSigned.Zip (4.7 MB)
Aspose.Cells19.2.1 For .Net4.0.Zip (4.8 MB)

Your issue should be fixed in it.

Let us know your feedback.

t1jsw · February 24, 2019, 11:35pm

Thanks Amjad for your yet again quick response.

Unfortunately we are using .Net 2 and I can’t get the AuthenticodeSigned one to work. Is it possible to provide an unsigned one which is what we usually use?

Thanks, Julie

ahsaniqbalsidiqui · February 25, 2019, 6:02am

@t1jsw,

Your request is recorded and we will let you know once it is ready to be shared.

amjad.sahi · February 25, 2019, 11:18am

@t1jsw,

Generally we do provide signed Dlls (fixes) in the forums. We will check if we could provide you the unsigned assembly for .NET 2.0 for your needs. Alternatively, you have to wait for our next official release, i.e., Aspose.Cells for .NET v19.3 to come out (which will contain both signed and unsigned Dlls (.NET 2.0)). Our next official release is scheduled to be released in the third/fourth week of March 2019.

amjad.sahi · February 28, 2019, 9:43am

@t1jsw,

Please try our latest unsigned Dll (.NET 2.0) version/fix: Aspose.Cells for .NET v19.2.3 (attached)

Aspose.Cells19.2.3 For .Net2.Zip (4.7 MB)

t1jsw · March 4, 2019, 4:18am

Thanks Amjad. This has fixed my original issue. However I have noticed two other behaviours.

In the created workbook hitting refresh on pivot table (from within excel) causes the cell formatting to change for the data in the pivot table.
The PivotChart on the second sheet is pointing to the PivotTable on the first sheet. If I manually copy via excel into same workbook the pivot chart points to the pivot table on the same sheet (ie the sheet that was just created).

NOTE: When copying PivotTables directly in excel the datasource is relative which is why I deliberately set in code to ensure it points to the data range on the same sheet. However this in not the case for the pivot charts. They are always changed to point to the pivot table on the same sheet (ie not relative).

Similar programming via excel results in me needing to change the datasource of the pivot table only and then the pivot chart all works fine.

Here is my code:

    Dim loFileStrm As New System.IO.FileStream("D:\Temp\Orig.xlsx", IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.ReadWrite)
    Dim loOrigWB = New Aspose.Cells.Workbook(loFileStrm)
    Dim loOrigWS As Aspose.Cells.Worksheet = loOrigWB.worksheets("Sheet1")
    loFileStrm.Close()

    '' Copy original sheet into another workbook and ensure pivot table is pointing to range contained on new sheet
    Dim loNewWB As New Aspose.Cells.Workbook(Aspose.Cells.FileFormatType.Xlsx)
    Dim loNewWS As Aspose.Cells.Worksheet = loNewWB.Worksheets(0)
    loNewWS.Copy(loOrigWS)
    Dim lsRanges(0) As String
    lsRanges(0) = "Sheet1!DATA"
    loNewWS.PivotTables(0).ChangeDataSource(lsRanges)

    '' Copy the newly created sheet into same workbook and ensure pivot table is pointing to range contained on new sheet
    Dim liNew As Integer = loNewWB.Worksheets.Add
    Dim loOtherNewWS As Aspose.Cells.Worksheet = loNewWB.Worksheets(liNew)
    loOtherNewWS.Copy(loNewWS)
    lsRanges(0) = "Sheet2!DATA"
    loOtherNewWS.PivotTables(0).ChangeDataSource(lsRanges)

    '' Add some data and remove empty rows and columns
    loOtherNewWS.Cells.InsertRows(14, 3)
    loOtherNewWS.Cells(15, 3).Value = 5
    loOtherNewWS.Cells(15, 4).Value = 110
    loOtherNewWS.Cells(16, 3).Value = 4
    loOtherNewWS.Cells(16, 4).Value = 120
    loOtherNewWS.Cells(14, 3).Value = 5
    loOtherNewWS.Cells(14, 4).Value = 130
    loOtherNewWS.Cells.DeleteRows(0, 3)
    loOtherNewWS.Cells.DeleteColumns(0, 3, True)

    loNewWB.Save("D:\Temp\Final.xlsx")

Also attached is the orig and final workbooks. Workbooks2.zip (28.5 KB)

Any assistance would be greatly appreciated. Let me know if you would rather I raise these issues on a separate thread.

Thanks, Julie

ahsaniqbalsidiqui · March 4, 2019, 3:44pm

@t1jsw,

I can observe this issue but need confirmation about the change in cell formatting. As per my observation, the font is changed from Calibri 11 to Arial 10, please confirm.

I have tried this scenario in Excel where I copied all the contents from one sheet and pasted it in a new sheet. The PivotChart on the new worksheet points to the first sheet rather than the same sheet. Could you please explain this second observation as I can see that Excel and Aspose.Cells are producing similar results?

Once you provide feedback about these points, we will log ticket(s) accordingly.

t1jsw · March 5, 2019, 12:07am

Hi,
I can confirm that the font change is from Calibri 11 to Arial 10.

For the second part I am using copy sheet. So not selecting within sheet and pasting to another existing sheet.

The code I provided is what I am trying to do which is not working.
Equivalent manual excel steps (tested in both 2010 and 2013) are:

Open Orig.xls
Choose to copy Sheet1 to same workbook
Sheet1 (2) is created
The pivot table data source on Sheet1 (2) refers to Sheet1!DATA
The pivot chart Select Data has [Orig.xlsx]Sheet1 (2)!PivotTable2 (greyed out so cannot be changed but is pointing to the pivot table on the same sheet)
Edit the first value in the DATA section of Sheet1 (2). ie change “Sheet1 (2)!E6” to 20
Select to change the datasource on the PivotTable on Sheet1 (2) from “Sheet1!DATA” to be “Sheet1 (2)!DATA”
Both the pivot table and the graph on Sheet1 (2) now display the figures on the DATA section of that sheet. Neither are referencing the original sheet.

When I run the code provided previously the PivotChart is actually pointing to the original Pivot table on Sheet1. So even after I change the data on Sheet1 (2) and update the pivot table to point to data on same sheet the graph does not reflect that new data. This is not the same behaviour as the same steps completed manually in excel.

Thanks, Julie

t1jsw · March 5, 2019, 3:21am

Hi,

Here is a better example of my second issue. It is similar to above except that the Original sheet is not called Sheet1 so as not to be confused with the newly created Sheet1 in a new workbook.

First I copy the original into a new workbook and change the pivot table datasource to point to local named range rather than the original named range. Then I copy that sheet into the same workbook and similarly update the datasource for the pivot table to be local rather than pointing back to previous sheet. I then alter the data in the range as well as remove some unnecessary columns and rows.

We currently perform corresponding steps in vb code using excel. When I am done the second sheet graph points to the pivot table on the same sheet which points to the data range also on that sheet. It displays the updated data.

When run via Aspose the Pivot Tables are displaying and working as expected but the associated charts have not maintained their connection to the table on the same sheet. Following the manual steps in my previous response shows how simply copying into same workbook results in the pivot chart always being associated with the pivot table on the same sheet and never the original sheet.

Here is the code:

    Dim loFileStrm As New System.IO.FileStream("D:\Temp\NewOrig.xlsx", IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.ReadWrite)
    Dim loOrigWB = New Aspose.Cells.Workbook(loFileStrm)
    Dim loOrigWS As Aspose.Cells.Worksheet = loOrigWB.worksheets("Original")
    loFileStrm.Close()

    '' Create a copy of original into a new workbook
    Dim loNewWB As New Aspose.Cells.Workbook(Aspose.Cells.FileFormatType.Xlsx)
    Dim loNewWS As Aspose.Cells.Worksheet = loNewWB.Worksheets(0)
    loNewWS.Copy(loOrigWS)

    '' Update the datasource of pivot table to be local to sheet
    Dim lsRanges(0) As String
    lsRanges(0) = "Sheet1!DATA"
    loNewWS.PivotTables(0).ChangeDataSource(lsRanges)

    '' Create a second copy from the first Copy
    Dim liNew As Integer = loNewWB.Worksheets.Add
    Dim loOtherNewWS As Aspose.Cells.Worksheet = loNewWB.Worksheets(liNew)
    loOtherNewWS.Copy(loNewWS)

    '' Update the datasource of pivot table to be local to sheet
    lsRanges(0) = "Sheet2!DATA"
    loOtherNewWS.PivotTables(0).ChangeDataSource(lsRanges)

    loOtherNewWS.Cells.InsertRows(14, 3)
    loOtherNewWS.Cells(15, 3).Value = 5
    loOtherNewWS.Cells(15, 4).Value = 110
    loOtherNewWS.Cells(16, 3).Value = 4
    loOtherNewWS.Cells(16, 4).Value = 120
    loOtherNewWS.Cells(14, 3).Value = 5
    loOtherNewWS.Cells(14, 4).Value = 130

    loOtherNewWS.Cells.DeleteRows(0, 3)
    loOtherNewWS.Cells.DeleteColumns(0, 3, True)
    loNewWB.Save("D:\Temp\NewFinal.xlsx")

Also here are the two workbooks. NewWorkbooks.zip (27.9 KB)

Hope this makes more sense and explains my issue better.

Thanks, Julie

ahsaniqbalsidiqui · March 5, 2019, 6:23am

@t1jsw,

Thanks for the details.

We need to look into it more. We have logged the issue “CELLSNET-46627” in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

ahsaniqbalsidiqui · March 11, 2019, 5:54am

@t1jsw,

Please change pivot source of chart as a temporary solution. We will continue to look into the issue and change the pivot source when copying worksheet. In the new fix, you would not need to add external code.

'Update the datasource of pivot table to be local to sheet
Dim lsRanges(0) As String
lsRanges(0) = "Sheet1!DATA"
loNewWS.PivotTables(0).ChangeDataSource(lsRanges)
loNewWS.Charts(0).PivotSource = "Sheet2!PivotTable2"

'Create a second copy from the first Copy        
Dim liNew As Integer = loNewWB.Worksheets.Add        
Dim loOtherNewWS As Aspose.Cells.Worksheet = loNewWB.Worksheets(liNew)        
loOtherNewWS.Copy(loNewWS)

'Update the datasource of pivot table to be local to sheet        
lsRanges(0) = "Sheet2!DATA"         
loOtherNewWS.PivotTables(0).ChangeDataSource(lsRanges)        
loOtherNewWS.Charts(0).PivotSource  = "Sheet2!PivotTable2"

t1jsw · March 12, 2019, 1:56am

Thank you very much. I have managed to code around this issue for now.

Can you please let me know when you have fixed this issue permanently so I can remove extra processing and retest?

Thanks again, Julie

ahsaniqbalsidiqui · March 12, 2019, 7:06am

@t1jsw,

Good to know that your issue is sorted out for now. Once, we will have some news for you, we will update you in this topic.

ahsaniqbalsidiqui · March 15, 2019, 8:04am

@t1jsw,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46627”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

aspose.notifier · March 15, 2019, 10:50am

The issues you have found earlier (filed as CELLSNET-46587) have been fixed in Aspose.Cells for .NET v19.3. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

ahsaniqbalsidiqui · March 21, 2019, 9:12am

@t1jsw,

Please try the latest fix 19.3.1 with the following codes:

        FileStream loFileStrm = File.OpenRead(Constants.sourcePath + "CELLSNET46627.xlsx");
        Workbook loOrigWB = new Aspose.Cells.Workbook(loFileStrm);
        Aspose.Cells.Worksheet loOrigWS = loOrigWB.Worksheets["Original"];

        loFileStrm.Close();
        Workbook loNewWB = new Aspose.Cells.Workbook();
        Aspose.Cells.Worksheet loNewWS = loNewWB.Worksheets[0];
        loNewWS.Copy(loOrigWS);

        //        string[] lsRanges = new string[] { "Sheet1!DATA" };
        //        loNewWS.PivotTables[0].ChangeDataSource(lsRanges);

        Assert.AreEqual("Sheet1!PivotTable2", loNewWS.Charts[0].PivotSource);
        int liNew = loNewWB.Worksheets.Add();
        Aspose.Cells.Worksheet loOtherNewWS = loNewWB.Worksheets[liNew];
        loOtherNewWS.Copy(loNewWS);
        string[] lsRanges = new string[] { "Sheet2!DATA" };
        loOtherNewWS.PivotTables[0].ChangeDataSource(lsRanges);
        loOtherNewWS.Cells.InsertRows(14, 3);
        loOtherNewWS.Cells[15, 3].Value = 5;
        loOtherNewWS.Cells[15, 4].Value = 110;
        loOtherNewWS.Cells[16, 3].Value = 4;
        loOtherNewWS.Cells[16, 4].Value = 120;
        loOtherNewWS.Cells[14, 3].Value = 5;
        loOtherNewWS.Cells[14, 4].Value = 130;
        loOtherNewWS.Cells.DeleteRows(0, 3);
        loOtherNewWS.Cells.DeleteColumns(0, 3, true);

BTW, MS Excel does not change the data source of the PivotTable when copying the worksheet in the same workbook, Aspose.Cells works as MS Excel, so you have update the data source of the PivotTable in the worksheet “Sheet2” by yourself.

Please try our latest version/fix: Aspose.Cells for .NET v19.3.1:

Aspose.Cells19.3.1 For .NetStandard20.Zip (4.0 MB)
Aspose.Cells19.3.1 For .Net2_AuthenticodeSigned.Zip (4.8 MB)
Aspose.Cells19.3.1 For .Net4.0.Zip (4.8 MB)

Let us know your feedback.

t1jsw · March 25, 2019, 5:58am

Hi,

Unfortunately I need the .Net2 (unsigned versions) to be able to get it to work with my code. If you are able to provide them then I will perform further testing. Otherwise I will wait for 19.4 to be released.

I was aware that when copying within a workbook that MS Excel does not change the data source of PivotTables so I already have code that does that (for both my Excel and Apsose versions). It was just with Aspose that I then had issues with the pivot table source which hopefully is now addressed.

Thank you for all your assistance.

Cheers, Julie