Powershell Excel自动化按日期保存工作簿 [英] Powershell Excel Automation Save Workbook by Date

问题描述

我正在创建一个PowerShell脚本，以使用SCVMM中的get-vm命令填充excel工作簿。

I am creating a powershell script to populate an excel workbook with information from the "get-vm" command in SCVMM.

保存文件路径目前正在进行中。
我想每天或每周运行脚本，并使用该日期作为文件名保存生成的excel工作簿。这可能吗？如何生成日期并将其用作保存excel输出的文件名？任何帮助将是伟大的。

The save filepath is a work in progress at the moment. I would like to run the script daily or weekly and save the generated excel workbook using that days date as a filename. Is this possible? How can I generate a date and use it as my filename for saving the excel output? any help would be great.

#run below line once and then comment out if not in VMM Command Shell. Will import modules for ISE/Powershell.
#Import-Module "C:\Program Files\Microsoft System Center 2012\Virtual Machine Manager\bin\psModules\virtualmachinemanager\virtualmachinemanager"

$server = Get-VMMServer -ComputerName "server"

$vminfo = Get-VM -VMMServer $server 

$xl=New-Object -ComObject "Excel.Application"
$wb=$xl.Workbooks.Add()
$ws=$wb.ActiveSheet
$cells=$ws.Cells
$xl.Visible=$True

$cells.item(1,1)="{0} VMM Server Report" -f $server.Name
$cells.item(1,1).font.bold=$True
$cells.item(1,1).font.size=18

#define some variables to control navigation
$row=3
$col=1

#insert column headings
"Name", "Description", "OperatingSystem", "CPUCount","Memory (GB)", "Status", "Hostname" | foreach {
$cells.item($row,$col)=$_
$cells.item($row,$col).font.bold=$True
$col++
}

foreach ($vm in $vminfo) {
$row++
$col=1
$cells.item($row,$col)=$vm.Name
$col++
$cells.item($row,$col)=$vm.Description
$col++
$cells.item($row,$col)=$vm.OperatingSystem.Name
$col++
$cells.item($row,$col)=$vm.CPUCount
$col++
$cells.item($row,$col)=$vm.Memory/1024
$col++
$cells.item($row,$col)=$vm.Status
$col++
$cells.item($row,$col)=$vm.HostName
}
$objRange = $ws.UsedRange 
$objRange.EntireColumn.Autofit() 

$date = get-date -DisplayHint date 

$filepath="C:\Users\paulm\Documents\"

if ($filepath) {
$wb.SaveAs($filepath)

}

已完成保存脚本如下所示：

Finished save script looks like this:

$date = Get-Date -Format yyyy-MM-dd 

$wb.SaveAs("C:\Users\paulm\Documents\$date")+ ".xls"

推荐答案

这个功能就是 Get-Date 。调用没有参数将返回当前日期。这样的事情

The function for that is Get-Date. Called without arguments it will return the current date. Something like this

(Get-Date -Format yyyy-MM-dd) + ".xls"

将给您一个可用的文件名。

will give you a usable filename.

这篇关于Powershell Excel自动化按日期保存工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！