List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setBorderRight
@Override public void setBorderRight(BorderStyle border)
From source file:net.mcnewfamily.rmcnew.model.excel.CellStyleEssence.java
License:Open Source License
public XSSFCellStyle toXSSFCellStyle(XSSFWorkbook workbook) { if (workbook != null) { XSSFCellStyle xssfCellStyle = workbook.createCellStyle(); xssfCellStyle.setBorderTop(topBorder.toPoiCellStyle()); xssfCellStyle.setBorderBottom(bottomBorder.toPoiCellStyle()); xssfCellStyle.setBorderLeft(leftBorder.toPoiCellStyle()); xssfCellStyle.setBorderRight(rightBorder.toPoiCellStyle()); xssfCellStyle.setAlignment(horizontalAlignment.toPoiCellStyle()); xssfCellStyle.setVerticalAlignment(verticalAlignment.toPoiCellStyle()); xssfCellStyle.setFillPattern(fillPattern.toPoiCellStyle()); // foreground color must be set before background color is set xssfCellStyle.setFillForegroundColor(foregroundColor); xssfCellStyle.setFillBackgroundColor(backgroundColor); xssfCellStyle.setWrapText(this.wrappedText); xssfCellStyle.setIndention(this.indentationInSpaces); if (this.fontEssence != null) { XSSFFont font = fontEssence.toXSSFFont(workbook); xssfCellStyle.setFont(font); }//from w w w . j a v a 2 s . c o m return xssfCellStyle; } else { throw new IllegalArgumentException("Cannot create XSSFCellStyle using a null XSSFWorkbook!"); } }
From source file:net.mcnewfamily.rmcnew.shared.Util.java
License:Open Source License
public static void copyXSSFCellStyle(XSSFCell srcCell, XSSFCell destCell) { XSSFCellStyle srcCellStyle = srcCell.getCellStyle(); XSSFCellStyle destCellStyle = destCell.getCellStyle(); // destCellStyle.cloneStyleFrom(srcCellStyle); destCellStyle.setAlignment(srcCellStyle.getAlignment()); destCellStyle.setVerticalAlignment(srcCellStyle.getVerticalAlignment()); destCellStyle.setFont(srcCellStyle.getFont()); destCellStyle.setBorderBottom(srcCellStyle.getBorderBottom()); destCellStyle.setBorderLeft(srcCellStyle.getBorderLeft()); destCellStyle.setBorderRight(srcCellStyle.getBorderRight()); destCellStyle.setBorderTop(srcCellStyle.getBorderTop()); destCellStyle.setFillPattern(srcCellStyle.getFillPattern()); // foreground color must be set before background color is set destCellStyle.setFillForegroundColor(srcCellStyle.getFillForegroundColor()); destCellStyle.setFillBackgroundColor(srcCellStyle.getFillBackgroundColor()); destCellStyle.setIndention(srcCellStyle.getIndention()); destCellStyle.setWrapText(srcCellStyle.getWrapText()); destCell.setCellStyle(destCellStyle); }
From source file:org.azkfw.document.database.xlsx.XLSXWriter.java
License:Apache License
private XSSFSheet createTableListSheet(final DatabaseModel datasource, final XSSFSheet sheet) { // /*from ww w. j a v a 2 s .co m*/ sheet.setColumnWidth(0, 640 * 1); sheet.setColumnWidth(1, 640 * 2); // No sheet.setColumnWidth(2, 640 * 10); // ??? sheet.setColumnWidth(3, 640 * 10); // ??? sheet.setColumnWidth(4, 640 * 15); // sheet.setColumnWidth(5, 640 * 1); ///////////////////////////////////////////////////////////////////// XSSFCellStyle styleLabel = workbook.createCellStyle(); styleLabel.setFillPattern(CellStyle.SOLID_FOREGROUND); styleLabel.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); styleLabel.setFont(fontLabel); styleLabel.setBorderTop(CellStyle.BORDER_THIN); styleLabel.setBorderBottom(CellStyle.BORDER_THIN); styleLabel.setBorderLeft(CellStyle.BORDER_THIN); styleLabel.setBorderRight(CellStyle.BORDER_THIN); XSSFCellStyle styleValue1 = workbook.createCellStyle(); styleValue1.setFillPattern(CellStyle.SOLID_FOREGROUND); styleValue1.setFillForegroundColor(IndexedColors.WHITE.getIndex()); styleValue1.setFont(fontValue); styleValue1.setBorderTop(CellStyle.BORDER_DOTTED); styleValue1.setBorderBottom(CellStyle.BORDER_DOTTED); styleValue1.setBorderLeft(CellStyle.BORDER_THIN); styleValue1.setBorderRight(CellStyle.BORDER_THIN); XSSFCellStyle styleValue2 = workbook.createCellStyle(); styleValue2.setFillPattern(CellStyle.SOLID_FOREGROUND); styleValue2.setFillForegroundColor(IndexedColors.WHITE.getIndex()); styleValue2.setFont(fontLink); styleValue2.setBorderTop(CellStyle.BORDER_DOTTED); styleValue2.setBorderBottom(CellStyle.BORDER_DOTTED); styleValue2.setBorderLeft(CellStyle.BORDER_THIN); styleValue2.setBorderRight(CellStyle.BORDER_THIN); ///////////////////////////////////////////////////////////////////// XSSFRow row = null; @SuppressWarnings("unused") XSSFCell cell = null; int rowIndex = 1; //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.table_list"), styleTitle, row); List<TableModel> tables = datasource.getTables(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == tables.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(2, Strings.get("doc.logic_table_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(3, Strings.get("doc.physical_table_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(4, Strings.get("doc.memo"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); rowIndex++; for (int i = 0; i < tables.size(); i++) { int bufRowIndex = rowIndex + i; TableModel table = tables.get(i); Hyperlink link = createTableLink(table.getName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == tables.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(2, table.getLabel(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(3, table.getName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(4, table.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); } rowIndex += tables.size(); workbook.setPrintArea(workbook.getSheetIndex(getTableListSheetName()), 0, 5, 0, rowIndex); sheet.setAutobreaks(true); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setFitWidth((short) 1); printSetup.setScale((short) 95); return sheet; }
From source file:org.gaia.gui.reports.ExcelReportExporter.java
License:Open Source License
/** * generate file EXCEL/* ww w .j a v a 2 s . c om*/ * * @param table * @param template */ public static void generateExcel(SortableTreeTable table, ReportTemplate template) { FileOutputStream fileOut = null; XSSFWorkbook wb = new XSSFWorkbook(); try { String excelFilename = generateFileName(template); fileOut = new FileOutputStream(excelFilename); List<TemplateColumnItem> items = ReportBuilder.orderColumns(template.getTemplateColumnItems()); TemplateColumnItem item; int colMax = table.getColumnModel().getColumnCount(); if (items.size() < colMax) { colMax = items.size(); } XSSFSheet bomSheet = (XSSFSheet) wb.createSheet(template.getTemplateName()); XSSFRow headerRow = (XSSFRow) bomSheet.createRow(0); XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setFontName("Tahoma"); headerStyle.setFont(font); headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < colMax; i++) { XSSFCell cell = (XSSFCell) headerRow.createCell(i); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(table.getColumnName(i)); cell.setCellStyle(headerStyle); } XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle(); font = wb.createFont(); font.setFontName("Tahoma"); cellStyle.setFont(font); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); for (int j = 0; j < table.getRowCount(); j++) { XSSFRow row = (XSSFRow) bomSheet.createRow(j + 1); for (int i = 0; i < colMax; i++) { XSSFCell cell = (XSSFCell) row.createCell(i); cell.setCellStyle(cellStyle); Object value = table.getValueAt(j, i); item = (TemplateColumnItem) items.get(i); if (value != null && !value.equals(StringUtils.EMPTY_STRING)) { Class<?> clazz = Class.forName(item.getReturnType()); //used for Snapshot Export if (clazz == String.class && NumberUtils.isInteger(value.toString())) { clazz = Integer.class; } else if (clazz == String.class && NumberUtils.isNumber(value.toString())) { clazz = BigDecimal.class; } if (short.class.isAssignableFrom(clazz) || Short.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Short) value).intValue()); } else if (int.class.isAssignableFrom(clazz) || Integer.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Integer.parseInt(value.toString())); } else if (long.class.isAssignableFrom(clazz) || Long.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Long) value).intValue()); } else if (float.class.isAssignableFrom(clazz) || Float.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Float) value).doubleValue()); } else if (BigDecimal.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(value.toString())); } else if (double.class.isAssignableFrom(clazz) || Double.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue((Double) value); } else if (Date.class.isAssignableFrom(clazz)) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(HSSFDateUtil.getExcelDate((java.sql.Date) value)); } else { cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(value.toString()); } } } } for (int i = 0; i < table.getColumnModel().getColumnCount(); i++) { bomSheet.autoSizeColumn(i); } SortableTreeTableModel model = (SortableTreeTableModel) table.getTreeTableModel(); AbstractSortableTreeTableNode root = (AbstractSortableTreeTableNode) model.getRoot(); groupNode(root, bomSheet); wb.write(fileOut); fileOut.flush(); fileOut.close(); nodeList.clear(); groupList.clear(); allNodeList.clear(); openExcel(excelFilename); } catch (ClassNotFoundException | IOException ex) { Exceptions.printStackTrace(ex); } finally { try { fileOut.close(); } catch (IOException ex) { logger.error(ex); } } }
From source file:packtest.CalendarDemo.java
License:Apache License
/** * cell styles used for formatting calendar sheets *//* w ww.j a va 2 s. c o m*/ private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFCellStyle style; XSSFFont titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 48); titleFont.setColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(titleFont); styles.put("title", style); XSSFFont monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(new XSSFColor(new java.awt.Color(255, 255, 255))); monthFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("month", style); XSSFFont dayFont = wb.createFont(); dayFont.setFontHeightInPoints((short) 14); dayFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 232, 243))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style.setFont(dayFont); styles.put("weekend_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 232, 243))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); styles.put("weekend_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setLeftBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style.setFont(dayFont); styles.put("workday_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); styles.put("workday_right", style); style = wb.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); styles.put("grey_left", style); style = wb.createCellStyle(); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89))); styles.put("grey_right", style); return styles; }
From source file:pe.gob.mef.gescon.util.ExcelUtil.java
/** * Mtodo que crea el estilo de la cabecera. * @return estilo Estilo de la cabecera, tipo XSSFCellStyle. *//* w w w. j av a 2 s. co m*/ public XSSFCellStyle creaEstiloCabecera() { XSSFCellStyle estilo = (XSSFCellStyle) libroExcel.createCellStyle(); estilo.setBorderBottom(XSSFCellStyle.BORDER_THIN); estilo.setBorderTop(XSSFCellStyle.BORDER_THIN); estilo.setBorderRight(XSSFCellStyle.BORDER_THIN); estilo.setBorderLeft(XSSFCellStyle.BORDER_THIN); estilo.setVerticalAlignment(VerticalAlignment.CENTER); estilo.setAlignment(XSSFCellStyle.ALIGN_CENTER); Font fuente = creaFuenteCabecera(); estilo.setFont(fuente); return estilo; }
From source file:pe.gob.mef.gescon.util.ExcelUtil.java
/** * Mtodo que crea el estilo para las filas pares con borde derecho. * @return estilo Estilo creado, tipo XSSFCellStyle. *///w ww. j av a2 s .c om public XSSFCellStyle crearEstiloBordeDerechoPar() { XSSFCellStyle estilo = (XSSFCellStyle) this.libroExcel.createCellStyle(); estilo.setBorderRight(XSSFCellStyle.BORDER_THIN); Font fuente = creaFuenteFilaPar(); estilo.setFont(fuente); return estilo; }
From source file:pe.gob.mef.gescon.util.ExcelUtil.java
/** * Mtodo que crea el estilo para las filas impares con borde derecho. * @return estilo Estilo creado, tipo XSSFCellStyle. */// w w w.j av a 2 s.c o m public XSSFCellStyle crearEstiloBordeDerechoImpar() { XSSFCellStyle estilo = (XSSFCellStyle) this.libroExcel.createCellStyle(); estilo.setBorderRight(XSSFCellStyle.BORDER_THIN); Font fuente = creaFuenteFilaImpar(); estilo.setFont(fuente); estilo.setFillForegroundColor(new XSSFColor(new java.awt.Color(202, 225, 255))); estilo.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); return estilo; }
From source file:reports.achievedReport.java
public String getAchievedReport(int passedYear, String passedPath, String dates) throws InvalidFormatException, IOException, SQLException { pepfaryear = passedYear;/*w w w . j ava 2 s . c o m*/ full_date = dates; dbConn conn = new dbConn(); pos = 0; incrementor = 0; prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); String allpath = passedPath; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("PARTNER NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getClients = "SELECT partner.partner_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,personal_information.completionyear," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",completionmonth,COUNT(personal_information.client_id) FROM personal_information " + "JOIN partner ON personal_information.partner_id=partner.partner_id " + " WHERE personal_information.completionmonth>0 && personal_information.completionyear>0 GROUP BY partner.partner_name,SEX,personal_information.completionyear,MONTHS,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; partnername = conn.rs.getString(1); month = conn.rs.getString(2); year = conn.rs.getInt(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = year + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); achieved = conn.rs.getInt(7); incrementor += achieved; System.out.println( "date key : " + datekey + "startdate : " + start + " end date : " + end + " year : " + year); if (datekey >= start && datekey <= end && year >= 2014) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(partnername); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } } for (int i = 0; i < myalphabet.length; i++) { try { System.out.println("at position : " + myalphabet[i]); String current_drive = myalphabet[i]; File f3 = new File(current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION"); // CREATE A DIRECTORY AND THE FILE TO HOLD DATA if (f3.exists() && f3.isDirectory()) { path = current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION\\DATA\\REPORTS"; new File(path).mkdirs(); filePath = path + "\\PWP_ATTACHED_REPORT" + full_date + ".xlsm"; } //select the last timestamp which a backup was picked from..... } finally { } } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } //if(incrementor==0){ // filePath="noreport"; //} //else{ //// url="no url to the report"; //} return filePath; }
From source file:reports.allStaticReports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {// w w w .j a va 2s .co m response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = ""; String county = ""; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1' order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " , isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont 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); XSSFFont 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); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.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_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; } rowpos++; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); 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=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReports.class.getName()).log(Level.SEVERE, null, ex); } }