I was having a few problems with the files generated by Aspose.Excel 3.2 namely:
1) If you opened the file directly in Excel without saving it and changed the worksheet name Excel prefixed it with .xls] which could not be removed until after it was saved.
2) More importantly when the pivot table in the template was refreshed (either by “refresh on open” or by code) Excel silently shutdown all copies of itself if there was a lot of data in the range used as the data source in the pivot table. Again the file was OK if the file was first saved and later opened in Excel.
I thought that I had better try the latest release (3.3.0) to see if the problem was still there but unfortunately I am now getting “Errors in Excel Save method:0” on all of the templates I am using (although they are all based upon the same one so if there is a problem in one it will probably be in all of them).
I have attached an example template that is causing the problem. Can you have a look at it to see if you can find out whats wrong?
Please try this attached fix.
Thanks for the very quick fix. The 2 important problems (Errors in Excel Save method:0 and problems refreshing the pivot table) are fixed although renaming a worksheet still appends ".xls]".
As an aside can you tell me whether you have any plans to support pivot tables in Aspose.Excel rather than just using a designer file?
Do you use OpenInExcel option? Did you specify "Result.xls" but it's open in Excel with "Result.xls"? That's the routine of IE and we cannot change it.
We did consider to support pivot tables at run time but don't make a specific plan yet because of lack of documentation.
Thanks for the reply.
I am responding to this thread because I am experiencing the pivot table refresh problem. The web user is presented with an option to open the file or save. When the user selects open for large Data sets, the Pivot Table "refresh on open" option causes excel to briefly open and then crashes. If the user saves the file first and then opens the file it works fine. I'm using 220.127.116.11 of the components. We are currently planning to roll this feature to our Production environment this Friday and would like to do it with the Pivot Table option if possible. An additional note: Small datasets do not experience this problem. I don't know what the exact cutoff is.
Could you send your problem file to firstname.lastname@example.org? I can add a property to turn off the "refresh on open" option. Could that serve your need?
Oh, such a property is not needed. You can turn off this option in your template file.
I also ran the test with "Refresh on Open" turned off and the file will open and load. However, the columns are not refreshed. The moment the end user clicks the refresh data option on the pivot table option excel crashes.
Thanks for your help,
Could you send me the file? I will check it. By the way, which version are you using? Please try this attached fix.
I'm currently testing the dll you sent and will let you know the results. I have attached a sample file. Please note that our data range changes every time and we dynamically change the named range every time.
1) We open the sample file
2) Load the data in
3) Modify the named range based on the data we loaded
One of my test datasets is 20 columns by 2600 + rows.
Update - Unfortunately the latest dll didn't make a difference. I'm fairly certain that it's not the aspose component because I do not experience the problem when performing a save first followed by an open. I'm fairly certain it's something in the way IE downloads the excel file from the website.
Thanks for your assistance,
Could you zip and send me the file with 20 columns by 2600 + rows which caused this problem? That will help us solve this problem faster. Thank you.
Is it possible to save your file on the web server and provide a link for your user?
This problem may be caused by that opening file directly will save the file in an internet temp folder.
We want to get the file directly into the user's hands because it is autogenerated while they wait on a web page. Our process is a bit complex but here is the summary:
1) User runs a report
2) User decides to have the report output in an excel file and clicks the download to excel button.
3) Our website (on linux box) accepts the request and uses java to update the database table
4) Our backend app on W2k polls the db table and picks up the request.
5) Our app opens the sample/existing excel file, puts the data into it and recreates the named range. It then uploads it into the Oracle database as a bfile and sets the done status in the table
6) Java picks up the done status, grabs the bfile from the database and sends it back to the user.
7) The user can click open or save. As we've discussed, Open causes excel to immediately crash while a save followed by open works fine.
This primarily seems to happen when the results file goes from a small range 2x6 to 20x2600 or something therein. It appears to be okay as long at the named range change isn't too significant.
I'm attaching a file as generated prior to it being loaded into the database (step 5). You will find that it opens just fine in excel .
How do you send the file back to the users?
I used the following code and find it works fine.
Aspose.Cells.License license = new Aspose.Cells.License();
// Put user code to initialize the page here
Excel excel = new Excel();
Range range = excel.Worksheets.Cells.CreateRange(0, 0, 2652, 17);
range.Name = "RCData";
excel.Save("abc.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);
I think you can try to extend the named range in your template file to let it includes larger rectangle area.
Our Java team is streaming the file back from Oracle with a contenttype\download. We are Not using the aspose streaming method at this time. Additionally, the file I sent you will open correctly in aspose and when opened directly in Excel. It is Only when we stream the file from the database back to the user and they select the open button rather than the save button from the file download dialog form that Internet Explorer provides.
Could you post your java code the streaming the file? Maybe we can find some clues.
Due to our release this weekend I am unable to post the code at this time. I will see if I can get you something next week.
Our java team provided the attached file for your testing. Let me know if you have any questions.
Thank you for your help,