Powershell with Excel

Question

What I need to do is to extract the data in the excel row and output them into different rows on Excel. After that, I will need to use the extracted data and perform certain conditions on the extracted data.

This is my current script:

To open excel and apply the formulas

$excel = New-Object -ComObject excel.application
$filepath = 'D:\testexcel.xlsx'
$workbook = $excel.workbooks.open("$filepath")
$worksheet = $workbook.worksheets.item(1)

$excel.Visible = $true

$rows = $worksheet.range("A1").currentregion.rows.count 
$worksheet.range("S1:S$rows").formula = $worksheet.range("S1").formula

Function to find the row, apply the formula and output it

function test123(){
    param([string]$test123)

    $sourcefile = "D:\testexcel.xlsx"
    $sheetname = "abc"

    $excel = new-object -comobject excel.application
    $excel.Visible = $true
    $excelworkbook = $excel.Workbooks.open($sourcefile, 2, $true) 
    $excelworksheet = $excelworkbook.worksheets.item($sheetname)

    $row = 1
    $column = 1
    $found = $false
    while(($excelworksheet.cells.item($row, $column).value() -ne $null) -and($found -eq $false)){ 
        if(($excelworksheet.cells.item($row, $column).value()).toupper() -eq $test123.ToUpper()){
            write-host $excelworksheet.cells.item($row, $column).value() $excelworksheet.cells.item($row, $column+1).value(), 
            $excelworksheet.cells.item($row, $column +2).value() $found = $true
            }
        $row += 1
    }

    #close workbook
    $excelworkbook.close()
    $excel.quit()
}
test123 -test123 "Test123"

Please guide me and tell me if this is the right way to do it... Thanks

Prasoon Karunan V · Accepted Answer · 2018-12-13 03:05:21Z

4

Please have a look into the ImportExcel module by Douge Finke. This module has the capability to do what you need.

Get it from PowerShell gallery: Install-Module -Name ImportExcel

Github link: https://github.com/dfinke/ImportExcel

you can then do Get-Help Import-Excel -Examples which has pretty good examples.

answered Dec 13, 2018 at 3:05

Prasoon Karunan V

3,0653 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell with Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related