Проблемы с форматированием Excel из PowerShell
Короче говоря... У меня есть сценарий, который использует RESTful API для получения информации о конфигурации из устройства. Этот сценарий относительно длинный, но длина не имеет значения.
Проблема, с которой я столкнулся, заключается в том, что когда я разработал сценарий, который написал для PS 5.1, в PS5.1 все работало нормально. Теперь, пытаясь использовать скрипт на PS7, я столкнулся с проблемой форматирования вывода.
В настоящее время я использую COM-объекты для экспорта в Excel, а не популярный модуль Import-Excel. Я хотел, чтобы он был автономным и требовал чего-то дополнительного. Кроме того, когда я писал сценарий, я не знал о существовании этого модуля, и мне не хотелось переделывать весь сценарий.
После инициализации книги Excel я перемещаю активную ячейку и вставляю данные по мере необходимости. Все это работает исправно. Проблема возникает при попытке добавить границы ячеек.
Если вы протестируете PS5.1, вы увидите, что все работает отлично, а в 7 вы получаете ошибку о весе границы.
Помимо предложения мне перепроектировать сценарий и использовать Import-Excel, буду благодарен за любую помощь.
Вот краткий пример набора команд для демонстрации. Очевидно, для этого потребуется установить Excel в вашей системе.
$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()
Ошибка PowerShell 7:
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
Проблемы GAC
Продолжая тему комментариев, основная проблема заключается в том, что выражение "microsoft.office.interop.excel.xlBorderWeight" -as [type] оценивается как $null в PowerShell Core.
В результате $borderWeight — это $null и эта строка:
$objRange.Borders.weight = $borderWeight::xlThin
эквивалентно
$objRange.Borders.weight = $null
Напротив, в Windows PowerShell выражение "microsoft.office.interop.excel.xlBorderWeight" -as [type] возвращает ссылку на тип xlBorderWeight:
PS> $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
PS> $borderweight
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True XlBorderWeight System.Enum
Это связано с тем, что Windows PowerShell ищет сборки в GAC, а PowerShell Core — нет:
В PowerShell 6 и выше GAC отсутствует.
Обходной путь
Быстрое и грязное решение — использовать постоянные значения для перечислений, например
$XlBorderWeight_xlThin = 2;
...
$objRange.Borders.weight = $XlBorderWeight_xlThin
Но учтите, что у вас та же проблема с $lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type] и с любыми другими типами, определенными в основных сборках взаимодействия Office.
Кроме того, похоже, что $objRange.Borders.LineStyle = $null не приводит к ошибке, тогда как $objRange.Borders.weight = $null вызывает - \o/.
Добавить тип
Альтернативно вы можете вручную загрузить сборки из GAC в PowerShell Core:
Add-Type -Path "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll"
Если вы хотите найти магическое значение этого пути, вы можете использовать Windows PowerShell 5.1:
PS> ("microsoft.office.interop.excel.xlLineStyle" -as [type]).Assembly.Location
C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll
Однако вам нужно будет использовать жестко запрограммированный путь в PowerShell Core.
Кастинг
Однако это еще не вся история, потому что после загрузки типов вы все равно получите ту же ошибку, но по другой причине.
$objRange.Borders.weight — это значение Variant — см.:
PS> $objRange.Borders | gm
TypeName: System.__ComObject#{00020854-0000-0000-c000-000000000046}
Name MemberType Definition
---- ---------- ----------
... snip ...
Weight Property Variant Weight () {get} {set}
Windows PowerShell, кажется, с удовольствием преобразует значение перечисления xlBorderWeight в Variant, но PowerShell Core этого не делает, поэтому вместо этого вам придется сделать что-то вроде этого, чтобы помочь ему, приведя перечисление к int:
$objRange.Borders.weight = [int]$borderWeight::xlThin
Как только вы все это сделаете, ваш код должен наконец работать...
Какой потрясающий ответ!! Действительно!! Большое спасибо за объяснение. С одной стороны это имеет смысл, но с другой стороны, зачем Microsoft удалила поддержку GAC по умолчанию в PS6+? Несмотря ни на что, еще раз спасибо!
GAC был функцией .Net Framework, которая не была перенесена в мир DotNet (Core) — это был способ совместного использования сборок между приложениями во времена, когда еще не было таких менеджеров пакетов, как nuget, что в значительной степени сделало его устаревшим.
Основная проблема заключается в том, что по какой-либо причине
"microsoft.office.interop.excel.xlBorderWeight" -as [type]возвращает ссылку на типMicrosoft.Office.Interop.Excel.XlBorderWeightв Windows PowerShell, но$nullв PowerShell 7, поэтому строка, которая не работает в PS7, эквивалентна$objRange.Borders.weight = $null. Быстрый и грязный обходной путь вместо реальной основной причины — сделать что-то вроде этого$XlBorderWeight_xlThin = 2; $objRange.Borders.weight = $XlBorderWeight_xlThin