Is there any way to get the Scripts property of an Excel worksheet using Aspose? Specifically I need to be able to get the id property of worksheet scipts.
Thanks,
Adam
Hi Adam,
Well, sorry but we do not understand your requirement completely. Could you elaborate and explain it more, which Scripts you are talking about and which id property you have mentioned. We appreciate if you could provide us complete details, so that we can understand you and reply you accordingly.
Thank you.
We’re attempting to switch from using office to aspose for dynamically populating documents in our system. Don’t ask why but we have code like this:
Excel.Sheets sheets = wb.Worksheets;
foreach(Excel.Worksheet ws in sheets) {
bool foundScript = false;
Office.Scripts scripts = ws.Scripts;
Office.Script s;
for(int i = 1; i <= scripts.Count; i++) {
s = scripts.Item(i);
String id = s.Id;
if(id == “SHEET_SNAPSHOT”) {
foundScript = true;
ReleaseCOMObject(s);
ReleaseCOMObject(id);
break;
}
else {
ReleaseCOMObject(s);
ReleaseCOMObject(id);
}
}
That we need to duplicate using aspose. Specifically we need to check the ID of all the scripts in the worksheet. Is this possible?
Thanks,
Adam
Hi Adam,
Thanks for providing us further details.
We will check it and get back to you soon.
Thank you.
Hi Adam,
Kindly post the template file whose scripts contain "SHEET_SNAPSHOT", we will check it ASAP.
Thank you.
Here you go. Thanks.
Hi Adam,
Thank you for providing us the template file. We will check it and get back to you soon.
Thank You & Best Regards,
Hi Adam,
After checking your template file, we find "SHEET_SNAPSHOT" is a part of the shape name("RESWARE_SHEET_SNAPSHOT"). And, we tested scripts with Excel interop, we find the id of the script is "RESWARE_SHEET_SNAPSHOT" too. See following codes:
string fileName = @"F:\FileTemp\TX_-_Tax_Statement.xls";
Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook workbookXL = xlApp.Workbooks.Open(
fileName,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
foreach(Microsoft.Office.Interop.Excel.Worksheet ws in workbookXL.Worksheets)
{
Microsoft.Office.Core.Scripts scripts = ws.Scripts;
for(int i = 1; i <= scripts.Count; i++) {
Console.WriteLine(scripts.Item(i).Id);
}
}
So if you want to find this object with Aspose.Cells, please try the following codes:
Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\TX_-_Tax_Statement.xls");
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
Worksheet sheet = workbook.Worksheets[i];
for (int j = 0; j < sheet.Shapes.Count; j++)
{
if (sheet.Shapes[j].Name.IndexOf("SHEET_SNAPSHOT") != -1)
{
Console.WriteLine(sheet.Shapes[j].Name);//RESWARE_SHEET_SNAPSHOT
}
}
}
Thank you.
Thanks this is helpful, but I think I have found a bug. If I make a copy of the worksheet using AddCopy(), then I changed the shape name in the new worksheet, the shape name in the old worksheet is also changed. Here’s the code I am using:
// Go through each worksheet to see if it needs a snapshot
Worksheets worksheets = workbook.Worksheets;
//don’t set this in the for loop because it changes when we add new sheets
int workSheetsCount = worksheets.Count;
for (int i = 0; i < workSheetsCount; i++) {
Worksheet ws = worksheets[i];
bool foundScript = false;
for (int j = 0; j < ws.Shapes.Count; j++) {
if (ws.Shapes[j].Name.IndexOf(“RESWARE_SHEET_SNAPSHOT”) != -1) {
foundScript = true;
break;
}
}
// Take our snapshot
if (foundScript) {
//copy the sheet marked for snapshot to a new sheet
int index = ws.Index;
int newIndex = worksheets.AddCopy(index);
//new name of snapshot is date + sheet name
string desiredName = DateTimeController.Instance.GetDateTime().ToString(“M-d-yy”) + " "
+ ws.Name;
// Go through all the worksheets that exist and see if this name is already on there
string actualName = desiredName;
int tryCount = 0;
while (ExcelSheetNameExists(actualName, workbook)) {
tryCount++;
actualName = desiredName + " (" + tryCount.ToString() + “)”;
}
Worksheet newWs = worksheets[newIndex];
newWs.Name = actualName;
//remove snapshot flag from new sheet so it doesn’t get snapshotted next time
for (int j = 0; j < newWs.Shapes.Count; j++) {
if (newWs.Shapes[j].Name.IndexOf(“RESWARE_SHEET_SNAPSHOT”) != -1) {
//when I set the name to “” here, ws.Shapes[j].Name is also changed…this is a problem!
newWs.Shapes[j].Name = “”;
break;
}
}
}
}
Hi,
Thanks for providing us details and pointing out the issue.
We will figure it out soon.
Thank you.
Hi,
Thank you for considering Aspose.
Please try the attached latest fix. We have fixed the bug of coping shapes.
Thank You & Best Regards,
Thanks for the quick response however with the new dll I am now getting an ArgumentOutOfRangeException at the call to AddCopy…
System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Server stack trace:
at System.Collections.ArrayList.get_Item(Int32 index)
at Aspose.Cells.MsoDrawing.?.get_?(Int32 ?)
at Aspose.Cells.Picture.get_?()
at Aspose.Cells.Picture.?(Picture ?)
at Aspose.Cells.Shapes.?(Shape ?, GroupShape ?, ? ?)
at Aspose.Cells.Shapes.?(Shapes ?, ? ?)
at Aspose.Cells.Worksheet.?(Worksheet ?, ? ?)
at Aspose.Cells.Worksheet.?(Worksheet ?, Boolean ?, ? ?)
at Aspose.Cells.Worksheet.Copy(Worksheet sourceSheet)
at Aspose.Cells.Worksheets.AddCopy(Int32 sheetIndex)
at Adeptive.ResWare.Server.Controllers.DocumentControllerAspose.PopulateExcelTemplateDocument(Object theTemplatePath, Int32 theFileID, Int32 theUserID, Int32 theOfficeID, Int32 theLedgerEntryID, Int32 theCommonSettlA first chance exception of type ‘System.ComponentModel.Win32Exception’ occurred in System.dll
ementID, Int32 thePolicyEndorsementID, Int32 theSigningNumber, Int32 theDocumentID, Boolean theCreateSnapshot) in C:\Sandbox\ResWareHead\ResWare\Server\Controllers\DocumentControllerAspose.cs:line 941
Hi,
We will look into your issue soon.
Thank you.
Hi,
Well, we tested the Worksheets.AddCopy() method but could not find the issue you have described. Could you post your template file with sample code to reproduce the issue, we will check it soon.
Thank you.
Hmmm I just tried reproducing it again and now it’s working. Perhaps something was wrong with the file. I will let you know if it happens again. Thanks!
The issues you have found earlier (filed as 7446) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Laurence.
The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan