List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue
void setCellValue(boolean value);
From source file:br.com.tecsinapse.dataio.util.WorkbookUtil.java
License:LGPL
private boolean setCellValueByType(Cell cell, Object o) { if (o instanceof Date) { cell.setCellValue(toExcelDate((Date) o)); return true; }//from ww w.ja v a2 s .c o m if (o instanceof Number) { Number number = (Number) o; cell.setCellValue(number.doubleValue()); return true; } return false; }
From source file:br.com.tecsinapse.exporter.util.WorkbookUtil.java
License:LGPL
private String setConvertedValue(Cell cell, TableCell tableCell, ExporterFormatter tableExporterFormatter) { Object cellValue = tableCell.getContentObject(); if (cellValue == null) { return null; }/*from w w w .ja v a 2 s . c o m*/ if (tableCell.getCellType().isAllowFormat()) { ExporterFormatter cellExporterFormatter = tableCell.getExporterFormatter(); ExporterFormatter exporterFormatter = cellExporterFormatter == null ? tableExporterFormatter : tableCell.getExporterFormatter(); boolean isCurrency = tableCell.getCellType() == CellType.CURRENCY_TYPE; String dataFormat = exporterFormatter != null ? exporterFormatter.getCellStringFormatByType(cellValue, isCurrency) : null; if (dataFormat != null && setCellValueByType(cell, cellValue)) { return dataFormat; } } cell.setCellValue(tableCell.getFormattedContentInternalFirst(tableExporterFormatter)); return null; }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcel(String templateHead, String[] columns, HSSFWorkbook workbook) { try {//from w w w . ja va 2s. com List<Funcionario> funcionarios = funcionarioService.search(); HSSFSheet sheet = workbook.createSheet(templateHead); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } for (int i = 0; i < columns.length; i++) { CellStyle stylerowHeading = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.WHITE.index); stylerowHeading.setFont(font); stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER); stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND); rowHeading.getCell(i).setCellStyle(stylerowHeading); } int r = 1; for (Funcionario f : funcionarios) { Row row = sheet.createRow(r); Cell Nome = row.createCell(0); Nome.setCellValue(f.getNome()); Cell cargo = row.createCell(1); cargo.setCellValue(f.getCargo()); Cell dtAdmissao = row.createCell(2); dtAdmissao.setCellValue(f.getDtAdmissao()); CellStyle styleDate = workbook.createCellStyle(); HSSFDataFormat dfAdmissao = workbook.createDataFormat(); styleDate.setDataFormat(dfAdmissao.getFormat("dd/mm/yyyy")); dtAdmissao.setCellStyle(styleDate); Cell area = row.createCell(3); area.setCellValue(f.getArea()); Cell gestor = row.createCell(4); gestor.setCellValue(f.getGestor()); try { Cell email = row.createCell(5); email.setCellValue(f.getEmail()); } catch (NullPointerException ne) { } try { Cell telefone = row.createCell(6); telefone.setCellValue(f.getTelefone()); } catch (NullPointerException e) { } try { Cell celular = row.createCell(7); celular.setCellValue(f.getCelular()); } catch (NullPointerException e) { } r++; } for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); } } catch (Exception e) { logger.error("Error gerate Report: " + e); System.out.println("Error" + e); } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcelFormacao(String templateHead, String[] columns, HSSFWorkbook workbook) { try {// w w w . ja v a2s .c o m List<Funcionario> funcionarios = funcionarioService.search(); HSSFSheet sheet = workbook.createSheet(templateHead); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } //Estilizar o Cabealho - Stylesheet the heading for (int i = 0; i < columns.length; i++) { CellStyle stylerowHeading = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.WHITE.index); stylerowHeading.setFont(font); stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER); stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND); rowHeading.getCell(i).setCellStyle(stylerowHeading); } //Preencher linhas int r = 1; for (Funcionario f : funcionarios) { if (!f.getFormacoes().isEmpty()) { for (Formacao fmc : f.getFormacoes()) { if (!fmc.getInstituicao().isEmpty() || !fmc.getCurso().isEmpty() || !fmc.getNivel().isEmpty()) { Row row = sheet.createRow(r); try { Cell Nome = row.createCell(0); Nome.setCellValue(f.getNome()); } catch (NullPointerException e) { } try { Cell curso = row.createCell(1); curso.setCellValue(fmc.getCurso()); } catch (NullPointerException e) { } try { Cell instituicao = row.createCell(2); instituicao.setCellValue(fmc.getInstituicao()); } catch (NullPointerException e) { } try { Cell nivel = row.createCell(3); nivel.setCellValue(fmc.getNivel()); } catch (NullPointerException e) { } try { Cell copia = row.createCell(4); copia.setCellValue(fmc.getCopiaCertificado()); } catch (NullPointerException e) { } r++; } } } } for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); } } catch (Exception e) { System.out.println("Error " + e); } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcelCertificacoes(String templateHead, String[] columns, HSSFWorkbook workbook) { try {/*from w w w . ja v a 2 s. com*/ List<Funcionario> funcionarios = funcionarioService.search(); HSSFSheet sheet = workbook.createSheet(templateHead); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } //Estilizar o Cabealho - Stylesheet the heading for (int i = 0; i < columns.length; i++) { CellStyle stylerowHeading = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.WHITE.index); stylerowHeading.setFont(font); stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER); stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND); rowHeading.getCell(i).setCellStyle(stylerowHeading); } //Preencher linhas int r = 1; for (Funcionario f : funcionarios) { if (!f.getCertificacoes().isEmpty()) { for (Certificacao ct : f.getCertificacoes()) { Row row = sheet.createRow(r); CellStyle styleDate = workbook.createCellStyle(); HSSFDataFormat dfExame = workbook.createDataFormat(); styleDate.setDataFormat(dfExame.getFormat("dd/mm/yyyy")); try { Cell Nome = row.createCell(0); Nome.setCellValue(f.getNome()); } catch (NullPointerException e) { } try { Cell cod = row.createCell(1); cod.setCellValue(ct.getCodigo()); } catch (NullPointerException e) { } try { Cell nome = row.createCell(3); nome.setCellValue(ct.getNome()); } catch (NullPointerException e) { } try { Cell empresa = row.createCell(2); empresa.setCellValue(ct.getEmpresa()); } catch (NullPointerException e) { } try { Cell dtExame = row.createCell(4); dtExame.setCellValue(ct.getDtExame()); dtExame.setCellStyle(styleDate); } catch (NullPointerException e) { } try { Cell dtValidade = row.createCell(5); dtValidade.setCellValue(ct.getDtValidade()); dtValidade.setCellStyle(styleDate); } catch (NullPointerException e) { } try { Cell copia = row.createCell(6); copia.setCellValue(ct.getCopia()); } catch (NullPointerException e) { } r++; } } } for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); } // String file = "C:/Users/ebranco.TELESULCORP/new.xls"; // FileOutputStream out = new FileOutputStream(file); // workbook.write(out); // out.close(); // workbook.close(); // System.out.println("Excell write succesfully"); } catch (Exception e) { System.out.println("Error" + e); } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcelIdiomas(String templateHead, String[] columns, HSSFWorkbook workbook) { try {//from w w w . java 2 s . co m List<Funcionario> funcionarios = funcionarioService.search(); HSSFSheet sheet = workbook.createSheet(templateHead); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } //Estilizar o Cabealho - Stylesheet the heading for (int i = 0; i < columns.length; i++) { CellStyle stylerowHeading = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.WHITE.index); stylerowHeading.setFont(font); stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER); stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND); rowHeading.getCell(i).setCellStyle(stylerowHeading); } //Preencher linhas int r = 1; for (Funcionario f : funcionarios) { if (!f.getIdiomas().isEmpty()) { for (Idioma idm : f.getIdiomas()) { try { if (idm.getNivel() != null || idm.getNome() != null) { Row row = sheet.createRow(r); Cell Nome = row.createCell(0); Nome.setCellValue(f.getNome()); Cell language = row.createCell(1); language.setCellValue(idm.getNome().toString()); Cell nivel = row.createCell(2); nivel.setCellValue(idm.getNivel().toString()); r++; } } catch (NullPointerException ne) { System.out.println("Error " + ne); break; } } } } for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); } } catch (Exception e) { System.out.println("Error" + e); } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcelSingle(Long id, HSSFWorkbook workbook) { String[] columns = { "Nome", "Cargo", "Data de Admissao", "?rea", "Gestor", "Email", "Telefone", "Celular" }; String[] colFormacao = { "Formao", "Curso", "Instituio", "Cpia de Certificao" }; String[] colIdioma = { "Idioma", "Nvel" }; String[] colCertificacao = { "Certificadora", "Exame", "Cdigo", "Data de Exame", "Data de Validade", "Cpia de Certificado" }; try {// w w w .j a v a 2 s.c o m Funcionario funcionario = this.funcionarioService.searchById(id); HSSFSheet sheet = workbook.createSheet("Funcionrio"); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } stylizeHeader(rowHeading, workbook, columns); int r = 1; Row row = sheet.createRow(r); Cell Nome = row.createCell(0); Nome.setCellValue(funcionario.getNome()); Cell cargo = row.createCell(1); cargo.setCellValue(funcionario.getCargo()); Cell dtAdmissao = row.createCell(2); dtAdmissao.setCellValue(funcionario.getDtAdmissao()); CellStyle styleDate = workbook.createCellStyle(); HSSFDataFormat dfAdmissao = workbook.createDataFormat(); styleDate.setDataFormat(dfAdmissao.getFormat("dd/mm/yyyy")); dtAdmissao.setCellStyle(styleDate); Cell area = row.createCell(3); area.setCellValue(funcionario.getArea()); Cell gestor = row.createCell(4); gestor.setCellValue(funcionario.getGestor()); try { Cell email = row.createCell(5); email.setCellValue(funcionario.getEmail()); } catch (NullPointerException ne) { } try { Cell telefone = row.createCell(6); telefone.setCellValue(funcionario.getTelefone()); } catch (NullPointerException ne) { } try { Cell celular = row.createCell(7); celular.setCellValue(funcionario.getCelular()); } catch (NullPointerException ne) { } int auxRow = 12; for (Formacao form : funcionario.getFormacoes()) { if (!form.getCurso().isEmpty() || !form.getNivel().isEmpty() || !form.getInstituicao().isEmpty()) { int headerFormacao = 11; Row rowHeadingForm = sheet.createRow(headerFormacao); for (int i = 0; i < colFormacao.length; i++) { rowHeadingForm.createCell(i).setCellValue(colFormacao[i]); } stylizeHeader(rowHeadingForm, workbook, colFormacao); int rowFormacao = 12; Row rowFormacaoDatas = sheet.createRow(rowFormacao); Cell formacao = rowFormacaoDatas.createCell(0); Cell curso = rowFormacaoDatas.createCell(1); Cell instituicao = rowFormacaoDatas.createCell(2); Cell copy = rowFormacaoDatas.createCell(3); for (Formacao f : funcionario.getFormacoes()) { try { formacao.setCellValue(f.getNivel()); } catch (NullPointerException e) { } try { curso.setCellValue(f.getCurso()); } catch (NullPointerException e) { } try { instituicao.setCellValue(f.getInstituicao()); } catch (NullPointerException e) { } try { copy.setCellValue(f.getCopiaCertificado()); } catch (NullPointerException e) { } rowFormacao++; auxRow = rowFormacao; } autoSizeColum(sheet, colFormacao); } } for (Idioma i : funcionario.getIdiomas()) { try { if (!i.getNome().toString().isEmpty() && !i.getNivel().toString().isEmpty()) { int headerIdiomas = auxRow + 9; Row rowHeadingIdioma = sheet.createRow(headerIdiomas); for (int j = 0; j < colIdioma.length; j++) { rowHeadingIdioma.createCell(j).setCellValue(colIdioma[j]); } stylizeHeader(rowHeadingIdioma, workbook, colIdioma); int rowIdioma = headerIdiomas + 1; Row rowIdiomasDatas = sheet.createRow(rowIdioma); Cell nivelIdm = rowIdiomasDatas.createCell(0); Cell language = rowIdiomasDatas.createCell(1); for (Idioma j : funcionario.getIdiomas()) { nivelIdm.setCellValue(j.getNivel().toString()); language.setCellValue(j.getNome().toString()); rowIdioma++; auxRow = rowIdioma; } autoSizeColum(sheet, colIdioma); } } catch (NullPointerException ne) { logger.error("Idiomas" + ne); break; } } if (funcionario.getCertificacoes().size() > 0) { int headerCertificacao = auxRow + 9; Row rowHeadingCert = sheet.createRow(headerCertificacao); for (int j = 0; j < colCertificacao.length; j++) { rowHeadingCert.createCell(j).setCellValue(colCertificacao[j]); } stylizeHeader(rowHeadingCert, workbook, colCertificacao); int rowCert = headerCertificacao + 1; for (Certificacao c : funcionario.getCertificacoes()) { Row rowCertDatas = sheet.createRow(rowCert); Cell certificadora = rowCertDatas.createCell(0); Cell exame = rowCertDatas.createCell(1); Cell codigo = rowCertDatas.createCell(2); Cell dtExame = rowCertDatas.createCell(3); dtExame.setCellStyle(styleDate); Cell dtValidade = rowCertDatas.createCell(4); dtValidade.setCellStyle(styleDate); Cell copia = rowCertDatas.createCell(5); try { certificadora.setCellValue(c.getEmpresa()); } catch (NullPointerException e) { } try { exame.setCellValue(c.getNome()); } catch (NullPointerException e) { } try { codigo.setCellValue(c.getCodigo()); } catch (NullPointerException e) { } try { dtExame.setCellValue(c.getDtExame()); } catch (NullPointerException e) { } try { dtValidade.setCellValue(c.getDtValidade()); } catch (NullPointerException e) { } try { copia.setCellValue(c.getCopia()); } catch (NullPointerException e) { } rowCert++; } autoSizeColum(sheet, colCertificacao); } //r++; } catch (Exception e) { logger.error("Error Writing Single Report: " + e); System.out.println("rror Writing Single Report: " + e); } }
From source file:br.ufal.cideei.util.count.MetricsTable.java
License:Open Source License
private void dumpEntry(String method, Collection<DefaultKeyValue> properties) { if (!headersWerePrint) { printHeaders();// w ww .ja va 2 s . co m headersWerePrint = true; } Row entryRow = sheet.createRow(rowCount++); Cell methodSignatureCell = entryRow.createCell(0); methodSignatureCell.setCellValue(method); Iterator<DefaultKeyValue> iterator = properties.iterator(); while (iterator.hasNext()) { DefaultKeyValue nextKeyVal = iterator.next(); String property = (String) nextKeyVal.getKey(); Object value = nextKeyVal.getValue(); Integer columnIndex = (Integer) columnMapping.getKey(property); if (value instanceof Double) { Cell cell = entryRow.createCell(columnIndex); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue((Double) value); } else { Cell cell = entryRow.createCell(columnIndex); cell.setCellValue((String) value); cell.setCellType(HSSFCell.CELL_TYPE_STRING); } } }
From source file:br.ufal.cideei.util.count.MetricsTable.java
License:Open Source License
private void printHeaders() { MapIterator columnMapIterator = columnMapping.orderedMapIterator(); Row headerRow = sheet.createRow(0);// w w w .j av a 2s . c om rowCount++; while (columnMapIterator.hasNext()) { Cell headerCell = headerRow.createCell((Integer) columnMapIterator.next()); headerCell.setCellValue((String) columnMapIterator.getValue()); } }
From source file:br.ufal.cideei.util.count.MetricsTable.java
License:Open Source License
private void printFooters() { int columns = columnMapping.size(); Row firstRow = sheet.getRow(1);/*from www. j a va 2 s . c o m*/ Row lastRow = sheet.getRow(rowCount - 1); Row sumFooterRow = sheet.createRow(rowCount++); Cell sumFooterLabelCell = sumFooterRow.createCell(0); sumFooterLabelCell.setCellValue("SUM"); Row averageFooterRow = sheet.createRow(rowCount++); Cell averageFooterLabelCell = averageFooterRow.createCell(0); averageFooterLabelCell.setCellValue("AVERAGE"); for (int index = 0; index <= columns; index++) { Cell cell = firstRow.getCell(index); if (cell == null) { cell = firstRow.createCell(index); } Cell sumFooterCell = sumFooterRow.createCell(index); Cell averageFooterCell = averageFooterRow.createCell(index); CellReference firstCell = new CellReference(firstRow.getCell(index)); Cell lastRowCell = lastRow.getCell(index); if (lastRowCell == null) { lastRowCell = lastRow.createCell(index); } CellReference lastCell = new CellReference(lastRowCell); sumFooterCell .setCellFormula("SUM(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")"); averageFooterCell.setCellFormula( "AVERAGE(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")"); } }