List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setBorderBottom
@Override public void setBorderBottom(BorderStyle border)
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportTaxinvoiceReport(List reptax) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("TaxInvoice"); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFRow row1 = sheet.createRow(0);/*from w ww. j a v a 2 s . c om*/ HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Tax Invoice Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("TAX ID"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("TAX NO"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("TAX DATE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("CODE AP"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("DESCRIPTION"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("GROSS AMOUNT"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("VAT AMOUNT"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("AMOUNT"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("FLAG TYPE"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("INVOICE TYPE"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("TAX NO 1"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("BRANCH"); cell32.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("BRANCH NO"); cell33.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); int count = 3; HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); for (int i = 0; i < reptax.size(); i++) { ReportTaxInvoice data = (ReportTaxInvoice) reptax.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getTaxid()); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getTaxno()); cell13.setCellStyle(styleC23); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(String.valueOf(data.getTaxdate())); cell2.setCellStyle(styleC23); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getCodeap()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getDescription().trim()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getGrossamount())) ? (data.getGrossamount()).doubleValue() : 0); cell5.setCellStyle(styleC25); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getVatamount())) ? (data.getVatamount()).doubleValue() : 0); cell6.setCellStyle(styleC25); HSSFCell cell7 = row.createCell(8); cell7.setCellValue( !"null".equalsIgnoreCase(String.valueOf(data.getAmount())) ? (data.getAmount()).doubleValue() : 0); cell7.setCellStyle(styleC25); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getFlagtype()); cell8.setCellStyle(styleC23); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getInvoicetype()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getTaxno1()); cell10.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(12); cell11.setCellValue(data.getBranch()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(13); cell12.setCellValue(data.getBranchno()); cell12.setCellStyle(styleC23); } for (int j = 0; j < 15; j++) { sheet.autoSizeColumn(j); } sheet.setColumnWidth(5, 256 * 40);//27 exportFileExcel("TaxInvoiceReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportAgentReport(List repAgent) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Agent"); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFRow row1 = sheet.createRow(0);//from w w w .ja va 2 s .com HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Agent Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("SYSTEM DATE"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("SYSTEM STAFF"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("CODE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("NAME"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("ADDRESS"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("TEL"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("FAX"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("DESCRIPTION"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("NAME T"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("DESCRIPTION T"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("ADDRESS T"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("EMAIL"); cell32.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("WEB"); cell33.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); HSSFCell cell34 = row2.createCell(14); cell34.setCellValue("REMARKS"); cell34.setCellStyle(styleC3Center); sheet.autoSizeColumn(14); HSSFCell cell35 = row2.createCell(15); cell35.setCellValue("WARNING"); cell35.setCellStyle(styleC3Center); sheet.autoSizeColumn(15); HSSFCell cell36 = row2.createCell(16); cell36.setCellValue("REF ID"); cell36.setCellStyle(styleC3Center); sheet.autoSizeColumn(16); HSSFCell cell37 = row2.createCell(17); cell37.setCellValue("BRANCH"); cell37.setCellStyle(styleC3Center); sheet.autoSizeColumn(17); HSSFCell cell38 = row2.createCell(18); cell38.setCellValue("BRANCH NO"); cell38.setCellStyle(styleC3Center); sheet.autoSizeColumn(18); HSSFCell cell39 = row2.createCell(19); cell39.setCellValue("TAX NO"); cell39.setCellStyle(styleC3Center); sheet.autoSizeColumn(19); int count = 3; HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); for (int i = 0; i < repAgent.size(); i++) { ReportAgent data = (ReportAgent) repAgent.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(String.valueOf(data.getSystemdate())); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getSystemstaff()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getCode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getName()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getAddress().trim()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getTel()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getFax()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getDescription().trim()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getNameT()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getDescriptionT().trim()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getAddressT().trim()); cell10.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(12); cell11.setCellValue(data.getEmail()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(13); cell12.setCellValue(data.getWeb()); cell12.setCellStyle(styleC24); HSSFCell cell14 = row.createCell(14); cell14.setCellValue(data.getRemarks()); cell14.setCellStyle(styleC24); HSSFCell cell15 = row.createCell(15); cell15.setCellValue(data.getWarning()); cell15.setCellStyle(styleC24); HSSFCell cell16 = row.createCell(16); cell16.setCellValue(data.getRefid()); cell16.setCellStyle(styleC24); HSSFCell cell17 = row.createCell(17); cell17.setCellValue(data.getBranch()); cell17.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(18); cell18.setCellValue(data.getBranchno()); cell18.setCellStyle(styleC24); HSSFCell cell19 = row.createCell(19); cell19.setCellValue(data.getTaxno()); cell19.setCellStyle(styleC23); } for (int j = 0; j < 20; j++) { sheet.autoSizeColumn(j); } for (int k = 4; k < 21; k++) { if (k != 6 && k != 7 && k != 12 && k != 13 && k < 16) { sheet.setColumnWidth(k, 256 * 35);//27 } else { sheet.setColumnWidth(k, 256 * 20);//27 } } exportFileExcel("AgentReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportStaffReport(List repStaff) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Staff"); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFRow row1 = sheet.createRow(0);/*from w ww. j a v a 2 s.c om*/ HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Staff Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("SYSTEM DATE"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("SYSTEM STAFF"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("CODE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("NAME"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("PASSWD"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("POSITION"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("DEPARTMENT ID"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("TEL"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("CAR"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("STATUS"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("SIGNATURE"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); int count = 3; HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); for (int i = 0; i < repStaff.size(); i++) { ReportStaff data = (ReportStaff) repStaff.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(String.valueOf(data.getSystemdate())); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getSystemstaff()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getCode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getName()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getPasswd()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getPosition()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getDepartmentid()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getTel()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getCar()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getStatus()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getSignature()); cell10.setCellStyle(styleC24); } for (int j = 0; j < 12; j++) { sheet.autoSizeColumn(j); } // sheet.setColumnWidth(8, 256*40);//27 // sheet.setColumnWidth(10, 256*40);//27 exportFileExcel("StaffReport", wb); }
From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java
License:Open Source License
public static void createCell(HSSFRow row, short column, String value, HSSFWorkbook wb) { HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND); HSSFCell cell = row.getCell(column); if (cell == null) cell = row.createCell(column);/*from w ww . j a va2s. c o m*/ //cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(value); cell.setCellStyle(style); }
From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java
License:Open Source License
public static void createCellAlignLeft(HSSFRow row, short column, String value, HSSFWorkbook wb) { HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND); HSSFCell cell = row.getCell(column); if (cell == null) cell = row.createCell(column);/*from w ww .j av a 2 s. c o m*/ //cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(value); cell.setCellStyle(style); }
From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java
License:Open Source License
public static void createCellBold(HSSFRow row, short column, String value, HSSFWorkbook wb) { HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont();//from ww w. j ava 2s.com font.setColor((short) 0xc); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND); HSSFCell cell = row.getCell(column); if (cell == null) cell = row.createCell(column); //cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(value); cell.setCellStyle(style); }
From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java
License:Open Source License
public static void createCell(HSSFRow row, short column, int value, HSSFWorkbook wb) { HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND); HSSFCell cell = row.getCell(column); if (cell == null) cell = row.createCell(column);/* w w w . ja v a2 s .c om*/ cell.setCellValue(value); cell.setCellStyle(style); }
From source file:Compras.Conciliacion.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: try {//from w ww .j av a 2 s.c o m javax.swing.JFileChooser archivo = new javax.swing.JFileChooser(); archivo.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null; if (archivo.showSaveDialog(null) == archivo.APPROVE_OPTION) { ruta = archivo.getSelectedFile().getAbsolutePath(); if (ruta != null) { File archivoXLS = new File(ruta + ".xls"); File plantilla = new File("imagenes/plantillaConciliacion.xls"); Session session = HibernateUtil.getSessionFactory().openSession(); ArrayList datos = new ArrayList(); Query query = session.createSQLQuery( "select compania.nombre, orden.tipo_nombre, orden.modelo, orden.no_serie, clientes.nombre as nombres,orden.id_orden \n" + "from orden inner join compania on compania.id_compania=orden.id_compania inner join clientes on clientes.id_clientes=orden.id_cliente\n" + "where orden.id_orden=" + Integer.parseInt(orden) + ""); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); datos = (ArrayList) query.list(); // Path FROM = Paths.get("imagenes/plantillaConciliacion.xls"); Path TO = Paths.get(ruta + ".xls"); //sobreescribir el fichero de destino, si existe, y copiar // los atributos, incluyendo los permisos rwx CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.COPY_ATTRIBUTES }; Files.copy(FROM, TO, options); FileInputStream miPlantilla = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla); HSSFWorkbook libro = new HSSFWorkbook(fsFileSystem); libro.getSheet("Conciliacion").getRow(0).getCell(6) .setCellValue("CONCILIACIN PARA FACTURACIN"); for (int i = 0; i < datos.size(); i++) { java.util.HashMap map = (java.util.HashMap) datos.get(i); libro.getSheet("Conciliacion").getRow(1).getCell(2) .setCellValue(map.get("nombre").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(2) .setCellValue(map.get("tipo_nombre").toString()); libro.getSheet("Conciliacion").getRow(3).getCell(2) .setCellValue(map.get("modelo").toString()); libro.getSheet("Conciliacion").getRow(4).getCell(2) .setCellValue(map.get("no_serie").toString()); libro.getSheet("Conciliacion").getRow(5).getCell(2) .setCellValue(map.get("nombres").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(12) .setCellValue(map.get("id_orden").toString()); } HSSFCellStyle borde_d = libro.createCellStyle(); borde_d.setBorderBottom(CellStyle.BORDER_THIN); borde_d.setBorderTop(CellStyle.BORDER_THIN); borde_d.setBorderRight(CellStyle.BORDER_THIN); borde_d.setBorderLeft(CellStyle.BORDER_THIN); borde_d.setAlignment(CellStyle.ALIGN_RIGHT); HSSFCellStyle borde_i = libro.createCellStyle(); borde_i.setBorderBottom(CellStyle.BORDER_THIN); borde_i.setBorderTop(CellStyle.BORDER_THIN); borde_i.setBorderRight(CellStyle.BORDER_THIN); borde_i.setBorderLeft(CellStyle.BORDER_THIN); borde_i.setAlignment(CellStyle.ALIGN_LEFT); HSSFCellStyle borde_c = libro.createCellStyle(); borde_c.setBorderBottom(CellStyle.BORDER_THIN); borde_c.setBorderTop(CellStyle.BORDER_THIN); borde_c.setBorderRight(CellStyle.BORDER_THIN); borde_c.setBorderLeft(CellStyle.BORDER_THIN); borde_c.setAlignment(CellStyle.ALIGN_CENTER); HSSFCellStyle borde_dr = libro.createCellStyle(); borde_dr.setBorderBottom(CellStyle.BORDER_THIN); borde_dr.setBorderTop(CellStyle.BORDER_THIN); borde_dr.setBorderRight(CellStyle.BORDER_THIN); borde_dr.setBorderLeft(CellStyle.BORDER_THIN); borde_dr.setAlignment(CellStyle.ALIGN_RIGHT); borde_dr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_dr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_dr.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_ir = libro.createCellStyle(); borde_ir.setBorderBottom(CellStyle.BORDER_THIN); borde_ir.setBorderTop(CellStyle.BORDER_THIN); borde_ir.setBorderRight(CellStyle.BORDER_THIN); borde_ir.setBorderLeft(CellStyle.BORDER_THIN); borde_ir.setAlignment(CellStyle.ALIGN_LEFT); borde_ir.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_ir.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_ir.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_cr = libro.createCellStyle(); borde_cr.setBorderBottom(CellStyle.BORDER_THIN); borde_cr.setBorderTop(CellStyle.BORDER_THIN); borde_cr.setBorderRight(CellStyle.BORDER_THIN); borde_cr.setBorderLeft(CellStyle.BORDER_THIN); borde_cr.setAlignment(CellStyle.ALIGN_CENTER); borde_cr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_cr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_cr.setFillForegroundColor(HSSFColor.YELLOW.index); DecimalFormat formatoDecimal = new DecimalFormat("####0.0"); DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); int miRenglon = 9; for (int i = 0; i < t_datos.getRowCount(); i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == true || (boolean) t_datos.getValueAt(i, 4) == true) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue("V"); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } //font1.setColor(BaseColor.WHITE); libro.getSheet("Conciliacion").createRow(miRenglon); libro.getSheet("Conciliacion") .addMergedRegion(new CellRangeAddress(miRenglon, miRenglon, 0, 14)); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue("Faltante en Vales"); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0).setCellStyle(borde_c); miRenglon++; for (int i = 0; i < t_datos.getRowCount(); i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == false && (boolean) t_datos.getValueAt(i, 4) == false && t_datos.getValueAt(i, 5) != null) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue(""); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } FileOutputStream archivo1 = new FileOutputStream(archivoXLS); libro.write(archivo1); archivo1.close(); Desktop.getDesktop().open(archivoXLS); } } } catch (Exception e) { e.printStackTrace(); } }
From source file:dataQuality.checkDuplicates.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();//from ww w . j a v a 2s. c o m 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();// w w w.j ava 2 s.co 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(); }