如何用PowerShell将XLSX表格列名中的空格替换为下划线？

嘿，作为新手刚处理这种带空格列名的XLSX表格时，我也踩过不少坑，试了Stack Overflow上的一些代码都没成功。直到找到Theo的这段PowerShell代码，轻松实现了把列标题里的空格替换成下划线，分享给你：

$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()

一些小提示： #

• 记得把$sheetname和$file替换成你自己的工作表名称和文件路径
• 代码里的-replace '\s+', '_'会把连续的空格也替换成单个下划线，非常实用
• 最后那段COM对象清理的代码一定要加上，不然Excel进程会在后台残留，占用系统资源

内容的提问来源于stack exchange，提问作者sam goto