List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion
int addMergedRegion(CellRangeAddress region);
From source file:net.illustrato.ctrl.CtrlCore.java
/** * * Este metodo toma la informacion que se diligencia en la clase y genera un * archivo en excel a partir de una plantilla. * * @param pathPlan la ruta donde esta la plantilla en excel HU3 * @param pathDeex la ruta donde se va a depositar el excel generado. * @param nombResa Nombre reporte de salida. * @throws Exception indica que el metodo genera excepciones que deben ser * capturadas para identificar cuando la misma no funcione bien. * *//*from w w w . ja v a2 s.co m*/ @Override public void escribirExcel(String pathPlan, String pathDeex, String nombResa) throws Exception { LOGGER.info("Iniciando escritura del archivo en excel"); LOGGER.debug("Ruta de la plantilla {}", pathPlan); LOGGER.debug("Ruta donde se va a escribir la plantilla {} ", pathDeex); //Archivo Origen File archOrig = null; //Archivo Destino File archDest = null; //ruta completa de la plantilla String pathDefi = pathDeex + File.separator + nombResa; //Registra del archivo de excel Row row = null; //Celda en el archivo de excel Cell cell; //Hoja de excel Sheet sheet = null; //Numero de hojas en el libro de excel int numberOfSheets; //Constantes final String NOMBRE_HOJA = "RESULTADOS EVALUACION"; // Fila y columna para int fila = 0; int columna = 0; //Fila inicio evidencia int filaEvid; try { archOrig = new File(pathPlan); if (!archOrig.exists()) { LOGGER.debug("Plantilla no existe en la ruta {} ", pathPlan); throw new IOException("La plantilla no existe en la ruta " + pathPlan); } archDest = new File(pathDeex); if (!archDest.exists()) { LOGGER.debug("Ruta no existe donde se va a depositar el excel {} , se va a crear", pathDeex); archDest.mkdirs(); } LOGGER.info("Ruta del archivo a crear {}", pathDefi); archDest = new File(pathDefi); if (!archDest.exists()) { LOGGER.info("No existe el archivo en la ruta {}, se procede a la creacion ", pathDefi); archDest.createNewFile(); } else { LOGGER.info("el archivo que se requiere crear, ya existe {} se va a recrear", pathDefi); archDest.delete(); LOGGER.info("archivo en la ruta {}, borrado", pathDefi); archDest.createNewFile(); LOGGER.info("archivo en la ruta {}, se vuelve a crear", pathDefi); } LOGGER.info("Se inicia con la copia de la plantilla de la ruta {} a la ruta {} ", pathPlan, pathDefi); try (FileChannel archTror = new FileInputStream(archOrig).getChannel(); FileChannel archTrDe = new FileOutputStream(archDest).getChannel();) { archTrDe.transferFrom(archTror, 0, archTror.size()); LOGGER.info("Termina la copia del archivo"); } catch (Exception e) { LOGGER.info("Se genera un error con la transferencia {} ", e.getMessage()); throw new Exception("Error [" + e.getMessage() + "]"); } LOGGER.info("Se inicia con el diligenciamiento del formato "); LOGGER.info("Nombre Archivo {}", archDest.getName()); if (!archDest.getName().toLowerCase().endsWith("xls")) { throw new Exception("La plantilla debe tener extension xls"); } try (FileInputStream fis = new FileInputStream(archDest); Workbook workbook = new HSSFWorkbook(fis); FileOutputStream fos = new FileOutputStream(archDest);) { if (workbook != null) { numberOfSheets = workbook.getNumberOfSheets(); LOGGER.debug("Numero de hojas {}", numberOfSheets); LOGGER.info("Hoja seleccionada:{}", NOMBRE_HOJA); sheet = workbook.getSheetAt(0); fila = 5; LOGGER.info("Se inicia con la escritura de las oportunidades de mejora"); LOGGER.info("Creando las celdas a llenar"); for (int numeFila = fila; numeFila < this.listOpme.size() + fila; numeFila++) { LOGGER.info("Fila {}", numeFila); if (numeFila > 8) { copyRow(workbook, sheet, numeFila - 2, numeFila - 1); sheet.addMergedRegion(new CellRangeAddress(numeFila - 1, numeFila - 1, 1, 4)); sheet.addMergedRegion(new CellRangeAddress(numeFila - 1, numeFila - 1, 6, 7)); } } LOGGER.info("Terminando de llenar celdas"); LOGGER.info("Poblar registros desde {} ", fila); for (OptuMejo optuMejo : this.listOpme) { LOGGER.debug("Se va actualizar la linea {} celda 1. Valor Capacitacion tecnica {}", fila, optuMejo.getCapaTecn()); row = null; cell = null; row = sheet.getRow(fila); cell = row.getCell(1); cell.setCellValue(optuMejo.getCapaTecn()); LOGGER.debug("Se va actualizar la linea {} celda 6. Valor compromisos del area {}", fila, optuMejo.getComporta()); row = null; cell = null; row = sheet.getRow(fila); cell = row.getCell(6); cell.setCellValue(optuMejo.getComporta()); fila++; } LOGGER.info("Termino de poblar el registro hasta {} ", fila); //Ajustando los formulario if (fila > 8) { sheet.addMergedRegion(new CellRangeAddress(fila, fila, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(fila + 1, fila + 1, 2, 6)); } else { fila = 9; } /* sheet.addMergedRegion(new CellRangeAddress(fila, fila, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(fila + 1, fila + 1, 2, 6));*/ LOGGER.info("Fin de la escritura de las oportunidades de mejora"); LOGGER.info("Se inicia la escritura de las evidencias "); fila += 2; filaEvid = fila + 5; LOGGER.info("Se inicia la creacion de las celdas desde el registro {} ", fila); for (Evidenci evidenci : this.listEvid) { if (filaEvid < fila) { copyRow(workbook, sheet, fila - 1, fila); } LOGGER.debug("Se va actualizar la linea {} celda 1. Valor Fecha {}", fila, evidenci.getFecha()); row = null; cell = null; row = sheet.getRow(fila); cell = row.getCell(1); cell.setCellValue(evidenci.getFecha()); LOGGER.debug("Se va actualizar la linea {} celda 6. Valor compromisos del area {}", fila, evidenci.getDescripc()); row = null; cell = null; row = sheet.getRow(fila); cell = row.getCell(2); cell.setCellValue(evidenci.getDescripc()); sheet.addMergedRegion(new CellRangeAddress(fila, fila, 2, 6)); fila++; } LOGGER.info("Fin de la escritura de las Evidencias"); LOGGER.info("Inicio de escritura de calificaciones"); //Ajustando los formulario - resultado /*sheet.addMergedRegion(new CellRangeAddress(fila, fila, 1, 7));*/ if (fila > filaEvid) { LOGGER.info("Fila a ejecutar {}", fila); sheet.addMergedRegion(new CellRangeAddress(fila + 1, fila + 1, 2, 5)); sheet.addMergedRegion(new CellRangeAddress(fila + 2, fila + 2, 2, 5)); sheet.addMergedRegion(new CellRangeAddress(fila + 3, fila + 3, 2, 5)); sheet.addMergedRegion(new CellRangeAddress(fila + 4, fila + 4, 2, 5)); sheet.addMergedRegion(new CellRangeAddress(fila + 1, fila + 1, 6, 7)); sheet.addMergedRegion(new CellRangeAddress(fila + 2, fila + 4, 6, 7)); //Firma del evaluado ajuste sheet.addMergedRegion(new CellRangeAddress(fila + 5, fila + 5, 1, 3)); sheet.addMergedRegion(new CellRangeAddress(fila + 5, fila + 5, 4, 6)); //Ajustando recursos sheet.addMergedRegion(new CellRangeAddress(fila + 6, fila + 6, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(fila + 8, fila + 8, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(fila + 10, fila + 10, 1, 7)); } else { fila = filaEvid + 1; LOGGER.info("Fila a ejecutar {}", fila); } LOGGER.debug("Se va actualizar la linea {} celda 2. Valor Excelente {}", fila + 2, this.excelent); row = null; cell = null; row = sheet.getRow(fila + 2); cell = row.getCell(2); cell.setCellValue((this.excelent != null ? this.excelent : "")); LOGGER.debug("Se va actualizar la linea {} celda 2. Valor satisfactorio {}", fila + 3, this.satisfac); row = null; cell = null; row = sheet.getRow(fila + 3); cell = row.getCell(2); cell.setCellValue((this.satisfac != null ? this.satisfac : "")); LOGGER.debug("Se va actualizar la linea {} celda 3. Valor no satisfactorio {}", fila + 4, this.noSatisf); row = null; cell = null; row = sheet.getRow(fila + 4); cell = row.getCell(2); cell.setCellValue((this.noSatisf != null ? this.noSatisf : "")); //Ajustando Total Calificacion en Numero LOGGER.debug("Se va actualizar la linea {} celda 2. Valor total calificacion {}", fila + 2, this.numeToca); row = null; cell = null; row = sheet.getRow(fila + 2); cell = row.getCell(6); cell.setCellValue(this.numeToca); LOGGER.info("Fin de escritura de calificaciones"); LOGGER.info("Inicio de escritura de interposicion de recursos"); LOGGER.debug("Se va actualizar la linea {} celda 5. Valor si interpone recursos {}", fila + 7, this.siinRecu); row = null; cell = null; row = sheet.getRow(fila + 7); cell = row.getCell(6); cell.setCellValue("SI:" + (this.siinRecu != null ? this.siinRecu : "")); LOGGER.debug("Se va actualizar la linea {} celda 5. Valor si interpone recursos {}", fila + 7, this.noinRecu); row = null; cell = null; row = sheet.getRow(fila + 7); cell = row.getCell(7); cell.setCellValue("NO:" + (this.noinRecu != null ? this.noinRecu : "")); LOGGER.debug("Se va actualizar la linea {} celda 5. Valor si interpone recursos {}", fila + 8, this.fech); row = null; cell = null; row = sheet.getRow(fila + 8); cell = row.getCell(1); cell.setCellValue("FECHA:" + (this.fech != null ? this.fech : "")); LOGGER.info("Fin de escritura de interposicion de recursos"); //Ajustando recursos workbook.write(fos); } else { throw new Exception("No se cargo de manera adecuada el archivo "); } } catch (Exception e) { System.out.println("" + e.getMessage()); } } catch (Exception e) { LOGGER.error(e.getMessage()); throw new Exception(e.getMessage()); } }
From source file:net.rrm.ehour.ui.common.report.AbstractExcelReport.java
License:Open Source License
protected int createHeaders(int rowNumber, Sheet sheet, Report report, ExcelWorkbook workbook) { Row row = sheet.createRow(rowNumber++); CellFactory.createCell(row, 0, getHeaderReportName(), workbook, ExcelStyle.BOLD_FONT); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1)); row = sheet.createRow(rowNumber++);// w w w .j a va2 s . c o m CellFactory.createCell(row, 0, new ResourceModel("report.dateStart"), workbook, ExcelStyle.BOLD_FONT); if (report.getReportRange() == null || report.getReportRange().getDateStart() == null) { CellFactory.createCell(row, 1, "--", workbook, ExcelStyle.BOLD_FONT); } else { CellFactory.createCell(row, 1, report.getReportCriteria().getReportRange().getDateStart(), workbook, ExcelStyle.BOLD_DATE); } CellFactory.createCell(row, 3, new ResourceModel("report.dateEnd"), workbook, ExcelStyle.BOLD_FONT); if (report.getReportRange() == null || report.getReportRange().getDateEnd() == null) { CellFactory.createCell(row, 4, "--", workbook, ExcelStyle.BOLD_FONT); } else { CellFactory.createCell(row, 4, report.getReportCriteria().getReportRange().getDateEnd(), workbook, ExcelStyle.BOLD_DATE); } rowNumber++; return rowNumber; }
From source file:net.sf.excelutils.ExcelParser.java
License:Apache License
/** * parse the cell//from www . j a va 2 s .co m * * @param context data object * @param cell excel cell */ public static void parseCell(Object context, Sheet sheet, Row row, Cell cell) { String str = cell.getStringCellValue(); if (null == str || "".equals(str)) { return; } if (str.indexOf(VALUED_DELIM) < 0) return; boolean bJustExpr = str.length() == (str.length() - str.lastIndexOf(VALUED_DELIM)); boolean bMerge = "!".equals(str.substring(str.indexOf(VALUED_DELIM) + VALUED_DELIM.length(), str.indexOf(VALUED_DELIM) + VALUED_DELIM.length() + 1)); if (str.indexOf(VALUED_DELIM) < 0) return; Object value = parseStr(context, str); // replace the cell if (null != value) { if (bJustExpr && "java.lang.Integer".equals(value.getClass().getName())) { cell.setCellValue(Double.parseDouble(value.toString())); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (bJustExpr && "java.lang.Double".equals(value.getClass().getName())) { cell.setCellValue(((Double) value).doubleValue()); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (bJustExpr && "java.util.Date".equals(value.getClass().getName())) { cell.setCellValue((Date) value); } else if (bJustExpr && "java.lang.Boolean".equals(value.getClass().getName())) { cell.setCellValue(((Boolean) value).booleanValue()); cell.setCellType(Cell.CELL_TYPE_BOOLEAN); } else if (bJustExpr && Number.class.isAssignableFrom(value.getClass())) { cell.setCellValue(((Number) (value)).doubleValue()); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else { // cell.setEncoding(Workbook.ENCODING_UTF_16); POI3.2? cell.setCellValue(value.toString()); } } else { cell.setCellValue(""); } // merge the cell that has a "!" character at the expression if (row.getRowNum() - 1 >= sheet.getFirstRowNum() && bMerge) { Row lastRow = WorkbookUtils.getRow(row.getRowNum() - 1, sheet); Cell lastCell = WorkbookUtils.getCell(lastRow, cell.getColumnIndex()); boolean canMerge = false; if (lastCell.getCellType() == cell.getCellType()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: canMerge = lastCell.getStringCellValue().equals(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: canMerge = lastCell.getBooleanCellValue() == cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: canMerge = lastCell.getNumericCellValue() == cell.getNumericCellValue(); break; } } if (canMerge) { CellRangeAddress region = new CellRangeAddress(lastRow.getRowNum(), row.getRowNum(), lastCell.getColumnIndex(), cell.getColumnIndex()); sheet.addMergedRegion(region); } } }
From source file:net.sf.excelutils.tags.EachTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { String expr = ""; String each = curCell.getStringCellValue(); LOG.debug("EachTag:" + each); StringTokenizer st = new StringTokenizer(each, " "); String widthstr = ""; String onstr = ""; int pos = 0;/*from w w w . j a v a 2s. c o m*/ while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { expr = str; } if (pos == 2 && !"on".equals(str)) { widthstr = str; } if (pos == 3 && !"on".equals(str)) { onstr = str; } if (pos == 4) { onstr = str; } pos++; } int[] widths = new int[0]; if (null != widthstr && !"".equals(widthstr)) { Object o = ExcelParser.parseStr(context, widthstr); if (null != o) { String[] s = o.toString().split(","); widths = new int[s.length]; for (int i = 0; i < widths.length; i++) { widths[i] = Integer.parseInt(s[i]); } } } Object obj = ExcelParser.parseExpr(context, expr); if (null == obj) return new int[] { 0, 0, 0 }; // by onstr get the property if (!"".equals(onstr)) { obj = ExcelParser.parseExpr(context, onstr); if (null == obj) return new int[] { 0, 0, 0 }; } // iterator properties Iterator it = ExcelParser.getIterator(obj); if (null == it) { if (obj instanceof DynaBean) { it = ExcelParser.getIterator(ExcelParser.getBeanProperties(((DynaBean) obj).getDynaClass())); } else { it = ExcelParser.getIterator(ExcelParser.getBeanProperties(obj.getClass())); } } if (null == it) { return new int[] { 0, 0, 0 }; } int index = 0; int arrayIndex = 0; int eachPos = curCell.getColumnIndex(); String modelName = expr.substring(ExcelParser.VALUED_DELIM.length(), expr.length() - ExcelParser.VALUED_DELIM2.length()); // restore the obj obj = ExcelParser.parseExpr(context, expr); while (it.hasNext()) { Object o = it.next(); String property = ""; if (o instanceof Field) { property = ((Field) o).getName(); } else if (o instanceof Map.Entry) { property = ((Map.Entry) o).getKey().toString(); } else if (o instanceof DynaProperty) { property = ((DynaProperty) o).getName(); } else if (null != o) { property = o.toString(); } // test the object is array/list or other if (obj.getClass().isArray() || obj instanceof Collection) { property = modelName + "[" + arrayIndex++ + "]"; } else { property = modelName + "." + property; } Object value = ExcelParser.getValue(context, property); if (null == value) value = ""; if (ExcelUtils.isCanShowType(value)) { // get cell merge count int width = 1; if (index < widths.length) { width = widths[index]; } else if (1 == widths.length) { width = widths[0]; } // get row merged of the curCell int rowMerged = 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == curCell.getColumnIndex() && r.getLastColumn() == curCell.getColumnIndex()) { rowMerged = r.getLastRow() - r.getFirstRow() + 1; break; } } Cell cell = WorkbookUtils.getCell(curRow, eachPos); // shift the after cell if (index > 0) { WorkbookUtils.shiftCell(sheet, curRow, cell, 1, rowMerged); } if (width > 1) { Cell nextCell = WorkbookUtils.getCell(curRow, eachPos + 1); WorkbookUtils.shiftCell(sheet, curRow, nextCell, width - 1, rowMerged); } // copy the style of curCell for (int rownum = curRow.getRowNum(); rownum < curRow.getRowNum() + rowMerged; rownum++) { for (int i = 0; i < width; i++) { Row r = WorkbookUtils.getRow(rownum, sheet); Cell c = WorkbookUtils.getCell(r, eachPos + i); Cell cc = WorkbookUtils.getCell(r, curCell.getColumnIndex()); c.setCellStyle(cc.getCellStyle()); } } // merge cells if (width > 1 || rowMerged > 1) { sheet.addMergedRegion( new CellRangeAddress(curRow.getRowNum(), curRow.getRowNum() + rowMerged - 1, cell.getColumnIndex(), cell.getColumnIndex() + width - 1)); } cell.setCellValue("${" + property + "}"); ExcelParser.parseCell(context, sheet, curRow, cell); eachPos += width; index++; } } return new int[] { 0, 0, 0 }; }
From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
/** * copy row//from w ww.j a v a 2 s . com * * @param sheet * @param from begin of the row * @param to destination fo the row * @param count count of copy */ public static void copyRow(Sheet sheet, int from, int to, int count) { for (int rownum = from; rownum < from + count; rownum++) { Row fromRow = sheet.getRow(rownum); Row toRow = getRow(to + rownum - from, sheet); if (null == fromRow) return; toRow.setHeight(fromRow.getHeight()); toRow.setHeightInPoints(fromRow.getHeightInPoints()); int lastCellNum = fromRow.getLastCellNum(); lastCellNum = lastCellNum > 255 ? 255 : lastCellNum; for (int i = fromRow.getFirstCellNum(); i <= lastCellNum && i >= 0; i++) { Cell fromCell = getCell(fromRow, i); Cell toCell = getCell(toRow, i); // toCell.setEncoding(fromCell.getEncoding()); toCell.setCellStyle(fromCell.getCellStyle()); toCell.setCellType(fromCell.getCellType()); switch (fromCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: toCell.setCellFormula(fromCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; default: } } } // copy merged region List shiftedRegions = new ArrayList(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= from && r.getLastRow() < from + count) { CellRangeAddress n_r = new CellRangeAddress(r.getFirstRow() + to - from, r.getLastRow() + to - from, r.getFirstColumn(), r.getLastColumn()); shiftedRegions.add(n_r); } } // readd so it doesn't get shifted again Iterator iterator = shiftedRegions.iterator(); while (iterator.hasNext()) { CellRangeAddress region = (CellRangeAddress) iterator.next(); sheet.addMergedRegion(region); } }
From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) { if (shift == 0) return;//from w w w .j av a2 s . c o m // get the from & to row int fromRow = row.getRowNum(); int toRow = row.getRowNum() + rowCount - 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == row.getRowNum()) { if (r.getLastRow() > toRow) { toRow = r.getLastRow(); } if (r.getFirstRow() < fromRow) { fromRow = r.getFirstRow(); } } } for (int rownum = fromRow; rownum <= toRow; rownum++) { Row curRow = WorkbookUtils.getRow(rownum, sheet); int lastCellNum = curRow.getLastCellNum(); for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) { Cell fromCell = WorkbookUtils.getCell(curRow, cellpos); Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift); toCell.setCellType(fromCell.getCellType()); toCell.setCellStyle(fromCell.getCellStyle()); switch (fromCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: toCell.setCellFormula(fromCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; case Cell.CELL_TYPE_ERROR: toCell.setCellErrorValue(fromCell.getErrorCellValue()); break; } fromCell.setCellValue(""); fromCell.setCellType(Cell.CELL_TYPE_BLANK); // Workbook wb = new Workbook(); // CellStyle style = wb.createCellStyle(); // fromCell.setCellStyle(style); } // process merged region for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) { Cell fromCell = WorkbookUtils.getCell(curRow, cellpos); List shiftedRegions = new ArrayList(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) { r.setFirstColumn((short) (r.getFirstColumn() + shift)); r.setLastColumn((short) (r.getLastColumn() + shift)); // have to remove/add it back shiftedRegions.add(r); sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } // readd so it doesn't get shifted again Iterator iterator = shiftedRegions.iterator(); while (iterator.hasNext()) { CellRangeAddress region = (CellRangeAddress) iterator.next(); sheet.addMergedRegion(region); } } } }
From source file:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java
License:LGPL
private void createDashBoard(Sheet sheet, String[] measureNames, List<FEMModelMeasure> measures, Map<String, CellStyle> styles) { // Create header - metaData is expected to be csv // Each user variable has 4 additional columns int maxMetaDataColumns = getMaxMetaDataColumns(measures); int rowCounter = 0; // Create header row Row headerRow1 = sheet.createRow(rowCounter++); Row headerRow2 = sheet.createRow(rowCounter++); Row headerRow3 = sheet.createRow(rowCounter++); // Create column headers Cell headerCell;/* ww w. j av a2 s .c om*/ int colCtr = 0; { int colID = colCtr++; headerRow1.createCell(colID); headerRow2.createCell(colID); headerCell = headerRow3.createCell(colID); /* * headerCell.setCellValue("Sno"); * headerCell.setCellStyle(styles.get("header")); */ sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$A$3")); headerRow1.getCell(0).setCellValue("Sno"); headerRow1.getCell(0).setCellStyle(styles.get("header")); } for (int i = 0; i < maxMetaDataColumns; i++) { int colID = colCtr++; String xcolID = getColumnPrefix(colID); headerRow3.createCell(colID); headerRow2.createCell(colID); headerCell = headerRow1.createCell(colID); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + xcolID + "$1:$" + xcolID + "$3")); headerCell.setCellValue("Model Identifier " + i); headerCell.setCellStyle(styles.get("header")); } String[] compHeader = { "MAX", "MIN", "AVERAGE", "STDEV" }; for (int i = 0; i < userSeries.size(); i++) { int startColID = colCtr; // For each measure for (int j = 0; j < measureNames.length; j++) { int mstartColID = colCtr; // The four possible composites for (int k = 0; k < compHeader.length; k++) { int colID = colCtr++; headerRow1.createCell(colID); headerRow2.createCell(colID); headerCell = headerRow3.createCell(colID); headerCell.setCellValue(compHeader[k]); headerCell.setCellStyle(styles.get("header")); } headerRow2.getCell(mstartColID).setCellValue(measureNames[j]); headerRow2.getCell(mstartColID).setCellStyle(styles.get("formula")); String startColPrefix = getColumnPrefix(mstartColID); String endColPrefix = getColumnPrefix(colCtr - 1); sheet.addMergedRegion( CellRangeAddress.valueOf("$" + startColPrefix + "$2:$" + endColPrefix + "$2")); } String[] exp = userSeries.get(i).split("="); headerRow1.getCell(startColID).setCellValue(exp[0]); headerRow1.getCell(startColID).setCellStyle(styles.get("title")); String startColPrefix = getColumnPrefix(startColID); String endColPrefix = getColumnPrefix(colCtr - 1); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + startColPrefix + "$1:$" + endColPrefix + "$1")); } // For each model output the data int ctr = 1; for (FEMModelMeasure model : measures) { Row row = sheet.createRow(rowCounter++); colCtr = 0; Cell cell = row.createCell(colCtr++); cell.setCellValue(ctr++); String[] metaData = model.getMetaData().split("\t"); int mLength = metaData.length; for (int i = 0; i < mLength; i++) { cell = row.createCell(colCtr++); cell.setCellValue(metaData[i]); } while (mLength < maxMetaDataColumns) { cell = row.createCell(colCtr++); mLength++; } // String modelName = model.getModelName().replaceAll(" ", // "_").trim();; for (int i = 0; i < userSeries.size(); i++) { String[] exp = userSeries.get(i).split("="); for (int j = 0; j < measureNames.length; j++) { double[][] strains = model.getMeasure(measureNames[j]); if (strains == null) continue; // Measure//userSeries Hashtable<String, String> map = model.getFormulaMap(measureNames[j]); try { String[] formulas = map.get(exp[0]).split("#"); for (int k = 0; k < compHeader.length; k++) { // String sname = // modelName+"_"+measureNames[j]+"_"+compHeader[k]+"_"+exp[0]; cell = row.createCell(colCtr++); cell.setCellFormula(formulas[k]); } } catch (Exception exx) { //exx.printStackTrace(); System.out.println(exx + " occured for expresion " + exp[0]); } } } } }
From source file:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java
License:LGPL
private void addWorkSheet(Workbook wb, String measure, List<FEMModelMeasure> measures, int maxTimePoints, Map<String, CellStyle> styles) { Sheet sheet = wb.createSheet(measure); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);// ww w . j a va 2 s . c o m sheet.setHorizontallyCenter(true); final int leadingHeaders = 4; int rowCounter = 0; // Create header row Row headerRow = sheet.createRow(rowCounter++); headerRow.setHeightInPoints(40); Cell headerCell; int colCtr = 0; { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("ModelName"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("StartTime"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("EndTime"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MetaData"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("ID"); headerCell.setCellStyle(styles.get("header")); } // Insert Time points for (int i = 0; i < maxTimePoints; i++) { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("" + i); headerCell.setCellStyle(styles.get("header")); } // Insert composite variables { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MAX"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MIN"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MEAN"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("SD"); headerCell.setCellStyle(styles.get("header")); } // Output the values for each measure for (FEMModelMeasure mes : measures) { double[][] strains = mes.getMeasure(measure); if (strains == null) continue; int numRows = strains.length + 1; // 1 for Avg int rowStarts = rowCounter; int colCounter = 0; for (int rctr = 0; rctr < numRows - 1; rctr++) { colCounter = 0; int myRowID = rowCounter + 1; Row row = sheet.createRow(rowCounter++); for (int colc = 0; colc < leadingHeaders; colc++) { // Common // Elements row.createCell(colCounter++); } // Create ROW ID { Cell cell = row.createCell(colCounter++); cell.setCellValue("S" + (rctr + 1)); } String strainStartXLColName = getColumnPrefix(colCounter); int strainLength = strains[rctr].length; for (int stc = 0; stc < strainLength; stc++) { Cell cell = row.createCell(colCounter++); cell.setCellValue(strains[rctr][stc]); } String strainEndXLColName = getColumnPrefix(colCounter - 1); while (strainLength < maxTimePoints) { // Create dummy cells to // fill up space row.createCell(colCounter++); strainLength++; } // Add formulas and create names { //String modelName = mes.getModelName(); //String sname = ""; // MAX Cell cell = row.createCell(colCounter++); String ref = strainStartXLColName + "" + myRowID + ":" + strainEndXLColName + "" + myRowID; cell.setCellFormula("MAX(" + ref + ")"); cell.setCellStyle(styles.get("MAX")); /* * sname = * (modelName+"_"+measure+"_"+"MAX_S"+(rctr+1)..replaceAll * (" ", "_").trim(); Name namedCel = wb.createName(); * namedCel.setNameName(sname); String reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // MIN cell = row.createCell(colCounter++); cell.setCellFormula("MIN(" + ref + ")"); cell.setCellStyle(styles.get("MIN")); /* * sname = modelName+"_"+measure+"_"+"MIN_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // MEAN cell = row.createCell(colCounter++); cell.setCellFormula("AVERAGE(" + ref + ")"); cell.setCellStyle(styles.get("AVERAGE")); /* * sname = modelName+"_"+measure+"_"+"AVERAGE_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // STANDARD DEVIATION cell = row.createCell(colCounter++); cell.setCellFormula("STDEV(" + ref + ")"); cell.setCellStyle(styles.get("STDEV")); /* * sname = modelName+"_"+measure+"_"+"STDEV_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+""+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ } } // Add user defined series Hashtable<String, String> formulaMap = new Hashtable<String, String>(); for (String exp : userSeries) { // Replace all S[0-9]*, and D[0-9]* with appropriate column // values String toks[] = exp.split("="); int myRowID = rowCounter; colCounter = 0; Row row = sheet.createRow(rowCounter++); for (int colc = 0; colc < leadingHeaders; colc++) { // Common // Elements row.createCell(colCounter++); } // Create ROW ID { Cell cell = row.createCell(colCounter++); cell.setCellValue(toks[0]); } String strainStartXLColName = getColumnPrefix(colCounter); int strainLength = strains[numRows - 2].length; for (int stc = 0; stc < strainLength; stc++) { Cell cell = row.createCell(colCounter++); // Get the expression String expression = toks[1].toLowerCase();// Regex is case // senstive, // since th // COLUMN // PREFIXs are // CAPS, // replaceAll // will work as // expected else // S17 will // mathc for S1 // (but not s1) for (int sCtr = mes.numSegments; sCtr > 0; sCtr--) { String XLColName = (char) ('A' + stc + leadingHeaders + 1) + "" + (rowStarts + sCtr); // Note // excel // formulas // need // base // 1 expression = expression.replaceAll("s" + sCtr + "{1}", XLColName); } cell.setCellFormula(expression); cell.setCellStyle(styles.get("AVGSERIES")); } String strainEndXLColName = getColumnPrefix(colCounter - 1); while (strainLength < maxTimePoints) { // Create dummy cells to // fill up space row.createCell(colCounter++); strainLength++; } // Add formulas and create names { StringBuffer formulas = new StringBuffer(); String modelName = mes.getModelName(); char c = modelName.charAt(0); if (c >= '0' && c <= '9') { modelName = "_" + modelName; } String measureName = measure.replaceAll("\\(", "_").replaceAll("\\)", "_").replaceAll(" ", ""); String sname = ""; // MAX Cell cell = row.createCell(colCounter++); String ref = strainStartXLColName + myRowID + ":" + strainEndXLColName + myRowID; cell.setCellFormula("MAX(" + ref + ")"); cell.setCellStyle(styles.get("MAX")); sname = (modelName + "_" + measureName + "_" + "MAX_" + toks[0]).replaceAll(" ", "_").trim(); Name namedCel = wb.createName(); namedCel.setNameName(sname); String reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base try { namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // MIN cell = row.createCell(colCounter++); cell.setCellFormula("MIN(" + ref + ")"); cell.setCellStyle(styles.get("MIN")); sname = (modelName + "_" + measureName + "_" + "MIN_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // MEAN cell = row.createCell(colCounter++); cell.setCellFormula("AVERAGE(" + ref + ")"); cell.setCellStyle(styles.get("AVERAGE")); sname = (modelName + "_" + measureName + "_" + "AVERAGE_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // STANDARD DEVIATION cell = row.createCell(colCounter++); cell.setCellFormula("STDEV(" + ref + ")"); cell.setCellStyle(styles.get("STDEV")); sname = (modelName + "_" + measureName + "_" + "STDEV_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference); formulaMap.put(toks[0], formulas.toString()); } catch (Exception exx) { //exx.printStackTrace(); System.out.println(exx + " occured for formula " + reference); } } } mes.addToFormulaMap(measure, formulaMap); // Set the commom columns Row row; Cell cell; row = sheet.getRow(rowStarts); cell = row.getCell(0); cell.setCellValue(mes.getModelName()); cell = row.getCell(1); cell.setCellValue(mes.getStartTime()); cell = row.getCell(2); cell.setCellValue(mes.getEndTime()); cell = row.getCell(3); cell.setCellValue(mes.getMetaData()); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (rowStarts + 1) + ":$A$" + (rowCounter))); // Since // excel // number // starts // from // 1 // but // api // is // 0 sheet.addMergedRegion(CellRangeAddress.valueOf("$B$" + (rowStarts + 1) + ":$B$" + (rowCounter))); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (rowStarts + 1) + ":$C$" + (rowCounter))); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + (rowStarts + 1) + ":$D$" + (rowCounter))); sheet.createRow(rowCounter++);// Create Empty row for model break } }
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * //from w w w . j a va2 s .co m * method name : getExcelSurveyReport * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : Get Streaming excel object for valid/invalid survey report * * Date : Mar 16, 2016 1:23:57 PM */ public OutputStream getExcelSurveyReport(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramStaffRole = params.get(Constants.CONST_ROLE_STAFF); String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String paramSemesterCode = params.get(Constants.CONST_PARAM_SEMESTER_CODE); String titleRegion = null; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; List<ReportSummary> reportSummaries = (List<ReportSummary>) object; if (templateName.equals(Constants.CONST_VALID_SURVEY_REPORT)) { sheet = workbook.createSheet( UtilProperty.getMessage("prop.course.teaching.survey.report.survey.valid", null, locale)); } if (templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { sheet = workbook.createSheet( UtilProperty.getMessage("prop.course.teaching.survey.report.survey.invalid", null, locale)); } sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey + " - " + paramSemesterCode); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey + " - " + paramSemesterCode); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD) && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.university", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.college", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); } cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.instructor.id", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.instructor.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.college", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) { cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); } cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.course.code", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.student.registered", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (ReportSummary reportSummary : reportSummaries) { int colNum = 0; Row row = sheet.createRow((short) rowNum); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD) && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { row.createCell(colNum++).setCellValue(reportSummary.getUniversityRank()); row.createCell(colNum++).setCellValue(reportSummary.getCollegeRank()); row.createCell(colNum++).setCellValue(reportSummary.getDepartmentRank()); } row.createCell(colNum++).setCellValue(Double.parseDouble(reportSummary.getEmpNumber())); row.createCell(colNum++).setCellValue(creationHelper.createRichTextString(reportSummary.getEmpName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(reportSummary.getCollegeCode())); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) { row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(reportSummary.getDepartmentName())); } row.createCell(colNum++).setCellValue(reportSummary.getCourseCode()); row.createCell(colNum++).setCellValue(Integer.parseInt(reportSummary.getSectionNo())); row.createCell(colNum++).setCellValue(reportSummary.getRegisteredStudent()); Cell cellStudentNoResponse = row.createCell(colNum++); cellStudentNoResponse.setCellValue(reportSummary.getStudentNoResponse()); cellStudentNoResponse.setCellStyle(styles.get(FORMULA_1)); Cell cellTeachingMean = row.createCell(colNum++); cellTeachingMean.setCellValue(reportSummary.getTeachingMean()); cellTeachingMean.setCellStyle(styles.get(FORMULA_1)); Cell cellTeachingPercentageFavor = row.createCell(colNum++); cellTeachingPercentageFavor.setCellValue(reportSummary.getTeachingPercentageFavor()); cellTeachingPercentageFavor.setCellStyle(styles.get(FORMULA_1)); Cell cellQuestionMean = row.createCell(colNum++); cellQuestionMean.setCellValue(reportSummary.getQuestionMean()); cellQuestionMean.setCellStyle(styles.get(FORMULA_1)); Cell cellQuestionPercentageFavor = row.createCell(colNum++); cellQuestionPercentageFavor.setCellValue(reportSummary.getQuestionPercentageFavor()); cellQuestionPercentageFavor.setCellStyle(styles.get(FORMULA_1)); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * /*from ww w . ja va2s . c o m*/ * method name : getExcelCollegeCoursesAsstDean * @param templateName * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : * * Date : Jun 7, 2016 11:49:24 AM */ public OutputStream getExcelCollegeCoursesAsstDean(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String titleRegion = null; List<StudentResponse> studentResponses = (List<StudentResponse>) object; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; sheet = workbook .createSheet(UtilProperty.getMessage("prop.course.teaching.survey.courses.list", null, locale)); sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper .createRichTextString(UtilProperty.getMessage("prop.course.teaching.survey.course", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.seats.taken", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.students", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.include.exclude", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (StudentResponse studentResponse : studentResponses) { int colNum = 0; Row row = sheet.createRow((short) rowNum); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getDepartmentName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getCourseCode())); row.createCell(colNum++).setCellValue(Integer.parseInt(studentResponse.getSectionNo())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpNumber())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpName())); row.createCell(colNum++).setCellValue(studentResponse.getSeatsTaken()); row.createCell(colNum++).setCellValue(studentResponse.getStudentResponse()); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getIncludeExclude())); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }