powershell rename XLSX spreadsheet columns-CodePudding

I have a spreadsheet that has spaces in the column names, how do I go about replacing the space with underscores on the column headers? Note: I am new at this so bear with me using this code with no luck: Powershell: search & replace in xlsx except first 3 columns

Theo's code works great!

$sheetname = 'my Data'
$file      = 'C:\Users\donkeykong\Desktop\1\booka.xlsx'
# create a COM Excel object
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false

$workbook = $objExcel.Workbooks.Open($file)
$sheet    = $workbook.Worksheets.Item($sheetname)
$sheet.Activate()

# get the number of columns used
$colMax = $sheet.UsedRange.Columns.Count

# loop over the column headers and replace the whitespaces
for ($col = 1; $col -le $colMax; $col  ) {
    $header = $sheet.Cells.Item(1, $col).Value() -replace '\s ', '_'
    $sheet.Cells.Item(1, $col) = $header
}
# close and save the changes
$workbook.Close($true)

$objExcel.Quit()
# IMPORTANT: clean-up used Com objects
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

CodePudding user response：

Assuming your Excel file has the headers in the first row, this should work without using the ImportExcel module:

$sheetname = 'my Data'
$file      = 'C:\Users\donkeykong\Desktop\1\booka.xlsx'
# create a COM Excel object
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false

$workbook = $objExcel.Workbooks.Open($file)
$sheet    = $workbook.Worksheets.Item($sheetname)
$sheet.Activate()

# get the number of columns used
$colMax = $sheet.UsedRange.Columns.Count

# loop over the column headers and replace the whitespaces
for ($col = 1; $col -le $colMax; $col  ) {
    $header = $sheet.Cells.Item(1, $col).Value() -replace '\s ', '_'
    $sheet.Cells.Item(1, $col) = $header
}
# close and save the changes
$workbook.Close($true)

$objExcel.Quit()
# IMPORTANT: clean-up used Com objects
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()