Edit to Post:
I have tested differently and now I am seeing the errors that I was seeing in Python in PowerShell. I changed from pasting the enitre script below into PowerShell, and am instead calling it directly with this command:
C:\temp\ExcelManipulation.ps1 When done this way in PowerShell, I see all the errors. Also, my excel file gets deleted.
I have written a PowerShell script using the ImportExcel PowerShell Module that will open an existing excel created from SQL and add a lot of formatting. I can confirm that the script I wrote works, and I am seeing what I want to see on the formatted excel file.
However, because of reasons outside of my control, I am not able to call the PowerShell script directly. Instead, I have a Python script that will open PowerShell and run the script. The problem ends up being that when calling the script through Python, I am getting errors returned in Python on several lines saying:
The property 'HorizontalAlignment' cannot be found on this object. Verify that the property exists and can be set.
or
The property 'Bold' cannot be found on this object. Verify that the property exists and can be set.
These Errors are not appearing if I run the PowerShell script without Python and I am getting my expected excel output. I am at a bit of a loss on what could be causing this. My python script is very simple:
import subprocess import sys p = subprocess.Popen(['powershell.exe', "-File", 'C:\\ExcelManipulation2\\ExcelManipulation.ps1'], stdout=sys.stdout) My PowerShell script is currently built for a static file:
#Load Excel File, Location will be dynamic $ExcelData = Open-ExcelPackage C:\ExcelManipulation2\ExcelTest.xlsx #rowCount is following the header row $runningRowCount = 1 #declare what sheet we are editing and rename it $sheet1 = $ExcelData.Workbook.Worksheets["Sheet1"] $sheet1.Name ="Emp Credit Export" #Get Column Count $columnCount = $sheet1.Dimension.Columns $rowcount = $sheet1.Dimension.Rows $staticRowCount = $sheet1.Dimension.Rows $alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" [string]$global:ColumnLetter = $null [string]$global:cellValue = $null $LongestColumn = 0 $grandTotalCountColumn = 'Last Name' $grandTotalSubtotalColumns = @('Hired', 'Screened', 'Eligible', 'eSigned', 'Needs 8850', 'Late 8850') $dateHeaderExceptions = @('Last PPED', 'DOB', 'Most Recent Payroll PPED', 'Latest PPED', 'First Time on Payroll', 'Sow Expiration', 'WOTC Program Start', 'WOTC Program End', 'LTU Program Start', 'LTU Program End', 'IEC Program Start', 'IEC Program End', 'NYY Program Start', 'NYY Program End', 'TOS Program Start', 'TOS Program End', 'TX TANF Program Start', 'TX TANF Program End', 'SC TANF Program Start', 'SC TANF Program End') $falsePositveDateHeaders = @('WOTC Wages to Date', 'Blank Original Start Date', 'Blank DOB', 'Invalid DOB', 'Term Date less than start date', 'Rehire date less than start date', 'Blank Term Dates', 'Blank Rehire Dates', 'Rehire dates equal to start dates','Changing Start Date', 'Start Date after PPED', 'Gave Info Date Calculation', 'Job Offer Date Calculation', 'Hire Date Calculation') function GetColumnLetter($columnNumber){ if ($columnNumber -gt 26) { $runningColumnCount = $columnNumber $loopCount = 0 while ($runningColumnCount -gt 26) { $runningColumnCount = $runningColumnCount - 26 $loopCount++ } #Get First Letter $subStringIndex = $loopCount-1 $splitString = $alphabet.Substring($subStringIndex) $columnLetter1 = $splitString.Substring(0,1) #Get Second Letter $subStringIndex = $runningColumnCount-1 $splitString = $alphabet.Substring($subStringIndex) $columnLetter2 = $splitString.Substring(0,1) #Combine Column Letters $global:ColumnLetter = $columnLetter1+$columnLetter2 } else { $subStringIndex = $columnNumber-1 $splitString = $alphabet.Substring($subStringIndex) $global:ColumnLetter = $splitString.Substring(0,1) } } #Insert Blank Row $sheet1.InsertRow(1, 1) #this insert a row at the top of the worksheet $runningRowCount++ $rowcount++ #Insert Filter (If applicable) $sheet1.InsertRow(1, 1) $sheet1.Cells["a1"].Value = 'Create Date Time:' $sheet1.Cells["b1"].Value = '10/30/1960' $sheet1.Cells["a1:b1"].Style.Font.Bold = $true $runningRowCount++ $rowcount++ #Insert Run Date $sheet1.InsertRow(1, 1) $sheet1.Cells["a1"].Value = 'Run Date:' $sheet1.Cells["b1"].Value = '10/05/2023' $sheet1.Cells["a1:b1"].Style.Font.Bold = $true $runningRowCount++ $rowcount++ #Insert Screen Date Range $sheet1.InsertRow(1, 1) $sheet1.Cells["a1"].Value = 'Screen Date Range:' $sheet1.Cells["b1"].Value = 'Unbound' $sheet1.Cells["a1:b1"].Style.Font.Bold = $true $runningRowCount++ $rowcount++ #Insert Start Date Range $sheet1.InsertRow(1, 1) $sheet1.Cells["a1"].Value = 'Start Date Range:' $sheet1.Cells["b1"].Value = '10/05/2022 - Unbound' $sheet1.Cells["a1:b1"].Style.Font.Bold = $true $runningRowCount++ $rowcount++ #Insert Corp Name $sheet1.InsertRow(1, 1) $sheet1.Cells["a1"].Value = 'This is a long corp name' $sheet1.Cells["a1"].Style.Font.Bold = $true $sheet1.Cells["a1"].Style.Font.Size = 13 $runningRowCount++ $rowcount++ #insert subtotal thing $sheet1.Cells["c1"].Value = 'This is a long subtotal name' $sheet1.Cells["c2"].Value = '100' $sheet1.Cells["c2"].Style.HorizontalAlignment = 'Center' $sheet1.Cells["c1:c2"].Style.Font.Bold = $true #Format header row GetColumnLetter $columnCount #fetch column Letter for below portion $sheet1.Cells["a"+$runningRowCount+":"+$global:ColumnLetter+$runningRowCount].Style.Font.Bold = $true $sheet1.Cells["a"+$runningRowCount+":"+$global:ColumnLetter+$runningRowCount].Style.Fill.PatternType = 'Solid' $sheet1.Cells["a"+$runningRowCount+":"+$global:ColumnLetter+$runningRowCount].Style.Fill.BackgroundColor.SetColor(0, 196, 196, 205) $sheet1.Cells["a"+$runningRowCount+":"+$global:ColumnLetter+$runningRowCount].AutoFilter = $true $headerRow = $runningRowCount $runningRowCount++ $sheet1.View.FreezePanes($runningRowCount, 1) #Find and remove R3pl@c3 from columns and resize columns $internalColumnCount = 1 for(($i = 0); $i -lt $columnCount; $i++) { GetColumnLetter $internalColumnCount if($internalColumnCount -le 2) { $x = 1 for(($j = 0); $j -le $headerRow; $j++) { if($sheet1.Cells[$global:ColumnLetter+$x].Value.length -gt $LongestColumn) { if($sheet1.Cells[$global:ColumnLetter+$x].Style.Font.Size -eq 13) { $LongestColumn = $sheet1.Cells[$global:ColumnLetter+$x].Value.length $y = 0 } else { $LongestColumn = $sheet1.Cells[$global:ColumnLetter+$x].Value.length $y = 1 } } $x++ } if($y -eq 0) { $sheet1.Column($internalColumnCount).Width = $LongestColumn+6 } else { $sheet1.Column($internalColumnCount).Width = $LongestColumn+4 } } else { $test = $sheet1.Cells[$global:ColumnLetter+"1"].Value.length if($test -lt 2) { if(($sheet1.Cells[$global:ColumnLetter+$headerRow].Value -eq "SSN") -or ($sheet1.Cells[$global:ColumnLetter+$headerRow].Value -eq "VRID")) { $sheet1.Column($internalColumnCount).Width = 11 } else { $length = $sheet1.Cells[$global:ColumnLetter+$headerRow].Value.length $sheet1.Column($internalColumnCount).Width = $length+4 } } else { $topRowLength = $sheet1.Cells[$global:ColumnLetter+"1"].Value.length $headerRowLength = $sheet1.Cells[$global:ColumnLetter+$headerRow].Value.length if($topRowLength -gt $headerRowLength) { $sheet1.Column($internalColumnCount).Width = $topRowLength+4 } else { $sheet1.Column($internalColumnCount).Width = $headerRowLength+4 } } } if($sheet1.Cells[$global:ColumnLetter+$runningRowCount].Value -like "R3pl@c3*") { $internalRowCount = $runningRowCount for(($j = 1); $j -lt $staticRowCount; $j++) { $sheet1.Cells[$global:ColumnLetter+$internalRowCount].Value = $sheet1.Cells[$global:ColumnLetter+$internalRowCount].Value.Substring(7) $internalRowCount++ } } if($global:ColumnLetter -eq 'A') { $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Value = 'Grand Total' $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Style.HorizontalAlignment = 'Left' $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Style.Font.Bold = $true } $global:cellValue = $sheet1.Cells[$global:ColumnLetter+($runningRowCount-1)].Value if(($global:cellValue -like "*Date*" -or $dateHeaderExceptions.Contains($global:cellValue)) -and $falsePositveDateHeaders -notcontains $global:cellValue) { Set-ExcelColumn -Worksheet $sheet1 -Column $internalColumnCount -NumberFormat 'Short Date' } $subtotalRow = $rowcount + 2 if($grandTotalCountColumn -eq $global:cellValue) { [string]$countFormula = 'COUNTA('+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Formula = '=COUNTA('+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount+')' $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Style.HorizontalAlignment = 'Right' $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Style.Font.Bold = $true } $percentRow = $rowcount + 3 if(($grandTotalSubtotalColumns.Contains('Hired') -and $global:ColumnLetter -eq 'L') -or ($grandTotalSubtotalColumns.Contains('Hired') -and $global:ColumnLetter -eq 'L')) { $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Formula = '=SUBTOTAL(9,'+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount+')' $sheet1.Cells[$global:ColumnLetter+$percentrow].Formula = '=IF(COUNTA('+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount+')=0,"0%",IF(AND(100*('+$global:ColumnLetter+$subtotalRow+'/'+$countFormula+'))>0,100*('+$global:ColumnLetter+$subtotalRow+'/COUNTA('+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount+'))<=1),"1%",IF(AND(100*('+$global:ColumnLetter+$subtotalRow+'/COUNTA('+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount+'))>=99,100*('+$global:ColumnLetter+$subtotalRow+'/COUNTA('+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount+'))<100),"99%",IF(100*('+$global:ColumnLetter+$subtotalRow+'/COUNTA('+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount+'))=100,"100%",ROUND(100*('+$global:ColumnLetter+$subtotalRow+'/COUNTA('+$global:ColumnLetter+$runningRowCount+':'+$global:ColumnLetter+$rowcount+')),0)&"%"))))' $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Style.HorizontalAlignment = 'Right' $sheet1.Cells[$global:ColumnLetter+$percentrow].Style.HorizontalAlignment = 'Right' $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Style.Font.Bold = $true $sheet1.Cells[$global:ColumnLetter+$percentrow].Style.Font.Bold = $true } $internalColumnCount++ } Close-ExcelPackage $ExcelData Is there any ideas on why Python would be seeing these errors but PowerShell is not?
Here is the full list of errors that are being returned in Python.
The property 'HorizontalAlignment' cannot be found on this object. Verify that the property exists and can be set. At C:\ExcelManipulation2\ExcelManipulation.ps1:189 char:9 + $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Style.Horizo ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound The property 'Bold' cannot be found on this object. Verify that the property exists and can be set. At C:\ExcelManipulation2\ExcelManipulation.ps1:190 char:9 + $sheet1.Cells[$global:ColumnLetter+$subtotalRow].Style.Font.B ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound Exception calling "Column" with "1" argument(s): "Object reference not set to an instance of an object." At C:\ExcelManipulation2\ExcelManipulation.ps1:139 char:13 + $sheet1.Column($internalColumnCount).Width = $LongestColu ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : NullReferenceException Exception calling "Column" with "1" argument(s): "Object reference not set to an instance of an object." At C:\ExcelManipulation2\ExcelManipulation.ps1:167 char:17 + ... $sheet1.Column($internalColumnCount).Width = $topRowLengt ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : NullReferenceException Exception calling "Column" with "1" argument(s): "Object reference not set to an instance of an object." At C:\ExcelManipulation2\ExcelManipulation.ps1:158 char:17 + ... $sheet1.Column($internalColumnCount).Width = $length+4 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : NullReferenceException Exception calling "Column" with "1" argument(s): "Object reference not set to an instance of an object." At C:\ExcelManipulation2\ExcelManipulation.ps1:153 char:17 + $sheet1.Column($internalColumnCount).Width = 11 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : NullReferenceException 
powershell.exe -File C:\ExcelManipulation2\ExcelManipulation.ps1?