PowerShell ed Excel: sfrutta il potere di ImportExcel – techsyncer

Microsoft Excel è uno di quegli strumenti ubiqui che la maggior parte di noi non può evitare, nemmeno se ci provasse. Molti professionisti IT utilizzano Excel come un piccolo database in cui archiviare tonnellate di dati in vari processi di automazione. Qual è il miglior scenario per l’automazione con Excel? PowerShell ed Excel!

Le tabelle di Excel sono sempre state notoriamente difficili da scriptare ed automatizzare. A differenza dei file CSV meno complessi, i lavori di Excel non sono semplici file di testo. I lavori di Excel richiedono a PowerShell la manipolazione di oggetti COM (Component Object Model) complessi, quindi era necessario avere Excel installato. Ma non più.

Fortunatamente, un membro attento della community di PowerShell, Doug Finke, ha creato un modulo PowerShell chiamato ImportExcel per noi comuni mortali. Il modulo ImportExcel astrae tutta questa complessità. Rende possibile gestire facilmente i lavori di Excel e utilizzare lo scripting di PowerShell!

In questo articolo, esploreremo cosa è possibile fare con PowerShell ed Excel utilizzando il modulo ImportExcel e alcuni casi d’uso popolari.

Prerequisiti

Quando esegui il modulo ImportExcel su un sistema Windows, non sono necessarie dipendenze separate. Tuttavia, se stai lavorando su macOS, dovrai installare il pacchetto mono-libgdiplus utilizzando brew install mono-libgdiplus. Tutti gli esempi in questo articolo verranno creati utilizzando macOS, ma tutti gli esempi dovrebbero funzionare su piattaforme incrociate.

Se stai utilizzando macOS, assicurati di riavviare la sessione di PowerShell prima di continuare.

Installazione del modulo ImportExcel

Inizia scaricando e installando il modulo tramite PowerShell Gallery eseguendo il comando Install-Module ImportExcel -Scope CurrentUser. Dopo alcuni istanti, sarai pronto per iniziare.

Utilizzo di PowerShell ed Excel per esportare su un foglio di lavoro

Potresti essere familiare con i cmdlet standard di PowerShell Export-Csv e Import-Csv. Questi cmdlet ti consentono di leggere ed esportare oggetti PowerShell in file CSV. Sfortunatamente, non esistono cmdlet Export-Excel e Import-Excel. Ma utilizzando il modulo ImportExcel, puoi creare la tua funzionalità.

Una delle richieste più comuni di un sysadmin è l’esportazione di oggetti PowerShell in un foglio di lavoro di Excel. Utilizzando il cmdlet Export-Excel nel modulo ImportExcel, puoi farlo facilmente.

Ad esempio, forse hai bisogno di trovare alcuni processi in esecuzione sul tuo computer locale e inserirli in un file di lavoro di Excel.

Il comando Export-Excel accetta qualsiasi oggetto esattamente come fa il comando Export-Csv. Puoi dirigere qualsiasi tipo di oggetto a questo comando.

Per trovare i processi in esecuzione su un sistema con PowerShell, utilizza il comando Get-Process che restituisce ogni processo in esecuzione e varie informazioni su ciascun processo. Per esportare queste informazioni su Excel, utilizza il comando Export-Excel fornendo il percorso del file di Excel che verrà creato. Puoi vedere un esempio del comando e uno screenshot del file di Excel generato di seguito.

Get-Process | Export-Excel -Path './processes.xlsx'

Worksheet created with PowerShell and Excel

Congratulazioni! Hai ora esportato tutte le informazioni proprio come Export-Csv, ma a differenza di Export-Csv, possiamo rendere questi dati molto più interessanti. Assicuriamoci che il nome del foglio di lavoro sia chiamato Processi, che i dati siano in una tabella e che le righe si adattino automaticamente.

Utilizzando il parametro di commutazione AutoSize per ridimensionare automaticamente tutte le righe, TableName per specificare il nome della tabella che includerà tutti i dati e il parametro WorksheetName di nome Processes, puoi vedere nella schermata sottostante cosa può essere costruito.

Get-Process | Export-Excel -Path './processes.xlsx' -AutoSize -TableName Processes -WorksheetName Proccesses

Il cmdlet Export-Excel ha un sacco di parametri che puoi utilizzare per creare fogli di calcolo di Excel di tutti i tipi. Per una panoramica completa di tutto ciò che Export-Excel può fare, esegui Get-Help Export-Excel.

Utilizzare PowerShell per importare in Excel

Quindi hai esportato alcune informazioni in un file chiamato processes.xlsx nella sezione precedente. Forse ora hai bisogno di spostare questo file su un altro computer e importare/leggere queste informazioni con PowerShell ed Excel. Nessun problema. Hai a disposizione Import-Excel.

