根据模板自动生成数据

 

#首先需将系统时间设置为每个月的第一个周一,然后运行此脚本
$wbs = 0..3
$sheets = 1..5
$rows = 4..6   #142
$definition = 1..40 | ForEach-Object {"rev. " + $_.ToString().PadLeft(3,"0")}
$date = Get-Date
$mins = 1..23
$secs = 1..59
$time = foreach($min in $mins) {
            foreach($sec in $secs) {
                $min.ToString() + ":" +  $sec.ToString()
        }}

$xl = New-Object -ComObject "Excel.Application"
$cmdbwb = $xl.Workbooks.Open("C:\Users\Administrator\Desktop\template.xlsx")
foreach($wb in $wbs) {
    foreach($sheet in $sheets) {
        $cmdbws = $cmdbwb.Worksheets.Item("SEPM Day $sheet")
        $cmdbws.Range("D2").Value2 = "Date: " + $date.AddDays($sheet - 1 + 7 * $wb).ToShortDateString()
        foreach($row in $rows) {
            $cmdbws.Range("D$row").Value2 = $date.AddDays($sheet - 1 + 7 * $wb).ToShortDateString() + " " + (Get-Random $time)   #Last Time Staus Changed
            $cmdbws.Range("E$row").value2 = $date.AddDays($sheet - 2 + 7 * $wb).ToShortDateString() + " " + (Get-Random $definition)   #Virus Definition
            $cmdbws.Range("F$row").value2 = $date.AddDays($sheet - 1 + 7 * $wb).ToShortDateString() + " " + (Get-Random $time)   #Last Scan
        }
    }
$cmdbws.SaveAs("C:\Users\Administrator\Desktop\Week " + (1 + $wb) + ".xlsx")
}

$xl.Quit()
$cmdbws = $null
$cmdbwb = $null
$cmdbws = $null
$cmdbwb = $null
$xl = $null