Powershell - Formatting Excel worksheet / Issues when rows are hidden

G L 21 Reputation points
2021-02-11T16:32:45.167+00:00

Hello,

I am automating some preprocessing needed in excel via Powershell, but have noticed that formatting isn't working when a row is hidden.

I have been searching the past 4 days the figure this out, but I am an idiot, so I apologize if this is an easy fix.

This is how the excel worksheet is given to us, and you can see that rows 4, 7, 8 (and many more) are hidden:
67034-excel-hidden-rows.jpg

After running my Powershell program, this is the output in CSV, you can see the hidden rows are not formatted:
66930-csv-output.jpg

There must be a way to format all cells in the column? Here is my Powershell code for reference:

$objExcel = New-Object -ComObject Excel.Application  
$objExcel.Visible = $false  
$objExcel.DisplayAlerts = $false  
$objExcel.AskToUpdateLinks = $false  
   
$file = Get-ChildItem -Path "C:\................\*.xlsx"  
$wb = $objExcel.Workbooks.Open($file.Fullname)  
$sheet = $wb.Worksheets.Item("Details")  
  
$sheet.Cells.Item(1, 1).EntireRow.Delete()  
$sheet.Cells.Item(1, 1).EntireRow.Replace("`n", " ", [Microsoft.Office.Interop.Excel.XlLookAt]::xlPart)  
  
$sheet.Range("U:U").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("V:V").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AC:AC").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AD:AD").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AE:AE").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AF:AF").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AG:AG").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AH:AH").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AI:AI").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AJ:AJ").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AO:AO").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AP:AP").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AQ:AQ").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AR:AR").EntireColumn.NumberFormat = "0.0000"  
$sheet.Range("AS:AS").EntireColumn.NumberFormat = "0.0000"  
$sheet.Range("AT:AT").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AU:AU").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AV:AV").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("AZ:AZ").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("BM:BM").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("BN:BN").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("BO:BO").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("BP:BP").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("BQ:BQ").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("BR:BR").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("BZ:BZ").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("CB:CB").EntireColumn.NumberFormat = "0.0000"  
$sheet.Range("CG:CG").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("CH:CH").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("CI:CI").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("CJ:CJ").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("CK:CK").EntireColumn.NumberFormat = "0.00"  
$sheet.Range("CL:CL").EntireColumn.NumberFormat = "0.00"  
  
$sheet.SaveAs("C:\................\" + "_converted_file" + ".csv", 6)  
   
$wb.Close()  
$objExcel.Quit()  
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)  

Thank you!

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,429 questions
{count} votes

0 additional answers

Sort by: Most helpful