Nel suo utilizzo più basilare, devi solo fornire il percorso al documento/foglio di calcolo di Excel utilizzando il parametro Path come mostrato di seguito. Vedrai che legge il primo foglio di lavoro, in questo caso il foglio di lavoro Processes, e restituisce oggetti PowerShell.

Import-Excel -Path './processes.xlsx'

Forse hai più fogli di lavoro in un foglio di calcolo di Excel? Puoi leggere un foglio di lavoro specifico utilizzando il parametro WorksheetName.

Import-Excel -Path './processes.xlsx' -WorkSheetname SecondWorksheet

Hai bisogno di leggere solo determinate colonne dal foglio di lavoro di Excel? Utilizza il parametro HeaderName per specificare solo i parametri che desideri leggere.

Import-Excel -Path './processes.xlsx' -WorkSheetname Processes -HeaderName 'CPU','Handle'

Il cmdlet Import-Excel ha altri parametri che puoi utilizzare per leggere i fogli di lavoro di Excel di ogni tipo. Per una panoramica completa di tutto ciò che Import-Excel può fare, esegui il comando Get-Help Import-Excel.

Utilizzando PowerShell per ottenere (e impostare) i valori delle celle di Excel

Ora sai come leggere un intero foglio di lavoro con PowerShell ed Excel, ma cosa succede se hai bisogno di un singolo valore di una cella? Tecnicamente, puoi utilizzare Import-Excel e filtrare il valore desiderato con Where-Object, ma non sarebbe molto efficiente.

Invece, utilizzando il cmdlet Open-ExcelPackage, puoi “convertire” un foglio di lavoro di Excel in un oggetto PowerShell che può quindi essere letto e manipolato. Per trovare il valore di una cella, apri prima il foglio di lavoro di Excel per portarlo in memoria.

$excel = Open-ExcelPackage -Path './processes.xlsx'

Il cmdlet Open-ExcelPackage è simile all’utilizzo di New-Object -comobject excel.application se si lavora direttamente con gli oggetti COM.

Successivamente, seleziona il foglio di lavoro all’interno del file di Excel.

$worksheet = $excel.Workbook.Worksheets['Processes']

Questo processo è simile al modo di aprire i fogli di lavoro con gli oggetti COM utilizzando excel.workbooks.open.

Una volta che hai assegnato il foglio di lavoro a una variabile, puoi ora navigare tra righe, colonne e celle individuali. Forse hai bisogno di trovare tutti i valori delle celle nella riga A1. Devi semplicemente fare riferimento alla proprietà Cells fornendo un indice di A1 come mostrato di seguito.

$worksheet.Cells['A1'].Value

Puoi anche cambiare il valore delle celle in un foglio di lavoro assegnando un valore diverso, ad esempio $worksheet.Cells['A1'] = 'valorediverso'

Una volta in memoria, è importante rilasciare il pacchetto di Excel utilizzando il cmdlet Close-ExcelPackage.

Close-ExcelPackage $excel

Convertire i fogli di lavoro in file CSV con PowerShell ed Excel

Una volta che hai i contenuti di un foglio di lavoro di Excel rappresentati tramite oggetti PowerShell, “convertire” i fogli di lavoro di Excel in CSV richiede semplicemente l’invio di quegli oggetti al cmdlet Export-Csv.

Utilizzando il file di lavoro processes.xlsx creato in precedenza, leggi il primo foglio di lavoro che ottiene tutti i dati in oggetti PowerShell e quindi esporta quegli oggetti in CSV utilizzando il comando seguente.

Import-Excel './processes.xlsx' | Export-Csv -Path './processes.csv' -NoTypeInformation

Se apri il file CSV risultante, vedrai gli stessi dati all’interno del foglio di lavoro Processes (in questo esempio).

Conversione di più fogli di lavoro

Se hai un file di lavoro di Excel con più fogli di lavoro, puoi anche creare un file CSV per ogni foglio di lavoro. Per farlo, puoi trovare tutti i fogli in un file di lavoro utilizzando il cmdlet Get-ExcelSheetInfo. Una volta ottenuti i nomi dei fogli di lavoro, puoi passare quei nomi al parametro WorksheetName e utilizzare anche il nome del foglio come nome del file CSV.

Di seguito puoi trovare il codice di esempio necessario utilizzando PowerShell ed Excel.

## trovare ogni foglio di lavoro nel workbook
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## leggere ogni foglio e creare un file CSV con lo stesso nome
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

Conclusion

Utilizzando PowerShell ed Excel, è possibile importare, esportare e gestire i dati nei fogli di lavoro di Excel esattamente come si farebbe con i file CSV senza dover installare Excel!

In questo articolo, hai imparato le basi per leggere e scrivere dati in un foglio di lavoro di Excel, ma questo è solo l’inizio. Utilizzando PowerShell e il modulo ImportExcel, è possibile creare grafici, tabelle pivot e sfruttare altre potenti funzionalità di Excel!