PowerShell 7中使用COM对象设置Excel单元格边框权重报错求助

问题描述 #

原本在PowerShell 5.1中正常运行的脚本，使用Excel COM对象操作表格，在PowerShell 7中初始化工作簿、插入数据均正常，但设置单元格边框Weight属性时报错，提示无法设置Borders类的Weight属性。

测试代码 #

$Excel = new-object -comobject excel.application
$Excel.Visible = $true
$ExcelWorkBook = $Excel.Workbooks.Add()
$WorkSheetIndex = 1
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item($WorkSheetIndex)

$ExcelWorkSheet.Cells.Item(1, 1) = "Test Data"

#Formatting shortcuts
$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]

$objRange = $ExcelWorkSheet.UsedRange
$objRange.Borders.LineStyle = $lineStyle::xlNone
$objRange.Borders.weight = $borderWeight::xlThin
$ExcelWorkSheet.Cells.Item(1, 1).Font.Size = 16
$ExcelWorkSheet.Cells.Item(1, 1).Font.Bold = $True
[void] $objRange.EntireColumn.Autofit()

报错信息 #

OperationStopped: C:\Work\Internal\GIT\Appliance_Administration\Excel_Test.ps1:15
Line |
 15 |  $objRange.Borders.weight = $borderWeight::xlThin
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Unable to set the Weight property of the Borders class

解决方案 #

方案1：逐个设置各边框的Weight属性 #

PowerShell 7基于.NET Core，COM互操作处理与.NET Framework（PowerShell 5.1）存在差异，直接给Borders集合整体设置Weight属性不再支持，需逐个指定边框类型设置：

$Excel = new-object -comobject excel.application
$Excel.Visible = $true
$ExcelWorkBook = $Excel.Workbooks.Add()
$WorkSheetIndex = 1
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item($WorkSheetIndex)

$ExcelWorkSheet.Cells.Item(1, 1) = "Test Data"

# 加载Excel Interop程序集（PS7中需手动加载）
Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$objRange = $ExcelWorkSheet.UsedRange
# 先清除所有边框样式
$objRange.Borders.LineStyle = [Microsoft.Office.Interop.Excel.XlLineStyle]::xlNone

# 逐个设置各边框的Weight
$borderWeight = [Microsoft.Office.Interop.Excel.XlBorderWeight]::xlThin
$objRange.Borders([Microsoft.Office.Interop.Excel.XlBordersIndex]::xlEdgeTop).Weight = $borderWeight
$objRange.Borders([Microsoft.Office.Interop.Excel.XlBordersIndex]::xlEdgeBottom).Weight = $borderWeight
$objRange.Borders([Microsoft.Office.Interop.Excel.XlBordersIndex]::xlEdgeLeft).Weight = $borderWeight
$objRange.Borders([Microsoft.Office.Interop.Excel.XlBordersIndex]::xlEdgeRight).Weight = $borderWeight
$objRange.Borders([Microsoft.Office.Interop.Excel.XlBordersIndex]::xlInsideHorizontal).Weight = $borderWeight
$objRange.Borders([Microsoft.Office.Interop.Excel.XlBordersIndex]::xlInsideVertical).Weight = $borderWeight

$ExcelWorkSheet.Cells.Item(1, 1).Font.Size = 16
$ExcelWorkSheet.Cells.Item(1, 1).Font.Bold = $True
[void] $objRange.EntireColumn.Autofit()

方案2：直接使用枚举对应的数值 #

如果不想逐个设置边框，可直接使用枚举对应的数值赋值（xlThin对应数值2，xlNone对应数值-4142），绕过类型引用的问题：

$Excel = new-object -comobject excel.application
$Excel.Visible = $true
$ExcelWorkBook = $Excel.Workbooks.Add()
$WorkSheetIndex = 1
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item($WorkSheetIndex)

$ExcelWorkSheet.Cells.Item(1, 1) = "Test Data"

$objRange = $ExcelWorkSheet.UsedRange
$objRange.Borders.LineStyle = -4142 # xlNone
$objRange.Borders.weight = 2 # xlThin

$ExcelWorkSheet.Cells.Item(1, 1).Font.Size = 16
$ExcelWorkSheet.Cells.Item(1, 1).Font.Bold = $True
[void] $objRange.EntireColumn.Autofit()

方案3：修正枚举类型的获取方式 #

在PS7中，直接用字符串转类型的方式可能存在加载问题，改为通过Add-Type加载程序集后直接引用枚举类型：

$Excel = new-object -comobject excel.application
$Excel.Visible = $true
$ExcelWorkBook = $Excel.Workbooks.Add()
$WorkSheetIndex = 1
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item($WorkSheetIndex)

$ExcelWorkSheet.Cells.Item(1, 1) = "Test Data"

# 手动加载Excel Interop程序集
Add-Type -AssemblyName Microsoft.Office.Interop.Excel

# 直接引用枚举类型
$objRange = $ExcelWorkSheet.UsedRange
$objRange.Borders.LineStyle = [Microsoft.Office.Interop.Excel.XlLineStyle]::xlNone
# 注意：此方式仍可能无法设置整个Borders集合的Weight，建议优先用方案1
$objRange.Borders.weight = [Microsoft.Office.Interop.Excel.XlBorderWeight]::xlThin

$ExcelWorkSheet.Cells.Item(1, 1).Font.Size = 16
$ExcelWorkSheet.Cells.Item(1, 1).Font.Bold = $True
[void] $objRange.EntireColumn.Autofit()

内容的提问来源于stack exchange，提问作者rick.leon.fl