O Microsoft Excel é uma daquelas ferramentas onipresentes da qual a maioria de nós não consegue escapar, mesmo se tentarmos. Muitos profissionais de TI utilizam 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 serem scriptadas e automatizadas. Ao contrário de seu equivalente em arquivo CSV, menos robusto (e mais simples), os livros do Excel não são apenas arquivos de texto simples. Os livros do Excel exigiam o PowerShell para manipular objetos complicados do Component Object Model (COM), sendo necessário ter o Excel instalado. Mas isso não é mais necessário.
Felizmente, um perspicaz membro da comunidade PowerShell, Doug Finke, criou um módulo PowerShell chamado ImportExcel para nós simples mortais. O módulo ImportExcel abstrai toda essa complexidade, tornando possível gerenciar facilmente os livros do Excel e utilizar o PowerShell para scripts!
Neste artigo, vamos explorar o que você pode fazer com o PowerShell e o Excel usando o módulo ImportExcel, além de alguns casos de uso populares.
Pré-requisitos
Quando executar o módulo ImportExcel em um sistema Windows, não são necessárias dependências separadas. No entanto, se estiver a trabalhar no macOS, será necessário instalar o pacote mono-libgdiplus usando brew install mono-libgdiplus. Todos os exemplos neste artigo serão desenvolvidos usando o macOS, mas todos os exemplos devem funcionar em qualquer plataforma.
Se estiver a usar o macOS, certifique-se de reiniciar a sessão do PowerShell antes de continuar.
Instalando o Módulo ImportExcel
Comece baixando e instalando o módulo através do PowerShell Gallery, executando Install-Module ImportExcel -Scope CurrentUser. Em poucos momentos, estará pronto para começar.
Usando o PowerShell e o 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 ler e exportar objetos do PowerShell para arquivos CSV. Infelizmente, não existe Export-Excel e Import-Excel. No entanto, 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 tornar isso possível.
Por exemplo, talvez você precise encontrar alguns processos em execução no seu computador local e colocá-los em um livro do Excel.
O cmdlet
Export-Excelaceita qualquer objeto exatamente da mesma maneira que oExport-Csvfaz. Você pode enviar qualquer tipo de objeto para este cmdlet.
Para encontrar processos em execução em um sistema com 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 o livro do Excel que será criado. Você pode ver um exemplo do comando e uma captura de tela do arquivo do Excel gerado abaixo.
Parabéns! Você agora exportou todas as informações exatamente como o Export-Csv, mas, ao contrário do Export-Csv, podemos tornar esses dados muito mais sofisticados. Vamos garantir que o nome da planilha seja chamado de Processos, os dados estejam em uma tabela e as linhas sejam dimensionadas automaticamente.
Usando o parâmetro de comutação AutoSize para redimensionar automaticamente todas as linhas, TableName para especificar o nome da tabela que incluirá todos os dados e o parâmetro WorksheetName de Processes, você pode ver na captura de tela abaixo o que pode ser construído.
O cmdlet
Export-Exceltem uma tonelada de parâmetros que você pode usar para criar pastas de trabalho do Excel de todos os tipos. Para obter uma explicação completa de tudo que oExport-Excelpode fazer, executeGet-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 Processes, e retorna objetos do PowerShell.
Talvez você tenha várias planilhas em um pasta de trabalho do Excel? Você pode ler uma planilha específica usando o parâmetro WorksheetName.
Você precisa apenas ler certas colunas da planilha do Excel? Use o parâmetro HeaderName para especificar apenas os parâmetros que você gostaria de ler.
O cmdlet
Import-Exceltem outros parâmetros que você pode usar para ler pastas de trabalho do Excel de todos os tipos. Para obter uma lista completa de tudo o que oImport-Excelpode fazer, executeGet-Help Import-Excel.
Usando o PowerShell para Obter (e Definir) Valores de Células do Excel
Agora você sabe como ler uma planilha inteira com o PowerShell e o Excel, mas e se você precisar apenas de um único valor de célula? Tecnicamente, você poderia usar o Import-Excel e filtrar o valor que você precisa com o 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.
O cmdlet
Open-ExcelPackageé semelhante a usarNew-Object -comobject excel.applicationse estiver trabalhando diretamente com objetos COM.
Em seguida, escolha a planilha dentro da pasta de trabalho.
Esse processo é semelhante à forma de objetos COM de abrir pastas de trabalho com
excel.workbooks.open.
Depois de ter a planilha atribuída a uma variável, agora você pode acessar linhas, colunas e células individuais. Talvez você precise encontrar todos os valores de célula na linha A1. Você só precisa fazer referência à propriedade Cells fornecendo um índice de A1, como mostrado abaixo.
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 na memória, é importante liberar o pacote do Excel usando o cmdlet Close-ExcelPackage.
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 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 PowerShell e, em seguida, exporte esses objetos para CSV usando o comando abaixo.
Se você abrir agora 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 ter 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.
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 da leitura e escrita de 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!