Read Excel sheet in Powershell

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Read Excel sheet in Powershell

Examples related to excel

Examples related to powershell