List of usage examples for org.apache.poi.ss.usermodel CellStyle setAlignment
void setAlignment(HorizontalAlignment align);
From source file:edu.vt.vbi.patric.common.ExcelHelper.java
License:Apache License
/** * This method creates a map of Cellstyle objects for page building. Note: this method used for HSSF pages * /*from www. j a v a 2 s . c om*/ * @return hashmap of styles */ private Map<String, CellStyle> createStyles() { // create custom colors HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); // This replaces various shades of grey with custom colors palette.setColorAtIndex(HSSFColor.GREY_25_PERCENT.index, (byte) 0, // RGB red (0-255) (byte) 52, // RGB green (byte) 94 // RGB blue ); palette.setColorAtIndex(HSSFColor.GREY_40_PERCENT.index, (byte) 230, (byte) 240, (byte) 248); palette.setColorAtIndex(HSSFColor.GREY_50_PERCENT.index, (byte) 255, (byte) 193, (byte) 193); // Create header style CellStyle style = createBorderStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(headerFont); styles.put("header", style); // Create alternating-color body styles Font bodyFont = wb.createFont(); bodyFont.setFontHeightInPoints((short) 8); style = createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(bodyFont); style.setWrapText(true); styles.put("bg1", style); style = createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(bodyFont); styles.put("bg2", style); // create style for empty cell style = createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(bodyFont); style.setWrapText(true); styles.put("empty", style); return styles; }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? */// w w w . j a v a 2 s .c o m @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xls"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(CellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? //ww w. ja va2 s . c o m */ @Test public void testModifyCellAttribute() throws Exception { try { LOGGER.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString()); LOGGER.debug("testModifyCellAttribute after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet2"); // sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); Font font = wb.createFont(); font.setFontHeight((short) 16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(CellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); // row.setHeight((short)300); // row? height for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString()); Sheet sheetT = wbT.getSheet("cell test sheet2"); LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles()); CellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); Font fontT = ((HSSFCellStyle) cs1).getFont(wbT); LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight()); LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight()); LOGGER.debug("font getFontName : {}", fontT.getFontName()); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); LOGGER.debug("getWrapText : {}", cs1.getWrapText()); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue()); assertEquals(16, fontT.getFontHeight()); assertEquals(3, fontT.getBoldweight()); assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//*from w w w. jav a2 s . c o m*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { log.debug("testWriteExcelFileAttribute start...."); short rowheight = 40; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cs.setFillPattern(HSSFCellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); log.debug("getAlignment : " + cs1.getAlignment()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); log.debug("getFillPattern : " + cs1.getFillPattern()); assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern()); log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor()); log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFileAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? //from w w w . ja v a 2 s. co m */ @Test public void testModifyCellAttribute() throws Exception { try { LOGGER.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? XSSFWorkbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); LOGGER.debug("testModifyCellAttribute after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet2"); sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontHeight(16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); row.setHeight((short) 300); // row? height for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? XSSFWorkbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("cell test sheet2"); LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles()); XSSFCellStyle cs1 = (XSSFCellStyle) wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); XSSFFont fontT = cs1.getFont(); LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight()); LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight()); LOGGER.debug("font getFontName : {}", fontT.getFontName()); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); LOGGER.debug("getWrapText : {}", cs1.getWrapText()); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue()); assertEquals(320, fontT.getFontHeight()); assertEquals(400, fontT.getBoldweight()); LOGGER.debug("fontT.getBoldweight()? ? 400? ?"); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellAttribute end...."); } }
From source file:eleanalysis.SampleLibrary.java
/** * writes a report in the form of an excel spreadsheet that is exported. * Stage variable is for using a filechooser to pick where to save file. * @param myStage Stage is for saving file using FileChooser * @throws FileNotFoundException /*from w w w . j a va 2 s .c o m*/ */ public void writeReport(Stage myStage) throws FileNotFoundException { FileChooser pickFile = new FileChooser(); pickFile.setInitialDirectory(new File("C:\\Users\\Yan\\Documents\\NetBeansProjects\\EleAnalysis\\")); pickFile.getExtensionFilters().addAll(new FileChooser.ExtensionFilter("XLS", "*.xls"), new FileChooser.ExtensionFilter("XLSX", "*.xlsx")); File writeFile = pickFile.showSaveDialog(myStage); FileOutputStream fileOut; fileOut = new FileOutputStream(writeFile); Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("WDXRF"); sheet1.setDefaultColumnWidth(15); // Create a cell space for (int i = 0; i < ElementUtils.skf.length + 5; i++) { Row tempR = sheet1.createRow(i); for (int j = 0; j <= array.size(); j++) { Cell tempC = tempR.createCell(j); } } CellStyle csCenter = wb.createCellStyle(); csCenter.setAlignment(CellStyle.ALIGN_CENTER); csCenter.setBorderTop(CellStyle.BORDER_THIN); csCenter.setBorderLeft(CellStyle.BORDER_THIN); csCenter.setBorderRight(CellStyle.BORDER_THIN); csCenter.setBorderBottom(CellStyle.BORDER_THIN); CellStyle csRight = wb.createCellStyle(); csRight.setAlignment(CellStyle.ALIGN_RIGHT); csRight.setBorderTop(CellStyle.BORDER_THIN); csRight.setBorderLeft(CellStyle.BORDER_THIN); csRight.setBorderRight(CellStyle.BORDER_THIN); csRight.setBorderBottom(CellStyle.BORDER_THIN); CellStyle csLeft = wb.createCellStyle(); csLeft.setAlignment(CellStyle.ALIGN_LEFT); csLeft.setBorderTop(CellStyle.BORDER_THIN); csLeft.setBorderLeft(CellStyle.BORDER_THIN); csLeft.setBorderRight(CellStyle.BORDER_THIN); csLeft.setBorderBottom(CellStyle.BORDER_THIN); //Top Row sheet1.getRow(0).setHeightInPoints(25); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, array.size())); sheet1.getRow(0).getCell(0).setCellValue("WDXRF Analysis"); sheet1.getRow(0).getCell(0).setCellStyle(csCenter); sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, array.size())); //Second Row sheet1.getRow(1).getCell(0).setCellValue("Conc as Wt%"); sheet1.getRow(1).getCell(0).setCellStyle(csCenter); // Third Row sheet1.getRow(2).setHeightInPoints(35); sheet1.getRow(2).getCell(0).setCellValue("Common Oxides/Oxication States"); sheet1.getRow(2).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(2).getCell(j).setCellStyle(csLeft); sheet1.getRow(2).getCell(j).setCellValue(array.get(j - 1).getName()); } //Fourth Row sheet1.getRow(3).getCell(0).setCellValue("% Detectable"); sheet1.getRow(3).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(3).getCell(j).setCellValue("0.00"); sheet1.getRow(3).getCell(j).setCellStyle(csLeft); } //Fifth Row sheet1.addMergedRegion(new CellRangeAddress(4, 4, 0, array.size())); sheet1.getRow(4).getCell(0).setCellValue("Results Normalized with Respect to Detectable Concentration"); sheet1.getRow(4).getCell(0).setCellStyle(csCenter); //Rows 6 and beyond. Prints element list and defaults values to 0 for (int i = 5; i < ElementUtils.skf.length + 5; i++) { sheet1.getRow(i).getCell(0).setCellValue(ElementUtils.skf[i - 5]); sheet1.getRow(i).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(i).getCell(j).setCellValue("0.0"); sheet1.getRow(i).getCell(j).setCellStyle(csRight); } } // Copies values in SampleLibrary array into report for (int i = 0; i < array.size(); i++) { List<Element> eleArray = array.get(i).getArrayCopy(); for (int j = 0; j < ElementUtils.skf.length; j++) { for (int k = 0; k < eleArray.size(); k++) { if (ElementUtils.skf[j].contains(eleArray.get(k).getBaseElement())) sheet1.getRow(j + 5).getCell(i + 1).setCellValue(eleArray.get(k).getConcWeight()); } } } try { wb.write(fileOut); wb.close(); fileOut.close(); } catch (IOException ex) { Logger.getLogger(SampleLibrary.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Export.ExporOnlyViagemExcel.java
private void dataTableDados(Workbook wb, Date dataInicio, Date dataFim) throws ParseException { Font fTitulo = wb.createFont(); fTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fTitulo.setFontHeightInPoints((short) 14); Font fTituloP = wb.createFont(); fTituloP.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fTituloP.setFontHeightInPoints((short) 12); // fTituloP.setStrikeout(true); fTituloP.setUnderline(org.apache.poi.ss.usermodel.Font.U_SINGLE); Font fTituloTabela = wb.createFont(); fTituloTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fTituloTabela.setFontHeightInPoints((short) 8); Font fCorpoTabela = wb.createFont(); fCorpoTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_NORMAL); fCorpoTabela.setFontHeightInPoints((short) 8.5); Font fRodapeTabela = wb.createFont(); fRodapeTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fRodapeTabela.setFontHeightInPoints((short) 8.5); Font fNormal = wb.createFont(); fNormal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fNormal.setFontHeightInPoints((short) 8.5); CellStyle csTitulo = wb.createCellStyle(); csTitulo.setFont(fTitulo);//from www . ja v a2s .c o m csTitulo.setAlignment((short) 1); csTitulo.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); csTitulo.setBorderBottom((short) 0); csTitulo.setBorderTop((short) 0); csTitulo.setBorderRight((short) 0); csTitulo.setBorderLeft((short) 0); csTitulo.setWrapText(true); CellStyle csTituloP = wb.createCellStyle(); csTituloP.setFont(fTituloP); csTituloP.setAlignment((short) 1); csTituloP.setVerticalAlignment((short) 1); csTituloP.setBorderBottom((short) 0); csTituloP.setBorderTop((short) 0); csTituloP.setBorderRight((short) 0); csTituloP.setBorderLeft((short) 0); csTituloP.setWrapText(true); CellStyle csTituloT = wb.createCellStyle(); csTituloT.setFont(fTituloP); csTituloT.setAlignment((short) 1); csTituloT.setVerticalAlignment((short) 1); csTituloT.setBorderBottom((short) 0); csTituloT.setBorderTop((short) 0); csTituloT.setBorderRight((short) 0); csTituloT.setBorderLeft((short) 0); csTituloT.setWrapText(true); CellStyle csTituloTabela = wb.createCellStyle(); csTituloTabela.setFont(fTituloTabela); csTituloTabela.setAlignment(CellStyle.ALIGN_CENTER); csTituloTabela.setVerticalAlignment((short) 2); csTituloTabela.setBorderBottom((short) 2); csTituloTabela.setBorderTop((short) 2); csTituloTabela.setBorderRight((short) 2); csTituloTabela.setBorderLeft((short) 2); csTituloTabela.setWrapText(true); CellStyle csTituloTabelaNBorder = wb.createCellStyle(); csTituloTabelaNBorder.setFont(fTituloTabela); csTituloTabelaNBorder.setAlignment(CellStyle.ALIGN_CENTER); csTituloTabelaNBorder.setVerticalAlignment((short) 2); csTituloTabelaNBorder.setBorderBottom((short) 2); csTituloTabelaNBorder.setBorderTop((short) 2); csTituloTabelaNBorder.setBorderRight((short) 2); csTituloTabelaNBorder.setBorderLeft((short) 2); csTituloTabelaNBorder.setWrapText(true); CellStyle csCorpoTabela = wb.createCellStyle(); csCorpoTabela.setFont(fCorpoTabela); csCorpoTabela.setAlignment((short) 2); csCorpoTabela.setVerticalAlignment((short) 1); csCorpoTabela.setBorderBottom((short) 1); csCorpoTabela.setBorderTop((short) 1); csCorpoTabela.setBorderRight((short) 1); csCorpoTabela.setBorderLeft((short) 1); csCorpoTabela.setWrapText(true); CellStyle csCorpoTabelaR = wb.createCellStyle(); csCorpoTabelaR.setFont(fCorpoTabela); csCorpoTabelaR.setAlignment(CellStyle.ALIGN_RIGHT); csCorpoTabelaR.setVerticalAlignment((short) 1); csCorpoTabelaR.setBorderBottom((short) 1); csCorpoTabelaR.setBorderTop((short) 1); csCorpoTabelaR.setBorderRight((short) 1); csCorpoTabelaR.setBorderLeft((short) 1); csCorpoTabelaR.setWrapText(true); CellStyle csCorpoTabelaL = wb.createCellStyle(); csCorpoTabelaL.setFont(fCorpoTabela); csCorpoTabelaL.setAlignment(CellStyle.ALIGN_LEFT); csCorpoTabelaL.setVerticalAlignment((short) 1); csCorpoTabelaL.setBorderBottom((short) 1); csCorpoTabelaL.setBorderTop((short) 1); csCorpoTabelaL.setBorderRight((short) 1); csCorpoTabelaL.setBorderLeft((short) 1); csCorpoTabelaL.setWrapText(true); CellStyle csRodapeTabela = wb.createCellStyle(); csRodapeTabela.setFont(fRodapeTabela); csRodapeTabela.setAlignment((short) 1); csRodapeTabela.setVerticalAlignment((short) 2); csRodapeTabela.setBorderBottom((short) 2); csRodapeTabela.setBorderTop((short) 2); csRodapeTabela.setBorderRight((short) 2); csRodapeTabela.setBorderLeft((short) 2); csRodapeTabela.setWrapText(true); CellStyle csRodapeTabelaR = wb.createCellStyle(); csRodapeTabelaR.setFont(fRodapeTabela); csRodapeTabelaR.setAlignment(CellStyle.ALIGN_RIGHT); csRodapeTabelaR.setVerticalAlignment((short) 2); csRodapeTabelaR.setBorderBottom((short) 2); csRodapeTabelaR.setBorderTop((short) 2); csRodapeTabelaR.setBorderRight((short) 2); csRodapeTabelaR.setBorderLeft((short) 2); csRodapeTabelaR.setWrapText(true); CellStyle csNomal = wb.createCellStyle(); csNomal.setFont(fCorpoTabela); csNomal.setAlignment((short) 1); csNomal.setVerticalAlignment((short) 1); csNomal.setBorderBottom((short) 0); csNomal.setBorderTop((short) 0); csNomal.setBorderRight((short) 0); csNomal.setBorderLeft((short) 0); csNomal.setWrapText(true); int i, p = 0; dataViagem(dataInicio, dataFim); for (Map.Entry<String, ArrayList<HashMap<String, Object>>> data : hasList.entrySet()) { totalSelo = 0f; totalConsumo = 0f; totalTotal = 0f; totalPremio = 0f; totalComissao = 0f; linha = 0; SimpleDateFormat sdfEsp = new SimpleDateFormat("MMMM, yyyy", Locale.ENGLISH); SimpleDateFormat sdf = new SimpleDateFormat("MM-yyyy"); String titile = sdfEsp.format(sdf.parse((data.getKey()))).toUpperCase(); Sheet s = wb.createSheet(titile); dataTableTitile(s, titile, csTitulo, csTituloP, csTituloTabelaNBorder); Row r = s.createRow(linha); Cell c = r.createCell(2); for (int j = 0; j < 13; j++) { switch (j) { case 0: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 1, 4); break; case 1: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 2, 6); break; case 2: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 3, 8); break; case 3: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 4, 6); break; case 4: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 5, 6); break; case 5: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 6, 4); break; case 6: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 7, 25); break; case 7: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 8, 10); break; case 8: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 9, 10); break; case 9: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 10, 8); break; case 10: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 11, 8); break; case 11: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 12, 8); break; case 12: createCell(c, r, s, csTituloTabela, linha, linha, funcaoTitulo(j), 13, 8); break; default: break; } } i = 1; for (HashMap<String, Object> hashMap : data.getValue()) { linha++; r = s.createRow(linha); c = r.createCell(2); createCell(c, r, s, csCorpoTabela, linha, linha, (i + ""), 1, 4); createCell(c, r, s, csCorpoTabela, linha, linha, toString(hashMap.get(DATA)), 2, 6); createCell(c, r, s, csCorpoTabela, linha, linha, toString(hashMap.get(NUMEROAPOLICE)), 3, 7); createCell(c, r, s, csCorpoTabela, linha, linha, ConfigDoc.toFormat(toString(hashMap.get(INICIO)), "dd-MM-yyyy", "yyyy-MM-dd"), 4, 6); createCell(c, r, s, csCorpoTabela, linha, linha, ConfigDoc.toFormat(toString(hashMap.get(FIM)), "dd-MM-yyyy", "yyyy-MM-dd"), 5, 6); createCell(c, r, s, csCorpoTabela, linha, linha, toString(hashMap.get(DIAS)), 6, 4); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(hashMap.get(CLIENTE)), 7, 20); createCell(c, r, s, csCorpoTabela, linha, linha, toString(hashMap.get(RECIBO)), 8, 10); createCell(c, r, s, csCorpoTabelaR, linha, linha, toString(hashMap.get(PREMIO)), 9, 10); totalComissao += toFloat(toString(hashMap.get(COMISSAO))); createCell(c, r, s, csCorpoTabelaR, linha, linha, ConfigDoc.toMoeda(toString(hashMap.get(COMISSAO)), ""), 10, 8); totalConsumo += toFloat(toString(hashMap.get(CONSUMO))); createCell(c, r, s, csCorpoTabelaR, linha, linha, ConfigDoc.toMoeda(toString(hashMap.get(CONSUMO)), ""), 11, 8); totalSelo += toFloat(toString(hashMap.get(SELO))); createCell(c, r, s, csCorpoTabelaR, linha, linha, ConfigDoc.toMoeda(toString(hashMap.get(SELO)), ""), 12, 8); totalTotal += toFloat(toString(hashMap.get(TOTAL))); createCell(c, r, s, csCorpoTabelaR, linha, linha, ConfigDoc.toMoeda(toString(hashMap.get(TOTAL)), ""), 13, 8); i++; } linha++; radapeTable(s, csRodapeTabelaR); } }
From source file:Export.ExportMapaProducaoExcel__.java
public String criarDoc(String user, Date incio, Date fim) { try {//from w w w . j a v a2s .c om Workbook wb = new HSSFWorkbook(); Font fTitulo = wb.createFont(); fTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); fTitulo.setFontHeightInPoints((short) 22); Font fTituloP = wb.createFont(); fTituloP.setBoldweight(Font.BOLDWEIGHT_BOLD); fTituloP.setFontHeightInPoints((short) 13); Font fTituloTabela = wb.createFont(); fTituloTabela.setBoldweight(Font.BOLDWEIGHT_BOLD); fTituloTabela.setFontHeightInPoints((short) 12.5); Font fCorpoTabela = wb.createFont(); fCorpoTabela.setBoldweight(Font.BOLDWEIGHT_NORMAL); fCorpoTabela.setFontHeightInPoints((short) 11.5); Font fRodapeTabela = wb.createFont(); fRodapeTabela.setBoldweight(Font.BOLDWEIGHT_BOLD); fRodapeTabela.setFontHeightInPoints((short) 11.5); Font fNormal = wb.createFont(); fNormal.setBoldweight(Font.BOLDWEIGHT_BOLD); fNormal.setFontHeightInPoints((short) 11); CellStyle csTitulo = wb.createCellStyle(); csTitulo.setFont(fTitulo); csTitulo.setAlignment((short) 1); csTitulo.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); csTitulo.setWrapText(true); csTitulo.setBorderBottom((short) 0); csTitulo.setBorderTop((short) 0); csTitulo.setBorderRight((short) 0); csTitulo.setBorderLeft((short) 0); csTitulo.setWrapText(true); CellStyle csTituloP = wb.createCellStyle(); csTituloP.setFont(fTituloP); csTituloP.setAlignment((short) 1); csTituloP.setVerticalAlignment((short) 1); csTituloP.setWrapText(true); csTituloP.setBorderBottom((short) 0); csTituloP.setBorderTop((short) 0); csTituloP.setBorderRight((short) 0); csTituloP.setBorderLeft((short) 0); csTituloP.setWrapText(true); CellStyle csTituloT = wb.createCellStyle(); csTituloT.setFont(fTituloP); csTituloT.setAlignment((short) 1); csTituloT.setVerticalAlignment((short) 1); csTituloT.setWrapText(true); csTituloT.setBorderBottom((short) 0); csTituloT.setBorderTop((short) 0); csTituloT.setBorderRight((short) 0); csTituloT.setBorderLeft((short) 0); csTituloT.setWrapText(true); CellStyle csTituloTabela = wb.createCellStyle(); csTituloTabela.setFont(fTituloTabela); csTituloTabela.setAlignment(CellStyle.ALIGN_CENTER); csTituloTabela.setVerticalAlignment((short) 2); csTituloTabela.setBorderBottom((short) 2); csTituloTabela.setBorderTop((short) 2); csTituloTabela.setBorderRight((short) 2); csTituloTabela.setBorderLeft((short) 2); csTituloTabela.setWrapText(true); CellStyle csCorpoTabela = wb.createCellStyle(); csCorpoTabela.setFont(fCorpoTabela); csCorpoTabela.setAlignment((short) 2); csCorpoTabela.setVerticalAlignment((short) 1); csCorpoTabela.setBorderBottom((short) 1); csCorpoTabela.setBorderTop((short) 1); csCorpoTabela.setBorderRight((short) 1); csCorpoTabela.setBorderLeft((short) 1); csCorpoTabela.setWrapText(true); CellStyle csCorpoTabelaR = wb.createCellStyle(); csCorpoTabelaR.setFont(fCorpoTabela); csCorpoTabelaR.setAlignment(CellStyle.ALIGN_RIGHT); csCorpoTabelaR.setVerticalAlignment((short) 1); csCorpoTabelaR.setBorderBottom((short) 1); csCorpoTabelaR.setBorderTop((short) 1); csCorpoTabelaR.setBorderRight((short) 1); csCorpoTabelaR.setBorderLeft((short) 1); csCorpoTabelaR.setWrapText(true); CellStyle csCorpoTabelaL = wb.createCellStyle(); csCorpoTabelaL.setFont(fCorpoTabela); csCorpoTabelaL.setAlignment(CellStyle.ALIGN_LEFT); csCorpoTabelaL.setVerticalAlignment((short) 1); csCorpoTabelaL.setBorderBottom((short) 1); csCorpoTabelaL.setBorderTop((short) 1); csCorpoTabelaL.setBorderRight((short) 1); csCorpoTabelaL.setBorderLeft((short) 1); csCorpoTabelaL.setWrapText(true); CellStyle csRodapeTabela = wb.createCellStyle(); csRodapeTabela.setFont(fRodapeTabela); csRodapeTabela.setAlignment((short) 1); csRodapeTabela.setVerticalAlignment((short) 2); csRodapeTabela.setBorderBottom((short) 2); csRodapeTabela.setBorderTop((short) 2); csRodapeTabela.setBorderRight((short) 2); csRodapeTabela.setBorderLeft((short) 2); csRodapeTabela.setWrapText(true); CellStyle csRodapeTabelaR = wb.createCellStyle(); csRodapeTabelaR.setFont(fRodapeTabela); csRodapeTabelaR.setAlignment(CellStyle.ALIGN_RIGHT); csRodapeTabelaR.setVerticalAlignment((short) 2); csRodapeTabelaR.setBorderBottom((short) 2); csRodapeTabelaR.setBorderTop((short) 2); csRodapeTabelaR.setBorderRight((short) 2); csRodapeTabelaR.setBorderLeft((short) 2); csRodapeTabelaR.setWrapText(true); CellStyle csNomal = wb.createCellStyle(); csNomal.setFont(fCorpoTabela); csNomal.setAlignment((short) 1); csNomal.setVerticalAlignment((short) 1); csNomal.setBorderBottom((short) 0); csNomal.setBorderTop((short) 0); csNomal.setBorderRight((short) 0); csNomal.setBorderLeft((short) 0); csNomal.setWrapText(true); SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy hh'.'mm'.'ss"); SimpleDateFormat sdfPt = new SimpleDateFormat("dd-MM-yyyy"); File ff = new File(getDiretorio() + "/" + user + "/Relatorio/"); ff.mkdirs(); String stringData = sdf.format(new Date()); ff = new File(ff.getAbsoluteFile() + "/" + "Export Mapa ProducaoExel " + stringData + ".xls"); String reString = "../Documentos/" + user + "/Relatorio/" + "Export Mapa ProducaoExel " + stringData + ".xls"; Sheet s = wb.createSheet("Mapa de produo de ".toUpperCase() + ((incio != null) ? sdfPt.format(incio) + " " : " dos Ultimos anos te hoje".toUpperCase()) + ((fim == null) ? "" : sdfPt.format(fim))); int linha = 0; // int pictureIdx; // try (InputStream inputStream = new FileInputStream("logo1.png")) { // byte[] bytes = IOUtils.toByteArray(inputStream); // pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); // } // CreationHelper helper = wb.getCreationHelper(); // Drawing drawing = s.createDrawingPatriarch(); // ClientAnchor anchor = helper.createClientAnchor(); // anchor.setCol1(0); // anchor.setCol2(3); // anchor.setRow1(0); // Picture pict = drawing.createPicture(anchor, pictureIdx); // pict.resize(); Row r = s.createRow(linha); Cell c = r.createCell(2); CreateCell(c, r, s, csTitulo, linha, linha + 3, ConfigDoc.Empresa.NOME, 1, 22); linha += 4; r = s.createRow(linha); CreateCell(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.ENDERECO, 1, 22); linha++; r = s.createRow(linha); CreateCell(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.CAIXAPOSTAL, 1, 22); linha++; r = s.createRow(linha); CreateCell(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.TELEFAX + " " + ConfigDoc.Empresa.EMAIL, 1, 22); linha++; r = s.createRow(linha); CreateCell(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.SOCIEDADE, 1, 22); ResultSet rs = ud.relatorioSeguroForImpresao(incio, fim); Consumer<HashMap<String, Object>> act = (map) -> { list = new ArrayList<>(); putNewDado(map, incio, fim); }; Call.forEchaResultSet(act, rs); for (Map.Entry<String, ArrayList<Producao>> al : hasList.entrySet()) { linha += 4; r = s.createRow(linha); CreateCell(c, r, s, csTituloT, linha, linha, al.getKey(), 1, 22); linha++; linha++; r = s.createRow(linha); CreateCell(c, r, s, csTituloTabela, linha, linha, "Nr. Factura", 1, 2); CreateCell(c, r, s, csTituloTabela, linha, linha, "Nome do Segurado", 3, 7); CreateCell(c, r, s, csTituloTabela, linha, linha, "Premio", 8, 10); CreateCell(c, r, s, csTituloTabela, linha, linha, "Imposto 6%", 11, 13); CreateCell(c, r, s, csTituloTabela, linha, linha, "Imposto 5%", 14, 16); CreateCell(c, r, s, csTituloTabela, linha, linha, "FGA 2.6%", 17, 19); CreateCell(c, r, s, csTituloTabela, linha, linha, "TOTAL", 20, 22); for (Producao pro : al.getValue()) { linha++; r = s.createRow(linha); if (!pro.DATA.equals("SOMATORIO")) { CreateCell(c, r, s, csCorpoTabela, linha, linha, pro.NUMAPOLICE, 1, 2); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, pro.CLIENTESEGURO, 3, 7); CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.PREMIO + " " + pro.MOEDA, 8, 10); CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.IMPOSTOCONSUMO + " " + pro.MOEDA, 11, 13); CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.IMPOSTOSELO + " " + pro.MOEDA, 14, 16); CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.FGA + " " + pro.MOEDA, 17, 19); CreateCell(c, r, s, csCorpoTabelaR, linha, linha, pro.VALORTOTAL + " " + pro.MOEDA, 20, 22); } else { CreateCell(c, r, s, csRodapeTabela, linha, linha, "Total " + al.getKey(), 1, 7); CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.PREMIO + " " + pro.MOEDA, 8, 10); CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.IMPOSTOCONSUMO + " " + pro.MOEDA, 11, 13); CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.IMPOSTOSELO + " " + pro.MOEDA, 14, 16); CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.FGA + " " + pro.MOEDA, 17, 19); CreateCell(c, r, s, csRodapeTabelaR, linha, linha, pro.VALORTOTAL + " " + pro.MOEDA, 20, 22); } } } try (FileOutputStream out = new FileOutputStream(ff)) { wb.write(out); } return reString; } catch (IOException ex) { Logger.getLogger(ExportMapaProducaoExcel__.class.getName()).log(Level.SEVERE, null, ex); return null; } }
From source file:Export.ExportViagemSemanaExcel.java
public static void criarDoc(Date dataInicio, Date dateFim, String user, String nomeFuncinario) { Workbook wb = new HSSFWorkbook(); Font fTitulo = wb.createFont(); fTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); fTitulo.setFontHeightInPoints((short) 14); Font fTituloP = wb.createFont(); fTituloP.setBoldweight(Font.BOLDWEIGHT_BOLD); fTituloP.setFontHeightInPoints((short) 12); // fTituloP.setStrikeout(true); fTituloP.setUnderline(Font.U_SINGLE); Font fTituloTabela = wb.createFont(); fTituloTabela.setBoldweight(Font.BOLDWEIGHT_BOLD); fTituloTabela.setFontHeightInPoints((short) 8); Font fCorpoTabela = wb.createFont(); fCorpoTabela.setBoldweight(Font.BOLDWEIGHT_NORMAL); fCorpoTabela.setFontHeightInPoints((short) 8.5); Font fRodapeTabela = wb.createFont(); fRodapeTabela.setBoldweight(Font.BOLDWEIGHT_BOLD); fRodapeTabela.setFontHeightInPoints((short) 8.5); Font fNormal = wb.createFont(); fNormal.setBoldweight(Font.BOLDWEIGHT_BOLD); fNormal.setFontHeightInPoints((short) 8.5); CellStyle csTitulo = wb.createCellStyle(); csTitulo.setFont(fTitulo);/*from w w w .j av a2 s. c o m*/ csTitulo.setAlignment((short) 1); csTitulo.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); csTitulo.setBorderBottom((short) 0); csTitulo.setBorderTop((short) 0); csTitulo.setBorderRight((short) 0); csTitulo.setBorderLeft((short) 0); csTitulo.setWrapText(true); CellStyle csTituloP = wb.createCellStyle(); csTituloP.setFont(fTituloP); csTituloP.setAlignment((short) 1); csTituloP.setVerticalAlignment((short) 1); csTituloP.setBorderBottom((short) 0); csTituloP.setBorderTop((short) 0); csTituloP.setBorderRight((short) 0); csTituloP.setBorderLeft((short) 0); csTituloP.setWrapText(true); CellStyle csTituloT = wb.createCellStyle(); csTituloT.setFont(fTituloP); csTituloT.setAlignment((short) 1); csTituloT.setVerticalAlignment((short) 1); csTituloT.setBorderBottom((short) 0); csTituloT.setBorderTop((short) 0); csTituloT.setBorderRight((short) 0); csTituloT.setBorderLeft((short) 0); csTituloT.setWrapText(true); CellStyle csTituloTabela = wb.createCellStyle(); csTituloTabela.setFont(fTituloTabela); csTituloTabela.setAlignment(CellStyle.ALIGN_CENTER); csTituloTabela.setVerticalAlignment((short) 2); csTituloTabela.setBorderBottom((short) 2); csTituloTabela.setBorderTop((short) 2); csTituloTabela.setBorderRight((short) 2); csTituloTabela.setBorderLeft((short) 2); csTituloTabela.setWrapText(true); CellStyle csTituloTabelaNBorder = wb.createCellStyle(); csTituloTabelaNBorder.setFont(fTituloTabela); csTituloTabelaNBorder.setAlignment(CellStyle.ALIGN_CENTER); csTituloTabelaNBorder.setVerticalAlignment((short) 2); csTituloTabelaNBorder.setBorderBottom((short) 2); csTituloTabelaNBorder.setBorderTop((short) 2); csTituloTabelaNBorder.setBorderRight((short) 2); csTituloTabelaNBorder.setBorderLeft((short) 2); csTituloTabelaNBorder.setWrapText(true); CellStyle csCorpoTabela = wb.createCellStyle(); csCorpoTabela.setFont(fCorpoTabela); csCorpoTabela.setAlignment((short) 2); csCorpoTabela.setVerticalAlignment((short) 1); csCorpoTabela.setBorderBottom((short) 1); csCorpoTabela.setBorderTop((short) 1); csCorpoTabela.setBorderRight((short) 1); csCorpoTabela.setBorderLeft((short) 1); csCorpoTabela.setWrapText(true); CellStyle csCorpoTabelaR = wb.createCellStyle(); csCorpoTabelaR.setFont(fCorpoTabela); csCorpoTabelaR.setAlignment(CellStyle.ALIGN_RIGHT); csCorpoTabelaR.setVerticalAlignment((short) 1); csCorpoTabelaR.setBorderBottom((short) 1); csCorpoTabelaR.setBorderTop((short) 1); csCorpoTabelaR.setBorderRight((short) 1); csCorpoTabelaR.setBorderLeft((short) 1); csCorpoTabelaR.setWrapText(true); CellStyle csCorpoTabelaL = wb.createCellStyle(); csCorpoTabelaL.setFont(fCorpoTabela); csCorpoTabelaL.setAlignment(CellStyle.ALIGN_LEFT); csCorpoTabelaL.setVerticalAlignment((short) 1); csCorpoTabelaL.setBorderBottom((short) 1); csCorpoTabelaL.setBorderTop((short) 1); csCorpoTabelaL.setBorderRight((short) 1); csCorpoTabelaL.setBorderLeft((short) 1); csCorpoTabelaL.setWrapText(true); CellStyle csRodapeTabela = wb.createCellStyle(); csRodapeTabela.setFont(fRodapeTabela); csRodapeTabela.setAlignment((short) 1); csRodapeTabela.setVerticalAlignment((short) 2); csRodapeTabela.setBorderBottom((short) 2); csRodapeTabela.setBorderTop((short) 2); csRodapeTabela.setBorderRight((short) 2); csRodapeTabela.setBorderLeft((short) 2); csRodapeTabela.setWrapText(true); CellStyle csRodapeTabelaR = wb.createCellStyle(); csRodapeTabelaR.setFont(fRodapeTabela); csRodapeTabelaR.setAlignment(CellStyle.ALIGN_RIGHT); csRodapeTabelaR.setVerticalAlignment((short) 2); csRodapeTabelaR.setBorderBottom((short) 2); csRodapeTabelaR.setBorderTop((short) 2); csRodapeTabelaR.setBorderRight((short) 2); csRodapeTabelaR.setBorderLeft((short) 2); csRodapeTabelaR.setWrapText(true); CellStyle csNomal = wb.createCellStyle(); csNomal.setFont(fCorpoTabela); csNomal.setAlignment((short) 1); csNomal.setVerticalAlignment((short) 1); csNomal.setBorderBottom((short) 0); csNomal.setBorderTop((short) 0); csNomal.setBorderRight((short) 0); csNomal.setBorderLeft((short) 0); csNomal.setWrapText(true); OutputStream outputStraem; try { SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy hh.mm.ss"); SimpleDateFormat sdfTitile = new SimpleDateFormat("dd-MM-yyyy"); File ff = new File(ConfigDoc.Fontes.getDiretorio() + "/" + user + "/Seguro Viagem/"); ff.mkdirs(); String Ddata = sdf.format(new Date()); ff = new File(ff.getAbsoluteFile() + "/" + "Export Mapa Viagem Semanal " + Ddata + ".xls"); String reString = "../Documentos/" + user + "/Seguro Viagem/" + "Export Mapa Viagem Semanal " + Ddata + ".xls"; outputStraem = new FileOutputStream(ff); int linha = 0; Sheet s = wb.createSheet("RELATORIO SEMANAL"); Row r = s.createRow(linha); Cell c = r.createCell(2); createCellM(c, r, s, csTitulo, linha, linha + 3, ConfigDoc.Empresa.NOME, 1, 22); linha += 4; r = s.createRow(linha); createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.ENDERECO, 1, 22); linha++; r = s.createRow(linha); createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.CAIXAPOSTAL, 1, 22); linha++; r = s.createRow(linha); createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.TELEFAX + " " + ConfigDoc.Empresa.EMAIL, 1, 22); linha++; r = s.createRow(linha); createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.SOCIEDADE, 1, 22); linha += 3; r = s.createRow(linha); createCellM(c, r, s, csTituloTabelaNBorder, linha, linha + 1, "RELATORIO SEMANAL NO. " + "" + "\n" + ((dataInicio != null) ? sdfTitile.format(dataInicio) + " - " : "") + ((dateFim != null) ? sdfTitile.format(dateFim) : ""), 1, 10); linha += 3; r = s.createRow(linha); c = r.createCell(2); createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(0), 1, 4); //1 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(1), 2, 6); //2 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(2), 3, 6); //1 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(3), 4, 6); //1 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(4), 5, 6); //2 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(5), 6, 20); //3 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(6), 7, 4); //1 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(7), 8, 4); //1 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(8), 9, 8); //2 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(9), 10, 8); //1 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(10), 11, 6); //2 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(11), 12, 6); //2 createCell(c, r, s, csTituloTabela, linha, linha + 1, titileTable(12), 13, 6); //2 dataViagem(dataInicio, dateFim); float premiototal = 0; linha++; for (HashMap<String, Object> data : hasList) { linha++; r = s.createRow(linha); c = r.createCell(2); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(DATA)), 1, 4); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(NUMEROAPOLICE)), 2, 6); createCell(c, r, s, csCorpoTabelaL, linha, linha, ConfigDoc.toFormat(toString(data.get(INICIO)), "dd-MM-yyyy", "yyyy-MM-dd"), 3, 6); createCell(c, r, s, csCorpoTabelaL, linha, linha, ConfigDoc.toFormat(toString(data.get(FIM)), "dd-MM-yyyy", "yyyy-MM-dd"), 4, 6); premiototal += toFloat(data.get(PREMIO)); createCell(c, r, s, csCorpoTabelaR, linha, linha, ConfigDoc.toMoeda(toFloat(data.get(PREMIO)), ""), 5, 6); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(CLIENTE)), 6, 20); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(DATANASCIMENTO)), 7, 4); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(TELEFONE)), 8, 4); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(ENDERECO)), 9, 8); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(LOCALNASCIMENTO)), 10, 8); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(PAISDESTINO)), 11, 6); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(CIDADEDESTINO)), 12, 6); createCell(c, r, s, csCorpoTabelaL, linha, linha, toString(data.get(ZONADESTINO)), 13, 6); } linha++; r = s.createRow(linha); c = r.createCell(2); createCellM(c, r, s, csRodapeTabela, linha, linha, "AL AMOUNT..........................................", 1, 5); createCellM(c, r, s, csRodapeTabelaR, linha, linha, ConfigDoc.toMoeda(premiototal, ""), 6, 7); createCellM(c, r, s, csRodapeTabela, linha, linha, " ", 8, 13); try (FileOutputStream out = new FileOutputStream(ff)) { wb.write(out); } catch (IOException ex) { Logger.getLogger(GenericExcel.class.getName()).log(Level.SEVERE, null, ex); } RequestContext.getCurrentInstance().execute("openAllDocument('" + reString + "')"); } catch (FileNotFoundException ex) { Logger.getLogger(ExportViagemSemanaExcel.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Export.GenericExcel.java
/** * * @param user// w w w . ja v a 2s . c om * @param nomeDoc * @param titleDoc * @param rs * @param paramFilter * @return */ public static String createDoc(String user, String nomeDoc, String titleDoc, DataTableControl rs, int paramFilter) { OutputStream outputStraem = null; try { i = 0; SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy hh'.'mm'.'ss"); File ff = new File(ConfigDoc.Fontes.getDiretorio() + "/" + user + "/Relatorio"); ff.mkdirs(); String Ddata = sdf1.format(new Date()); ff = new File(ff.getAbsoluteFile() + "/" + nomeDoc + " " + Ddata + ".xls"); outputStraem = new FileOutputStream(ff); reString = "../Documentos/" + user + "/Relatorio/" + nomeDoc + " " + Ddata + ".xls"; getMap(rs, paramFilter); float[] colun = createPerncetage(list, paramFilter); Workbook wb = new HSSFWorkbook(); Font fTitulo = wb.createFont(); fTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); fTitulo.setFontHeightInPoints((short) 14); Font fTituloP = wb.createFont(); fTituloP.setBoldweight(Font.BOLDWEIGHT_BOLD); fTituloP.setFontHeightInPoints((short) 12); fTituloP.setUnderline(Font.U_SINGLE); Font fTituloTabela = wb.createFont(); fTituloTabela.setBoldweight(Font.BOLDWEIGHT_BOLD); fTituloTabela.setFontHeightInPoints((short) 8); Font fCorpoTabela = wb.createFont(); fCorpoTabela.setBoldweight(Font.BOLDWEIGHT_NORMAL); fCorpoTabela.setFontHeightInPoints((short) 8.5); Font fRodapeTabela = wb.createFont(); fRodapeTabela.setBoldweight(Font.BOLDWEIGHT_BOLD); fRodapeTabela.setFontHeightInPoints((short) 8.5); Font fNormal = wb.createFont(); fNormal.setBoldweight(Font.BOLDWEIGHT_BOLD); fNormal.setFontHeightInPoints((short) 8.5); CellStyle csTitulo = wb.createCellStyle(); csTitulo.setFont(fTitulo); csTitulo.setAlignment((short) 1); csTitulo.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); csTitulo.setBorderBottom((short) 0); csTitulo.setBorderTop((short) 0); csTitulo.setBorderRight((short) 0); csTitulo.setBorderLeft((short) 0); csTitulo.setWrapText(true); CellStyle csTituloP = wb.createCellStyle(); csTituloP.setFont(fTituloP); csTituloP.setAlignment((short) 1); csTituloP.setVerticalAlignment((short) 1); csTituloP.setBorderBottom((short) 0); csTituloP.setBorderTop((short) 0); csTituloP.setBorderRight((short) 0); csTituloP.setBorderLeft((short) 0); csTituloP.setWrapText(true); CellStyle csTituloT = wb.createCellStyle(); csTituloT.setFont(fTituloP); csTituloT.setAlignment((short) 1); csTituloT.setVerticalAlignment((short) 1); csTituloT.setBorderBottom((short) 0); csTituloT.setBorderTop((short) 0); csTituloT.setBorderRight((short) 0); csTituloT.setBorderLeft((short) 0); csTituloT.setWrapText(true); CellStyle csTituloTabela = wb.createCellStyle(); csTituloTabela.setFont(fTituloTabela); csTituloTabela.setAlignment(CellStyle.ALIGN_CENTER); csTituloTabela.setVerticalAlignment((short) 2); csTituloTabela.setBorderBottom((short) 2); csTituloTabela.setBorderTop((short) 2); csTituloTabela.setBorderRight((short) 2); csTituloTabela.setBorderLeft((short) 2); csTituloTabela.setWrapText(true); CellStyle csTituloTabelaNBorder = wb.createCellStyle(); csTituloTabelaNBorder.setFont(fTituloTabela); csTituloTabelaNBorder.setAlignment(CellStyle.ALIGN_CENTER); csTituloTabelaNBorder.setVerticalAlignment((short) 2); csTituloTabelaNBorder.setBorderBottom((short) 2); csTituloTabelaNBorder.setBorderTop((short) 2); csTituloTabelaNBorder.setBorderRight((short) 2); csTituloTabelaNBorder.setBorderLeft((short) 2); csTituloTabelaNBorder.setWrapText(true); CellStyle csCorpoTabela = wb.createCellStyle(); csCorpoTabela.setFont(fCorpoTabela); csCorpoTabela.setAlignment((short) 2); csCorpoTabela.setVerticalAlignment((short) 1); csCorpoTabela.setBorderBottom((short) 1); csCorpoTabela.setBorderTop((short) 1); csCorpoTabela.setBorderRight((short) 1); csCorpoTabela.setBorderLeft((short) 1); csCorpoTabela.setWrapText(true); CellStyle csCorpoTabelaR = wb.createCellStyle(); csCorpoTabelaR.setFont(fCorpoTabela); csCorpoTabelaR.setAlignment(CellStyle.ALIGN_RIGHT); csCorpoTabelaR.setVerticalAlignment((short) 1); csCorpoTabelaR.setBorderBottom((short) 1); csCorpoTabelaR.setBorderTop((short) 1); csCorpoTabelaR.setBorderRight((short) 1); csCorpoTabelaR.setBorderLeft((short) 1); csCorpoTabelaR.setWrapText(true); CellStyle csCorpoTabelaL = wb.createCellStyle(); csCorpoTabelaL.setFont(fCorpoTabela); csCorpoTabelaL.setAlignment(CellStyle.ALIGN_LEFT); csCorpoTabelaL.setVerticalAlignment((short) 1); csCorpoTabelaL.setBorderBottom((short) 1); csCorpoTabelaL.setBorderTop((short) 1); csCorpoTabelaL.setBorderRight((short) 1); csCorpoTabelaL.setBorderLeft((short) 1); csCorpoTabelaL.setWrapText(true); CellStyle csRodapeTabelaL = wb.createCellStyle(); csRodapeTabelaL.setFont(fRodapeTabela); csRodapeTabelaL.setAlignment(CellStyle.ALIGN_CENTER); csRodapeTabelaL.setVerticalAlignment((short) 2); csRodapeTabelaL.setBorderBottom((short) 2); csRodapeTabelaL.setBorderTop((short) 2); csRodapeTabelaL.setBorderRight((short) 2); csRodapeTabelaL.setBorderLeft((short) 2); csRodapeTabelaL.setWrapText(true); CellStyle csRodapeTabela = wb.createCellStyle(); csRodapeTabela.setFont(fRodapeTabela); csRodapeTabela.setAlignment(CellStyle.ALIGN_CENTER); csRodapeTabela.setVerticalAlignment((short) 2); csRodapeTabela.setBorderBottom((short) 2); csRodapeTabela.setBorderTop((short) 2); csRodapeTabela.setBorderRight((short) 2); csRodapeTabela.setBorderLeft((short) 2); csRodapeTabela.setWrapText(true); CellStyle csRodapeTabelaR = wb.createCellStyle(); csRodapeTabelaR.setFont(fRodapeTabela); csRodapeTabelaR.setAlignment(CellStyle.ALIGN_RIGHT); csRodapeTabelaR.setVerticalAlignment((short) 2); csRodapeTabelaR.setBorderBottom((short) 2); csRodapeTabelaR.setBorderTop((short) 2); csRodapeTabelaR.setBorderRight((short) 2); csRodapeTabelaR.setBorderLeft((short) 2); csRodapeTabelaR.setWrapText(true); CellStyle csNomal = wb.createCellStyle(); csNomal.setFont(fCorpoTabela); csNomal.setAlignment((short) 1); csNomal.setVerticalAlignment((short) 1); csNomal.setBorderBottom((short) 0); csNomal.setBorderTop((short) 0); csNomal.setBorderRight((short) 0); csNomal.setBorderLeft((short) 0); csNomal.setWrapText(true); Sheet s = wb.createSheet(titleDoc); linha = 0; Row r = s.createRow(linha); Cell c = r.createCell(2); createCellM(c, r, s, csTitulo, linha, linha + 3, ConfigDoc.Empresa.NOME, 1, 22); linha += 4; r = s.createRow(linha); createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.ENDERECO, 1, 22); linha++; r = s.createRow(linha); createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.CAIXAPOSTAL, 1, 22); linha++; r = s.createRow(linha); createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.TELEFAX + " " + ConfigDoc.Empresa.EMAIL, 1, 22); linha++; r = s.createRow(linha); createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.SOCIEDADE, 1, 22); linha += 3; r = s.createRow(linha); SimpleDateFormat format = new SimpleDateFormat("dd 'de' MMMM 'de' yyyy", new Locale("pt", "BR")); createCellM(c, r, s, csTituloT, linha, linha, titleDoc.toUpperCase() + ((dF != null && dI != null) ? (" de " + format.format(dI) + " " + format.format(dF)).toUpperCase() : ""), 1, 22); linha += 2; if (paramFilter < 0) { for (Object[] emap : list) { k = 0; r = s.createRow(linha); for (int j = 0; j < emap.length; j++) { if (j != paramFilterOculta) { if (i == 0) { lista_titulo_table = emap; csCorpoTabela.setFillBackgroundColor(HSSFColor.BLUE.index); createCell(c, r, s, csTituloTabela, linha, linha, toString(emap[j]), k + 1, toInt(colun[k])); k++; } else { csCorpoTabelaL.setFillBackgroundColor( ((i % 2) == 0) ? HSSFColor.WHITE.index : HSSFColor.GREY_25_PERCENT.index); createCell(c, r, s, ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT) ? csCorpoTabelaR : ((alignment.get(j) == Alignment.CENTER) ? csCorpoTabela : csCorpoTabelaL)) : csCorpoTabelaL), linha, linha, toString(emap[j]), k + 1, toInt(colun[k])); k++; } } } i++; linha++; } for (Map.Entry<String, ArrayList<Object[]>> entrySet : mapTotal.entrySet()) { for (Object[] emapT : entrySet.getValue()) { k = 0; r = s.createRow(linha); for (int j = 0; j < emapT.length; j++) { if (j != paramFilterOculta) { csCorpoTabelaL.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); createCell(c, r, s, ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT) ? csRodapeTabelaR : ((alignment.get(j) == Alignment.CENTER) ? csRodapeTabelaR : csRodapeTabelaL)) : csRodapeTabelaL), linha, linha, ((j == 0) ? "TOTAL" : toString(emapT[j])), k + 1, toInt(colun[k])); k++; } } } } } else { int t = 0; for (Map.Entry<String, ArrayList<Object[]>> lista : map.entrySet()) { r = s.createRow(linha); csTituloTabela.setFillBackgroundColor(HSSFColor.WHITE.index); createCellM(c, r, s, csTituloTabelaNBorder, linha, linha, toString(lista.getKey()), 1, 7); linha += 2; for (Object[] emap : lista.getValue()) { k = 0; r = s.createRow(linha); for (int j = 0; j < emap.length; j++) { if (j != paramFilterOculta) { if (i == 0) { lista_titulo_table = emap; csTituloTabela.setFillBackgroundColor(HSSFColor.BLUE.index); createCell(c, r, s, csTituloTabela, linha, linha, toString(emap[j]), k + 1, toInt(colun[k])); k++; } else { csCorpoTabelaL.setFillBackgroundColor(((i % 2) == 0) ? HSSFColor.WHITE.index : HSSFColor.GREY_25_PERCENT.index); createCell(c, r, s, ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT) ? csCorpoTabelaR : ((alignment.get(j) == Alignment.CENTER) ? csCorpoTabela : csCorpoTabelaL)) : csCorpoTabelaL), linha, linha, toString(emap[j]), k + 1, toInt(colun[k])); k++; } } } i++; linha++; } if (mapTotal.containsKey(lista.getKey())) { for (Object[] emapT : mapTotal.get(lista.getKey())) { k = 0; r = s.createRow(linha); for (int j = 0; j < emapT.length; j++) { if (j != paramFilterOculta) { createCell(c, r, s, ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT) ? csRodapeTabelaR : ((alignment.get(j) == Alignment.CENTER) ? csRodapeTabela : csRodapeTabelaL)) : csRodapeTabelaL), linha, linha, ((j == 0) ? "TOTAL" : toString(emapT[j])), k + 1, toInt(colun[k])); k++; } } } } t++; i = 0; linha += 3; if (t == map.size() && paramFilter > -1) { k = 0; r = s.createRow(linha); for (int j = 0; j < lista_titulo_table.length; j++) { if (j != paramFilterOculta) { csTituloTabela.setFillBackgroundColor(HSSFColor.BLUE.index); createCell(c, r, s, csTituloTabela, linha, linha, toString(lista_titulo_table[j]), k + 1, toInt(colun[k])); k++; } } linha++; Double[] total_total = new Double[lista_titulo_table.length]; for (Map.Entry<String, ArrayList<Object[]>> lista_for_total : map.entrySet()) { String key = lista_for_total.getKey(); for (Object[] emapT : mapTotal.get(key)) { k = 0; r = s.createRow(linha); for (int j = 0; j < emapT.length; j++) { if (j != paramFilterOculta) { if (Moeda.unFormat(toString(emapT[j]).replaceAll(" ", "") .replaceAll(",", ".").replaceAll("STD", "")) != -1) { Double v = ((total_total[j] == null) ? 0.0 : total_total[j]); total_total[j] = Moeda.unFormat(toString(emapT[j]).replaceAll(" ", "") .replaceAll(",", ".").replaceAll("STD", "")) + v; } createCell(c, r, s, ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT) ? csRodapeTabelaR : ((alignment.get(j) == Alignment.CENTER) ? csRodapeTabela : csRodapeTabelaL)) : csRodapeTabelaL), linha, linha, ((j == 0) ? "TOTAL " + key.toUpperCase() : toString(emapT[j])), k + 1, toInt(colun[k])); k++; } } } linha++; } k = 0; r = s.createRow(linha); for (int j = 0; j < total_total.length; j++) { if (j != paramFilterOculta) { csTituloTabela.setFillBackgroundColor(HSSFColor.BLUE.index); createCell(c, r, s, ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT) ? csRodapeTabelaR : ((alignment.get(j) == Alignment.CENTER) ? csRodapeTabela : csRodapeTabelaL)) : csRodapeTabelaL), linha, linha, (total_total[j] != null) ? Moeda.format_are(total_total[j]) : " ", k + 1, toInt(colun[k])); k++; } } } } } try (FileOutputStream out = new FileOutputStream(ff)) { wb.write(out); } catch (IOException ex) { Logger.getLogger(GenericExcel.class.getName()).log(Level.SEVERE, null, ex); } RequestContext.getCurrentInstance().execute("openAllDocument('" + reString + "')"); no = false; nomeNo = ""; dI = null; dF = null; paramFilterOculta = -1; removeItem = new int[] {}; renameItem = new HashMap<>(); alignment = new HashMap<>(); valoresTotal = new String[] {}; arrValoresTotal = new int[] {}; return reString; } catch (FileNotFoundException ex) { Logger.getLogger(GenericExcel.class.getName()).log(Level.SEVERE, null, ex); return reString; } }