The generated Excel sheet is corrupted and i could not open the excel sheet.
My requirement is that, i generate a pivot table. The data field contains some 5 columns. Now i need to add another column next to the grand total. The values for this column is calulated, which gives the difference between the grand total and sum of all the data fields leaving the first column of the datafield.
Please let me know if you could understand the problem.
Also, suppose my pivot table is placed in the area "A5:G20", i could not able to retrive the values of the cells in between these areas. If i could get these cell values, then also my problem will be solved.
Well, you requirement is not very clear. Can you please Post your template file and explain your requirement in a bit more detail, so that we can provide you with a proper solution to you issue.
I couldn't post the template, but let me explain in detail.
In pivot table you have Row fields, Column fields. The Row Grand Total and Column Grand Total fields are generated automatically.
Now my requirement is similar to the Column Grand Total, but instead of adding all the column fields, i would like to add some selected columns / in wise versa, I can also subtract some column values from the Grand Column Total.
Hi,<?xml:namespace prefix = o ns = “urn:schemas-microsoft-com:office:office” />
Thank you for Considering Aspose.
We tried to add such calculated field to the pivot table, but we were unable to do it.
It seems MS Excel could not use GetPivotData function in the calculated field of the pivot table. Please post your created file with the calculated field, so we can provide you with an appropriate solution.
Also, if your file contains confidential data, you can email to us, so we can resolve your issue. To email please follow these under mentioned steps,
1: click the Contact button in the Post.
2: In the drop down list options click "Send Nausherwan an Email”.
3: Attach the template file and send it.
4: Once you have done it, kindly confirm us on this thread.
Also, We checked the function GetPivotData in MS Excel Help, the params(“Data_Field”,“Field1”,“Item1”.etc) of the function GetPivotData should be string value. You should use your parameters as string values.
You can see the details about GetPivotData function from the following link,
Please try the attached latest version of Aspose.Cells. We have support to add Pivot calculated item to pivot field.
See the following sample code:
//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.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["D1"];
cell.PutValue("Sales1");
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);
PivotTables pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", "E20", "PivotTable1");
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Dragging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.ColumnFields[0].AddCalculatedItem("CountQtr3", "=COUNT('Qtr3')");
//"Qtr3" should be the pivot item name of the pivot field.
//Please add "'" before and after the pivot item name.Suche as 'Qtr3'.
workbook.Save(@"F:\FileTemp\dest.xls");
Following is the summary information of the PivotField.AddCalculatedItem method for your reference.
///
/// Add a calculated item to the pivot field.
///
/// The item’s name.
/// The item’s formula
///
/// Only supports to add calculated item to Row/Column field.
///
public void AddCalculatedItem(string name,string formula)
Now i have a new problem with the Pivot Table - Column - Add Calculate Item.
The Excel report is corrupted and i couldn't open my report, if i add more than one calculate item in the Pivot column field. Please consider the below code.
String[] ColumnArray = xlObj.ExcelWorkBook.Worksheets["Pivot By Region"].PivotTables[0].ColumnFields[0].Items;
I could not find the problem you have described as I added two calculated items for the column field(s). Could you post your template files(input + output etc.) and paste your code here or alternatively you may create a sample console application to reproduce the issue, we will check it soon.