none
Defer layout update powerpivot C# RRS feed

  • Question

  • Hi all,

    So I have a VSTO workbook which has a lot of powerpivot date loaded, my C# code basically creates numerous pivot tables based on data model. Due to the amount of data being pulled doing things like removing subtotals takes a stupid amount of time.

    As a test I did this in VBA to check how long it takes, please bear in mind that I have already put manual updates to true when creating the actual pivot data.

    Here is the VBA code WS is a worksheet variable:

    On Error Resume Next
    For Each pt In WS.PivotTables
      For Each PF In pt.PivotFields
        PF.Subtotals(1) = True
        PF.Subtotals(1) = False
      Next PF
      pt.ManualUpdate = False
      pt.ShowDrillIndicators = False

    Next pt

    On Error GoTo 0

    Here is my C# code processed in backgroundworker:

                            

    orksheet = (Excel.Worksheet)sheets.get_Item("Backing information");
                foreach (Excel.PivotTable pt in worksheet.PivotTables())
                {
                    foreach (Excel.PivotField pf in pt.PivotFields())
                    {
                        try
                        {
                            i = i + 1; backgroundWorker1.ReportProgress(i);
                            pt.ManualUpdate = true;
                            pf.set_Subtotals(1, true);

                            pf.set_Subtotals(1, false);
                        }
                        catch (System.Runtime.InteropServices.COMException)
                        {
                            // Do nothing
                        }
                    }
                    pt.ManualUpdate = false;
                    pt.ShowDrillIndicators = false;
                    i = i + 4; backgroundWorker1.ReportProgress(i);
                }
                i = 99; backgroundWorker1.ReportProgress(i);

    So the C# code takes about 13-14 seconds where as VBA takes under 8 seconds. You will notice I have an extra manualupdate=true in c#... without that its closer to 20 seconds.

    I think it's because every time it changes the subtotal value it sets the manual update to true, I also think it's only happening on Cube Fields.

    I have tried to put this in:

     worksheet = (Excel.Worksheet)sheets.get_Item("Backing information");
                foreach (Excel.PivotTable pt in worksheet.PivotTables())
                {
                    foreach (Excel.PivotField pf in pt.PivotFields())
                    {
                        try
                        {
                            i = i + 1; backgroundWorker1.ReportProgress(i);
                            pt.ManualUpdate = true;
                            pf.set_Subtotals(1, true);
                            pt.ManualUpdate = true;
                            pf.set_Subtotals(1, false);
                        }
                        catch (System.Runtime.InteropServices.COMException)
                        {
                            // Do nothing
                        }
                    }
                    pt.ManualUpdate = false;
                    pt.ShowDrillIndicators = false;
                    i = i + 4; backgroundWorker1.ReportProgress(i);
                }

    This massively speeds the running up but none of the pivots actually show any data it's like they didn't update after manual update =false.

    Have I missed something? I want to basically speed up the pivot updating as it feels a bit clunky and slow. Can anyone suggest anything?

    Thanks for your help and hopefully it all makes sense.

    Many thanks,

    Joe

    Thursday, July 20, 2017 3:37 PM

Answers

  • Hi Terry,

    I have figured it out, all I needed to was make sure that when the last subtotal was set to update false that it was the last field:

     foreach (Excel.PivotTable pt in worksheet.PivotTables())
                {
                    double counter = pt.RowFields.Count;
                    double pfi = 1;
                    
                    foreach (Excel.PivotField pf in pt.RowFields)
                    {
                        try
                        {
                            if (!(pfi == counter))
                            {
                                pt.ManualUpdate = true;
                            }
                            else
                            {
                                pt.ManualUpdate = false;
                            }
                            pf.set_Subtotals(1, false);
                        }
                        catch (System.Runtime.InteropServices.COMException)
                        {
                            // Do nothing
                        }
                        pfi = pfi + 1;
                    }
                    pt.ManualUpdate = false;
                    pt.ShowDrillIndicators = false;
                    i = i + 1; 
                }
    

    The above calculates even quicker than the vba equivalent in under 4 seconds on the same data.

    Hopefully someone else finds this useful, I think its whenever the pivotfield is a cube field.

    Many thanks,

    Joe

    • Marked as answer by JoeyStyles Saturday, July 22, 2017 11:17 PM
    Saturday, July 22, 2017 11:17 PM

All replies

  • Hi JoeyStyles,

    I could reproduce your issue. I used the last piece code you provided and none of the pivots show any data. However, after removing ManualUpdate=true form the code, pivot table shows data correctly. Besides, I also suggest you removing the Subtotals directly and use ScreenUpdating to make you code run faster.

    Here is the my code now.

                Excel.Worksheet worksheet = xlApp.ActiveWorkbook.ActiveSheet;
                xlApp.ScreenUpdating = false;
                foreach (Excel.PivotTable pt in worksheet.PivotTables())
                {
                    foreach (Excel.PivotField pf in pt.PivotFields())
                    {
                        try
                        {
                          pf.set_Subtotals(1, false);
                        }
                        catch (System.Runtime.InteropServices.COMException)
                        {
                            // Do nothing
                        }
                    }
                    pt.ManualUpdate = false;
                    pt.ShowDrillIndicators = false;
                }
                xlApp.ScreenUpdating = true;

    Best Regards,

    Terry

    Friday, July 21, 2017 7:35 AM
  • Hi Terry,

    Thanks for the help as always.

    I have just tried your code and tested it, the problem is when a pivot table has multiple fields it turns the subtotals off on 1 field then updates the table then moves onto the next field turns subtotals off on that field and updates again. Even though the manualupdate=false is outside of the foreach pivotfield.

    Where as I want it to turn off subtotals on all fields then and only then it should update.

    Hopefully that makes sense.

    Many thanks,

    Joe

    Friday, July 21, 2017 8:52 AM
  • Hi Terry,

    I have figured it out, all I needed to was make sure that when the last subtotal was set to update false that it was the last field:

     foreach (Excel.PivotTable pt in worksheet.PivotTables())
                {
                    double counter = pt.RowFields.Count;
                    double pfi = 1;
                    
                    foreach (Excel.PivotField pf in pt.RowFields)
                    {
                        try
                        {
                            if (!(pfi == counter))
                            {
                                pt.ManualUpdate = true;
                            }
                            else
                            {
                                pt.ManualUpdate = false;
                            }
                            pf.set_Subtotals(1, false);
                        }
                        catch (System.Runtime.InteropServices.COMException)
                        {
                            // Do nothing
                        }
                        pfi = pfi + 1;
                    }
                    pt.ManualUpdate = false;
                    pt.ShowDrillIndicators = false;
                    i = i + 1; 
                }
    

    The above calculates even quicker than the vba equivalent in under 4 seconds on the same data.

    Hopefully someone else finds this useful, I think its whenever the pivotfield is a cube field.

    Many thanks,

    Joe

    • Marked as answer by JoeyStyles Saturday, July 22, 2017 11:17 PM
    Saturday, July 22, 2017 11:17 PM