Hi,
We’re using Aspose.Cells for editing formatting and data for Excel documents containing web queries for updating Excel contents later by the user.
It worked correctly for us when using xls format for both input and output documents, but we can’t get it to work with any Excel 2007 formats.
For test purposes we have created simple xlsm with web query and even with simple code as below after opening generated document web query is broken, and isn’t recognized in Excel and can’t be refreshed.
Code (document has web query in 1st cell):
string path = @“manual_generated.xlsm”;
License license = new License();
license.SetLicense(“Aspose.Cells.lic”);
Workbook wb = new Workbook(path);
wb.Save(@“c:\test.xlsm”, SaveFormat.Xltm);
I’m attaching both initial document and the one generated with Aspose.Cells after just opening and saving it.
It would be great if you could let us know if it’s possible to work with your library on any Excel 2007 format while persisting web query functionality that is contained in initial document.
Regards,
Leszek
Hi,
and let us know if it works fine or not?
Hi,
Thanks for quick reply.
I’ve checked the version you posted and it works exactly the same as 7.2.0 at which I did tests earlier.
Regards,
Leszek
Hi,
Hi,
Please set OoxmlSaveOptions.ExportCellName as true.
C#
OoxmlSaveOptions saveOptions = new OoxmlSaveOptions();
saveOptions.ExportCellName = true;
workbook.Save(@“D:\FileTemp\dest.xlsm”,saveOptions);
Thanks for your answer.
I’ve tried setting those options as in code below:
string path = @“C:\manual_generated.xlsm”;
License license = new License();
license.SetLicense(“Aspose.Cells.lic”);
Workbook wb = new Workbook(path);
OoxmlSaveOptions saveOptions = new OoxmlSaveOptions();
saveOptions.ExportCellName = true;
wb.Save(@“c:\dest.xlsx”, saveOptions);
Still when opening the file afterwards I don’t have an option to refresh on cell containing web query while being able to do it using initial document.
Regards,
Leszek
Hi,
Hi,
I have tested your file with the following code using the latest version:
Aspose.Cells
for .NET v7.2.0.8
Please download the output file: manual_generated.xlsm.out.xlsm and let me know your feedback if it.
I found the issue still exists. I have attached the screenshot for a reference.
Please see the output file is now in xlsm format and not xlsx format which does not support any macros.
C#
string path = @“F:\manual_generated.xlsm”;
Workbook wb = new Workbook(path);
OoxmlSaveOptions saveOptions = new OoxmlSaveOptions(SaveFormat.Xlsm);
saveOptions.ExportCellName = true;
wb.Save(path + “.out.xlsm”, saveOptions);
Screenshot:
Hi,
I’ve checked the files you attached and there is still the same issue. When I open the initial file in Excel and right clicked on first cell (A1, or anywhere up to M10) you have web query options like refresh, edit query, etc.
In case of the second of those documents there are no such options, just some generic ones, also when using refresh all option nothing happens as opposed to initial file.
Regards,
Leszek
Hi,
Thanks for your feedback.
I was able to find the issue. I have logged the above screenshot and your comment in our database against the issue id: CELLSNET-40664
Thanks very much. It works really well now.
Unfortunatelly we just stumbled into another issue.
When generating excel documents based on some initial ones containing web queries one of operations that we’re doing is renaming worksheets. From what we’ve checked it breaks web query references to parameters (we keep it in first cell) as they have those references as =‘WorksheetName’!$column$row so they have specified worksheet through its name. When using your library we rename worksheet then this reference isn’t being updated and when trying to refresh data through web query it fails with information that it couldn’t acquire this parameter (web query parameters are in Data->Connections->Properties->Definition->Parameters, and there is defined cell for fetching that parameter).
I haven’t found any way to access web query definition and update this reference through code.
I’ve done similar test but with initial and destination document being in xls format and it worked automatically, when trying to use xls as initial document and xlsx as destination format any information about web query seems to have been lost.
Simple code:
string path = @“C:\manual_generated.xlsm”;
License license = new License();
license.SetLicense(“Aspose.Cells.lic”);
Workbook wb = new Workbook(path);
var ws = wb.Worksheets[0];
ws.Name = “b”;
OoxmlSaveOptions saveOptions = new OoxmlSaveOptions();
saveOptions.ExportCellName = true;
wb.Save(@“c:\renamed.xlsx”, saveOptions);
Thanks,
Leszek
Hi Leszek,
Hi,
We do not support parsing them now and we just simply keep them. So we do not support changing the parameters too.
This feature has been added in our development schedule.Hopefully we could support it within 2-3 months.
Thank for your understanding.
Thanks for the reply.
By the way - would it be possible for you to let us know when this functionality would be implemented e.g. by updating this thread.
Regards,
Leszek
Hi,
Yes, we will update you on this thread by posting here.
Another thing is when your issue is resolved, it will be marked as resolved in our database which will then show a resolve tag on the top left corner of this thread.
For more illustration, please see the following screenshot.
Screenshot:
Hi,
Thanks for using Aspose.Cells.
We have fixed this issue.
Please download and try this fix: Aspose.Cells for .NET v7.3.0.3 and let us know your feedback.
Please see the following sample code.
C#
book.Worksheets[0].QueryTables[0].PreserveFormatting = false;
book.Worksheets[0].QueryTables[0].AdjustColumnWidth = false;
The issues you have found earlier (filed as CELLSNET-40664) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.