List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion
int addMergedRegion(CellRangeAddress region);
From source file:forseti.reportes.JReportesDlg.java
License:Open Source License
public void generarArchivoXLS(HttpServletRequest request, HttpServletResponse response, Workbook wb) throws ServletException, IOException { JReportesSet m_RepSet = (JReportesSet) request.getAttribute("m_RepSet"); JReportesLevel1 m_setL1 = (JReportesLevel1) request.getAttribute("m_setL1"); JReportesCompL1Set m_setCL1 = (JReportesCompL1Set) request.getAttribute("m_setCL1"); Boolean m_bSelectL1 = (Boolean) request.getAttribute("m_bSelectL1"); Boolean m_bSelectL2 = (Boolean) request.getAttribute("m_bSelectL2"); Boolean m_bSelectL3 = (Boolean) request.getAttribute("m_bSelectL3"); Boolean m_bComputeL1 = (Boolean) request.getAttribute("m_bComputeL1"); Boolean m_bComputeL2 = (Boolean) request.getAttribute("m_bComputeL2"); Boolean m_bComputeL3 = (Boolean) request.getAttribute("m_bComputeL3"); JReportesBind3Set m_colL1 = (JReportesBind3Set) request.getAttribute("m_colL1"); JReportesBind3Set m_colL2 = (JReportesBind3Set) request.getAttribute("m_colL2"); JReportesBind3Set m_colL3 = (JReportesBind3Set) request.getAttribute("m_colL3"); JReportesBind3Set m_colCL1 = (JReportesBind3Set) request.getAttribute("m_colCL1"); JReportesBind3Set m_colCL2 = (JReportesBind3Set) request.getAttribute("m_colCL2"); JReportesBind3Set m_colCL3 = (JReportesBind3Set) request.getAttribute("m_colCL3"); String fsi_filtro = (String) request.getAttribute("fsi_filtro"); Sheet sheet = wb.createSheet("reporte " + Integer.toString(m_RepSet.getAbsRow(0).getID_Report())); short nrow = 0; Row row = sheet.createRow(nrow++);//from w ww . j a v a 2 s . c o m Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.DARK_BLUE.index); Cell cell = row.createCell(0); cell.setCellValue(m_RepSet.getAbsRow(0).getDescription() + " " + fsi_filtro); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); cellStyle.setFont(font); cell.setCellStyle(cellStyle); JRepCellStyles cellStyles = new JRepCellStyles(wb); /* Font fenc = wb.createFont(); fenc.setBoldweight(Font.BOLDWEIGHT_BOLD); fenc.setColor(HSSFColor.WHITE.index); Font fnorm = wb.createFont(); fnorm.setColor(HSSFColor.BLACK.index); /////////////////////////////////////////////////////////////////////////////////////////////////////// CellStyle cellStyle = wb.createCellStyle(); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); if(alin != null) { if(alin.equals("center")) cellStyle.setAlignment(CellStyle.ALIGN_CENTER); else if(alin.equals("right")) cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); else cellStyle.setAlignment(CellStyle.ALIGN_LEFT); } if(tipocel != null) { if(tipocel.equals("encabezado")) { cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); cellStyle.setFillPattern(CellStyle.BIG_SPOTS); } else if(tipocel.equals("agregado")) { cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); cellStyle.setFillPattern(CellStyle.BIG_SPOTS); } } /////////////////////////////////////////////////////////////////////////////////////////////////////// */ if (m_bSelectL1.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL1.getNumRows(); i++) { if (m_colL1.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL1.getAbsRow(i).getColName().toUpperCase(), "general", "STRING", m_colL1.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll1.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll1.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL2.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL2.getNumRows(); i++) { if (m_colL2.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL2.getAbsRow(i).getColName().toUpperCase(), "general", "STRING", m_colL2.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll2.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll2.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL3.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL3.getNumRows(); i++) { if (m_colL3.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL3.getAbsRow(i).getColName().toUpperCase(), "general", "STRING", m_colL3.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll3.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll3.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL1.booleanValue()) { for (int RL1 = 0; RL1 < m_setL1.getNumRows(); RL1++) { Row rowl1 = sheet.createRow(nrow++); for (int CL1 = 0; CL1 < m_colL1.getNumRows(); CL1++) { if (m_colL1.getAbsRow(CL1).getWillShow()) JUtil.DatoXLS(cellStyles, rowl1, CL1, m_setL1.getAbsRow(RL1).getSTS(m_colL1.getAbsRow(CL1).getColName()), m_colL1.getAbsRow(CL1).getFormat(), m_colL1.getAbsRow(CL1).getBindDataType(), m_colL1.getAbsRow(CL1).getAlinHor(), null, "fnorm", request); } // Nivel 2 if (m_bSelectL2.booleanValue()) { for (int RL2 = 0; RL2 < m_setL1.getAbsRow(RL1).getSetL2().getNumRows(); RL2++) { Row rowl2 = sheet.createRow(nrow++); for (int CL2 = 0; CL2 < m_colL2.getNumRows(); CL2++) { if (m_colL2.getAbsRow(CL2).getWillShow()) JUtil.DatoXLS(cellStyles, rowl2, CL2, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2) .getSTS(m_colL2.getAbsRow(CL2).getColName()), m_colL2.getAbsRow(CL2).getFormat(), m_colL2.getAbsRow(CL2).getBindDataType(), m_colL2.getAbsRow(CL2).getAlinHor(), null, "fnorm", request); } // Nivel 3 if (m_bSelectL3.booleanValue()) { for (int RL3 = 0; RL3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3() .getNumRows(); RL3++) { Row rowl3 = sheet.createRow(nrow++); for (int CL3 = 0; CL3 < m_colL3.getNumRows(); CL3++) { if (m_colL3.getAbsRow(CL3).getWillShow()) JUtil.DatoXLS(cellStyles, rowl3, CL3, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3() .getAbsRow(RL3).getSTS(m_colL3.getAbsRow(CL3).getColName()), m_colL3.getAbsRow(CL3).getFormat(), m_colL3.getAbsRow(CL3).getBindDataType(), m_colL3.getAbsRow(CL3).getAlinHor(), null, "fnorm", request); } } if (m_bComputeL3.booleanValue()) { for (int RC3 = 0; RC3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3() .getNumRows(); RC3++) { Row rowc3 = sheet.createRow(nrow++); for (int CC3 = 0; CC3 < m_colCL3.getNumRows(); CC3++) { if (m_colCL3.getAbsRow(CC3).getWillShow()) JUtil.DatoXLS(cellStyles, rowc3, CC3, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3() .getAbsRow(RC3) .getSTS(m_colCL3.getAbsRow(CC3).getColName()), m_colCL3.getAbsRow(CC3).getFormat(), m_colCL3.getAbsRow(CC3).getBindDataType(), m_colCL3.getAbsRow(CC3).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL3 } // Fin SI L3 } if (m_bComputeL2.booleanValue()) { for (int RC2 = 0; RC2 < m_setL1.getAbsRow(RL1).getSetCL2().getNumRows(); RC2++) { Row rowc2 = sheet.createRow(nrow++); for (int CC2 = 0; CC2 < m_colCL2.getNumRows(); CC2++) { if (m_colCL2.getAbsRow(CC2).getWillShow()) JUtil.DatoXLS(cellStyles, rowc2, CC2, m_setL1.getAbsRow(RL1).getSetCL2().getAbsRow(RC2) .getSTS(m_colCL2.getAbsRow(CC2).getColName()), m_colCL2.getAbsRow(CC2).getFormat(), m_colCL2.getAbsRow(CC2).getBindDataType(), m_colCL2.getAbsRow(CC2).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL2 } // Fin SI L2 } if (m_bComputeL1.booleanValue()) { for (int RC1 = 0; RC1 < m_setCL1.getNumRows(); RC1++) { Row rowc1 = sheet.createRow(nrow++); for (int CC1 = 0; CC1 < m_colCL1.getNumRows(); CC1++) { if (m_colCL1.getAbsRow(CC1).getWillShow()) JUtil.DatoXLS(cellStyles, rowc1, CC1, m_setCL1.getAbsRow(RC1).getSTS(m_colCL1.getAbsRow(CC1).getColName()), m_colCL1.getAbsRow(CC1).getFormat(), m_colCL1.getAbsRow(CC1).getBindDataType(), m_colCL1.getAbsRow(CC1).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL1 } // Fin SI L1 int colsmer; if (m_colL1.getNumRows() > m_colL2.getNumRows() && m_colL1.getNumRows() > m_colL3.getNumRows()) colsmer = m_colL1.getNumRows() - 1; else if (m_colL2.getNumRows() > m_colL1.getNumRows() && m_colL2.getNumRows() > m_colL3.getNumRows()) colsmer = m_colL2.getNumRows() - 1; else colsmer = m_colL3.getNumRows() - 1; sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colsmer == -1 ? 0 : colsmer))); }
From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java
License:Apache License
/** * Ajoute les en-ttes dans la feuille de calcul et cache les colonnes qui doivent l'tre. * /*from w ww. j a va 2s. c om*/ * @param sheet feuille de calcul * @param rowIndex numro de la ligne * @param columnInfos RangeMap contenant les informations de colonnes (valeurs) et les index sur auxquelles s'appliquent ces colonnes (cls). * Les "colonnes" s'tendant sur plus d'un index seront automatiquement fusionnes. */ protected void addHeadersToSheet(Sheet sheet, int rowIndex, RangeMap<Integer, ColumnInformation> columnInfos) { Row rowHeader = sheet.createRow(rowIndex); for (Map.Entry<Range<Integer>, ColumnInformation> entry : columnInfos.asMapOfRanges().entrySet()) { Range<Integer> range = entry.getKey(); ColumnInformation columnInformation = entry.getValue(); addHeaderCell(rowHeader, range.lowerEndpoint(), getColumnLabel(columnInformation.getHeaderKey())); for (Integer columnIndex : ContiguousSet.create(range, DiscreteDomain.integers())) { sheet.setColumnHidden(columnIndex, columnInformation.isHidden()); } int beginIndex = range.lowerEndpoint(); int endIndex = range.upperEndpoint(); if (beginIndex != endIndex) { sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, beginIndex, endIndex)); } } }
From source file:fsi_admin.reportes.JReportesDlg.java
License:Open Source License
public void generarArchivoXLS(HttpServletRequest request, HttpServletResponse response, Workbook wb) throws ServletException, IOException { JReportesSet m_RepSet = (JReportesSet) request.getAttribute("m_RepSet"); JReportesLevel1 m_setL1 = (JReportesLevel1) request.getAttribute("m_setL1"); JReportesCompL1Set m_setCL1 = (JReportesCompL1Set) request.getAttribute("m_setCL1"); Boolean m_bSelectL1 = (Boolean) request.getAttribute("m_bSelectL1"); Boolean m_bSelectL2 = (Boolean) request.getAttribute("m_bSelectL2"); Boolean m_bSelectL3 = (Boolean) request.getAttribute("m_bSelectL3"); Boolean m_bComputeL1 = (Boolean) request.getAttribute("m_bComputeL1"); Boolean m_bComputeL2 = (Boolean) request.getAttribute("m_bComputeL2"); Boolean m_bComputeL3 = (Boolean) request.getAttribute("m_bComputeL3"); JReportesBind3Set m_colL1 = (JReportesBind3Set) request.getAttribute("m_colL1"); JReportesBind3Set m_colL2 = (JReportesBind3Set) request.getAttribute("m_colL2"); JReportesBind3Set m_colL3 = (JReportesBind3Set) request.getAttribute("m_colL3"); JReportesBind3Set m_colCL1 = (JReportesBind3Set) request.getAttribute("m_colCL1"); JReportesBind3Set m_colCL2 = (JReportesBind3Set) request.getAttribute("m_colCL2"); JReportesBind3Set m_colCL3 = (JReportesBind3Set) request.getAttribute("m_colCL3"); String fsi_filtro = (String) request.getAttribute("fsi_filtro"); Sheet sheet = wb.createSheet("reporte " + Integer.toString(m_RepSet.getAbsRow(0).getID_Report())); short nrow = 0; Row row = sheet.createRow(nrow++);/*from w ww . ja v a 2 s .c o m*/ Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.DARK_BLUE.index); Cell cell = row.createCell(0); cell.setCellValue(m_RepSet.getAbsRow(0).getDescription() + " " + fsi_filtro); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); cellStyle.setFont(font); cell.setCellStyle(cellStyle); JRepCellStyles cellStyles = new JRepCellStyles(wb); if (m_bSelectL1.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL1.getNumRows(); i++) { if (m_colL1.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL1.getAbsRow(i).getColName(), "general", "STRING", m_colL1.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll1.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll1.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL2.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL2.getNumRows(); i++) { if (m_colL2.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL2.getAbsRow(i).getColName(), "general", "STRING", m_colL2.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll2.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll2.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL3.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL3.getNumRows(); i++) { if (m_colL3.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL3.getAbsRow(i).getColName(), "general", "STRING", m_colL3.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll3.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll3.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL1.booleanValue()) { for (int RL1 = 0; RL1 < m_setL1.getNumRows(); RL1++) { Row rowl1 = sheet.createRow(nrow++); for (int CL1 = 0; CL1 < m_colL1.getNumRows(); CL1++) { if (m_colL1.getAbsRow(CL1).getWillShow()) JUtil.DatoXLS(cellStyles, rowl1, CL1, m_setL1.getAbsRow(RL1).getSTS(m_colL1.getAbsRow(CL1).getColName()), m_colL1.getAbsRow(CL1).getFormat(), m_colL1.getAbsRow(CL1).getBindDataType(), m_colL1.getAbsRow(CL1).getAlinHor(), null, "fnorm", request); } // Nivel 2 if (m_bSelectL2.booleanValue()) { for (int RL2 = 0; RL2 < m_setL1.getAbsRow(RL1).getSetL2().getNumRows(); RL2++) { Row rowl2 = sheet.createRow(nrow++); for (int CL2 = 0; CL2 < m_colL2.getNumRows(); CL2++) { if (m_colL2.getAbsRow(CL2).getWillShow()) JUtil.DatoXLS(cellStyles, rowl2, CL2, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2) .getSTS(m_colL2.getAbsRow(CL2).getColName()), m_colL2.getAbsRow(CL2).getFormat(), m_colL2.getAbsRow(CL2).getBindDataType(), m_colL2.getAbsRow(CL2).getAlinHor(), null, "fnorm", request); } // Nivel 3 if (m_bSelectL3.booleanValue()) { for (int RL3 = 0; RL3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3() .getNumRows(); RL3++) { Row rowl3 = sheet.createRow(nrow++); for (int CL3 = 0; CL3 < m_colL3.getNumRows(); CL3++) { if (m_colL3.getAbsRow(CL3).getWillShow()) JUtil.DatoXLS(cellStyles, rowl3, CL3, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3() .getAbsRow(RL3).getSTS(m_colL3.getAbsRow(CL3).getColName()), m_colL3.getAbsRow(CL3).getFormat(), m_colL3.getAbsRow(CL3).getBindDataType(), m_colL3.getAbsRow(CL3).getAlinHor(), null, "fnorm", request); } } if (m_bComputeL3.booleanValue()) { for (int RC3 = 0; RC3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3() .getNumRows(); RC3++) { Row rowc3 = sheet.createRow(nrow++); for (int CC3 = 0; CC3 < m_colCL3.getNumRows(); CC3++) { if (m_colCL3.getAbsRow(CC3).getWillShow()) JUtil.DatoXLS(cellStyles, rowc3, CC3, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3() .getAbsRow(RC3) .getSTS(m_colCL3.getAbsRow(CC3).getColName()), m_colCL3.getAbsRow(CC3).getFormat(), m_colCL3.getAbsRow(CC3).getBindDataType(), m_colCL3.getAbsRow(CC3).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL3 } // Fin SI L3 } if (m_bComputeL2.booleanValue()) { for (int RC2 = 0; RC2 < m_setL1.getAbsRow(RL1).getSetCL2().getNumRows(); RC2++) { Row rowc2 = sheet.createRow(nrow++); for (int CC2 = 0; CC2 < m_colCL2.getNumRows(); CC2++) { if (m_colCL2.getAbsRow(CC2).getWillShow()) JUtil.DatoXLS(cellStyles, rowc2, CC2, m_setL1.getAbsRow(RL1).getSetCL2().getAbsRow(RC2) .getSTS(m_colCL2.getAbsRow(CC2).getColName()), m_colCL2.getAbsRow(CC2).getFormat(), m_colCL2.getAbsRow(CC2).getBindDataType(), m_colCL2.getAbsRow(CC2).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL2 } // Fin SI L2 } if (m_bComputeL1.booleanValue()) { for (int RC1 = 0; RC1 < m_setCL1.getNumRows(); RC1++) { Row rowc1 = sheet.createRow(nrow++); for (int CC1 = 0; CC1 < m_colCL1.getNumRows(); CC1++) { if (m_colCL1.getAbsRow(CC1).getWillShow()) JUtil.DatoXLS(cellStyles, rowc1, CC1, m_setCL1.getAbsRow(RC1).getSTS(m_colCL1.getAbsRow(CC1).getColName()), m_colCL1.getAbsRow(CC1).getFormat(), m_colCL1.getAbsRow(CC1).getBindDataType(), m_colCL1.getAbsRow(CC1).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL1 } // Fin SI L1 int colsmer; if (m_colL1.getNumRows() > m_colL2.getNumRows() && m_colL1.getNumRows() > m_colL3.getNumRows()) colsmer = m_colL1.getNumRows() - 1; else if (m_colL2.getNumRows() > m_colL1.getNumRows() && m_colL2.getNumRows() > m_colL3.getNumRows()) colsmer = m_colL2.getNumRows() - 1; else colsmer = m_colL3.getNumRows() - 1; sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colsmer == -1 ? 0 : colsmer))); }
From source file:functions.excels.Excel.java
License:Apache License
protected void collerLogoEtTitre(int page, String titre) throws IOException { // TODO Auto-generated method stub Sheet sheet = wb.getSheetAt(0); sheet.createRow(page * LIGNES + 3).createCell(4).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(page * LIGNES + 3, //first row (0-based) page * LIGNES + 5, //last row (0-based) 4, //first column (0-based) 8 //last column (0-based) ));/* w w w . j av a 2 s .com*/ this.collerLogo(page); }
From source file:functions.excels.exports.CarteSommeBiodiversiteExcel.java
License:Apache License
public CarteSommeBiodiversiteExcel(Map<String, String> info, CarteSommeBiodiversite csb) { super();/* w w w .j a va2 s . c o m*/ Sheet sheet = wb.createSheet("Carte somme de la biodiversit"); 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"))); String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Carte indiquant les premires observations "; if (espece != null) titre += "de " + espece.espece_nom; else if (sous_groupe != null) titre += "de " + sous_groupe; else if (groupe != null) titre += "de " + groupe; if (stade_sexe != null) titre += " au stade " + stade_sexe; titre += " du " + date1 + " au " + date2; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); Row rowHead = sheet.createRow(1); rowHead.createCell(0).setCellValue("UTM"); rowHead.createCell(1).setCellValue("Fiche ID"); rowHead.createCell(2).setCellValue("Espce"); rowHead.createCell(3).setCellValue("Date"); rowHead.createCell(4).setCellValue("Tmoin(s)"); CellStyle cellStyleDate = wb.createCellStyle(); CreationHelper creationHelper = wb.getCreationHelper(); cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("dd/mm/yyyy")); int i = 2; for (UTMS utm : UTMS.findAll()) { List<List<InformationsComplementaires>> observationsDansCetteMaille = csb.carte.get(utm); for (List<InformationsComplementaires> observationsPourCetteEspece : observationsDansCetteMaille) { for (InformationsComplementaires complements : observationsPourCetteEspece) { Row row = sheet.createRow(i); row.createCell(0).setCellValue(utm.utm); row.createCell(1).setCellValue( complements.informations_complementaires_observation.observation_fiche.fiche_id); row.createCell(2).setCellValue( complements.informations_complementaires_observation.observation_espece.espece_nom); Cell cellDate = row.createCell(3); cellDate.setCellValue( complements.informations_complementaires_observation.observation_fiche.fiche_date); cellDate.setCellStyle(cellStyleDate); StringBuilder membres = new StringBuilder(); List<FicheHasMembre> fhms = complements.informations_complementaires_observation.observation_fiche .getFicheHasMembre(); for (int j = 0; j < fhms.size() - 1; j++) { membres.append(fhms.get(j).membre); membres.append(", "); } if (!fhms.isEmpty()) membres.append(fhms.get(fhms.size() - 1).membre); else membres.append("et al."); row.createCell(4).setCellValue(membres.toString()); i++; } } } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); for (int k = 1; k <= 20; k++) { if (sheet.getRow(k) == null) sheet.createRow(k); } CellStyle redBackGround = wb.createCellStyle(); redBackGround.setFillBackgroundColor(IndexedColors.RED.getIndex()); redBackGround.setFillPattern(CellStyle.BIG_SPOTS); for (UTMS utm : csb.carte.keySet()) { int xy[] = UTMtoXY.convert10x10(utm.utm); Row row = sheet.getRow(xy[1] + 1); Cell cell = row.createCell(xy[0] + 5); int nombreDEspeces = csb.getNombreDEspecesDansMaille(utm); cell.setCellValue(nombreDEspeces); if (nombreDEspeces != 0) cell.setCellStyle(redBackGround); } for (int k = 5; k < 25; k++) { sheet.autoSizeColumn(k); } Row rowUniteMailleEspece; if ((rowUniteMailleEspece = sheet.getRow(23)) == null) rowUniteMailleEspece = sheet.createRow(23); rowUniteMailleEspece.createCell(6).setCellValue("Units maille-espce : " + csb.getUnitesMailleEspece()); }
From source file:functions.excels.exports.CarteSommeExcel.java
License:Apache License
public CarteSommeExcel(Map<String, String> info, CarteSomme cs) { super();//from w w w .jav a 2 s . c om Sheet sheet = wb.createSheet("Carte somme"); 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"))); String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Carte indiquant le nombre d'observations "; if (espece != null) titre += "de " + espece.espece_nom; else if (sous_groupe != null) titre += "de " + sous_groupe; else if (groupe != null) titre += "de " + groupe; if (stade_sexe != null) titre += " au stade " + stade_sexe; titre += " du " + date1 + " au " + date2; titre += " (" + cs.getSomme() + " tmoignages)"; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); for (int i = 1; i <= 20; i++) { sheet.createRow(i); } CellStyle redBackGround = wb.createCellStyle(); redBackGround.setFillBackgroundColor(IndexedColors.RED.getIndex()); redBackGround.setFillPattern(CellStyle.BIG_SPOTS); for (UTMS utm : cs.carte.keySet()) { int xy[] = UTMtoXY.convert10x10(utm.utm); Row row = sheet.getRow(xy[1] + 1); Cell cell = row.createCell(xy[0]); int nombre = cs.carte.get(utm); cell.setCellValue(nombre); if (nombre != 0) cell.setCellStyle(redBackGround); } for (int k = 1; k <= 20; k++) { sheet.autoSizeColumn(k); } }
From source file:functions.excels.exports.ChronologieDUnTemoinExcel.java
License:Apache License
public ChronologieDUnTemoinExcel(Map<String, String> info, ChronologieDUnTemoin cdut) { super();/*from w w w .j a v a2s . c o m*/ Sheet sheet = wb.createSheet("Chronologie d'un tmoin"); 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"))); String maille = info.get("maille"); String temoin = info.get("temoin"); String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Chronologie des tmoignages "; if (espece != null) titre += "de " + espece.espece_nom; else if (sous_groupe != null) titre += "de " + sous_groupe; else if (groupe != null) titre += "de " + groupe; if (stade_sexe != null) titre += " au stade " + stade_sexe; if (!maille.equals("")) titre += " dans la maille " + maille; titre += " faits par " + temoin; titre += " du " + date1 + " au " + date2; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); Row rowHead = sheet.createRow(1); rowHead.createCell(0).setCellValue("Fiche ID"); rowHead.createCell(1).setCellValue("UTM"); rowHead.createCell(2).setCellValue("Lieu-dit"); rowHead.createCell(3).setCellValue("Commune"); rowHead.createCell(4).setCellValue("Dp."); rowHead.createCell(5).setCellValue("Date min"); rowHead.createCell(6).setCellValue("Date"); rowHead.createCell(7).setCellValue("Espce"); rowHead.createCell(8).setCellValue("Nombre"); rowHead.createCell(9).setCellValue("Stade/Sexe"); rowHead.createCell(10).setCellValue("Tmoins"); rowHead.createCell(11).setCellValue("Mmo"); rowHead.createCell(12).setCellValue("Groupe"); CellStyle cellStyleDate = wb.createCellStyle(); CreationHelper creationHelper = wb.getCreationHelper(); cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("dd/mm/yyyy")); int i = 2; for (InformationsComplementaires complements : cdut.chronologie) { Row row = sheet.createRow(i); Observation observation = complements.informations_complementaires_observation; Fiche fiche = observation.observation_fiche; row.createCell(0).setCellValue(fiche.fiche_id); row.createCell(1).setCellValue(fiche.fiche_utm.utm); row.createCell(2).setCellValue(fiche.fiche_lieudit); if (fiche.fiche_commune != null) { row.createCell(3).setCellValue(fiche.fiche_commune.ville_nom_aer); row.createCell(4).setCellValue(fiche.fiche_commune.ville_departement.departement_code); } if (fiche.fiche_date_min != null) { Cell cell = row.createCell(5); cell.setCellValue(fiche.fiche_date_min.getTime()); cell.setCellStyle(cellStyleDate); } Cell cell = row.createCell(6); cell.setCellValue(fiche.fiche_date.getTime()); cell.setCellStyle(cellStyleDate); row.createCell(7).setCellValue(observation.observation_espece.espece_nom); Integer nombre = complements.informations_complementaires_nombre_de_specimens; if (nombre == null) row.createCell(8).setCellValue("?"); else row.createCell(8).setCellValue(nombre); row.createCell(9).setCellValue(complements.informations_complementaires_stade_sexe.stade_sexe_intitule); StringBuilder membres = new StringBuilder(); List<FicheHasMembre> fhms = fiche.getFicheHasMembre(); for (int j = 0; j < fhms.size() - 1; j++) { membres.append(fhms.get(j).membre); membres.append(", "); } if (!fhms.isEmpty()) membres.append(fhms.get(fhms.size() - 1).membre); else membres.append("et al."); row.createCell(10).setCellValue(membres.toString()); row.createCell(11).setCellValue(fiche.fiche_memo); row.createCell(12) .setCellValue(observation.observation_espece.espece_sous_groupe.sous_groupe_groupe.groupe_nom); i++; } for (int j = 0; j < 11; j++) sheet.autoSizeColumn(j); }
From source file:functions.excels.exports.HistogrammeDesImagosExcel.java
License:Apache License
public HistogrammeDesImagosExcel(Map<String, String> info, HistogrammeDesImagos hdi) throws IOException { super();/*from ww w . ja v a2 s. c om*/ Sheet sheet = wb.createSheet("Histogramme des imagos"); 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"))); String maille = info.get("maille"); String date1 = info.get("jour1") + "/" + info.get("mois1") + "/" + info.get("annee1"); String date2 = info.get("jour2") + "/" + info.get("mois2") + "/" + info.get("annee2"); String titre = "Diagramme reprsentant les imagos en fonction de la priode de l'anne "; if (espece != null) titre += "de " + espece.espece_nom; else if (sous_groupe != null) titre += "de " + sous_groupe; else if (groupe != null) titre += "de " + groupe; if (!maille.equals("")) titre += " dans la maille " + maille; titre += " du " + date1 + " au " + date2; titre += " (" + hdi.getSomme() + " tmoignages)"; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); Row row = sheet.createRow(1); row.createCell(0).setCellValue("Priode"); row.createCell(1).setCellValue("Nbr. Obs."); int i = 0; while (i < hdi.histogramme.length) { row = sheet.createRow(i + 2); row.createCell(0); row.createCell(1); sheet.getRow(i + 2).getCell(0).setCellValue(hdi.legende.get(i)); sheet.getRow(i + 2).getCell(1).setCellValue(hdi.histogramme[i]); i++; } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); }
From source file:functions.excels.exports.HistoriqueDesEspecesExcel.java
License:Apache License
public HistoriqueDesEspecesExcel(Map<String, String> info, HistoriqueDesEspeces hde) throws IOException { super();// ww w .j a v a 2 s . co m Sheet sheet = wb.createSheet("Historique des espces"); 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"))); String maille = info.get("maille"); String titre = "Historie des espces "; if (espece != null) titre += "de " + espece.espece_nom; else if (sous_groupe != null) titre += "de " + sous_groupe; else if (groupe != null) titre += "de " + groupe; if (!maille.equals("")) titre += " dans la maille " + maille; titre += " (" + hde.getSomme() + " tmoignages, " + hde.nbTemoignagesRejetes + " tmoignages rejets)"; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); Row row = sheet.createRow(1); row.createCell(0).setCellValue("Priode"); row.createCell(1).setCellValue("Nbr. Obs."); int i = 0; while (i < hde.histogramme.length) { row = sheet.createRow(i + 2); row.createCell(0); row.createCell(1); sheet.getRow(i + 2).getCell(0).setCellValue(hde.legende.get(i)); sheet.getRow(i + 2).getCell(1).setCellValue(hde.histogramme[i]); i++; } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); }
From source file:functions.excels.exports.MaChronologieExcel.java
License:Apache License
public MaChronologieExcel(Map<String, String> info, MaChronologie maChronologie) { super();// w w w . j a v a2 s .c om Sheet sheet = wb.createSheet("Ma chronologie"); String temoin = info.get("temoin"); String titre = "Chronologie des tmoignages "; titre += " dposs par " + temoin; sheet.createRow(0).createCell(0).setCellValue(titre); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); Row rowHead = sheet.createRow(1); rowHead.createCell(0).setCellValue("Fiche ID"); rowHead.createCell(1).setCellValue("UTM"); rowHead.createCell(2).setCellValue("Lieu-dit"); rowHead.createCell(3).setCellValue("Commune"); rowHead.createCell(4).setCellValue("Dp."); rowHead.createCell(5).setCellValue("Date min"); rowHead.createCell(6).setCellValue("Date"); rowHead.createCell(7).setCellValue("Espce"); rowHead.createCell(8).setCellValue("Nombre"); rowHead.createCell(9).setCellValue("Stade/Sexe"); rowHead.createCell(10).setCellValue("Tmoins"); rowHead.createCell(11).setCellValue("Mmo"); rowHead.createCell(12).setCellValue("Groupe"); CellStyle cellStyleDate = wb.createCellStyle(); CreationHelper creationHelper = wb.getCreationHelper(); cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat("dd/mm/yyyy")); int i = 2; for (Observation observation : maChronologie.chronologie) { for (InformationsComplementaires complement : observation.getInfos()) { Row row = sheet.createRow(i); Fiche fiche = observation.observation_fiche; row.createCell(0).setCellValue(fiche.fiche_id); row.createCell(1).setCellValue(fiche.fiche_utm.utm); row.createCell(2).setCellValue(fiche.fiche_lieudit); if (fiche.fiche_commune != null) { row.createCell(3).setCellValue(fiche.fiche_commune.ville_nom_aer); row.createCell(4).setCellValue(fiche.fiche_commune.ville_departement.departement_code); } if (fiche.fiche_date_min != null) { Cell cell = row.createCell(5); cell.setCellValue(fiche.fiche_date_min.getTime()); cell.setCellStyle(cellStyleDate); } Cell cell = row.createCell(6); cell.setCellValue(fiche.fiche_date.getTime()); cell.setCellStyle(cellStyleDate); row.createCell(7).setCellValue(observation.observation_espece.espece_nom); Integer nombre = complement.informations_complementaires_nombre_de_specimens; if (nombre == null) row.createCell(8).setCellValue("?"); else row.createCell(8).setCellValue(nombre); row.createCell(9) .setCellValue(complement.informations_complementaires_stade_sexe.stade_sexe_intitule); StringBuilder membres = new StringBuilder(); List<FicheHasMembre> fhms = fiche.getFicheHasMembre(); for (int j = 0; j < fhms.size() - 1; j++) { membres.append(fhms.get(j).membre); membres.append(", "); } if (!fhms.isEmpty()) membres.append(fhms.get(fhms.size() - 1).membre); else membres.append("et al."); row.createCell(10).setCellValue(membres.toString()); row.createCell(11).setCellValue(fiche.fiche_memo); row.createCell(12).setCellValue( observation.observation_espece.espece_sous_groupe.sous_groupe_groupe.groupe_nom); i++; } } for (int j = 0; j < 11; j++) sheet.autoSizeColumn(j); }