List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook createFont
@Override
public Font createFont()
From source file:cfdi.clases.db.DerbyUtilities.java
License:Open Source License
/** * Exporta los registros de de CFDI datos generales o su detalle, con el filtro que se * haya utilizado en la interface grfica * /* www . j a v a 2 s .c o m*/ * @param query es el query filtradn para la tabla de CFDI y CFDI_DETALLE * @param nombre nombre del archivo * @param path directorio donde se va a crear el archivo de excel * @return the boolean */ public boolean exportarExcel(String query, String nombre, String path) { Connection connection = null; Statement st = null; ResultSet rs = null; boolean respuesta = false; BoneCP connectionPool = null; try { Class.forName(propiedades.getProperty("DB_DRIVER")); // setup the connection pool BoneCPConfig config = new BoneCPConfig(); config.setJdbcUrl(propiedades.getProperty("DB_SERVER")); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb config.setUsername(propiedades.getProperty("DB_USER")); config.setPassword(propiedades.getProperty("DB_PASSWORD")); config.setMinConnectionsPerPartition(5); config.setMaxConnectionsPerPartition(10); config.setPartitionCount(1); connectionPool = new BoneCP(config); // setup the connection pool FileOutputStream fileOut = new FileOutputStream(path + nombre + ".xlsx"); connection = connectionPool.getConnection(); // fetch a connection if (connection != null) { st = connection.createStatement(); rs = st.executeQuery(query); ResultSetMetaData metaData = rs.getMetaData(); int count = metaData.getColumnCount(); SXSSFWorkbook workbook = new SXSSFWorkbook(10000); Sheet sheet = workbook.createSheet(nombre); int rownum = 0; Row row = sheet.createRow(rownum++); CellStyle stylec = workbook.createCellStyle(); stylec.setBorderBottom(CellStyle.BORDER_THIN); stylec.setBottomBorderColor(IndexedColors.BLACK.getIndex()); Font fontc = workbook.createFont(); fontc.setBoldweight(Font.BOLDWEIGHT_BOLD); stylec.setFont(fontc); for (int i = 1; i <= count; i++) { row.createCell(i).setCellValue(metaData.getColumnName(i)); row.getCell(i).setCellStyle(stylec); } while (rs.next()) { Row rowh = sheet.createRow(rownum++); for (int i = 1; i <= count; i++) { if (metaData.getColumnTypeName(i).equalsIgnoreCase("INT") || metaData.getColumnTypeName(i).equalsIgnoreCase("INT UNSIGNED")) rowh.createCell(i).setCellValue(rs.getInt(i)); else if (metaData.getColumnTypeName(i).equalsIgnoreCase("DOUBLE")) rowh.createCell(i).setCellValue(rs.getDouble(i)); else rowh.createCell(i).setCellValue(rs.getString(i)); } } /*if(rownum<5000){ for (int i = 1; i <= count; i++) sheet.autoSizeColumn(i); }*/ try { workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { System.out.println("Error: export 1"); } catch (IOException e) { System.out.println("Error: export 2"); } respuesta = true; connectionPool.shutdown(); } } catch (SQLException e) { System.out.println("Error: insertDatos 3"); logger.log(Level.SEVERE, null, e); } catch (ClassNotFoundException ex) { logger.log(Level.SEVERE, null, ex); } catch (Exception ex) { System.out.println("Error: insertDatos 5"); logger.log(Level.SEVERE, null, ex); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { System.out.println("Error: insertDatos 4"); logger.log(Level.SEVERE, null, e); } } } return respuesta; }
From source file:com.hauldata.dbpa.file.book.XlsxTargetSheet.java
License:Apache License
private static Font getFont(FontStyles fontStyles, SXSSFWorkbook book, Map<FontStyles, XSSFFont> fontsUsed, Map<Integer, XSSFColor> colorsUsed) { XSSFFont font = fontsUsed.get(fontStyles); if (font != null) { return font; }//from ww w . ja v a2 s . c om font = (XSSFFont) book.createFont(); if (fontStyles.color != null) { font.setColor(getColor(fontStyles.color, book, colorsUsed)); } if (fontStyles.fontStyle != null) { switch (fontStyles.fontStyle) { case NORMAL: break; case ITALIC: font.setItalic(true); break; } } if (fontStyles.fontWeight != null) { switch (fontStyles.fontWeight) { case NORMAL: break; case BOLD: font.setBold(true); break; } } if (fontStyles.textDecorationLine != null) { switch (fontStyles.textDecorationLine) { case NONE: break; case LINE_THROUGH: font.setStrikeout(true); break; case UNDERLINE: font.setUnderline((fontStyles.textDecorationStyle == FontStyles.TextDecorationStyle.DOUBLE) ? FontUnderline.DOUBLE : FontUnderline.SINGLE); break; } } fontsUsed.put(fontStyles, font); return font; }
From source file:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
public static void writeFile(String directory, String fileName, Map<String, List<List<ExcelCell>>> sheets, int headerRowHeight, int commentRowHeight) { if (StringUtils.isNotBlank(directory) && StringUtils.isNotBlank(fileName) && sheets != null && !sheets.isEmpty()) { SXSSFWorkbook workbook = new SXSSFWorkbook(); Font invisibleFont = workbook.createFont(); for (Entry<String, List<List<ExcelCell>>> entry : sheets.entrySet()) { // TODO: remove and logging // log.info("writeFile","Started writing sheet: "+entry.getKey()); SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(entry.getKey()); int totalColumn = 0; if (entry.getValue() != null && !entry.getValue().isEmpty()) { int rowNumber = 0; Font dataFont = null; for (List<ExcelCell> rows : entry.getValue()) { // Row row = sheet.getRow(rowNumber)!=null ? sheet.getRow(rowNumber) : rowMap.get(rowNumber); Row row = sheet.createRow(rowNumber); int rowHeight = rowNumber == 0 ? headerRowHeight : commentRowHeight; if (rowNumber == 0 || rowNumber == 1) { if (rowHeight > 0) { row.setHeight((short) rowHeight); }/*from ww w. j av a2 s. c o m*/ addDataValidation(rowNumber, sheet); } rowNumber++; if (rows != null && !rows.isEmpty()) { int cellNum = 0; Font font = null; if (rowNumber > 3 && dataFont != null) { font = dataFont; } else { font = workbook.createFont(); dataFont = font; } // as each row requires different syle with separate font Map<IndexedColors, CellStyle> s_cellStyle = new HashMap<IndexedColors, CellStyle>(); for (ExcelCell cellValue : rows) { Cell cell = row.createCell(cellNum); updateCell(cell, cellValue, s_cellStyle, workbook, font, invisibleFont); ++cellNum; } totalColumn = cellNum; } if (rowNumber == 2) {/* * auto size after DOCUMENTATION-ROW (row=2) so, we don't have to do * multiple times */ autoSize(sheet, totalColumn, false); // rowMap = createRows(workbook, sheet, rowNumber+1, excelConfig.getMaxInputRows()); } } } autoSize(sheet, totalColumn, true); } // addMetaSheet(workbook); writeWorkBook(directory, fileName, workbook); } }
From source file:com.rapidminer.operator.io.ExcelExampleSetWriter.java
License:Open Source License
/** * Writes the provided {@link ExampleSet} to a XLSX formatted data sheet. * * @param wb/*w ww. java 2s . c o m*/ * the workbook to use * @param sheet * the excel sheet to write to. * @param dateFormat * a string which describes the format used for dates. * @param numberFormat * a string which describes the format used for numbers. * @param exampleSet * the exampleSet to write * @param op * needed for checkForStop * @throws ProcessStoppedException * if the process was stopped by the user. * @throws WriteException */ private static void writeXLSXDataSheet(SXSSFWorkbook wb, Sheet sheet, String dateFormat, String numberFormat, ExampleSet exampleSet, Operator op) throws WriteException, ProcessStoppedException { Font headerFont = wb.createFont(); headerFont.setBold(true); CellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(headerFont); // create the header Iterator<Attribute> a = exampleSet.getAttributes().allAttributes(); int columnCounter = 0; int rowCounter = 0; Row headerRow = sheet.createRow(rowCounter); while (a.hasNext()) { Attribute attribute = a.next(); Cell headerCell = headerRow.createCell(columnCounter); headerCell.setCellValue(attribute.getName()); headerCell.setCellStyle(headerStyle); columnCounter++; } rowCounter++; // body font Font bodyFont = wb.createFont(); bodyFont.setBold(false); CreationHelper createHelper = wb.getCreationHelper(); // number format CellStyle numericalStyle = wb.createCellStyle(); numericalStyle.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat)); numericalStyle.setFont(bodyFont); // date format CellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat)); dateStyle.setFont(bodyFont); // create nominal cell style CellStyle nominalStyle = wb.createCellStyle(); nominalStyle.setFont(bodyFont); // fill body for (Example example : exampleSet) { // create new row Row bodyRow = sheet.createRow(rowCounter); // iterate over attributes and save examples a = exampleSet.getAttributes().allAttributes(); columnCounter = 0; while (a.hasNext()) { Attribute attribute = a.next(); Cell currentCell = bodyRow.createCell(columnCounter); if (!Double.isNaN(example.getValue(attribute))) { if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.DATE_TIME)) { Date dateValue = example.getDateValue(attribute); currentCell.setCellValue(dateValue); currentCell.setCellStyle(dateStyle); } else if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.NUMERICAL)) { double numericalValue = example.getNumericalValue(attribute); currentCell.setCellValue(numericalValue); currentCell.setCellStyle(numericalStyle); } else { currentCell.setCellValue( stripIfNecessary(replaceForbiddenChars(example.getValueAsString(attribute)))); currentCell.setCellStyle(nominalStyle); } } columnCounter++; } rowCounter++; // checkForStop every 100 examples if (op != null && rowCounter % 100 == 0) { op.checkForStop(); } } }
From source file:edu.harvard.hms.dbmi.bd2k.irct.ws.rs.resultconverter.XSLXTabularDataConverter.java
License:Mozilla Public License
@Override public StreamingOutput createStream(final Result result) { StreamingOutput stream = new StreamingOutput() { @Override//from ww w .j a va 2 s .com public void write(OutputStream outputStream) throws IOException, WebApplicationException { ResultSet rs = null; SXSSFWorkbook wb = null; try { rs = (ResultSet) result.getData(); rs.load(result.getResultSetLocation()); wb = new SXSSFWorkbook(100); // Create Sheet Sheet sh = wb.createSheet("Results"); // Create Header CellStyle headerStyle = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFont(font); Row headerRow = sh.createRow(0); for (int i = 0; i < rs.getColumnSize(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(rs.getColumn(i).getName()); } // Add data rs.beforeFirst(); int rowNum = 1; while (rs.next()) { Row row = sh.createRow(rowNum); for (int i = 0; i < rs.getColumnSize(); i++) { String value = rs.getString(i); Cell cell = row.createCell(i); if (value != null) { cell.setCellValue(rs.getString(i)); } } rowNum++; } wb.write(outputStream); } catch (ResultSetException | PersistableException e) { log.info("Error creating XSLX Stream: " + e.getMessage()); } finally { if (wb != null) { wb.close(); } if (rs != null && !rs.isClosed()) { try { rs.close(); } catch (ResultSetException e) { e.printStackTrace(); } } if (outputStream != null) { outputStream.close(); } } } }; return stream; }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *///from w w w. j a va 2 s . c o m @Test public void testWriteExcelFileAttribute() throws Exception { try { log.debug("testWriteExcelFileAttribute start...."); short rowheight = 40; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cs.setFillPattern(HSSFCellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); log.debug("getAlignment : " + cs1.getAlignment()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); log.debug("getFillPattern : " + cs1.getFillPattern()); assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern()); log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor()); log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFileAttribute end...."); } }
From source file:excelmasivo.ExcelMasivo.java
/** * @param args the command line arguments *//*w w w.java 2 s . co m*/ public static void main(String[] args) { String driver = "oracle.jdbc.OracleDriver"; String user = "DRKL"; String pass = "DRKL"; String url = "jdbc:oracle:thin:@localhost:1521:XE"; String query = "SELECT * FROM PRODUCTOS ORDER BY TO_NUMBER(SUBSTR(CODIGO_PRODUCTO,7))"; Connection con; Statement st; ResultSet rs; ResultSetMetaData rsm; SXSSFWorkbook libro = new SXSSFWorkbook(); SXSSFSheet hoja = libro.createSheet("Reporte"); SXSSFRow fila; SXSSFCell celda; FileOutputStream out; int x = 0; CellStyle cs = libro.createCellStyle(); cs.getFillForegroundColor(); Font f = libro.createFont(); //f.setBoldweight(Font.BOLDWEIGHT_BOLD); f.setFontHeightInPoints((short) 12); cs.setFont(f); try { Class.forName(driver); con = DriverManager.getConnection(url, user, pass); st = con.createStatement(); rs = st.executeQuery(query); rsm = rs.getMetaData(); while (rs.next()) { //crear la fila fila = hoja.createRow(x++); for (int i = 1; i <= rsm.getColumnCount(); i++) { //recorrer las columnas celda = fila.createCell(i); celda.setCellStyle(cs); celda.setCellValue(rs.getString(i)); //System.out.print(rs.getString(i)); } //System.out.println(); if (x % 50000 == 0) { System.out.println("Se procesaron:" + x); } } out = new FileOutputStream(new File("D:\\java\\Productos_" + GregorianCalendar.MILLISECOND + ".xlsx")); libro.write(out); out.close(); System.out.println("Archivo generado con exito"); } catch (ClassNotFoundException | SQLException | FileNotFoundException ex) { Logger.getLogger(ExcelMasivo.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelMasivo.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.notReached.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*www . ja va 2 s .c om*/ dbConn conn = new dbConn(); position = 1; String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME, DIC NAME, GROUP NAME,CLIENT FULL NAME ," + " CCC NO. , MOBILE NUMBER , GENDER , DATE OF BIRTH , MARITAL STATUS , EMPLOYMENT STATUS ," + "EDUCATION LEVEL , ART STATUS , SERVICE PROVIDER NAME , HEALTH FACILITY, LESSONS ATTENDED,AGE BRACKET, Knowledge of HIV Status," + "Partner HIV Testing,Child HIV Testing,Discordance,HIV Disclosure,Risk Factor/Reduction,Condom Use," + "Alcohol and Substance Abuse,Adherence,STIs,Family Planning,PMTCT,TB").split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb1; OPCPackage pkg = OPCPackage.open(allpath); wb1 = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 100); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); Sheet shet1 = wb.getSheet("Sheet1"); Font 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); Font font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); CellStyle 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); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } CellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle 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); Font fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); Cell cell; Row rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String getClients = "SELECT county.county_name,partner.partner_name,district.district_name,dic.dic_name," + "groups.group_name,personal_information.fname,personal_information.mname,personal_information.lname," + "personal_information.ccc_no,personal_information.mobile_no, personal_information.gender," + "personal_information.dob,marital_status.name,employment_status.name,education_levels.name," + "art_status.name," + "service_provider.fname,service_provider.mname,service_provider.lname,health_facility.hf_name, " + "personal_information.lessons_attended," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13 " + " FROM personal_information " + " LEFT JOIN groups ON personal_information.group_id=groups.group_id " + " LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + " LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id " + " LEFT JOIN health_facility ON personal_information.hf_id=health_facility.hf_id " + " LEFT JOIN district ON personal_information.district_id=district.district_id " + " LEFT JOIN marital_status ON personal_information.marital_status=marital_status.id " + " LEFT JOIN employment_status ON personal_information.employment_status=employment_status.id " + " LEFT JOIN education_levels ON personal_information.education_level=education_levels.id " + " LEFT JOIN art_status ON personal_information.art_status=art_status.id " + " LEFT JOIN register ON personal_information.client_id=register.client_id " + " LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + " LEFT JOIN county ON district.county_id=county.county_id " + " WHERE (personal_information.completionmonth=0 || personal_information.completionyear=0) " + " ORDER BY partner.partner_name,county.county_name,district.district_name,dic.dic_name," + "groups.group_name"; System.out.println("query is : " + getClients); conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { // ADD THE DATA TO EXCEL HERE groupName = DICName = districtName = partnerName = countyName = agebracket = lessons_attended = year = ""; clientFname = clientMname = clientLname = ccc_no = mobile_no = gender = dob = marital_status = ""; location = employment_status = education_level = under_18 = ovc_children = hiv_year = art_status = ""; registration_date = approved_by = designation = approval_date = ""; SPFname = SPMname = SPLname = SPFullName = healthFacility = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = ""; if (conn.rs.getString(1) != null) { countyName = conn.rs.getString(1); } if (conn.rs.getString(2) != null) { partnerName = conn.rs.getString(2); } if (conn.rs.getString(3) != null) { districtName = conn.rs.getString(3); } if (conn.rs.getString(4) != null) { DICName = conn.rs.getString(4); } else { DICName = "NO DIC"; } if (conn.rs.getString(5) != null) { groupName = conn.rs.getString(5); } else { groupName = "Individual"; } if (conn.rs.getString(6) != null) { clientFname = conn.rs.getString(6); } if (conn.rs.getString(7) != null) { clientMname = conn.rs.getString(7); } if (conn.rs.getString(8) != null) { clientLname = conn.rs.getString(8); } if (conn.rs.getString(9) != null) { ccc_no = conn.rs.getString(9); } if (conn.rs.getString(10) != null) { mobile_no = conn.rs.getString(10); } if (conn.rs.getString(11) != null) { gender = conn.rs.getString(11); } if (conn.rs.getString(12) != null) { dob = conn.rs.getString(12); } if (conn.rs.getString(13) != null) { marital_status = conn.rs.getString(13); } if (conn.rs.getString(14) != null) { employment_status = conn.rs.getString(14); } if (conn.rs.getString(15) != null) { education_level = conn.rs.getString(15); } if (conn.rs.getString(16) != null) { art_status = conn.rs.getString(16); } if (conn.rs.getString(17) != null) { SPFname = conn.rs.getString(17); } if (conn.rs.getString(18) != null) { SPMname = conn.rs.getString(18); } if (conn.rs.getString(19) != null) { SPLname = conn.rs.getString(19); } if (conn.rs.getString(20) != null) { healthFacility = conn.rs.getString(20); } if (conn.rs.getString(21) != null) { lessons_attended = conn.rs.getString(21); } if (conn.rs.getString(22) != null) { agebracket = conn.rs.getString(22); } if (conn.rs.getString(23) != null) { gender = conn.rs.getString(23); } if (conn.rs.getString(24) != null) { s1 = conn.rs.getString(24); } if (conn.rs.getString(25) != null) { s2 = conn.rs.getString(25); } if (conn.rs.getString(26) != null) { s3 = conn.rs.getString(26); } if (conn.rs.getString(27) != null) { s4 = conn.rs.getString(27); } if (conn.rs.getString(28) != null) { s5 = conn.rs.getString(28); } if (conn.rs.getString(29) != null) { s6 = conn.rs.getString(29); } if (conn.rs.getString(30) != null) { s7 = conn.rs.getString(30); } if (conn.rs.getString(31) != null) { s8 = conn.rs.getString(31); } if (conn.rs.getString(32) != null) { s9 = conn.rs.getString(32); } if (conn.rs.getString(33) != null) { s10 = conn.rs.getString(33); } if (conn.rs.getString(34) != null) { s11 = conn.rs.getString(34); } if (conn.rs.getString(35) != null) { s12 = conn.rs.getString(35); } if (conn.rs.getString(36) != null) { s13 = conn.rs.getString(36); } if (s1.equals("5")) { s1 = ""; } if (s1.equals("2")) { s1 = "0"; } if (s2.equals("5")) { s2 = ""; } if (s2.equals("2")) { s2 = "0"; } if (s3.equals("5")) { s3 = ""; } if (s3.equals("2")) { s3 = "0"; } if (s4.equals("5")) { s4 = ""; } if (s4.equals("2")) { s4 = "0"; } if (s5.equals("5")) { s5 = ""; } if (s5.equals("2")) { s5 = "0"; } if (s6.equals("5")) { s6 = ""; } if (s6.equals("2")) { s6 = "0"; } if (s7.equals("5")) { s7 = ""; } if (s7.equals("2")) { s7 = "0"; } if (s8.equals("5")) { s8 = ""; } if (s8.equals("2")) { s8 = "0"; } if (s9.equals("5")) { s9 = ""; } if (s9.equals("2")) { s9 = "0"; } if (s10.equals("5")) { s10 = ""; } if (s10.equals("2")) { s10 = "0"; } if (s11.equals("5")) { s11 = ""; } if (s11.equals("2")) { s11 = "0"; } if (s12.equals("5")) { s12 = ""; } if (s12.equals("2")) { s12 = "0"; } if (s13.equals("5")) { s13 = ""; } if (s13.equals("2")) { s13 = "0"; } if (clientMname.equals(clientLname)) { clientMname = ""; } if (SPMname.equals(SPLname)) { SPMname = ""; } SPFullName = SPFname + " " + SPMname + " " + SPLname; clientFullName = clientFname + " " + clientMname + " " + clientLname; String rawData[] = (countyName + "," + partnerName + "," + districtName + "," + DICName + "," + groupName + "," + clientFullName + "," + ccc_no + "," + mobile_no + "," + gender + "," + dob + "," + marital_status + "," + employment_status + "," + education_level + "," + art_status + "," + SPFullName + "," + healthFacility + "," + lessons_attended + "," + agebracket + "," + s1 + "," + s2 + "," + s3 + "," + s4 + "," + s5 + "," + s6 + "," + s7 + "," + s8 + "," + s9 + "," + s10 + "," + s11 + "," + s12 + "," + s13).split(","); Row rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw1.createCell(i); cell.setCellStyle(styleBorder); } for (int i = 0; i <= (rawData.length - 1); i++) { cell = rw1.getCell(i); cell.setCellValue(rawData[i]); } position++; System.out.println("at position : " + position); } IdGenerator CRT = new IdGenerator(); 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_CLIENTS_NOT_REACHED_REPORT_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }