PivotFieldDataDisplayFormat.PercentageOfColumn in xslx and pdf

Aspose.Cells Product Family
1

Hi

We intend to develop a web based aspx applications using Aspose.cells to generate Excel files. The tool is still in evaluation in our company.

The Excel file will be generated on a web server and streamed to our 15 sale representatives in France, Germany, Austria, Denmark and Switzerland.

We also want to be able convert the streamed Excel files to pdf. To achieve this we tried to use SaveFormat.Pdf instead of SaveFormat.Xlsx. This works ok as long as we have simple Excel sheets. If I create a pivot table with a field like:

PivoTable.AddReturnFieldToArea(PivotFieldType.Data, “Fieldname”, PivotFieldDataDisplayFormat.PercentageOfColumn);

The PercentageOfColumn is calculated correctly in the xlsx file but not in the pdf. A data field value=1343 is shown as 134300.00% (wrong) in pdf and 6.82% (correct) in xslx.

Is there a way to achieve the same output in pdf as in Excel for these PercentageOfColumn pivot fields?

Jacques

2

Hi,


Could you try to download and use our latest version/fix: Aspose.Cells for .NET v7.7.2.2 if it makes any difference. If you still find the issue, kindly provide your saved XLSX file and output PDF file here, we will check it soon.

Thank you.

3

Hi

Here is a quick example in aspx/c# and also the xslx and pdf outputs I get. I used aspose.cells 7.7.2.2 as suggested.

press the xslx button and you will get a correct pivot table

press the pdf button and you will get a pdf with incorrect percentages

Jacques

4

Hi Jacques,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue. The code gives the output pdf which has incorrect percentages in the pivot table. We have tested this issue with the following code using the latest version but the problem still occurs. We have attached the output files for a reference.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42427.

C#


Workbook wb = new Workbook(FileFormatType.Pdf);

Worksheet ws = wb.Worksheets[wb.Worksheets.Add()];

ws.Name = “Sheet”;


ws.Cells[0, 0].PutValue(“X”);

ws.Cells[0, 1].PutValue(“Y”);

ws.Cells[0, 2].PutValue(“Data”);


ws.Cells[1, 0].PutValue(“A”);

ws.Cells[1, 1].PutValue(“C”);

ws.Cells[1, 2].PutValue(10);


ws.Cells[2, 0].PutValue(“A”);

ws.Cells[2, 1].PutValue(“D”);

ws.Cells[2, 2].PutValue(25);


ws.Cells[3, 0].PutValue(“B”);

ws.Cells[3, 1].PutValue(“C”);

ws.Cells[3, 2].PutValue(30);


ws.Cells[4, 0].PutValue(“B”);

ws.Cells[4, 1].PutValue(“D”);

ws.Cells[4, 2].PutValue(45);


PivotTableCollection ptc = ws.PivotTables;

int index = ptc.Add("=Sheet!A1:C5", “A7”, “PivotTable1”);

PivotTable pt = ptc[index];


int fp; PivotField pf;


fp = pt.AddFieldToArea(PivotFieldType.Row, “X”);

pf = pt.Fields(PivotFieldType.Row)[fp];


fp = pt.AddFieldToArea(PivotFieldType.Column, “Y”);

pf = pt.Fields(PivotFieldType.Column)[fp];


fp = pt.AddFieldToArea(PivotFieldType.Data, “Data”);

pf = pt.Fields(PivotFieldType.Data)[fp];


fp = pt.AddFieldToArea(PivotFieldType.Data, “Data”);

pf = pt.Fields(PivotFieldType.Data)[fp];

pf.DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOfColumn;


pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);

pt.RefreshData();

pt.CalculateData();


wb.Save(“out.xlsx”);

wb.Save(“out.pdf”);

5

Hi

Since it is 2 weeks now I posted an enquiry for this bug, I would like to know if you made any progress on fixing this bug.

Aspose.cells has made a good impression on our internal clients, but still it remains important for us to have a reliable pdf output of the generated excel.

Yours faithfully

Jacques

6

Hi Jacques,

Thanks for your posting and using Aspose.Cells.

We are afraid, your issue is still unresolved and there is no update for you. However, we have logged your comments in our database against this issue and requested the development team to provide some ETA or update for this issue. Once, there is some fix or update for you, we will let you know asap.

7

Howdy

I think this bug is not yet fixed. Our end users would appreciate to be able to generate directly a pdf equivalent of an xlsx file. Is there any chance to see this problem solved in the near future?

8

Hi Jacques,

Thanks for your posting and using Aspose.Cells.

We have evaluated this issue extensively and we are afraid, we could not fix it and now have no plan to fix it in future. We are sorry for inconvenience.

However, this issue is still open, in case there is some further update for you, we will let you know by posting in this thread.

9

Hi Jacques,

Thanks for using Aspose.Cells.

This is actually a New Feature which is not supported by Aspose.Cells. When you open the “out.xlsx” file, MS-Excel will refresh the PivotTable
automatically, so you will get the result that you want.



But when you export to pdf format, the data is the result that we calculate so PDF is different form XLSX file.



We will support it later.

10

@chosjsc,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.