Como mesclar várias planilhas Excel em uma só com PowerShell?

Unir dados de múltiplas planilhas manualmente é uma tarefa tediosa e propensa a erros. Com o PowerShell, você automatiza esse processo em segundos, garantindo precisão e consistência nos dados consolidados.

Pré-requisitos

• PowerShell 5.0 ou superior instalado
• Excel instalado no computador
• Permissão de leitura e escrita nos arquivos de origem e destino

Passo 1: Carregar o componente COM do Excel

Para manipular arquivos Excel, o PowerShell precisa acessar os objetos COM do Excel. Isso é feito adicionando uma referência ao componente.

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false

Passo 2: Definir a pasta de origem e o arquivo de destino

Configure o caminho onde estão as planilhas a serem mescladas e o local do arquivo final. Certifique-se de que todas as planilhas tenham a mesma estrutura de colunas.

$pastaOrigem = "C:\Pasta\Com\Planilhas"
$arquivoDestino = "C:\Pasta\Com\Planilhas\Consolidado.xlsx"

Passo 3: Criar um novo workbook de destino

Inicie um novo arquivo Excel que receberá os dados mesclados. Isso garante que o resultado final seja um único arquivo organizado.

$workbookDestino = $Excel.Workbooks.Add()
$planilhaDestino = $workbookDestino.Worksheets.Item(1)
$planilhaDestino.Name = "Dados Consolidados"

Passo 4: Percorrer e copiar dados de cada planilha

Para cada arquivo Excel na pasta, abra-o, copie os dados da primeira planilha e cole na planilha de destino. O script ignora a primeira linha (cabeçalho) após a primeira planilha para evitar duplicação.

$primeiraPlanilha = $true
Get-ChildItem -Path $pastaOrigem -Filter "*.xlsx" | ForEach-Object {
    $workbookOrigem = $Excel.Workbooks.Open($_.FullName)
    $planilhaOrigem = $workbookOrigem.Worksheets.Item(1)
    $rangeOrigem = $planilhaOrigem.UsedRange
    
    if ($primeiraPlanilha) {
        $rangeOrigem.Copy($planilhaDestino.Cells.Item(1, 1))
        $primeiraPlanilha = $false
    } else {
        $ultimaLinhaDestino = $planilhaDestino.Cells.Item($planilhaDestino.Rows.Count, 1).End(-4162).Row
        $rangeOrigem.Offset(1, 0).Copy($planilhaDestino.Cells.Item($ultimaLinhaDestino + 1, 1))
    }
    $workbookOrigem.Close($false)
}

Passo 5: Salvar e fechar o arquivo consolidado

Após copiar todos os dados, salve o novo arquivo Excel e libere os recursos do Excel.

$workbookDestino.SaveAs($arquivoDestino)
$workbookDestino.Close()
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

Exemplo Prático

Considere três planilhas com a mesma estrutura: ID, Nome e Valor.

Após executar o script, o arquivo consolidado conterá:

Conclusão

Com este método, você automatiza a tarefa de mesclar várias planilhas Excel em uma só com PowerShell, evitando erros manuais e ganhando tempo valioso no processamento de dados.