Este artigo demonstra como gerar relatórios Excel dinâmicos a partir de um modelo pré-definido, onde diferentes planilhas recebem dados variados e listas suspensas são populadas com base em informações de outras planilhas. O objetivo é preencher um arquivo Excel com dados específicos para cada seção, permitindo a criação de formulários de importação com validação de dados.

Implementação em Java com Apache POI

A solução utiliza a biblioteca Apache POI para manipulação de arquivos .xlsx. O código Java a seguir encapsula a lógica de processamento do modelo Excel.

Classe Principal: ExcelReportGenerator

@Slf4j
@Component
public class ExcelReportGenerator {

   private static final String TEMPLATE_FILE_PATH = "templates/ImportacaoFreteMaritimo.xlsx";

   /**
    * Processa o modelo Excel, preenche dados e retorna um InputStream.
    *
    * @param productCodes Lista de códigos de produtos.
    * @param warehouseLocations Lista de locais de armazém.
    * @return InputStream contendo o relatório Excel processado.
    * @throws IOException Se ocorrer um erro ao ler ou escrever o arquivo.
    */
   public InputStream generateReport(List<String> productCodes, List<String> warehouseLocations) throws IOException {
       // Carrega o modelo a partir dos recursos
       ClassPathResource resource = new ClassPathResource(TEMPLATE_FILE_PATH);
       XSSFWorkbook workbook = new XSSFWorkbook(resource.getInputStream());

       try {
           // Processa a planilha de códigos de produto
           processProductCodesSheet(workbook, productCodes);
           // Processa a planilha de locais de armazém
           processWarehouseLocationsSheet(workbook, warehouseLocations);
           // Configura a lista suspensa na planilha de pedido
           configureOrderSheetDropdown(workbook, productCodes.size());

           // Converte o workbook processado para um ByteArrayOutputStream
           ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
           workbook.write(outputStream);
           return new ByteArrayInputStream(outputStream.toByteArray());
       } finally {
           // Garante que o workbook seja fechado
           workbook.close();
       }
   }

   /**
    * Preenche a planilha 'Lista de Produtos' com os códigos fornecidos.
    *
    * @param workbook O objeto XSSFWorkbook.
    * @param products Lista de códigos de produtos.
    */
   private void processProductCodesSheet(XSSFWorkbook workbook, List<String> products) {
       XSSFSheet sheet = workbook.getSheet("Lista de Produtos");
       if (sheet == null) {
           throw new RuntimeException("Modelo Excel não contém a planilha 'Lista de Produtos'.");
       }

       // Limpa o conteúdo existente antes de adicionar novos dados
       clearSheetContents(sheet);

       // Insere os códigos de produto na primeira coluna (coluna A)
       for (int i = 0; i < products.size(); i++) {
           XSSFRow row = sheet.createRow(i);
           XSSFCell cell = row.createCell(0); // Coluna A
           cell.setCellValue(products.get(i));
       }
   }

   /**
    * Preenche a planilha 'Armazéns' com os locais de armazém fornecidos.
    *
    * @param workbook O objeto XSSFWorkbook.
    * @param locations Lista de locais de armazém.
    */
   private void processWarehouseLocationsSheet(XSSFWorkbook workbook, List<String> locations) {
       XSSFSheet sheet = workbook.getSheet("Armazéns");
       if (sheet == null) {
           throw new RuntimeException("Modelo Excel não contém a planilha 'Armazéns'.");
       }

       // Limpa o conteúdo existente
       clearSheetContents(sheet);

       // Insere os locais de armazém na primeira coluna (coluna A)
       for (int i = 0; i < locations.size(); i++) {
           XSSFRow row = sheet.createRow(i);
           XSSFCell cell = row.createCell(0); // Coluna A
           cell.setCellValue(locations.get(i));
       }
   }

   /**
    * Configura a lista suspensa na planilha 'Registro de Pedido' para seleção de produto.
    *
    * @param workbook O objeto XSSFWorkbook.
    * @param productCount Número total de produtos disponíveis.
    */
   private void configureOrderSheetDropdown(XSSFWorkbook workbook, int productCount) {
       XSSFSheet sheet = workbook.getSheet("Registro de Pedido");
       if (sheet == null) {
           throw new RuntimeException("Modelo Excel não contém a planilha 'Registro de Pedido'.");
       }

       // Cria um helper para validação de dados
       XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

       // Define a origem da lista suspensa (referência à coluna A da planilha 'Lista de Produtos')
       String validationSource = String.format("Lista de Produtos!$A$1:$A$%d", productCount);
       XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
               dvHelper.createFormulaListConstraint(validationSource);

       // Define o intervalo de células onde a lista suspensa será aplicada (Célula H4)
       CellRangeAddressList addressRange = new CellRangeAddressList(3, 3, 7, 7); // Linha 4, Coluna H

       // Cria e adiciona a validação de dados ao sheet
       XSSFDataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressRange);
       dataValidation.setSuppressDropDownArrow(false); // Exibe a seta da lista suspensa

       // Remove validações existentes para evitar conflitos
       sheet.getDataValidations().clear();
       sheet.addValidationData(dataValidation);

       // Define um valor padrão na célula, se houver produtos
       if (productCount > 0) {
           XSSFRow row = sheet.getRow(3); // Linha 4
           if (row == null) {
               row = sheet.createRow(3);
           }
           XSSFCell cell = row.getCell(7); // Coluna H
           if (cell == null) {
               cell = row.createCell(7);
           }
           // Opcionalmente, pode-se definir um valor padrão aqui.
           // cell.setCellValue(products.get(0)); // Exemplo: definir o primeiro produto como padrão
       }
   }

   /**
    * Remove todas as linhas de uma planilha.
    *
    * @param sheet A planilha a ser limpa.
    */
   private void clearSheetContents(XSSFSheet sheet) {
       int lastRowIndex = sheet.getLastRowNum();
       for (int i = 0; i <= lastRowIndex; i++) {
           XSSFRow row = sheet.getRow(i);
           if (row != null) {
               sheet.removeRow(row);
           }
       }
   }
}
   

Dependência do Apache POI

Para utilizar este código, adicione a seguinte dependência ao seu arquivo pom.xml:

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>5.2.3</version><!-- Use uma versão recente -->
</dependency>
   

Funcionalidade Detalhada

• Processamento de Múltiplas Planilhas: O sistema é capaz de manipular um arquivo Excel com diversas planilhas.
• Dados Dinâmicos: Informações como códigos de produto e locais de armazém são inseridas dinamicamente nas planilhas "Lista de Produtos" e "Armazéns", respectivamente.
• Listas Suspensas (Dropdowns): A planilha "Registro de Pedido" é configuraad para apresentar uma lista suspensa na célula H4. Os itens desta lista são derivados dos dados presentes na coluna A da planilha "Lista de Produtos".
• Geração de Arquivo: Ao final do processamento, um InputStream contendo o arquivo Excel preenchido é retornado, pronto para ser baixado ou salvo.
• Limpeza de Conteúdo: Antes de inserir novos dados, as planilhas de origem de dados são limpas para garantir que não haja informações residuais do modelo.

Por exemplo, a coluna "Localização do Armazém" na planilha de registro de pedidos pode ser preenchida com os dados da coluna A da planilha "Armazéns", e a coluna "Canal" com os dados da planilha "Lista de Produtos", através da configuração de validação de dados.