CODEWITHSUNDEEP
excelpowershell
methuselah
methuselah

Reputation: 13206

Read Excel sheet in Powershell

The below script reads the sheet names of an Excel document....

How could I improve it so it could extract all the contents of column B (starting from row 5 - so row 1-4 are ignored) in each worksheet and create an object?

E.g. if column B in worksheet 1 (called London) has the following values:

Marleybone
Paddington
Victoria
Hammersmith

and column C in worksheet 2 (called) Nottingham has the following values:

Alverton 
Annesley
Arnold
Askham

I'd want to create a object that from that looks like this:

City,Area
London,Marleybone
London,Paddington
London,Victoria
London,Hammersmith
Nottingham,Alverton 
Nottingham,Annesley
Nottingham,Arnold
Nottingham,Askham

This is my code so far:

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;
    }

$sheetname

Upvotes: 23

Views: 166074

Answers (5)

Shoshana Tzi
Shoshana Tzi

Reputation: 259

You can use ImportExcel which uses OfficeOpenXml

Upvotes: 0

MUY Belgium
MUY Belgium

Reputation: 2452

There is the possibility of making something really more cool!

# Powershell 
$xl = new-object -ComObject excell.application
$doc=$xl.workbooks.open("Filepath")
$doc.Sheets.item(1).rows |
% { ($_.value2 | Select-Object -first 3 | Select-Object -last 2) -join "," }

Upvotes: 0

Dirk
Dirk

Reputation: 676

This assumes that the content is in column B on each sheet (since it's not clear how you determine the column on each sheet.) and the last row of that column is also the last row of the sheet.

$xlCellTypeLastCell = 11 
$startRow = 5 
$col = 2 

$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++)
{
    $sh = $wb.Sheets.Item($i)
    $endRow = $sh.UsedRange.SpecialCells($xlCellTypeLastCell).Row
    $city = $sh.Cells.Item($startRow, $col).Value2
    $rangeAddress = $sh.Cells.Item($startRow + 1, $col).Address() + ":" + $sh.Cells.Item($endRow, $col).Address()
    $sh.Range($rangeAddress).Value2 | foreach 
    {
        New-Object PSObject -Property @{ City = $city; Area = $_ }
    }
}

$excel.Workbooks.Close()

Upvotes: 29

xBr0k3n
xBr0k3n

Reputation: 415

This was extremely helpful for me when trying to automate Cisco SIP phone configuration using an Excel spreadsheet as the source. My only issue was when I tried to make an array and populate it using $array | Add-Member ... as I needed to use it later on to generate the config file. Just defining an array and making it the for loop allowed it to store correctly.

$lastCell = 11 
$startRow, $model, $mac, $nOF, $ext = 1, 1, 5, 6, 7

$excel = New-Object -ComObject excel.application
$wb = $excel.workbooks.open("H:\Strike Network\Phones\phones.xlsx")
$sh = $wb.Sheets.Item(1)
$endRow = $sh.UsedRange.SpecialCells($lastCell).Row

$phoneData = for ($i=1; $i -le $endRow; $i++)
            {
                $pModel = $sh.Cells.Item($startRow,$model).Value2
                $pMAC = $sh.Cells.Item($startRow,$mac).Value2
                $nameOnPhone = $sh.Cells.Item($startRow,$nOF).Value2
                $extension = $sh.Cells.Item($startRow,$ext).Value2
                New-Object PSObject -Property @{ Model = $pModel; MAC = $pMAC; NameOnPhone = $nameOnPhone; Extension = $extension }
                $startRow++
            }

I used to have no issues adding information to an array with Add-Member but that was back in PSv2/3, and I've been away from it a while. Though the simple solution saved me manually configuring 100+ phones and extensions - which nobody wants to do.

Upvotes: 3

Chris
Chris

Reputation: 71

Sorry I know this is an old one but still felt like helping out ^_^

Maybe it's the way I read this but assuming the excel sheet 1 is called "London" and has this information; B5="Marleybone" B6="Paddington" B7="Victoria" B8="Hammersmith". And the excel sheet 2 is called "Nottingham" and has this information; C5="Alverton" C6="Annesley" C7="Arnold" C8="Askham". Then I think this code below would work. ^_^

$xlCellTypeLastCell = 11 
$startRow = 5

$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++)
    {
        $sh = $wb.Sheets.Item($i)
        $endRow = $sh.UsedRange.SpecialCells($xlCellTypeLastCell).Row
        $col = $col + $i - 1
        $city = $wb.Sheets.Item($i).name
        $rangeAddress = $sh.Cells.Item($startRow, $col).Address() + ":" + $sh.Cells.Item($endRow, $col).Address()
        $sh.Range($rangeAddress).Value2 | foreach{
            New-Object PSObject -Property @{City = $city; Area=$_}
        }
    }

$excel.Workbooks.Close()

This should be the output (without the commas):

City, Area
---- ----
London, Marleybone
London, Paddington
London, Victoria
London, Hammersmith
Nottingham, Alverton
Nottingham, Annesley
Nottingham, Arnold
Nottingham, Askham

Upvotes: 7

Related Questions