利用PowerShell实现excel的批量操作

批量自动行高列宽

$files = Get-ChildItem "D:\work\guoxin\test"
foreach($path in $files){
    $excel = New-Object -ComObject excel.application
    $excel.visible = $false
    $wb = $excel.Workbooks.open($path.fullname)
    foreach ($i in 1..8){
        $sht = $wb.Worksheets.Item($i)
        $range = $sht.UsedRange
        $range.EntireColumn.AutoFit() | Out-Null
    }
    $wb.save()
    $wb.close()
    $excel.quit()
}

• 第一个循环是遍历文件夹中的所有excel文件
• 第二个循环是遍历excel文件中的子表，总共有8个表
• 每循环一次就退出一次excel，当excel处于多个进程的时候会出现，RPC不可用的报错

批量调整两位小数

$files = Get-ChildItem "D:\work\huang\save"
foreach($path in $files) {
    $excel = New-Object -ComObject excel.application
    $excel.visible = $true
    $wb = $excel.Workbooks.open($path.fullname)
    $ws = $wb.WorkSheets.item(1)
    $ws.range("J:K").NumberFormat = "#,##0.00"
    $wb.save()
    $wb.close()
    $excel.quit()
}

批量替换删除

$files = Get-ChildItem "D:\chen\tocustomer\process"
foreach($path in $files) {
    $excel = New-Object -ComObject excel.application
    $excel.visible = $true
    $wb = $excel.Workbooks.open($path.fullname)
    $ws = $wb.WorkSheets.item(1)
    $all = $ws.UsedRange
    $all.replace("#N/A", "-")
    $ws.range("I:M").NumberFormat = "#,##0.00"
    $ws.range("O:P").NumberFormat = "#,##0.00"
    $ws.range("R:S").NumberFormat = "#,##0.00"
    $ws.range("U:Y").NumberFormat = "#,##0.00"
    $ws.Columns(1).EntireColumn.Delete()
    $wb.save()
    $wb.close()
    $excel.quit()
}

根据某列内容分割成多个文件

$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $true
$path = "D:\chen\my.xlsx"
$workbook = $objExcel.workbooks.open($path)
$ws=$workbook.WorkSheets.item(1)
$alpha_list = @()
$range = $ws.UsedRange
$max_num = $range.Rows.Count
foreach($i in 2..$max_num) {
    $alpha_list += $ws.cells.item($i, 1).value2
}
$alpha_list = $alpha_list | sort-object -unique
echo $alpha_list
foreach ($i in $alpha_list) {
    $range.EntireColumn.AutoFilter()
    $range.AutoFilter(1, $i)
    $fil_range = $ws.UsedRange
    $fil_range.copy()
    $wb_split = $objExcel.workbooks.add()
    $wb_split.activate()
    $ws_split = $wb_split.WorkSheets.item(1)
    $ws_split.Range("A1").PasteSpecial(-4104) | out-null
    $save_path = "D:\chen\za\split\MDC{0}.xlsx" -f $i
    $wb_split.saveas($save_path)
    $wb_split.close()
    $ws.activate()
}
$objExcel.Quit()

统计工作簿中所有工作表的行数

XML操作