List of usage examples for org.apache.poi.ss.usermodel Sheet getWorkbook
Workbook getWorkbook();
From source file:de.quamoco.qm.editor.export.ResultCalibrationExporter.java
License:Apache License
/** * Create the body of the sheet to display the {@link FactorAggregation}s. * There is a column for each {@link FactorAggregation}. *///from w w w .j a va 2 s .co m private void createFactorAggregationSheetBody(Sheet sheet, EAttribute attribute, int header, IProgressMonitor monitor) throws IOException { checkNumberOfColumns(sheet.getWorkbook(), multiMeasureEvaluations.size() + factorAggregations.size() + 1); int column = 1; for (WeightedSumFactorAggregation aggregation : factorAggregations) { sheet.getRow(0).createCell(column).setCellValue(UUIDUtils.getId(aggregation)); sheet.getRow(1).createCell(column).setCellValue(aggregation.getEvaluates().getQualifiedName()); for (FactorRanking ranking : aggregation.getRankings()) { int row = factors.indexOf(ranking.getFactor()) + header; Object value = ranking.eGet(attribute); double doubleValue = 0.0; if (value instanceof Integer) { doubleValue = (Integer) value; } else { doubleValue = (Double) value; } sheet.getRow(row).createCell(column).setCellValue(doubleValue); } column++; monitor.worked(1); } for (WeightedSumMultiMeasureEvaluation aggregation : multiMeasureEvaluations) { sheet.getRow(0).createCell(column).setCellValue(UUIDUtils.getId(aggregation)); sheet.getRow(1).createCell(column).setCellValue(aggregation.getEvaluates().getQualifiedName()); for (MeasureRanking ranking : aggregation.getRankings()) { int row = factors.size() + measures.indexOf(ranking.getMeasure()) + header; Object value = ranking.eGet(attribute); double doubleValue = 0.0; if (value instanceof Integer) { doubleValue = (Integer) value; } else { doubleValue = (Double) value; } sheet.getRow(row).createCell(column).setCellValue(doubleValue); } column++; monitor.worked(1); } }
From source file:de.quamoco.qm.editor.export.ResultCalibrationExporter.java
License:Apache License
/** * Create the body of the sheet to display {@link Impact}s and * {@link FactorRefinement}s to {@link FactorAggregation}s. There is a * column for each {@link FactorAggregation}. *///from ww w. j a va 2s . c o m private void createFactorImpactSheetBody(Sheet sheet, int header, IProgressMonitor monitor) throws IOException { checkNumberOfColumns(sheet.getWorkbook(), factorAggregations.size() + 1); int column = 1; for (WeightedSumFactorAggregation aggregation : factorAggregations) { createFactorImpactColumn(sheet, aggregation, monitor, column, header); column++; } for (WeightedSumMultiMeasureEvaluation evaluation : multiMeasureEvaluations) { createFactorImpactColumn(sheet, evaluation, monitor, column, header); column++; } }
From source file:demo.poi.AddDimensionedImage.java
License:Apache License
/** * Add an image to a worksheet./*from w w w .j a v a2s .c om*/ * * @param colNumber * A primitive int that contains the index number of a column on * the worksheet; POI column indices are zero based. Together * with the rowNumber parameter's value, this parameter * identifies a cell on the worksheet. The images top left hand * corner will be aligned with the top left hand corner of this * cell. * @param rowNumber * A primitive int that contains the index number of a row on the * worksheet; POI row indices are zero based. Together with the * rowNumber parameter's value, this parameter identifies a cell * on the worksheet. The images top left hand corner will be * aligned with the top left hand corner of this cell. * @param sheet * A reference to the sheet that contains the cell identified by * the two parameters above. * @param drawing * An instance of the DrawingPatriarch class. This is now passed * into the method where it was, previously, recovered from the * sheet in order to allow multiple pictures be inserted. If the * patriarch was not 'cached in this manner each time it was * created any previously positioned images would be simply * over-written. * @param imageFile * An instance of the URL class that encapsulates the name of and * path to the image that is to be 'inserted into' the sheet. * @param reqImageWidthMM * A primitive double that contains the required width of the * image in millimetres. * @param reqImageHeightMM * A primitive double that contains the required height of the * image in millimetres. * @param resizeBehaviour * A primitive int whose value will determine how the code should * react if the image is larger than the cell referenced by the * colNumber and rowNumber parameters. Four constants are * provided to determine what should happen; * AddDimensionedImage.EXPAND_ROW * AddDimensionedImage.EXPAND_COLUMN * AddDimensionedImage.EXPAND_ROW_AND_COLUMN * AddDimensionedImage.OVERLAY_ROW_AND_COLUMN * @throws java.io.FileNotFoundException * If the file containing the image cannot be located. * @throws java.io.IOException * If a problem occurs whilst reading the file of image data. * @throws java.lang.IllegalArgumentException * If an invalid value is passed to the resizeBehaviour * parameter or if the extension of the image file indicates * that it is of a type that cannot currently be added to the * worksheet. */ public void addImageToSheet(int colNumber, int rowNumber, Sheet sheet, Drawing drawing, URL imageFile, double reqImageWidthMM, double reqImageHeightMM, int resizeBehaviour) throws IOException, IllegalArgumentException { ClientAnchor anchor = null; ClientAnchorDetail rowClientAnchorDetail = null; ClientAnchorDetail colClientAnchorDetail = null; int imageType = 0; // Validate the resizeBehaviour parameter. if ((resizeBehaviour != AddDimensionedImage.EXPAND_COLUMN) && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW) && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW_AND_COLUMN) && (resizeBehaviour != AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)) { throw new IllegalArgumentException("Invalid value passed to the " + "resizeBehaviour parameter of AddDimensionedImage.addImageToSheet()"); } // Call methods to calculate how the image and sheet should be // manipulated to accomodate the image; columns and then rows. colClientAnchorDetail = this.fitImageToColumns(sheet, colNumber, reqImageWidthMM, resizeBehaviour); rowClientAnchorDetail = this.fitImageToRows(sheet, rowNumber, reqImageHeightMM, resizeBehaviour); // Having determined if and how to resize the rows, columns and/or the // image, create the ClientAnchor object to position the image on // the worksheet. Note how the two ClientAnchorDetail records are // interrogated to recover the row/column co-ordinates and any insets. // The first two parameters are not used currently but could be if the // need arose to extend the functionality of this code by adding the // ability to specify that a clear 'border' be placed around the image. anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor(); anchor.setDx1(0); anchor.setDy1(0); anchor.setDx2(colClientAnchorDetail.getInset()); anchor.setDy2(rowClientAnchorDetail.getInset()); anchor.setCol1(colClientAnchorDetail.getFromIndex()); anchor.setRow1(rowClientAnchorDetail.getFromIndex()); anchor.setCol2(colClientAnchorDetail.getToIndex()); anchor.setRow2(rowClientAnchorDetail.getToIndex()); // For now, set the anchor type to do not move or resize the // image as the size of the row/column is adjusted. This could easilly // become another parameter passed to the method. Please read the note // above regarding the behaviour of image resizing. anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE); // Now, add the picture to the workbook. Note that unlike the similar // method in the HSSF Examples section, the image type is checked. // First, // the image files location is identified by interrogating the URL // passed // to the method, the images type is identified before it is added to // the // sheet. String sURL = imageFile.toString().toLowerCase(); if (sURL.endsWith(".png")) { imageType = Workbook.PICTURE_TYPE_PNG; } else if (sURL.endsWith("jpg") || sURL.endsWith(".jpeg")) { imageType = Workbook.PICTURE_TYPE_JPEG; } else { throw new IllegalArgumentException("Invalid Image file : " + sURL); } int index = sheet.getWorkbook().addPicture(IOUtils.toByteArray(imageFile.openStream()), imageType); drawing.createPicture(anchor, index); }
From source file:eu.impact_project.resultsrepository.report.Report.java
License:Apache License
private void evaluateFormulas(Sheet sheet) { FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); Row row = null;/*from w w w . j a v a 2 s . c om*/ Cell cell = null; for (int i = 0; i < 100; i++) { row = sheet.getRow(i); if (row == null) row = sheet.createRow(i); for (int j = 0; j < 50; j++) { cell = row.getCell(j); if (cell == null) cell = row.createCell(j); evaluator.evaluateFormulaCell(cell); } } }
From source file:eu.impact_project.resultsrepository.report.Report.java
License:Apache License
private InputStream toStream(Sheet sheet) throws IOException { Workbook excelWorkbook = sheet.getWorkbook(); ByteArrayOutputStream os = new ByteArrayOutputStream(); excelWorkbook.write(os);/* www . j av a 2s . co m*/ InputStream is = new ByteArrayInputStream(os.toByteArray()); os.close(); return is; }
From source file:guru.qas.martini.report.DefaultState.java
License:Apache License
public void updateLongestExecutions() { if (!longestExecutionCells.isEmpty()) { for (Cell cell : longestExecutionCells) { CellStyle original = cell.getCellStyle(); Sheet sheet = cell.getSheet(); Workbook workbook = sheet.getWorkbook(); CellStyle newStyle = workbook.createCellStyle(); newStyle.cloneStyleFrom(original); int originalFontIndex = original.getFontIndexAsInt(); Font originalFont = workbook.getFontAt(originalFontIndex); Font font = workbook.createFont(); font.setBold(true);/*ww w . ja v a 2 s . co m*/ font.setColor(IndexedColors.DARK_RED.getIndex()); font.setFontHeight((short) Math.round(originalFont.getFontHeight() * 1.5)); newStyle.setFont(font); cell.setCellStyle(newStyle); Row row = cell.getRow(); short firstCellNum = row.getFirstCellNum(); short lastCellNum = row.getLastCellNum(); for (int i = firstCellNum; i < lastCellNum; i++) { Cell rowCell = row.getCell(i); original = rowCell.getCellStyle(); CellStyle borderStyle = workbook.createCellStyle(); borderStyle.cloneStyleFrom(original); borderStyle.setBorderTop(BorderStyle.MEDIUM); borderStyle.setBorderBottom(BorderStyle.MEDIUM); if (i == cell.getColumnIndex()) { borderStyle.setBorderLeft(BorderStyle.MEDIUM); borderStyle.setBorderRight(BorderStyle.MEDIUM); } else if (i == firstCellNum) { borderStyle.setBorderLeft(BorderStyle.MEDIUM); } else if (i == lastCellNum - 1) { borderStyle.setBorderRight(BorderStyle.MEDIUM); } rowCell.setCellStyle(borderStyle); } } } }
From source file:guru.qas.martini.report.DefaultState.java
License:Apache License
protected void colorCompromisedThemes() { Collection<Cell> failed = statii.get("FAILED"); if (!failed.isEmpty()) { List<Row> rows = Lists.newArrayListWithExpectedSize(failed.size()); for (Cell cell : failed) { Row row = cell.getRow();//from w w w . j a v a2s .com rows.add(row); } Set<Cell> compromisedThemeCells = Sets.newHashSet(); Map<String, Collection<Cell>> themeMap = themes.asMap(); for (Map.Entry<String, Collection<Cell>> mapEntry : themeMap.entrySet()) { Collection<Cell> themeCells = mapEntry.getValue(); boolean compromised = false; for (Iterator<Cell> iterator = themeCells.iterator(); !compromised && iterator.hasNext();) { Cell themeCell = iterator.next(); Row row = themeCell.getRow(); compromised = rows.contains(row); } if (compromised) { compromisedThemeCells.addAll(themeCells); } } Set<String> compromisedThemes = Sets.newHashSet(); for (Cell themeCell : compromisedThemeCells) { String contents = themeCell.getStringCellValue(); if (null != contents) { Iterable<String> themes = Splitter.onPattern("\\s+").omitEmptyStrings().split(contents); Iterables.addAll(compromisedThemes, themes); } } for (String theme : compromisedThemes) { Collection<Cell> cells = themes.get(theme); for (Cell cell : cells) { CellStyle cellStyle = cell.getCellStyle(); Sheet sheet = cell.getSheet(); Workbook workbook = sheet.getWorkbook(); int originalFontIndex = cellStyle.getFontIndexAsInt(); Font originalFont = workbook.getFontAt(originalFontIndex); CellStyle clone = workbook.createCellStyle(); clone.cloneStyleFrom(cellStyle); Font font = workbook.findFont(true, IndexedColors.DARK_RED.getIndex(), originalFont.getFontHeight(), originalFont.getFontName(), originalFont.getItalic(), originalFont.getStrikeout(), originalFont.getTypeOffset(), originalFont.getUnderline()); if (null == font) { font = workbook.createFont(); font.setBold(true); font.setColor(IndexedColors.DARK_RED.getIndex()); font.setFontHeight(originalFont.getFontHeight()); font.setFontName(originalFont.getFontName()); font.setItalic(originalFont.getItalic()); font.setStrikeout(originalFont.getStrikeout()); font.setTypeOffset(originalFont.getTypeOffset()); font.setUnderline(originalFont.getUnderline()); } clone.setFont(font); cell.setCellStyle(clone); } } } }
From source file:guru.qas.martini.report.DefaultState.java
License:Apache License
@Override public void updateSuites(Sheet sheet) { int lastRowNum = sheet.getLastRowNum(); Row row = sheet.createRow(0 == lastRowNum ? 0 : lastRowNum + 1); row.createCell(0, CellType.STRING).setCellValue("ID"); row.createCell(1, CellType.STRING).setCellValue("Date"); row.createCell(2, CellType.STRING).setCellValue("Name"); row.createCell(3, CellType.STRING).setCellValue("Hostname"); row.createCell(4, CellType.STRING).setCellValue("IP"); row.createCell(5, CellType.STRING).setCellValue("Username"); row.createCell(6, CellType.STRING).setCellValue("Profiles"); row.createCell(7, CellType.STRING).setCellValue("Environment Variables"); for (Map.Entry<String, JsonObject> mapEntry : suites.entrySet()) { row = sheet.createRow(sheet.getLastRowNum() + 1); String id = mapEntry.getKey(); row.createCell(0, CellType.STRING).setCellValue(id); JsonObject suite = mapEntry.getValue(); JsonPrimitive primitive = suite.getAsJsonPrimitive("startTimestamp"); Long timestamp = null == primitive ? null : primitive.getAsLong(); Cell cell = row.createCell(1);//from w w w. j a va 2 s . com if (null != timestamp) { Workbook workbook = sheet.getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); CreationHelper creationHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm")); cellStyle.setVerticalAlignment(VerticalAlignment.TOP); cell.setCellValue(new Date(timestamp)); cell.setCellStyle(cellStyle); } cell = row.createCell(2); primitive = suite.getAsJsonPrimitive("name"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(3); JsonObject host = suite.getAsJsonObject("host"); primitive = null == host ? null : host.getAsJsonPrimitive("name"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(4); primitive = null == host ? null : host.getAsJsonPrimitive("ip"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(5); primitive = null == host ? null : host.getAsJsonPrimitive("username"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(6); JsonArray array = suite.getAsJsonArray("profiles"); List<String> profiles = Lists.newArrayList(); if (null != array) { int size = array.size(); for (int i = 0; i < size; i++) { JsonElement element = array.get(i); String profile = null == element ? null : element.getAsString(); profiles.add(profile); } String profilesValue = Joiner.on('\n').skipNulls().join(profiles); cell.setCellValue(profilesValue); } cell = row.createCell(7); JsonObject environmentVariables = suite.getAsJsonObject("environment"); Map<String, String> index = new TreeMap<>(); if (null != environmentVariables) { Set<Map.Entry<String, JsonElement>> entries = environmentVariables.entrySet(); for (Map.Entry<String, JsonElement> environmentEntry : entries) { String key = environmentEntry.getKey(); JsonElement element = environmentEntry.getValue(); String value = null == element ? "" : element.getAsString(); index.put(key, value); } String variablesValue = Joiner.on('\n').withKeyValueSeparator('=').useForNull("").join(index); cell.setCellValue(variablesValue); } } for (int i = 0; i < 8; i++) { sheet.autoSizeColumn(i, false); } }
From source file:guru.qas.martini.report.DefaultTraceabilityMatrix.java
License:Apache License
protected void addHeader(Sheet sheet) { Row row = sheet.createRow(0);//from w w w . jav a2 s . c om Workbook workbook = sheet.getWorkbook(); CellStyle style = getHeaderStyle(workbook); for (int i = 0; i < columns.size(); i++) { TraceabilityColumn column = columns.get(i); Cell cell = row.createCell(i, CellType.STRING); String label = column.getLabel(); cell.setCellValue(label); cell.setCellStyle(style); } sheet.createFreezePane(0, 1); }
From source file:hu.webhejj.commons.io.table.excel.ExcelTableAppender.java
License:Apache License
public ExcelTableAppender(Sheet sheet) { this.sheet = sheet; rowIndex = 0; // sheet.getPhysicalNumberOfRows(); columnIndex = 0;// ww w . j a v a 2s . co m textCellStyle = sheet.getWorkbook().createCellStyle(); textCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); }