List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getHeader
@Override
public Header getHeader()
From source file:coverageqc.functions.MyExcelEditor.java
public static void excelFormator(XSSFSheet currentSheet, File variantTsvFile, String tsvHeadingLine) throws IOException { String[] headingsArray = tsvHeadingLine.split("\t"); HashMap<String, Integer> headings = new HashMap<String, Integer>(); for (int x = 0; x < headingsArray.length; x++) { headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x); }//from w w w . j a va 2s .c o m XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup(); File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx"); currentSheet.getHeader().setLeft(xslxTempFile.getName()); currentSheet.getHeader().setRight("DO NOT DISCARD!!! Keep with patient folder."); //in Dr. Carter's VBA was set at points 18 which is .25 inches currentSheet.setMargin(Sheet.RightMargin, .25); currentSheet.setMargin(Sheet.LeftMargin, .25); printSetup.setOrientation(PrintOrientation.LANDSCAPE); //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this //is the best workaround I can do, it will only looked cramped for those with a lot of calls printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 3); currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1")); currentSheet.setFitToPage(true); //making it by default not print the fellow's interp currentSheet.getWorkbook().setPrintArea(0, 1, currentSheet.getRow(0).getPhysicalNumberOfCells(), 0, currentSheet.getLastRowNum()); for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) { currentSheet.autoSizeColumn(x); } currentSheet.setColumnWidth(0, 10000); currentSheet.setColumnWidth(1, 10000); currentSheet.setColumnWidth(headings.get("Consequence").intValue() + 2, 3500); currentSheet.setColumnHidden(headings.get("Classification").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Inherited From").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Allelic Depths").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Custom Annotation").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Custom Gene Annotation").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Num Transcripts").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Transcript").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("cDNA Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("CDS Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Protein Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Amino Acids").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Codons").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGNC").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Transcript HGNC").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Canonical").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Sift").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("PolyPhen").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("ENSP").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGVSc").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGVSp").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("dbSNP ID").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Ancestral Allele").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Allele Freq").intValue() + 2, true); //everything beyond this is hidden for (int x = headings.get("Global Minor Allele").intValue() + 2; x < currentSheet.getRow(0) .getPhysicalNumberOfCells(); x++) { currentSheet.setColumnHidden(x, true); } }
From source file:coverageqc.functions.MyExcelGenerator.java
public void excelFormator(String sheetName, File variantTsvFile) throws IOException { // String[] headingsArray = tsvHeadingLine.split("\t"); // HashMap<String, Integer> headings = new HashMap<String, Integer>(); // for(int x = 0; x < headingsArray.length; x++) { // headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x); // }/*from w w w . j av a2s .c o m*/ XSSFSheet currentSheet = this.workbookcopy.getSheet(sheetName); XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup(); File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx"); currentSheet.getHeader().setLeft(xslxTempFile.getName()); currentSheet.getHeader().setRight("DO NOT DISCARD!!! Keep with patient folder."); //in Dr. Carter's VBA was set at points 18 which is .25 inches currentSheet.setMargin(Sheet.RightMargin, .25); currentSheet.setMargin(Sheet.LeftMargin, .25); printSetup.setOrientation(PrintOrientation.LANDSCAPE); //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this //is the best workaround I can do, it will only looked cramped for those with a lot of calls printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 3); currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1")); currentSheet.setFitToPage(true); //making it by default not print the fellow's interp currentSheet.getWorkbook().setPrintArea(0, 2, 20, 0, currentSheet.getLastRowNum()); for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) { currentSheet.autoSizeColumn(x); if (x > 33) { currentSheet.setColumnHidden(x, true); } } currentSheet.setColumnWidth(0, 10000); currentSheet.setColumnWidth(1, 10000); currentSheet.setColumnWidth(2, 10000); // currentSheet.setColumnWidth(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, 3500); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Gene"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Variant"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Chr"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Coordinate"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Type"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Genotype"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Exonic"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Filters"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Quality"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("GQX"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Variant Freq"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Read Depth"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Read Depth"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Global Minor"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Classification"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Inherited From"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allelic Depths"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Annotation"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Gene Annotation"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Num Transcripts"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("cDNA Position"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("CDS Position"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Protein Position"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Amino Acids"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Codons"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGNC"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript HGNC"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Canonical"))+3, true); // //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false); // //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ENSP"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSc"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSp"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("dbSNP ID"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Ancestral Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Global Minor Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Amr"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Asn"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Af"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Eur"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Evs"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Coverage"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Samples"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Conserved Sequence"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC ID"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Wildtype"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Gene"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Primary Site"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Histology"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Accession"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Ref"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Alleles"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Allele Type"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Significance"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alternate Alleles"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Google Scholar"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PubMed"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("UCSC Browser"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar RS"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Disease Name"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar MedGen"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar OMIM"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Orphanet"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar GeneReviews"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar SnoMedCt ID"))+3, true); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
/** * Create a 'Summary' sheet containing the table of averages *//* w ww . j a v a 2 s . c om*/ 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;//from w ww.ja v a 2s. c om 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 w w . j a v a 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); } }