PowerShell y Excel: Aprovecha el Poder de ImportExcel – techsyncer

Excel de Microsoft es una de esas herramientas ubicuas de las que la mayoría de nosotros no podemos escapar ni siquiera si lo intentamos. Muchos profesionales de TI utilizan Excel como una pequeña base de datos almacenando toneladas de datos en varias rutinas de automatización. ¿Cuál es el mejor escenario para la automatización y Excel? ¡PowerShell y Excel!

Las hojas de cálculo de Excel siempre han sido notoriamente difíciles de script y automatizar. A diferencia de su contraparte de archivo CSV menos destacada (y más simple), los libros de Excel no son simplemente archivos de texto. Los libros de Excel requieren PowerShell para manipular objetos complicados del Modelo de Objetos de Componentes (COM), por lo que era necesario tener Excel instalado. Ya no.

Afortunadamente, un perspicaz miembro de la comunidad de PowerShell, Doug Finke, creó un módulo de PowerShell llamado ImportExcel para nosotros simples mortales. El módulo ImportExcel abstrae toda esa complejidad. ¡Hace posible administrar libros de Excel y sumergirse en PowerShell scripting fácilmente!

En este artículo, exploremos qué puedes hacer con PowerShell y Excel utilizando el módulo ImportExcel y algunos casos de uso populares.

Prerrequisitos

Cuando ejecutas el módulo ImportExcel en un sistema Windows, no son necesarias dependencias adicionales. Sin embargo, si estás trabajando en macOS, deberás instalar el paquete mono-libgdiplus utilizando brew install mono-libgdiplus. Todos los ejemplos en este artículo estarán construidos utilizando macOS, pero deberían funcionar en todas las plataformas.

Si estás usando macOS, asegúrate de reiniciar tu sesión de PowerShell antes de continuar.

Instalación del módulo ImportExcel

Comienza descargando e instalando el módulo a través de PowerShell Gallery ejecutando Install-Module ImportExcel -Scope CurrentUser. Después de unos momentos, estarás listo para empezar.

Usando PowerShell y Excel para exportar a una hoja de cálculo

Es posible que estés familiarizado con los cmdlets estándar de PowerShell Export-Csv y Import-Csv. Estos cmdlets te permiten leer y exportar objetos de PowerShell a archivos CSV. Desafortunadamente, no hay cmdlets Export-Excel y Import-Excel. Pero utilizando el módulo ImportExcel, puedes construir tu propia funcionalidad.

Una de las solicitudes más comunes de un administrador de sistemas es exportar objetos de PowerShell a una hoja de cálculo de Excel. Utilizando el cmdlet Export-Excel en el módulo ImportExcel, puedes hacerlo fácilmente.

Por ejemplo, quizás necesites encontrar algunos procesos en ejecución en tu computadora local y llevarlos a un libro de Excel.

El cmdlet Export-Excel acepta cualquier objeto exactamente de la misma manera que lo hace Export-Csv. Puedes enviar cualquier tipo de objeto a este cmdlet.

Para encontrar procesos en ejecución en un sistema con PowerShell, utiliza el cmdlet Get-Process, que devuelve cada proceso en ejecución y varias informaciones sobre cada proceso. Para exportar esa información a Excel, utiliza el cmdlet Export-Excel proporcionando la ruta del archivo del libro de Excel que se creará. Puedes ver un ejemplo del comando y una captura de pantalla del archivo de Excel generado a continuación.

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

Worksheet created with PowerShell and Excel

¡Enhorabuena! Ahora has exportado toda la información, al igual que con Export-Csv, pero a diferencia de Export-Csv, podemos hacer que estos datos sean mucho más elegantes. Asegurémonos de que el nombre de la hoja de trabajo se llame Procesos, que los datos estén en una tabla y que las filas se ajusten automáticamente.

Al usar el parámetro de cambio AutoSize para ajustar automáticamente todas las filas, TableName para especificar el nombre de la tabla que incluirá todos los datos y el nombre del parámetro de WorksheetName de Procesos, puedes ver en la captura de pantalla a continuación lo que se puede construir.

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

El cmdlet Export-Excel tiene un montón de parámetros que puedes utilizar para crear libros de Excel de todo tipo. Para obtener un resumen completo de todo lo que Export-Excel puede hacer, ejecuta Get-Help Export-Excel.

Usando PowerShell para importar a Excel

