CODEWITHSUNDEEP
excelpowershell
Charles Ryan
Charles Ryan

Reputation: 23

How do you add data into an excel spreadsheet from Powershell?

I am trying to add data into a spreadsheet into Excel from Powershell where in column A, it says "Asset Name" and in Column B it says "Host Name". I will be using a variable name $ServerName that will have data that needs to be written in the second row of these columns ($ServerName = TestServer).

The worksheet name is "Asset Search - Server Required"

The spreadsheet is .xlsx

Can anyone help me with this?

Upvotes: 1

Views: 23512

Answers (1)

Aatif Akhter
Aatif Akhter

Reputation: 2206

Here is an example for your reference-

$excel = New-Object -ComObject excel.application
$excel.visible = $True
$workbook = $excel.Workbooks.Add()
$workbook.Worksheets.Add()
$Data= $workbook.Worksheets.Item(1)
$Data.Name = 'MySpreadsheet'
$Data.Cells.Item(1,1) = 'Asset Name'
$Data.Cells.Item(1,2) = 'HostName'

# Insert Data   
$Data.Cells.Item(3,1) = "MyAssetName"
$Data.Cells.Item(3,2) = "MyHostName"

# Format, save and quit excel
$usedRange = $Data.UsedRange                                                                                              
$usedRange.EntireColumn.AutoFit() | Out-Null
$workbook.SaveAs("C:\MyExcel.xlsx")
$excel.Quit()

Upvotes: 8

Related Questions