将 Excel 数据导入 PowerShell 变量 [英] Import Excel data into PowerShell variables

问题描述

我有一个 Excel 文件，其中包含未知数量的记录，以及这 3 列:

I have an Excel File which has an unknown number of records in it, and these 3 columns:

Variable Name, Store Number, Email Address

我在 QlikView 中使用它来导入某些商店的数据，然后为列表中的每个商店创建一个单独的报告.然后我需要将每个报告通过电子邮件发送到每个单独的商店(商店编号将在报告文件名中).

I use this in QlikView to import data for certain stores and then create a separate report for each store in the list. I then need to email each report to each individual store (store number will be in the report file name).

所以在 PowerShell 中，我想读取 Excel 文件并为每个商店设置变量:

So in PowerShell I would like to read the Excel File and set variables for each store:

$Store1 = The Store Number in Row 2 of the Excel File  
$Store1Email = The Store Email in Row 2 of the Excel File  
$Store2 = The Store Number in Row 3 of the Excel File  
$Store2Email = The Store Email in Row 3 of the Excel File  
etc. for each Storein the file (can be any number of stores).  

请注意必须忽略 excel 文件中的变量名称"(即 QLikView)，并且必须按照我上面的示例命名 PowerShell 变量，每次递增数字.

Please note the "Variable Name" in the excel file must be ignored (that is for QLikView) and the PowerShell variables must be named as per my above examples, each time incrementing the number.

推荐答案

好的，首先，如果您要使用实际的 .XLS 或 .XLSX 或 .XLSM 文件，我强烈建议您使用来自TechNet 库(在此处找到).

Ok, first off if you are going to be working with actual .XLS or .XLSX or .XLSM files I would highly suggest using the Import-XLS function from the TechNet gallery (found here).

之后，只需引用它导入的对象即可发送电子邮件，而不是为每个商店制作对象.如:

After that, just reference the object it imports to send the emails instead of making objects for each store. Such as:

$StoreList = Import-XLS <path to Excel file>
GC <report folder> | %{
    $Current = $_
    $Store = $StoreList|?{$_.StoreNumber -match $Current.BaseName}|Select -ExpandProperty StoreNumber
    $Email = $StoreList|?{$_.StoreNumber -match $Current.BaseName}|Select -ExpandProperty StoreEmail
    <code to send $Current to $Email>
}

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