Improvement import-excel or explain, please

[Jireck-npds]

Hello
Improvement of the import-excel function with a "all tabs" parameter that would return a multi-dimensional array with as Key, the tab index as provided by the Get-ExcelSheetInfo function.
or something like that, so you can work on the object.

unless there's another method?

Regards

[jhoneill]

$hash = @{}
$e=Open-ExcelPackage C:\Users\mcp\desktop\Test.xlsx     
foreach ($sheet in $e.workbook.worksheets) {$hash[$sheet.name]=  Import-Excel -ExcelPackage $e -WorksheetName $sheet.name} 
Close-ExcelPackage $e -NoSave 

If you want the index instead of the name use $hash[$sheet.index]=

Tab indexes begin at 1. This will convert the hash table to a zero based array
$array = $hash[$e.Workbook.Worksheets.name]
But Sheet1 will be in $array[0]

[Jireck-npds]

Hello,
Thank you @jhoneill
I try something like that :
$arr = @()
$ok = Get-ExcelSheetInfo -path .\test.xlsx
foreach ($s in $ok) {
$arr[$s.Index]= Import-Excel -path .\test.xlsx -WorksheetName $s.name
}

Thank again for confirmation, i keep also your code.

And i think it's a good improvement for import-excel function to have a parameter like that :

$alltab = import-excel -path .\test.xlsx -tabs

we have with this all option in function :

• no parameter import last sheet
• with -WorksheetName import named sheet
• tabs import all tabs

It's just a idee

[dfinke]

It's a good idea! May write this up as an example or add it as a separate function, Get-AllExcelSheets.

[Jireck-npds]

Perfect !
I'm Happy to help you for this great powershell module @dfinke !

[dfinke]

@jhoneill question, should this be an example or supported function? Would you want to take it?

[jhoneill]

I would do it as an example. I think there are multiple ways that people might want to do this so the a function would only be right for some, better to show how it is done and let people roll their own.

I can take it.