时间戳

首页 > 解决方案 > 如何将XML中的数组值插入Excel中行的每个顺序单元格，为每一行循环？

excel - 如何将XML中的数组值插入Excel中行的每个顺序单元格，为每一行循环？

问题描述

我有一个 XML 文件，其中包含给定月份给定日期的每个小时的值。

例如，开始日期/时间将是给定月份的上午 12:00 的 15 日，结束日期/时间将是下个月的上午 12:00 的 15 日。

XML 文件示例：

<Generated>
<Entry>
    <ID>76492055</ID>
    <Date>2018-09-15</Date>
    <Time>00:00:00</Time>
    <Income>746557.0993</Income>
</Entry>
<Entry>
    <ID>76492055</ID>
    <Date>2018-09-15</Date>
    <Time>01:00:00</Time>
    <Income>815445.5908</Income>
</Entry>
<Entry>
    <ID>76492055</ID>
    <Date>2018-09-15</Date>
    <Time>02:00:00</Time>
    <Income>1190228.1310</Income>
</Entry>
<Entry>
    <ID>76492055</ID>
    <Date>2018-09-15</Date>
    <Time>03:00:00</Time>
    <Income>932243.0268</Income>
</Entry>
<Entry>
    <ID>76492055</ID>
    <Date>2018-09-15</Date>
    <Time>04:00:00</Time>
    <Income>709702.1181</Income>
</Entry>
...
</Generated>

我当然可以在 PowerShell 中使用以下代码很好地显示数据：

$xml = [System.Xml.XmlDocument](Get-Content "TheXMLFile.xml")

[datetime]$EndDate = (Get-Date).AddMonths(0).ToString("yyyy-MM-15")
[datetime]$StartDate = (Get-Date).AddMonths(-1).ToString("yyyy-MM-15")

$IncomeDates = 0..(($EndDate - $StartDate).days-1) | % { 
$StartDate.AddDays($_).ToShortDateString() }

foreach ($IncomeDate in $IncomeDates) { 
    $xml.Generated.Entry | Where {$_.Date -eq $IncomeDate} | Select ID,Date,Time,Income
}

但是，我需要让 PowerShell 提取该月给定日期（每小时）的 XML 数据，并将其插入目标 Excel 工作表中该行（当天）的每个连续单元格中。

目标工作表示例：

目标工作表

标签： excelxmlpowershell

解决方案

假设您在电子表格中已经有顶行和左列，这将读取 XML，打开电子表格，读取日期范围，根据该日期范围创建对象数组，从 XML 填充数据，然后粘贴它进入电子表格。

$xml = [System.Xml.XmlDocument](Get-Content "TheXMLFile.xml")

$XL = New-Object -ComObject Excel.Application
$WB = $XL.workbooks.open('C:\Path\To\Spreadsheet.xls')
$DateRange = $WB.ActiveSheet.UsedRange.Columns.Item(1).Cells|% Text|Select -skip 1

# Create a hashtable representing a blank day
$BlankDay = [ordered]@{}
0..23|ForEach{$Blankday.Add(("{0}:00:00" -f "$_".Padleft(2,'0')),$null)}

# Build Hashtable to track all days as objects based off the blank day hashtable
$DataHash = [ordered]@{}
$DateRange | ForEach-Object{ $DataHash.add($_,[pscustomobject]$BlankDay) }

# Update each day with data from the XML
$xml.Generated.Entry | Where{$_.date -in $DateRange} | ForEach-Object {
    $DataHash[$_.Date]."$($_.Time)" = $_.Income
}
# Convert the data to a tab delimited CSV, skipping the header row, and copy it to the clipboard
$DataHash.Values|convertto-csv -del "`t" -notype|Select -Skip 1 | clip
# Paste it into cell B2
$WB.ActiveSheet.Rows.Item(2).Cells.Item(2).PasteSpecial()

推荐阅读