excel - 是否可以像这样通过powershell读取Excel？

Question

我有这个excel

每一行都是我需要使用某些参数执行的自动化脚本，excel是因为每个脚本都接收不同的参数，我需要做一个powershell脚本来读取excel文件并为每一行执行该进程id（脚本）和发送那些参数

有没有办法做到这一点？可行吗？

到目前为止我有这个

$file = "C:\Users\MX02689\Documents\Parametros.xlsx"
$sheetName = "Sheet1"
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item($sheetName)
$objExcel.Visible=$false
$rowMax = ($sheet.UsedRange.Rows).count

$colMax = ($sheet.UsedRange.Columns).count

$rowName,$colName = 1,1
#the idea here is that for each row that has values do this
for($i=1;$i-le $colMax-1; $i++)

#The idea here is that if (parameter 1 -eq 1 ){
execute the command we use to send the scripts process id; "parameter2 parameter 3 parameter 4" 
}else{
skip the row and go to the next one
}
{
Write-Output("" + $sheet.Cells.Item($rowName,$colName+$i).text)

}

我在正确的方向吗？感谢您的帮助 ：）

我在正确的方向吗？我想做的事情可行吗？有没有优化的方法来实现这一目标？谢谢你的帮助:) 问候

标签： excelpowershell

Answer 1

使用 Excel 并不是使用 PowerShell 执行此操作的最快或最简单的方法。
可以这样做：

$file = "D:\Parametros.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item(1)
$objExcel.Visible = $false
$rowMax = ($sheet.UsedRange.Rows).count
$colMax = ($sheet.UsedRange.Columns).count

for ($row = 2; $row -le $rowMax; $row++) {   # skip the header row
    $params = @()
    for ($col = 1; $col -le $colMax; $col++) {
        $params += $sheet.Cells.Item($row, $col).Value()
    }
    # execute the command. For demo, just show the parameters used
    'Invoke-Command parameters: {0}' -f ($params -join ', ')
}

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

更方便的是将您的 Excel 文件保存为 CSV 并使用它：

Import-Csv -Path 'D:\Parametros.csv' | ForEach-Object {
    # execute the command. For demo, just show the parameters used
    'Invoke-Command parameters: {0}, {1}, {2}, {3}' -f $_.'process id', $_.parameter1, $_.parameter2, $_.parameter3, $_.parameter4
}

两种方法的演示输出：

Invoke-Command parameters: 235522, 1, testinguser3, Mko12345, something
Invoke-Command parameters: 235266, 0, testinguser4, Mko12346, something
Invoke-Command parameters: 235266, 1, testinguser5, Mko12347, something

---

从您的评论中，我现在了解参数 1 中的“1”或“0”是什么意思。下面找到 Excel 以及 CSV 方法的调整代码：

Excel的方法：

$file = "D:\Parametros.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item(1)
$objExcel.Visible = $false
$rowMax = ($sheet.UsedRange.Rows).count
$colMax = ($sheet.UsedRange.Columns).count

for ($row = 2; $row -le $rowMax; $row++) {   # skip the header row
    $params = @()
    for ($col = 1; $col -le $colMax; $col++) {
        $params += $sheet.Cells.Item($row, $col).Value()
    }
    # if the second parameter value converted to int = 1, proceed; if 0 skip the line
    if ([int]$param[1] -ne 0) {
        # execute the command. For demo, just show the parameters used
        'Invoke-Command parameters: {0}' -f ($params -join ', ').TrimEnd(", ")
    }
}

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

CSV文件的方法：

Import-Csv -Path 'D:\Parametros.csv' | ForEach-Object {
    # get the field values from the row in array $params (not a fixed number of fields)
    $params = @($_.PsObject.Properties).Value

    # if the second parameter value converted to int = 1, proceed; if 0 skip the line
    if ([int]$params[1] -ne 0) {
        # execute the command. For demo, just show the parameters used
        'Invoke-Command parameters: {0}' -f ($params -join ', ').TrimEnd(", ")
    }
}