使用powershell在excel表中表示不同数组中的值

bakd9h0s  于 2023-02-10  发布在  Shell
关注(0)|答案(1)|浏览(123)

我有两个数组,里面有值,相互对应,我用虚拟数据来表示我的问题,我有四个不同的数组。
Name = @("John","Mary","Sarah") Age = @("23","44","31") Hobby = @("Flying, Fishing","Dancing","Singing") Kids = @("Lucas","Simon, Lisa","Anna")
每个数组中的每个条目都与另一个相对应。数组1中的条目1属于数组2、3和4中的条目1。我想将它们列成一个表格,以数组名作为标题。每个数组中的值应该显示在列沿着的单个单元格中。如果值包含逗号,则应该将其分隔开,并将每个条目放在彼此下方的单个单元格中。
我目前正在powershell中创建一个脚本来创建这个excel文件。数组数据已经给出,我只需要创建excel工作表。
我想要的结果是这个(图片的上半部分),但我得到了这个(图片的下半部分)(https://i.stack.imgur.com/sUq2J.png)我尝试了几段代码和解决方案在互联网上找到,但没有满足我的需求。有人能帮助我吗?

m1m5dgzv

m1m5dgzv1#

这里是一个例子,逐步通过我的意思是指导你。
初始化多维数组

Clear-Host
$MDArrayData = @(
    @('John','Mary','Sarah'), 
    @('23','44','31'), 
    @('Flying, Fishing','Dancing','Singing'), 
    @('Lucas','Simon, Lisa','Anna')
)

按索引从每个数组中挑选目标样本数据以确保验证成功。我使用PowerShell variable squeezing将结果赋给变量并同时输出到屏幕。

(
    $MyDataObject = [PSCustomObject]@{
        Name  = $MDArrayData[0][0]
        Age   = $MDArrayData[1][0]
        Hobby = $MDArrayData[2][0]
        Kids  = $MDArrayData[3][0]
    }
)
# Results
<#
Name Age Hobby           Kids 
---- --- -----           ---- 
John 23  Flying, Fishing Lucas
#>

枚举主数组,数组0,形成报表

Clear-Host
$MDArrayData = @(
    @('John','Mary','Sarah'), 
    @('23','44','31'), 
    @('Flying, Fishing','Dancing','Singing'), 
    @('Lucas','Simon, Lisa','Anna')
)

0..(($MDArrayData[0]).Count -1) | 
ForEach-Object {
    (
        $MyDataObject = [PSCustomObject]@{
            Name  = $MDArrayData[0][$PSItem]
            Age   = $MDArrayData[1][$PSItem]
            Hobby = $MDArrayData[2][$PSItem]
            Kids  = $MDArrayData[3][$PSItem]
        }
    )
}
# Results
<#
Name  Age Hobby           Kids       
----  --- -----           ----       
John  23  Flying, Fishing Lucas      
Mary  44  Dancing         Simon, Lisa
Sarah 31  Singing         Anna  
#>

您可以选择跳过多维数组,只使用单个数组,结果是相同的,然后转到用例的MS Excel部分。

Clear-Host
$Name  = @("John","Mary","Sarah") 
$Age   = @("23","44","31") 
$Hobby = @("Flying, Fishing","Dancing","Singing") 
$Kids  = @("Lucas","Simon, Lisa","Anna")

使用PowerShell ForEach方法和自定义对象。

$DataIndex = 0
$Name.ForEach(
    {
        (
            $MyDataObject = [PSCustomObject]@{
                Name  = $PSItem
                Age   = $Age[$DataIndex]
                Hobby = $Hobby[$DataIndex]
                Kids  = $Kids[$DataIndex]
            }    
        )
        
        $DataIndex++            
    }
)

# Results
<#
Name  Age Hobby           Kids       
----  --- -----           ----       
John  23  Flying, Fishing Lucas      
Mary  44  Dancing         Simon, Lisa
Sarah 31  Singing         Anna  
#>

MSExcel转换需要最终Csv输出

Clear-Host
$MDArrayData = @(
    @('John','Mary','Sarah'), 
    @('23','44','31'), 
    @('Flying, Fishing','Dancing','Singing'), 
    @('Lucas','Simon, Lisa','Anna')
)

0..(($MDArrayData[0]).Count -1) | 
ForEach-Object {
    (
        $MyDataObject = [PSCustomObject]@{
            Name  = $MDArrayData[0][$PSItem]
            Age   = $MDArrayData[1][$PSItem]
            Hobby = $MDArrayData[2][$PSItem]
            Kids  = $MDArrayData[3][$PSItem]
        }
    )
} | 
# Create or overwrite and existing report
Export-Csv -Path 'D:\Temp\FamilyReport.csv' -NoTypeInformation -Force

创建MSExcel COM对象以打开文件

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$Excel         = New-Object -ComObject excel.application
$Excel.Visible = $True

Open your report for formatting

$Workbook                = $Excel.workbooks.open('D:\Temp\FamilyReport.csv')
$Range                   = $Excel.Range("A:D")
$Range.VerticalAlignment = -4160
$Range.WrapText          = $True
$Range.EntireColumn.AutoFit()

1..4 | 
ForEach-Object {$Workbook.ActiveSheet.Cells.Item(1, $PSItem).Interior.ColorIndex = 15}

$Workbook.ActiveSheet.Cells.Item(1, 1).EntireRow.Font.Bold = $True

其他格式

$Workbook.ActiveSheet.ListObjects.Add(1, $range, $true)

你仍然必须记得清理你自己后面的所有东西示例化。又名GarbageCollection

相关问题