List of usage examples for org.apache.poi.xssf.usermodel XSSFFont setFontName
public void setFontName(String name)
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static int generateHeader(XSSFSheet worksheet, XSSFWorkbook workbook, int rowNum) { // Now add/*from www . j ava2 s . c om*/ XSSFRow row = worksheet.createRow(rowNum); XSSFCell cell; XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.index); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyle.setBorderBottom(BorderStyle.MEDIUM); headerStyle.setBorderLeft(BorderStyle.MEDIUM); headerStyle.setBorderRight(BorderStyle.MEDIUM); headerStyle.setBorderTop(BorderStyle.MEDIUM); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 9); txtFont.setBold(true); headerStyle.setFont(txtFont); cell = row.createCell(0); cell.setCellValue("Photo"); cell.setCellStyle(headerStyle); worksheet.setColumnWidth(0, ConvertImageUnits.pixel2WidthUnits(COLUMN_WIDTH_PX));//4387 cell = row.createCell(1); cell.setCellValue("Rfrence"); cell.setCellStyle(headerStyle); cell = row.createCell(2); cell.setCellValue("Libell"); cell.setCellStyle(headerStyle); cell = row.createCell(3); cell.setCellValue("Stock\n Appock"); cell.setCellStyle(headerStyle); cell.getCellStyle().setWrapText(true); cell = row.createCell(4); cell.setCellValue("Stock\n rel"); cell.setCellStyle(headerStyle); cell.getCellStyle().setWrapText(true); row.setHeight((short) 600); return rowNum + 1; }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static void createRow(XSSFSheet worksheet, XSSFWorkbook workbook, ArticleStock article, CatalogueService catalogueService, int rowNumber) throws IOException { int col = 0;/*from w w w . java2 s .c o m*/ XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFCellStyle cellImageStyle = workbook.createCellStyle(); cellImageStyle.setBorderBottom(BorderStyle.THIN); cellImageStyle.setBorderLeft(BorderStyle.THIN); cellImageStyle.setBorderRight(BorderStyle.THIN); cellImageStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellImageStyle.setAlignment(HorizontalAlignment.CENTER); if (rowNumber != 1) { cellStyle.setBorderTop(BorderStyle.THIN); } XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 9); txtFont.setBold(false); cellStyle.setFont(txtFont); XSSFRow row = worksheet.createRow(rowNumber); row.setHeight((short) ROW_HEIGHT_TWIPS);//80px 1600 // Photo File image = null; try { image = catalogueService.getFilePieceJointe(article.getArticleCatalogue().getPhotoArticleCatalogue()); } catch (IllegalArgumentException e) { log.warn("No image to display for article " + article.getArticleCatalogue().getLibelle()); } XSSFCell cell = row.createCell(col); cell.setCellStyle(cellImageStyle); if (image != null) addImage(workbook, worksheet, image, rowNumber); col = col + 1; // Rfrence cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getReferenceArticleStock()); // Libell cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getArticleCatalogue().getLibelle()); // Appock Stock cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getQuantiteStock()); cell.setCellType(CellType.NUMERIC); // Stock reel cell = row.createCell(col); cell.setCellStyle(cellStyle); cell.setCellType(CellType.NUMERIC); }
From source file:org.agmip.ui.afsirs.util.SummaryReportExcelFormat.java
private XSSFCellStyle getCellStyle(int type) { XSSFFont font = workbook.createFont(); XSSFCellStyle style = null;/*from w w w . j av a 2 s . c om*/ switch (type) { case 1: font.setFontHeightInPoints((short) 15); font.setFontName("IMPACT"); font.setItalic(true); font.setColor(HSSFColor.BLUE.index); style = workbook.createCellStyle(); style.setWrapText(true); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(font); break; case 2: font.setFontHeightInPoints((short) 15); font.setFontName("IMPACT"); font.setItalic(true); font.setColor(HSSFColor.BLUE.index); style = workbook.createCellStyle(); style.setWrapText(true); style.setShrinkToFit(true); style.setFont(font); break; case 3: font.setFontHeightInPoints((short) 15); font.setFontName("IMPACT"); font.setItalic(true); font.setColor(HSSFColor.BLUE.index); style = workbook.createCellStyle(); style.setWrapText(true); style.setShrinkToFit(true); style.setFont(font); break; case 4: font.setFontHeightInPoints((short) 15); font.setFontName("IMPACT"); font.setItalic(true); font.setColor(HSSFColor.BLUE.index); style = workbook.createCellStyle(); style.setWrapText(true); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(font); break; case 5: font.setFontHeightInPoints((short) 15); font.setFontName("IMPACT"); font.setItalic(true); font.setColor(HSSFColor.BLUE.index); style = workbook.createCellStyle(); style.setWrapText(true); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(font); break; } return style; }
From source file:org.apache.fineract.accounting.closure.storeglaccountbalance.service.GLClosureJournalEntryBalanceReadPlatformServiceImpl.java
License:Apache License
/** * Create the excel file with the balance report data * //from ww w . j a v a 2s. c o m * @param reportDataList * @return {@link File} object */ @SuppressWarnings("unused") private File createGLClosureAccountBalanceReportExcelFile( final Collection<GLClosureAccountBalanceReportData> reportDataList) { File file = null; try { if (reportDataList != null) { final String[] columnTitles = new String[13]; columnTitles[0] = "AccountCostCentre"; columnTitles[1] = "AccountDepartment"; columnTitles[2] = "AccountNumber"; columnTitles[3] = "TransactionType"; columnTitles[4] = "TransactionDate"; columnTitles[5] = "GoodsAmount"; columnTitles[6] = "Reference"; columnTitles[7] = "Narrative"; columnTitles[8] = "UniqueReferenceNumber"; columnTitles[9] = "UserNumber"; columnTitles[10] = "Source"; columnTitles[11] = "PostedDate"; columnTitles[12] = "TransactionAnalysisCode"; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(" nominaltransactions "); XSSFRow row; XSSFFont font; XSSFCellStyle style; XSSFDataFormat dataFormat; int rowId = 0; int cellId = 0; row = spreadsheet.createRow(rowId++); for (String columnTitle : columnTitles) { font = workbook.createFont(); style = workbook.createCellStyle(); font.setBold(true); font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); style.setFont(font); Cell cell = row.createCell(cellId++); cell.setCellValue(columnTitle); cell.setCellStyle(style); } for (GLClosureAccountBalanceReportData reportData : reportDataList) { row = spreadsheet.createRow(rowId++); font = workbook.createFont(); dataFormat = workbook.createDataFormat(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); font.setBold(false); // ==================================================== Cell cell = row.createCell(2); style = workbook.createCellStyle(); cell.setCellType(Cell.CELL_TYPE_STRING); style.setDataFormat(dataFormat.getFormat("@")); style.setFont(font); cell.setCellValue(reportData.getAccountNumber()); cell.setCellStyle(style); // ==================================================== // ==================================================== if (reportData.getTransactionType() != null) { cell = row.createCell(3); style = workbook.createCellStyle(); style.setFont(font); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(reportData.getTransactionType().getValue()); cell.setCellStyle(style); } // ==================================================== // ==================================================== if (reportData.getTransactionDate() != null) { cell = row.createCell(4); style = workbook.createCellStyle(); Date transactionDate = reportData.getTransactionDate().toDate(); style.setDataFormat(dataFormat.getFormat("MM/DD/YY")); style.setFont(font); cell.setCellValue(transactionDate); cell.setCellStyle(style); } // ==================================================== // ==================================================== if (reportData.getAmount() != null) { cell = row.createCell(5); style = workbook.createCellStyle(); Double amount = reportData.getAmount().doubleValue(); style.setDataFormat(dataFormat.getFormat("0.00")); style.setFont(font); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(amount); cell.setCellStyle(style); } // ==================================================== // ==================================================== if (reportData.getReference() != null) { cell = row.createCell(6); style = workbook.createCellStyle(); cell.setCellType(Cell.CELL_TYPE_STRING); style.setDataFormat(dataFormat.getFormat("@")); style.setFont(font); cell.setCellValue(reportData.getReference()); cell.setCellStyle(style); } // ==================================================== // ==================================================== if (reportData.getPostedDate() != null) { cell = row.createCell(11); style = workbook.createCellStyle(); Date postedDate = reportData.getPostedDate().toDate(); style.setDataFormat(dataFormat.getFormat("MM/DD/YY")); style.setFont(font); cell.setCellValue(postedDate); cell.setCellStyle(style); } // ==================================================== } final String fileDirectory = FileSystemContentRepository.MIFOSX_BASE_DIR + File.separator + ""; if (!new File(fileDirectory).isDirectory()) { new File(fileDirectory).mkdirs(); } file = new File(fileDirectory + "gl_closure_account_balance_report.xls"); FileOutputStream fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); fileOutputStream.close(); } } catch (Exception exception) { logger.error(exception.getMessage(), exception); } return file; }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();//w w w .ja v a 2 s. c o 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.cgiar.ccafs.ap.summaries.projects.xlsx.BaseXLS.java
License:Open Source License
/** * Method used to initialize the different styles according to the type of value *//*from w ww.j a v a 2s. co m*/ private void initializeStyles(int[] columnTypes) { // Style header styleHeader = (XSSFCellStyle) workbook.createCellStyle(); styleHeader.setAlignment(CellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleHeader.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); styleHeader.setFillForegroundColor(new XSSFColor(Color.decode(HEADER_BG_COLOR_HEX))); styleHeader.setWrapText(true); // Font XSSFFont font = (XSSFFont) workbook.createFont(); font.setBold(true); font.setFontName(HEADER_FONT_NAME); font.setColor(new XSSFColor(Color.decode(HEADER_FONT_COLOR_HEX))); font.setFontHeightInPoints(HEADER_FONT_SIZE); styleHeader.setFont(font); richTextFont = workbook.createFont(); richTextFont.setFontName("Tahoma"); richTextFont.setBold(true); richTextFont.setColor(HSSFColor.RED.index); // border this.setBottomBorderCell(styleHeader, Color.decode(HEADER_BORDER_COLOR_HEX)); CreationHelper createHelper = workbook.getCreationHelper(); columnStyles = new XSSFCellStyle[columnTypes.length]; for (int c = 0; c < columnTypes.length; c++) { columnStyles[c] = (XSSFCellStyle) workbook.createCellStyle(); switch (columnTypes[c]) { // Style numeric case COLUMN_TYPE_NUMERIC: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; // Style date case COLUMN_TYPE_DATE: columnStyles[c].setDataFormat(createHelper.createDataFormat().getFormat(CELL_DATE_FORMAT)); columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; // styleBoleean case COLUMN_TYPE_BOOLEAN: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); columnStyles[c].setDataFormat(workbook.createDataFormat().getFormat("#.##")); break; // styleBudget case COLUMN_TYPE_BUDGET: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); columnStyles[c].setDataFormat(workbook.createDataFormat().getFormat("$#,##0.00")); // "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)" break; // Style decimal case COLUMN_TYPE_DECIMAL: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); columnStyles[c].setDataFormat(workbook.createDataFormat().getFormat("#.##")); break; // Style long string case COLUMN_TYPE_TEXT_LONG: columnStyles[c].setAlignment(HorizontalAlignment.LEFT); columnStyles[c].setWrapText(true); break; // Style short string case COLUMN_TYPE_TEXT_SHORT: columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; // Style hyperlink case COLUMN_TYPE_HYPERLINK: XSSFFont hlinkfont = (XSSFFont) workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); columnStyles[c].setFont(hlinkfont); columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; // Style hyperlink case COLUMN_TYPE_DATE_TIME: columnStyles[c].setDataFormat(createHelper.createDataFormat().getFormat(CELL_DATE_TIME_FORMAT)); columnStyles[c].setAlignment(CellStyle.ALIGN_CENTER); break; } this.setBottomBorderCell(columnStyles[c], Color.decode(CELL_BORDER_COLOR_HEX)); if (c == 0) { columnStyles[c].setBorderLeft(CELL_BORDER_TYPE_LEFT); columnStyles[c].setBorderColor(BorderSide.LEFT, new XSSFColor(Color.decode(CELL_BORDER_COLOR_HEX))); } else if (c == columnTypes.length - 1) { columnStyles[c].setBorderRight(CELL_BORDER_TYPE_RIGHT); columnStyles[c].setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.decode(CELL_BORDER_COLOR_HEX))); } } }
From source file:org.javasoft.ciclope.servlets.ExportMaterialePratica.java
private static XSSFCellStyle createTitleFont(XSSFWorkbook workbook) { //Create a new font and alter it. XSSFFont font = workbook.createFont(); font.setFontHeight(15);/*from www .j a v a 2 s . com*/ font.setFontName("MYTITLE"); font.setBold(true); font.setColor(HSSFColor.BLACK.index); //Set font into style XSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); return style; }
From source file:preprocessing.EnemyInfoGenerator.java
private static void createCellStyles(XSSFWorkbook book) { XSSFDataFormat format = book.createDataFormat(); XSSFFont font = book.createFont(); font.setFontHeightInPoints((short) 9); font.setFontName(""); XSSFFont headerFont = book.createFont(); headerFont.setFontHeightInPoints((short) 9); headerFont.setBold(true);/*w ww.j a va2 s .c om*/ headerFont.setColor(new XSSFColor(new Color(0, 176, 80))); headerFont.setFontName(""); headerStyle = book.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // headerStyle.setBorderRight(CellStyle.BORDER_THIN); // headerStyle.setBorderLeft(CellStyle.BORDER_THIN); // headerStyle.setBorderTop(CellStyle.BORDER_THIN); // headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setFont(headerFont); headerStyle.setWrapText(true); contentStyle = book.createCellStyle(); contentStyle.setAlignment(HorizontalAlignment.CENTER); contentStyle.setVerticalAlignment(VerticalAlignment.CENTER); // contentStyle.setBorderRight(CellStyle.BORDER_THIN); // contentStyle.setBorderLeft(CellStyle.BORDER_THIN); // contentStyle.setBorderTop(CellStyle.BORDER_THIN); // contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setFont(font); moneyStyle = book.createCellStyle(); moneyStyle.setAlignment(HorizontalAlignment.CENTER); moneyStyle.setVerticalAlignment(VerticalAlignment.CENTER); moneyStyle.setBorderRight(CellStyle.BORDER_THIN); moneyStyle.setBorderLeft(CellStyle.BORDER_THIN); moneyStyle.setBorderTop(CellStyle.BORDER_THIN); moneyStyle.setBorderBottom(CellStyle.BORDER_THIN); moneyStyle.setDataFormat(format.getFormat("$#,##0.00_);[Red]($#,##0.00)")); moneyStyle.setFont(font); }
From source file:reports.achievedReport.java
public String getAchievedReport(int passedYear, String passedPath, String dates) throws InvalidFormatException, IOException, SQLException { pepfaryear = passedYear;/* w w w . j a v a 2 s .co m*/ full_date = dates; dbConn conn = new dbConn(); pos = 0; incrementor = 0; prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); String allpath = passedPath; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("PARTNER NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getClients = "SELECT partner.partner_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,personal_information.completionyear," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",completionmonth,COUNT(personal_information.client_id) FROM personal_information " + "JOIN partner ON personal_information.partner_id=partner.partner_id " + " WHERE personal_information.completionmonth>0 && personal_information.completionyear>0 GROUP BY partner.partner_name,SEX,personal_information.completionyear,MONTHS,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; partnername = conn.rs.getString(1); month = conn.rs.getString(2); year = conn.rs.getInt(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = year + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); achieved = conn.rs.getInt(7); incrementor += achieved; System.out.println( "date key : " + datekey + "startdate : " + start + " end date : " + end + " year : " + year); if (datekey >= start && datekey <= end && year >= 2014) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(partnername); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } } for (int i = 0; i < myalphabet.length; i++) { try { System.out.println("at position : " + myalphabet[i]); String current_drive = myalphabet[i]; File f3 = new File(current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION"); // CREATE A DIRECTORY AND THE FILE TO HOLD DATA if (f3.exists() && f3.isDirectory()) { path = current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION\\DATA\\REPORTS"; new File(path).mkdirs(); filePath = path + "\\PWP_ATTACHED_REPORT" + full_date + ".xlsm"; } //select the last timestamp which a backup was picked from..... } finally { } } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } //if(incrementor==0){ // filePath="noreport"; //} //else{ //// url="no url to the report"; //} return filePath; }
From source file:reports.allStaticReports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/* w w w .j a v a2s. co m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = ""; String county = ""; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1' order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " , isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; } rowpos++; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReports.class.getName()).log(Level.SEVERE, null, ex); } }