node.js用activex/com+自动化读写excel时查询接口、参数的调试方法

在某个 excel 的自动化应用中，打开某个特殊的文件时，会一直弹出一个对话框，提示：

This workbook contains links to one or more external sources that could be unsafe.
If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have.

只有手动关了对话框，自动化程序才能进行下一步，网上也搜索不到现成的解决方案。

我们的程序是使用的 winax 模块，通过COM+接口操作的excel，理论上excel上所有手动操作都应该可以通过程序来控制，比如我们之前添加的忽略警告 displayAlerts = false 的设置

/**
 * If activate is true: cannot run two instance the second time
 */
const $excel = new winax.Object("Excel.Application", { activate: false });
$excel.displayAlerts = false
$excel.visible = true

所以应该有一个接口可以阻止弹出这个contains unsafe links，对话框。下面介绍一下如何在JS里通过LOG来找到这个接口。

在JS里找到对象的接口还是比较容易的。

1. 首先打印 $excel 的所有方法

直接打印为 {}，可通过 for in

for(let method in $excel) {
    console.log(method)
}

__vars
__methods
__type
__value
__id
toString
valueOf

2. 依次打印这些成员

发现 __methods 可能是所有方法集合：

console.log($excel.__methods)

{
QueryInterface: {
  name: 'QueryInterface',
  dispid: 1610612736,
  invkind: 1,
  flags: 1,
  argcnt: 2
},
AddRef: {
  name: 'AddRef',
  dispid: 1610612737,
  invkind: 1,
  flags: 1,
  argcnt: 0
},
Release: {
  name: 'Release',
  dispid: 1610612738,
  invkind: 1,
  flags: 1,
  argcnt: 0
},
GetTypeInfoCount: {
  name: 'GetTypeInfoCount',
  dispid: 1610678272,
  invkind: 1,
  flags: 1,
  argcnt: 1
},
GetTypeInfo: {
  name: 'GetTypeInfo',
  dispid: 1610678273,
  invkind: 1,
  flags: 1,
  argcnt: 3
},
GetIDsOfNames: {
  name: 'GetIDsOfNames',
  dispid: 1610678274,
  invkind: 1,
  flags: 1,
  argcnt: 5
},
Invoke: {
  name: 'Invoke',
  dispid: 1610678275,
  invkind: 1,
  flags: 1,
  argcnt: 8
},
Application: { name: 'Application', dispid: 148, invkind: 2, flags: 0, argcnt: 0 },
Creator: { name: 'Creator', dispid: 149, invkind: 2, flags: 0, argcnt: 0 },
Parent: { name: 'Parent', dispid: 150, invkind: 2, flags: 0, argcnt: 0 },
ActiveCell: { name: 'ActiveCell', dispid: 305, invkind: 2, flags: 0, argcnt: 0 },
ActiveChart: { name: 'ActiveChart', dispid: 183, invkind: 2, flags: 0, argcnt: 0 },
ActiveDialog: {
  name: 'ActiveDialog',
  dispid: 815,
  invkind: 2,
  flags: 64,
  argcnt: 0
},
...

可以只打印方法名称方便查找

console.log(Object.keys($excel.__methods))

[
    'QueryInterface',         'AddRef',             'Release',
    'GetTypeInfoCount',       'GetTypeInfo',        'GetIDsOfNames',
    'Invoke',                 'Application',        'Creator',
    'Parent',                 'ActiveCell',         'ActiveChart',
    'ActiveDialog',           'ActiveMenuBar',      'ActivePrinter',
    'ActiveSheet',            'ActiveWindow',       'ActiveWorkbook',
    'AddIns',                 'Assistant',          'Calculate',
    'Cells',                  'Charts',             'Columns',
    'CommandBars',            'DDEAppReturnCode',   'DDEExecute',
    'DDEInitiate',            'DDEPoke',            'DDERequest',
    'DDETerminate',           'DialogSheets',       'Evaluate',
    '_Evaluate',              'ExecuteExcel4Macro', 'Intersect',
    'MenuBars',               'Modules',            'Names',
    'Range',                  'Rows',               'Run',
    '_Run2',                  'Selection',          'SendKeys',
    'Sheets',                 'ShortcutMenus',      'ThisWorkbook',
    'Toolbars',               'Union',              'Windows',
    'Workbooks',              'WorksheetFunction',  'Worksheets',
    'Excel4IntlMacroSheets',  'Excel4MacroSheets',  'ActivateMicrosoftApp',
    'AddChartAutoFormat',     'AddCustomList',      'AlertBeforeOverwriting',
    'AltStartupPath',         'AskToUpdateLinks',   'EnableAnimations',
    'AutoCorrect',            'Build',              'CalculateBeforeSave',
    'Calculation',            'Caller',             'CanPlaySounds',
    'CanRecordSounds',        'Caption',            'CellDragAndDrop',
    'CentimetersToPoints',    'CheckSpelling',      'ClipboardFormats',
    'DisplayClipboardWindow', 'ColorButtons',       'CommandUnderlines',
    'ConstrainNumeric',       'ConvertFormula',     'CopyObjectsWithCells',
    'Cursor',                 'CustomListCount',    'CutCopyMode',
    'DataEntryMode',          'Dummy1',             'Dummy2',
    'Dummy3',                 'Dummy4',             'Dummy5',
    'Dummy6',                 'Dummy7',             'Dummy8',
    'Dummy9',                 'Dummy10',            'Dummy11',
    '_Default',               'DefaultFilePath',    'DeleteChartAutoFormat',
    'DeleteCustomList',
    ... 236 more items
]

3. 通过名称发现 AskToUpdateLinks 这个接口比较像

在接口详细中，发现 argcnt 只需要一个参数，很有可能是 true/false 控制参数

AskToUpdateLinks: {
    name: 'AskToUpdateLinks',
    dispid: 992,
    invkind: 4,
    flags: 0,
    argcnt: 1
},

4. 调用此参数，更改代码并测试

测试后发现工作正常，无更新不安全链接的对话框弹出

const $excel = new winax.Object("Excel.Application", { activate: false });
$excel.displayAlerts = false
$excel.AskToUpdateLinks = false
$excel.visible = true

总结

js调用ActiveX/COM+与windows程序通过时，对象的所有接口都都可以通过此方法查询到接口，比如读、取值接口：

export const setCellValue = ($excel: any, sheet: string, row: number, col: number, value: string | number) => {
    $excel.worksheets.item(sheet).cells.item(row, col).value = value
}

export const getCellValue = ($excel: any, sheet: string, row: number, col: number) => {
    const value = $excel.worksheets.item(sheet).cells.item(row, col).Text
    return value.trim()
}

对于一些参数较多的方法，比如 saveAsHtml，可以网上搜索 powershell/vba 这样同样调用COM+接口的语言，查询到示例代码，然后将对应的参数翻译到 node.js 即可，比如excel保存成 html 所需要的参数如下：

export const saveAsHtml = ($workbook: any, htmlPath: string) => {
    $workbook.SaveAs(htmlPath, 44, undefined, undefined, false, false, 1, 2);
}