We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Unsupported function in formula calculation engine - code 358; Error in calculating cell E23 in Worksheet pivot table

I have written a process that will take a workbook and save it as HTML to one of my servers. Aspose.Cells has been working very well until I published a workbook with a pivot table.

I checked the following articles, but couldn’t find a resolution.

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/supported-formulas-functions.html

I am not sure if this means that I can calculate GetPivotData for the HTML export, or not.

http://office.microsoft.com/en-us/excel/HP052091071033.aspx

I tried changing my fields and items to strings, but this did not seem to fix the issue. Here is the formula that was in the cell:

=+GETPIVOTDATA(“Amount – Reporting”,$A$3,“Main Account Code”,“1120”,“year”,2007,“Sub Account Name2”,“Bank”)
+GETPIVOTDATA(“Amount – Reporting”,$A$3,“Main Account Code”,“1130”,“year”,2007,“Sub Account Name2”,“Bank”,“Sub Account Name3”,“Labor Costs - Job Related”)
+GETPIVOTDATA(“Amount – Reporting”,$A$3,“Main Account Code”,“1130”,“year”,2007,“Sub Account Name2”,“Bank”,“Sub Account Name3”,“Overhead Costs”)
+GETPIVOTDATA(“Amount – Reporting”,$A$3,“Main Account Code”,“1130”,“year”,2007,“Sub Account Name2”,“Bank”,“Sub Account Name3”,“Owners Labor”)
+GETPIVOTDATA(“Amount – Reporting”,$A$3,“Main Account Code”,“1140”,“year”,2007,“Sub Account Name2”,“Bank”)

Thank you for any information or help you can provide.

Also, if I remove these formulas, and generate the HTML, it appears that the original / non-pivot data has replaced my pivot table. The HTML worksheet for the original / non-pivot data appears corrupt.

example attached

Hi,

Thanks for sharing us details with template files.

Well, unless you do not call Workbook.CalculateFormula() method in your code you won’t get the error you described. The GetPivotData formula is not supported for calculation rather just for setting and reading the formula (by the Aspose.Cells calculation engine) as described in the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/supported-formulas-functions.html

The formula would not be calculated at runtime but you may set the formula using Aspose.Cells for .net and let MS Excel calculate it when opening the xls file into MS Excel. If you simply open and save the file as html using Aspose.Cells Api, but if you update some values into the cells and calls Worbook.CalculateFormula(), the error would appear (as you mentioned).

1) e.g (This works)
Workbook workBook = new Workbook();
workBook.Open(@“f:\test\pivot+table.xls”);
workBook.Save(“f:\test\outputfile.html”,FileFormatType.Html);

2) e.g (This will produce the error)
Workbook workBook = new Workbook();
workBook.Open(@“f:\test\pivot+table.xls”);
//…
workBook.CalculateFormula();
workBook.Save(“f:\test\outputfile.html”,FileFormatType.Html);


I have also attached the latest version/fix for you, if you could try.


Thank you.


Thank you for your prompt response. Could you also comment on the 2nd post, and the results I’ve attached.

Thanks again :slight_smile:

Hi

Please try our latest fix v4.8.0.11(that I attached in my previous post) and let us know if you find any issue, we will check it soon.


Thank you.

I am sorry for the delay. The updated library still exhibits the defect.

Thanks.

Hi,

I am not sure about your issue, could you elaborate in detail.Also, kindly create a sample application, zip it and post it here to show the issue, we will check it soon.


I have also attached my output html file here for your reference, here is my sample code:

Workbook workBook = new Workbook();
workBook.Open(@“f:\test\unsupported\pivot table.xls”);
workBook.Save(“f:\test\unsupported\myhtml.html”,FileFormatType.Html);

Thank you.

I will work on getting this to you, but our code is not much different.

My zip includes the workbook, and the 3 html files that come out - 1 for each worksheet.

Hi,

We will certainly wait for your sample application to reproduce the issue, so that we can look into your issue to figure it out soon.

Thanks for your work.