Powershell将数据写入excel文件 [英] Powershell writing data into excel file
问题描述
我有两个数组$ ActiveUsers和$ InactiveUsers有4个字段和每个后面的数据
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ，Josh
fgh8458 | Kayla Benson | 95463 | Trot，Cook K
ndcf846 | Sam Huff | 56737 | Mcghee，John
vdfg456 | Mark Jones | 67843 | Hart，Josh W
fasf345 | Amber Sean | 24678 | John，Kneel G
$ b $ InavtiveUsers = fasd034 | Colin Hart | 35473 | David，Casper
aertp56 | Andy Matthews | 56849 | Debby，Gould K
Ahshb86 | Mark Michael | 46848 | Ty，Justin H
gkr5057 | Josh Meeker | 56848 | Ashley，Rhonda R
irrk106 | Tom Mortin | 64838 |贝克，Alan
eqer348 | Nathan Willis | 469894 | Baker，John T
现在，我试图拉ea从$ ActiveUsers ch行，并将其添加到我创建的Excel文件。我对$ InactiveUsers中的每一行做了相同的处理，并将它们添加到一个新的Excel文件中。
需求略有变化，而不是将两个数组添加到两个Excel文件，我必须把所有的东西放在一个Excel文件中，另外我需要突出显示（有一些颜色）我从$ InactiveUsers中得到的行在那个Excel文件里，我写了所有东西。
有没有办法，我可以通过两个数组同时循环，并通过higlighting我从$ InactiveUsers中获得的行将行写入一个Excel文件？下面是我写到目前为止
$ ExcelObject = new-Object -comobject Excel.Application
$ ExcelObject.visible = $ false
$ ExcelObject.DisplayAlerts = $ false
$ date = get-date -formatyyyyMMddHHss
$ strPath1 =o：\UserCert\Active_Users_ $ date.xlsx
if（Test-Path $ strPath1）{
＃打开文档
$ ActiveWorkbook = $ ExcelObject.WorkBooks.Open（$ strPath1）
$ ActiveWorksheet = $ ActiveWorkbook.Worksheets。 （1）
} else {
＃创建Excel文件
$ ActiveWorkbook = $ ExcelObject.Workbooks.Add（）
$ ActiveWorksheet = $ ActiveWorkbook.Worksheets.Item（1）
$ b $＃添加头文件到excel文件
$ ActiveWorksheet.Cells.Item（1,1）=User_Id
$ ActiveWorksheet.cells.item（1,2）= User_Name
$ ActiveWorksheet.cells.item（1,3）=CostCenter
$ ActiveWorksheet.cells.item（1,4）=审批管理器
$ format = $ ActiveWorksheet .UsedRange
$ format.Interior.ColorIndex = 19
$ format.Font.Colo rIndex = 11
$ format.Font.Bold =True
}
＃通过数组循环并将数据添加到创建的excel文件中。
foreach $ $ $ $ $ $ b $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ActiveWorksheet。 UsedRange.Rows.Count + 1
$ ActiveWorksheet.cells.item（$ introw，1）= $ user_id
$ ActiveWorksheet.cells.item（$ introw，2）= $ user_name
$ ActiveWorksheet.cells.item（$ introw，3）= $ Costcntr
$ ActiveWorksheet.cells.item（$ introw，4）= $ ApprMgr
$ ActiveWorksheet.UsedRange.EntireColumn.AutoFit（）;
}
我重复了$ InactiveUsers的上述内容。如果两个数组的记录数相同，则可以这样做：
（$ i = 0，$ i -lt $ activeUsers.Length; $ i ++）{
（$ user_id，$ user_name，$ Costcntr，$ ApprMgr）= $ activeUsers [$ i ] .split（'|'）
$ row = 2 * $ i + 2
$ ActiveWorksheet.Cells.Item（$ i，1）= $ user_id
$ ActiveWorksheet.Cells.Item （$ i，2）= $ user_name
$ ActiveWorksheet.Cells.Item（$ i，3）= $ Costcntr
$ ActiveWorksheet.Cells.Item（$ i，4）= $ ApprMgr
（$ user_id，$ user_name，$ Costcntr，$ ApprMgr）= $ inactiveUsers [$ i] .split（'|'）
$ ActiveWorksheet.Cells.Item（$ i + 1，1）= $ user_id
$ ActiveWorksheet.Cells.Item（$ i + 1,2）= $ user_name
$ ActiveWorksheet.Cells.Item（$ i + 1，3）= $ Costcntr
$ ActiveWorksheet.Cells。 Item（$ i + 1，4）= $ ApprMgr
$ ActiveWorksheet.Rows.Item（$ i + 1）.EntireRow.Interior.ColorIndex = 3
}
如果两个数组长度不同，可以这样做：
if（$ activeUsers.Length -gt $ inactiveUsers.Length）{
$ larger = $ activeUsers
$ small = $ inactiveUsers
} else {
$ larger = $ inactiveUsers
$ smaller = $ activeUsers
}
for（$ i = 0，$ i - 长$小。 $ $ b $ {
$ user_id $ user_name $ Costcntr $ ApprMgr $ small $ [$ i] .split（'|'）
...
（$ user_id， $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ I -lt $ large.Length; $ i ++）{
（$ user_id，$ user_name，$ Costcntr，$ ApprMgr）= $ larger [$ i] .split（'|'）
...
$ / code> 然而，更好的解决方案可能是引入一个额外的列，账户状态：
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 。
（$ i = 0，$ i -lt $ activeUsers.Length; $ i ++）{
（$ user_id，$ user_name，$ Costcntr，$ ApprMgr）= $ activeUsers [$ i]。 split（'|'）
$ row = 2 * $ i + 2
$ ActiveWorksheet.Cells.Item（$ i，1）= $ user_id
$ ActiveWorksheet.Cells.Item（$ i，2）= $ user_name
$ ActiveWorksheet.Cells.Item（$ i，3）= $ Costcntr
$ ActiveWorksheet.Cells.Item（$ i，4）= $ ApprMgr
$ USER_ID，$ USER_NAME，$ Costcntr，$ ApprMgr） = $ inactiveUsers [$ i] .split（'|'）
$ ActiveWorksheet.Cells.Item（$ i + 1，1）= $ user_id
$ ActiveWorksheet.Cells.Item（$ i + 1 ，2）= $ user_name
$ ActiveWorksheet.Cells.Item（$ i + 1，3）= $ Costcntr
$ ActiveWorksheet.Cells.Item（$ i + 1,4）= $ ApprMgr
$ ActiveWorksheet.Cells.Item（$ i + 1，5）=x
}
$ b $那么你可以使用条件格式来突出显示第5列的值是x的行（该格式的公式是 =（INDIRECT （E& ROW（）））=x）。
如果您要引入上述附加列，你甚至可以像这样简化脚本：
$ users = $ activeUsers | ％{$ _ +| }
$ users + = $ inactiveUsers | $ {$ _ +| x}
...
$ ActiveWorksheet.Cells.Item（1,5）=Inactive
...
for （$ | $ i $）{
（$ user_id，$ user_name，$ Costcntr，$ ApprMgr，$ inactive）= $ users [$ i] .split（'|'i = 0，$ i -lt $ users.Length; ）
$ ActiveWorksheet.Cells.Item（$ i + 2，1）= $ user_id
$ ActiveWorksheet.Cells.Item（$ i + 2,2）= $ user_name
$ ActiveWorksheet。 Cells.Item（$ i + 2，3）= $ Costcntr
$ ActiveWorksheet.Cells.Item（$ i + 2,4）= $ ApprMgr
$ ActiveWorksheet.Cells.Item（$ i + 2 ，5）= $ inactive
}
hoping to get some help with what i'am trying to achieve here.
i have two arrays $ActiveUsers and $InactiveUsers with 4 fields and following data in each
$ActiveUsers = fahe532|Allyson Sullivan|34563|Cain,Ashley J
pers274|Martha Walsh|53732|Mckenny,Josh
fgh8458|Kayla Benson|95463|Trot,Cook K
ndcf846|Sam Huff|56737|Mcghee,John
vdfg456|Mark Jones |67843|Hart,Josh W
fasf345|Amber Sean|24678|John,Kneel G
$InavtiveUsers = fasd034|Colin Hart|35473|David, Casper
aertp56|Andy Matthews|56849|Debby, Gould K
ahshb86|Mark Michael|46848|Ty, Justin H
gkr5057|Josh Meeker|56848|Ashley, Rhonda R
irrk106|Tom Mortin|64838|Becks, Alan
eqer348|Nathan Willis|469894|Baker ,John T
Now, i'am trying to pull each row from $ActiveUsers and adding it to a excel file i'am creating. I did the same for each row in $InactiveUsers and added them into a new Excel file
There was a slight change in the requirement, instead of having two arrays added into two Excel files i have to put everything in one excel file, Also i need to highlight(with some color) the rows i'am getting from $InactiveUsers in that one excel file where i'am writing everything.
Is there a way i can loop through two arrays simultaneoulsly and write rows into one excel file by higlighting the rows i get from $InactiveUsers? Below is what i wrote so far
$ExcelObject = new-Object -comobject Excel.Application
$ExcelObject.visible = $false
$ExcelObject.DisplayAlerts =$false
$date= get-date -format "yyyyMMddHHss"
$strPath1="o:\UserCert\Active_Users_$date.xlsx"
if (Test-Path $strPath1) {
#Open the document
$ActiveWorkbook = $ExcelObject.WorkBooks.Open($strPath1)
$ActiveWorksheet = $ActiveWorkbook.Worksheets.Item(1)
} else {
# Create Excel file
$ActiveWorkbook = $ExcelObject.Workbooks.Add()
$ActiveWorksheet = $ActiveWorkbook.Worksheets.Item(1)
#Add Headers to excel file
$ActiveWorksheet.Cells.Item(1,1) = "User_Id"
$ActiveWorksheet.cells.item(1,2) = "User_Name"
$ActiveWorksheet.cells.item(1,3) = "CostCenter"
$ActiveWorksheet.cells.item(1,4) = "Approving Manager"
$format = $ActiveWorksheet.UsedRange
$format.Interior.ColorIndex = 19
$format.Font.ColorIndex = 11
$format.Font.Bold = "True"
}
#Loop through the Array and add data into the excel file created.
foreach ($line in $Activeusers){
($user_id,$user_name,$Costcntr,$ApprMgr) = $line.split('|')
$introw = $ActiveWorksheet.UsedRange.Rows.Count + 1
$ActiveWorksheet.cells.item($introw, 1) = $user_id
$ActiveWorksheet.cells.item($introw, 2) = $user_name
$ActiveWorksheet.cells.item($introw, 3) = $Costcntr
$ActiveWorksheet.cells.item($introw, 4) = $ApprMgr
$ActiveWorksheet.UsedRange.EntireColumn.AutoFit();
}
I repeated the above for $InactiveUsers.
If both arrays have the same number of records you could do something like this:
for ($i = 0, $i -lt $activeUsers.Length; $i++) {
($user_id,$user_name,$Costcntr,$ApprMgr) = $activeUsers[$i].split('|')
$row = 2 * $i + 2
$ActiveWorksheet.Cells.Item($i, 1) = $user_id
$ActiveWorksheet.Cells.Item($i, 2) = $user_name
$ActiveWorksheet.Cells.Item($i, 3) = $Costcntr
$ActiveWorksheet.Cells.Item($i, 4) = $ApprMgr
($user_id,$user_name,$Costcntr,$ApprMgr) = $inactiveUsers[$i].split('|')
$ActiveWorksheet.Cells.Item($i+1, 1) = $user_id
$ActiveWorksheet.Cells.Item($i+1, 2) = $user_name
$ActiveWorksheet.Cells.Item($i+1, 3) = $Costcntr
$ActiveWorksheet.Cells.Item($i+1, 4) = $ApprMgr
$ActiveWorksheet.Rows.Item($i+1).EntireRow.Interior.ColorIndex = 3
}
If the length of both arrays differs, you could do something like this:
if ($activeUsers.Length -gt $inactiveUsers.Length) {
$larger = $activeUsers
$smaller = $inactiveUsers
} else {
$larger = $inactiveUsers
$smaller = $activeUsers
}
for ($i = 0, $i -lt $smaller.Length; $i++) {
($user_id,$user_name,$Costcntr,$ApprMgr) = $smaller[$i].split('|')
...
($user_id,$user_name,$Costcntr,$ApprMgr) = $larger[$i].split('|')
...
}
for ($i = $smaller.Length, $i -lt $larger.Length; $i++) {
($user_id,$user_name,$Costcntr,$ApprMgr) = $larger[$i].split('|')
...
}
A better solution, however, might be to introduce an additional column, indicating the state of the account:
$ActiveWorksheet.Cells.Item(1,5) = "Inactive"
...
for ($i = 0, $i -lt $activeUsers.Length; $i++) {
($user_id,$user_name,$Costcntr,$ApprMgr) = $activeUsers[$i].split('|')
$row = 2 * $i + 2
$ActiveWorksheet.Cells.Item($i, 1) = $user_id
$ActiveWorksheet.Cells.Item($i, 2) = $user_name
$ActiveWorksheet.Cells.Item($i, 3) = $Costcntr
$ActiveWorksheet.Cells.Item($i, 4) = $ApprMgr
($user_id,$user_name,$Costcntr,$ApprMgr) = $inactiveUsers[$i].split('|')
$ActiveWorksheet.Cells.Item($i+1, 1) = $user_id
$ActiveWorksheet.Cells.Item($i+1, 2) = $user_name
$ActiveWorksheet.Cells.Item($i+1, 3) = $Costcntr
$ActiveWorksheet.Cells.Item($i+1, 4) = $ApprMgr
$ActiveWorksheet.Cells.Item($i+1, 5) = "x"
}
Then you could use a conditional format to highlight rows where the value of the 5th column is "x" (the formula for that format would be =(INDIRECT("E"&ROW()))="x").
If you're going to introduce the abovementioned additional column, you could even simplify the script like this:
$users = $activeUsers | % { $_ + "|" }
$users += $inactiveUsers | % { $_ + "|x" }
...
$ActiveWorksheet.Cells.Item(1,5) = "Inactive"
...
for ($i = 0, $i -lt $users.Length; $i++) {
($user_id,$user_name,$Costcntr,$ApprMgr,$inactive) = $users[$i].split('|')
$ActiveWorksheet.Cells.Item($i+2, 1) = $user_id
$ActiveWorksheet.Cells.Item($i+2, 2) = $user_name
$ActiveWorksheet.Cells.Item($i+2, 3) = $Costcntr
$ActiveWorksheet.Cells.Item($i+2, 4) = $ApprMgr
$ActiveWorksheet.Cells.Item($i+2, 5) = $inactive
}
这篇关于Powershell将数据写入excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
