List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellValue
@Override public void setCellValue(boolean value)
From source file:packtest.AligningCells.java
License:Apache License
/** * Creates a cell and aligns it a certain way. * * @param wb the workbook/*from www. j av a 2s . co m*/ * @param row the row to create the cell in * @param column the column number to create the cell in * @param halign the horizontal alignment for the cell. */ private static void createCell(XSSFWorkbook wb, XSSFRow row, short column, short halign, short valign) { XSSFCell cell = row.createCell(column); cell.setCellValue(new XSSFRichTextString("Align It")); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cell.setCellStyle(cellStyle); }
From source file:packtest.AligningCells.java
License:Apache License
/** * Center a text over multiple columns using ALIGN_CENTER_SELECTION * * @param wb the workbook/* w ww .j a v a 2s . c om*/ * @param row the row to create the cell in * @param start_column the column number to create the cell in and where the selection starts * @param end_column the column number where the selection ends * @param valign the horizontal alignment for the cell. * * @author Cristian Petrula, Romania */ private static void centerAcrossSelection(XSSFWorkbook wb, XSSFRow row, short start_column, short end_column, short valign) { // Create cell style with ALIGN_CENTER_SELECTION XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION); cellStyle.setVerticalAlignment(valign); // Create cells over the selected area for (int i = start_column; i <= end_column; i++) { XSSFCell cell = row.createCell(i); cell.setCellStyle(cellStyle); } // Set value to the first cell XSSFCell cell = row.getCell(start_column); cell.setCellValue(new XSSFRichTextString("Align It")); // Make the selection CTRowImpl ctRow = (CTRowImpl) row.getCTRow(); // Add object with format start_coll:end_coll. For example 1:3 will span from // cell 1 to cell 3, where the column index starts with 0 // // You can add multiple spans for one row Object span = start_column + ":" + end_column; List<Object> spanList = new ArrayList<Object>(); spanList.add(span); //add spns to the row ctRow.setSpans(spanList); }
From source file:packtest.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);/*from w w w . jav a 2 s .c o m*/ XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); wb.close(); }
From source file:packtest.CreateTable.java
License:Apache License
public static void main(String[] args) throws FileNotFoundException, IOException { Workbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet) wb.createSheet(); //Create /*from w w w . jav a 2 s . c o m*/ XSSFTable table = sheet.createTable(); table.setDisplayName("Test"); CTTable cttable = table.getCTTable(); //Style configurations CTTableStyleInfo style = cttable.addNewTableStyleInfo(); style.setName("TableStyleMedium2"); style.setShowColumnStripes(false); style.setShowRowStripes(true); //Set which area the table should be placed in AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(2, 2)); cttable.setRef(reference.formatAsString()); cttable.setId(1); cttable.setName("Test"); cttable.setTotalsRowCount(1); CTTableColumns columns = cttable.addNewTableColumns(); columns.setCount(3); CTTableColumn column; XSSFRow row; XSSFCell cell; for (int i = 0; i < 3; i++) { //Create column column = columns.addNewTableColumn(); column.setName("Column"); column.setId(i + 1); //Create row row = sheet.createRow(i); for (int j = 0; j < 3; j++) { //Create cell cell = row.createCell(j); if (i == 0) { cell.setCellValue("Column" + j); } else { cell.setCellValue("0"); } } } FileOutputStream fileOut = new FileOutputStream(Utils.getPath("ooxml-table.xlsx")); wb.write(fileOut); fileOut.close(); }
From source file:packtest.WorkingWithRichText.java
License:Apache License
public static void main(String[] args) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); try {// www . j a v a 2 s . c om XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow((short) 2); XSSFCell cell = row.createCell(1); XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox"); XSSFFont font1 = wb.createFont(); font1.setBold(true); font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0))); rt.applyFont(0, 10, font1); XSSFFont font2 = wb.createFont(); font2.setItalic(true); font2.setUnderline(XSSFFont.U_DOUBLE); font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0))); rt.applyFont(10, 19, font2); XSSFFont font3 = wb.createFont(); font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255))); rt.append(" Jumped over the lazy dog", font3); cell.setCellValue(rt); // Write the output to a file OutputStream fileOut = new FileOutputStream(Utils.getPath("xssf-richtext.xlsx")); try { wb.write(fileOut); } finally { fileOut.close(); } } finally { wb.close(); } }
From source file:poi.xssf.usermodel.examples.AligningCells.java
License:Apache License
/** * Center a text over multiple columns using ALIGN_CENTER_SELECTION * * @param wb the workbook/*from www. j av a2 s .c om*/ * @param row the row to create the cell in * @param start_column the column number to create the cell in and where the selection starts * @param end_column the column number where the selection ends * @param valign the horizontal alignment for the cell. * * @author Cristian Petrula, Romania */ private static void centerAcrossSelection(XSSFWorkbook wb, XSSFRow row, short start_column, short end_column, short valign) { // Create cell style with ALIGN_CENTER_SELECTION XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION); cellStyle.setVerticalAlignment(valign); // Create cells over the selected area for (int i = start_column; i <= end_column; i++) { XSSFCell cell = row.createCell(i); cell.setCellStyle(cellStyle); } // Set value to the first cell XSSFCell cell = row.getCell(start_column); cell.setCellValue(new XSSFRichTextString("Align It")); // Make the selection CTRowImpl ctRow = (CTRowImpl) row.getCTRow(); List spanList = new ArrayList(); // Add object with format start_coll:end_coll. For example 1:3 will span from // cell 1 to cell 3, where the column index starts with 0 // // You can add multiple spans for one row Object span = start_column + ":" + end_column; spanList.add(span); //add spns to the row ctRow.setSpans(spanList); }
From source file:poi.xssf.usermodel.examples.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);//from w w w .j a v a 2s . co m XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); }
From source file:poi.xssf.usermodel.examples.WorkingWithRichText.java
License:Apache License
public static void main(String[] args) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); XSSFSheet sheet = wb.createSheet();//ww w . ja v a 2 s . c o m XSSFRow row = sheet.createRow((short) 2); XSSFCell cell = row.createCell(1); XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox"); XSSFFont font1 = wb.createFont(); font1.setBold(true); font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0))); rt.applyFont(0, 10, font1); XSSFFont font2 = wb.createFont(); font2.setItalic(true); font2.setUnderline(XSSFFont.U_DOUBLE); font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0))); rt.applyFont(10, 19, font2); XSSFFont font3 = wb.createFont(); font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255))); rt.append(" Jumped over the lazy dog", font3); cell.setCellValue(rt); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:relatorios.relItensPreco.java
public void expExcel(String nomeArquivo) throws FileNotFoundException, IOException { database = new conexao(); database.conecta();//from w ww.ja va 2 s .c o m FileInputStream fsIP = new FileInputStream(new File(nomeArquivo)); XSSFWorkbook workbook = new XSSFWorkbook(fsIP); XSSFSheet firstSheet = workbook.getSheetAt(0); FileOutputStream fos = null; XSSFCell cell = null; try { // Este trecho obtem uma lista de objetos do tipo CD // do banco de dados atravs de um DAO e itera sobre a lista // criando linhas e colunas em um arquivo Excel com o contedo // dos objetos. String sql = "select item_id, preco_item, numero_nota_fiscal from entrada_estoque"; database.executaSQL(sql); ResultSet result = database.resultset; String soma = "select sum(preco_item) as Soma_total from entrada_estoque"; database.executaSQL(soma); ResultSet result_soma = database.resultset; int r = 7; /*row.createCell(0).setCellValue("cod".toUpperCase()); row.createCell(1).setCellValue("nome".toUpperCase()); row.createCell(2).setCellValue("preco".toUpperCase()); row.createCell(3).setCellValue("nomecliente".toUpperCase()); row.createCell(4).setCellValue("formaPagamento".toUpperCase()); row.createCell(5).setCellValue("data".toUpperCase());*/ result_soma.first(); XSSFRow x = firstSheet.getRow(4); cell = x.getCell(7); cell.setCellValue(result_soma.getDouble("Soma_total")); int i = 1; while (result.next()) { //row = firstSheet.createRow(i++); XSSFRow sheetrow = firstSheet.getRow(r); if (sheetrow == null) { sheetrow = firstSheet.createRow(r); } //Update the value of cell cell = sheetrow.getCell(7); if (cell == null) { cell = sheetrow.createCell(7); } cell.setCellValue(result.getString("item_id")); cell = sheetrow.getCell(8); if (cell == null) { cell = sheetrow.createCell(8); } cell.setCellValue(result.getInt("numero_nota_fiscal")); cell = sheetrow.getCell(9); if (cell == null) { cell = sheetrow.createCell(9); } cell.setCellValue(result.getDouble("preco_item")); r++; //row.createCell(5).setCellValue(result.getString("data")); } fsIP.close(); JFileChooser arquivo2 = new JFileChooser("E:/"); int retorno = arquivo2.showSaveDialog(null); if (retorno == JFileChooser.APPROVE_OPTION) { String url = arquivo2.getSelectedFile().getAbsolutePath(); //File diretorio = new File(url); //File arquivo = new File(url+".sql"); //fos = new FileOutputStream(nomeArquivo); fos = new FileOutputStream(url + ".xlsx"); workbook.write(fos); } } catch (Exception e) { e.printStackTrace(); System.out.println("Erro ao exportar arquivo"); } finally { try { fos.flush(); fos.close(); } catch (Exception e) { e.printStackTrace(); } } }
From source file:reports.achievedReport.java
public String getAchievedReport(int passedYear, String passedPath, String dates) throws InvalidFormatException, IOException, SQLException { pepfaryear = passedYear;//from w w w. j a v a 2s . c o m full_date = dates; dbConn conn = new dbConn(); pos = 0; incrementor = 0; prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); String allpath = passedPath; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont 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); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle 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); XSSFCellStyle 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); XSSFFont 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, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("PARTNER NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getClients = "SELECT partner.partner_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,personal_information.completionyear," + "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" + ",completionmonth,COUNT(personal_information.client_id) FROM personal_information " + "JOIN partner ON personal_information.partner_id=partner.partner_id " + " WHERE personal_information.completionmonth>0 && personal_information.completionyear>0 GROUP BY partner.partner_name,SEX,personal_information.completionyear,MONTHS,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; partnername = conn.rs.getString(1); month = conn.rs.getString(2); year = conn.rs.getInt(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = year + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); achieved = conn.rs.getInt(7); incrementor += achieved; System.out.println( "date key : " + datekey + "startdate : " + start + " end date : " + end + " year : " + year); if (datekey >= start && datekey <= end && year >= 2014) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(partnername); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } } for (int i = 0; i < myalphabet.length; i++) { try { System.out.println("at position : " + myalphabet[i]); String current_drive = myalphabet[i]; File f3 = new File(current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION"); // CREATE A DIRECTORY AND THE FILE TO HOLD DATA if (f3.exists() && f3.isDirectory()) { path = current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION\\DATA\\REPORTS"; new File(path).mkdirs(); filePath = path + "\\PWP_ATTACHED_REPORT" + full_date + ".xlsm"; } //select the last timestamp which a backup was picked from..... } finally { } } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); 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(); } //if(incrementor==0){ // filePath="noreport"; //} //else{ //// url="no url to the report"; //} return filePath; }