将 Applescript 移植到 Scripting Bridge：无法获取 Excel 2008 中的范围值

Question

我需要将一些 Applescript 代码移动到 Scripting Bridge 以利用一些 Cocoa 挂钩，而不需要 Applescript-ObjC。

将 Excel 2008 与 Applescript 结合使用，获取范围的值很容易：

set myList to GetValuesFromColumnRange("A", 1, 100)

 on GetValuesFromColumnRange(ColumnLetter, FirstRow, LastRow) -- (string, integer, integer) as list
     set theList to {}
     tell application "Microsoft Excel"
         set theRange to ColumnLetter & FirstRow & ":" & ColumnLetter & LastRow
         set AppleScript's text item delimiters to {return}
         set theList to (get value of range theRange as list)
         set AppleScript's text item delimiters to {""}
     end tell
     set theList to ConvertItemizedListToValueList(theList) of me -- Note this dependency due to how MSE2008 returns the data
     return theList
 end GetValuesFromColumnRange

但在 Scripting Bridge 中，我在根据范围获取工作表的单元格时遇到问题。以下是我到目前为止所拥有的。

    Excel2008Application *excelApp = [SBApplication applicationWithBundleIdentifier:@"com.microsoft.Excel"];

    Excel2008Workbook *workbook = excelApp.activeWorkbook;

    SBElementArray *sheets = [workbook sheets];

    Excel2008Sheet *targetSheet;
    int thisSheet = 0;
    int lastSheet = [sheets count]; 

    for (thisSheet = 0; thisSheet < lastSheet; thisSheet++) {
        Excel2008Sheet *currentSheet = [sheets objectAtIndex:thisSheet];
        NSString *currentSheetName = currentSheet.name;
        if ([currentSheetName isEqualToString:@"Metadata"]) {
            targetSheet = currentSheet;
            [targetSheet retain];
        }
    }

    Excel2008Range *range = [[[excelApp classForScriptingClass:@"range"] alloc] initWithProperties:[NSDictionary dictionaryWithObjectsAndKeys:@"A1:A10", @"formulaR1c1", nil]];

    [[targetSheet ranges] addObject:range];
    range = [[targetSheet ranges] lastObject]; // not null; stated class in log: MicrosoftExcelRange

    Excel2008Sheet *valueSheet = range.worksheetObject; // supposed to be new worksheet based upon values within the range; not null; stated class in log: MicrosoftExcelSheet
    [valueSheet retain];

    SBElementArray *valueCells = [valueSheet cells]; // not null, but count is 0
    [valueCells retain];

问题出现在最后一行，当我实际从 valueSheet 获取单元格时，返回的 SBElementArray 不为 null，但它也不包含任何对象。获取 targetSheet 中的单元格也是如此。

从我所有的搜索中可以看出，执行此操作的文档并不存在，我已尽我所能。

Answer 1

解决了。

NSString *rangeName = @"A1:A10";

Excel2008Range *range = [[[excelApp classForScriptingClass:@"range"] alloc] initWithProperties:[NSDictionary dictionaryWithObjectsAndKeys:rangeName, @"name", nil]];
[[targetSheet ranges] addObject:range];

Excel2008Range *currentRange;
if ([[[targetSheet ranges] objectWithName:rangeName] exists]) {
    currentRange = [[targetSheet ranges] objectWithName:rangeName];
}

NSLog(@"[currentRange.value get] = %@", [currentRange.value get]);
// result: ((key),(1),(2),(3),(4),(5),(6),(7),(8),(9)) = NSArray

看来诀窍是将范围字符串设置到 Excel2008Range 的 name 属性中。我在处理此问题时发现的一个陷阱是永远不要填充范围的 formulaR1c1 — 如 [NSDictionary DictionaryWithObjectsAndKeys:rangeName, @"formulaR1c1", nil] —因为这将使用范围字符串的值填充范围。

中完全相同的命令的语法时，这实际上是有意义的

tell application "Microsoft Excel"
    set theValues to get value of range "A1:A10"
end tell

事后看来，当阅读 Applescript ... 和 objc-appscript

NSString *rangeString = @"A1:A10"
MEApplication *microsoftExcel = [MEApplication applicationWithName: @"Microsoft Excel"];
MEReference *cells = [[[microsoftExcel ranges] byName:rangeString] value];
NSArray *cellValues = [cells getItem];

...在这两种情况下，范围的值都是通过获取带有其名称的值范围来获得的。但对于 Scripting Bridge，据我所知，名称​​必须在创建范围对象时显式应用。

我确信有更好的方法来实现这一点（通常有），但至少这是有效的。