Así que has exportado alguna información a un archivo llamado procesos.xlsx en la sección anterior. Tal vez ahora necesitas mover este archivo a otra computadora e importar/leer esta información con PowerShell y Excel. No hay problema. Tienes Import-Excel a tu disposición.

En su uso más básico, solo necesitas proporcionar la ruta al documento/libro de Excel utilizando el parámetro Path como se muestra a continuación. Verás que lee la primera hoja de trabajo, en este caso, la hoja de trabajo de Procesos, y devuelve objetos de PowerShell.

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

¿Quizás tienes varias hojas de trabajo en un libro de Excel? Puedes leer una hoja de trabajo en particular usando el parámetro WorksheetName.

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

¿Necesitas leer solo ciertas columnas de la hoja de cálculo de Excel? Utiliza el parámetro HeaderName para especificar solo esos parámetros que te gustaría leer.

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

El cmdlet Import-Excel tiene otros parámetros que puedes usar para leer libros de Excel de todo tipo. Para obtener información completa sobre todo lo que Import-Excel puede hacer, ejecuta Get-Help Import-Excel.

Usando PowerShell para Obtener (y Establecer) Valores de Celdas en Excel

Ahora sabes cómo leer una hoja de trabajo completa con PowerShell y Excel, pero ¿qué pasa si solo necesitas el valor de una celda? Técnicamente, podrías usar Import-Excel y filtrar el valor que necesitas con Where-Object, pero eso no sería muy eficiente.

En cambio, utilizando el cmdlet Open-ExcelPackage, puedes “convertir” un libro de Excel en un objeto de PowerShell que luego se puede leer y manipular. Para encontrar el valor de una celda, primero abre el libro de Excel para llevarlo a la memoria.

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

El cmdlet Open-ExcelPackage es similar a usar New-Object -comobject excel.application si trabajas directamente con objetos COM.

A continuación, elige la hoja de trabajo dentro del libro.

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

Este proceso es similar a la forma de abrir libros de trabajo con objetos COM utilizando excel.workbooks.open.

Una vez que tienes la hoja de trabajo asignada a una variable, ahora puedes profundizar en filas, columnas y celdas individuales. Tal vez necesites encontrar todos los valores de celdas en la fila A1. Simplemente debes hacer referencia a la propiedad Cells proporcionando un índice de A1, como se muestra a continuación.

$worksheet.Cells['A1'].Value

También puedes cambiar el valor de las celdas en una hoja de cálculo asignando un valor diferente, por ejemplo. $worksheet.Cells['A1'] = 'valorDiferente'

Una vez en la memoria, es importante liberar el paquete de Excel usando el cmdlet Close-ExcelPackage.

Close-ExcelPackage $excel

Convertir hojas de cálculo en archivos CSV con PowerShell y Excel

Una vez que tienes el contenido de una hoja de cálculo de Excel representado mediante objetos de PowerShell, “convertir” hojas de cálculo de Excel a CSV simplemente requiere enviar esos objetos al cmdlet Export-Csv.

Usando el libro de trabajo processes.xlsx creado anteriormente, lee la primera hoja de cálculo que obtiene todos los datos en objetos de PowerShell, y luego exporta esos objetos a CSV usando el comando a continuación.

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

Si ahora abres el archivo CSV resultante, verás los mismos datos dentro de la hoja de cálculo Procesos (en este ejemplo).

Convertir múltiples hojas de cálculo

Si tienes un libro de Excel con varias hojas de cálculo, también puedes crear un archivo CSV para cada hoja de cálculo. Para hacerlo, puedes encontrar todas las hojas en un libro de trabajo usando el cmdlet Get-ExcelSheetInfo. Una vez que tengas los nombres de las hojas de cálculo, puedes pasar esos nombres al parámetro WorksheetName y también usar el nombre de la hoja como nombre del archivo CSV.

A continuación, puedes ver el código de ejemplo necesario usando PowerShell y Excel.

## encontrar cada hoja en el libro de trabajo
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## leer cada hoja y crear un archivo CSV con el mismo nombre
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

Conclusión

¡Usando PowerShell y Excel, puedes importar, exportar y gestionar datos en libros de Excel exactamente como lo harías con archivos CSV sin necesidad de instalar Excel!

En este artículo, aprendiste lo básico sobre cómo leer y escribir datos en un libro de Excel, pero esto apenas rasca la superficie. ¡Usando PowerShell y el módulo ImportExcel, puedes crear gráficos, tablas dinámicas y aprovechar otras características poderosas de Excel!