Hi Guys,
I am writing a Script to batch deploy vm from spec XLS file which pre-manual input parmeter .
below is my script , I want to achive read value from XLS and auto read next data use (while statement). but it seems doesn't work, Could somebady can resolve my issue. also how to generate a log after deploy VM.?
I appreciated you can help me on that.!
Param([string]$file)
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $False
$workbook = $excel.Workbooks.Open($file)
$i = 4
$sheet = $workbook.Worksheets.Item("PC服务器虚拟机")
$project = $sheet.Cells.Item($i,1).text
$Name = $sheet.Cells.Item($i,3).text
$Temp = $sheet.Cells.Item($i,5).text
$NumCpu = $sheet.Cells.Item($i,6).text
$NumMem = $sheet.Cells.Item($i,7).text
$SizeDisk = $sheet.Cells.Item($i,8).text
$Clustername = $sheet.Cells.Item($i,13).text
$ESXi = $sheet.Cells.Item($i,14).text
$Vlan = $sheet.Cells.Item($i,17).text
$excel.Workbooks.Close()
$excel.Quit()
<#$NumCpus = $NumCpu -replace "[^0-9]"
$NumMems = $NumMem -replace "[^0-9]"
$SizeDisks = $SizeDisk -replace "[^0-9]#>
$value = @{"$project" = "$sheet.Cells.Item($i,1).text";
"$Name" = "$sheet.Cells.Item($i,3).text";
"$Temp" = "$sheet.Cells.Item($i,5).text";
"$NumCpu" = "$sheet.Cells.Item($i,6).text";
"$NumMem" = "$sheet.Cells.Item($i,7).text";
"$SizeDisk" = "$sheet.Cells.Item($i,8).text";
"$Clustername" = "$sheet.Cells.Item($i,13).text";
"$ESXi" = "$sheet.Cells.Item($i,14).text";
"$Vlan" = "$sheet.Cells.Item($i,17).text"}
$vms = @()
while ( "$Name" -ne "" )
{
$i++
$vms += $value
}
Foreach( $myvm in $vms) {
$Template = Get-Template $Temp
$VMHost = Get-VMHost $ESXi
$Folder = Get-folder $project
$Cluster = Get-Cluster $Clustername
#$Datastore = Get-Datastore $store
#$OSCustomization = Get-OSCustomizationSpec $vm.Customization
if(-Not(Get-Template -name $Temp)) {
$show = "Template is not exist, please input a valid template name"
Write-Host $show.toUpper() -ForegroundColor red -BackgroundColor yellow
exit 1
}
elseif (Get-VM -Name $Name) {
$display = "VM $Name is already exist, please input a different name"
Write-Host $display.toUpper() -ForegroundColor red -BackgroundColor yellow
exit 1
}
else {
New-VM -vmhost $VMHost -Name $Name -Template $Template -ResourcePool $Cluster -Location $Folder -RunAsync
$checkVM = $null
while ($checkVM -eq $null){
$checkVM = get-vm $Name -erroraction silentlycontinue | get-networkadapter
sleep 5
}
Set-VM -vm $Name -Numcpu $NumCpus -MemoryGB $NumMems -RunAsync -Confirm:$false
#$disk = Get-VM $Name | Get-HardDisk | ? {$_.Name -eq "Hard disk 1"}
#Set-HardDisk -harddisk $disk -CapacityGB $SizeDisks -Confirm:$false
$vmnet = Get-VM $Name | Get-NetworkAdapter | where { $_.Name -eq "Network Adapter 1" }
$vmnet | Set-NetworkAdapter -NetworkName $Vlan -StartConnected:$true -Confirm:$false
}
}
<# Writes Jobs in to log file
$Date = Get-Date
$logfile = "C:\scripts\log\autobuild" + "_" + $Date.Day + "-" + $Date.Month + "-" + $Date.Year + ".txt"
if (-not (test-path c:\scripts\log\))
{
MD c:\scripts\log | Out-Null
}
Receive-Job * | Out-File -Encoding ASCII -FilePath $logfile -Append
Remove-Job *
Write-Host "Automated VM build is completed." -ForegroundColor Yellow
Write-Host ""
Invoke-Item $logfile
exit
#>
Have you considered Doug's Import-Excel module?
It avoids all this Cell work, and tremendously simplifies importing a worksheet.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
