List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont
void setFont(Font font);
From source file:Export.ExportMapaProducaoExcel__.java
public String criarDoc(String user, Date incio, Date fim) { try {// w ww .ja v a 2s . c o m 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); 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);//from w w w . j av a 2s . co m 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 .j a v a2 s . c o m*/ * @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; } }
From source file:Export.ListaVeiculo.java
public static void criarDocExcel(List<Veiculo> ls, String user) { try {// w ww .j av a 2 s . c om 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() + "/" + "Lista de Veiculos" + " " + Ddata + ".xls"); FileOutputStream outputStraem = new FileOutputStream(ff); String reString = "../Documentos/" + user + "/Relatorio/" + "Lista de Veiculos" + " " + Ddata + ".xls"; Workbook wb = new HSSFWorkbook(); org.apache.poi.ss.usermodel.Font fTitulo = wb.createFont(); fTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fTitulo.setFontHeightInPoints((short) 22); org.apache.poi.ss.usermodel.Font fTituloP = wb.createFont(); fTituloP.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fTituloP.setFontHeightInPoints((short) 15); // fTituloP.setStrikeout(true); fTituloP.setUnderline(org.apache.poi.ss.usermodel.Font.U_SINGLE); org.apache.poi.ss.usermodel.Font fTituloTabela = wb.createFont(); fTituloTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fTituloTabela.setFontHeightInPoints((short) 11); org.apache.poi.ss.usermodel.Font fCorpoTabela = wb.createFont(); fCorpoTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_NORMAL); fCorpoTabela.setFontHeightInPoints((short) 11.5); org.apache.poi.ss.usermodel.Font fRodapeTabela = wb.createFont(); fRodapeTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fRodapeTabela.setFontHeightInPoints((short) 11.5); org.apache.poi.ss.usermodel.Font fNormal = wb.createFont(); fNormal.setBoldweight(org.apache.poi.ss.usermodel.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); Sheet s = wb.createSheet("Lista de Veiculos"); short 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); CreateCellM(c, r, s, csTituloT, linha, linha, "Lista de Veiculos".toUpperCase(), 1, 22); linha += 2; csCorpoTabela.setFillBackgroundColor(HSSFColor.BLUE.index); r = s.createRow(linha); CreateCell(c, r, s, csTituloTabela, linha, linha, getList(0), 1, 8); CreateCell(c, r, s, csTituloTabela, linha, linha, getList(1), 2, 18); CreateCell(c, r, s, csTituloTabela, linha, linha, getList(2), 3, 18); CreateCell(c, r, s, csTituloTabela, linha, linha, getList(3), 4, 18); CreateCell(c, r, s, csTituloTabela, linha, linha, getList(4), 5, 18); CreateCell(c, r, s, csTituloTabela, linha, linha, getList(5), 6, 10); CreateCell(c, r, s, csTituloTabela, linha, linha, getList(6), 7, 10); CreateCell(c, r, s, csTituloTabela, linha, linha, getList(7), 8, 8); linha++; for (int i = 0; i < ls.size(); i++) { r = s.createRow(linha); csCorpoTabelaL.setFillBackgroundColor( ((i % 2) == 0) ? HSSFColor.WHITE.index : HSSFColor.GREY_25_PERCENT.index); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getNumeroMatricula(), 1, 8); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getMarca(), 2, 18); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getModelo(), 3, 18); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getNumMotor(), 4, 18); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getChassi(), 5, 18); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, (ls.get(i).getAnoFabrico() == null || ls.get(i).getAnoFabrico().equals("")) ? "" : Integer.valueOf(ls.get(i).getAnoFabrico()), 6, 10); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, (ls.get(i).getAnoCompra() == null || ls.get(i).getAnoCompra().equals("")) ? "" : Integer.valueOf(ls.get(i).getAnoCompra()), 7, 10); CreateCell(c, r, s, csCorpoTabelaL, linha, linha, Integer.valueOf(ls.get(i).getCapacidade()), 8, 8); linha++; } 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 (IOException ex) { Logger.getLogger(GenericExcel.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:export.notes.view.to.excel.ExcelWriter.java
License:Apache License
@SuppressWarnings("unchecked") public void createTableHeader(View view) throws NotesException { Vector<ViewColumn> columns = view.getColumns(); // offset column int offset = 0; for (int x = 0; x < columns.size(); x++) { ViewColumn column = columns.get(x); if (column.isConstant()) { offset++;/* www . j a v a2 s .c om*/ } else if (column.isFormula()) { // A column value (ViewEntry.getColumnValues()) is not // returned if it is determined by a constant. Check it. String formula = column.getFormula(); if (formula == null) { offset++; } else { // empty string formula = formula.replaceAll("\"", "").trim(); // some whitespaces if (StringUtils.isBlank(formula)) { offset++; } } } // hidden and icons columns not will be use if (!column.isHidden() && !column.isIcon() && !column.isConstant()) { String s = columns.get(x).getTitle(); if (s == null || "".equals(s)) { //$NON-NLS-1$ s = " "; //$NON-NLS-1$ } int position = x - offset; headers.put(position, column); ViewNavigator nav = view.createViewNav(); ViewEntry entry = nav.getFirst(); while (!entry.isDocument()) { entry = nav.getNext(); } createCellStyle(position, column, entry); } } // column indexes int idy = 0; // Generate column headings Cell c = null; if (sheet == null) { SimpleDateFormat sf = new SimpleDateFormat("dd.MM.yyyy HH:mm"); //$NON-NLS-1$ sheet = createSheet(Messages.ExportAction_10 + " " + sf.format(new Date())); } Row row = sheet.createRow(0); Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFont(fontBold); for (Entry<Integer, ViewColumn> entry : headers.entrySet()) { ViewColumn column = entry.getValue(); c = row.createCell(idy++); c.setCellValue(column.getTitle()); c.setCellStyle(cellStyle); } sheet.createFreezePane(0, 1, 0, 1); }
From source file:export.notes.view.to.excel.ExcelWriter.java
License:Apache License
private void createCellStyle(int position, ViewColumn column, ViewEntry entry) throws NotesException { CellStyle cellStyle = workbook.createCellStyle(); Font font = workbook.createFont(); if (column.isFontBold()) { font.setBoldweight(Font.BOLDWEIGHT_BOLD); }//from w w w. j a va 2 s . c o m font.setItalic(column.isFontItalic()); switch (column.getFontColor()) { case RichTextStyle.COLOR_BLACK: font.setColor(HSSFColor.BLACK.index); break; case RichTextStyle.COLOR_BLUE: font.setColor(HSSFColor.BLUE.index); break; case RichTextStyle.COLOR_CYAN: font.setColor(HSSFColor.CORAL.index); break; case RichTextStyle.COLOR_DARK_BLUE: font.setColor(HSSFColor.DARK_BLUE.index); break; case RichTextStyle.COLOR_DARK_CYAN: font.setColor(HSSFColor.DARK_GREEN.index); break; case RichTextStyle.COLOR_DARK_GREEN: font.setColor(HSSFColor.DARK_GREEN.index); break; case RichTextStyle.COLOR_DARK_MAGENTA: font.setColor(HSSFColor.VIOLET.index); break; case RichTextStyle.COLOR_DARK_RED: font.setColor(HSSFColor.DARK_RED.index); break; case RichTextStyle.COLOR_DARK_YELLOW: font.setColor(HSSFColor.DARK_YELLOW.index); break; case RichTextStyle.COLOR_GRAY: font.setColor(HSSFColor.GREY_80_PERCENT.index); break; case RichTextStyle.COLOR_GREEN: font.setColor(HSSFColor.GREEN.index); break; case RichTextStyle.COLOR_LIGHT_GRAY: font.setColor(HSSFColor.GREY_50_PERCENT.index); break; case RichTextStyle.COLOR_MAGENTA: font.setColor(HSSFColor.VIOLET.index); break; case RichTextStyle.COLOR_RED: font.setColor(HSSFColor.RED.index); break; case RichTextStyle.COLOR_WHITE: font.setColor(HSSFColor.BLACK.index); break; case RichTextStyle.COLOR_YELLOW: font.setColor(HSSFColor.YELLOW.index); break; default: break; } cellStyle.setFont(font); switch (column.getAlignment()) { case ViewColumn.ALIGN_CENTER: cellStyle.setAlignment(CellStyle.ALIGN_CENTER); break; case ViewColumn.ALIGN_LEFT: cellStyle.setAlignment(CellStyle.ALIGN_LEFT); break; case ViewColumn.ALIGN_RIGHT: cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); break; default: break; } @SuppressWarnings("unchecked") Vector<Object> values = entry.getColumnValues(); Object value = values.get(position); String name = value.getClass().getSimpleName(); short format = 0; if (name.contains("Double")) { //$NON-NLS-1$ XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat(); switch (column.getNumberFormat()) { case ViewColumn.FMT_CURRENCY: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(6)); break; case ViewColumn.FMT_FIXED: String zero = "0"; //$NON-NLS-1$ String fixedFormat = "#0"; //$NON-NLS-1$ int digits = column.getNumberDigits(); if (digits > 0) { String n = StringUtils.repeat(zero, digits); fixedFormat = fixedFormat + "." + n; } format = fmt.getFormat(fixedFormat); break; default: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(1)); break; } } else if (name.contains("DateTime")) { //$NON-NLS-1$ XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat(); switch (column.getTimeDateFmt()) { case ViewColumn.FMT_DATE: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe)); break; case ViewColumn.FMT_DATETIME: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0x16)); break; case ViewColumn.FMT_TIME: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0x15)); break; default: format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe)); break; } } cellStyle.setDataFormat(format); styles.add(cellStyle); }
From source file:facturasdiferidas.frmFacturasDif.java
private void ExportarExcel(ResultSet rs) throws IOException, SQLException { JFileChooser file = new JFileChooser(); FileNameExtensionFilter filtro = new FileNameExtensionFilter("Excel(*.XLSX)", "xlsx"); file.setFileFilter(filtro);/*from w w w . ja va2s . co m*/ int seleccion = file.showSaveDialog(this); if (seleccion == JFileChooser.CANCEL_OPTION) { return; } File guarda = file.getSelectedFile(); //file.set if (guarda != null) { // file.setFileFilter(filtro); /*guardamos el archivo y le damos el formato directamente, * si queremos que se guarde en formato doc lo definimos como .doc*/ rutaArchivo = guarda.getAbsolutePath() + ".xlsx"; // JOptionPane.showMessageDialog(null, // "Se creo el archivo... Generando informacin", // "Informacin en\n"+rutaArchivo,JOptionPane.INFORMATION_MESSAGE); } else { // file.setFileFilter(filtro); rutaArchivo = System.getProperty("user.home") + "/Kardex.xlsx"; // JOptionPane.showMessageDialog(null, // "Se creo el archivo... Generando informacin", // "Informacin en\n"+rutaArchivo,JOptionPane.INFORMATION_MESSAGE); } /*Se crea el objeto de tipo File con la ruta del archivo*/ archivoXLS = new File(rutaArchivo); /*Si el archivo existe se elimina*/ if (archivoXLS.exists()) archivoXLS.delete(); // try { /*Se crea el archivo*/ //archivoXLS. archivoXLS.createNewFile(); libro = new XSSFWorkbook(); archivo = new FileOutputStream(archivoXLS); //archivo.close(); CreationHelper createhelper = libro.getCreationHelper(); cellStyle = libro.createCellStyle(); cellStyle.setDataFormat(createhelper.createDataFormat().getFormat("dd/mm/yyyy")); fuente = libro.createFont(); fuente.setFontHeightInPoints((short) 10); fuente.setFontName("Calibri"); fuente.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); fuente.setBold(true); fuente3 = libro.createFont(); fuente3.setFontHeightInPoints((short) 8); fuente3.setFontName("Calibri"); fuente3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); fuente2 = libro.createFont(); fuente2.setFontHeightInPoints((short) 9); fuente2.setFontName("Calibri"); //fuente.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); CellStyle cellStyle2 = libro.createCellStyle(); cellStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); cellStyle2.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle2.setFont(fuente); cellStyle2.setWrapText(true); CellStyle cellStyle3 = libro.createCellStyle(); cellStyle3.setAlignment(XSSFCellStyle.ALIGN_LEFT); cellStyle3.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle3.setFont(fuente); CellStyle cellStyle4 = libro.createCellStyle(); // cellStyle4.setAlignment(XSSFCellStyle. ALIGN_LEFT); // cellStyle4.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle4.setFont(fuente); hoja = libro.createSheet("FacturasDiferidas"); //hoja. hoja.setDefaultRowHeightInPoints(12); Row fila = hoja.createRow(0); Cell celda = fila.createCell(0); celda.setCellValue("TIENDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(1); celda.setCellValue("TIPO_DOC"); celda.setCellStyle(cellStyle4); celda = fila.createCell(2); celda.setCellValue("DOC_DIFERIDO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(3); celda.setCellValue("FECHA_EMI"); celda.setCellStyle(cellStyle4); celda = fila.createCell(4); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(5); celda.setCellValue("NOMBRE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(6); celda.setCellValue("COD_PROD"); celda.setCellStyle(cellStyle4); celda = fila.createCell(7); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(8); celda.setCellValue("MONEDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(9); celda.setCellValue("PRECIO_UNITARIO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(10); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle4); celda = fila.createCell(11); celda.setCellValue("CANT_FACTURADA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(12); celda.setCellValue("SALDO_TOTAL_X_ATENDER"); celda.setCellStyle(cellStyle4); celda = fila.createCell(13); celda.setCellValue("CANTIDAD_ATENDIDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(14); celda.setCellValue("TIPO_DOC_A"); celda.setCellStyle(cellStyle4); celda = fila.createCell(15); celda.setCellValue("DOCUMENTO_ATIENDE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(16); celda.setCellValue("FECHA_GUIA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(17); celda.setCellValue("TIENDA_ATIENDE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(18); celda.setCellValue("ALMACEN_ATIENDE"); celda.setCellStyle(cellStyle4); f = 1; reloj = new Timer(delay, new EjecutarTarea2()); reloj.start(); // // // while (rs.next()){ // // } }
From source file:fi.thl.pivot.export.XlsxExporter.java
private CellStyle measureStyle(Workbook wb, int decimals) { if (decimals == 0) { return numberStyle; }//w w w . ja va2 s . c o m if (decimalStyles.containsKey(decimals)) { return decimalStyles.get(decimals); } CellStyle style = wb.createCellStyle(); String format = String.format("#,##0.%0" + decimals + "d", 0); style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(format)); style.setFont(valueFont); decimalStyles.put(decimals, style); return style; }
From source file:FILING.cboreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w w w. ja va2 s.com*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String District[]; String Year = ""; District = request.getParameterValues("District"); Year = request.getParameter("Year"); String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String distval = ""; int activeOVC = 0; int activeHH = 0; float activeovc = 0; float activehh = 0; float percent = 0; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_CENTER); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("CBO FILING TRACKER REPORT" + "( " + Year + ")"); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 4500); shet1.setColumnWidth(1, 8500); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5000); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5000); shet1.setColumnWidth(17, 4000); shet1.setColumnWidth(18, 4000); shet1.setColumnWidth(19, 4200); shet1.setColumnWidth(20, 4200); shet1.setColumnWidth(21, 4200); shet1.setColumnWidth(22, 4200); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(40); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell4 = rw4.createCell(3); cell1.setCellValue("DISTRICT"); cell1.setCellStyle(style); cell2.setCellValue("CBO"); cell2.setCellStyle(style); cell6 = rw6.createCell(0); cell6.setCellValue("Status"); cell6.setCellStyle(style); cell7 = rw6.createCell(1); cell7.setCellValue(""); cell7.setCellStyle(style); shet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); int rowcount = 3; int doccounter = 4; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; cell3 = rw4.createCell(2); cell3.setCellValue("ACTIVE OVC"); cell3.setCellStyle(style); cell3 = rw4.createCell(3); cell3.setCellValue("ACTIVE HH"); cell3.setCellStyle(style); cell6 = rw6.createCell(2); cell6.setCellValue(""); cell6.setCellStyle(stylex); cell6 = rw6.createCell(3); cell6.setCellValue(""); cell6.setCellStyle(stylex); ArrayList docidarray = new ArrayList(); String getdocname = "select * from ovcdocuments WHERE DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { docidarray.add(conn.rs3.getString(1)); System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); cell6 = rw6.createCell(doccounter1); cell6.setCellValue("Available"); cell6.setCellStyle(stylex); // cell5=rw6.createCell(doccounter1); // cell5.setCellValue("Not Available"); // cell5.setCellStyle(stylex); doccounter1++; System.out.println("mergecounter b4" + mergecounter); // shet1.addMergedRegion(new CellRangeAddress(2,2,2,3)); // shet1.addMergedRegion(new CellRangeAddress(2,2,4,5)); // shet1.addMergedRegion(new CellRangeAddress(2,2,6,7)); // shet1.addMergedRegion(new CellRangeAddress(2,2,8,9)); // shet1.addMergedRegion(new CellRangeAddress(2,2,10,11)); // shet1.addMergedRegion(new CellRangeAddress(2,2,12,13)); // shet1.addMergedRegion(new CellRangeAddress(2,2,14,15)); // shet1.addMergedRegion(new CellRangeAddress(2,2,16,17)); // shet1.addMergedRegion(new CellRangeAddress(2,2,18,19)); // shet1.addMergedRegion(new CellRangeAddress(2,2,20,21)); System.out.println("mergecounter after" + mergecounter); // mergecounter++; //shet1.addMergedRegion(new CellRangeAddress(2,2,doccounter1,doccounter1++)); } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; int countercopy = 4; HSSFRow rw5 = null; for (int j = 0; j < District.length; j++) { String getcboCOUNT = "select * from CBO where DistrictID='" + District[j] + "' Order by DistrictID "; conn.rs2 = conn.state2.executeQuery(getcboCOUNT); while (conn.rs2.next()) { counter++; rowcount++; rw5 = shet1.createRow(rowcount); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } cell2 = rw5.createCell(1); cell2.setCellValue(conn.rs2.getString(2)); cell2.setCellStyle(style_border); // cell5=rw5.createCell(rowcount); // cell6=rw5.createCell(rowcounter++); // cell5.setCellValue("x"); // cell6.setCellValue("y"); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); rw5.setHeightInPoints(25); // cell3=rw5.createCell(2); String getDistrict = "select * from District where DistrictID='" + District[j] + "' Order by DistrictID "; conn.rs4 = conn.state4.executeQuery(getDistrict); while (conn.rs4.next()) { districtname = conn.rs4.getString("District"); cell1 = rw5.createCell(0); cell1.setCellValue(districtname); cell1.setCellStyle(style_border1); } doccounter = 4; System.out.println("doccount " + doccounter); String getdocname1 = "select * from ovcdocuments"; // conn.rs3 = conn.state.executeQuery(getdocname1); // while(conn.rs3.next()){ String getData = "select " + "SUM(CASE WHEN ovcfiling.value='1' THEN 1 ELSE 0 END) AS COUNT1, " + "SUM(CASE WHEN ovcfiling.value='0' THEN 1 ELSE 0 END) AS COUNT0," + "Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District," + "count(Clientdetails.OVCID)," + "count(DISTINCT(Clientdetails.HouseHoldheadID))" + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + District[j] + "' and " + "Clientdetails.Cbo='" + conn.rs2.getString(1) + "' and Clientdetails.Exited='1' and " + " (Year='" + Year + "' OR Year='') AND Clientdetails.OVCID = ovcfiling.ovcid " + "group by ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District Order by ovcfiling.ovcdocid,District "; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); value0 = conn.rs.getInt(2); cboid = conn.rs.getString(3); doc = conn.rs.getString(4); Districtid = conn.rs.getString(5); activeOVC = conn.rs.getInt(6); activeHH = conn.rs.getInt(7); int a = 0; for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i) + " " + doc); if (docidarray.get(i).equals(doc)) { System.out.println(doc + "lll" + docidarray.get(i)); int cellcount = i + 2; activeovc = activeOVC; activehh = activeHH; if (doc.equals("8")) { percent = value1 / activehh * 100; } else { percent = value1 / activeovc * 100; } cell7 = rw5.createCell(2); cell8 = rw5.createCell(3); cell5 = rw5.createCell(doccounter); // cell6=rw5.createCell(doccounter); cell5.setCellValue(Math.round(percent) + "%"); // cell6.setCellValue(value0); //FOR ACTICE OVCs cell7.setCellValue(activeOVC); cell7.setCellStyle(style_border); //FOR ACTICE hhs cell8.setCellValue(activeHH); cell8.setCellStyle(style_border); cell5.setCellStyle(style_border); cell6.setCellStyle(style_border); System.out.println("****a " + doc + " " + doccounter); if (doc.equals("2")) { // System.out.println("****i "+doc +" "+doccounter); doccounter++; // doccounter++; System.out.println("am in2"); System.out.println("****b " + doc + " " + doccounter); } if (doc.equals("3")) { doccounter--; // doccounter--; // doccounter--; System.out.println("****f " + doc + " " + doccounter); cell5 = rw5.createCell(doccounter++); cell5.setCellStyle(style_border); cell5.setCellValue(Math.round(percent) + "%"); cell6 = rw5.createCell(doccounter); // cell6.setCellStyle(style_border); // cell6.setCellValue(value0); System.out.println("****b " + doc + " " + doccounter); doccounter--; } // else if(!doc.equals("4") && !docidarray.get(i).equals("4")){ // doccounter++; // doccounter++; // System.out.println("****c "+doc +" "+doccounter); // } doccounter++; } } } doccounter = 2; // String getcbo= "select * from CBO where cboid='"+cboid+"'"; // conn.rs2 = conn.state2.executeQuery(getcbo); // while(conn.rs2.next()){ // // cell2=rw5.createCell(1); // cell2.setCellValue(conn.rs2.getString(2)); // // System.out.println("rowcount "+rowcount ); // // } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); } //} // end of while loop if (distval.equals("")) { // totalvalue= countercopy+counter; System.out.println(countercopy + " counter " + counter + " " + rowcount); distval = districtname; System.out.println(countercopy + " nnnn " + counter + " " + rowcount); if (counter > countercopy) { shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount, 0, 0)); countercopy = rowcount; //cell1.setCellValue(districtname); } System.out.println(countercopy + " nnn " + counter + " " + rowcount + " " + distval); } // cell1.setCellValue(districtname); if (!distval.equals(districtname) && !distval.equals("")) { distval = districtname; // cell1.setCellValue(districtname); shet1.addMergedRegion(new CellRangeAddress(countercopy + 1, rowcount, 0, 0)); countercopy = rowcount; // System.out.println(counter + "@@@@1 " + rowcount + "__" + countercopy); System.out.println(distval + "@@@@1 " + districtname); } System.out.println(counter + "@@@@ " + rowcount); //shet1.addMergedRegion(new CellRangeAddress(counter,rowcount,0,0)); System.out.println(distval + "@@@@ " + districtname); } // end of for loop // int totalvalue=countercopy+counter; // System.out.println(counter+" hhhh "+countercopy); // if(counter>countercopy){ // shet1.addMergedRegion(new CellRangeAddress(countercopy,totalvalue-1,0,0)); // // countercopy=counter; // } // System.out.println("aaaaaannnn "+districtname+"__"+ cboname +"____"+ doc +"___"+value1 +"__"+value0 ); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=CBO_FILING_TRACKER_REPORT_FOR_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }
From source file:FILING.childdetailsreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w w w .ja v a2 s . com * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String District = ""; District = request.getParameter("District"); String Year = ""; Year = request.getParameter("Year"); String chw = ""; String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String chwval = ""; String docid = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); int cbocount = 3; //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_CENTER); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("OVC DOCUMENT DETAILS REPORT"); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 9000); shet1.setColumnWidth(1, 9000); shet1.setColumnWidth(2, 9000); shet1.setColumnWidth(3, 9000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(50); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell0, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell4 = rw4.createCell(3); cell0.setCellValue("CBO"); cell0.setCellStyle(style); cell1.setCellValue("CHW"); cell1.setCellStyle(style); cell2.setCellValue("OVCID"); cell2.setCellStyle(style); cell4.setCellValue("FULLNAME"); cell4.setCellStyle(style); ArrayList docidarray = new ArrayList(); int rowcount = 3; int doccounter = 3; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; String getdocname = "select * from ovcdocuments WHERE DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); docidarray.add(conn.rs3.getString(1)); doccounter1++; } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; int countercopy = 3; int countercopy1 = 3; HSSFRow rw5 = null; String getcboCOUNT = "select * from CBO where DistrictID='" + District + "' Order by DistrictID "; conn.rs2 = conn.state2.executeQuery(getcboCOUNT); while (conn.rs2.next()) { counter++; System.out.println("bb b " + conn.rs2.getString(2)); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); System.out.println("doccount " + doccounter); String getData = "select " + "ovcfiling.value,Clientdetails.FirstName, Middlename,SurName,ovcfiling.OVCID" + " , Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District,Clientdetails.Volunteerid " + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + District + "' and Clientdetails.Cbo='" + conn.rs2.getString(1) + "' " + "and Clientdetails.OVCID = ovcfiling.ovcid and (Year='" + Year + "' OR Year='') " + "group by Clientdetails.FirstName, Middlename,Surname,ovcfiling.OVCID,Clientdetails.Volunteerid,ovcfiling.value,ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District " + "Order by Clientdetails.Volunteerid,ovcfiling.OVCID,ovcfiling.ovcdocid"; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); FirstName = conn.rs.getString(2); MiddleName = conn.rs.getString(3); Surname = conn.rs.getString(4); ovcid1 = conn.rs.getString(5); // String getcbo= "select * from CBO where CBOID='"+cboid+"' "; // System.out.println("aaa "+getcbo); // conn.rs_1= conn.state4.executeQuery(getcbo); // while(conn.rs_1.next()){ //// // cbocount++; // cboname=conn.rs_1.getString("CBO"); // System.out.println("aaab "+cboname); // } cboname = conn.rs2.getString(2); cboid = conn.rs.getString(6); docid = conn.rs.getString(7); String getchw = "select * from CHW where CHWID='" + conn.rs.getString(9) + "' "; conn.rs3 = conn.state3.executeQuery(getchw); while (conn.rs3.next()) { chw = conn.rs3.getString("FirstName") + " " + conn.rs3.getString("MiddleName") + " " + conn.rs3.getString("Surname") + " " + conn.rs3.getString("CBOID"); } // fro holding ovc id // to create rows if (docid.equals("1")) { rw5 = shet1.createRow(rowcount); rw5.setHeightInPoints(25); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } rowcount++; cbocount++; } cell1 = rw5.createCell(0); cell1.setCellValue(cboname); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(1); cell1.setCellValue(chw); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(2); cell1.setCellValue(ovcid1); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(3); cell1.setCellValue(FirstName + " " + MiddleName + " " + Surname); cell1.setCellStyle(style_border1); for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i)); if (rw5 == null) { rw5 = shet1.createRow(rowcount); } if (docidarray.get(i).equals(docid)) { int cellcount = i + 4; cell2 = rw5.createCell(cellcount); cell2.setCellValue(value1); cell2.setCellStyle(style_border1); // if( docid.equals("3") ) { // System.out.println("am in2"); // System.out.println("****a "+docid +" "+cellcount); // cellcount++; // } System.out.println("****b " + docid + " " + cellcount); } } // cell3=rw5.createCell(2); // cell3.setCellValue(docid); // cell3.setCellStyle(style_border1); System.out.println( FirstName + " " + ovcid1 + " " + value1 + "___" + doccounter + "_____" + rowcount); if (docid.equals("10")) { // rowcount++; doccounter = 2; } if (chwval.equals("")) { chwval = chw; System.out.println(countercopy + " nnnna " + rowcount); // shet1.addMergedRegion(new CellRangeAddress(countercopy,rowcount-1,0,0)); // countercopy=rowcount; System.out.println(countercopy + " nnnnb " + rowcount + " " + chwval); } if (!chwval.equals(chw) && !chwval.equals("")) { chwval = chw; System.out.println(countercopy + " nnna" + rowcount + " " + chwval); shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount - 2, 1, 1)); countercopy = rowcount - 1; System.out.println(countercopy + " nnnb " + rowcount + " " + chwval); } String cboval = ""; if (cboval.equals("")) { cboval = cboname; // shet1.addMergedRegion(new CellRangeAddress(countercopy1,cbocount-1,0,0)); // countercopy1=cbocount; } if (!cboval.equals(cboname) && !cboval.equals("")) { cboval = cboname; System.out.println(countercopy1 + " nnna" + cbocount + " " + chwval); shet1.addMergedRegion(new CellRangeAddress(countercopy1, cbocount - 2, 0, 0)); countercopy1 = cbocount - 1; System.out.println(countercopy1 + " nnnb " + rowcount + " " + cboval); } // // if(monthval.equals("")){ // monthval= months; // System.out.println("88"+monthval +"___"+months); // System.out.println("88"+monthcopy1); // System.out.println("88"+counter1); // cell31.setCellValue(""+months+ " ("+conn.rs3.getInt(5)+")"); // shet2.addMergedRegion(new CellRangeAddress(monthcopy_1,counter1-1,1,1)); // monthcopy1=counter1; // // } // if(!monthval.equals("") && !monthval.equals(months)){ // monthval= months; // System.out.println("!!!"+monthval +"___"+months); // System.out.println("!!!"+monthcopy_1); // System.out.println("!!!!"+counter1); //// cell31.setCellValue(months); //// shet1.addMergedRegion(new CellRangeAddress(monthcopy,counter-1,1,1)); // monthcopy_1=counter1; // // } // if(rowcount>countercopy) { // // shet1.addMergedRegion(new CellRangeAddress(countercopy,rowcount-1,0,0)); // countercopy=rowcount; // } } if (rowcount > countercopy) { shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount - 1, 1, 1)); countercopy = rowcount; } if (cbocount > countercopy1) { shet1.addMergedRegion(new CellRangeAddress(countercopy1, cbocount - 1, 0, 0)); countercopy1 = cbocount; } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); } // // end of while loop ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=CBO_Details_Report_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }