Pivot error while opening the excel without saving

Aspose.Cells Product Family
1

Hi Aspose Team,

We are facing one issue while opening an excel file(consisting of Pivot table in one of the sheet).

While the excel sheet is generated we are presented with options to open, save or cancel.

If we choose the option to open the file then we get error saying "Cannot open Pivot table source file 'All_cofunds(1).xls'Data". (please refer attached word doc for screen shots, also attaching the excel file. "Data" sheet is the source for Pivot table data).

Please note that we are using the "attachment" option for the respose.setHeader in our web application (refer below line of code)

response.setHeader("Content-disposition","attachment; filename=\"" + reportName + ".xls\"");

Also. we were able to simulate the issue with the Web-based demo application provied by Aspose.

Regards,

Muin

2

Hi,


Please check the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Pivot+Table+Issue


I think you my try to change your line of code,e .g

response.setHeader("Content-disposition","inline; filename=\"" + reportName + ".xls\"");"
if it works fine for you.

Thank you.

3

Hi,

We tried using SaveType.OPEN_IN_BROWSER option while saving the file, but it did not work. We are getting the error (please refer attachemnt) while clicking on Open option (from Open, Save or Cancel options). Is there any other code that we are missing?

To add, we are getting the same error even if we try to save the file and then open it (please find attached the excel file as well)

We dont want to use "inline" option in response.setHeader as using this will not provide the Open, Save or Cancel options while the file is generated. Instead it directly tries to open the file and we dont want that. We want to give user the option of either directly opening the file or saving it to the disk.

Please suggest.

Regards,

Muin

4

Hi,


Please download and try our latest version: Aspose.Cells for Java v7.2.1.8

If you still find the issue with the latest version/fix, please create a sample console (runnable) Java program to reproduce the issue on our end, kindly attach the Java code file here. Please also attach your template files(input + output etc.) here. We will check your issue soon.

Thank you.

5

Hi,

We are already using the latest version of Aspose Aspose.Cells for Java v7.2.1.8

Please find attached the sample code (DemoBaseServlet.txt, PivotTableServlet.txt) in the zip file. Like I said before we were able to simulate this with the web-based demo application which is available on Aspose site.

1.With the attached code

When the report is generated we get Open, Save and Cancel options/buttons. If we click on Open button then we are getting error message and the excel sheet does not consist of pivot table (refer Pivot_error.doc for error message screen shot and PivotTable_Error.xls). But if we select on Save button and save the excel file and then open it then it is fine (no issues here). But we want the excel to open (along with pivot table) without saving also.

2. With the suggested fix

If we change the line 26 in the file DemoBaseServlet.txt to the suggested fix i.e,

wb.save(response.getOutputStream(), SaveType.OPEN_IN_BROWSER);

the generated excel cannot be interpreted. It opens with some error messages and with some junk values (refer Pivot_Browser_error.doc and PivotTable_Browser.xls).

Please provide fix for this.

Awaiting your response.

Thanks and Regards,

Muin

6

Hi,


Thanks for the files and screen shots.

I think if an Excel file has pivot table in it, then you have to use “inline” option as I have given the link to “Pivot Table Issue” document in my previous reply. e.g
response.setContentType(contentType);
response.addHeader(“content-disposition”, “inline;filename=” + fileName);

By the way, I can open your attached file “PivotTable_Browser.xlsx” into Ms Excel, see the attached image.

Since you are finding some junk data via using “inline” option for which I am not complete sure about it. Anyways, I have logged a ticket for your issue with an id: CELLSJAVA-40209. We will look into it soon. Once we have any update about it, we will let you know here.

Thank you.
7

Hi,

For your questions:

  1. For the pivot table issue when opening generated file in the browser, please also use “POST” method instead of “GET” method when sending the request to server to get the generated file. Without those special steps, even simply sending the stream of an Excel created file to client also may cause such kind of issues.

  2. For the generated file cannot be interpreted, it is because you gave incorrect file format for Workbook.save(). The constants for saved file format should be those mentioned in SaveFormat interface, not SaveType.

Thank you.

8

Hi,

I guess we are loosing the main issue here. Let me just reiterate the issue

Issue :

We are facing one issue while opening an excel file(consisting of Pivot table in one of the sheet).

While the excel sheet is generated we are presented with options to open, save or cancel.

If we choose the option to open the file then we get error saying "Cannot open Pivot table source file 'All_cofunds(1).xls'Data".Please note that we are using the "attachment" option for the respose.setHeader in our web application (refer below line of code)

response.setHeader("Content-disposition","attachment; filename=\"" + reportName + ".xls\"");

Your sugestion was:

To use "inline" instead of attachment and the link provided by you suggested to use SaveType and not SaveFormat. But this did not work for us.

The problem with using "inline" option is that the user is not presented with dialog box with Open, Save and Cancel buttons. We want to retain this option at any cost. If we use "inline" then it directly opens the workbook/excel which is not desired (though the pivot table appears here).

So can you please tell us how to go about this?

You can send a sample code (web-based) to suggest how to create a workbook with pivot table with dialog box Open, Save and Cancel buttons.

Regards,

Muin

9

Hi,


As the issue mentioned in the document here:
http://www.aspose.com/docs/display/cellsnet/Pivot+Table+Issue


Well, one should use Inline instead of Attachment option. The issue is not related to Aspose.Cells rather it is routine of the browser (e.g IE) that is also mentioned there.

Anyways, we have logged your feedback and attach it with your existing ticket "CELLSJAVA-40209", we will further explore and investigate if something can be done or is there any other workaround, we will get back to you soon.
Once we have any update, we will let you know.

Thank you.