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

0
votes

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?

1
excelpowershell
There are at least two ways you can do it, one requiring a module and the other using Excel's COM interface. What have you attempted so far? – alroc
I have been trying to use the ComObject Excel.Application – Charles Ryan
OK, then please show your code and point out where you're getting stuck. – alroc
Request you to include minimal code/effort in your question, So that people can understand your problem and help you. – Aatif Akhter

1 Answers

6
votes

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()