I'm using apache poi to generate an excel file document, i'am importing data from database, and i use two queries for that, but when i'am filling cells, but i have a gap between results of the two queries like in the picture below (pic 1)
and normaly it should be like this (pic2)
here is the code i used for that
public static void Excel () { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet1 = workbook.createSheet("Etat");//***************HEADER Cell cell1 = sheet1.createRow(2).createCell(1); cell1.setCellValue("NUMEROS"); sheet1.addMergedRegion(new CellRangeAddress(2,2,1,4)); // new Region(2,(short)1,2,(short)4)); XSSFCellStyle CellST = workbook.createCellStyle(); XSSFCellStyle CellST2 = workbook.createCellStyle(); XSSFFont fonte = workbook.createFont(); fonte.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fonte.setFontHeightInPoints((short) 7); fonte.setFontName("Courier New"); CellST.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellST.setFont(fonte); CellST.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); CellST.setFillPattern(CellStyle.SOLID_FOREGROUND); CellST.setBottomBorderColor(Red); cell1.setCellStyle(CellST); Cell cell2 = sheet1.createRow(3).createCell(1); cell2.setCellValue("D'Or-"); cell2.setCellStyle(CellST); Cell cell3 = sheet1.createRow(4).createCell(1); cell3.setCellValue("dre"); cell3.setCellStyle(CellST); Cell cell4 = sheet1.getRow(3).createCell(2); cell4.setCellValue("des"); cell4.setCellStyle(CellST); Cell cell5 = sheet1.getRow(4).createCell(2); cell5.setCellValue("requisitions"); cell5.setCellStyle(CellST);.........//*****************QUERIES & REPORT GENERATIONtry { String sql1 = "select ordre.num_ordre , parcelle.code_parcelle, parcelle.num_req , prop_dite.lbl_fr, " + "prop_dite.lbl_ar , parcelle.surface_adop , mappe.mappe," + "(select array_to_string(array_agg(consistance.lib_consistance), '+'::text)" + "from consistance where id_consistance in (select id_consistance from " + "consist_parcelle where id_parcelle = parcelle.id_parcelle)) AS consistance " + "from ordre , parcelle , prop_dite , mappe ,mappe_parcelle where " + "ordre.id_parcelle = parcelle.id_parcelle and prop_dite.id_prop = " + "parcelle.id_prop and mappe_parcelle.id_parcelle= parcelle.id_parcelle and " + "mappe_parcelle.id_mappe= mappe.id_mappe and mappe_parcelle.priorite=0"; String sql2 = "select ordre.ordre_ser,( ordre.id_personne, ordre.cts, ordre.htiers ) ," + "CASE when ordre.htiers = 1 then 'Heritiers de '::text else ''::text END || " + "(personne.nom_pers::text || ' '::text) || personne.prenom_pers::text" + "|| case when ordre.cts = 1 then ' et CTS'::text else ''::text END " + "AS lbl, adresse.lib_adresse , adresse.lib_adresse_ar from personne " + ", ordre , adresse where adresse.id_adresse = personne.id_adresse " + "and personne.id_personne = ordre.id_personne order by ordre.ordre_ser ; "; Connection conn = conectar(); Statement st = conn.createStatement(); ResultSet rs1 = st.executeQuery(sql1); int i =5; while (rs1.next()) { Cell cell25 = sheet1.createRow(i).createCell(1); cell25.setCellValue(rs1.getString("num_ordre")); cell25.setCellStyle(CellST); Cell cell26 = sheet1.getRow(i).createCell(4); cell26.setCellValue(rs1.getString("code_parcelle")); cell26.setCellStyle(CellST); Cell cell27 = sheet1.getRow(i).createCell(4); cell27.setCellValue(rs1.getString("code_parcelle")); cell27.setCellStyle(CellST); int rowcount = rs1.getRow(); Cell cell31 = sheet1.getRow(i).createCell(9); cell31.setCellValue(rs1.getString("lbl_fr")); cell31.setCellStyle(CellST); Cell cell32 = sheet1.getRow(i).createCell(10); cell32.setCellValue(rs1.getString("lbl_ar")); cell32.setCellStyle(CellST); Cell cell33 = sheet1.getRow(i).createCell(11); cell33.setCellValue(rs1.getString("surface_adop")); cell33.setCellStyle(CellST); Cell cell34 = sheet1.getRow(i).createCell(12); cell34.setCellValue(rs1.getString("mappe")); cell34.setCellStyle(CellST); Cell cell35 = sheet1.getRow(i).createCell(13); cell35.setCellValue(rs1.getString("consistance")); cell35.setCellStyle(CellST); i++; } rs1.close(); st.close(); Statement st2 = conn.createStatement(); ResultSet rs2 = st2.executeQuery(sql2); int j =5; while (rs2.next()) { Cell cell28 = sheet1.createRow(5).createCell(5); cell28.setCellValue(rs2.getString("lbl")); cell28.setCellStyle(CellST); Cell cell29 = sheet1.getRow(j).createCell(7); cell29.setCellValue(rs2.getString("lib_adresse")); cell29.setCellStyle(CellST); Cell cell30 = sheet1.getRow(j).createCell(8); cell30.setCellValue(rs2.getString("lib_adresse_ar")); cell30.setCellStyle(CellST); int rowcount2 = rs2.getRow(); j++; } rs2.close(); st2.close(); DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy"); Calendar cal = Calendar.getInstance(); String date = dateFormat.format(cal.getTime()); FileOutputStream fileOut = new FileOutputStream("C:\\Saadia\\Etatxxx"+date+".xlsx"); workbook.write(fileOut); fileOut.close(); System.out.println("Your excel file has been generated!");}catch (Exception e ) { e.printStackTrace();}}I think the problem cames from cell's number and the while loop, i made many changes but they didn't work, hope to help to fix the issue of this.
Any help will be appreciated
أكثر...
and normaly it should be like this (pic2)
here is the code i used for that
public static void Excel () { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet1 = workbook.createSheet("Etat");//***************HEADER Cell cell1 = sheet1.createRow(2).createCell(1); cell1.setCellValue("NUMEROS"); sheet1.addMergedRegion(new CellRangeAddress(2,2,1,4)); // new Region(2,(short)1,2,(short)4)); XSSFCellStyle CellST = workbook.createCellStyle(); XSSFCellStyle CellST2 = workbook.createCellStyle(); XSSFFont fonte = workbook.createFont(); fonte.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fonte.setFontHeightInPoints((short) 7); fonte.setFontName("Courier New"); CellST.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellST.setFont(fonte); CellST.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); CellST.setFillPattern(CellStyle.SOLID_FOREGROUND); CellST.setBottomBorderColor(Red); cell1.setCellStyle(CellST); Cell cell2 = sheet1.createRow(3).createCell(1); cell2.setCellValue("D'Or-"); cell2.setCellStyle(CellST); Cell cell3 = sheet1.createRow(4).createCell(1); cell3.setCellValue("dre"); cell3.setCellStyle(CellST); Cell cell4 = sheet1.getRow(3).createCell(2); cell4.setCellValue("des"); cell4.setCellStyle(CellST); Cell cell5 = sheet1.getRow(4).createCell(2); cell5.setCellValue("requisitions"); cell5.setCellStyle(CellST);.........//*****************QUERIES & REPORT GENERATIONtry { String sql1 = "select ordre.num_ordre , parcelle.code_parcelle, parcelle.num_req , prop_dite.lbl_fr, " + "prop_dite.lbl_ar , parcelle.surface_adop , mappe.mappe," + "(select array_to_string(array_agg(consistance.lib_consistance), '+'::text)" + "from consistance where id_consistance in (select id_consistance from " + "consist_parcelle where id_parcelle = parcelle.id_parcelle)) AS consistance " + "from ordre , parcelle , prop_dite , mappe ,mappe_parcelle where " + "ordre.id_parcelle = parcelle.id_parcelle and prop_dite.id_prop = " + "parcelle.id_prop and mappe_parcelle.id_parcelle= parcelle.id_parcelle and " + "mappe_parcelle.id_mappe= mappe.id_mappe and mappe_parcelle.priorite=0"; String sql2 = "select ordre.ordre_ser,( ordre.id_personne, ordre.cts, ordre.htiers ) ," + "CASE when ordre.htiers = 1 then 'Heritiers de '::text else ''::text END || " + "(personne.nom_pers::text || ' '::text) || personne.prenom_pers::text" + "|| case when ordre.cts = 1 then ' et CTS'::text else ''::text END " + "AS lbl, adresse.lib_adresse , adresse.lib_adresse_ar from personne " + ", ordre , adresse where adresse.id_adresse = personne.id_adresse " + "and personne.id_personne = ordre.id_personne order by ordre.ordre_ser ; "; Connection conn = conectar(); Statement st = conn.createStatement(); ResultSet rs1 = st.executeQuery(sql1); int i =5; while (rs1.next()) { Cell cell25 = sheet1.createRow(i).createCell(1); cell25.setCellValue(rs1.getString("num_ordre")); cell25.setCellStyle(CellST); Cell cell26 = sheet1.getRow(i).createCell(4); cell26.setCellValue(rs1.getString("code_parcelle")); cell26.setCellStyle(CellST); Cell cell27 = sheet1.getRow(i).createCell(4); cell27.setCellValue(rs1.getString("code_parcelle")); cell27.setCellStyle(CellST); int rowcount = rs1.getRow(); Cell cell31 = sheet1.getRow(i).createCell(9); cell31.setCellValue(rs1.getString("lbl_fr")); cell31.setCellStyle(CellST); Cell cell32 = sheet1.getRow(i).createCell(10); cell32.setCellValue(rs1.getString("lbl_ar")); cell32.setCellStyle(CellST); Cell cell33 = sheet1.getRow(i).createCell(11); cell33.setCellValue(rs1.getString("surface_adop")); cell33.setCellStyle(CellST); Cell cell34 = sheet1.getRow(i).createCell(12); cell34.setCellValue(rs1.getString("mappe")); cell34.setCellStyle(CellST); Cell cell35 = sheet1.getRow(i).createCell(13); cell35.setCellValue(rs1.getString("consistance")); cell35.setCellStyle(CellST); i++; } rs1.close(); st.close(); Statement st2 = conn.createStatement(); ResultSet rs2 = st2.executeQuery(sql2); int j =5; while (rs2.next()) { Cell cell28 = sheet1.createRow(5).createCell(5); cell28.setCellValue(rs2.getString("lbl")); cell28.setCellStyle(CellST); Cell cell29 = sheet1.getRow(j).createCell(7); cell29.setCellValue(rs2.getString("lib_adresse")); cell29.setCellStyle(CellST); Cell cell30 = sheet1.getRow(j).createCell(8); cell30.setCellValue(rs2.getString("lib_adresse_ar")); cell30.setCellStyle(CellST); int rowcount2 = rs2.getRow(); j++; } rs2.close(); st2.close(); DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy"); Calendar cal = Calendar.getInstance(); String date = dateFormat.format(cal.getTime()); FileOutputStream fileOut = new FileOutputStream("C:\\Saadia\\Etatxxx"+date+".xlsx"); workbook.write(fileOut); fileOut.close(); System.out.println("Your excel file has been generated!");}catch (Exception e ) { e.printStackTrace();}}I think the problem cames from cell's number and the while loop, i made many changes but they didn't work, hope to help to fix the issue of this.
Any help will be appreciated
أكثر...