Pivot Table is not generated as per the requirement

Aspose.Cells Product Family
21

Hello Again,


I have a to generate the output.xls by reading the input.csv file and using template.xls.
Output.xls should contains the below worksheets
1.Invoice Total
2.Summary
3.Invoice_recap (Inv456_Recap where 456 is invoice no)

Step 1 : Read input.csv and covert it into dataset.
Step 2 : Copy the template.xls to the output folder as output.xls.
Step 3 : Add,Remove,Rename,Sort some colums of datatable (Only one datatable would be there in dataset).
Step 4 : In the output.xls (a copy of template) update the highlighted Invoice Total 's cells from dataset.
Step 5 : Generate the Inv_recap by importing the datatable (Which would be a source for Summary worksheet)
Step 6 : Create the pivot table in Summary worksheet.

While generating the pivot table the blank record row is appearing.
Please refer the attachments.

Regards,
Chandrakant
22

Hi Chandrakant,


Thank you for sharing the samples.

I have checked this scenario and found that you are incorrectly setting the Pivot Table’s data source. Please note, in Output.xlsx, the data source is set to Inv02311698_Recap!$A$1:$Y$10871 where there are only 1088 data rows in the worksheet Inv02311698_Recap. If you include those empty rows in the Pivot Table’s data source, they will surely show in the Pivot Table as well.

Please use the following statement to add the Pivot Table in your code. Please also check the resultant spreadsheet for worksheet Summary 2.

C#

int index = pivotTables.Add("=" + workbook.Worksheets[“Inv02311698_Recap”].Name + “!A1:Y” + (workbook.Worksheets[“Inv02311698_Recap”].Cells.MaxDataRow + 1), “B3”, “PivotTable1”);
23

Hi Babar,
Yes you are correct AU NO field has blank record then it should show the fee for the blank record.


Regards,
Chandrakant

24

Hi Chandrakant,


Thank you for your understanding. Please feel free to contact us back in case you need our further assistance with Aspose APIs.