List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion
int addMergedRegion(CellRangeAddress region);
From source file:functions.excels.exports.MaillesParEspeceExcel.java
License:Apache License
public MaillesParEspeceExcel(Map<String, String> info, MaillesParEspece cb) throws IOException { ArrayList<Espece> especes = new ArrayList<Espece>(cb.mailles_par_espece.keySet()); Collections.sort(especes, new Comparator<Espece>() { @Override/*from w w w.j a va 2 s .com*/ public int compare(Espece arg0, Espece arg1) { return arg0.espece_nom.compareTo(arg1.espece_nom); } }); Sheet sheet = wb.createSheet("Mailles par espce"); Espece espece = Espece.find.byId(Integer.parseInt(info.get("espece"))); SousGroupe sous_groupe = SousGroupe.find.byId(Integer.parseInt(info.get("sous_groupe"))); Groupe groupe = Groupe.find.byId(Integer.parseInt(info.get("groupe"))); StadeSexe stade_sexe = StadeSexe.find.byId(Integer.parseInt(info.get("stade"))); //Titre String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Mailles par espce pour "; if (espece != null) titre += espece.espece_nom; else if (sous_groupe != null) titre += " les espces " + sous_groupe; else if (groupe != null) titre += " les espces " + groupe; if (stade_sexe != null) titre += " au stade " + stade_sexe; titre += crLf + "du " + date1 + " au " + date2; int page = 0; int ligne = 7; for (Espece especeATraiter : especes) { //Coller le logo en haut gauche this.collerLogo(page); HashMap<UTMS, Integer> observations = cb.mailles_par_espece.get(especeATraiter); Row row = sheet.createRow(ligne); sheet.createRow(ligne - 4).createCell(4).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(ligne - 4, //first row (0-based) ligne - 3, //last row (0-based) 4, //first column (0-based) 8 //last column (0-based) )); row.createCell(0).setCellValue(especeATraiter.espece_nom); sheet.addMergedRegion(new CellRangeAddress(ligne, //first row (0-based) ligne, //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) )); ligne++; row = sheet.createRow(ligne); row.createCell(0).setCellValue("Maille"); row.createCell(1).setCellValue("Nbre Obs."); ligne++; boolean ecritAGauche = true; for (UTMS utm : UTMS.findAll()) { int nbrObs = observations.get(utm); if (nbrObs != 0) { if (ecritAGauche) { row = sheet.createRow(ligne); row.createCell(0).setCellValue(utm.utm); row.createCell(1).setCellValue(nbrObs); ligne++; } else { row = sheet.getRow(ligne); row.createCell(2).setCellValue(utm.utm); row.createCell(3).setCellValue(nbrObs); ligne++; } if (ligne % LIGNES == (LIGNES - 2)) { if (ecritAGauche) { ecritAGauche = !ecritAGauche; ligne -= (LIGNES - 10); row = sheet.getRow(ligne); row.createCell(2).setCellValue("Maille"); row.createCell(3).setCellValue("Nbre Obs."); } else { ecritAGauche = !ecritAGauche; //On crit le pied de page row = sheet.createRow(ligne + 1); row.createCell(8).setCellValue("Page " + (page + 1)); //On fait une nouvelle page ligne += 10; page++; this.collerLogo(page); sheet.createRow(ligne - 4).createCell(4).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(ligne - 4, //first row (0-based) ligne - 3, //last row (0-based) 4, //first column (0-based) 8 //last column (0-based) )); row = sheet.createRow(ligne); row.createCell(0).setCellValue(especeATraiter.espece_nom); sheet.addMergedRegion(new CellRangeAddress(ligne, //first row (0-based) ligne, //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) )); ligne++; row = sheet.createRow(ligne); row.createCell(0).setCellValue("Maille"); row.createCell(1).setCellValue("Nbre Obs."); } } } } //On passe forcment la page suivante. while (ligne % LIGNES != 7) ligne++; //On crit le pied de page row = sheet.createRow(ligne - 8); row.createCell(8).setCellValue("Page " + (page + 1)); //On ajoute l'image de la carte ! Carte carte = new Carte(observations); this.pasteMap(carte, page); page++; } }
From source file:functions.excels.exports.MaillesParPeriodeExcel.java
License:Apache License
public MaillesParPeriodeExcel(Map<String, String> info, MaillesParPeriode mpp) throws IOException { ArrayList<Espece> especes = new ArrayList<Espece>(mpp.nb_mailles_par_espece.keySet()); Collections.sort(especes, new Comparator<Espece>() { @Override//from w w w . j av a 2 s. co m public int compare(Espece arg0, Espece arg1) { return arg0.espece_nom.compareTo(arg1.espece_nom); } }); Sheet sheet = wb.createSheet("Mailles par espce"); Espece espece = Espece.find.byId(Integer.parseInt(info.get("espece"))); SousGroupe sous_groupe = SousGroupe.find.byId(Integer.parseInt(info.get("sous_groupe"))); Groupe groupe = Groupe.find.byId(Integer.parseInt(info.get("groupe"))); StadeSexe stade_sexe = StadeSexe.find.byId(Integer.parseInt(info.get("stade"))); //Titre String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Mailles par priode pour "; if (espece != null) titre += espece.espece_nom; else if (sous_groupe != null) titre += " les espces " + sous_groupe; else if (groupe != null) titre += " les espces " + groupe; if (stade_sexe != null) titre += " au stade " + stade_sexe; titre += crLf + "du " + date1 + " au " + date2; int page = 0; int ligne = 7; boolean ecritAGauche = true; //Coller le logo en haut gauche this.collerLogo(page); Row row = sheet.createRow(ligne); sheet.createRow(ligne - 4).createCell(4).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(ligne - 4, //first row (0-based) ligne - 3, //last row (0-based) 4, //first column (0-based) 8 //last column (0-based) )); ligne++; for (Espece especeATraiter : especes) { if (ecritAGauche) { row = sheet.createRow(ligne); row.createCell(0).setCellValue(especeATraiter.espece_nom); sheet.addMergedRegion(new CellRangeAddress(ligne, //first row (0-based) ligne, //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) )); row.createCell(2).setCellValue(mpp.nb_mailles_par_espece.get(especeATraiter) + " maille(s)"); ligne++; } else { row = sheet.getRow(ligne); row.createCell(4).setCellValue(especeATraiter.espece_nom); sheet.addMergedRegion(new CellRangeAddress(ligne, //first row (0-based) ligne, //last row (0-based) 4, //first column (0-based) 5 //last column (0-based) )); row.createCell(6).setCellValue(mpp.nb_mailles_par_espece.get(especeATraiter) + " maille(s)"); ligne++; } if (ligne % LIGNES == (LIGNES - 2)) { if (ecritAGauche) { ecritAGauche = !ecritAGauche; ligne -= (LIGNES - 10); } else { ecritAGauche = !ecritAGauche; //On crit le pied de page row = sheet.createRow(ligne + 1); row.createCell(8).setCellValue("Page " + (page + 1)); //On fait une nouvelle page ligne += 10; page++; this.collerLogo(page); sheet.createRow(ligne - 4).createCell(4).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(ligne - 4, //first row (0-based) ligne - 3, //last row (0-based) 4, //first column (0-based) 8 //last column (0-based) )); } } } }
From source file:GapAnalysis.gapAnalysis.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); //PrintWriter out = response.getWriter(); try {//from w w w.java 2 s . c o m ArrayList keyal = new ArrayList(); ArrayList countyal = new ArrayList(); ArrayList scountyal = new ArrayList(); ArrayList facilal = new ArrayList(); ArrayList yearmonthal = new ArrayList(); ArrayList monthal = new ArrayList(); ArrayList sectional = new ArrayList(); XSSFWorkbook wb; String periodname = ""; String allpath = getServletContext().getRealPath("/Gapanalysis.xlsm"); System.out.println(allpath); XSSFWorkbook workbook; String mydrive = allpath.substring(0, 1); // wb = new XSSFWorkbook( OPCPackage.open(allpath) ); Date da = new Date(); String dat2 = da.toString().replace(" ", "_"); dat2 = dat2.toString().replace(":", "_"); String np = mydrive + ":\\APHIAPLUS\\InternalSystem\\Gapanalysis" + dat2 + ".xlsm"; System.out.println("path:: " + np); // String desteepath1 = getServletContext().getRealPath("/Females 15to24.xlsm"); String sr = getServletContext().getRealPath("/Gapanalysis.xlsm"); //check if file exists //first time , it should create those folders that host the macro file File f = new File(np); if (!f.exists() && !f.isDirectory()) { /* do something */ copytemplates ct = new copytemplates(); ct.transfermacros(sr, np); //rem np is the destination file name System.out.println("Copying macro template first time .."); } else //copy the file alone { copytemplates ct = new copytemplates(); //copy the agebased file only ct.copymacros(sr, np); } String filepth = np; File allpathfile = new File(filepth); OPCPackage pkg = OPCPackage.open(allpathfile); pathtodelete = filepth; wb = new XSSFWorkbook(pkg); dbConn conn = new dbConn(); HashMap<String, String> rawdatahashmap = new HashMap<String, String>(); int year = 0; String yearval = ""; int prevyear = 0; String quarter = ""; String yearmonth = ""; String startyearmonth = ""; String endyearmonth = ""; yearval = request.getParameter("year").toString(); System.out.println("YEARVAL" + yearval); year = Integer.parseInt(yearval); prevyear = year - 1; quarter = request.getParameter("quarter"); periodname += yearval + "_"; if (quarter.equals("1")) { startyearmonth = prevyear + "10"; endyearmonth = prevyear + "12"; periodname = prevyear + "_(Oct_Dec)"; } else if (quarter.equals("2")) { startyearmonth = year + "01"; endyearmonth = year + "03"; periodname = yearval + "_(Jan-Mar)"; } else if (quarter.equals("3")) { startyearmonth = year + "04"; endyearmonth = year + "06"; periodname = yearval + "_(Apr_Jun)"; } else if (quarter.equals("4")) { startyearmonth = year + "07"; endyearmonth = year + "09"; periodname = yearval + "_(Jul_Sep)"; } int colsmerging = 6; String Sections[] = { "ART", "HTC", "PMTCT" }; String headers[] = { "County", "Sub-County", "Facility", "Year", "Month" }; String headergsn[] = { "County", "Sub-County", "Facility" }; //if one wants gaps for one service area if (request.getParameterValues("gapsection") != null) { Sections = request.getParameterValues("gapsection"); } //This is the loop that well use to create worksheets for each String period = " 1=1 and Annee=" + yearval + " and yearmonth between " + startyearmonth + " and " + endyearmonth + " "; String gsnperiod = " 1=1 "; //______________________________________________________________________________________ //______________________________________________________________________________________ Font font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 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); CellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.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); CellStyle stylex1 = wb.createCellStyle(); stylex1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex1.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex1.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex1.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex1.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex1.setAlignment(HSSFCellStyle.ALIGN_LEFT); CellStyle stylex2 = wb.createCellStyle(); stylex2.setFillForegroundColor(HSSFColor.SKY_BLUE.index); stylex2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex2.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex2.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex2.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex2.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex2.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex3 = wb.createCellStyle(); stylex3.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); stylex3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex3.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex3.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex3.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex3.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex3.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylex1.setFont(fontx); stylex1.setWrapText(true); stylex2.setFont(fontx); stylex2.setWrapText(true); //================================================== for (int a = 0; a < Sections.length; a++) { int column = 0; int Row = 3; Sheet shet = wb.createSheet(Sections[a]); Row rwx = shet.createRow(2); Row rw1 = null; Row rw2 = null; Row rw = shet.createRow(0); rw.setHeightInPoints(25); Cell cl0 = rw.createCell(0); cl0.setCellValue(Sections[a] + " GAP ANALYSIS"); cl0.setCellStyle(stylex1); //this will depend on the length of the number of elements being checked for (int b = 1; b <= colsmerging; b++) { Cell clx = rw.createCell(b); clx.setCellValue(""); clx.setCellStyle(stylex); } //now go to the database and do a query for each section int determinant = 2; String getqueries = " Select * from gap_analysis where active=1 and section='" + Sections[a] + "' "; conn.rs = conn.st.executeQuery(getqueries); while (conn.rs.next()) { //if an excel sheet exists, then get the row number 1 if (shet.getRow(1) != null) { rw1 = shet.getRow(1); } else { rw1 = shet.createRow(1); rw1.setHeightInPoints(25); } //print blanks before printing real header //for gsns, we only print three columns and no period if (conn.rs.getString("id").equals("1")) { for (int p = 0; p < headergsn.length; p++) { Cell cl2 = rw1.createCell(column + p); cl2.setCellValue(""); cl2.setCellStyle(stylex); shet.setColumnWidth(column + p, 5000); } } else { for (int p = 0; p < headers.length; p++) { Cell cl2 = rw1.createCell(column + p); cl2.setCellValue(""); cl2.setCellStyle(stylex); shet.setColumnWidth(column + p, 5000); } } determinant++; if (determinant % 2 == 0) { Cell cl1 = rw1.createCell(column); cl1.setCellValue(conn.rs.getString("rule")); cl1.setCellStyle(stylex3); } else { Cell cl1 = rw1.createCell(column); cl1.setCellValue(conn.rs.getString("rule")); cl1.setCellStyle(stylex2); } //Create the column header if (shet.getRow(2) != null) { rw2 = shet.getRow(2); } else { rw2 = shet.createRow(2); rw2.setHeightInPoints(25); } if (conn.rs.getString("id").equals("1")) { for (int p = 0; p < headergsn.length; p++) { Cell cl2 = rw2.createCell(column + p); cl2.setCellValue(headergsn[p]); cl2.setCellStyle(stylex); } } else { for (int p = 0; p < headers.length; p++) { Cell cl2 = rw2.createCell(column + p); cl2.setCellValue(headers[p]); cl2.setCellStyle(stylex); } } String currentqry = conn.rs.getString("query"); //process each query as you //pass the necessary period parameters from the interface //rem each query ends with a 'and' if (conn.rs.getString("id").equals("1")) { currentqry += gsnperiod; } else { currentqry += period + " and subpartnera." + Sections[a] + "= 1 "; } System.out.println("" + currentqry); Row = 3; conn.rs1 = conn.st1.executeQuery(currentqry); while (conn.rs1.next()) { if (shet.getRow(Row) != null) { rwx = shet.getRow(Row); } else { rwx = shet.createRow(Row); rwx.setHeightInPoints(25); } Cell cly = rwx.createCell(column); cly.setCellValue(conn.rs1.getString("County")); cly.setCellStyle(style2); Cell cly2 = rwx.createCell(column + 1); cly2.setCellValue(conn.rs1.getString("DistrictNom")); cly2.setCellStyle(style2);//gsn sites do not have a yearmonth Cell cly1 = rwx.createCell(column + 2); cly1.setCellValue(conn.rs1.getString("SubPartnerNom")); cly1.setCellStyle(style2); //if the current list is not inclusive of GSNs if (!conn.rs.getString(1).equals("1")) { Cell cly3 = rwx.createCell(column + 3); cly3.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(0, 4))); cly3.setCellStyle(style2); //the month section Cell cly3x = rwx.createCell(column + 4); cly3x.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(4))); cly3x.setCellStyle(style2); //my key is a String mykey = Sections[a] + conn.rs1.getString("SubPartnerNom") + "_" + conn.rs1.getString("yearmonth") + "_"; //add all the facilities at this point //ignore the sites in ART since they are static if (!keyal.contains(mykey)) { keyal.add(mykey); countyal.add(conn.rs1.getString("County")); scountyal.add(conn.rs1.getString("DistrictNom")); facilal.add(conn.rs1.getString("SubPartnerNom")); sectional.add(Sections[a]); yearmonthal.add(conn.rs1.getString("yearmonth")); monthal.add(conn.rs1.getString("yearmonth").substring(4)); } } Row++; } if (conn.rs.getString(1).equals("1")) { column += 3; } else { column += 5; } if (conn.rs.getString("id").equals("1")) { shet.addMergedRegion(new CellRangeAddress(1, 1, 0, column - 1)); } else { shet.addMergedRegion(new CellRangeAddress(1, 1, column - 5, column - 1)); } } //end of all queries per section shet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1)); } // end of sheets loop //create a new sheet //county subcounty facility yearmonth section Sheet shet = wb.getSheet("Sheet1"); Row rw = shet.createRow(0); Cell cl0 = rw.createCell(0); cl0.setCellValue("county"); cl0.setCellStyle(stylex1); Cell cl1 = rw.createCell(1); cl1.setCellValue("subcounty"); cl1.setCellStyle(stylex1); Cell cl2 = rw.createCell(2); cl2.setCellValue("facility"); cl2.setCellStyle(stylex1); Cell cl3 = rw.createCell(3); cl3.setCellValue("year"); cl3.setCellStyle(stylex1); Cell cl4 = rw.createCell(4); cl4.setCellValue("month"); cl4.setCellStyle(stylex1); Cell cl5 = rw.createCell(5); cl5.setCellValue("section"); cl5.setCellStyle(stylex1); for (int q = 0; q < keyal.size(); q++) { Row rwx = shet.createRow(q + 1); Cell cl01 = rwx.createCell(0); cl01.setCellValue(countyal.get(q).toString()); cl01.setCellStyle(style2); Cell cl11 = rwx.createCell(1); cl11.setCellValue(scountyal.get(q).toString()); cl11.setCellStyle(style2); Cell cl21 = rwx.createCell(2); cl21.setCellValue(facilal.get(q).toString()); cl21.setCellStyle(style2); Cell cl31 = rwx.createCell(3); cl31.setCellValue(new Integer(yearmonthal.get(q).toString().substring(0, 4))); cl31.setCellStyle(style2); Cell cl41 = rwx.createCell(4); cl41.setCellValue(new Integer(monthal.get(q).toString())); cl41.setCellStyle(style2); Cell cl51 = rwx.createCell(5); cl51.setCellValue(sectional.get(q).toString()); cl51.setCellStyle(style2); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); 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=GapAnalysis_For" + periodname + "_Generatted_On_" + createdOn + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); pkg.close(); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st != null) { conn.st.close(); } File file = new File(pathtodelete); System.out.println("path: 2" + pathtodelete); if (file.delete()) { System.out.println(file.getName() + " is deleted!"); } else { System.out.println("Delete operation failed."); } } catch (SQLException ex) { Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex); } finally { } }
From source file:generate.XGenerator.java
public static void doMerge(Sheet worksheet, int rowIndex, int columnIndex, int rowSpan, int columnSpan, boolean border) { range = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, columnIndex, columnIndex + columnSpan - 1); Cell cell = worksheet.getRow(rowIndex).getCell(columnIndex); if (CopyRow.getNbOfMergedRegions(worksheet, rowIndex + rowSpan - 1) <= 0) { worksheet.addMergedRegion(range); }//w ww . j av a2 s. co m if (border == true) { RegionUtil.setBorderTop(CellStyle.BORDER_THIN, range, worksheet); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, range, worksheet); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, range, worksheet); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, range, worksheet); } }
From source file:invoiceapplication.CopyRowOriginal.java
private static void doMerge(Sheet worksheet, int rowIndex, int columnIndex, int rowSpan, int columnSpan) { Cell cell = worksheet.getRow(rowIndex).getCell(columnIndex); CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, columnIndex, columnIndex + columnSpan - 1); worksheet.addMergedRegion(range); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, range, worksheet, worksheet.getWorkbook()); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, range, worksheet, worksheet.getWorkbook()); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, range, worksheet, worksheet.getWorkbook()); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, range, worksheet, worksheet.getWorkbook()); }
From source file:invoiceapplication.CopyRowOriginal.java
private static void copyMergeRegion(Sheet worksheet, Row sourceRow, Row newRow, CellRangeAddress mergedRegion) { CellRangeAddress range = mergedRegion; if (range.getFirstRow() == sourceRow.getRowNum()) { //System.out.println(range.formatAsString()); int lastRow = newRow.getRowNum() + (range.getFirstRow() - range.getLastRow()); worksheet.addMergedRegion(new CellRangeAddress(newRow.getRowNum(), lastRow, range.getFirstColumn(), range.getLastColumn())); }// w ww . j ava2s .c o m }
From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java
License:Mozilla Public License
/** * Builds the rows' headers recursively with this order: * |-----|-----|-----|// w ww . j a v a2 s. c o m * | | | 3 | * | | |-----| * | | 2 | 4 | * | | |-----| * | 1 | | 5 | * | |-----|-----| * | | | 7 | * | | 6 |-----| * | | | 8 | * |-----|-----|-----| * | | | 11 | * | 9 | 10 |-----| * | | | 12 | * |-----|-----|-----| * * @param sheet The sheet of the XLS file * @param siblings The siblings nodes of the headers structure * @param rowNum The row number where the first sibling must be inserted * @param columnNum The column number where the siblings must be inserted * @param createHelper The file creation helper * @throws JSONException */ protected void buildRowsHeaders(Sheet sheet, CrossTab cs, List<Node> siblings, int rowNum, int columnNum, CreationHelper createHelper, Locale locale, CellStyle cellStyle) throws JSONException { int rowsCounter = rowNum; for (int i = 0; i < siblings.size(); i++) { Node aNode = siblings.get(i); List<Node> childs = aNode.getChilds(); Row row = sheet.getRow(rowsCounter); Cell cell = row.createCell(columnNum); String text = (String) aNode.getDescription(); if (cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) { //apply the measure scale factor text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale); } cell.setCellValue(createHelper.createRichTextString(text)); cell.setCellType(this.getCellTypeString()); cell.setCellStyle(cellStyle); int descendants = aNode.getLeafsNumber(); if (descendants > 1) { sheet.addMergedRegion(new CellRangeAddress(rowsCounter, //first row (0-based) rowsCounter + descendants - 1, //last row (0-based) columnNum, //first column (0-based) columnNum //last column (0-based) )); } if (childs != null && childs.size() > 0) { buildRowsHeaders(sheet, cs, childs, rowsCounter, columnNum + 1, createHelper, locale, cellStyle); } int increment = descendants > 1 ? descendants : 1; rowsCounter = rowsCounter + increment; } }
From source file:it.eng.spagobi.engines.qbe.crosstable.exporter.CrosstabXLSExporter.java
License:Mozilla Public License
/** * Builds the columns' headers recursively with this order: * |------------------------------------------| * | 1 | 9 | * |------------------------------------------| * | 2 | 5 | 10 | * |-----------|-----------------|------------| * | 3 | 4 | 6 | 7 | 8 | 11 | 12 | * |------------------------------------------| * /*from ww w . j av a2 s . c o m*/ * @param sheet The sheet of the XLS file * @param siblings The siblings nodes of the headers structure * @param rowNum The row number where the siblings must be inserted * @param columnNum The column number where the first sibling must be inserted * @param createHelper The file creation helper * @param dimensionCellStyle The cell style for cells containing dimensions (i.e. attributes' names) * @param memberCellStyle The cell style for cells containing members (i.e. attributes' values) * @throws JSONException */ protected void buildColumnsHeader(Sheet sheet, CrossTab cs, List<Node> siblings, int rowNum, int columnNum, CreationHelper createHelper, Locale locale, CellStyle memberCellStyle, CellStyle dimensionCellStyle) throws JSONException { int columnCounter = columnNum; for (int i = 0; i < siblings.size(); i++) { Node aNode = (Node) siblings.get(i); List<Node> childs = aNode.getChilds(); Row row = sheet.getRow(rowNum); Cell cell = row.createCell(columnCounter); String text = (String) aNode.getDescription(); if (!cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) { //apply the measure scale factor text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale); } cell.setCellValue(createHelper.createRichTextString(text)); cell.setCellType(this.getCellTypeString()); int descendants = aNode.getLeafsNumber(); if (descendants > 1) { sheet.addMergedRegion(new CellRangeAddress(rowNum, //first row (0-based) rowNum, //last row (0-based) columnCounter, //first column (0-based) columnCounter + descendants - 1 //last column (0-based) )); } /* * Now we have to set the style properly according to the nature of * the node: if it contains the name of a dimension or a member. * Since the structure foresees that a list of members follows a * dimension, we calculate the position of the node with respect to * the leaves; in case it is odd, the cell contains a dimension; in * case it is even, the cell contains a dimension. */ int distanceToLeaves = aNode.getDistanceFromLeaves(); if (!cs.isMeasureOnRow()) { distanceToLeaves--; } boolean isDimensionNameCell = distanceToLeaves > 0 && (distanceToLeaves % 2) == 1; if (isDimensionNameCell) { cell.setCellStyle(dimensionCellStyle); } else { cell.setCellStyle(memberCellStyle); } if (childs != null && childs.size() > 0) { buildColumnsHeader(sheet, cs, childs, rowNum + 1, columnCounter, createHelper, locale, memberCellStyle, dimensionCellStyle); } int increment = descendants > 1 ? descendants : 1; columnCounter = columnCounter + increment; } }
From source file:it.redev.parco.ext.ExportableModelEntityQuery.java
License:Open Source License
private void exportHeaders(Sheet sheet) { Row row0 = sheet.createRow(0);/*ww w .jav a2 s . c om*/ Row row1 = sheet.createRow(1); int coll = 0; for (Header header : headers) { Cell cell0 = row0.createCell(coll); cell0.setCellValue(StringUtils.capitalizeMethodName(header.clazz.getSimpleName())); CellRangeAddress region = new CellRangeAddress(0, 0, coll, coll + header.properties.size() - 1); sheet.addMergedRegion(region); CellStyle style = workbook.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); cell0.setCellStyle(style); for (String pd : header.properties) { Cell cell1 = row1.createCell(coll); cell1.setCellValue(StringUtils.capitalizeMethodName(pd)); coll++; } } }
From source file:itpreneurs.itp.report.archive.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//w w w . j a v a 2 s .c om if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/loan-calculator.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }