AppleScript读取Excel生成JSON报错：无法获取指定工作表

我需要将Excel中367列、2行的数据批量导出为JSON文件——文件名取第1行对应列的值并以.json为后缀，文件内容为第2行对应列的值。编写的AppleScript脚本仅修改过文件路径，但在获取指定工作表时报错：
Can't get worksheet "HARDCODEJSON" of missing value. number -1728

原脚本如下：

tell application "Microsoft Excel"
    set workbookName to POSIX file "/Users/myname/Documents/exportedstreams/AllStreamsFlatFile.xlsx"
    set sheetName to "HARDCODEJSON"
    set filePath to POSIX file "/Users/myname/Documents/exportedstreams/"
    
    -- Open the workbook file directly
    set workbookFile to open file workbookName
    set workbookObj to workbook of workbookFile
    
    -- Get the range of data from the specified sheet
    set sheetObj to get worksheet sheetName of workbookObj
    set dataRange to value of used range of sheetObj
    
    -- Loop through each column in the data range
    repeat with columnIndex from 1 to count of columns of dataRange
        set fileName to item columnIndex of item 1 of dataRange
        set fileContent to item columnIndex of item 2 of dataRange
        
        -- Save the content as a JSON file
        set fileRef to open for access ((filePath as text) & fileName & ".json") with write permission
        set eof fileRef to 0
        write (fileContent as text) to fileRef
        close access fileRef
    end repeat
    
    -- Close the workbook file
    close workbookFile saving no
end tell

修改建议 #

核心问题修复 #

错误根源是open file workbookName返回的就是工作簿对象，不需要再通过workbook of workbookFile获取，后者会得到missing value，导致后续无法定位工作表。

修改后的完整脚本 #

tell application "Microsoft Excel"
    set workbookPath to "/Users/myname/Documents/exportedstreams/AllStreamsFlatFile.xlsx"
    set sheetName to "HARDCODEJSON"
    set outputFolderPath to "/Users/myname/Documents/exportedstreams/"
    
    -- 直接打开工作簿并获取对象
    set workbookObj to open workbookPath
    -- 获取指定工作表
    set sheetObj to worksheet sheetName of workbookObj
    -- 获取已使用区域的数据
    set dataRange to value of used range of sheetObj
    
    -- 遍历每一列导出JSON
    repeat with columnIndex from 1 to count of columns of dataRange
        set fileName to item columnIndex of item 1 of dataRange
        set fileContent to item columnIndex of item 2 of dataRange
        
        -- 拼接完整输出文件路径
        set fullFilePath to outputFolderPath & fileName & ".json"
        -- 写入文件（用系统级文件操作，避免Excel环境冲突）
        tell application "System Events"
            set fileRef to open for access fullFilePath with write permission
            set eof fileRef to 0
            write fileContent as text to fileRef
            close access fileRef
        end tell
    end repeat
    
    -- 关闭工作簿不保存
    close workbookObj saving no
end tell

额外优化点 #

• 直接使用字符串路径代替POSIX file对象，减少类型转换带来的路径拼接问题
• 将文件写入操作放在System Events中，避免与Excel的文件访问逻辑冲突
• 简化工作簿对象的获取逻辑，去掉无效的层级调用

内容的提问来源于stack exchange，提问作者James Davies