In this blog, I want to discuss a few commands that can be used when we have to do an update on Excel cells using PowerShell. Recently I was working on a script for updating an exported Excel document, and finding commands for various functions in one place was difficult. Here are a few commands that are helpful for updating an Excel document using the power of PowerShell:
Open a New Excel Workbook/Worksheet
$excel = New-Object -Com Excel.Application
$wb = $excel.Workbooks.Add()
$ws = $wb.Worksheets.Add()
Load an Already Existing Excel File
$excel = New-Object -Com Excel.Application
$importFile = 'C:\Users\User\Downloads\exceldoc.xlsx'
#Open the importfile for edit.
$wb = $excel.Workbooks.Open($importFile)
Get the Worksheet and Rename It
$ws = $wb.sheets.item(1)
$ws.name = "Asset"
Get the Last Row and Last Column Used
This could be used in iterating through the used range in a worksheet.
$ws.UsedRange.columns.count
$ws.UsedRange.rows.count
Read Value From a Cell
$header= $ws.Cells.Item(1, 1).text
Add or Modify Value in a Cell
#update the cell with 'New value'
$ws.Cells.Item(1,1).Value="New value"
Delete Entire Row or Column
[void]$ws.Cells.Item(1,1).EntireColumn.Delete()
[void]$ws.Cells.Item(1,1).EntireRow.Delete()
Save the Workbook
$excel.Save()
Quit the Workbook
$excel.Quit()
Conclusion
These commands can be used together to mass update the cells based on conditions and iterations. You could add many more to the above list, but the ones mentioned are the ones I use and find most helpful for mass updations.
Thanks for reading!