List of usage examples for org.apache.poi.hssf.usermodel HSSFRow setRowStyle
@Override public void setRowStyle(CellStyle style)
From source file:br.edu.tglima.model.result.PlanilhaXLS.java
License:Open Source License
/** * Mtodo responsvel por gerar a planilha XLS. * /*from w w w . j a v a2 s.com*/ * * @param arquivo Referente ao local e nome do arquivo. * @return Retorno do tipo Boolean, indicando se o arquivo foi gerado * com sucesso, ou no. */ public boolean gerarPlanilha(File arquivo) { try { /*Verificamos se j existe um arquivo com esse "nome". Caso ele exista, ele ser removido e o novo arquivo ser gerado.*/ if (workbook.getNumberOfSheets() > 0) { workbook.removeSheetAt(0); } HSSFSheet sheet = workbook.createSheet("Valores Exportados"); /* Criando as linhas. --------------------------------------------- */ HSSFRow header1 = sheet.createRow((short) 0); HSSFRow linha02 = sheet.createRow((short) 1); HSSFRow linha03 = sheet.createRow((short) 2); HSSFRow linha04 = sheet.createRow((short) 3); HSSFRow linha05 = sheet.createRow((short) 4); HSSFRow header2 = sheet.createRow((short) 7); HSSFRow linha09 = sheet.createRow((short) 8); HSSFRow linha10 = sheet.createRow((short) 9); HSSFRow linha11 = sheet.createRow((short) 10); HSSFRow linha12 = sheet.createRow((short) 11); HSSFRow linha13 = sheet.createRow((short) 12); HSSFRow linha14 = sheet.createRow((short) 13); HSSFRow linha15 = sheet.createRow((short) 14); HSSFRow linha17 = sheet.createRow((short) 16); HSSFRow header3 = sheet.createRow((short) 19); HSSFRow linha21 = sheet.createRow((short) 20); HSSFRow linha22 = sheet.createRow((short) 21); HSSFRow linha23 = sheet.createRow((short) 22); HSSFRow linha25 = sheet.createRow((short) 24); // ------------------------------------------------------------------------ // /* Mesclando as clulas. ------------------------------------------ */ sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(19, 19, 0, 2)); // ------------------------------------------------------------------------ // /* Definindo a largura das colunas. ------------------------------- */ sheet.setColumnWidth(0, 12000); sheet.setColumnWidth(1, 5200); sheet.setColumnWidth(2, 6500); // ------------------------------------------------------------------------ // /* Definindo os estilos das clulas. ------------------------------ */ CellStyle headerStyle = workbook.createCellStyle(); Font headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 20); headerStyle.setFont(headerFont); headerStyle.setAlignment(HorizontalAlignment.CENTER); CellStyle cellCentered = workbook.createCellStyle(); cellCentered.setAlignment(HorizontalAlignment.CENTER); CellStyle cellFontBold = workbook.createCellStyle(); Font fontBold = workbook.createFont(); fontBold.setBold(true); cellFontBold.setFont(fontBold); CellStyle cellResulted = workbook.createCellStyle(); Font resultFont = workbook.createFont(); resultFont.setBold(true); cellResulted.setFont(resultFont); cellResulted.setAlignment(HorizontalAlignment.CENTER); Cell cell; // ------------------------------------------------------------------------ // /* Criando as Colunas da Tabela ----------------------------------- */ //Colunas da linha 1 header1.createCell(0).setCellValue("Dados Fornecidos"); //Colunas da linha 2 linha02.createCell(0).setCellValue("Data de Entrada"); linha02.createCell(2).setCellValue(this.rst.getDataEntrada()); //Colunas da Linha 3 linha03.createCell(0).setCellValue("Data de Sada"); linha03.createCell(2).setCellValue(this.rst.getDataSaida()); //Colunas da Linha 4 linha04.createCell(0).setCellValue("Salrio Informado"); linha04.createCell(2).setCellValue(this.rst.getSalario()); //Colunas da Linha 5 linha05.createCell(0).setCellValue("Motivo da Sada"); linha05.createCell(2).setCellValue(this.rst.getMotivoRes()); //Colunas da linha 8 header2.createCell(0).setCellValue("Resciso"); //Colunas da linha 9 linha09.createCell(0).setCellValue("Item"); linha09.createCell(1).setCellValue("Referncia"); linha09.createCell(2).setCellValue("Valor"); //Colunas da Linha 10 linha10.createCell(0).setCellValue("Saldo Salrio"); linha10.createCell(1).setCellValue(this.rst.getTotDiasTrabUltMes()); linha10.createCell(2).setCellValue(this.rst.getUltSalario()); //Colunas da Linha 11 linha11.createCell(0).setCellValue("13 Proporcional"); linha11.createCell(1).setCellValue(this.rst.getTotMesesTrabUltAno()); linha11.createCell(2).setCellValue(this.rst.getVlrDecimo()); //Colunas da Linha 12 linha12.createCell(0).setCellValue("Frias Proporcional"); linha12.createCell(1).setCellValue(this.rst.getTotMesesAqFerias()); linha12.createCell(2).setCellValue(this.rst.getVlrFerias()); //Colunas da Linha 13 linha13.createCell(0).setCellValue("1/3 Frias Proporcional"); linha13.createCell(1).setCellValue("-"); linha13.createCell(2).setCellValue(this.rst.getVlrTercoFerias()); //Colunas da Linha 14 linha14.createCell(0).setCellValue("Frias Vencidas"); linha14.createCell(1).setCellValue(this.rst.getTotFeriasVenc()); linha14.createCell(2).setCellValue(this.rst.getVlrFeriasVenc()); //Colunas da linha 15 linha15.createCell(0).setCellValue("Aviso Prvio"); linha15.createCell(1).setCellValue(this.rst.getTotDiasAviso()); linha15.createCell(2).setCellValue(this.rst.getVlrAvisoP()); //Colunas da linha 17 linha17.createCell(0).setCellValue("Valor Total"); linha17.createCell(1).setCellValue("-"); linha17.createCell(2).setCellValue(this.rst.getVlrTotVenc()); //Colunas da Linha 20 header3.createCell(0).setCellValue("FGTS"); //Colunas da Linha 21 linha21.createCell(0).setCellValue("Valores do FGTS estaro disponveis para saque?"); linha21.createCell(2).setCellValue(this.rst.getReceberFgts()); //Colunas da Linha 22 linha22.createCell(0).setCellValue("Saldo FGTS"); linha22.createCell(2).setCellValue(this.rst.getSaldoFgts()); //Colunas da Linha 23 linha23.createCell(0).setCellValue("Multa de 40%"); linha23.createCell(2).setCellValue(this.rst.getVlrMulta()); //Colunas da Linha 25 linha25.createCell(0).setCellValue("Valor total"); linha25.createCell(2).setCellValue(this.rst.getVlrTotFgts()); // ------------------------------------------------------------------------ // /* Aplicando os estilos nas clulas ------------------------------- */ cell = header1.getCell(0); cell.setCellStyle(headerStyle); header1.setRowStyle(headerStyle); header1.setHeightInPoints(30); cell = header2.getCell(0); cell.setCellStyle(headerStyle); header2.setRowStyle(headerStyle); header2.setHeightInPoints(30); cell = header3.getCell(0); cell.setCellStyle(headerStyle); header3.setRowStyle(headerStyle); header3.setHeightInPoints(30); linha02.getCell(2).setCellStyle(cellCentered); linha03.getCell(2).setCellStyle(cellCentered); linha04.getCell(2).setCellStyle(cellCentered); linha05.getCell(2).setCellStyle(cellCentered); linha09.getCell(1).setCellStyle(cellCentered); linha09.getCell(2).setCellStyle(cellCentered); linha10.getCell(1).setCellStyle(cellCentered); linha10.getCell(2).setCellStyle(cellCentered); linha11.getCell(1).setCellStyle(cellCentered); linha11.getCell(2).setCellStyle(cellCentered); linha12.getCell(1).setCellStyle(cellCentered); linha12.getCell(2).setCellStyle(cellCentered); linha13.getCell(1).setCellStyle(cellCentered); linha13.getCell(2).setCellStyle(cellCentered); linha14.getCell(1).setCellStyle(cellCentered); linha14.getCell(2).setCellStyle(cellCentered); linha15.getCell(1).setCellStyle(cellCentered); linha15.getCell(2).setCellStyle(cellCentered); linha17.getCell(0).setCellStyle(cellFontBold); linha17.getCell(1).setCellStyle(cellCentered); linha17.getCell(2).setCellStyle(cellResulted); linha21.getCell(2).setCellStyle(cellCentered); linha22.getCell(2).setCellStyle(cellCentered); linha23.getCell(2).setCellStyle(cellCentered); linha25.getCell(0).setCellStyle(cellFontBold); linha25.getCell(2).setCellStyle(cellResulted); // ------------------------------------------------------------------------ // /* Escrever, salvar e fechar o arquivo ---------------------------- */ workbook.write(arquivo); // Escrevendo no arquivo. workbook.close(); // Salvando e fechando o arquivo. return true; } catch (Exception e) { System.err.println("No foi possvel gerar seu arquivo!" + " \n" + e.getLocalizedMessage()); return false; } }
From source file:com.onsemi.cdars.config.FtpConfigUSL24hrs.java
public void cronRun() throws FileNotFoundException, IOException { LOGGER.info(// ww w . j ava 2 s . co m "Upper Spec Limit (USL Shipping) executed at everyday on 8:00 am. Current time is : " + new Date()); String username = System.getProperty("user.name"); if (!"fg79cj".equals(username)) { username = "imperial"; } DateFormat dateFormat = new SimpleDateFormat("ddMMMyyyy"); Date date = new Date(); String todayDate = dateFormat.format(date); String reportName = "C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Sending to SBN Factory Report (" + todayDate + ").xls"; FileOutputStream fileOut = new FileOutputStream(reportName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("HIMS PROCESS EXCEED USL"); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName(HSSFFont.FONT_ARIAL); font.setBoldweight(HSSFFont.COLOR_NORMAL); font.setBold(true); font.setColor(HSSFColor.DARK_BLUE.index); style.setFont(font); sheet.createFreezePane(0, 1); // Freeze 1st Row HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setRowStyle(style); HSSFCell cell1_0 = rowhead.createCell(0); cell1_0.setCellStyle(style); cell1_0.setCellValue("HARDWARE TYPE"); HSSFCell cell1_1 = rowhead.createCell(1); cell1_1.setCellStyle(style); cell1_1.setCellValue("HARDWARE ID"); HSSFCell cell1_2 = rowhead.createCell(2); cell1_2.setCellStyle(style); cell1_2.setCellValue("MATERIAL PASS NO"); HSSFCell cell1_3 = rowhead.createCell(3); cell1_3.setCellStyle(style); cell1_3.setCellValue("DURATION"); HSSFCell cell1_4 = rowhead.createCell(4); cell1_4.setCellStyle(style); cell1_4.setCellValue("CURRENT STATUS"); // WhUSLDAO whUslDAO = new WhUSLDAO(); // List<WhUSL> whUslList = whUslDAO.getWhUSLLog(); String materialPassNo = ""; String hardwareId = ""; String hardwareType = ""; String duration = ""; String status = ""; String text = ""; WhStatusLogDAO statusD = new WhStatusLogDAO(); List<WhStatusLog> whUslList = statusD.getTLReqToApproveAndApproveToMpCreatedList(); boolean checksize1 = false; boolean checksize2 = false; for (int i = 0; i < whUslList.size(); i++) { checksize1 = true; hardwareType = whUslList.get(i).getEquipmentType(); hardwareId = whUslList.get(i).getEquipmentId(); materialPassNo = whUslList.get(i).getMpNo(); String hourReqApp = whUslList.get(i).getRequestToApprove24(); String hourReqAppIfNull = whUslList.get(i).getRequestToApproveTemp24(); String hourAppMp = whUslList.get(i).getApproveToMPCreated24(); String hourAppMpIfNull = whUslList.get(i).getApproveToMPCreatedTemp24(); boolean flag = false; if (hourReqAppIfNull != null) { if (Integer.parseInt(hourReqAppIfNull) >= 24 && hourReqApp == null) { duration = whUslList.get(i).getRequestToApproveTemp(); status = "Pending Approval"; flag = true; } } if (hourAppMpIfNull != null) { if (Integer.parseInt(hourAppMpIfNull) >= 24 && hourAppMp == null && hourReqApp != null) { duration = whUslList.get(i).getApproveToMPCreatedTemp(); status = "Pending Material Pass Number"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } WhStatusLogDAO statusD2 = new WhStatusLogDAO(); List<WhStatusLog> whUslList2 = statusD2.getTLMpCreatedToFinalInventoryDateList(); for (int i = 0; i < whUslList2.size(); i++) { checksize2 = true; hardwareType = whUslList2.get(i).getEquipmentType(); hardwareId = whUslList2.get(i).getEquipmentId(); materialPassNo = whUslList2.get(i).getMpNo(); String hourMpTt = whUslList2.get(i).getMpCreatedToTtScan24(); String hourMpTtIfNull = whUslList2.get(i).getMpCreatedToTtScanTemp24(); String hourTtBs = whUslList2.get(i).getTtScanToBsScan24(); String hourTtBsIfNull = whUslList2.get(i).getTtScanToBsScanTemp24(); String hourBsShip = whUslList2.get(i).getBsScanToShip24(); String hourBsShipIfNull = whUslList2.get(i).getBsScanToShipTemp24(); String hourShipInv = whUslList2.get(i).getShipToInventory24(); String hourShipInvIfNull = whUslList2.get(i).getShipToInventoryTemp24(); boolean flag = false; if (hourMpTtIfNull != null) { if (Integer.parseInt(hourMpTtIfNull) >= 24 && hourMpTt == null) { duration = whUslList2.get(i).getMpCreatedToTtScanTemp(); status = "Pending Trip Ticket Scanning"; flag = true; } } if (hourTtBsIfNull != null) { if (Integer.parseInt(hourTtBsIfNull) >= 24 && hourTtBs == null && hourMpTt != null) { duration = whUslList2.get(i).getTtScanToBsScanTemp(); status = "Pending Barcode Sticker Scanning"; flag = true; } } if (hourBsShipIfNull != null) { if (Integer.parseInt(hourBsShipIfNull) >= 24 && hourBsShip == null && hourTtBs != null && hourMpTt != null) { duration = whUslList2.get(i).getBsScanToShipTemp(); status = "Pending Shipping Packing List"; flag = true; } } if (hourShipInvIfNull != null) { if (Integer.parseInt(hourShipInvIfNull) >= 24 && hourShipInv == null && hourBsShip != null && hourTtBs != null && hourMpTt != null) { duration = whUslList2.get(i).getShipToInventoryTemp(); status = "Pending Inventory in Seremban Factory"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } if (checksize1 == true || checksize2 == true) { workbook.write(fileOut); workbook.close(); //send email LOGGER.info("send email to person in charge"); EmailSender emailSender = new EmailSender(); com.onsemi.cdars.model.User user = new com.onsemi.cdars.model.User(); user.setFullname("All"); List<String> a = new ArrayList<String>(); String emailApprover = ""; String emaildistList1 = ""; String emaildistList2 = ""; String emaildistList3 = ""; String emaildistList4 = ""; emailApprover = "fg79cj@onsemi.com"; a.add(emailApprover); EmailConfigDAO econfD = new EmailConfigDAO(); int countDistList1 = econfD.getCountTask("Dist List 1"); if (countDistList1 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList1 = econfD.getEmailConfigByTask("Dist List 1"); emaildistList1 = distList1.getEmail(); a.add(emaildistList1); } econfD = new EmailConfigDAO(); int countDistList2 = econfD.getCountTask("Dist List 2"); if (countDistList2 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList2 = econfD.getEmailConfigByTask("Dist List 2"); emaildistList2 = distList2.getEmail(); a.add(emaildistList2); } econfD = new EmailConfigDAO(); int countDistList3 = econfD.getCountTask("Dist List 3"); if (countDistList3 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList3 = econfD.getEmailConfigByTask("Dist List 3"); emaildistList3 = distList3.getEmail(); a.add(emaildistList3); } econfD = new EmailConfigDAO(); int countDistList4 = econfD.getCountTask("Dist List 4"); if (countDistList4 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList4 = econfD.getEmailConfigByTask("Dist List 4"); emaildistList4 = distList4.getEmail(); a.add(emaildistList4); } String[] myArray = new String[a.size()]; String[] emailTo = a.toArray(myArray); // String[] to = {"fg79cj@onsemi.com"}; emailSender.htmlEmailWithAttachment(servletContext, user, //user name requestor emailTo, new File("C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Sending to SBN Factory Report (" + todayDate + ").xls"), "List of Hardware Exceed USL (24 hours) for Sending to SBN Factory", //subject "Report for Hardware Process from HIMS(Hadware Sending to SBN Factory) that exceed Upper Specs Limit (24 hours) has been made. <br />" + "Hence, attached is the report file for your view and perusal. <br /><br />" + "<br /><br /> " + "<style>table, th, td {border: 1px solid black;} </style>" + "<table style=\"width:100%\">" //tbl + "<tr>" + "<th>HARDWARE TYPE</th> " + "<th>HARDWARE ID</th> " + "<th>MATERIAL PASS NO.</th>" + "<th>DURATION</th>" + "<th>CURRENT STATUS</th>" + "</tr>" + table() + "</table>" + "<br />Thank you." //msg ); } // } }
From source file:com.onsemi.cdars.config.FtpConfigUSL24hrs.java
public void cronRun2() throws FileNotFoundException, IOException { LOGGER.info("Upper Spec Limit (USL Retrieval) executed at everyday on 8:00 am. Current time is : " + new Date()); String username = System.getProperty("user.name"); if (!"fg79cj".equals(username)) { username = "imperial"; }// w w w . j a v a 2 s .co m DateFormat dateFormat = new SimpleDateFormat("ddMMMyyyy"); Date date = new Date(); String todayDate = dateFormat.format(date); String reportName = "C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Retrieving from SBN Factory Report (" + todayDate + ").xls"; FileOutputStream fileOut = new FileOutputStream(reportName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("HIMS PROCESS EXCEED USL"); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName(HSSFFont.FONT_ARIAL); font.setBoldweight(HSSFFont.COLOR_NORMAL); font.setBold(true); font.setColor(HSSFColor.DARK_BLUE.index); style.setFont(font); sheet.createFreezePane(0, 1); // Freeze 1st Row HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setRowStyle(style); HSSFCell cell1_0 = rowhead.createCell(0); cell1_0.setCellStyle(style); cell1_0.setCellValue("HARDWARE TYPE"); HSSFCell cell1_1 = rowhead.createCell(1); cell1_1.setCellStyle(style); cell1_1.setCellValue("HARDWARE ID"); HSSFCell cell1_2 = rowhead.createCell(2); cell1_2.setCellStyle(style); cell1_2.setCellValue("MATERIAL PASS NO"); HSSFCell cell1_3 = rowhead.createCell(3); cell1_3.setCellStyle(style); cell1_3.setCellValue("DURATION"); HSSFCell cell1_4 = rowhead.createCell(4); cell1_4.setCellStyle(style); cell1_4.setCellValue("CURRENT STATUS"); String materialPassNo = ""; String hardwareId = ""; String hardwareType = ""; String duration = ""; String status = ""; String text = ""; WhStatusLogDAO statusD = new WhStatusLogDAO(); List<WhStatusLog> whUslList = statusD.getTLRetrieveRequestToCloseList(); boolean checksize1 = false; for (int i = 0; i < whUslList.size(); i++) { checksize1 = true; hardwareType = whUslList.get(i).getEquipmentType(); hardwareId = whUslList.get(i).getEquipmentId(); materialPassNo = whUslList.get(i).getMpNo(); String hourReqVer = whUslList.get(i).getRequestToVerifiedDate24(); String hourReqVerIfNull = whUslList.get(i).getRequestToVerifiedDateTemp24(); String hourVerShip = whUslList.get(i).getVerifiedDatetoShipDate24(); String hourVerShipIfNull = whUslList.get(i).getVerifiedDatetoShipDateTemp24(); String hourShipBScan = whUslList.get(i).getShipDateToBsScan24(); String hourShipBScanIfNull = whUslList.get(i).getShipDateToBsScanTemp24(); String hourBScanTT = whUslList.get(i).getBsScanToTtScan24(); String hourBScanTTIfNull = whUslList.get(i).getBsScanToTtScanTemp24(); boolean flag = false; if (hourReqVerIfNull != null) { if (Integer.parseInt(hourReqVerIfNull) >= 24 && hourReqVer == null) { duration = whUslList.get(i).getRequestToVerifiedDateTemp(); status = "Pending Box Barcode Verification at SBN Factory"; flag = true; } } if (hourVerShipIfNull != null) { if (Integer.parseInt(hourVerShipIfNull) >= 24 && hourVerShip == null && hourReqVer != null) { duration = whUslList.get(i).getVerifiedDatetoShipDateTemp(); status = "Pending Shipping Packing List"; flag = true; } } if (hourShipBScanIfNull != null) { if (Integer.parseInt(hourShipBScanIfNull) >= 24 && hourShipBScan == null && hourVerShip != null) { duration = whUslList.get(i).getShipDateToBsScanTemp(); status = "Pending Box Barcode Verification at Rel Lab"; flag = true; } } if (hourBScanTTIfNull != null) { if (Integer.parseInt(hourBScanTTIfNull) >= 24 && hourBScanTT == null && hourShipBScan != null) { duration = whUslList.get(i).getBsScanToTtScanTemp(); status = "Pending Trip Ticket Verification at Rel Lab"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } if (checksize1 == true) { workbook.write(fileOut); workbook.close(); //send email LOGGER.info("send email to person in charge"); EmailSender emailSender = new EmailSender(); com.onsemi.cdars.model.User user = new com.onsemi.cdars.model.User(); user.setFullname("All"); List<String> a = new ArrayList<String>(); String emailApprover = ""; String emaildistList1 = ""; String emaildistList2 = ""; String emaildistList3 = ""; String emaildistList4 = ""; emailApprover = "fg79cj@onsemi.com"; a.add(emailApprover); EmailConfigDAO econfD = new EmailConfigDAO(); int countDistList1 = econfD.getCountTask("Dist List 1"); if (countDistList1 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList1 = econfD.getEmailConfigByTask("Dist List 1"); emaildistList1 = distList1.getEmail(); a.add(emaildistList1); } econfD = new EmailConfigDAO(); int countDistList2 = econfD.getCountTask("Dist List 2"); if (countDistList2 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList2 = econfD.getEmailConfigByTask("Dist List 2"); emaildistList2 = distList2.getEmail(); a.add(emaildistList2); } econfD = new EmailConfigDAO(); int countDistList3 = econfD.getCountTask("Dist List 3"); if (countDistList3 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList3 = econfD.getEmailConfigByTask("Dist List 3"); emaildistList3 = distList3.getEmail(); a.add(emaildistList3); } econfD = new EmailConfigDAO(); int countDistList4 = econfD.getCountTask("Dist List 4"); if (countDistList4 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList4 = econfD.getEmailConfigByTask("Dist List 4"); emaildistList4 = distList4.getEmail(); a.add(emaildistList4); } String[] myArray = new String[a.size()]; String[] emailTo = a.toArray(myArray); // String[] to = {"hmsrelon@gmail.com", "hmsrelontest@gmail.com"}; //9/11/16 // String[] to = {"fg79cj@onsemi.com"}; emailSender.htmlEmailWithAttachment(servletContext, user, //user name requestor emailTo, new File("C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Retrieving from SBN Factory Report (" + todayDate + ").xls"), "List of Hardware Exceed USL (24 hours) for Retrieval from SBN Factory", //subject "Report for Hardware Process from HIMS(Hadware Retrieval from SBN Factory) that exceed Upper Specs Limit (24 hours) has been made. <br />" + "Hence, attached is the report file for your view and perusal. <br /><br />" + "<br /><br /> " + "<style>table, th, td {border: 1px solid black;} </style>" + "<table style=\"width:100%\">" //tbl + "<tr>" + "<th>HARDWARE TYPE</th> " + "<th>HARDWARE ID</th> " + "<th>MATERIAL PASS NO.</th>" + "<th>DURATION</th>" + "<th>CURRENT STATUS</th>" + "</tr>" + table2() + "</table>" + "<br />Thank you." //msg ); } // } }
From source file:dataQuality.checkDuplicates.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();//w w w . j av a2 s.c om dbConn conn = new dbConn(); i = 4; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5500); shet1.setColumnWidth(4, 7000); shet1.setColumnWidth(5, 5300); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5200); shet1.setColumnWidth(8, 5200); shet1.setColumnWidth(9, 5200); shet1.setColumnWidth(10, 5800); shet1.setColumnWidth(11, 5000); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); // styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); // styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell3 = rw4.createCell(2); cell4 = rw4.createCell(3); cell5 = rw4.createCell(4); cell6 = rw4.createCell(5); cell7 = rw4.createCell(6); cell8 = rw4.createCell(7); cell9 = rw4.createCell(8); cell10 = rw4.createCell(9); cell11 = rw4.createCell(10); cell12 = rw4.createCell(11); cell13 = rw4.createCell(12); cell14 = rw4.createCell(13); cell1.setCellValue("COUNTY NAME"); cell2.setCellValue("PARTNER NAME"); cell3.setCellValue("DISTRICT"); cell4.setCellValue("FACILITY"); cell5.setCellValue("GROUP NAME"); cell6.setCellValue("CLIENT NAME"); cell7.setCellValue("AGE"); cell8.setCellValue("GENDER"); cell9.setCellValue("YEAR"); cell10.setCellValue("SESSION ATTENDED"); cell11.setCellValue("No. of duplicates"); cell11.setCellValue("SERVICE PROVIDER"); cell12.setCellValue("START DATE"); cell13.setCellValue("END DATE"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); cell5.setCellStyle(stylex); cell6.setCellStyle(stylex); cell7.setCellStyle(stylex); cell8.setCellStyle(stylex); cell9.setCellStyle(stylex); cell10.setCellStyle(stylex); cell11.setCellStyle(stylex); cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); //cell14.setCellStyle(stylex); i = 1; String getClients = "SELECT * FROM clients ORDER BY fname"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; sess = val = cds = duplicate = 0; startdate = enddate = ""; // System.out.println("here : "+i); HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; cell1x = rw4x.createCell(0); cell2x = rw4x.createCell(1); cell3x = rw4x.createCell(2); cell4x = rw4x.createCell(3); cell5x = rw4x.createCell(4); cell6x = rw4x.createCell(5); cell7x = rw4x.createCell(6); cell8x = rw4x.createCell(7); cell9x = rw4x.createCell(8); cell10x = rw4x.createCell(9); cell11x = rw4x.createCell(10); cell12x = rw4x.createCell(11); cell13x = rw4x.createCell(12); // cell14x=rw4x.createCell(13); providername = ""; clientid = conn.rs.getString(1); clientname = conn.rs.getString(2) + " " + conn.rs.getString(3) + " " + conn.rs.getString(4); age = conn.rs.getString(5); gender = conn.rs.getString(6); groupid = conn.rs.getString(7); groupings = conn.rs.getString(8); districtid = conn.rs.getString(9); partnerid = conn.rs.getString(10); year = conn.rs.getString(13); providerid = conn.rs.getString(14); if (conn.rs.getString(3).equals(conn.rs.getString(4))) { clientname = conn.rs.getString(2) + " " + conn.rs.getString(4); } cds = conn.rs.getInt("lessons_attended"); start_date = end_date = ""; String serviceprov = "SELECT fname,mname,lname FROM service_provider WHERE provider_id='" + providerid + "'"; conn.rs1 = conn.st1.executeQuery(serviceprov); if (conn.rs1.next()) { if (conn.rs1.getString(2).equals(conn.rs1.getString(3))) { providername = conn.rs1.getString(1) + " " + conn.rs.getString(3); } else { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(2) + " " + conn.rs.getString(3); } } String checker = "SELECT COUNT(client_id) FROM clients WHERE fname=? && lname=? && client_id!=?"; conn.pst = conn.conn.prepareStatement(checker); conn.pst.setString(1, conn.rs.getString(2)); conn.pst.setString(2, conn.rs.getString(3)); // conn.pst.setString(3, conn.rs.getString(4)); conn.pst.setString(3, clientid); // conn.pst.setString(3, conn.rs.getString(2)); // conn.pst.setString(4, conn.rs.getString(3)); // conn.pst.setString(5, groupid); // conn.pst.setString(6, conn.rs.getString(2)); // conn.pst.setString(7, age); // conn.pst.setString(8, clientid); // conn.pst.setString(8, age); conn.rs1 = conn.pst.executeQuery(); found = 0; if (conn.rs1.next() == true) { found = conn.rs1.getInt(1); } System.out.println("found : " + found); if (found > 0) { duplicate = found; } if (found == 0) { duplicate = 0; } String getCnt = "SELECT district.district_name,county.county_name FROM district JOIN county ON district.county_id=county.county_id WHERE district.district_id='" + districtid + "'"; conn.rs1 = conn.st1.executeQuery(getCnt); if (conn.rs1.next() == true) { district = conn.rs1.getString(1); county = conn.rs1.getString(2); } String getPart = "SELECT partner_name FROM partner WHERE partner_id='" + partnerid + "'"; conn.rs1 = conn.st1.executeQuery(getPart); if (conn.rs1.next() == true) { partner = conn.rs1.getString(1); } if (!groupid.equals("0")) { String getgrp = "SELECT groups.group_name,health_facility.hf_name FROM groups JOIN health_facility ON groups.nhf_id=health_facility.hf_id" + " WHERE groups.group_id='" + groupid + "'"; conn.rs1 = conn.st1.executeQuery(getgrp); if (conn.rs1.next() == true) { groupname = conn.rs1.getString(1); hf = conn.rs1.getString(2); // SELECT START END DATE DATE FOR GROUP INDIVIDUALS---------------------------------------------- String getDates = "SELECT MAX(session_date),MIN(session_date) FROM sessions WHERE group_id='" + groupid + "' && session_date!=''"; conn.rs1 = conn.st1.executeQuery(getDates); if (conn.rs1.next() == true) { start_date = conn.rs1.getString(2); end_date = conn.rs1.getString(1); } } } if (groupid.equals("0")) { String getgrp1 = "SELECT health_facility.hf_name FROM no_group JOIN health_facility ON no_group.nhf_id=health_facility.hf_id" + " WHERE no_group.name='" + groupings + "'"; conn.rs1 = conn.st1.executeQuery(getgrp1); if (conn.rs1.next() == true) { groupname = "INDIVIDUAL"; hf = conn.rs1.getString(1); } String getDates = "SELECT MAX(session_date),MIN(session_date) FROM sessions WHERE groupings='" + groupings + "' && session_date!=''"; conn.rs1 = conn.st1.executeQuery(getDates); if (conn.rs1.next() == true) { start_date = conn.rs1.getString(2); end_date = conn.rs1.getString(1); } } // OUTPUT ATTENDED-------------------------------- if (duplicate > 0) { // OUTPUT SERVICES PROVIDED================================ cell1x.setCellValue(county); cell2x.setCellValue(partner); cell3x.setCellValue(district); cell4x.setCellValue(hf); cell5x.setCellValue(groupname); cell6x.setCellValue(clientname); cell7x.setCellValue(age); cell8x.setCellValue(gender); cell9x.setCellValue(year); cell10x.setCellValue(cds); // cell11x.setCellValue(duplicate); cell11x.setCellValue(providername); cell12x.setCellValue(start_date); cell13x.setCellValue(end_date); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); cell5x.setCellStyle(styleBorder); cell6x.setCellStyle(styleBorder); cell7x.setCellStyle(styleBorder); cell8x.setCellStyle(styleBorder); cell9x.setCellStyle(styleBorder); cell10x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell12x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); //cell14x.setCellStyle(styleBorder); i++; System.out.println("here : " + i); } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // 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=PWP_DUPLICATE_REPORT.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:dataQuality.Syncer.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from ww w . ja va 2 s . c o m*/ dbConn conn = new dbConn(); String[] dater1 = request.getParameter("startdate").split("/"); String[] dater2 = request.getParameter("enddate").split("/"); String[] bydate = request.getParameter("bydate").split("/"); System.out.println("started"); datekey = bydate[2] + "" + bydate[1] + "" + bydate[0]; startdate = dater1[2] + "-" + dater1[1] + "-" + dater1[0]; enddate = dater2[2] + "-" + dater2[1] + "-" + dater2[0]; sdate = dater1[2] + "_" + dater1[1] + "_" + dater1[0]; ndate = dater2[2] + "_" + dater2[1] + "_" + dater2[0]; submissiondate = bydate[1] + "/" + bydate[0] + "/" + bydate[2]; System.out.println(submissiondate); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); // HSSFSheet shet1=wb.createSheet("Client Enrollments"); HSSFSheet shet2 = wb.createSheet("Session Attendance"); HSSFSheet shet3 = wb.createSheet("Services Provided"); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // // HSSFSheet sheet1 = wb.getSheetAt(0); // shet1.setColumnWidth(0, 4000); // shet1.setColumnWidth(1, 4000); // shet1.setColumnWidth(2, 4000); // shet1.setColumnWidth(3, 7500); // shet1.setColumnWidth(4, 7000); // shet1.setColumnWidth(5, 7300); // shet1.setColumnWidth(6, 2500); // shet1.setColumnWidth(7, 3200); // shet1.setColumnWidth(8, 4200); shet2.setColumnWidth(0, 4000); shet2.setColumnWidth(1, 4000); shet2.setColumnWidth(2, 4000); shet2.setColumnWidth(3, 6500); shet2.setColumnWidth(4, 7000); shet2.setColumnWidth(5, 7300); shet2.setColumnWidth(6, 2500); shet2.setColumnWidth(7, 3200); shet2.setColumnWidth(8, 4200); shet3.setColumnWidth(0, 4000); shet3.setColumnWidth(1, 4000); shet3.setColumnWidth(2, 4000); shet3.setColumnWidth(3, 6500); shet3.setColumnWidth(4, 7000); shet3.setColumnWidth(5, 7300); shet3.setColumnWidth(6, 2500); shet3.setColumnWidth(7, 3200); shet3.setColumnWidth(8, 4200); shet3.setColumnWidth(9, 4200); shet3.setColumnWidth(10, 4800); shet3.setColumnWidth(11, 4000); shet3.setColumnWidth(12, 4300); shet3.setColumnWidth(13, 4000); shet3.setColumnWidth(14, 4300); shet3.setColumnWidth(15, 4000); shet3.setColumnWidth(16, 4200); shet3.setColumnWidth(17, 4200); // HSSFRow rw4=shet1.createRow(0); // rw4.setHeightInPoints(45); // rw4.setRowStyle(style2); //// rw4.createCell(1).setCellValue("Number"); // HSSFCell cell0,cell1,cell2,cell3,cell4,cell5,cell6,cell7,cell8; // // cell0=rw4.createCell(0); // cell1=rw4.createCell(1); // cell2=rw4.createCell(2); // cell3=rw4.createCell(3); // cell4=rw4.createCell(4); // cell5=rw4.createCell(5); // cell6=rw4.createCell(6); // cell7=rw4.createCell(7); // cell8=rw4.createCell(8); // // // cell0 .setCellValue("County name"); // cell1.setCellValue("Partner name"); // cell2.setCellValue("District name"); // cell3.setCellValue("Group name"); // cell4.setCellValue("Service provider"); // cell5.setCellValue("Client name"); // cell6.setCellValue("Age"); // cell7.setCellValue("Gender"); // cell8.setCellValue("Timestamp"); // cell0 .setCellStyle(stylex); // cell1.setCellStyle(stylex); // cell2.setCellStyle(stylex); // cell3.setCellStyle(stylex); // cell4.setCellStyle(stylex); // cell5.setCellStyle(stylex); // cell6.setCellStyle(stylex); // cell7.setCellStyle(stylex); // cell8.setCellStyle(stylex); HSSFRow rw2m = shet2.createRow(0); rw2m.setHeightInPoints(45); rw2m.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cellm0, cellm1, cellm2, cellm3, cellm4, cellm5, cellm6, cellm7, cellm8, cellm9, cellm10, cellmk11; cellm0 = rw2m.createCell(0); cellm1 = rw2m.createCell(1); cellm2 = rw2m.createCell(2); cellm3 = rw2m.createCell(3); cellm4 = rw2m.createCell(4); cellm5 = rw2m.createCell(5); cellm6 = rw2m.createCell(6); cellm7 = rw2m.createCell(7); cellm8 = rw2m.createCell(8); cellm9 = rw2m.createCell(9); cellm10 = rw2m.createCell(10); cellmk11 = rw2m.createCell(11); cellm0.setCellValue("County name"); cellm1.setCellValue("Partner name"); cellm2.setCellValue("District name"); cellm3.setCellValue("Group name"); cellm4.setCellValue("Service provider"); cellm5.setCellValue("Client name"); cellm6.setCellValue("Age"); cellm7.setCellValue("Gender"); cellm8.setCellValue("Message"); cellm9.setCellValue("Status"); cellm10.setCellValue("Session Date"); cellmk11.setCellValue("Timestamp"); cellm0.setCellStyle(stylex); cellm1.setCellStyle(stylex); cellm2.setCellStyle(stylex); cellm3.setCellStyle(stylex); cellm4.setCellStyle(stylex); cellm5.setCellStyle(stylex); cellm6.setCellStyle(stylex); cellm7.setCellStyle(stylex); cellm8.setCellStyle(stylex); cellm9.setCellStyle(stylex); cellm10.setCellStyle(stylex); cellmk11.setCellStyle(stylex); HSSFRow rw4n = shet3.createRow(0); rw4n.setHeightInPoints(45); rw4n.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0n, cell1n, cell2n, cell3n, cell4n, cell5n, cell6n, cell7n, cell8n, cell9n, cell10n, cell11n, cell12n, cell13n, cell14n, cell15n, cell16n, cell17n, cell18n; cell0n = rw4n.createCell(0); cell1n = rw4n.createCell(1); cell2n = rw4n.createCell(2); cell3n = rw4n.createCell(3); cell4n = rw4n.createCell(4); cell5n = rw4n.createCell(5); cell6n = rw4n.createCell(6); cell7n = rw4n.createCell(7); cell8n = rw4n.createCell(8); cell9n = rw4n.createCell(9); cell10n = rw4n.createCell(10); cell11n = rw4n.createCell(11); cell12n = rw4n.createCell(12); cell13n = rw4n.createCell(13); cell14n = rw4n.createCell(14); cell15n = rw4n.createCell(15); cell16n = rw4n.createCell(16); cell17n = rw4n.createCell(17); cell18n = rw4n.createCell(18); cell0n.setCellValue("County name"); cell1n.setCellValue("Partner name"); cell2n.setCellValue("District name"); cell3n.setCellValue("Group name"); cell4n.setCellValue("Service provider"); cell5n.setCellValue("Client name"); cell6n.setCellValue("Age"); cell7n.setCellValue("Gender"); cell8n.setCellValue("Message (s)"); cell9n.setCellValue("Received Contraceptives"); cell10n.setCellValue("Reffered To Service Point"); cell11n.setCellValue("Given Condoms"); cell12n.setCellValue("Screened For TB"); cell13n.setCellValue("Screened For STIs"); cell14n.setCellValue("Partner Tested"); cell15n.setCellValue("Children Tested"); cell16n.setCellValue("Disclosed Status"); cell17n.setCellValue("Submission date"); cell18n.setCellValue("Timestamp"); cell0n.setCellStyle(stylex); cell1n.setCellStyle(stylex); cell2n.setCellStyle(stylex); cell3n.setCellStyle(stylex); cell4n.setCellStyle(stylex); cell5n.setCellStyle(stylex); cell6n.setCellStyle(stylex); cell7n.setCellStyle(stylex); cell8n.setCellStyle(stylex); cell9n.setCellStyle(stylex); cell10n.setCellStyle(stylex); cell11n.setCellStyle(stylex); cell12n.setCellStyle(stylex); cell13n.setCellStyle(stylex); cell14n.setCellStyle(stylex); cell15n.setCellStyle(stylex); cell16n.setCellStyle(stylex); cell17n.setCellStyle(stylex); cell18n.setCellStyle(stylex); pos1 = 1; pos2 = 1; pos3 = 1; //String getClientRegistration="SELECT county.county_name,partner.partner_name,district.district_name,group_id,clients.age,provider_id, " // + "clients.gender,clients.timestamp,clients.fname,clients.mname,clients.lname FROM clients JOIN partner ON clients.partner_id=partner.partner_id " // + "JOIN (district JOIN county ON district.county_id=county.county_id )ON district.district_id=clients.district_id " // + "WHERE STR_TO_DATE(clients.timestamp,'%Y-%m-%d') BETWEEN STR_TO_DATE('"+startdate+"','%Y-%m-%d') AND STR_TO_DATE('"+enddate+"','%Y-%m-%d')"; // conn.rs=conn.st.executeQuery(getClientRegistration); // while(conn.rs.next()){ // countyname=conn.rs.getString(1); // partnername=conn.rs.getString(2); // districtname=conn.rs.getString(3); // age=conn.rs.getInt(5); // gender=conn.rs.getString(7); // timestamp=conn.rs.getString(8); // // clientname=conn.rs.getString(9)+" "+conn.rs.getString(10)+" "+conn.rs.getString(11); // if(conn.rs.getString(10).equals(conn.rs.getString(11))){ // clientname=conn.rs.getString(9)+" "+conn.rs.getString(11); // } // // if(!conn.rs.getString(4).equals("0")){ // String getGroupname="SELECT group_name FROM groups WHERE group_id='"+conn.rs.getString(4)+"'"; // conn.rs1=conn.st1.executeQuery(getGroupname); // if(conn.rs1.next()==true){ // groupname=conn.rs1.getString(1); // } // } // else{ // groupname="INDIVIDUALS"; // } // String getProvider="SELECT fname,mname,lname FROM service_provider WHERE provider_id='"+conn.rs.getString(6)+"'"; // conn.rs1=conn.st1.executeQuery(getProvider); // if(conn.rs1.next()==true){ // providername=conn.rs1.getString(1)+" "+conn.rs1.getString(2)+" "+conn.rs1.getString(3); // if(conn.rs1.getString(2).equals(conn.rs1.getString(3))){ // providername=conn.rs1.getString(1)+" "+conn.rs1.getString(3); // } // } //// ADD TO THE EXCELL OUTPUT.............................................................. // // HSSFRow rw4x=shet1.createRow(pos1); // rw4x.setHeightInPoints(25); // rw4x.setRowStyle(style2); // HSSFCell cell0x,cell1x,cell2x,cell3x,cell4x,cell5x,cell6x,cell7x,cell8x; // cell0x=rw4x.createCell(0); // cell1x=rw4x.createCell(1); // cell2x=rw4x.createCell(2); // cell3x=rw4x.createCell(3); // cell4x=rw4x.createCell(4); // cell5x=rw4x.createCell(5); // cell6x=rw4x.createCell(6); // cell7x=rw4x.createCell(7); // cell8x=rw4x.createCell(8); // // // OUTPUT SERVICES PROVIDED================================ // cell0x .setCellValue(countyname); // cell1x.setCellValue(partnername); // cell2x.setCellValue(districtname); // cell3x.setCellValue(groupname); // cell4x.setCellValue(providername); // cell5x.setCellValue(clientname); // cell6x.setCellValue(age); // cell7x.setCellValue(gender); // cell8x.setCellValue(timestamp); // // cell0x .setCellStyle(stborder); // cell1x.setCellStyle(stborder); // cell2x.setCellStyle(stborder); // cell3x.setCellStyle(stborder); // cell4x.setCellStyle(stborder); // cell5x.setCellStyle(stborder); // cell6x.setCellStyle(stborder); // cell7x.setCellStyle(stborder); // cell8x.setCellStyle(stborder); // // // pos1++; // } // CHECK SESSIONS ATTENDED============================================================== String getSessioner = "SELECT county.county_name,partner.partner_name,district.district_name,group_id,clients.age,provider_id, " + "clients.gender,register2.timestamp,register2.session_no,register2.value,clients.fname,clients.mname,clients.lname,register2.date " + "FROM clients JOIN partner ON clients.partner_id=partner.partner_id " + "JOIN (district JOIN county ON district.county_id=county.county_id )ON district.district_id=clients.district_id " + "JOIN register2 ON register2.client_id=clients.client_id " + "WHERE register2.value<'5' && register2.datekey<'" + datekey + "' && register2.datekey>'0' && STR_TO_DATE(register2.timestamp,'%Y-%m-%d') BETWEEN STR_TO_DATE('" + startdate + "','%Y-%m-%d') AND STR_TO_DATE('" + enddate + "','%Y-%m-%d')" + " ORDER BY clients.client_id "; conn.rs = conn.st.executeQuery(getSessioner); while (conn.rs.next()) { countyname = conn.rs.getString(1); partnername = conn.rs.getString(2); districtname = conn.rs.getString(3); age = conn.rs.getInt(5); gender = conn.rs.getString(7); timestamp = conn.rs.getString(8); sessionno = conn.rs.getInt(9); value = conn.rs.getInt(10); // String sessiondate=conn.rs.getString(14); String[] sessdt = conn.rs.getString(14).split("/"); String sessiondate = sessdt[2] + "-" + sessdt[0] + "-" + sessdt[1]; if (value == 1) { attendance_status = "Present"; } else { attendance_status = "Absent"; } clientname = conn.rs.getString(11) + " " + conn.rs.getString(12) + " " + conn.rs.getString(13); if (conn.rs.getString(12).equals(conn.rs.getString(13))) { clientname = conn.rs.getString(11) + " " + conn.rs.getString(13); } if (!conn.rs.getString(4).equals("0")) { String getGroupname = "SELECT group_name FROM groups WHERE group_id='" + conn.rs.getString(4) + "'"; conn.rs1 = conn.st1.executeQuery(getGroupname); if (conn.rs1.next() == true) { groupname = conn.rs1.getString(1); } } else { groupname = "INDIVIDUALS"; } String getProvider = "SELECT fname,mname,lname FROM service_provider WHERE provider_id='" + conn.rs.getString(6) + "'"; conn.rs1 = conn.st1.executeQuery(getProvider); if (conn.rs1.next() == true) { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(2) + " " + conn.rs1.getString(3); if (conn.rs1.getString(2).equals(conn.rs1.getString(3))) { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(3); } } String getMessage = "SELECT message FROM message_codes WHERE message_id='" + sessionno + "'"; conn.rs1 = conn.st1.executeQuery(getMessage); if (conn.rs1.next()) { messagename = conn.rs1.getString(1); } // ADD TO THE EXCELL OUTPUT.............................................................. HSSFRow rw2m1 = shet2.createRow(pos2); rw2m1.setHeightInPoints(25); rw2m1.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cellm01, cellm11, cellm21, cellm31, cellm41, cellm51, cellm61, cellm71, cellm81, cellm91, cellm101, cellm111; cellm01 = rw2m1.createCell(0); cellm11 = rw2m1.createCell(1); cellm21 = rw2m1.createCell(2); cellm31 = rw2m1.createCell(3); cellm41 = rw2m1.createCell(4); cellm51 = rw2m1.createCell(5); cellm61 = rw2m1.createCell(6); cellm71 = rw2m1.createCell(7); cellm81 = rw2m1.createCell(8); cellm91 = rw2m1.createCell(9); cellm101 = rw2m1.createCell(10); cellm111 = rw2m1.createCell(11); cellm01.setCellValue(countyname); cellm11.setCellValue(partnername); cellm21.setCellValue(districtname); cellm31.setCellValue(groupname); cellm41.setCellValue(providername); cellm51.setCellValue(clientname); cellm61.setCellValue(age); cellm71.setCellValue(gender); cellm81.setCellValue(messagename); cellm91.setCellValue(attendance_status); cellm101.setCellValue(sessiondate); cellm111.setCellValue(timestamp); cellm01.setCellStyle(stborder); cellm11.setCellStyle(stborder); cellm21.setCellStyle(stborder); cellm31.setCellStyle(stborder); cellm41.setCellStyle(stborder); cellm51.setCellStyle(stborder); cellm61.setCellStyle(stborder); cellm71.setCellStyle(stborder); cellm81.setCellStyle(stborder); cellm91.setCellStyle(stborder); cellm101.setCellStyle(stborder); cellm111.setCellStyle(stborder); pos2++; System.out.println("register record number : " + pos2); } System.out.println("finished processing register2"); // CHECK SESSIONS ATTENDED============================================================== String getServices = "SELECT county.county_name,partner.partner_name,district.district_name,group_id,clients.age,provider_id, " + "clients.gender,services_provided.timestamp,services_provided.session_no,services_provided.contraceptive_method," + "services_provided.rsp,services_provided.cds_given,services_provided.screened_tb, services_provided.screened_stis, services_provided.tested_partner, services_provided.tested_children, services_provided.disclosed_status" + ",clients.fname,clients.mname,clients.lname,services_provided.submission_date" + " FROM clients JOIN partner ON clients.partner_id=partner.partner_id " + "JOIN (district JOIN county ON district.county_id=county.county_id )ON district.district_id=clients.district_id " + "JOIN services_provided ON services_provided.client_id=clients.client_id " + "WHERE STR_TO_DATE(services_provided.submission_date,'%m/%d/%Y')< STR_TO_DATE(services_provided.submission_date,'" + submissiondate + "') && STR_TO_DATE(services_provided.timestamp,'%Y-%m-%d') BETWEEN STR_TO_DATE('" + startdate + "','%Y-%m-%d') AND STR_TO_DATE('" + enddate + "','%Y-%m-%d') " // + " && (services_provided.contraceptive_method='YES' || services_provided.rsp || services_provided.cds_given>0 || services_provided.screened_tb='YES' || services_provided.screened_stis='YES' || services_provided.tested_partner='YES' || services_provided.tested_children='YES' || services_provided.disclosed_status='YES')" + " ORDER BY clients.client_id "; conn.rs = conn.st.executeQuery(getServices); while (conn.rs.next()) { cm = rsp = screenedTB = ScreenedStis = screenedTb = testedpartner = testedChildren = discosedStatus = messagename = ""; givenCDS = 0; countyname = conn.rs.getString(1); partnername = conn.rs.getString(2); districtname = conn.rs.getString(3); age = conn.rs.getInt(5); gender = conn.rs.getString(7); timestamp = conn.rs.getString(8); sess = conn.rs.getString(9).split(","); cm = conn.rs.getString(10); rsp = conn.rs.getString(11); givenCDS = conn.rs.getInt(12); screenedTb = conn.rs.getString(13); ScreenedStis = conn.rs.getString(14); testedpartner = conn.rs.getString(15); testedChildren = conn.rs.getString(16); discosedStatus = conn.rs.getString(17); String submissiondate = conn.rs.getString(21); clientname = conn.rs.getString(18) + " " + conn.rs.getString(19) + " " + conn.rs.getString(20); if (conn.rs.getString(19).equals(conn.rs.getString(20))) { clientname = conn.rs.getString(18) + " " + conn.rs.getString(20); } for (String session1 : sess) { if (!(session1.equals("") || session1.equals(","))) { String getMess = "SELECT message FROM message_codes WHERE message_id='" + session1 + "'"; conn.rs1 = conn.st1.executeQuery(getMess); if (conn.rs1.next() == true) { messagename += conn.rs1.getString(1) + "\n"; } } } if (!conn.rs.getString(4).equals("0")) { String getGroupname = "SELECT group_name FROM groups WHERE group_id='" + conn.rs.getString(4) + "'"; conn.rs1 = conn.st1.executeQuery(getGroupname); if (conn.rs1.next() == true) { groupname = conn.rs1.getString(1); } } else { groupname = "INDIVIDUALS"; } String getProvider = "SELECT fname,mname,lname FROM service_provider WHERE provider_id='" + conn.rs.getString(6) + "'"; conn.rs1 = conn.st1.executeQuery(getProvider); if (conn.rs1.next() == true) { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(2) + " " + conn.rs1.getString(3); if (conn.rs1.getString(2).equals(conn.rs1.getString(3))) { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(3); } } // ADD TO THE EXCELL OUTPUT.............................................................. HSSFRow rw4n1 = shet3.createRow(pos3); rw4n1.setHeightInPoints(25); rw4n1.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0n1, cell1n1, cell2n1, cell3n1, cell4n1, cell5n1, cell6n1, cell7n1, cell8n1, cell9n1, cell10n1, cell11n1, cell12n1, cell13n1, cell14n1, cell15n1, cell16n1, cell17n1, cell18n1; cell0n1 = rw4n1.createCell(0); cell1n1 = rw4n1.createCell(1); cell2n1 = rw4n1.createCell(2); cell3n1 = rw4n1.createCell(3); cell4n1 = rw4n1.createCell(4); cell5n1 = rw4n1.createCell(5); cell6n1 = rw4n1.createCell(6); cell7n1 = rw4n1.createCell(7); cell8n1 = rw4n1.createCell(8); cell9n1 = rw4n1.createCell(9); cell10n1 = rw4n1.createCell(10); cell11n1 = rw4n1.createCell(11); cell12n1 = rw4n1.createCell(12); cell13n1 = rw4n1.createCell(13); cell14n1 = rw4n1.createCell(14); cell15n1 = rw4n1.createCell(15); cell16n1 = rw4n1.createCell(16); cell17n1 = rw4n1.createCell(17); cell18n1 = rw4n1.createCell(18); cell0n1.setCellValue(countyname); cell1n1.setCellValue(partnername); cell2n1.setCellValue(districtname); cell3n1.setCellValue(groupname); cell4n1.setCellValue(providername); cell5n1.setCellValue(clientname); cell6n1.setCellValue(age); cell7n1.setCellValue(gender); cell8n1.setCellValue(messagename); cell9n1.setCellValue(cm); cell10n1.setCellValue(rsp); cell11n1.setCellValue(givenCDS); cell12n1.setCellValue(screenedTb); cell13n1.setCellValue(ScreenedStis); cell14n1.setCellValue(testedpartner); cell15n1.setCellValue(testedChildren); cell16n1.setCellValue(discosedStatus); cell17n1.setCellValue(submissiondate); cell18n1.setCellValue(timestamp); cell0n1.setCellStyle(stborder); cell1n1.setCellStyle(stborder); cell2n1.setCellStyle(stborder); cell3n1.setCellStyle(stborder); cell4n1.setCellStyle(stborder); cell5n1.setCellStyle(stborder); cell6n1.setCellStyle(stborder); cell7n1.setCellStyle(stborder); cell8n1.setCellStyle(stborder); cell9n1.setCellStyle(stborder); cell10n1.setCellStyle(stborder); cell11n1.setCellStyle(stborder); cell12n1.setCellStyle(stborder); cell13n1.setCellStyle(stborder); cell14n1.setCellStyle(stborder); cell15n1.setCellStyle(stborder); cell16n1.setCellStyle(stborder); cell17n1.setCellStyle(stborder); cell18n1.setCellStyle(stborder); pos3++; System.out.println("services record number : " + pos3); } System.out.println("finished all"); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // 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=PWP_Raw_Data_Between_" + sdate.trim() + "_AND_" + ndate.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:demo.admin.controller.UserController.java
@RequestMapping(value = "/user/downloadData") @VerifyAuthentication(Trader = true, Admin = true, Operation = true) public HttpEntity<byte[]> downloadUserData(String status, String securephone, @RequestParam(value = "startDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate, @RequestParam(value = "endDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate) throws IOException, DocumentException { if (securephone != null && securephone != "") { securephone = Where.$like$(securephone); }/*www . j a v a 2s .co m*/ List<Map<String, Object>> users = userMapper.userExport(status, securephone, startDate, endDate); String type = status + "?"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(type); HSSFRow row = sheet.createRow(0); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); sheet.setVerticallyCenter(true); sheet.setHorizontallyCenter(true); String[] excelHeader = { "??", "???", "??", "", "??" }; for (int i = 0; i < excelHeader.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader[i]); cell.setCellStyle(cellStyle); sheet.autoSizeColumn(i, true); } for (int i = 0; i < users.size(); i++) { Map<String, Object> resultSet = users.get(i); sheet.autoSizeColumn(i, true); row = sheet.createRow(i + 1); row.setRowStyle(cellStyle); row.createCell(0).setCellValue(i + 1); row.createCell(1).setCellValue(String.valueOf(resultSet.get("name"))); row.createCell(2).setCellValue(String.valueOf(resultSet.get("address"))); row.createCell(3).setCellValue(String.valueOf(resultSet.get("registertime"))); row.createCell(4).setCellValue(String.valueOf(resultSet.get("securephone"))); } File file = File.createTempFile(".xls", ".xls"); OutputStream out = new FileOutputStream(file); wb.write(out); HttpHeaders headers = new HttpHeaders(); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); headers.setContentDispositionFormData("attachment", URLEncoder.encode(type, "UTF-8") + LocalDate.now() + ".xls"); return new HttpEntity<byte[]>(FileUtils.readFileToByteArray(file), headers); }
From source file:kitt.admin.controller.UserController.java
@RequestMapping(value = "/downloadData") @Authority(role = AuthenticationRole.Service) @Authority(role = AuthenticationRole.TraderAssistant) @Authority(role = AuthenticationRole.LegalPersonnel) @Authority(role = AuthenticationRole.Admin) @Authority(role = AuthenticationRole.Operation) public void downloadUserData(String status, @RequestParam(value = "securephone", required = false, defaultValue = "") String securephone, @RequestParam(value = "clienttype", required = false, defaultValue = "0") int clienttype, @RequestParam(value = "startDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate, @RequestParam(value = "endDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate, HttpServletRequest request, HttpServletResponse response) throws IOException, DocumentException { List<Map<String, Object>> users = userMapper.userExport(status, Where.$like$(securephone), clienttype, startDate, endDate);/*from ww w . j av a 2s . com*/ String filename = status + "?"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(filename); HSSFRow row = sheet.createRow(0); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); sheet.setVerticallyCenter(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 1200); sheet.setColumnWidth(1, 3600); sheet.setColumnWidth(2, 8000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 4500); String[] excelHeader = { "??", "", "??", "??", "" }; for (int i = 0; i < excelHeader.length; i++) { sheet.autoSizeColumn(i, true); HSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader[i]); cell.setCellStyle(cellStyle); } for (int i = 0; i < users.size(); i++) { Map<String, Object> resultSet = users.get(i); sheet.autoSizeColumn(i, true); row = sheet.createRow(i + 1); row.setRowStyle(cellStyle); row.createCell(0).setCellValue(i + 1); row.createCell(1).setCellValue(String.valueOf(resultSet.get("tradername")).equals("null") ? "" : String.valueOf(resultSet.get("tradername"))); row.createCell(2).setCellValue(String.valueOf(resultSet.get("companyname"))); row.createCell(3).setCellValue(String.valueOf(resultSet.get("securephone"))); row.createCell(4).setCellValue(String.valueOf(resultSet.get("verifytime"))); } response.setCharacterEncoding("UTF-8"); response.setContentType("application/x-download"); filename += LocalDate.now() + ".xls"; if (request.getHeader("user-agent").toLowerCase().contains("firefox")) { filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1"); } else { filename = URLEncoder.encode(filename, "UTF-8"); } response.addHeader("Content-Disposition", "attachment; filename=" + filename); OutputStream out = response.getOutputStream(); wb.write(out); out.close(); }
From source file:org.openmrs.module.tracnetreporting.impl.TracNetIndicatorServiceImpl.java
License:Open Source License
/** * Exports data to the Excel File/* ww w.j av a 2s . c o m*/ * * @throws IOException * @see org.openmrs.module.tracnetreporting.service.TracNetIndicatorService#exportDataToExcelFile(java.util.Map) */ @SuppressWarnings({ "deprecation", "unchecked" }) public void exportDataToExcelFile(HttpServletRequest request, HttpServletResponse response, Map<String, Integer> indicatorsList, String filename, String title, String startDate, String endDate) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\""); HSSFSheet sheet = workbook.createSheet(title); int count = 0; sheet.setDisplayRowColHeadings(true); // Setting Style HSSFFont font = workbook.createFont(); HSSFCellStyle cellStyle = workbook.createCellStyle(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFFont.COLOR_RED); cellStyle.setFillForegroundColor((short) 0xA); // Title HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(""); row.setRowStyle(cellStyle); row.createCell((short) 1).setCellValue("" + title + "(Between " + startDate + " and " + endDate + ")"); // Headers row = sheet.createRow((short) 2); row.createCell((short) 0).setCellValue("#"); row.createCell((short) 1).setCellValue("INDICATOR NAME"); row.createCell((short) 2).setCellValue("INDICATOR"); Log log = LogFactory.getLog(this.getClass()); log.info("00000000000000000000000000000000000000000000000000000000000000000"); // log.info(); List<String> indicator_msg = null; indicator_msg = (ArrayList<String>) request.getSession().getAttribute(request.getParameter("id") + "_msg"); for (String indicator : indicatorsList.keySet()) { count++; row = sheet.createRow((short) count + 3); row.createCell((short) 0).setCellValue(indicator.toString().substring(4)); row.createCell((short) 1).setCellValue(indicator_msg.get(count - 1));// .substring(3) row.createCell((short) 2).setCellValue(indicatorsList.get(indicator).toString()); // log.info("========================>>> "+count); } OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); }
From source file:org.openmrs.module.tracnetreporting.impl.TracNetIndicatorServiceImpl.java
License:Open Source License
/** * Exports data to the Excel File/*from www.j ava 2 s .c om*/ * * @throws IOException * @see org.openmrs.module.tracnetreporting.service.TracNetIndicatorService#exportDataToExcelFile(java.util.Map) */ @SuppressWarnings("deprecation") @Override public void exportPatientsListToExcelFile(HttpServletRequest request, HttpServletResponse response, List<Person> patientsList, String filename, String title, String startDate, String endDate) throws IOException { log.info("exporttttttttttttttttttttttttttttttttttttt" + patientsList); Session session = getSessionFactory().getCurrentSession(); HSSFWorkbook workbook = new HSSFWorkbook(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\""); HSSFSheet sheet = workbook.createSheet(title); int count = 0; sheet.setDisplayRowColHeadings(true); // Setting Style HSSFFont font = workbook.createFont(); HSSFCellStyle cellStyle = workbook.createCellStyle(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFFont.COLOR_RED); cellStyle.setFillForegroundColor((short) 0xA); // Title HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(""); row.setRowStyle(cellStyle); row.createCell((short) 1).setCellValue("" + title + "(Between " + startDate + " and " + endDate + ")"); // Headers row = sheet.createRow((short) 2); row.createCell((short) 0).setCellValue("#"); row.createCell((short) 1).setCellValue("Patient ID"); row.createCell((short) 2).setCellValue("Patient Names"); row.createCell((short) 3).setCellValue("Gender"); row.createCell((short) 4).setCellValue("Last Encounter Date"); row.createCell((short) 5).setCellValue("Last Return Visit Date"); for (Person person : patientsList) { // Getting some stuff for Last Encounter Date and Last Return Visit // Date. Date maxEncounterDateTime = (Date) session .createSQLQuery("select cast(max(encounter_datetime)as DATE) from encounter where patient_id = " + person.getPersonId()) .uniqueResult(); Date maxReturnVisitDay = (Date) session .createSQLQuery("select cast(max(value_datetime) as DATE ) " + "from obs where concept_id = " + ConstantValues.NEXT_SCHEDULED_VISIT + " and person_id = " + person.getPersonId()) .uniqueResult(); count++; row = sheet.createRow((short) count + 3); row.createCell((short) 0).setCellValue(count); row.createCell((short) 1).setCellValue(person.getPersonId()); row.createCell((short) 2).setCellValue(person.getFamilyName() + " " + person.getGivenName()); row.createCell((short) 3).setCellValue(person.getGender()); row.createCell((short) 4).setCellValue(maxEncounterDateTime.toString()); row.createCell((short) 5).setCellValue(maxReturnVisitDay.toString()); } OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); }
From source file:org.openmrs.module.tracnetreporting.impl.TracNetPatientServiceImpl.java
License:Open Source License
/** * Exports data to the Excel File/*from ww w. j a v a 2 s . c o m*/ * * @throws IOException * @see org.openmrs.module.tracnetreporting.service.TracNetIndicatorService#exportDataToExcelFile(java.util.Map) */ @SuppressWarnings("deprecation") public void exportDataToExcelFile(HttpServletRequest request, HttpServletResponse response, Map<String, Integer> indicatorsList, String filename, String title, String startDate, String endDate) throws IOException { log.info("exporttttttttttttttttttttttttttttttt" + indicatorsList); HSSFWorkbook workbook = new HSSFWorkbook(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\""); HSSFSheet sheet = workbook.createSheet(title); int count = 0; sheet.setDisplayRowColHeadings(true); // Setting Style HSSFFont font = workbook.createFont(); HSSFCellStyle cellStyle = workbook.createCellStyle(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFFont.COLOR_RED); cellStyle.setFillForegroundColor((short) 0xA); // Title HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(""); row.setRowStyle(cellStyle); row.createCell((short) 1).setCellValue("" + title + "(Between " + startDate + " and " + endDate + ")"); // Headers row = sheet.createRow((short) 2); row.createCell((short) 0).setCellValue("#"); row.createCell((short) 1).setCellValue("INDICATOR NAME"); row.createCell((short) 2).setCellValue("INDICATOR"); // for (String indicator : indicatorsList.keySet()) { // count++; // row = sheet.createRow((short) count + 3); // row.createCell((short) 0).setCellValue(count); // row.createCell((short) 1).setCellValue(indicator.toString()); // row.createCell((short) 2).setCellValue( // indicatorsList.get(indicator).toString()); // } OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); }