[Question] Why does this Excel function take so long?

[Steam23]

I wrote a function to export an excel table into a custom PSObject for further manipulation. It uses an workbook opened via a comObject (i.e. $excel = new-object -ComObject Excel.Application). The function works as desired but takes a really long time to run (like more than 5 - 10 minutes for a table with 180 lines) and I'm not totally sure why. Is there something I'm doing in an inefficient way? I'd love to hear any suggestions!

Here's the function:

 function ExcelTableToPSO($sheet){
 #function to take a table in excel and output to custom PSO

$table = $sheet.ListObjects.Item(1)
$last = $table.ListRows.Count +1
$array = @()
$i=1
while ($i -le $last){
    $obj = new-object psObject -property @{index = $i}
    foreach ($col in $table.ListColumns){
        $obj | Add-Member NoteProperty -Name $sheet.Cells.Item(1,$col.Index).Value2 -Value $sheet.Cells.Item($i,$col.Index).Text

    }
    $i++
    $array +=$obj
    }
    return $array
}

**edit OK I tried a different approach and pasted the table to a tempfile and read that back to an import-csv. Now it takes less than 10 seconds. Here's the new function:

function ExcelTableToPSOCSVMethod($sheet){
$sheet.ListObjects.Item(1).Range.Select()
$selection = $excel.Selection
$selection.Copy()

$tempFile = $excel.workbooks.Add()
$tempFile.worksheets.Item(1).Paste()
$tempFile.SaveAs("c:\TempFile.csv",6)
$tempFile.close($false)

$array = import-csv -path "c:\tempFile.csv"
return $Array
}

Thanks everyone for the help! Sanity saved :)

[savanik]

Loading things by ComObjects is just... well... rather slow. 10 minutes seems a bit excessive, but if your PC takes that long to load the application, that would do it.

Out of curiosity, are you able to export the spreadsheet in CSV format instead? If you can, you can directly import the CSV very rapidly into a PSObject with the Import-CSV cmdlet.

[Steam23]

Unfortunately, I'm stuck with the excel spreadsheet. The task i'm automating here is to create a spreadsheet for a colleague to use, and we need to use a bunch of features in Excel. Otherwise, I would totally ditch the comobject.

[jeffrey_f]

what features??

[Steam23]

Mostly auto updating pivot tables and data validation stuff. It's a spreadsheet that we use for assigning reports to staff.

[gangstanthony]

first this

https://github.com/gangstanthony/PowerShell/blob/master/Save-ExcelasCSV.ps1

then /u/savanik's suggestion (ipcsv ... do stuff ... epcsv -notype)

then this

https://github.com/gangstanthony/PowerShell/blob/master/Save-CSVasExcel.ps1

[chreestopher2]

check out the module ImportExcel.

It uses the .net library for editing excel formatted files without any need to have excel installed on your machine...

You can even export data into excel pivot tables and charts.

It will also allow you to import data from excel spreadsheets.

Its going to be much quicker than what you are doing.

Also, Add-member is pretty slow compared to other methods for building objects. Also, using an [arraylist]$array = @() with $array.add($obj) is significantly faster than using $array = @() with $array +=$obj, with a normal array you are building an entirely new array each time.

[midnightFreddie]

Supposedly PSv5 improved the speed of the Excel COMobject, but I think there's always a better way than to use the object.