List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat
void setDataFormat(short fmt);
From source file:br.com.algoritmo.compilacao.CompilaXlsx.java
License:Apache License
/** * Create a library of cell styles//from ww w . j a v a 2s. c om */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 12); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 10); monthFont.setColor(IndexedColors.WHITE.getIndex()); monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); Font monthFont1 = wb.createFont(); monthFont1.setFontHeightInPoints((short) 10); monthFont1.setColor(IndexedColors.WHITE.getIndex()); monthFont1.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont1); style.setWrapText(true); styles.put("header1", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:br.com.objectos.way.io.WayIO.java
License:Apache License
static void applyFormatTo(Workbook wb, CellStyle style, String formatString) { DataFormat df = wb.createDataFormat(); style.setDataFormat(df.getFormat(formatString)); }
From source file:br.com.objectos.xls.PoiFormatString.java
License:Apache License
@Override public void applyTo(CellStyle style, DataFormat df, Font font) { style.setDataFormat(df.getFormat(format)); }
From source file:br.com.tecsinapse.dataio.style.TableCellStyle.java
License:LGPL
public CellStyle toCellStyle(Workbook wb) { CellStyle cellStyle = wb.createCellStyle(); final HSSFColor bgColor = getBackgroundColor(); if (bgColor != null) { if (cellStyle instanceof XSSFCellStyle) { ((XSSFCellStyle) cellStyle)/* ww w. j ava2 s . c o m*/ .setFillForegroundColor(new XSSFColor(toAwtColor(bgColor), new DefaultIndexedColorMap())); } else { cellStyle.setFillForegroundColor(getBackgroundColor().getIndex()); } cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); } if (getBorder() != null) { cellStyle = border.toCellStyle(cellStyle); } if (getVAlign() != null) { cellStyle.setVerticalAlignment(vAlign.getCellStyleVAlign()); } if (getHAlign() != null) { cellStyle.setAlignment(hAlign.getCellStyleHAlign()); } cellStyle.setWrapText(isWrapText()); Font font = wb.createFont(); configFont(font); cellStyle.setFont(font); if (cellFormat != null && !cellFormat.isEmpty()) { cellStyle.setDataFormat(wb.createDataFormat().getFormat(cellFormat)); } return cellStyle; }
From source file:br.com.tecsinapse.exporter.style.TableCellStyle.java
License:LGPL
public CellStyle toCellStyle(Workbook wb) { CellStyle cellStyle = wb.createCellStyle(); Font font = wb.createFont();/*from w w w . j a v a 2s .c o m*/ if (getBackgroundColor() != null) { cellStyle.setFillForegroundColor(getBackgroundColor().getIndex()); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); } if (getBorder() != null) { cellStyle = border.toCellStyle(cellStyle); } if (getvAlign() != null) { cellStyle.setVerticalAlignment(vAlign.getCellStyleVAlign()); } if (gethAlign() != null) { cellStyle.setAlignment(hAlign.getCellStyleHAlign()); } configFont(font); cellStyle.setFont(font); if (cellFormat != null && !cellFormat.isEmpty()) { cellStyle.setDataFormat(wb.createDataFormat().getFormat(cellFormat)); } return cellStyle; }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcel(String templateHead, String[] columns, HSSFWorkbook workbook) { try {/*from w w w . j a va 2 s. c om*/ 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 writeExcelCertificacoes(String templateHead, String[] columns, HSSFWorkbook workbook) { try {//w ww . j a v a2s . 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.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 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 {//from w ww.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:cn.afterturn.easypoi.excel.export.styler.ExcelExportStylerBorderImpl.java
License:Apache License
@Override public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) { CellStyle style = workbook.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderBottom(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setDataFormat(STRING_FORMAT); if (isWarp) { style.setWrapText(true);/*from w w w. j a v a2 s .co m*/ } return style; }
From source file:cn.afterturn.easypoi.excel.export.styler.ExcelExportStylerColorImpl.java
License:Apache License
@Override public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) { CellStyle style = workbook.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderBottom(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setFillForegroundColor((short) 41); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setDataFormat(STRING_FORMAT); if (isWarp) { style.setWrapText(true);//from w ww . j a v a 2 s. com } return style; }