Free Support Forum - aspose.com

RefreshData doesn't sort data before opening file


#1
 Workbook wb = new Workbook(@"../../APP_DATA/UnsortedSamleData1.xlsb");
        Worksheet ws = wb.Worksheets[1];



        var pTable = ws.PivotTables[0];

        pTable.RefreshDataFlag = true;

        pTable.RowFields[2].IsAutoSort = true;
        pTable.RowFields[2].IsAscendSort = false;
        pTable.RowFields[2].AutoSortField = 43;

       //pTable.RefreshDataOnOpeningFile = true;
        pTable.RefreshData();           
        pTable.CalculateData();
      
        //Need ptable to be sorted by here. We will be using the sorted index for grouprows for accounts with empty amounts in the next code.  
        //Currently ptable is not sorted and is messing up with groupsrows

        wb.Save(@"../../APP_DATA/Output.Xlsb", SaveFormat.Xlsb);

        //it sorts fine once the excel is opened means RefreshDataOnOpeningFile is working fine.

#2

@LakshmiAys,

Thanks for your query.
Please provide your sample file along with the detailed description of issue with the help of images/snapshots. We will reproduce the problem and provide our feedback after analysis.


#3

Thanks for the response. I am uploading the sample file and my findings.RefreshSortinghappensOnlAafterOpeningfile.zip (489.5 KB)


#4

@LakshmiAys,

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-46484 - RefreshData doesn’t sort data before opening file in Excel

#5

Is there any update on this?


#6

@LakshmiAys,

This issue is still pending and we will write back here once any feedback is ready to provide here.


#7

I am adding more details as when it works and when it doesn’t work. When autosortfield is set to itself it works fine means it sorts before opening the file. It doesn’t work when autosortfield is set to LTM. Also attached the sample code.SortIssueBeforeOpeningtheFile.zip (9.0 MB)

Below is the code

Workbook wb = new Workbook(@"…/…/APP_DATA/UnsortedSamleData1.xlsb");
Worksheet ws = wb.Worksheets[1];

        var pTable = ws.PivotTables[0];

        pTable.RefreshDataFlag = true;

        pTable.RowFields[2].IsAutoSort = true;
        pTable.RowFields[2].IsAscendSort = false;

        // When Pivot table is refreshed, accoutdescription is sorted correctly before we open the file when we set autosortfield to itself (-1 means accountdescription)
        pTable.RowFields[2].AutoSortField = -1; // this works before opening

        //   Sorting is not done before opening the excel file when accountdescription is sorted by column LTM (index 43). It sorts correctly after opening the excel file
        //pTable.RowFields[2].AutoSortField = 43; // works only after opening file


        pTable.RefreshData();
        pTable.RefreshDataFlag = true;
        pTable.CalculateData();
        pTable.RefreshData();

        var accounts = new List<string>();           

        var headerRow = pTable.TableRange1.StartRow + 1;    // Add 1 for the Values heading, this is the first row with column headers
        var endRow = pTable.TableRange1.EndRow + 1; // Correct for 1-based Aspose/Excel index
        var startColumn = pTable.TableRange1.StartColumn;

        //test the accounts if they are sorted the way it looks after opening. Which are sorted when AutoSortField = -1
        for (int pivotRowIndex = headerRow; pivotRowIndex < endRow; pivotRowIndex++)
        {
            int AccountDescColumnIndex = 3;
            string description = ws.Cells[pivotRowIndex, AccountDescColumnIndex].StringValue;
            accounts.Add(description);
            }

        // when accounts are debugged accounts are sorted in the code before opening file when autosortfield is set to self which is AccountDesc. This doesn't 
        //work when autosortfield is set to LTM before opening file, only sorts after opening.

        wb.Save(@"../../APP_DATA/Output.Xlsb", SaveFormat.Xlsb);

#8

@LakshmiAys,

Thanks for providing more description.

This information is recorded with the ticket for our reference and will be used while working on this issue. We will write back here once any feedback is ready to share.


#9

@LakshmiAys,

