List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static int createTitle(XSSFWorkbook workbook, XSSFSheet worksheet, Service service) { XSSFCellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 11); txtFont.setBold(true);//from w ww . java 2s . c om titleStyle.setFont(txtFont); XSSFRow row = worksheet.createRow(0); XSSFCell cell = row.createCell(0); cell.setCellValue( "Inventaire " + service.getDirection().getLibelleCourt() + " - " + service.getLibelleCourt()); cell.setCellStyle(titleStyle); worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); return 2; }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();//from www . j av a2 s .co m report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale), InterfaceReport.FORMAT_NOTE); FileOutputStream fos = null; XSSFCreationHelper factory = workbook.getCreationHelper(); XSSFFont boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setBold(true); boldFont.setCharSet(134); boldFont.setFontHeightInPoints((short) 9); XSSFFont plainFont = workbook.createFont(); plainFont.setFontName("Arial"); plainFont.setCharSet(134); plainFont.setFontHeightInPoints((short) 9); XSSFSheet errorSheet = null; if (errorMessages.keySet().size() > 0) { String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors"; errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName); errorSheet = workbook.createSheet(errorSheetName); workbook.setSheetOrder(errorSheetName, 0); workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName)); XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch(); if (drawingPatriarch == null) { drawingPatriarch = errorSheet.createDrawingPatriarch(); } for (int i = 0; i <= getHeaderRowNo(); i++) { XSSFRow newRow = errorSheet.createRow(i); XSSFRow row = sheet.getRow(i); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, drawingPatriarch); } // copy merged regions for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() < getHeaderRowNo()) { errorSheet.addMergedRegion(mergedRegion); } } // copy images List<XSSFPictureData> pics = workbook.getAllPictures(); List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); XSSFAnchor anchor = shape.getAnchor(); if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor; if (clientAnchor.getRow1() < getHeaderRowNo()) { for (int j = 0; j < pics.size(); j++) { XSSFPictureData picture = pics.get(j); if (picture.getPackagePart().getPartName() .equals(pic.getPictureData().getPackagePart().getPartName())) { drawingPatriarch.createPicture(clientAnchor, j); } } } } } } try { // set comments in the original sheet XSSFDrawing patriarch = sheet.getDrawingPatriarch(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol())); boolean isNewComment = false; if (comment == null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(cell.getCol()); anchor.setCol2(cell.getCol() + 4); anchor.setRow1(cell.getRow()); anchor.setRow2(cell.getRow() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); comment = patriarch.createCellComment(anchor); isNewComment = true; } XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n"); rts.applyFont(boldFont); rts.append(errorMessages.get(cell), plainFont); comment.setString(rts); comment.setAuthor("Apache OFBiz PriCat"); if (isNewComment) { sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment); OFBizPricatUtil.formatCommentShape(sheet, cell); } } } // set comments in the new error sheet XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch(); int newRowNum = getHeaderRowNo() + 1; Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFRow row = sheet.getRow(cell.getRow()); Integer rowNum = Integer.valueOf(row.getRowNum()); int errorRow = newRowNum; if (rowMapping.containsKey(rowNum)) { errorRow = rowMapping.get(rowNum).intValue(); } else { XSSFRow newRow = errorSheet.getRow(errorRow); if (newRow == null) { newRow = errorSheet.createRow(errorRow); } rowMapping.put(rowNum, Integer.valueOf(errorRow)); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, errorPatriarch); newRowNum++; } } } // write to file if (sequenceNum > 0L) { File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx"); fos = new FileOutputStream(commentedExcel); workbook.write(fos); } else { fos = new FileOutputStream(pricatFile); workbook.write(fos); } fos.flush(); fos.close(); workbook.close(); } catch (FileNotFoundException e) { report.println(e); Debug.logError(e, module); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (fos != null) { try { fos.close(); } catch (IOException e) { Debug.logError(e, module); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(); }
From source file:org.azkfw.document.database.xlsx.XLSXWriter.java
License:Apache License
private XSSFSheet createTableSheet(final DatabaseModel datasource, final TableModel table, final XSSFSheet sheet) { // // w w w.j a v a2s . co m for (int i = 0; i < 39; i++) { sheet.setColumnWidth(i, 640); } ///////////////////////////////////////////////////////////////////// XSSFRow row = null; @SuppressWarnings("unused") XSSFCell cell = null; int rowIndex = 1; //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.table_info"), styleTitle, row); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.system_name"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, option.getSystemName(), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, Strings.get("doc.creator"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 6, option.getCreateUser(), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(29, 3, Strings.get("doc.create_day"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(32, 6, toString(option.getCreateDate()), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 28)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 29, 31)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 32, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.sub_system_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, option.getSubSystemName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, Strings.get("doc.updater"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 6, option.getUpdateUser(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(29, 3, Strings.get("doc.update_day"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(32, 6, toString(option.getUpdateDate()), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 28)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 29, 31)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 32, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.schema_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getSchema().getName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.logic_table_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getLabel(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.physical_table_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 37, Strings.get("doc.comment"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_DOTTED, BD_RECT, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, BD_RECT), row); row = sheet.createRow(rowIndex + 1); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, BD_RECT), row); row = sheet.createRow(rowIndex + 2); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, BD_RECT, BD_RECT, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 2, 1, 37)); rowIndex += 3; } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.column_info"), styleTitle, row); List<FieldModel> fields = table.getFields(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == fields.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.logic_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 6, Strings.get("doc.physical_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(15, 6, Strings.get("doc.column_type"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, Strings.get("doc.not_null"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, Strings.get("doc.default_value"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.comment"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 14)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 15, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); IndexModel primaryIndex = table.getPrimaryIndex(); rowIndex++; for (int i = 0; i < fields.size(); i++) { int bufRowIndex = rowIndex + i; FieldModel field = fields.get(i); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == fields.size()) { bottom = BD_RECT; } XSSFCellStyle defStylePK = defStyleListValue; if (null != primaryIndex) { if (null != primaryIndex.getField(field.getName())) { defStylePK = defStyleListValuePK; } } String type = field.getType().getLabel(); if (StringUtility.isNotEmpty(field.getExtra())) { type += " " + field.getExtra(); } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, field.getLabel(), styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 6, field.getName(), styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(15, 6, type, styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, toTrue(field.isNotNull()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, toDefault(field), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, field.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 14)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 15, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += fields.size(); } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.index_info"), styleTitle, row); List<IndexModel> indexs = table.getIndexs(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == indexs.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.index_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, Strings.get("doc.primary_key"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, Strings.get("doc.unique_key"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.comment"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); rowIndex++; for (int i = 0; i < indexs.size(); i++) { int bufRowIndex = rowIndex + i; IndexModel index = indexs.get(i); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == indexs.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, index.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toColumnList(index), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, toTrue(index.isPrimaryKey()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, toTrue(index.isUnique()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, index.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += indexs.size(); } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.foreign_key_info"), styleTitle, row); List<ForeignKeyModel> foreignKeys = table.getForeignKeys(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == foreignKeys.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.foreign_key_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, Strings.get("doc.ref_table"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.ref_column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); rowIndex++; for (int i = 0; i < foreignKeys.size(); i++) { int bufRowIndex = rowIndex + i; ForeignKeyModel foreignKey = foreignKeys.get(i); Hyperlink link = createTableLink(foreignKey.getReferenceTableName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == foreignKeys.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, foreignKey.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toForeignKeyColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, foreignKey.getReferenceTableName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(27, 11, toForeignKeyRefColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += foreignKeys.size(); } //////////////////////////////////////////////////////////////////////////// // (Ref) //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.foreign_key_info_ref"), styleTitle, row); int size = 0; for (TableModel targetTable : datasource.getTables()) { if (targetTable.equals(table)) { continue; } List<ForeignKeyModel> foreignKeys = targetTable.getForeignKeys(); for (ForeignKeyModel foreignKey : foreignKeys) { if (!foreignKey.getReferenceTableName().equals(table.getName())) { continue; } size++; } } short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == size) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.foreign_key_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, Strings.get("doc.ref_former_table"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.ref_former_column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); int cnt = 0; rowIndex++; for (TableModel targetTable : datasource.getTables()) { if (targetTable.equals(table)) { continue; } List<ForeignKeyModel> foreignKeys = targetTable.getForeignKeys(); for (ForeignKeyModel foreignKey : foreignKeys) { if (!foreignKey.getReferenceTableName().equals(table.getName())) { continue; } int bufRowIndex = rowIndex + cnt; Hyperlink link = createTableLink(targetTable.getName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (cnt == 0) { top = CellStyle.BORDER_THIN; } if (cnt + 1 == size) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", cnt + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, foreignKey.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toForeignKeyRefColumnList(foreignKey), styleManager.get( defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, targetTable.getName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(27, 11, toForeignKeyColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); cnt++; } } rowIndex += cnt; } workbook.setPrintArea(workbook.getSheetIndex(getTableSheetName(table.getName())), 0, 38, 0, rowIndex); sheet.setAutobreaks(true); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setFitWidth((short) 1); printSetup.setScale((short) 95); return sheet; }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copySheets(XSSFSheet newSheet, XSSFSheet sheet) { int maxColumnNum = 0; Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>(); int mergedReqionsCount = sheet.getNumMergedRegions(); for (int i = 0; i < mergedReqionsCount; ++i) { newSheet.addMergedRegion(sheet.getMergedRegion(i)); }//from ww w . j a v a 2 s.c o m for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { XSSFRow srcRow = sheet.getRow(i); XSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:org.obiba.mica.dataset.search.rest.harmonization.ExcelContingencyWriter.java
License:Open Source License
private void writeTableHeaders(XSSFSheet sheet, String title, List<String> headers) { int colNum = headers.size() + 1; IntStream.rangeClosed(0, 3).forEach(i -> { Row rowTemp = sheet.createRow(i); IntStream.rangeClosed(0, colNum).forEach(j -> rowTemp.createCell(j)); });//from w ww .ja v a 2s.c om Row row = sheet.getRow(0); Cell titleLabel = row.getCell(0); titleLabel.setCellValue(title); titleLabel.setCellStyle(titleStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNum)); row = sheet.getRow(2); Cell crossVarLabel = row.getCell(0); crossVarLabel.setCellValue(this.crossVariable.getName()); sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0)); Cell varLabel = row.getCell(1); varLabel.setCellValue(this.variable.getName()); sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, colNum - 1)); Cell totalLabel = row.getCell(colNum); totalLabel.setCellValue("Total"); sheet.addMergedRegion(new CellRangeAddress(2, 3, colNum, colNum)); int cellnum = 1; row = sheet.getRow(3); for (String h : headers) { Cell cell = row.getCell(cellnum++); cell.setCellValue(h); } addMergedStyles(sheet, new CellRangeAddress(2, 3, 0, colNum)); }
From source file:packtest.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);//from ww w .j a v a 2s . c o m XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); wb.close(); }
From source file:poi.xssf.usermodel.examples.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);//w ww .j av a 2 s . co m XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); }
From source file:reports.allStaticReportsdynamic.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String monthrange = ""; String periodlabel = ""; String period = "Month"; //The default one String periodicgroupby = " "; //note that in the current query there is an existing group by. Therefore this will be an extra infor to be added on the existing group by String isgroupby = "yes"; try {/* w w w . j a v a 2 s . c o m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String months[] = null; 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) { months = request.getParameterValues("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } if (request.getParameter("groupby") != null) { isgroupby = request.getParameter("groupby"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711A")) { form = "MOH711"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; String subcounty_countywhere = ""; String indicatorslist = "all"; String sections = "all"; String subsections = "all"; String indicatorswhere = ""; //________________________________________________________________________________________________________________________________________________________ //________________________________________________________________________________________________________________________________________________________ String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String facil = "361"; String yearmonthstart = ""; String yearmonthend = ""; //===================================================================================================== String header = ""; String reportType = ""; dbConn conn = new dbConn(); if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } if (request.getParameter("indicators") != null) { indicatorslist = request.getParameter("indicators"); } //--------------------------sections------------ String sectionid[] = null; if (request.getParameterValues("sections") != null) { sectionid = request.getParameterValues("sections"); } String sectionvals = "("; if (request.getParameterValues("sections") != null) { for (int a = 0; a < sectionid.length; a++) { if (a == sectionid.length - 1) { sectionvals += sectionid[a] + ""; } else { sectionvals += sectionid[a] + ","; } } } sectionvals += ")"; if (sectionvals.equals("()")) { indicatorswhere += " "; } else { indicatorswhere += " and sectionid in " + sectionvals + " "; } //______________________________________________________subsections_______________________________________ String subsectionid[] = null; if (request.getParameterValues("subsection") != null) { subsectionid = request.getParameterValues("subsection"); } String subsectionvals = "("; if (request.getParameterValues("subsection") != null) { for (int a = 0; a < subsectionid.length; a++) { if (a == subsectionid.length - 1) { subsectionvals += subsectionid[a] + ""; } else { subsectionvals += subsectionid[a] + ","; } } } subsectionvals += ")"; if (subsectionvals.equals("()")) { indicatorswhere += " "; } else { indicatorswhere += " and subsectionid in " + subsectionvals + " "; } //______________________________________________________subsections_______________________________________ //add sections //special indicators if (indicatorslist.equals("special")) { indicatorswhere += " and specialindicator='1'"; } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("facility") != null && reportType.equals("2")) { try { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } } if (request.getParameter("county") != null && reportType.equals("2")) { try { county = request.getParameter("county"); subcounty_countywhere = " (county.CountyID='" + county + "') and ";//20160711 String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } } int yearcopy = Integer.parseInt(year); String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; String currentperiodlabel = "";//a variable to hold the name of a period inside a conn.rs if . header += " YEAR : " + year + ""; // GET REPORT DURATION============================================ //annually //____________________________________________________________________________________________________________Annual____________________________________ if (reportDuration.equals("1")) { yearmonth = "Annual Report For " + year; duration = " and " + form + ".yearmonth BETWEEN '" + prevYear + "10' AND '" + year + "09'"; period = "Year"; periodicgroupby = ", period "; monthrange = year; // tbstatduration="year='"+year+"'"; } //____________________________________________________________________________________________________________Semi_annual_____________________________ else if (reportDuration.equals("2")) { period = "Semi-Annual"; periodicgroupby = ", period "; try { String startMonth = "", endMonth = ""; String semiannualarray[] = request.getParameterValues("semi_annual"); String temporaryheader = " SEMI-ANNUAL"; for (int p = 0; p < semiannualarray.length; p++) { semi_annual = semiannualarray[p]; String getperiodname = "SELECT name as semiannual_name,months FROM semi_annual WHERE id='" + semi_annual + "'"; conn.rs = conn.st.executeQuery(getperiodname); if (conn.rs.next() == true) { String monthsinsemiannual[] = conn.rs.getString("months").split(","); currentperiodlabel = conn.rs.getString("semiannual_name"); //_________________add year at the end of period label if (semi_annual.equals("1")) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("semiannual_name").replace("-", " " + prevYear + "-") + " " + year; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("semiannual_name") + " " + year; } //____________________ if (p == 0) { startMonth = monthsinsemiannual[0]; endMonth = monthsinsemiannual[5]; monthrange = currentperiodlabel; } if (p == semiannualarray.length - 1 && semiannualarray.length > 1) { //last row monthrange += " to " + currentperiodlabel; //by now we expect monthrange to be something like Oct-Mar-Apr-Sep endMonth = monthsinsemiannual[5]; } if (periodlabel.equals("")) { periodlabel = conn.rs.getString("semiannual_name"); } else { periodlabel += "_" + conn.rs.getString("semiannual_name"); } } //end of conn. //get the yearmonthstart date if (p == 0 && semiannualarray.length > 1) { //if the quarters selected are several //this is the starting quarter temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(semi_annual) == 1) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + startMonth + "' and "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; } } else if (p == semiannualarray.length - 1 && semiannualarray.length > 1) { //the last month temporaryheader += currentperiodlabel; //by now we expct something like from Semi-annuals oct-Mar to Apr -Sep //this assumes that the last month can never be yearmonthend = "'" + year + "" + endMonth + "'"; } else if (p == 0 && semiannualarray.length == 1) { // the number of quarters selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(semi_annual) == 1) { yearmonthstart = " " + form + ".yearmonth between '" + prevYear + startMonth + "' and"; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; } yearmonthend = "'" + year + "" + endMonth + "'"; //this assumes no end month date can come in oct-dec ..all the year month ends come on mar or sep } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { System.out.println(ex); } if (!semi_annual.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } // ____________________________________________________________________________________________________Quarterly____________________ else if (reportDuration.equals("3")) { period = "Quarter"; periodicgroupby = ", period "; try { String startMonth = "", endMonth = ""; String quarterarray[] = request.getParameterValues("quarter"); String temporaryheader = " QUARTER"; for (int p = 0; p < quarterarray.length; p++) { quarter = quarterarray[p]; String getMonth = "SELECT months,name as qtrname FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { String monthsinqtr[] = conn.rs.getString(1).split(","); //_________________add year at the end of period label if (quarter.equals("1")) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("qtrname").replace("-", " " + prevYear + "-") + " " + year; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("qtrname") + " " + year; } //____________________ if (p == 0) { startMonth = monthsinqtr[0]; monthrange = conn.rs.getString("qtrname"); endMonth = monthsinqtr[2]; } if (p == quarterarray.length - 1 && quarterarray.length > 1) { //last row monthrange += " to " + conn.rs.getString("qtrname"); endMonth = monthsinqtr[2]; } if (periodlabel.equals(""))// note period label gets all the periods in my loop { periodlabel = conn.rs.getString("qtrname"); } else { periodlabel += "_" + conn.rs.getString("qtrname"); } } //end of if //get the yearmonthstart date if (p == 0 && quarterarray.length > 1) { //if the quarters selected are several //this is the starting quarter temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(quarter) == 1) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + startMonth + "' and"; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and"; } } else if (p == quarterarray.length - 1 && quarterarray.length > 1) { //the last month temporaryheader += currentperiodlabel; //by now we expct something like from Quarters oct-Dec to Jan -Mar yearmonthend = "'" + year + "" + endMonth + "'"; } else if (p == 0 && quarterarray.length == 1) { // the number of quarters selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(quarter) == 1) { yearmonthstart = " " + form + ".yearmonth between '" + prevYear + startMonth + "' and "; yearmonthend = " '" + prevYear + "" + endMonth + "' "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; yearmonthend = " '" + year + "" + endMonth + "' "; } } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { System.out.println(ex); } if (!quarter.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } //_______________________________________________________________________________________________________________monthly______________________________ else if (reportDuration.equals("4")) { period = "Month"; periodicgroupby = ", period "; try { months = request.getParameterValues("month"); String temporaryheader = " MONTH"; for (int u = 0; u < months.length; u++) { month = months[u]; String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { //_________________add year at the end of period label if (new Integer(month) >= 10 && new Integer(month) <= 12) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("name").substring(0, 3) + " " + prevYear; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("name").substring(0, 3) + " " + year; } //____________________ if (u == 0) { monthrange = conn.rs.getString(1); } if (u == months.length - 1 && months.length > 1) { monthrange += " to " + conn.rs.getString(1); } if (periodlabel.equals("")) { periodlabel = conn.rs.getString("name"); } else { periodlabel += "_" + conn.rs.getString("name"); } } //get the yearmonthstart date if (u == 0 && months.length > 1) { //if the month selected are several //this is the starting month temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(month) >= 10) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + month + "' and "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "0" + month + "' and "; } } else if (u == months.length - 1 && months.length > 1) { //the last month if (new Integer(month) >= 10) { yearmonthend = "'" + prevYear + month + "'"; } else { yearmonthend = "'" + year + "0" + month + "'"; } } else if (u == 0 && months.length == 1) { // the number of months selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(month) >= 10) { yearmonthstart = " " + form + ".yearmonth = " + prevYear + month; } else { yearmonthstart = " " + form + ".yearmonth = " + year + "0" + month; } yearmonthend = ""; } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } if (!month.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } //end of monthly records else { duration = ""; } //if someone doest want periodic grouping, then dont add a group by in the report.. get numbers for all rows. if (isgroupby.equals("No")) { periodicgroupby = ""; } //______________________________________________________________________________________COUNTY , SUBCOUNTY AND String subcountywhere = ""; String subcounty = ""; if (reportType.equalsIgnoreCase("2")) { //get data per county if the selected if (!request.getParameter("subcounty").equals("")) { subcounty = request.getParameter("subcounty"); subcounty_countywhere = " (district.DistrictID='" + subcounty + "') and "; } if (!request.getParameter("county").equals("")) { county = request.getParameter("county"); subcounty_countywhere = " (district.countyid='" + request.getParameter("county") + "') and ";//20160711 } if (!county.equals("")) { countywhere = " and district.countyid = '" + county + "'"; } if (!subcounty.equals("")) { subcountywhere = " and subpartnera.DistrictID = '" + subcounty + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "'"; } } String joinedwhwere = " where 1=1 " + duration + " " + countywhere + " " + subcountywhere + " " + facilitywhere; // System.out.println(""+joinedwhwere); //we need a case statement in our main query. This will allow for friendly display of String myperiodcase = ""; if (isgroupby.equals("No")) { myperiodcase = "'" + monthrange + "' as period"; } else { if (period.equalsIgnoreCase("Year")) { myperiodcase = " case when Annee !='' then Annee else 'no year' end as period "; } else if (period.equalsIgnoreCase("Semi-Annual")) { myperiodcase = " case when (SUBSTRING(Yearmonth,5,6) >=01 and SUBSTRING(Yearmonth,5,6) <=03) || (SUBSTRING(Yearmonth,5,6) >=10 and SUBSTRING(Yearmonth,5,6) <=12) then 'Oct-Mar' " + "when SUBSTRING(Yearmonth,5,6) >=04 and SUBSTRING(Yearmonth,5,6) <=09 then 'Apr-Sep' else 'No period' end as period "; } else if (period.equalsIgnoreCase("Quarter")) { myperiodcase = " case when (SUBSTRING(Yearmonth,5,6) >=01 and SUBSTRING(Yearmonth,5,6) <=03) then 'Jan-Mar'" + "when (SUBSTRING(Yearmonth,5,6) >=10 and SUBSTRING(Yearmonth,5,6) <=12) then 'Oct-Dec' " + "when SUBSTRING(Yearmonth,5,6) >=04 and SUBSTRING(Yearmonth,5,6) <=06 then 'Apr-Jun' " + "when SUBSTRING(Yearmonth,5,6) >=07 and SUBSTRING(Yearmonth,5,6) <=09 then 'Jul-Sep' " + "else 'No period' end as period "; } else if (period.equalsIgnoreCase("Month")) { myperiodcase = " case " + " when SUBSTRING(Yearmonth,5,6)= '01' then 'Jan' " + " when SUBSTRING(Yearmonth,5,6)= '02' then 'Feb' " + " when SUBSTRING(Yearmonth,5,6)= '03' then 'Mar' " + " when SUBSTRING(Yearmonth,5,6)= '04' then 'Apr' " + " when SUBSTRING(Yearmonth,5,6)= '05' then 'May' " + " when SUBSTRING(Yearmonth,5,6)= '06' then 'Jun' " + " when SUBSTRING(Yearmonth,5,6)= '07' then 'Jul' " + " when SUBSTRING(Yearmonth,5,6)= '08' then 'Aug' " + " when SUBSTRING(Yearmonth,5,6)= '09' then 'Sep' " + " when SUBSTRING(Yearmonth,5,6)= '10' then 'Oct' " + " when SUBSTRING(Yearmonth,5,6)= '11' then 'Nov' " + " when SUBSTRING(Yearmonth,5,6)= '12' then 'Dec' " + " else 'No period' end as period "; } } //_________________________________________________________________END OF FILTERING SECTION_______________________________________________________________ //________________________________________________________________________________________________________________________________________________________ //an array to store haeder information. //the header information should appear only if certain parameters are met //The parameters listed in here can be removed if the report type doesnt require some parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add(period); 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 and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); //create an array to store the number of row for each excel worksheet. //This will help in retrieving the number of rows for each month since we are wring data for different months with increasing rows. //the size of that array will be determined by the number of excel worksheets String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' " + indicatorswhere + " group by section 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 servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } int rowstartpersheet[] = new int[distinctsheets.size()]; //initialize the row start position for each workshett with 2 for (int x = 0; x < rowstartpersheet.length; x++) { rowstartpersheet[x] = 2; } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' " + indicatorswhere + " 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.equalsIgnoreCase("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 pivot table active //labels.add("ART High Volume"); //labels.add("HTC High Volume"); //labels.add("PMTCT High Volume"); //System.out.println(perfacilselect); //______________________________________________________________________________________ // 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); XSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); int cellrange[] = new int[4]; for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(5, 2); int firstcell = 1; //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg MONTH | 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("ART High Volume"); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell1 = rw.createCell(headercellpos); cell1.setCellValue("HTC High Volume"); cell1.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2 = rw.createCell(headercellpos); cell2.setCellValue("PMTCT High Volume"); cell2.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2a = rw.createCell(headercellpos); cell2a.setCellValue("GSN"); cell2a.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell3 = rw.createCell(headercellpos); cell3.setCellValue("Form Validated ?"); cell3.setCellStyle(stylex); headercellpos++; } //______________________________________________________________________________________ //______________________________________________________________________________________ //-------------------------------------------------------------------------------------------- // MONTH LOOPS //-------------------------------------------------------------------------------------------- int cumulativestatingpoint = 3; boolean cumstartpointnoted = false; int colposcopy = 0; //for (int w = 0; w < months.length; w++) { String perfacilselect = "select " + myperiodcase + " , CONCAT(UPPER(SUBSTRING(County,1,1)),LOWER(SUBSTRING(County,2))) as County , DistrictNom as District , SubPartnerNom as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //have a stringto get all distinct periods before execution of the main query. This will help in knowing the last String getdistinctperiod = "select " + myperiodcase; //-------------------------------------------------------------------------------------------- // 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 += " SUBSTRING_INDEX(GROUP_CONCAT(CAST(IFNULL(" + dbcolumns.get(a) + ",0) AS CHAR) ORDER BY yearmonth DESC),',',1) as " + dbcolumns.get(a); //SUBSTRING_INDEX(GROUP_CONCAT(CAST(IFNULL(HV0303,0) AS CHAR) ORDER BY yearmonth DESC),',',1) } 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 += " ,"; // } } //---------------------------------add highvolume------------------------------------------------ perfacilselect += " IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume, IFNULL(GSN,0) as GSN,"; //------------------------------------------------------------------------------------------------- // 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 "; getdistinctperiod += " 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 += joinedwhwere; //contains any filterings getdistinctperiod += joinedwhwere; //contains any filterings //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID " + periodicgroupby + " order by yearmonth "; getdistinctperiod += " group by period order by yearmonth "; String lastperiod = ""; ArrayList alldistinctperiods = new ArrayList(); //System.out.println(""+getdistinctperiod); conn.rs = conn.st.executeQuery(getdistinctperiod); while (conn.rs.next() == true) { lastperiod = conn.rs.getString(1); //here am asumming the last period will appear last and so will be mantained in the alldistinctperiods.add(lastperiod); } String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section (eg HTC, PMTCT) changes, change the current workshhet index too //also, reset the row position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { rowpos = rowstartpersheet[g]; shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); //System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { if (lastperiod.equalsIgnoreCase(conn.rs.getString("period")) && cumstartpointnoted == false) { //save the current row position. //get it from the current rowstartpersheet[g] cumulativestatingpoint = rowstartpersheet[0]; cumstartpointnoted = true; //this ensures that we are fetching dataonce. // System.out.println("LAST STARTING POINT__"+cumulativestatingpoint); } //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, output integers if (e > 3) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { 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.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop //ART_highvolume XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt("ART_highvolume")); cell0.setCellStyle(stborder); colpos++; //HTC_highvolume XSSFCell cell1 = rw.createCell(colpos); cell1.setCellValue(conn.rs.getInt("HTC_highvolume")); cell1.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2 = rw.createCell(colpos); cell2.setCellValue(conn.rs.getInt("PMTCT_highvolume")); cell2.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2a = rw.createCell(colpos); cell2a.setCellValue(conn.rs.getInt("GSN")); cell2a.setCellStyle(stborder); colpos++; String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell3 = rw.createCell(colpos); cell3.setCellValue(isvalidated); cell3.setCellStyle(stborder); colpos++; rowpos++; if (colpos > 1) { colposcopy = colpos - 1; } colpos = 0; } // end of while loop getting data from the db //____________________________________________________________________________________ //COLUMN AUTOSIZE COLUMN WIDTH , AUTOFILTER & AUTOSUM //____________________________________________________________________________________ //At this point we are sure this is the last row and we have exhausted fetching data for all periods //System.out.println(" Column position Before "+colposcopy); // if (lastperiod.equalsIgnoreCase(conn.rs.getString("period"))) { //System.out.println(" Column after "+colposcopy); shet.setAutoFilter(new CellRangeAddress(1, rowpos - 1, 0, colposcopy)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int e = 0; e < Headerorgunits.size(); e++) { shet.autoSizeColumn(e); } //autosum XSSFRow initialrow = shet.getRow(2); XSSFRow prevrow = shet.getRow(rowpos - 1); XSSFRow cumrow = shet.getRow(cumulativestatingpoint);//not used for now but let it stay here may be of use in future XSSFRow rwsum = shet.createRow(rowpos); int colpossum = 0; int firstcols = 5; int periodcolumn = 0; for (int f = 0; f < firstcols; f++) { if (f == 0) { XSSFCell cellsum = rwsum.createCell(0); cellsum.setCellValue("Total"); cellsum.setCellStyle(stylesum); } else if (f > 0 && f < firstcols) { XSSFCell cellsum = rwsum.createCell(f); cellsum.setCellValue(" "); cellsum.setCellStyle(stylesum); } } for (int c = 0; c < dbcolumns.size(); c++) { if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cellsum = rwsum.createCell(colpossum + firstcols); XSSFCell initialcell = initialrow.getCell(colpossum + firstcols); String cellformula = ""; XSSFCell prevcell = prevrow.getCell(colpossum + firstcols);//the last cell of the current column. rem whe are looping through all the columns //periodcolumncell XSSFCell initialperiodcell = initialrow.getCell(periodcolumn);// the first cell of the period(month, year) column XSSFCell currentperiodcell = prevrow.getCell(periodcolumn);//the last cell of the period column cellsum.setCellType(cellsum.CELL_TYPE_FORMULA); String startcellreference = initialcell.getReference(); String lastavailableperiod = currentperiodcell.getStringCellValue(); if (iscumulative.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1),ROW(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ")-ROW(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1)),0))=1),--(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + "=\"" + lastavailableperiod + "\")," + startcellreference + ":" + prevcell.getReference() + ")"; } else if (ispercent.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "ROUNDUP(SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + "),1)"; // round of maximum values } else { cellformula = "SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + ")"; } //for cumulative indicators, we need to do column total for the last selected month //we therefore need to always track where the previous month started at. cellsum.setCellFormula(cellformula); cellsum.setCellStyle(stylesum); colpossum++; } } //merge last cell shet.addMergedRegion(new CellRangeAddress(rowpos, rowpos, 0, 4)); // } end of checking if this is the last month //disbled for now // rowstartpersheet[g] = rowpos; } // end of distinct sheets report // }//end of monthly loop IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_RPT_FOR_" + year.trim() + "(" + periodlabel + ")_GEN_" + 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() + "(" + periodlabel.replace(" ", "") + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.allStaticReportsMonthly.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String monthrange = ""; String periodlabel = ""; String period = "Month"; //The default one try {/* ww w .j a va2 s . c o m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String months[] = null; String year = "2016"; String month = "7"; 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) { months = request.getParameterValues("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711A")) { form = "MOH711"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } 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 + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if certain parameters are met //The parameters listed in here can be removed if the report type doesnt require some parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add(period); 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 and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); //create an array to store the number of row for each excel worksheet. //This will help in retrieving the number of rows for each month since we are wring data for different months with increasing rows. //the size of that array will be determined by the number of excel worksheets String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' group by section 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 servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } int rowstartpersheet[] = new int[distinctsheets.size()]; //initialize the row start with 2 for (int x = 0; x < rowstartpersheet.length; x++) { rowstartpersheet[x] = 2; } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' 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.equalsIgnoreCase("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 pivot table active //labels.add("ART High Volume"); //labels.add("HTC High Volume"); //labels.add("PMTCT High Volume"); //System.out.println(perfacilselect); //______________________________________________________________________________________ // 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); XSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); int cellrange[] = new int[4]; for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(5, 2); int firstcell = 1; //shet.setAutoFilter(CellRangeAddress.valueOf("A2:N1")); //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("ART High Volume"); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell1 = rw.createCell(headercellpos); cell1.setCellValue("HTC High Volume"); cell1.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2 = rw.createCell(headercellpos); cell2.setCellValue("PMTCT High Volume"); cell2.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell3 = rw.createCell(headercellpos); cell3.setCellValue("Form Validated ?"); cell3.setCellStyle(stylex); headercellpos++; } //______________________________________________________________________________________ //______________________________________________________________________________________ //-------------------------------------------------------------------------------------------- // MONTH LOOPS //-------------------------------------------------------------------------------------------- int cumulativestatingpoint = 3; boolean cumstartpointnoted = false; int colposcopy = 0; for (int w = 0; w < months.length; w++) { month = months[w]; if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { monthrange = conn.rs.getString(1); if (periodlabel.equals("")) { periodlabel = monthrange.substring(0, 3); } else { periodlabel += "_" + monthrange.substring(0, 3); } } String perfacilselect = "select '" + monthrange + "', CONCAT(UPPER(SUBSTRING(County,1,1)),LOWER(SUBSTRING(County,2))) as County , DistrictNom as District , 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 += " ,"; // } } //---------------------------------add highvolume------------------------------------------------ perfacilselect += " IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume,"; //------------------------------------------------------------------------------------------------- // 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"; String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------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)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { if (w == months.length - 1 && cumstartpointnoted == false) { //save the current row position. //get it from the current rowstartpersheet[g] cumulativestatingpoint = rowstartpersheet[0]; cumstartpointnoted = true; //this ensures that we are fetching dataonce. // System.out.println("LAST STARTING POINT__"+cumulativestatingpoint); } rowpos = rowstartpersheet[g]; shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, output integers if (e > 3) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { 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.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop //ART_highvolume XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt("ART_highvolume")); cell0.setCellStyle(stborder); colpos++; //HTC_highvolume XSSFCell cell1 = rw.createCell(colpos); cell1.setCellValue(conn.rs.getInt("HTC_highvolume")); cell1.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2 = rw.createCell(colpos); cell2.setCellValue(conn.rs.getInt("PMTCT_highvolume")); cell2.setCellStyle(stborder); colpos++; String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell3 = rw.createCell(colpos); cell3.setCellValue(isvalidated); cell3.setCellStyle(stborder); colpos++; rowpos++; if (colpos > 1) { colposcopy = colpos - 1; } colpos = 0; } // end of while loop getting data from the db //____________________________________________________________________________________ //COLUMN AUTOSIZE COLUMN WIDTH , AUTOFILTER & AUTOSUM //____________________________________________________________________________________ //At this point we are sure this is the last row and we have exhausted fetching data for all months //System.out.println(" Column position Before "+colposcopy); if (w == months.length - 1) { //System.out.println(" Column after "+colposcopy); shet.setAutoFilter(new CellRangeAddress(1, rowpos - 1, 0, colposcopy)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int e = 0; e < Headerorgunits.size(); e++) { shet.autoSizeColumn(e); } //autosum XSSFRow rwsum = shet.createRow(rowpos); XSSFRow initialrow = shet.getRow(2); XSSFRow prevrow = shet.getRow(rowpos - 1); XSSFRow cumrow = shet.getRow(cumulativestatingpoint);//not used for now but let it stay here may be of use in future int colpossum = 0; int firstcols = 5; int periodcolumn = 0; for (int f = 0; f < firstcols; f++) { if (f == 0) { XSSFCell cellsum = rwsum.createCell(0); cellsum.setCellValue("Total"); cellsum.setCellStyle(stylesum); } else if (f > 0 && f < firstcols) { XSSFCell cellsum = rwsum.createCell(f); cellsum.setCellValue(" "); cellsum.setCellStyle(stylesum); } } for (int c = 0; c < dbcolumns.size(); c++) { if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cellsum = rwsum.createCell(colpossum + firstcols); XSSFCell initialcell = initialrow.getCell(colpossum + firstcols); String cellformula = ""; XSSFCell prevcell = prevrow.getCell(colpossum + firstcols);//the last cell of the current column. rem whe are looping through all the columns //periodcolumncell XSSFCell initialperiodcell = initialrow.getCell(periodcolumn);// the first cell of the period(month, year) column XSSFCell currentperiodcell = prevrow.getCell(periodcolumn);//the last cell of the period column cellsum.setCellType(cellsum.CELL_TYPE_FORMULA); String startcellreference = initialcell.getReference(); String lastavailableperiod = currentperiodcell.getStringCellValue(); if (iscumulative.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1),ROW(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ")-ROW(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1)),0))=1),--(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + "=\"" + lastavailableperiod + "\")," + startcellreference + ":" + prevcell.getReference() + ")"; } else if (ispercent.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "ROUNDUP(SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + "),1)"; // round of maximum values } else { cellformula = "SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + ")"; } //for cumulative indicators, we need to do column total for the last selected month //we therefore need to always track where the previous month started at. cellsum.setCellFormula(cellformula); cellsum.setCellStyle(stylesum); colpossum++; } } //merge last cell shet.addMergedRegion(new CellRangeAddress(rowpos, rowpos, 0, 4)); } // rowstartpersheet[g] = rowpos; } // end of distinct sheets report } //end of monthly loop IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_RPT_FOR_" + year.trim() + "(" + month.trim() + ")_GEN_" + 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() + "(" + periodlabel + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:se.inera.intyg.rehabstod.service.export.xlsx.XlsxExportServiceImpl.java
License:Open Source License
/** * Creates a merged span to make all filter sections the same size and style. * * @param fromColumn// w ww . j a va 2s .co m * @param style * @param sheet * @param row */ private void createMergedCellFromColumn(int fromColumn, XSSFCellStyle style, XSSFSheet sheet, XSSFRow row) { // Create and style cells for the span for (int i = fromColumn + 1; i <= FILTER_END_COLUMN_SPAN; i++) { XSSFCell cell = row.createCell(i); cell.setCellStyle(style); } sheet.addMergedRegion( new CellRangeAddress(row.getRowNum(), row.getRowNum(), fromColumn, FILTER_END_COLUMN_SPAN)); }