使用Powershell读取Excel数据并写入变量 [英] Read Excel data with Powershell and write to a variable

问题描述

使用PowerShell我想捕获用户输入，将输入与Excel电子表格中的数据进行比较，并将相应单元格中的数据写入变量。我对PowerShell来说是相当新鲜的，似乎无法想像出来。示例是：提示用户输入商店号码，他们输入123。然后将输入与列A中的数据进行比较。相应单元格中的数据被捕获并写入变量，例如$ GoLiveDate。

Using PowerShell I would like to capture user input, compare the input to data in an Excel spreadsheet and write the data in corresponding cells to a variable. I am fairly new to PowerShell and can't seem to figure this out. Example would be: A user is prompted for a Store Number, they enter "123". The input is then compared to the data in Column A. The data in the corresponding cells is captured and written to a variable, say $GoLiveDate.

任何帮助将会非常大赞赏。

Any help would be greatly appreciated.

样本数据： http://reddirttechnology.com /table.html

推荐答案

用户输入可以这样读：

$num = Read-Host "Store number"

Excel可以这样处理：

Excel can be handled like this:

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\path\to\your.xlsx")
$ws = $wb.Sheets.Item(1)

查看一列中的值，并将另一列中的相应值分配给一个变量可以这样做：

Looking up a value in one column and assigning the corresponding value from another column to a variable could be done like this:

for ($i = 1; $i -le 3; $i++) {
  if ( $ws.Cells.Item($i, 1).Value -eq $num ) {
    $GoLiveDate = $ws.Cells.Item($i, 2).Value
    break
  }
}

不要忘记清理：

$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

这篇关于使用Powershell读取Excel数据并写入变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！