We did evaluate your issue in details. I am afraid, we cannot support sorting the PivotField via other DataField. We will support it later on. But we have no plan for it now.

Once we have any new information, we will share it with you.


#10

I am not sure if this is understood correctly. Sorting the PivotField via other DataField works fine after opening the file but sort is not done before in the code after refresh. It does only after opening excel file. While it works fine when PivotField is set to itself.
We need the sorting before in the code to do grouping of accounts with all 0 zero amounts. Need to know the sorted indexes ahead so that we can set the row grouping for accounts with all periods having zeros.

I put the code to test accounts sort before and after refresh in both scenarios ( when set to any datafield and when set to itself)

LoadAsposeLicenses();

        Workbook wb = new Workbook(@"../../APP_DATA/UnsortedSamleData1.xlsb");
        Worksheet ws = wb.Worksheets[1];

        var pTable = ws.PivotTables[0];

        pTable.RefreshDataFlag = true;

        pTable.RowFields[2].IsAutoSort = true;
        pTable.RowFields[2].IsAscendSort = false;

        // When Pivot table is refreshed, accoutdescription is sorted correctly before we open the file when we set autosortfield to itself (-1 means accountdescription)
        pTable.RowFields[2].AutoSortField = -1; // this works before opening

        //   Sorting is not done before opening the excel file when accountdescription is sorted by column LTM (index 43). It sorts correctly after opening the excel file
        //pTable.RowFields[2].AutoSortField = 43; // works only after opening file
        //another datafield july 15. It doesn't work either until the file is opened
        //pTable.RowFields[2].AutoSortField = 6;

        var accountsBeforeRefresh = new List<string>();

        var headerRow = pTable.TableRange1.StartRow + 1;    // Add 1 for the Values heading, this is the first row with column headers
        var endRow = pTable.TableRange1.EndRow + 1; // Correct for 1-based Aspose/Excel index
        var startColumn = pTable.TableRange1.StartColumn;

        //test before refresh. The accounts are still not sorted
        for (int pivotRowIndex = headerRow; pivotRowIndex < endRow; pivotRowIndex++)
        {
            int AccountDescColumnIndex = 3;
            string description = ws.Cells[pivotRowIndex, AccountDescColumnIndex].StringValue;
            accountsBeforeRefresh.Add(description);
        }


        pTable.RefreshData();
        pTable.RefreshDataFlag = true;
        pTable.CalculateData();
        pTable.RefreshData();

        var accountsAfterRefresh = new List<string>();
        //test  after refresh the accounts are sorted the way it looks after opening when AutoSortField = -1. It doesn't sort in the code when AutoSortField = 43, in this case it sorts after opening the file.
        for (int pivotRowIndex = headerRow; pivotRowIndex < endRow; pivotRowIndex++)
        {
            int AccountDescColumnIndex = 3;
            string description = ws.Cells[pivotRowIndex, AccountDescColumnIndex].StringValue;
            accountsAfterRefresh.Add(description);
            }
      
        //does't work when autosortfield is set to LTM before opening file, only sorts after opening.

        wb.Save(@"../../APP_DATA/Output.Xlsb", SaveFormat.Xlsb);

#11

@LakshmiAys,

Thanks for providing feedback.

We have logged your concerns with the ticket and will analyze it soon.


#12

Thank you.

This is related to “CELLSNET-46387 - Problem sorting pivot table” which was fixed means it sorts
after opening the file. But we want pivottable to be sorted after CaculateData().

In the sample code when uncomment the below line (pTable.RefreshDataOnOpeningFile is true by default)
pTable.RowFields[2].AutoSortField = 43;

sort works after opening file Output.Xlsb. Need it even before in the code after calling CaculateData().


#13

@LakshmiAys,

Thanks for more clarification. We will consider this requirement during analysis.


#14

@LakshmiAys,

When you open the resultant file via MS-Excel, MS-Excel will refresh the PivotTable again. So you can get the correct results when sorting the PivotField via other DataField. We cannot support sorting the PivotField via other DataField for now (as we told you earlier). So you cannot get the correct results after calling PivotTable.CalculateData() method.