Trabalhar com arquivos Excel é uma tarefa comum em empresas que fornecem software que gerenciam dados e valores de negócio. Mas nem sempre é fácil.
Há algum tempo publiquei um artigo sobre como adicionar segurança em arquivos do Excel com o POI. Também estou trabalhando num pequeno framework para gerar arquivos Excel com base em templates baseado no MVEL e no POI.
Deu pra notar que estou mexendo bastante com isso, né? Vamos a algumas dicas para quem está começando com o Apache POI.
User models – o que são e como usar
A primeira dica é algo que você deve saber. A biblioteca POI possui três diferentes APIs para trabalhar com arquivos Excel:
- HSSF: classes para trabalhar com o formado binário mais antigo do Excel (XLS).
- XSSF: classes para trabalhar com o formato mais novo do Excel baseado em XML (XLSX).
- SS: supermodelo que engloba tanto HSSF quanto XSSF.
Cada uma dessas APIs representa um user model, isto é, um modelo de dados para que o código “cliente” (o seu código) possa manipular um tipo específico de arquivo.
Ao trabalhar com classes do pacote hssf, você somente poderá ler e criar arquivos XLS. O pacote xssf somente lê e cria arquivos XLSX.
Enfim, use sempre as classes do pacote ss (supermodel) pois elas servem para os dois tipos de arquivos do Office. Caso contrário, você terá que fazer implementações distintas para trabalhar com arquivos binários e XML.
Desmistificando o método shiftRows
Uma das funcionalidades que implementei no desenvolvimento do framework foi a replicação automática de linhas. Também implementei um operador condicional para incluir ou não linhas no arquivo de acordo com variáveis. Para tanto, foi necessário implementar código para incluir e remover intervalores de linhas do arquivo.
Estudando a API, encontrei a função shiftRows da classe Sheet. Este método é frequentemente mal usado pelos programadores que não entendem bem como ele funciona.
Ok, a ideia do termo shift seria para deslocar as linhas. Mas o método possui os três parâmetros:
void shiftRows(int startRow, int endRow, int n)
Em geral, o entendimento inicial que as pessoas tem é que esse comando seria similar à funcionalidade do Excel de replicar linhas. Por exemplo, você seleciona as linhas de 1 a 3 e replica n vezes. Considere o comando:
sheet.shiftRows(0, 2, 5);
Será que esse comando copia as três primeiras linhas da planilha cinco vezes? A resposta é não.
Na verdade, o método shiftRows apenas desloca as linhas, alterando o número das linhas selecionadas. O comando acima simplesmente incrementa em 5 o número da linha das três primeiras linhas da planilha.
Após a chamada ao método, a linha 0 (primeira linha) irá para a posição 5 (sexta linha), a linha 1 irá para a posição 6 (sétima linha) e a linha 2 irá para a posição 7 (oitava linha).
Confuso? Pense da seguinte forma:
Pegue as linhas de
0a2e mova cinco posições para “baixo”.
O mesmo raciocínio poderia ser aplicado caso o terceiro parâmetro fosse negativo. No caso, isso significa mover as linhas para “cima”. Enfim, este parâmetro diz em quanto as linhas selecionadas devem ser deslocadas, para “baixo” ou para “cima”.
Problemas com o shiftRows
É preciso tomar cuidado ao usar o método para não acabar com uma planilha corrompida. Isso já ocorreu várias vezes com várias pessoas que conheço.
O problema é que, ao deslocar as linhas com shiftRows, elas podem sobrepor outras linhas que já existem naquela posição. E isso pode causar vários comportamentos estranhos, desde a perda de alguns dados até uma planilha totalmente corrompida.
Imagine, no exemplo anterior, se já existissem as linhas 5, 6 ou 7.
Aparentemente, o POI não tem um tratamento para isso. Sinceramente, não verifiquei se isso foi corrigido em versões recentes, mas não é raro lidarmos com versões anteriores em projetos já existentes.
Portanto, sempre que for incluir linhas, mova todas as linhas até o final da planilha.
Exemplo:
sheet.shiftRows(
posicaoParaInserir,
sheet.getLastRowNum(),
quantidadeLinhasNovas);
Para remover linhas, primeiramente é preciso remover manualmente os objetos do tipo Row existentes no intervalo, depois deslocar todas as linhas de baixo para cima.
Veja como ficou uma implementação minha para remover um intervalo de linhas:
private void removeRows(Sheet sheet, int position, int count) {
for (int i = 0; i < count; i++) {
Row row = sheet.getRow(position + i);
if (row != null) {
sheet.removeRow(row);
}
}
sheet.shiftRows(position + count, sheet.getLastRowNum(), -count);
}
Explicando novamente, o código acima faz o seguinte:
-
Remove as linhas existentes no intervalo começando em
positionaté a quantidadecount. Note que é necessário verificar se o objetoRowexiste para cada linha, porque a implementação é de uma lista esparsa, isto é, as linhas que não tem informação não existem na memória. -
Desloca as linhas que estão acima do intervalo para cima, aplicando um valor negativo ao tamanho do intervalo. Em outras palavras, se eu quiser excluir as linhas 6 a 10, todas as linhas a partir da linha 11 terão o seu número subtraído de 5.
Clonar células
No Excel é simples duplicar o conteúdo com formatação. No POI esta tarefa demanda um certo trabalho. Não há um comando pronto, sendo necessário copiar o conteúdo, o estilo e alguns outros atributos de cada célula.
Fiz uma implementação baseada neste artigo do Stack Overflow:
private void cloneRow(Row sourceRow, Row newRow) {
// Loop through source columns to add to new row
for (Cell oldCell : sourceRow) {
// Grab a copy of the old/new cell
Cell newCell = newRow.createCell(oldCell.getColumnIndex());
// Copy style from old cell and apply to new cell
CellStyle newCellStyle = workbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
newCell.setCellStyle(newCellStyle);
// If there is a cell comment, copy
if (oldCell.getCellComment() != null) {
newCell.setCellComment(oldCell.getCellComment());
}
// If there is a cell hyperlink, copy
if (oldCell.getHyperlink() != null) {
newCell.setHyperlink(oldCell.getHyperlink());
}
// Set the cell data type
newCell.setCellType(oldCell.getCellType());
// Set the cell data value
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
}
}
// If there are are any merged regions in the source row, copy to new row
Sheet sheet = sourceRow.getSheet();
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
CellRangeAddress newCellRangeAddress = new CellRangeAddress(
newRow.getRowNum(),
(newRow.getRowNum() +
(cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
cellRangeAddress.getFirstColumn(),
cellRangeAddress.getLastColumn());
sheet.addMergedRegion(newCellRangeAddress);
}
}
}
Considerações finais
A biblioteca POI é bem flexível e permite manipular arquivos de planilha de forma relativamente simples e eficiente.
Porém, determinadas funcionalidades podem representar um desafio até para o entendimento dela. Espero que este artigo, dentro dos cenários propostos, lhe possa ser útil. 😀
