PowerShell e Excel: Aproveite o Poder do ImportExcel – techsyncer

O Microsoft Excel é uma daquelas ferramentas onipresentes da qual a maioria de nós não pode escapar, mesmo que tente. Muitos profissionais de TI usam o Excel como um pequeno banco de dados, armazenando toneladas de dados em várias rotinas de automação. Qual é o melhor cenário para automação com o Excel? PowerShell e Excel!

Planilhas do Excel sempre foram notoriamente difíceis de criar scripts e automatizar. Ao contrário de seu equivalente de arquivo CSV com menos recursos (e mais simples), os livros do Excel não são apenas arquivos de texto simples. Livros do Excel exigiam o PowerShell para manipular objetos complicados do Modelo de Objeto de Componente (COM), sendo necessário ter o Excel instalado. Não mais.

Felizmente, um perspicaz membro da comunidade do PowerShell, Doug Finke, criou um módulo PowerShell chamado ImportExcel para nós simples mortais. O módulo ImportExcel abstrai toda essa complexidade. Torna possível gerenciar facilmente livros do Excel e usar o PowerShell para scripts!

Neste artigo, vamos explorar o que você pode fazer com PowerShell e Excel usando o módulo ImportExcel e alguns casos de uso populares.

Pré-requisitos

Ao executar o módulo ImportExcel em um sistema Windows, não são necessárias dependências separadas. No entanto, se você estiver trabalhando no macOS, será necessário instalar o pacote mono-libgdiplus usando brew install mono-libgdiplus. Todos os exemplos neste artigo serão construídos usando o macOS, mas todos os exemplos devem funcionar em várias plataformas.

Se estiver usando macOS, certifique-se de reiniciar sua sessão do PowerShell antes de continuar.

Instalando o Módulo ImportExcel

Comece baixando e instalando o módulo via PowerShell Gallery, executando Install-Module ImportExcel -Scope CurrentUser. Após alguns momentos, estará pronto para usar.

Usando PowerShell e Excel para Exportar para uma Planilha

Você pode estar familiarizado com os cmdlets padrão do PowerShell Export-Csv e Import-Csv. Esses cmdlets permitem que você leia e exporte objetos do PowerShell para arquivos CSV. Infelizmente, não há Export-Excel e Import-Excel cmdlets. Mas usando o módulo ImportExcel, você pode criar sua própria funcionalidade.

Uma das solicitações mais comuns de um sysadmin é exportar objetos do PowerShell para uma planilha do Excel. Usando o cmdlet Export-Excel no módulo ImportExcel, você pode facilmente fazer isso acontecer.

Por exemplo, talvez você precise encontrar alguns processos em execução no seu computador local e inseri-los em uma pasta de trabalho do Excel.

O cmdlet Export-Excel aceita qualquer objeto exatamente da mesma forma que o Export-Csv faz. Você pode encaminhar qualquer tipo de objeto para este cmdlet.

Para encontrar processos em execução em um sistema com o PowerShell, use o cmdlet Get-Process, que retorna cada processo em execução e várias informações sobre cada processo. Para exportar essas informações para o Excel, use o cmdlet Export-Excel fornecendo o caminho do arquivo para a pasta de trabalho do Excel que será criada. Você pode ver um exemplo do comando e uma captura de tela do arquivo do Excel gerado abaixo.

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

Worksheet created with PowerShell and Excel

Parabéns! Você agora exportou todas as informações assim como o Export-Csv, mas, ao contrário do Export-Csv, podemos tornar esses dados muito mais elegantes. Vamos garantir que o nome da planilha seja Processos, que os dados estejam em uma tabela e que as linhas tenham tamanho automático.

Ao usar o parâmetro de comutação AutoSize para ajustar automaticamente todas as linhas, TableName para especificar o nome da tabela que incluirá todos os dados e o nome do parâmetro WorksheetName para Processos, você pode ver na captura de tela abaixo o que pode ser construído.

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

O cmdlet Export-Excel possui uma tonelada de parâmetros que você pode usar para criar pastas de trabalho do Excel de todos os tipos. Para obter uma lista completa de tudo que o Export-Excel pode fazer, execute Get-Help Export-Excel.

Usando PowerShell para Importar para o Excel

