List of usage examples for org.apache.poi.ss.usermodel HorizontalAlignment RIGHT
HorizontalAlignment RIGHT
To view the source code for org.apache.poi.ss.usermodel HorizontalAlignment RIGHT.
Click Source Link
From source file:DSC.PackerReport.java
private static void createSpreadsheets() { orderList.sort(new Comparator<Order>() { @Override//w w w.j av a 2s . c om public int compare(Order o1, Order o2) { int result; if (o1.getFamilySize() < o2.getFamilySize()) { result = -1; } else if (o1.getFamilySize() == o2.getFamilySize()) { result = 0; } else { result = 1; } return result; } }); XSSFWorkbook workbook = new XSSFWorkbook(); for (Route route : routeList) { XSSFSheet sheet = workbook.createSheet("PackerReports Route - " + route.getID()); Map<String, String[]> data = new TreeMap<>(); data.put("1", new String[] { "Doorstep Chef Packer Sheet", "", "", route.getDrivers().get(0).getDriver().getDriverName().split(" ")[0] + " - " + route.getDrivers().get(0).getDriver().getContactNumber(), "", " Week: " + DriverReport.returnWeekInt() + " Route: " + route.getID() }); data.put("2", new String[] { "", "", "", "", "", "" }); data.put("3", new String[] { "Customer", "FamSize", "MealType", "Qty", "Allergies", "Exclutions" }); int[] totals = new int[11]; int counter = 4; for (Order order : orderList) { if (order.getRoute().equals(route.getID())) { Client client = order.getClient(); String customer = client.getName() + " " + client.getSurname(); String famSize = order.getFamilySize() + ""; for (Meal meal : order.getMeals()) { data.put(counter + "", new String[] { customer, famSize, meal.getMealType(), meal.getQuantity() + "", meal.getAllergies(), meal.getExclusions() }); customer = ""; famSize = ""; counter++; if (meal.getMealType().equals("Standard")) { totals[1] += meal.getQuantity(); } else if (meal.getMealType().equals("Low Carb")) { totals[2] += meal.getQuantity(); } else if (meal.getMealType().equals("Kiddies")) { totals[3] += meal.getQuantity(); } switch (meal.getQuantity()) { case 1: totals[4]++; break; case 2: totals[5]++; break; case 3: totals[6]++; break; case 4: totals[7]++; break; case 5: totals[8]++; break; case 6: totals[9]++; break; default: if (meal.getQuantity() > 6) { totals[10]++; } } } totals[0]++; } } Set<String> keySet = data.keySet(); int totalSize = 22000; int longestCustomer = 0; for (int key = 1; key < keySet.size() + 1; key++) { Row row = sheet.createRow(key - 1); String[] arr = data.get(key + ""); for (int i = 0; i < arr.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(arr[i]); XSSFCellStyle borderStyle = workbook.createCellStyle(); if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) { longestCustomer = ((String) arr[i]).length(); } if (!((key + "").equals("1") || (key + "").equals("2"))) { borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); if ((key + "").equals("3")) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setAlignment(HorizontalAlignment.CENTER); borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS); borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); XSSFFont font = workbook.createFont(); font.setColor(IndexedColors.WHITE.getIndex()); font.setBold(true); borderStyle.setFont(font); } else { if (i != 0) { borderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); } if (i != 5) { borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } if (i == 5 || i == 4) { borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY); } if ((Integer.parseInt((key + ""))) != keySet.size()) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); } if (i == 3 || i == 1) { borderStyle.setAlignment(HorizontalAlignment.CENTER); } } else { if (key != 3 && (i == 4 || i == 5)) { borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY); } if (i == 3) { borderStyle.setAlignment(HorizontalAlignment.CENTER); } else if (i == 5) { borderStyle.setAlignment(HorizontalAlignment.RIGHT); } XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); } cell.setCellStyle(borderStyle); } } //<editor-fold defaultstate="collapsed" desc="Add Totals"> Row row = sheet.createRow(keySet.size()); Cell cell1 = row.createCell(0); cell1.setCellValue("Clients: " + totals[0]); XSSFCellStyle cellStyle1 = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); font.setBold(true); cellStyle1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); cellStyle1.setFont(font); cell1.setCellStyle(cellStyle1); Cell cell2 = row.createCell(1); cell2.setCellValue("Standard: " + totals[1]); XSSFCellStyle cellStyle2 = workbook.createCellStyle(); font.setBold(true); cellStyle2.setFont(font); cell2.setCellStyle(cellStyle2); Cell cell3 = row.createCell(4); cell3.setCellValue("Low Carb: " + totals[2]); XSSFCellStyle cellStyle3 = workbook.createCellStyle(); font.setBold(true); cellStyle3.setFont(font); cell3.setCellStyle(cellStyle3); Cell cell4 = row.createCell(5); cell4.setCellValue("Kiddies: " + totals[3]); XSSFCellStyle cellStyle4 = workbook.createCellStyle(); font.setBold(true); cellStyle4.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle4.setFont(font); cell4.setCellStyle(cellStyle4); row = sheet.createRow(keySet.size() + 1); Cell holder = row.createCell(0); XSSFCellStyle border1 = workbook.createCellStyle(); border1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); holder.setCellStyle(border1); cell2 = row.createCell(1); cell2.setCellValue("Single: " + totals[4]); XSSFCellStyle cellStyle6 = workbook.createCellStyle(); font.setBold(true); cellStyle6.setFont(font); cell2.setCellStyle(cellStyle6); cell3 = row.createCell(4); cell3.setCellValue("Couple: " + totals[5]); XSSFCellStyle cellStyle7 = workbook.createCellStyle(); font.setBold(true); cellStyle7.setFont(font); cell3.setCellStyle(cellStyle7); cell4 = row.createCell(5); cell4.setCellValue("Small(3): " + totals[6]); XSSFCellStyle cellStyle8 = workbook.createCellStyle(); font.setBold(true); cellStyle8.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle8.setFont(font); cell4.setCellStyle(cellStyle8); row = sheet.createRow(keySet.size() + 2); Cell holder2 = row.createCell(0); XSSFCellStyle border2 = workbook.createCellStyle(); border2.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); border2.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder2.setCellStyle(border2); cell2 = row.createCell(1); cell2.setCellValue("Medium(4): " + totals[7]); XSSFCellStyle cellStyle9 = workbook.createCellStyle(); font.setBold(true); cellStyle9.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle9.setFont(font); cell2.setCellStyle(cellStyle9); Cell holder3 = row.createCell(2); XSSFCellStyle border3 = workbook.createCellStyle(); border3.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder3.setCellStyle(border3); Cell holder4 = row.createCell(3); XSSFCellStyle border4 = workbook.createCellStyle(); border4.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder4.setCellStyle(border4); cell3 = row.createCell(4); cell3.setCellValue("Large(5): " + totals[8]); XSSFCellStyle cellStyle10 = workbook.createCellStyle(); font.setBold(true); cellStyle10.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle10.setFont(font); cell3.setCellStyle(cellStyle10); cell4 = row.createCell(5); cell4.setCellValue("XLarge(6): " + totals[9]); XSSFCellStyle cellStyle11 = workbook.createCellStyle(); font.setBold(true); cellStyle11.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle11.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle11.setFont(font); cell4.setCellStyle(cellStyle11); //</editor-fold> sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4)); sheet.addMergedRegion(new CellRangeAddress(keySet.size(), keySet.size(), 1, 3)); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 1, 3)); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 2, keySet.size() + 2, 1, 3)); sheet.setColumnWidth(0, (longestCustomer + 1) * 240); sheet.setColumnWidth(1, 8 * 240); sheet.setColumnWidth(2, 10 * 240); sheet.setColumnWidth(3, 4 * 240); int usedSize = 0; for (int i = 0; i <= 3; i++) { usedSize += sheet.getColumnWidth(i); } sheet.setColumnWidth(4, (totalSize - usedSize) / 2); sheet.setColumnWidth(5, (totalSize - usedSize) / 2); Row rowDate = sheet.createRow(keySet.size() + 4); Cell cell = rowDate.createCell(0); SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss"); cell.setCellValue(sf.format(Calendar.getInstance().getTime())); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 4, keySet.size() + 4, 0, 5)); } try { workbook.write(excelOut); excelOut.close(); System.out.println("Done - Packer"); if (DSC_Main.generateAllReports) { DSC_Main.reportsDone++; if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) { DSC_Main.reportsDone(); } } else { packerLoadingObj.setVisible(false); packerLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "PackerReport Succesfully Generated", "Success", JOptionPane.INFORMATION_MESSAGE); } } catch (IOException io) { packerLoadingObj.setVisible(false); packerLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "An error occured\nCould not create PackerReport", "Error", JOptionPane.ERROR_MESSAGE); System.err.println("Error - Could not create new PackerReport: "); io.printStackTrace(); } }
From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
protected HorizontalAlignment convertHorizontalAlign(int hAlignment) { switch (hAlignment) { case jdbreport.model.CellStyle.LEFT: return HorizontalAlignment.LEFT; case jdbreport.model.CellStyle.RIGHT: return HorizontalAlignment.RIGHT; case jdbreport.model.CellStyle.CENTER: return HorizontalAlignment.CENTER; case jdbreport.model.CellStyle.JUSTIFY: return HorizontalAlignment.JUSTIFY; }/*from w ww.j a va2 s .co m*/ return HorizontalAlignment.LEFT; }
From source file:jgnash.convert.exportantur.ssf.AccountExport.java
License:Open Source License
public static void exportAccount(final Account account, final String[] columnNames, final LocalDate startDate, final LocalDate endDate, final File file) { Objects.requireNonNull(account); Objects.requireNonNull(startDate); Objects.requireNonNull(endDate); Objects.requireNonNull(file); Objects.requireNonNull(columnNames); final String extension = FileUtils.getFileExtension(file.getAbsolutePath()); try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) { final CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet final Sheet s = wb.createSheet(account.getName()); // create 2 fonts objects final Font defaultFont = wb.createFont(); final Font headerFont = wb.createFont(); defaultFont.setFontHeightInPoints((short) 10); defaultFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setFontHeightInPoints((short) 11); headerFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setBold(true);//from w w w. java 2 s. c om // create header cell styles final CellStyle headerStyle = wb.createCellStyle(); // Set the other cell style and formatting headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); DataFormat df_header = wb.createDataFormat(); headerStyle.setDataFormat(df_header.getFormat("text")); headerStyle.setFont(headerFont); headerStyle.setAlignment(HorizontalAlignment.CENTER); final CellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yy")); dateStyle.setFont(defaultFont); final CellStyle timestampStyle = wb.createCellStyle(); timestampStyle.setDataFormat(createHelper.createDataFormat().getFormat("YYYY-MM-DD HH:MM:SS")); timestampStyle.setFont(defaultFont); final CellStyle textStyle = wb.createCellStyle(); textStyle.setFont(defaultFont); final CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(defaultFont); amountStyle.setAlignment(HorizontalAlignment.RIGHT); final DecimalFormat format = (DecimalFormat) NumericFormats .getFullCommodityFormat(account.getCurrencyNode()); final String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix()); final DataFormat df = wb.createDataFormat(); amountStyle.setDataFormat(df.getFormat(pattern)); // Create headers int row = 0; Row r = s.createRow(row); for (int i = 0; i < columnNames.length; i++) { Cell c = r.createCell(i); c.setCellValue(createHelper.createRichTextString(columnNames[i])); c.setCellStyle(headerStyle); } // Dump the transactions for (final Transaction transaction : account.getTransactions(startDate, endDate)) { r = s.createRow(++row); int col = 0; // date Cell c = r.createCell(col, CellType.STRING); c.setCellValue(DateUtils.asDate(transaction.getLocalDate())); c.setCellStyle(dateStyle); // timestamp c = r.createCell(++col, CellType.STRING); c.setCellValue(DateUtils.asDate(transaction.getTimestamp())); c.setCellStyle(timestampStyle); // number c = r.createCell(++col, CellType.STRING); c.setCellValue(transaction.getNumber()); c.setCellStyle(textStyle); // payee c = r.createCell(++col, CellType.STRING); c.setCellValue(transaction.getPayee()); c.setCellStyle(textStyle); // memo c = r.createCell(++col, CellType.STRING); c.setCellValue(transaction.getMemo()); c.setCellStyle(textStyle); // account c = r.createCell(++col, CellType.STRING); c.setCellValue(getAccountColumnValue(transaction, account)); c.setCellStyle(textStyle); // clr, strip any zero width spaces c = r.createCell(++col, CellType.STRING); c.setCellValue(transaction.getReconciled(account).toString().replaceAll(ZERO_WIDTH_SPACE, "")); c.setCellStyle(textStyle); final BigDecimal amount = transaction.getAmount(account); // increase c = r.createCell(++col, CellType.NUMERIC); if (amount.signum() >= 0) { c.setCellValue(amount.doubleValue()); } c.setCellStyle(amountStyle); // decrease c = r.createCell(++col, CellType.NUMERIC); if (amount.signum() < 0) { c.setCellValue(amount.abs().doubleValue()); } c.setCellStyle(amountStyle); // balance c = r.createCell(++col, CellType.NUMERIC); c.setCellValue(account.getBalanceAt(transaction).doubleValue()); c.setCellStyle(amountStyle); } // autosize the column widths final short columnCount = s.getRow(1).getLastCellNum(); // autosize all of the columns + 10 pixels for (int i = 0; i <= columnCount; i++) { s.autoSizeColumn(i); s.setColumnWidth(i, s.getColumnWidth(i) + 10); } Logger.getLogger(AccountExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles()); // Save final String filename; if (wb instanceof XSSFWorkbook) { filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xlsx"; } else { filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xls"; } try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) { wb.write(out); } catch (final Exception e) { Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } } catch (final IOException e) { Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
/** * Create a 'Summary' sheet containing the table of averages *///from w w w . j a v a2 s . c o m private void createSummarySheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj = getTestService().getTestRunMetadata(test, run); // Create the sheet XSSFSheet sheet = workbook.createSheet("Summary"); // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need XSSFCellStyle summaryDataStyle = sheet.getWorkbook().createCellStyle(); summaryDataStyle.setAlignment(HorizontalAlignment.RIGHT); XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold); XSSFRow row = null; int rowCount = 0; row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Name:"); row.getCell(0).setCellStyle(headerStyle); row.getCell(1).setCellValue(title); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { String description = (String) testRunObj.get(FIELD_DESCRIPTION); description = description == null ? "" : description; row.getCell(0).setCellValue("Description:"); row.getCell(0).setCellStyle(headerStyle); row.getCell(1).setCellValue(description); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Progress (%):"); row.getCell(0).setCellStyle(headerStyle); Double progress = (Double) testRunObj.get(FIELD_PROGRESS); progress = progress == null ? 0.0 : progress; row.getCell(1).setCellValue(progress * 100); row.getCell(1).setCellType(XSSFCell.CELL_TYPE_NUMERIC); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("State:"); row.getCell(0).setCellStyle(headerStyle); String state = (String) testRunObj.get(FIELD_STATE); if (state != null) { row.getCell(1).setCellValue(state); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Started:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_STARTED); if (time > 0) { row.getCell(1).setCellValue(FastDateFormat .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Finished:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_COMPLETED); if (time > 0) { row.getCell(1).setCellValue(FastDateFormat .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Duration:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_DURATION); if (time > 0) { row.getCell(1).setCellValue(DurationFormatUtils.formatDurationHMS(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } rowCount++; rowCount++; // Create a header row row = sheet.createRow(rowCount++); // Header row String[] headers = new String[] { "Event Name", "Total Count", "Success Count", "Failure Count", "Success Rate (%)", "Min (ms)", "Max (ms)", "Arithmetic Mean (ms)", "Standard Deviation (ms)" }; int columnCount = 0; for (String header : headers) { XSSFCell cell = row.getCell(columnCount++); cell.setCellStyle(headerStyle); cell.setCellValue(header); } // Grab results and output them columnCount = 0; TreeMap<String, ResultSummary> summaries = collateResults(true); for (Map.Entry<String, ResultSummary> entry : summaries.entrySet()) { // Reset column count columnCount = 0; row = sheet.createRow(rowCount++); String eventName = entry.getKey(); ResultSummary summary = entry.getValue(); SummaryStatistics statsSuccess = summary.getStats(true); SummaryStatistics statsFail = summary.getStats(false); // Event Name row.getCell(columnCount++).setCellValue(eventName); // Total Count row.getCell(columnCount++).setCellValue(summary.getTotalResults()); // Success Count row.getCell(columnCount++).setCellValue(statsSuccess.getN()); // Failure Count row.getCell(columnCount++).setCellValue(statsFail.getN()); // Success Rate (%) row.getCell(columnCount++).setCellValue(summary.getSuccessPercentage()); // Min (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMin()); // Max (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMax()); // Arithmetic Mean (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMean()); // Standard Deviation (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getStandardDeviation()); } // Auto-size the columns for (int i = 0; i < 10; i++) { sheet.autoSizeColumn(i); } sheet.setColumnWidth(1, 5120); // Printing PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitWidth((short) 1); ps.setLandscape(true); // Header and footer sheet.getHeader().setCenter(title); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createPropertiesSheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj;/* w w w . j av a 2s . c o m*/ try { testRunObj = services.getTestDAO().getTestRun(test, run, true); } catch (ObjectNotFoundException e) { logger.error("Test run not found!", e); return; } // Ensure we don't leak passwords testRunObj = AbstractRestResource.maskValues(testRunObj); BasicDBList propertiesList = (BasicDBList) testRunObj.get(FIELD_PROPERTIES); if (propertiesList == null) { logger.error("Properties not found!"); return; } // Order the properties, nicely TreeMap<String, DBObject> properties = new TreeMap<String, DBObject>(); for (Object propertyObj : propertiesList) { DBObject property = (DBObject) propertyObj; String key = (String) property.get(FIELD_NAME); properties.put(key, property); } XSSFSheet sheet = workbook.createSheet("Properties"); // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need XSSFCellStyle propertyStyle = sheet.getWorkbook().createCellStyle(); propertyStyle.setAlignment(HorizontalAlignment.RIGHT); propertyStyle.setWrapText(true); XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold); XSSFRow row = null; int rowCount = 0; XSSFCell cell = null; int cellCount = 0; row = sheet.createRow(rowCount++); cell = row.createCell(cellCount++); { cell.setCellValue("Property"); cell.setCellStyle(headerStyle); } cell = row.createCell(cellCount++); { cell.setCellValue("Value"); cell.setCellStyle(headerStyle); } cell = row.createCell(cellCount++); { cell.setCellValue("Origin"); cell.setCellStyle(headerStyle); } cellCount = 0; // Iterate all the properties for the test run for (Map.Entry<String, DBObject> entry : properties.entrySet()) { DBObject property = entry.getValue(); String key = (String) property.get(FIELD_NAME); String value = (String) property.get(FIELD_VALUE); String origin = (String) property.get(FIELD_ORIGIN); row = sheet.createRow(rowCount++); cell = row.createCell(cellCount++); { cell.setCellValue(key); cell.setCellStyle(propertyStyle); } cell = row.createCell(cellCount++); { cell.setCellValue(value); cell.setCellStyle(propertyStyle); } cell = row.createCell(cellCount++); { cell.setCellValue(origin); cell.setCellStyle(propertyStyle); } // Back to first column cellCount = 0; } // Size the columns sheet.autoSizeColumn(0); sheet.setColumnWidth(1, 15360); sheet.autoSizeColumn(2); // Printing PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitWidth((short) 1); ps.setLandscape(true); // Header and footer sheet.getHeader().setCenter(title); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createEventSheets(final XSSFWorkbook workbook) { // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need CreationHelper helper = workbook.getCreationHelper(); final XSSFCellStyle dataStyle = workbook.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.RIGHT); final XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold);//from w ww .java 2 s . c o m final XSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(helper.createDataFormat().getFormat("HH:mm:ss")); // Calculate a good window size ResultService resultService = getResultService(); EventRecord firstResult = resultService.getFirstResult(); EventRecord lastResult = resultService.getLastResult(); if (firstResult == null || lastResult == null) { return; } long start = firstResult.getStartTime(); long end = lastResult.getStartTime(); long windowSize = AbstractEventReporter.getWindowSize(start, end, 100); // Well-known window sizes // Keep track of sheets by event name. Note that XLSX truncates sheets to 31 chars, so use 28 chars and ~01, ~02 final Map<String, String> sheetNames = new HashMap<String, String>(31); final Map<String, XSSFSheet> sheets = new HashMap<String, XSSFSheet>(31); final Map<String, AtomicInteger> rowNums = new HashMap<String, AtomicInteger>(31); ResultHandler handler = new ResultHandler() { @Override public boolean processResult(long fromTime, long toTime, Map<String, DescriptiveStatistics> statsByEventName, Map<String, Integer> failuresByEventName) throws Throwable { // Get or create a sheet for each event for (String eventName : statsByEventName.keySet()) { // What sheet name to we use? String sheetName = sheetNames.get(eventName); if (sheetName == null) { sheetName = eventName; if (eventName.length() > 28) { int counter = 1; // Find a sheet name not in use while (true) { sheetName = eventName.substring(0, 28); sheetName = String.format("%s~%02d", sheetName, counter); // Have we used this, yet? if (sheets.containsKey(sheetName)) { // Yes, we have used it. counter++; continue; } // This is unique break; } } sheetNames.put(eventName, sheetName); } // Get and create the sheet, if necessary XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { // Create try { sheet = workbook.createSheet(sheetName); sheets.put(sheetName, sheet); sheet.getHeader().setCenter(title + " - " + eventName); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setLandscape(true); } catch (Exception e) { logger.error("Unable to create workbook sheet for event: " + eventName, e); continue; } // Intro XSSFCell cell = sheet.createRow(0).createCell(0); cell.setCellValue(title + " - " + eventName + ":"); cell.setCellStyle(headerStyle); // Headings XSSFRow row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue("time"); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue("mean"); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue("min"); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue("max"); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue("stdDev"); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue("num"); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue("numPerSec"); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue("fail"); cell = row.createCell(8); cell.setCellStyle(headerStyle); cell.setCellValue("failPerSec"); // Size the columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); } AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { rowNum = new AtomicInteger(2); rowNums.put(eventName, rowNum); } DescriptiveStatistics stats = statsByEventName.get(eventName); Integer failures = failuresByEventName.get(eventName); double numPerSec = (double) stats.getN() / ((double) (toTime - fromTime) / 1000.0); double failuresPerSec = (double) failures / ((double) (toTime - fromTime) / 1000.0); XSSFRow row = sheet.createRow(rowNum.getAndIncrement()); XSSFCell cell; cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(dateStyle); cell.setCellValue(new Date(toTime)); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getN()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numPerSec); cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failures); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failuresPerSec); // Leave out values if there is no mean if (Double.isNaN(stats.getMean())) { continue; } cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMean()); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMin()); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMax()); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getStandardDeviation()); } return true; } }; resultService.getResults(handler, start, windowSize, windowSize, false); // Create charts in the sheets for (String eventName : sheetNames.keySet()) { // Get the sheet name String sheetName = sheetNames.get(eventName); if (sheetName == null) { logger.error("Did not find sheet for event: " + eventName); continue; } // Get the sheet XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { logger.error("Did not find sheet for name: " + sheetName); continue; } // What row did we get up to AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { logger.error("Did not find row number for event: " + sheetName); continue; } // This axis is common to both charts ChartDataSource<Number> xTime = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 0, 0)); // Graph of event times XSSFDrawing drawingTimes = sheet.createDrawingPatriarch(); ClientAnchor anchorTimes = drawingTimes.createAnchor(0, 0, 0, 0, 0, 5, 15, 25); Chart chartTimes = drawingTimes.createChart(anchorTimes); ChartLegend legendTimes = chartTimes.getOrCreateLegend(); legendTimes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataTimes = chartTimes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisTimes = chartTimes.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); bottomAxisTimes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisTimes = chartTimes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Mean ChartDataSource<Number> yMean = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 1, 1)); LineChartSeries yMeanSerie = chartDataTimes.addSeries(xTime, yMean); yMeanSerie.setTitle(title + " - " + eventName + ": Mean (ms)"); // Std Dev ChartDataSource<Number> yStdDev = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 4, 4)); LineChartSeries yStdDevSerie = chartDataTimes.addSeries(xTime, yStdDev); yStdDevSerie.setTitle(title + " - " + eventName + ": Standard Deviation (ms)"); // Plot event times chartTimes.plot(chartDataTimes, bottomAxisTimes, leftAxisTimes); // Graph of event volumes // Graph of event times XSSFDrawing drawingVolumes = sheet.createDrawingPatriarch(); ClientAnchor anchorVolumes = drawingVolumes.createAnchor(0, 0, 0, 0, 0, 25, 15, 35); Chart chartVolumes = drawingVolumes.createChart(anchorVolumes); ChartLegend legendVolumes = chartVolumes.getOrCreateLegend(); legendVolumes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataVolumes = chartVolumes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisVolumes = chartVolumes.getChartAxisFactory() .createCategoryAxis(AxisPosition.BOTTOM); bottomAxisVolumes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisVolumes = chartVolumes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Number per second ChartDataSource<Number> yNumPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 6, 6)); LineChartSeries yNumPerSecSerie = chartDataVolumes.addSeries(xTime, yNumPerSec); yNumPerSecSerie.setTitle(title + " - " + eventName + ": Events per Second"); // Failures per second ChartDataSource<Number> yFailPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 8, 8)); LineChartSeries yFailPerSecSerie = chartDataVolumes.addSeries(xTime, yFailPerSec); yFailPerSecSerie.setTitle(title + " - " + eventName + ": Failures per Second"); // Plot volumes chartVolumes.plot(chartDataVolumes, bottomAxisVolumes, leftAxisVolumes); } }
From source file:org.appdynamics.licensecount.file.WriteExcelDoc.java
public void init() { XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet licenseSummary = workbook.createSheet(LicenseS.LICENSE_SUMMARY); XSSFSheet licenseTiers = workbook.createSheet(LicenseS.TIER_SUMMARY); XSSFSheet licenseHourlyTiers = workbook.createSheet(LicenseS.HOURLY_TIER_SUMMARY); XSSFSheet licenseNodeInfo = workbook.createSheet(LicenseS.NODE_INFO_SUMMARY); XSSFSheet licenseNoNodeTiers = workbook.createSheet(LicenseS.TIERS_WITH_NO_NODES); style = workbook.createCellStyle();/*from w ww. j ava 2 s . c om*/ style.setAlignment(HorizontalAlignment.RIGHT); addNodeInfo(licenseNodeInfo); addTierWNoNodeInfo(licenseNoNodeTiers); // Lets create the first row which will be the header. int headerRowIndex = 0; Row headerRow = licenseSummary.createRow(headerRowIndex); Row tierRow = licenseTiers.createRow(headerRowIndex); Row hourlyTierRow = licenseHourlyTiers.createRow(headerRowIndex); int i = 0; Cell cell_1 = headerRow.createCell(i); cell_1.setCellValue(LicenseS.CUSTOMER_NAME); Cell cell_2 = tierRow.createCell(i); cell_2.setCellValue(LicenseS.APPLICATION_NAME); cell_2 = tierRow.createCell(i + 1); cell_2.setCellValue(LicenseS.TIER_NAME); Cell cell_3 = hourlyTierRow.createCell(i); cell_3.setCellValue(LicenseS.APPLICATION_NAME); cell_3 = hourlyTierRow.createCell(i + 1); cell_3.setCellValue(LicenseS.TIER_NAME); i += 2; int columnCount = 2; int columnCount1 = 3; // Create the date headers for (CustomerLicenseRange cRange : customer.getCustomerRangeValues()) { cell_1 = headerRow.createCell(columnCount); cell_1.setCellValue(cRange.getColumnName()); cell_2 = tierRow.createCell(columnCount1); cell_2.setCellValue(cRange.getColumnName()); columnCount++; columnCount1++; } i = addCustomer(licenseSummary, i); //logger.log(Level.INFO,"Next row " + ++i); headerRow = licenseSummary.createRow(++i); cell_1 = headerRow.createCell(0); cell_1.setCellValue(LicenseS.APPLICATION_NAME); i++; int tierRowCount = 2; int createdHourlyTierHeader = 0; columnCount1 = 3; //logger.log(Level.INFO,new StringBuilder().append("\n\n\tNumber of applications ").append(customer.getApplications().size()).toString()); for (ApplicationLicenseCount app : customer.getApplications().values()) { i = addApplication(licenseSummary, i, app); int inCount = 0; for (TierLicenseCount tier : app.getTierLicenses().values()) { if (createdHourlyTierHeader == 0) { for (TierHourLicenseRange tr : tier.getTierHourLicenseRange()) { cell_3 = hourlyTierRow.createCell(columnCount1); cell_3.setCellValue(tr.getHourColumnName()); columnCount1++; } createdHourlyTierHeader = 1; } tierRowCount = addTier(licenseTiers, tierRowCount, tier, app.getApplicationName(), inCount); inCount++; } if (inCount != 0) tierRowCount++; i++; } tierRowCount = 2; for (ApplicationLicenseCount app : customer.getApplications().values()) { //i=addApplication(licenseSummary,i,app); int inCount = 0; tierRowCount = addHourlyApp(licenseHourlyTiers, tierRowCount, app, inCount); for (TierLicenseCount tier : app.getTierLicenses().values()) { tierRowCount = addHourlyTier(licenseHourlyTiers, tierRowCount, tier, app.getApplicationName(), inCount); inCount++; } tierRowCount++; i++; } try { //Write the workbook in file system //String fileName=new StringBuilder().append("/Users/gilbert.solorzano/Documents/").append(customer.getName()).append("LicenseFile.xlsx").toString(); FileOutputStream out = new FileOutputStream(new File(LicenseS.FILENAME_V)); workbook.write(out); out.close(); StringBuilder bud = new StringBuilder(); bud.append("Completed writing the file: ").append(LicenseS.FILENAME_V).append("."); logger.log(Level.INFO, bud.toString()); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.dashbuilder.dataset.service.DataSetExportServicesImpl.java
License:Apache License
private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); CellStyle style;// w w w . j a v a 2s. com Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 12); titleFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(titleFont); style.setWrapText(false); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex()); styles.put("header", style); Font cellFont = wb.createFont(); cellFont.setFontHeightInPoints((short) 10); cellFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setVerticalAlignment(VerticalAlignment.BOTTOM); style.setFont(cellFont); style.setWrapText(false); style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(3))); styles.put("integer_number_cell", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setVerticalAlignment(VerticalAlignment.BOTTOM); style.setFont(cellFont); style.setWrapText(false); style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4))); styles.put("decimal_number_cell", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.BOTTOM); style.setFont(cellFont); style.setWrapText(false); style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); styles.put(TEXT_CELL, style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.BOTTOM); style.setFont(cellFont); style.setWrapText(false); style.setDataFormat(wb.createDataFormat() .getFormat(DateFormatConverter.convert(Locale.getDefault(), dateFormatPattern))); styles.put("date_cell", style); return styles; }
From source file:org.haplo.jsinterface.generate.KGenerateXLS.java
License:Mozilla Public License
private void styleAlign(SheetStyleInstruction i) { HorizontalAlignment align = null;/*from w w w. ja va 2 s . c om*/ if (i.colour instanceof CharSequence) { switch (i.colour.toString()) { case "CENTRE": case "CENTER": align = HorizontalAlignment.CENTER; break; case "RIGHT": align = HorizontalAlignment.RIGHT; break; } } if (align == null) { return; } HashMap<String, Object> properties = new HashMap<String, Object>(1); properties.put(CellUtil.ALIGNMENT, align); styleApplyToRegion(i, properties); }
From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java
License:Open Source License
private static void appendTotalRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum) { Font boldFont = wb.createFont(); boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD); boldFont.setColor((short) 0x0); XSSFCellStyle commentCell = wb.createCellStyle(); commentCell.setBorderTop(CellStyle.BORDER_THIN); XSSFCellStyle totalCell = wb.createCellStyle(); totalCell.setBorderTop(CellStyle.BORDER_THIN); totalCell.setFont(boldFont);// ww w . j av a 2s.c om XSSFCellStyle totalCellRight = wb.createCellStyle(); totalCellRight.setBorderTop(CellStyle.BORDER_THIN); totalCellRight.setAlignment(HorizontalAlignment.RIGHT); totalCellRight.setFont(boldFont); XSSFRow row = sheet.createRow(rowNum); XSSFCell t1 = row.createCell(0); t1.setCellValue("Total:"); t1.setCellStyle(totalCellRight); XSSFCell t2 = row.createCell(1); t2.setCellFormula("SUM(B1:B" + rowNum + ")*" + TEST_PADDING); t2.setCellStyle(totalCell); XSSFCell t3 = row.createCell(2); t3.setCellStyle(totalCell); XSSFCell t4 = row.createCell(3); t4.setCellValue("Total with Testing & App Migration Factors"); t4.setCellStyle(commentCell); }