Powershell - Formatting Excel worksheet / Issues when rows are hidden

Question

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:

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

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!

Answer 1

Hi,
Have you checked the PSExcel 1.0 information in the link below?
https://www.powershellgallery.com/packages/PSExcel/1.0/Content/Format-Cell.ps1
Best Regards,
Carl