List of usage examples for org.apache.poi.ss.usermodel.charts ChartAxis setNumberFormat
void setNumberFormat(String format);
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);// w ww . j ava2 s . co 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); } }