Free Support Forum - aspose.com

Pivot Table is not generated as per the requirement


Hello Aspose Team,

I 'm trying to create the pivot table as per the requirement of the Customer using aspose.
But it is not coming up with what customer required.
Please refer the attached output excel file Test.xlsx. In Test.xlsx Summary sheet with Pivot Table is generated by using the code; source data is taken from first sheet SummaryDataSource.
And the another file Customer.xlsx (Summary sheet)is also attached which is needed by customer.
I have also attached the complete code.
Just for your information :When I try to open the Test.xlsx it gives me error: We found problem with some content in Test.xlsx. Do you want to recover. ....Yes/No?

Waiting for help desperately.

Regards,
Chandrakant

Hi Chandrakant,


Thank you for contacting Aspose support.

I have started evaluating your presented scenario by taking the Customer.xlsx as required data representation, and I have noticed that if I add another Pivot Table to the Customer.xlsx and save the result as another spreadsheet, the resultant file cannot be loaded with Excel application. In fact, the Excel removes the XML parts related to the Pivot Tables. Please check the attached snapshot for your reference. My suspicion is that the original Pivot Table uses the external data source as found to be “[TEST.xlsx]SummaryDataSource’!$A$1:$Y$1088” present on “D:\Ebilling\EBilling_Invoicing\resultlocation\2015-08-31\14012” location. In order to by pass this problem, I have manually copied the data onto a new Workbook using the Excel application and added a Pivot Table to it. In this case, the Excel still tries to repair the resultant spreadsheet by altering the records in “/xl/pivotCache/pivotCacheDefinition0.xml”.

Please allow me some more time to look further into this matter, and get back to you with updates.

Thanks Babar Raza !!For your quick response.

Waiting for further updates.
Regards,
Chandrakant

Hi Chandrakant,

This is to update you that we have done further testing, however we were not able to avoid the Excel’s repair warning while loading the resultant spreadsheet therefore we have logged this incident in our bug tracking system as CELLSNET-43877. Please note, the aforesaid ticket has been logged to address the problem related to the Excel’s repair warning by adding/creating Pivot Table. Once we sort out this problem, we will look further into the requirement of creating a Pivot Table with desired layout.

Please allow the product team to further look into the details of this problem and we will keep you updated on the status of correction. We apologize for your inconvenience.

Hello


Any progress on the ticket CELLSNET-43877.

Regards,
Chandrakant

Hi Chandrakant,

I am afraid, we haven’t yet received any updates regarding the aforesaid ticket. You have to spare us little more time as the ticket is currently pending for investigation and is in the queue with other priority tasks. That said, I have recorded a note to the aforesaid ticket, requesting the concerned member of the product team to share the insight of the problem. As soon as we receive any news, we will post here for your kind reference.

Hi again,

This is to inform you that the ticket logged earlier as CELLSNET-43877 has been marked resolved by the product team. We will shortly provide the fix here after ensuring the quality and incorporating other enhancements.


It sound good .It would save me.
I would appreciate if I could get the solution today as I have to complete the task.
Regards,
Chandrakant

Hi,


Hopefully, we will publish the next .NET fix within in the next couple of days, so kindly schedule your tasks accordingly

Keep in touch.

Thank you.

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.5.2.4 and let us know your feedback.

Thank You Very Much!!

Let me check and test.
Will get back soon with the results.

Regards,
Chandrakant

Hi ,


Earlier I had not used the Aspose.Cells.tlb.
Can you please tell me how to utilize it.

Regards,
Chandrakant

Hi,

Well, Aspose.Cells.tlb is a type library, you do not need this file. Well, a type library (.tlb) is a binary file that stores information about a COM or DCOM object's properties and methods in a form that is accessible to other applications at runtime. Using a type library, an application can determine which interfaces an object supports, and invoke an object's interface methods. In other words, if you wish to call a .NET assembly from a COM client (like VB6 etc.), you need to first register the original component i.e; Aspose.Cells.dll using the RegAsm utility. Then a reference to type library tell the IDE what objects are COM accessible. In your scenario, you will not need the type library file.

Thank you.

Thanks for your support.

I used the updated Aspose.Cells.dll.
Warning is not coming at the time of opening the file but the pivot table is still not getting constructed as per the desired layout.

Regards,
Chandrakant

Hi Chandrakant,


Thank you for the confirmation on previously reported problem. It is good to know that you are not seeing the Excel warning while adding Pivot Table to the worksheet. Regarding the layout of the newly created Pivot Table, we are working on your requirement, however, please confirm that you want the layout as Summary of Customer.xlsx.

Hello,


Yes I need the layout as per the Summary sheet of Customer.xls.

Regards,
Chandrakant






Hi Chandrakant,


Thank you for the confirmation.

Please check the following piece of code that creates a new Pivot Table by added a new worksheet to the collection in the same layout as of the Pivot Table in the Summary worksheet. I have also attached the resultant spreadsheet here for your review, moreover, the changes in the code are highlighted in bold for future reference.

C#

LoadOptions loadOptions = new LoadOptions();
Workbook workbook = new Workbook(“D:/customer.xlsx”, loadOptions);
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
sheet2.Name = “Summary 2”;
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
int index = pivotTables.Add("=" + workbook.Worksheets[0].Name + “!A1:Y” + (workbook.Worksheets[0].Cells.MaxDataRow + 1), “B3”, “PivotTable1”);
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “AU No”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Debtor/Search Name”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Obligor No”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Date/Time”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “Fee”);
if (pivotTable.DataField != null)
{
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
}
int rowFieldsCount = pivotTable.RowFields.Count;
for (int j = 0; j < rowFieldsCount; j++)
{
PivotField f = pivotTable.RowFields[j];
f.ShowCompact = true;
f.ShowInOutlineForm = true;
f.ShowSubtotalAtTop = true;
}
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium9;
workbook.Save(“D:/output.xlsx”);

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


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

Many Many Thanks to Aspose Team,You all did a great job to save my life.

Since its a festival time here; office was closed then came weekend; yesterday was busy with other stuff.
Today I checked the code you provided.It is working great but still a small issue with blank record in the field “AU NO”.
Can you please give me an idea to remove those blank records from the sheet.
Please refer attached .png file.

Regards,
Chandrakant

Hi Chandrakant,


Thank you for your nice gesture. Regarding your recent concerns, I have checked the output.xlsx generated on my side and I was not able to find the blank fields in the Pivot Table so I suspect you are operating on a different spreadsheet. Please share the sample spreadsheet showing the blank fields, we will check and share the possible solution with you. If you have changed/modified the code to generate the Pivot Table then please share the modified code too.