powershell - 如何在excel中为每个单独的单元格获取一个？

Question

我是 PS 新手，我有这个脚本，我想遍历行，但我想单独获取一个单元格的值，然后是下一个，依此类推，所以如果 value1 -eq 某事这样做或到目前为止，我有这个：

    #Install-module PSExcel -Scope CurrentUser
#Get-command -module psexcel

$path = "\\srf201002\Sistemas Liberaciones\Hilda Ruiz\QA Sistemas\Borrador\Azure Pipeline\Prueba.xlsx"
$objExcel = New-Object -ComObject Excel.Application 
$objExcel.Visible = $false
$objExcel.DisplayAlerts = $false
$WorkBook = $objExcel.Workbooks.Open($path)
$WorkSheet = $WorkBook.worksheets.Item(1)
$totalNoOfRecords = ($WorkSheet.UsedRange.Rows).count
Write-Output $totalNoOfRecords



$people = new-object System.Collections.ArrayList
$i=1
foreach ($item in $WorkSheet)
{

$op=$Worksheet.Rows.Item(5).Columns.Item(7).Text
$people.add($op) | Out-Printer #I don't want to see the output

}


Write-Output $people

它所做的只是打印我告诉它打印的单元格

我怎样才能让它循环通过 1-7 然后转到下一行？感谢您的所有帮助:)

标签： powershell

Answer 1

这种类型的用例在整个网络上都有类似的示例，只需使用您的用例搜索它们即可。从而得到类似...的结果

' PowerShell 每行验证 excel 单元格'

### Read excel file line by line

# Open excel file and use specific sheet
    $objExcel=New-Object -ComObject Excel.Application
    $objExcel.Visible=$false
    $WorkBook=$objExcel.Workbooks.Open($strFileName)
    if ($strSheetName -eq "")
    {
    $worksheet = $WorkBook.sheets.Item(1)
    }
    else
    {
    $worksheet = $WorkBook.sheets.Item($strSheetName)
    }
# loop for each row of the excel file
    $intRowMax = ($worksheet.UsedRange.Rows).count
    for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
    {
    $codeName  = $worksheet.cells.item($intRow,$intCode).value2
    $author    = $worksheet.cells.item($intRow,$intAuth).value2
    $createDate= $worksheet.cells.item($intRow,$intCrDt).value2
    $validate  = $worksheet.cells.item($intRow,$intVali).value2
    $validDate = $worksheet.cells.item($intRow,$intVlDt).value2
    } 
    $WorkBook.close()
    $objexcel.quit()

当然，您需要调整以上内容以适合您的用例。特别是如果您需要动态发现列数并对其采取行动。

所以，我对上面的调整以处理这样一个动态列用例将是这样的......

# Mine
Clear-Host
$strFileName = 'D:\Temp\Test.xlsx'
$strSheetName = 'Sheet1'

# Open excel file and use specific sheet
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false

$WorkBook = $objExcel.Workbooks.Open($strFileName)

if ($strSheetName -eq '') { $worksheet = $WorkBook.sheets.Item(1) }
else { $worksheet = $WorkBook.sheets.Item($strSheetName) }

# loop for each row of the excel file
$intRowMax = ($worksheet.UsedRange.Rows).count

for($intRow = 1 ; $intRow -le $intRowMax ; $intRow++)
{
    "Showing cell data for row#$intRow"
    $WorkBook.Sheets.Item(1).Rows($intRow).value2
} 

$WorkBook.close()
$objexcel.quit()

# Shut down Excel
Get-Process -Name EXCEL -ErrorAction SilentlyContinue | 
Stop-Process -Force

# Results
<#
Showing cell data for row#1
Row1-1
Row1-2
Row1-3
Row1-4
Row1-5
Row1-6
Row1-7
Showing cell data for row#2
Row2-1
Row2-2
Row2-3
Row2-4
Row2-5
Row1-6
Row2-7
Showing cell data for row#3
Row3-1
Row3-2
Row3-3
Row3-4
Row3-5
Row3-6
Row3-7
#>

同样，这只是一个例子，因为总是有很多选项可以做 X 或 Y 的事情。

...顺便说一句，这些问答/帖子本可以回答您的问题。

Powershell脚本匹配excel单元格值的条件

或者这个

如何在excel中读取单元格数据并输出到命令提示符[关闭]