在 Excel 中循环浏览每个工作表以在 PowerShell 中执行任务

Question

我正在尝试编写一个PowerShell脚本(我的第一个)，该脚本将遍历Excel文件中的每个工作表并删除第二行，然后删除一系列单元格。 从StackOverflow上的其他帖子中，我已经能够拼凑出一个脚本，可以在一定程度上完成这项任务。

此任务将删除每个工作表上的第二行，然后删除指定的单元格区域 - 但如果添加了另一个工作表 - 则必须修改脚本以解决此问题。

$file = 'C:SSISEmail Report Content.xlsx'
$excel = new-object -com Excel.Application -Property @{Visible = $false} 
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $workbook.Sheets.Item(1) # Activate the first worksheet
[void]$sheet.Cells.Item(2, 1).EntireRow.Delete() # Delete the second row
$range = $sheet.Range("E2","I13")
$range.delete()
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM

$file = 'C:SSISEmail Report Content.xlsx'
$excel = new-object -com Excel.Application -Property @{Visible = $false} 
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $workbook.Sheets.Item(2) # Activate the first worksheet
[void]$sheet.Cells.Item(2, 1).EntireRow.Delete() # Delete the second row
$range = $sheet.Range("E2","I13")
$range.delete()
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM
$file = 'C:SSISEmail Report Content.xlsx'
$excel = new-object -com Excel.Application -Property @{Visible = $false} 
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $workbook.Sheets.Item(3) # Activate the first worksheet
[void]$sheet.Cells.Item(2, 1).EntireRow.Delete() # Delete the second row
$range = $sheet.Range("E2","I13")
$range.delete()
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM

我想在这里做的是让脚本是动态的，以便它会自动选取其他工作表并在每个工作表上执行相同的任务。我试图修改我在专家交流中找到的逻辑，但无法使其正常工作。

$File = 'C:SSISEmail Report Content.xlsx'
$Excel = New-Object -ComObject Excel.Application -Property @{Visible = $False} 
$Workbook = $Excel.Workbooks.Open($File) # Open the file
$WorkSheet = $Workbook.WorkSheets
foreach ($WorkSheet in $Workbook.WorkSheets ) {
# $Sheet = $Workbook.Sheets.Item(1) # Activate the first worksheet
[void]$Sheet.Cells.Item(2, 1).EntireRow.Delete() # Delete the second row
$Range = $Sheet.Range("E2","I13")
$Range.delete()
$Workbook.Close($true) # Close workbook and save changes
}
$Excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) # Release COM 

我可以得到一些关于如何处理这项任务的帮助吗？谢谢

Answer 1

能够使用此代码：

$file = 'C:SSISEmail Report Content.xlsx'
$excel = new-object -com Excel.Application -Property @{Visible = $false} 
$workbook = $excel.Workbooks.Open($file) # Open the file
foreach ($sheet in $workbook.Worksheets) {
[void]$sheet.Cells.Item(2, 1).EntireRow.Delete() # Delete the second row
$range = $sheet.Range("E2","I13")
$range.delete()
}
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM