List of usage examples for org.apache.poi.ss.usermodel Sheet getWorkbook
Workbook getWorkbook();
From source file:com.oleke.facebookcrawler.ExcelAPI.java
License:Apache License
/** * This method creates the headers for the FaceBook posts file * * @param sh the sheet to write on//from w w w.jav a 2 s . c o m */ public void F_Posts_Header(Sheet sh) { Row r = createRow(sh); addCell(r, p_id, "id"); addCell(r, post, "post"); addCell(r, post_type, "post_type"); addCell(r, p_gender, "gender"); commitChanges("posts", sh.getWorkbook()); }
From source file:com.opendoorlogistics.core.tables.io.PoiIO.java
License:Open Source License
private static void exportTable(Sheet sheet, ODLTableReadOnly table, int firstOutputRow, ProcessingApi processingApi, ExecutionReport report) { UpdateTimer timer = new UpdateTimer(250); int nbOversized = 0; // create header row int nc = table.getColumnCount(); Row header = sheet.createRow(firstOutputRow); for (int col = 0; col < nc; col++) { Cell cell = header.createCell(col); cell.setCellValue(table.getColumnName(col)); }/* www . ja va2 s . co m*/ // set header style styleHeader(sheet.getWorkbook(), sheet); // write data for (int srcRow = 0; srcRow < table.getRowCount(); srcRow++) { Row row = sheet.createRow(firstOutputRow + 1 + srcRow); for (int col = 0; col < nc; col++) { Cell cell = row.createCell(col); if (saveElementToCell(table, srcRow, col, cell) == SaveElementResult.OVERSIZED) { nbOversized++; } } if (processingApi != null && processingApi.isCancelled()) { return; } if (processingApi != null && timer.isUpdate()) { processingApi.postStatusMessage( "Saving - processed row " + (srcRow + 1) + " of sheet " + table.getName()); } } if (nbOversized > 0 && report != null) { report.log(getOversizedWarningMessage(nbOversized, table.getName())); } }
From source file:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
/** * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat} * //from w w w.java2s . co m * @param cell * @param excell * @param style * @param font */ private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle, Workbook workbook, Font font, Font invisibleFont) { if (excell != null) { // [1] format cell formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont); // [2] set enum if (!excell.isConsiderEnum()) { if (StringUtils.isNotBlank(excell.getDisplayText())) { cell.setCellValue(excell.getDisplayText()); } if (!excell.isMultiSelect() && excell.isNumberValidation()) { addNumberValidation(cell); } } else { String[] list = (String[]) excell.getRestriction().getEnumValues() .toArray(new String[excell.getRestriction().getEnumValues().size()]); SXSSFSheet sheet = (SXSSFSheet) cell.getSheet(); DataValidationHelper dvHelper = sheet.getDataValidationHelper(); DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper .createExplicitListConstraint(list); CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex()); DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions); dataValidation.setSuppressDropDownArrow(true); dataValidation.createErrorBox("Not Applicable", "Can't change the value"); dataValidation.setShowErrorBox(true); try { if (isValidEnumList(list)) { sheet.addValidationData(dataValidation); } else { Sheet hidden = null; String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText()); Workbook wBook = cell.getSheet().getWorkbook(); if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) { hidden = wBook.getSheet(hiddenName); } else { hidden = wBook.createSheet(hiddenName); for (int i = 0, length = list.length; i < length; i++) { String name = list[i]; Row row = hidden.createRow(i); Cell cell1 = row.createCell(0); cell1.setCellValue(name); } Name namedCell = hidden.getWorkbook().getName(hiddenName); namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName(); namedCell.setNameName(hiddenName); namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length); } dvConstraint = (XSSFDataValidationConstraint) dvHelper .createFormulaListConstraint(hiddenName); dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions); dataValidation.createErrorBox("Not Applicable", "Can't change the value"); dataValidation.setShowErrorBox(true); cell.getSheet().addValidationData(dataValidation); wBook.setSheetHidden(wBook.getSheetIndex(hidden), true); } } catch (Exception e) { String msg = "Excel creation failed while building cell: " + excell.getDisplayText(); throw new IllegalStateException(msg, e); } // cell.setCellValue(excelConfig.getDropDownMsg()); } } }
From source file:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
private static CellStyle getDateStyle(String style, Sheet sheet, Font font) { if (StringUtils.isNotBlank(style) && sheet != null) { StyleKey cache = new StyleKey(style, sheet.getSheetName()); if (styleCache.get(cache) == null) { CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); font.setFontHeight((short) 10); cellStyle.setFont(font);/*from w w w. j a va 2s . co m*/ styleCache.put(cache, cellStyle); } return styleCache.get(cache); } return null; }
From source file:com.r573.enfili.common.doc.spreadsheet.SpreadsheetHelper.java
License:Apache License
public static void calculate(Sheet sheet) { FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); for (Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.hasNext();) { Row row = rowIterator.next();// w ww . j av a 2s . c om for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) { Cell cell = cellIterator.next(); if (cell != null && cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { evaluator.evaluateInCell(cell); } } } }
From source file:com.runwaysdk.dataaccess.io.ExcelExportSheet.java
License:Open Source License
protected void writeHeader(Sheet sheet, Drawing drawing, Row nameRow, Row labelRow, int i, ExcelColumn column, CellStyle boldStyle) {/*from w ww. j av a2 s . c om*/ CreationHelper helper = sheet.getWorkbook().getCreationHelper(); // Notify the listeners for (ExcelExportListener listener : listeners) { listener.preHeader(column); } nameRow.createCell(i).setCellValue(helper.createRichTextString(column.getAttributeName())); Cell cell = labelRow.createCell(i); cell.setCellValue(helper.createRichTextString(column.getDisplayLabel())); if (column.isRequired() && boldStyle != null) { cell.setCellStyle(boldStyle); } if (column.getDescription() != null && column.getDescription().length() > 0) { ClientAnchor anchor = helper.createClientAnchor(); anchor.setDx1(0); anchor.setDy1(0); anchor.setDx2(0); anchor.setDy2(0); anchor.setCol1(0); anchor.setRow1(0); anchor.setCol2(0); anchor.setRow2(4); Comment comment = drawing.createCellComment(anchor); comment.setString(helper.createRichTextString(column.getDescription())); cell.setCellComment(comment); } sheet.autoSizeColumn((short) i); }
From source file:com.runwaysdk.query.ViewArrayExcelExporter.java
License:Open Source License
/** * Prepares a new sheet (which represents a type) in the workbook. Fills in * all necessary information for the sheet. * * @return/*from ww w .ja v a 2s. c o m*/ */ protected Sheet prepareSheet() { Sheet sheet = super.createSheet(); CreationHelper helper = sheet.getWorkbook().getCreationHelper(); // Row typeRow = sheet.createRow(0); // typeRow.createCell(0).setCellValue(new HSSFRichTextString(type)); Row labelRow = sheet.createRow(0); for (int col = 0; col < attributes.size(); col++) { String attributeName = attributes.get(col); MdAttributeDAOIF mdAttribute = this.getMdAttribute(mdView, attributeName); if (mdAttribute != null) { labelRow.createCell(col).setCellValue( helper.createRichTextString(mdAttribute.getDisplayLabel(Session.getCurrentLocale()))); } } for (int row = 0; row < array.length; row++) { View view = array[row]; Row valueRow = sheet.createRow(row + 1); for (int col = 0; col < attributes.size(); col++) { String attributeName = attributes.get(col); MdAttributeDAOIF mdAttribute = this.getMdAttribute(mdView, attributeName).getMdAttributeConcrete(); if (mdAttribute != null) { String value = view.getValue(attributeName); if (mdAttribute instanceof MdAttributeBooleanDAOIF) { MdAttributeBooleanDAOIF mdAttributeBooleanDAOIF = (MdAttributeBooleanDAOIF) mdAttribute; populateBooleanCell(valueRow, col, value, mdAttributeBooleanDAOIF); } else if (mdAttribute instanceof MdAttributeNumberDAOIF) { populateNumberCell(valueRow, col, value); } else if (mdAttribute instanceof MdAttributeDateDAOIF) { populateDateCell(valueRow, col, value); } else if (mdAttribute instanceof MdAttributeDateTimeDAOIF) { populateDateTimeCell(valueRow, col, value); } else if (mdAttribute instanceof MdAttributeTimeDAOIF) { populateTimeCell(valueRow, col, value); } else if (mdAttribute instanceof MdAttributeCharacterDAOIF || mdAttribute instanceof MdAttributeReferenceDAOIF) { populateCharacterCell(valueRow, col, value); } } } } return sheet; }
From source file:com.upbest.utils.AddDimensionedImage.java
License:Apache License
/** * Add an image to a worksheet.//from www . j ava2s . c o m * * @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:com.vaadin.addon.spreadsheet.action.InsertDeleteCellCommentAction.java
License:Open Source License
private void createCellComment(Spreadsheet spreadsheet, Sheet sheet, Cell cell, CellReference cellRef) { CreationHelper factory = sheet.getWorkbook().getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(cell.getRowIndex());/*from w w w .j av a 2 s. c o m*/ anchor.setRow2(cell.getRowIndex() + 3); // Create the comment and set the text+author Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(""); comment.setString(str); // Fetch author from provider or fall back to default String author = null; if (spreadsheet.getCommentAuthorProvider() != null) { author = spreadsheet.getCommentAuthorProvider().getAuthorForComment(cellRef); } if (author == null || author.trim().isEmpty()) { author = "Spreadsheet User"; } comment.setAuthor(author); // Assign the comment to the cell cell.setCellComment(comment); }
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight payments that are due in the next thirty days. * In this example, Due dates are entered in cells A2:A4. *///from w w w.j ava 2s. c om static void expiry(Sheet sheet) { CellStyle style = sheet.getWorkbook().createCellStyle(); style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm")); sheet.createRow(0).createCell(0).setCellValue("Date"); sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29"); sheet.createRow(2).createCell(0).setCellFormula("A2+1"); sheet.createRow(3).createCell(0).setCellFormula("A3+1"); for (int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted"); }