Issue when removing data source in Pivot table

Aspose.Cells Product Family
1

Hi,

I am creating a Pivot table using aspose.cells for .Net. after creating the Pivot table , i want to remove the source data for that Pivot to reduce the size of the file. But after removing the source data it is not working. it should not be the case as the source data should be saved in Pivot cache.

Any body have any idea to fix this issue.

Thanks and Regards

Thomas Joseph

2

Hi,


I think you may try to call PivotTable.CalculateData and PivotTable.RefreshData method after creating the pivot table in the file and then lastly delete / remove your desired source data range. See the sample code below.

Sample code:

//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
//Setting the value to the cells
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(“Sport”);
cell = cells[“B1”];
cell.PutValue(“Quarter”);
cell = cells[“C1”];
cell.PutValue(“Sales”);
cell = cells[“A2”];
cell.PutValue(“Golf”);
cell = cells[“A3”];
cell.PutValue(“Golf”);
cell = cells[“A4”];
cell.PutValue(“Tennis”);
cell = cells[“A5”];
cell.PutValue(“Tennis”);
cell = cells[“A6”];
cell.PutValue(“Tennis”);
cell = cells[“A7”];
cell.PutValue(“Tennis”);
cell = cells[“A8”];
cell.PutValue(“Golf”);
cell = cells[“B2”];
cell.PutValue(“Qtr3”);
cell = cells[“B3”];
cell.PutValue(“Qtr4”);
cell = cells[“B4”];
cell.PutValue(“Qtr3”);
cell = cells[“B5”];
cell.PutValue(“Qtr4”);
cell = cells[“B6”];
cell.PutValue(“Qtr3”);
cell = cells[“B7”];
cell.PutValue(“Qtr4”);
cell = cells[“B8”];
cell.PutValue(“Qtr3”);
cell = cells[“C2”];
cell.PutValue(1500);
cell = cells[“C3”];
cell.PutValue(2000);
cell = cells[“C4”];
cell.PutValue(600);
cell = cells[“C5”];
cell.PutValue(1500);
cell = cells[“C6”];
cell.PutValue(4070);
cell = cells[“C7”];
cell.PutValue(5000);
cell = cells[“C8”];
cell.PutValue(6430);

PivotTableCollection pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", “E3”, “PivotTable2”);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
//Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);

pivotTable.RefreshData();
pivotTable.CalculateData();

cells.DeleteRange(0, 0, 7, 2, ShiftType.None);

workbook.Save(“e:\test2\pivotrange.xls”);

Note: However, if you delete the whole range including field labels, you might get an error in MS Excel e.g:
“The pivot field name is not valid. To create a pivottable report, yo must use data that is organized as a list with labeled columns. If you are changing the name of a pivot field…”.
This is MS Excel’s behavior and nothing to do with Aspose.Cells.




Thank you.

3

Thank you for the quick response.

when i am removing the source data tab i am getting the error which you have mentioned.

I am trying to do the same thing which is mentioned in the below article. Theoretically it should work, am i missing something?

http://datapigtechnologies.com/blog/index.php/cut-the-size-of-your-pivot-table-workbooks-in-half/

4

Hi,

I have tested the code provided by Amjad using the latest version:
Aspose.Cells
for .NET v7.2.1.6
. I think, the message is unavoidable, we will look into it.

If we could get rid of message, we will provide you a fix.

We have logged this issue for investigation. Once the issue is resolved or we have some other update for it, we will let you know asap.

This issue has been logged as CELLSNET-40725.

5

Thank you. Is there any way i can track the status of the ticket?

What is the best number or way to get hold of support directly? we have Aspose license.

6

Hi,

Thanks for your posting.

You can request us for updates but you cannot directly access the issues inside our issue tracking system.

Your issue has been logged as a normal user. It will take 2~3 days normally if your issue is easier to be fixed and it could take a couple of weeks if it is difficult.

If you had purchased priority support, then we would log your issue as a priority user. In that case a Priority Support Icon would have appeared under your username.

Below is a Priority Support Icon.

7

Hi,

Please download and try the latest fix: Aspose.Cells for .NET v7.2.1.7

Note:
However, if you delete the whole range including field labels, you might get an error in MS Excel e.g:

“The pivot field name is not valid. To create a pivottable report, yo must use data that is organized as a list with labeled columns. If you are changing the name of a pivot field…”.

This is MS Excel’s behavior and nothing to do with Aspose.Cells.

If the RefreshDataOnOpeningFile flag of pivot table is true, you will also get this error when deleting the whole range of the pivot table’s source data manually using MS Excel.

So, this is MS Excel’s behavior.

You could see the template files in the attachments.

If you find other issues, please give us your template file and sample code.

8

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

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