package br.com.ec.controller.util;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import com.itextpdf.text.Document;
import com.itextpdf.text.pdf.PdfCopy;
import com.itextpdf.text.pdf.PdfImportedPage;
import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.parser.PdfReaderContentParser;
import com.itextpdf.text.pdf.parser.SimpleTextExtractionStrategy;
import com.itextpdf.text.pdf.parser.TextExtractionStrategy;
import br.com.ec.core.util.StringUtil;
import br.com.ec.core.util.VerificadorUtil;
import br.com.ec.domain.model.Avaliacao;
import br.com.ec.domain.model.AvaliacaoFuncionario;
import br.com.ec.domain.model.Funcionario;
import br.com.ec.domain.model.Pessoa;
import br.com.ec.domain.model.Vigencia;
public class ExcelUtil
{
public static HSSFSheet criarAbas
(HSSFWorkbook workbook,
String nomeAba
) {
HSSFSheet aba = workbook.
createSheet(nomeAba
);
return aba
;
}
public static void definirPadraoAba
(HSSFSheet aba,
Integer larguraColunas,
short alturaLinhas
) {
aba.
setDefaultColumnWidth(larguraColunas
);//15
aba.
setDefaultRowHeight((short)alturaLinhas
);//400
}
public static CellStyle configurarCelulaCabecalho
(HSSFWorkbook workbook
) {
CellStyle headerStyle = workbook.
createCellStyle();
headerStyle.
setFillForegroundColor(IndexedColors.
GREY_25_PERCENT.
getIndex());
headerStyle.
setFillPattern(FillPatternType.
SOLID_FOREGROUND);
headerStyle.
setAlignment(HorizontalAlignment.
CENTER);
headerStyle.
setVerticalAlignment(VerticalAlignment.
CENTER);
return headerStyle
;
}
public static CellStyle configurarCelulaTexto
(HSSFWorkbook workbook
) {
CellStyle textStyle = workbook.
createCellStyle();
textStyle.
setAlignment(HorizontalAlignment.
CENTER);
textStyle.
setVerticalAlignment(VerticalAlignment.
CENTER);
return textStyle
;
}
public static CellStyle configurarCelulaNumerico
(HSSFWorkbook workbook
) {
CellStyle numberStyle = workbook.
createCellStyle();
//Configurando estilos de células (Cores, alinhamento, formatação, etc..)
HSSFDataFormat numberFormat = workbook.
createDataFormat();
numberStyle.
setDataFormat(numberFormat.
getFormat("#,##0.00"));
numberStyle.
setVerticalAlignment(VerticalAlignment.
CENTER);
return numberStyle
;
}
public static void configurarCabecalho
(HSSFWorkbook workbook, HSSFSheet aba
) {
CellStyle headerStyle = configurarCelulaCabecalho
(workbook
);
Row linha = aba.
createRow(0);
criarCelula
(workbook, aba, linha,
0, headerStyle,
"CÓDIGO");
// criarCelula(workbook, aba, linha, 1, headerStyle, "NOME");
// criarCelula(workbook, aba, linha, 2, headerStyle, "PREÇO");
}
public static void criarCelula
(HSSFWorkbook workbook, HSSFSheet aba, Row linha,
Integer posicaoColuna, CellStyle estilo,
String valor
) {
Cell celula = linha.
createCell(posicaoColuna
);
if (VerificadorUtil.
naoEstaNulo(estilo
)) {
celula.
setCellStyle(estilo
);
}
celula.
setCellValue(valor
);
}
public static void main
(String[] args
) throws IOException {
HSSFWorkbook wb =
new HSSFWorkbook
();
// ABAS
HSSFSheet aba1 = criarAbas
(wb,
"lista funcionários para pagamen");
Row linha = aba1.
createRow(0);
criarCelula
(wb, aba1, linha,
0, configurarCelulaTexto
(wb
),
"1465");
criarCelula
(wb, aba1, linha,
1, configurarCelulaTexto
(wb
),
"84044");
criarCelula
(wb, aba1, linha,
2, configurarCelulaTexto
(wb
),
"3");
criarCelula
(wb, aba1, linha,
3, configurarCelulaNumerico
(wb
),
"Adalberto Soares Brito Neto");
criarCelula
(wb, aba1, linha,
4, configurarCelulaTexto
(wb
),
"074.713.574-60");
criarCelula
(wb, aba1, linha,
5, configurarCelulaTexto
(wb
),
"1");
criarCelula
(wb, aba1, linha,
6, configurarCelulaTexto
(wb
),
"2306,98");
try {
FileOutputStream stream =
new FileOutputStream("d:/planilhaaa.xls");
wb.
write(stream
);
stream.
close();
wb.
close();
System.
out.
println("Success!!");
} catch (FileNotFoundException e
) {
e.
printStackTrace();
} catch (IOException e
) {
e.
printStackTrace();
}
}
public static void main33
(String[] args
) throws IOException {
HSSFWorkbook wb =
new HSSFWorkbook
();
// ABAS
HSSFSheet aba1 = criarAbas
(wb,
"Planilha 1");
criarAbas
(wb,
"Planilha 2");
criarAbas
(wb,
"Planilha 3");
configurarCabecalho
(wb, aba1
);
Row linha = aba1.
createRow(1);
criarCelula
(wb, aba1, linha,
0, configurarCelulaTexto
(wb
),
"1");
criarCelula
(wb, aba1, linha,
1, configurarCelulaTexto
(wb
),
"TESTE");
criarCelula
(wb, aba1, linha,
2, configurarCelulaNumerico
(wb
),
"10");
try {
FileOutputStream stream =
new FileOutputStream("d:/planilhaaa.xls");
wb.
write(stream
);
stream.
close();
wb.
close();
System.
out.
println("Success!!");
} catch (FileNotFoundException e
) {
e.
printStackTrace();
} catch (IOException e
) {
e.
printStackTrace();
}
}
public static void main3
(String[] args
) throws IOException {
String filePath =
"d:/planilha.xls";
try{
// Abrindo o arquivo e recuperando a planilha
FileInputStream file =
new FileInputStream(new File(filePath
));
HSSFWorkbook workbook =
new HSSFWorkbook
(file
);
HSSFSheet sheet = workbook.
getSheetAt(0);
List products =
new ArrayList();
Iterator rowIterator = sheet.
rowIterator();
while (rowIterator.
hasNext()) {
Row row =
(Row
) rowIterator.
next();
// Descantando a primeira linha com o header
if(row.
getRowNum() ==
0){
continue;
}
Iterator cellIterator = row.
cellIterator();
// Product product = new Product();
// products.add(product);
while (cellIterator.
hasNext()) {
Cell cell =
(Cell
) cellIterator.
next();
switch (cell.
getColumnIndex()) {
case 0:
// product.setId(((Double)cell.getNumericCellValue()).longValue());
cell.
setCellValue("2");// Substituindo valores
System.
out.
println(cell.
getStringCellValue());
break;
case 1:
System.
out.
println(cell.
getStringCellValue());
break;
case 2:
System.
out.
println(cell.
getNumericCellValue());
break;
}
}
}
// Reescrever
try {
FileOutputStream stream =
new FileOutputStream("d:/planilha.xls");
workbook.
write(stream
);
stream.
close();
workbook.
close();
System.
out.
println("Success!!");
} catch (FileNotFoundException e
) {
e.
printStackTrace();
} catch (IOException e
) {
e.
printStackTrace();
}
file.
close();
workbook.
close();
} catch (FileNotFoundException e
) {
e.
printStackTrace();
}
}
public static void main4
(String[] args
) throws IOException {
Avaliacao avaliacao =
new Avaliacao
();
Vigencia vigencia =
new Vigencia
();
vigencia.
setDescricao("11/2020");
avaliacao.
setVigencia(vigencia
);
List<AvaliacaoFuncionario
> participantes =
new ArrayList<AvaliacaoFuncionario
>();
AvaliacaoFuncionario avaliacaoFuncionario =
new AvaliacaoFuncionario
();
Funcionario funcionario =
new Funcionario
();
Pessoa pessoa =
new Pessoa
();
pessoa.
setNome("ADALBERTO SOARES BRITO NETO");
funcionario.
setPessoa(pessoa
);
funcionario.
setCodigoContabilidade(48);
avaliacaoFuncionario.
setFuncionario(funcionario
);
avaliacaoFuncionario.
setValorComissao(new Double(50.0));
participantes.
add(avaliacaoFuncionario
);
avaliacao.
setParticipantes(new HashSet<AvaliacaoFuncionario
>());
avaliacao.
getParticipantes().
add(avaliacaoFuncionario
);
String filePath =
"d:/importacao.xls";
try {
FileInputStream file =
new FileInputStream(new File(filePath
));
HSSFWorkbook workbook =
new HSSFWorkbook
(file
);
HSSFSheet sheet = workbook.
getSheetAt(0);
Iterator rowIterator = sheet.
rowIterator();
while (rowIterator.
hasNext()) {
Row row =
(Row
) rowIterator.
next();
Iterator cellIterator = row.
cellIterator();
if (row.
getRowNum() < 11) {
if (row.
getRowNum() ==
4) {
while (cellIterator.
hasNext()) {
Cell cell =
(Cell
) cellIterator.
next();
switch (cell.
getColumnIndex()) {
case 2:
cell.
setCellValue(vigencia.
getDescricao());
System.
out.
println(cell.
getRichStringCellValue()); break;
}
}
}
continue;
}
Integer codigoFolha =
null;
while (cellIterator.
hasNext()) {
Boolean atualizar =
true;
Cell cell =
(Cell
) cellIterator.
next();
switch (cell.
getColumnIndex()) {
case 1:
Integer tipoCelula = cell.
getCellType();
if (tipoCelula.
equals(Cell.
CELL_TYPE_NUMERIC)) {
System.
out.
println(cell.
getNumericCellValue());
Double codigo = cell.
getNumericCellValue();
codigoFolha = codigo.
intValue();
} else {
atualizar =
false;
}
break;
case 2:
if (atualizar
) {
System.
out.
println(cell.
getStringCellValue()); break;
}
case 3:
if (atualizar
) {
for (AvaliacaoFuncionario avaliacaoFunc : avaliacao.
getParticipantes()) {
if (avaliacaoFunc.
getFuncionario().
getCodigoContabilidade().
equals(codigoFolha
)) {
String valor = StringUtil.
formatarValorComDoisDigitos(avaliacaoFunc.
getValorComissao().
toString());
cell.
setCellValue(valor.
replace(".",
","));
break;
}
}
System.
out.
println(cell.
getStringCellValue());
}
break;
}
}
}
try {
FileOutputStream stream =
new FileOutputStream("d:/importacao.xls");
workbook.
write(stream
);
stream.
close();
workbook.
close();
System.
out.
println("Success!!");
} catch (FileNotFoundException e
) {
e.
printStackTrace();
} catch (IOException e
) {
e.
printStackTrace();
}
file.
close();
workbook.
close();
} catch (FileNotFoundException e
) {
e.
printStackTrace();
} catch (Exception e
) {
e.
printStackTrace();
}
}
/*
public static void main( String[] args )
{
// Criando o arquivo e uma planilha chamada "Product"
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Product");
// Definindo alguns padroes de layout
sheet.setDefaultColumnWidth(15);
sheet.setDefaultRowHeight((short)400);
//Carregando os produtos
List products = getProducts();
int rownum = 0;
int cellnum = 0;
Cell cell;
Row row;
//Configurando estilos de células (Cores, alinhamento, formatação, etc..)
HSSFDataFormat numberFormat = workbook.createDataFormat();
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
CellStyle textStyle = workbook.createCellStyle();
textStyle.setAlignment(CellStyle.ALIGN_CENTER);
textStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
CellStyle numberStyle = workbook.createCellStyle();
numberStyle.setDataFormat(numberFormat.getFormat("#,##0.00"));
numberStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// Configurando Header
row = sheet.createRow(rownum++);
cell = row.createCell(cellnum++);
cell.setCellStyle(headerStyle);
cell.setCellValue("Code");
cell = row.createCell(cellnum++);
cell.setCellStyle(headerStyle);
cell.setCellValue("Name");
cell = row.createCell(cellnum++);
cell.setCellStyle(headerStyle);
cell.setCellValue("Price");
// Adicionando os dados dos produtos na planilha
for (Product product : products) {
row = sheet.createRow(rownum++);
cellnum = 0;
cell = row.createCell(cellnum++);
cell.setCellStyle(textStyle);
cell.setCellValue(product.getId());
cell = row.createCell(cellnum++);
cell.setCellStyle(textStyle);
cell.setCellValue(product.getName());
cell = row.createCell(cellnum++);
cell.setCellStyle(numberStyle);
cell.setCellValue(product.getPrice());
}
try {
//Escrevendo o arquivo em disco
FileOutputStream out = new FileOutputStream(new File("/tmp/products.xls"));
workbook.write(out);
out.close();
workbook.close();
System.out.println("Success!!");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//Simulando uma listagem de produtos
private static List getProducts(){
List products = new ArrayList();
products.add(new Product(1l, "Produto 1", 200.5d));
products.add(new Product(2l, "Produto 2", 1050.5d));
products.add(new Product(3l, "Produto 3", 50d));
products.add(new Product(4l, "Produto 4", 200d));
products.add(new Product(5l, "Produto 5", 450d));
products.add(new Product(6l, "Produto 6", 150.5d));
products.add(new Product(7l, "Produto 7", 300.99d));
products.add(new Product(8l, "Produto 8", 1000d));
products.add(new Product(9l, "Produto 9", 350d));
products.add(new Product(10l, "Produto 10", 200d));
return products;
}
*/
/*
public static void main(String[] args) throws IOException, DocumentException {
String caminhoArquivo = "H://recibo.pdf";
List<byte[]> arquivos = separarPDF(caminhoArquivo);
int i = 1;
for (byte[] arquivo : arquivos) {
// System.out.println(extrairPDF(arquivo));
String[] linhas = extrairPDFEmLinhas(arquivo);
boolean capturar = false;
for (String linha : linhas) {
if (capturar) {
System.out.println(linha);
capturar = false;
}
if (linha.contains("CC:")) {
capturar = true;
}
}
System.out.println(caminhoArquivo.substring(0, caminhoArquivo.indexOf(".pdf")) + "-" + String.format("%03d", i + 1) + ".pdf");
// gravarArquivo(caminhoArquivo.substring(0, caminhoArquivo.indexOf(".pdf")) + "-" + String.format("%03d", i + 1) + ".pdf", arquivo);
i++;
//break;
}
}
*/
public static void gravarArquivo
(String caminhoArquivo,
byte[] arquivo
) {
try {
FileOutputStream fos =
new FileOutputStream(caminhoArquivo
);
fos.
write(arquivo
);
fos.
flush();
fos.
close();
} catch (Exception e
) {
e.
printStackTrace();
}
}
public static String[] extrairPDFEmLinhas
(byte[] arquivo
) throws IOException {
StringBuilder texto =
new StringBuilder();
texto.
append(extrairPDF
(arquivo
));
return texto.
toString().
split("\n");
}
public static String extrairPDF
(byte[] arquivo
) throws IOException {
PdfReader reader =
new PdfReader
(arquivo
);
String textoExtraido =
null;
try {
PdfReaderContentParser parser =
new PdfReaderContentParser
(reader
);
int quantidadePaginas = reader.
getNumberOfPages();
TextExtractionStrategy strategy
;
for (int i =
1; i
<= quantidadePaginas
; i++
) {
strategy = parser.
processContent(i,
new SimpleTextExtractionStrategy
());
textoExtraido = strategy.
getResultantText().
toString();
}
} catch (Exception e
) {
e.
printStackTrace();
} finally {
reader.
close();
}
return textoExtraido
;
}
public static boolean verificarSeTemTexto
(String texto,
String textoExtraido
) {
if (VerificadorUtil.
naoEstaNuloOuVazio(textoExtraido
)) {
if (textoExtraido.
contains(texto
)) {
return true;
}
}
return false;
}
public static List<byte[]> separarPDF
(byte[] arquivo
) throws IOException {
return separarPDF
(new PdfReader
(arquivo
));
}
public static List<byte[]> separarPDF
(String caminhoArquivo
) throws IOException {
return separarPDF
(new PdfReader
(caminhoArquivo
));
}
private static List<byte[]> separarPDF
(PdfReader reader
) {
List<byte[]> arquivos =
new ArrayList<byte[]>();
try {
int quantidadePaginas = reader.
getNumberOfPages();
int i =
0;
while (i
< quantidadePaginas
) {
Document documento =
new Document(reader.
getPageSizeWithRotation(1));
ByteArrayOutputStream pdfOutputStream =
new ByteArrayOutputStream();
PdfCopy writer =
new PdfCopy
(documento, pdfOutputStream
);
documento.
open();
PdfImportedPage pagina = writer.
getImportedPage(reader, ++i
);
writer.
addPage(pagina
);
documento.
close();
writer.
close();
arquivos.
add(pdfOutputStream.
toByteArray());
pdfOutputStream.
close();
}
} catch (Exception e
) {
e.
printStackTrace();
} finally {
reader.
close();
}
return arquivos
;
}
public static void gerarPDF
(byte[] bytesPDF,
String destinoArquivo
) {
try {
PdfReader reader =
new PdfReader
(bytesPDF
);
Document documento =
new Document(reader.
getPageSizeWithRotation(1));
PdfCopy copy =
new PdfCopy
(documento,
new FileOutputStream(destinoArquivo
));
documento.
open();
PdfImportedPage page = copy.
getImportedPage(reader,
1);
copy.
addPage(page
);
documento.
close();
copy.
close();
} catch (Exception e
) {
e.
printStackTrace();
}
}
public static void separarSalvandoArquivoPDF
(String caminhoArquivo
) {
try {
PdfReader reader =
new PdfReader
(caminhoArquivo
);
int n = reader.
getNumberOfPages();
int i =
0;
while (i
< n
) {
String destinoArquivo = caminhoArquivo.
substring(0, caminhoArquivo.
indexOf(".pdf")) +
"-" +
String.
format("%03d", i +
1) +
".pdf";
Document document =
new Document(reader.
getPageSizeWithRotation(1));
PdfCopy writer =
new PdfCopy
(document,
new FileOutputStream(destinoArquivo
));
document.
open();
PdfImportedPage page = writer.
getImportedPage(reader, ++i
);
writer.
addPage(page
);
document.
close();
writer.
close();
}
} catch (Exception e
) {
e.
printStackTrace();
}
}
}