How to read excel using Powershell

How to extract data from excel such that in first column having the details of machine and in second column having multiple user name and it read excel like it will delete all users from column 2 through machine name which is written in column 1. Script which I tried-

clear all

sheetname = @()

$excel=new-object -com excel.application
$wb=$excel.workbooks.open("c:\users\administrator\my_test.xls")
for ($i=1; $i -le $wb.sheets.count; $i++)
{
  $sheetname+=$wb.Sheets.Item($i).Name;
}

But unable to read in the format which I want to run.

Solution 1:

In Powershell, you can use the great ImportExcel module to handle all sorts of excel data:

# First-time setup, install the module:
Install-Module ImportExcel

# Import the module:
Import-Module ImportExcel

# Now, import the data from your existing excel document:
$Sheet = Import-Excel -Path 'c:\folder\file.xlsx' -WorksheetName Sheet1

# Display the data by using the column names:
$Sheet | Select 'Computername','Username'

You didn't say what your data looks like, or what you want to do with it, but here's an example:

# List the data, except the users for certain computername values
$Filtered = $Sheet | Where Computername -NotLike "*BadComputer*" |
  Select Computername,User

# Export the manipulated data back to a new excel sheet
$Filtered | Export-Excel -Path 'c:\folder\file.xlsx' -WorksheetName FilteredSheet