List of usage examples for org.apache.poi.ss.usermodel CellStyle setFillPattern
void setFillPattern(FillPatternType fp);
From source file:fr.amapj.service.engine.generator.excel.ExcelGeneratorTool.java
License:Open Source License
private void beGray(CellStyle style) { style.setFillPattern(CellStyle.SOLID_FOREGROUND); if (wb instanceof HSSFWorkbook) { HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); palette.setColorAtIndex(HSSFColor.LAVENDER.index, (byte) 0xF0, (byte) 0xF0, (byte) 0xF0); style.setFillForegroundColor(IndexedColors.LAVENDER.getIndex()); } else {//from ww w .ja va 2 s . co m ((XSSFCellStyle) style).setFillForegroundColor(new XSSFColor(new java.awt.Color(0xF0, 0xF0, 0xF0))); } }
From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java
License:Apache License
/** * Initialisation des styles de cellule/*from ww w . ja v a2 s . co m*/ */ protected void initStyles() { CellStyle defaultStyle = workbook.createCellStyle(); defaultStyle.setFont(getFont(FONT_NORMAL_NAME)); setStyleFillForegroundColor(defaultStyle, colorRegistry, HSSFColor.WHITE.index); defaultStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); defaultStyle.setBorderBottom(CellStyle.BORDER_THIN); setStyleBottomBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderLeft(CellStyle.BORDER_THIN); setStyleLeftBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderRight(CellStyle.BORDER_THIN); setStyleRightBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderTop(CellStyle.BORDER_THIN); setStyleTopBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultStyle.setWrapText(true); registerStyle(STYLE_DEFAULT_NAME, defaultStyle); CellStyle styleHeader = workbook.createCellStyle(); styleHeader.setAlignment(CellStyle.ALIGN_CENTER); styleHeader.setFont(getFont(FONT_HEADER_NAME)); setStyleFillForegroundColor(styleHeader, colorRegistry, HEADER_BACKGROUND_COLOR_INDEX); styleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND); styleHeader.setBorderBottom(CellStyle.BORDER_THIN); setStyleBottomBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderLeft(CellStyle.BORDER_THIN); setStyleLeftBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderRight(CellStyle.BORDER_THIN); setStyleRightBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderTop(CellStyle.BORDER_THIN); setStyleTopBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setDataFormat((short) 0); styleHeader.setWrapText(true); registerStyle(STYLE_HEADER_NAME, styleHeader); CellStyle styleOdd = cloneStyle(defaultStyle); registerStyle(STYLE_STANDARD_NAME + ROW_ODD_NAME, styleOdd); CellStyle styleEven = cloneStyle(styleOdd); setStyleFillForegroundColor(styleEven, colorRegistry, EVEN_ROW_BACKGROUND_COLOR_INDEX); registerStyle(STYLE_STANDARD_NAME + ROW_EVEN_NAME, styleEven); // styles pour les nombres entiers short integerFormatIndex = dataFormat.getFormat(integerDataFormat); CellStyle styleOddInteger = cloneStyle(styleOdd); styleOddInteger.setAlignment(CellStyle.ALIGN_RIGHT); styleOddInteger.setDataFormat(integerFormatIndex); registerStyle(STYLE_INTEGER_NAME + ROW_ODD_NAME, styleOddInteger); CellStyle styleEvenInteger = cloneStyle(styleEven); styleEvenInteger.setAlignment(CellStyle.ALIGN_RIGHT); styleEvenInteger.setDataFormat(integerFormatIndex); registerStyle(STYLE_INTEGER_NAME + ROW_EVEN_NAME, styleEvenInteger); // styles pour les nombres dcimaux short decimalFormatIndex = dataFormat.getFormat(decimalDataFormat); CellStyle styleOddDecimal = cloneStyle(styleOdd); styleOddDecimal.setAlignment(CellStyle.ALIGN_RIGHT); styleOddDecimal.setDataFormat(decimalFormatIndex); registerStyle(STYLE_DECIMAL_NAME + ROW_ODD_NAME, styleOddDecimal); CellStyle styleEvenDecimal = cloneStyle(styleEven); styleEvenDecimal.setAlignment(CellStyle.ALIGN_RIGHT); styleEvenDecimal.setDataFormat(decimalFormatIndex); registerStyle(STYLE_DECIMAL_NAME + ROW_EVEN_NAME, styleEvenDecimal); // styles pour les dates short dateFormatIndex = dataFormat.getFormat(dateDataFormat); CellStyle styleOddDate = cloneStyle(styleOdd); styleOddDate.setDataFormat(dateFormatIndex); registerStyle(STYLE_DATE_NAME + ROW_ODD_NAME, styleOddDate); CellStyle styleEvenDate = cloneStyle(styleEven); styleEvenDate.setDataFormat(dateFormatIndex); registerStyle(STYLE_DATE_NAME + ROW_EVEN_NAME, styleEvenDate); // styles pour les dates avec heure short dateTimeFormatIndex = dataFormat.getFormat(dateTimeDataFormat); CellStyle styleOddDateTime = cloneStyle(styleOdd); styleOddDateTime.setDataFormat(dateTimeFormatIndex); registerStyle(STYLE_DATE_TIME_NAME + ROW_ODD_NAME, styleOddDateTime); CellStyle styleEvenDateTime = cloneStyle(styleEven); styleEvenDateTime.setDataFormat(dateTimeFormatIndex); registerStyle(STYLE_DATE_TIME_NAME + ROW_EVEN_NAME, styleEvenDateTime); // styles pour les pourcentages short percentFormatIndex = dataFormat.getFormat(percentDataFormat); CellStyle styleOddPercent = cloneStyle(styleOdd); styleOddPercent.setDataFormat(percentFormatIndex); registerStyle(STYLE_PERCENT_NAME + ROW_ODD_NAME, styleOddPercent); CellStyle styleEvenPercent = cloneStyle(styleEven); styleEvenPercent.setDataFormat(percentFormatIndex); registerStyle(STYLE_PERCENT_NAME + ROW_EVEN_NAME, styleEvenPercent); short percentRelativeFormatIndex = dataFormat.getFormat(percentRelativeDataFormat); CellStyle styleOddPercentRelative = cloneStyle(styleOdd); styleOddPercentRelative.setDataFormat(percentRelativeFormatIndex); registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_ODD_NAME, styleOddPercentRelative); CellStyle styleEvenPercentRelative = cloneStyle(styleEven); styleEvenPercentRelative.setDataFormat(percentRelativeFormatIndex); registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_EVEN_NAME, styleEvenPercentRelative); // styles pour les liens CellStyle styleOddLink = cloneStyle(styleOdd); styleOddLink.setFont(getFont(FONT_LINK_NAME)); registerStyle(STYLE_LINK_NAME + ROW_ODD_NAME, styleOddLink); CellStyle styleEvenLink = cloneStyle(styleEven); styleEvenLink.setFont(getFont(FONT_LINK_NAME)); registerStyle(STYLE_LINK_NAME + ROW_EVEN_NAME, styleEvenLink); // styles pour les tailles de fichiers short fileSizeFormatIndex = dataFormat.getFormat(fileSizeDataFormat); CellStyle styleOddFileSize = cloneStyle(styleOdd); styleOddFileSize.setDataFormat(fileSizeFormatIndex); registerStyle(STYLE_FILE_SIZE_NAME + ROW_ODD_NAME, styleOddFileSize); CellStyle styleEvenFileSize = cloneStyle(styleEven); styleEvenFileSize.setDataFormat(fileSizeFormatIndex); registerStyle(STYLE_FILE_SIZE_NAME + ROW_EVEN_NAME, styleEvenFileSize); }
From source file:functions.excels.exports.CarteSommeBiodiversiteExcel.java
License:Apache License
public CarteSommeBiodiversiteExcel(Map<String, String> info, CarteSommeBiodiversite csb) { super();/*from w ww. j a v a2 s . c om*/ 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 .j a v a2 s. co m 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: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 . j av a 2 s. co 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:gregchen.Simulation.java
License:Open Source License
private void initializeExcelFile() { //short rownum; //Create a Data folder if it does not already exist File theDir = new File("Data"); //if the directory does not exist, create it if(!theDir.exists()) {//w w w .j a v a 2s .c om theDir.mkdir(); } // create a new file if(fileCount == 0) { File file = null; do { fileCount++; file = new File("Data/Data " + fileCount + ".xls"); }while(file.exists()); } try { excelOut = new FileOutputStream("Data/Data " + fileCount + ".xls"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } fileCount++; // create a new workbook wb = new HSSFWorkbook(); // create a new sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 3 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle cs3 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it blue f.setColor( (short)0xc ); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it red f2.setColor( (short)Font.COLOR_RED ); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setStrikeout( true ); //set cell stlye cs.setFont(f); //set the cell format cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); //set the cell format to text see DataFormat for a full list cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "Greg Chen"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // Make header cells r = s.createRow(0); c = r.createCell(7); c.setCellStyle(cs3); c.setCellValue("Initial number: " + initialNumber + " Number per release: " + numPerRelease + " Number Releases: " + numReleases + " Release Interval: " + releaseInterval + " Female lethal gene: " + fsRIDL); currentRow = 2; for (short cellnum = (short) 0; cellnum < 7; cellnum ++) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals String heading = null; switch(cellnum) { case 0: heading = "Day"; break; case 1: heading = "Total Population"; break; case 2: heading = "Male Population"; break; case 3: heading = "Female Population"; break; case 4: heading = "FF"; break; case 5: heading = "Ff"; break; case 6: heading = "ff"; break; } c.setCellValue(heading); // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" // make this column a bit wider if(cellnum > 0) { s.setColumnWidth((short) (cellnum), (short) ((300) / ((double) 1 / 20))); } r.setHeight((short) 800); } /* int rownum; for (rownum = (short) 0; rownum < 30; rownum++) { // create a row r = s.createRow(rownum); r.setHeight((short) 0x249); // } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); */ // write the workbook to the output stream // close our file (don't blow out our file handles) }
From source file:guru.qas.martini.report.DefaultState.java
License:Apache License
protected void colorRow(short color, Row row) { short firstCellNum = row.getFirstCellNum(); short lastCellNum = row.getLastCellNum(); for (int i = firstCellNum; i <= lastCellNum; i++) { Cell cell = row.getCell(i);/* w ww.j a v a 2s .c om*/ if (null != cell) { CellStyle cellStyle = cell.getCellStyle(); Workbook workbook = cell.getSheet().getWorkbook(); CellStyle clone = workbook.createCellStyle(); clone.cloneStyleFrom(cellStyle); clone.setFillForegroundColor(color); clone.setFillPattern(FillPatternType.SOLID_FOREGROUND); BorderStyle borderStyle = cellStyle.getBorderLeftEnum(); clone.setBorderLeft(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle); short borderColor = cellStyle.getLeftBorderColor(); clone.setLeftBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor); borderStyle = cellStyle.getBorderRightEnum(); clone.setBorderRight(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle); borderColor = cellStyle.getRightBorderColor(); clone.setRightBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor); borderStyle = cellStyle.getBorderTopEnum(); clone.setBorderTop(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle); borderColor = cellStyle.getTopBorderColor(); clone.setTopBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor); borderStyle = cellStyle.getBorderBottomEnum(); clone.setBorderBottom(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle); borderColor = cellStyle.getBottomBorderColor(); clone.setBottomBorderColor(borderColor); cell.setCellStyle(clone); } } }
From source file:hornet.framework.web.service.export.AbstractTableExportService.java
License:CeCILL license
@Override public HSSFWorkbook construireXlsModel(final T toExport) { // Blank workbook final HSSFWorkbook workbook = new HSSFWorkbook(); // Create a blank sheet final HSSFSheet sheet = workbook.createSheet(); int rownum = 0; // Style pour la bordure des cellules final CellStyle styleBordure = workbook.createCellStyle(); styleBordure.setBorderBottom(BorderStyle.THIN); styleBordure.setBorderTop(BorderStyle.THIN); styleBordure.setBorderRight(BorderStyle.THIN); styleBordure.setBorderLeft(BorderStyle.THIN); final CellStyle styleEntete = workbook.createCellStyle(); styleEntete.cloneStyleFrom(styleBordure); styleEntete.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); styleEntete.setFillPattern(FillPatternType.SOLID_FOREGROUND); // Rcupration du Table VO final TableVO tableVo = construireTableauExport(toExport); final Collection<String> colTitles = tableVo.getColumnsTitles(); final Iterator<String> itTitles = colTitles.iterator(); // Titre des colonnes int cellnum = 0; final Row xlsRow = sheet.createRow(rownum++); while (itTitles.hasNext()) { final String title = itTitles.next(); final Cell cell = xlsRow.createCell(cellnum++); cell.setCellValue(title);/* w w w. j a v a2s. c om*/ cell.setCellStyle(styleEntete); } if (tableVo.getRows() != null) { final List<RowVO> rows = tableVo.getRows(); final Iterator<RowVO> itRows = rows.iterator(); // Lignes while (itRows.hasNext()) { this.exporteLigne(itRows, rownum, sheet, styleBordure); rownum++; } for (int i = 0; i < cellnum; i++) { sheet.autoSizeColumn(i); } } return workbook; }
From source file:info.informationsea.tableio.excel.ExcelSheetWriter.java
License:Open Source License
public void registerBaseCellStyle(Object index, CellStyle style) { CellStyle baseCellStyles = style;//from w w w . jav a2s. co m // header style CellStyle headerCellStyles = sheet.getWorkbook().createCellStyle(); headerCellStyles.cloneStyleFrom(style); Font headerFont = sheet.getWorkbook().createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerCellStyles.setFont(headerFont); headerCellStyles.setFillPattern(CellStyle.SOLID_FOREGROUND); headerCellStyles.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // alternative style CellStyle alternativeCellStyles = sheet.getWorkbook().createCellStyle(); alternativeCellStyles.cloneStyleFrom(style); if (style instanceof XSSFCellStyle) { ((XSSFCellStyle) alternativeCellStyles).setFillForegroundColor(new XSSFColor(new Color(242, 242, 242))); } else { alternativeCellStyles.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); } alternativeCellStyles.setFillPattern(CellStyle.SOLID_FOREGROUND); // link style Font linkFont = sheet.getWorkbook().createFont(); linkFont.setColor(IndexedColors.BLUE.getIndex()); CellStyle linkStyle = sheet.getWorkbook().createCellStyle(); linkStyle.cloneStyleFrom(baseCellStyles); linkStyle.setFont(linkFont); CellStyle alternativeLinkStyle = sheet.getWorkbook().createCellStyle(); alternativeLinkStyle.cloneStyleFrom(alternativeCellStyles); alternativeLinkStyle.setFont(linkFont); Map<CellStyleType, CellStyle> styleMap = new HashMap<>(); styleMap.put(CellStyleType.BASE, baseCellStyles); styleMap.put(CellStyleType.HEADER, headerCellStyles); styleMap.put(CellStyleType.ALTERNATIVE, alternativeCellStyles); styleMap.put(CellStyleType.BASE, baseCellStyles); styleMap.put(CellStyleType.LINK, linkStyle); styleMap.put(CellStyleType.LINK_ALTERNATIVE, alternativeLinkStyle); styles.put(index, styleMap); }
From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java
License:Mozilla Public License
public CellStyle buildHeaderCellStyle(Sheet sheet) { CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_LEFT); cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); /* cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN);*/ Font font = sheet.getWorkbook().createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Arial"); font.setColor(IndexedColors.BLACK.getIndex()); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font);/*w w w . j a v a 2s . c o m*/ return cellStyle; }