Então você exportou algumas informações para um arquivo chamado processes.xlsx na seção anterior. Talvez agora você precise mover este arquivo para outro computador e importar/ler estas informações com PowerShell e Excel. Sem problemas. Você tem o Import-Excel à sua disposição.

Em seu uso mais básico, você só precisa fornecer o caminho para o documento/pasta de trabalho do Excel usando o parâmetro Path como mostrado abaixo. Você verá que ele lê a primeira planilha, neste caso, a planilha Processos, e retorna objetos do PowerShell.

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

Talvez você tenha várias planilhas em uma pasta de trabalho do Excel? Você pode ler uma planilha específica usando o parâmetro WorksheetName.

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

Você precisa apenas ler determinadas colunas da planilha do Excel? Use o parâmetro HeaderName para especificar apenas os parâmetros que você gostaria de ler.

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

O cmdlet Import-Excel possui outros parâmetros que você pode usar para ler pastas de trabalho do Excel de todos os tipos. Para obter uma visão completa de tudo que Import-Excel pode fazer, execute Get-Help Import-Excel.

Usando o PowerShell para Obter (e Definir) Valores de Células no Excel

Agora você sabe como ler uma planilha inteira com o PowerShell e o Excel, mas e se você precisar apenas de um valor de célula? Tecnicamente, você poderia usar Import-Excel e filtrar o valor necessário com Where-Object, mas isso não seria muito eficiente.

Em vez disso, usando o cmdlet Open-ExcelPackage, você pode “converter” uma pasta de trabalho do Excel em um objeto PowerShell que pode ser lido e manipulado. Para encontrar um valor de célula, primeiro, abra a pasta de trabalho do Excel para trazê-la para a memória.

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

O cmdlet Open-ExcelPackage é semelhante ao uso de New-Object -comobject excel.application ao trabalhar diretamente com objetos COM.

Em seguida, escolha a planilha dentro da pasta de trabalho.

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

Esse processo é semelhante à maneira de objetos COM de abrir pastas de trabalho com excel.workbooks.open.

Depois de atribuir a planilha a uma variável, você pode agora aprofundar-se em linhas, colunas e células individuais. Talvez você precise encontrar todos os valores de célula na linha A1. Basta fazer referência à propriedade Cells, fornecendo um índice de A1, como mostrado abaixo.

$worksheet.Cells['A1'].Value

Você também pode alterar o valor das células em uma planilha atribuindo um valor diferente, por exemplo, $worksheet.Cells['A1'] = 'valorDiferente'

Uma vez em memória, é importante liberar o pacote do Excel usando o cmdlet Close-ExcelPackage.

Close-ExcelPackage $excel

Convertendo Planilhas para Arquivos CSV com PowerShell e Excel

Depois de ter o conteúdo de uma planilha do Excel representado por meio de objetos do PowerShell, “converter” planilhas do Excel para CSV simplesmente requer enviar esses objetos para o cmdlet Export-Csv.

Usando a pasta de trabalho processes.xlsx criada anteriormente, leia a primeira planilha que obtém todos os dados em objetos do PowerShell e, em seguida, exporte esses objetos para CSV usando o comando abaixo.

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

Se você abrir o arquivo CSV resultante, verá os mesmos dados dentro da planilha Processes (neste exemplo).

Convertendo Múltiplas Planilhas

Se você tiver uma pasta de trabalho do Excel com várias planilhas, também pode criar um arquivo CSV para cada planilha. Para fazer isso, você pode encontrar todas as planilhas em uma pasta de trabalho usando o cmdlet Get-ExcelSheetInfo. Depois de obter os nomes das planilhas, você pode passar esses nomes para o parâmetro WorksheetName e também usar o nome da planilha como o nome do arquivo CSV.

Abaixo, você encontra o código de exemplo necessário usando PowerShell e Excel.

## encontrar cada planilha no livro de trabalho
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## ler cada planilha e criar um arquivo CSV com o mesmo nome
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

Conclusão

Usando PowerShell e Excel, você pode importar, exportar e gerenciar dados em pastas de trabalho do Excel exatamente como faria com CSVs sem precisar instalar o Excel!

Neste artigo, você aprendeu o básico de como ler e escrever dados em uma pasta de trabalho do Excel, mas isso apenas arranha a superfície. Usando PowerShell e o módulo ImportExcel, você pode criar gráficos, tabelas dinâmicas e aproveitar outros recursos poderosos do Excel!