Example usage for org.apache.poi.ss.usermodel Sheet getWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getWorkbook.

Prototype

Workbook getWorkbook();

Source Link

Document

Return the parent workbook

Usage

From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java

License:Apache License

public void setCellValue(Sheet shProcess, int nRow, int nCol, Object objValue, boolean isFormula,
        PoiCellStyle pCellStyle) {/* www.  ja v  a 2s . c  om*/
    // Logger logCurrent =
    // LoggerFactory.getLogger(WorkbookProcessor.class.getCanonicalName());

    try {
        Row rw = shProcess.getRow(nRow);
        if (rw == null) {
            // logCurrent.finest("Create Row");
            rw = shProcess.createRow(nRow);
        }
        Cell c = rw.getCell(nCol);
        if (c == null) {
            // logCurrent.finest("Create Cell");
            c = rw.createCell(nCol);
        }
        if (isFormula) {
            c.setCellFormula((String) objValue);
        } else {
            if (objValue instanceof Double) {
                c.setCellValue((Double) objValue);
            } else if (objValue instanceof Integer) {
                c.setCellValue((Integer) objValue);
            } else {
                if (objValue instanceof Date) {
                    c.setCellValue((Date) objValue);
                } else {
                    c.setCellValue("" + objValue);
                }
            }
        }
        // *** STYLE CONFIG Since V 1.1.7 ***

        if (pCellStyle != null) {
            checkStyleConstantValues();
            if (pCellStyle.getCellStyle() != null) {
                c.setCellStyle(pCellStyle.getCellStyle());
            } else {
                CellStyle style = shProcess.getWorkbook().createCellStyle();

                if (pCellStyle.getAlignment() != null)
                    style.setAlignment(m_StyleConstantValues.get(pCellStyle.getAlignment()));

                if (pCellStyle.getBorderBottom() != null)
                    style.setBorderBottom(m_StyleConstantValues.get(pCellStyle.getBorderBottom()));

                if (pCellStyle.getBorderLeft() != null)
                    style.setBorderLeft(m_StyleConstantValues.get(pCellStyle.getBorderLeft()));

                if (pCellStyle.getBorderRight() != null)
                    style.setBorderRight(m_StyleConstantValues.get(pCellStyle.getBorderRight()));

                if (pCellStyle.getBorderTop() != null)
                    style.setBorderTop(m_StyleConstantValues.get(pCellStyle.getBorderTop()));

                if (pCellStyle.getBottomBorderColor() != null)
                    style.setBottomBorderColor(
                            IndexedColors.valueOf(pCellStyle.getBottomBorderColor()).getIndex());

                if (pCellStyle.getDataFormat() != null) {
                    DataFormat format = shProcess.getWorkbook().createDataFormat();
                    style.setDataFormat(format.getFormat(pCellStyle.getDataFormat()));
                }

                if (pCellStyle.getFillBackgroundColor() != null)
                    style.setFillBackgroundColor(
                            IndexedColors.valueOf(pCellStyle.getFillBackgroundColor()).getIndex());

                if (pCellStyle.getFillForegroundColor() != null)
                    style.setFillForegroundColor(
                            IndexedColors.valueOf(pCellStyle.getFillForegroundColor()).getIndex());

                if (pCellStyle.getFillPattern() != null)
                    style.setFillPattern(m_StyleConstantValues.get(pCellStyle.getFillPattern()));

                // Create a new font and alter it.
                Font font = shProcess.getWorkbook().createFont();

                if (pCellStyle.getFontBoldweight() != null)
                    font.setBoldweight(m_StyleConstantValues.get(pCellStyle.getFontBoldweight()));

                if (pCellStyle.getFontColor() != null)
                    font.setColor(IndexedColors.valueOf(pCellStyle.getFontColor()).getIndex());

                if (pCellStyle.getFontHeightInPoints() != 0)
                    font.setFontHeightInPoints(pCellStyle.getFontHeightInPoints());

                if (pCellStyle.getFontName() != null)
                    font.setFontName(pCellStyle.getFontName());

                if (pCellStyle.isFontItalic())
                    font.setItalic(pCellStyle.isFontItalic());

                if (pCellStyle.isFontStrikeout())
                    font.setStrikeout(pCellStyle.isFontStrikeout());

                if (pCellStyle.getFontUnderline() != null)
                    font.setUnderline(m_StyleByteConstantValues.get(pCellStyle.getFontUnderline()));

                if (pCellStyle.getFontTypeOffset() != null)
                    font.setTypeOffset(m_StyleConstantValues.get(pCellStyle.getFontTypeOffset()));

                // Set Font
                style.setFont(font);

                if (pCellStyle.isHidden())
                    style.setHidden(pCellStyle.isHidden());

                if (pCellStyle.getIndention() != null)
                    style.setIndention(m_StyleConstantValues.get(pCellStyle.getIndention()));

                if (pCellStyle.getLeftBorderColor() != null)
                    style.setLeftBorderColor(IndexedColors.valueOf(pCellStyle.getLeftBorderColor()).getIndex());

                if (pCellStyle.isLocked())
                    style.setLocked(pCellStyle.isLocked());

                if (pCellStyle.getRightBorderColor() != null)
                    style.setRightBorderColor(
                            IndexedColors.valueOf(pCellStyle.getRightBorderColor()).getIndex());

                if (pCellStyle.getRotation() != 0)
                    style.setRotation(pCellStyle.getRotation());

                if (pCellStyle.getTopBorderColor() != null)
                    style.setTopBorderColor(IndexedColors.valueOf(pCellStyle.getTopBorderColor()).getIndex());

                if (pCellStyle.getVerticalAlignment() != null)
                    style.setVerticalAlignment(m_StyleConstantValues.get(pCellStyle.getVerticalAlignment()));

                if (pCellStyle.isWrapText())
                    style.setWrapText(pCellStyle.isWrapText());

                c.setCellStyle(style);
                pCellStyle.setCellStyle(style);
            }

        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:br.com.objectos.way.io.WayIO.java

License:Apache License

static Workbook workbookOf(Cell cell) {
    Sheet s = cell.getSheet();
    return s.getWorkbook();
}

---

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map, boolean colForeach) throws Exception {
    if (sheet.getWorkbook() instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }//from  w  w w  .  j  av  a  2s.  c  o  m
    deleteCell(sheet, map);
    mergedRegionHelper = new MergedRegionHelper(sheet);
    templateSumHandler = new TemplateSumHandler(sheet);
    if (colForeach) {
        colForeach(sheet, map);
    }
    Row row = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            if (row.getCell(i) != null
                    && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {
                setValueForCellByMap(row.getCell(i), map);
            }
        }
    }

    //??
    handlerSumCell(sheet);
}

---

From source file:cn.org.vbn.util.LinkedDropDownLists.java

License:Apache License

/**
 * Called to populate the named areas/regions. The contents of the cells on
 * row one will be used to populate the first drop down list. The contents of
 * the cells on rows two, three and four will be used to populate the second
 * drop down list, just which row will be determined by the choice the user
 * makes in the first drop down list.//from  w ww . jav a  2 s. c  o m
 *
 * In all cases, the approach is to create a row, create and populate cells
 * with data and then specify a name that identifies those cells. With the
 * exception of the first range, the names that are chosen for each range
 * of cells are quite important. In short, each of the options the user
 * could select in the first drop down list is used as the name for another
 * range of cells. Thus, in this example, the user can select either
 * 'Animal', 'Vegetable' or 'Mineral' in the first drop down and so the
 * sheet contains ranges named 'ANIMAL', 'VEGETABLE' and 'MINERAL'.
 *
 * @param dataSheet An instance of a class that implements the Sheet Sheet
 *        interface (HSSFSheet or XSSFSheet).
 */
private static final void buildDataSheet(Sheet dataSheet) {
    Row row = null;
    Cell cell = null;
    Name name = null;

    // The first row will hold the data for the first validation.
    row = dataSheet.createRow(10);
    cell = row.createCell(0);
    cell.setCellValue("Animal");
    cell = row.createCell(1);
    cell.setCellValue("Vegetable");
    cell = row.createCell(2);
    cell.setCellValue("Mineral");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$11:$C$11");
    name.setNameName("CHOICES");

    // The next three rows will hold the data that will be used to
    // populate the second, or linked, drop down list.
    row = dataSheet.createRow(11);
    cell = row.createCell(0);
    cell.setCellValue("Lion");
    cell = row.createCell(1);
    cell.setCellValue("Tiger");
    cell = row.createCell(2);
    cell.setCellValue("Leopard");
    cell = row.createCell(3);
    cell.setCellValue("Elephant");
    cell = row.createCell(4);
    cell.setCellValue("Eagle");
    cell = row.createCell(5);
    cell.setCellValue("Horse");
    cell = row.createCell(6);
    cell.setCellValue("Zebra");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$12:$G$12");
    name.setNameName("ANIMAL");

    row = dataSheet.createRow(12);
    cell = row.createCell(0);
    cell.setCellValue("Cabbage");
    cell = row.createCell(1);
    cell.setCellValue("Cauliflower");
    cell = row.createCell(2);
    cell.setCellValue("Potato");
    cell = row.createCell(3);
    cell.setCellValue("Onion");
    cell = row.createCell(4);
    cell.setCellValue("Beetroot");
    cell = row.createCell(5);
    cell.setCellValue("Asparagus");
    cell = row.createCell(6);
    cell.setCellValue("Spinach");
    cell = row.createCell(7);
    cell.setCellValue("Chard");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$13:$H$13");
    name.setNameName("VEGETABLE");

    row = dataSheet.createRow(13);
    cell = row.createCell(0);
    cell.setCellValue("Bauxite");
    cell = row.createCell(1);
    cell.setCellValue("Quartz");
    cell = row.createCell(2);
    cell.setCellValue("Feldspar");
    cell = row.createCell(3);
    cell.setCellValue("Shist");
    cell = row.createCell(4);
    cell.setCellValue("Shale");
    cell = row.createCell(5);
    cell.setCellValue("Mica");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$14:$F$14");
    name.setNameName("MINERAL");
}

---

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected void drawLineUnder(Sheet sheet, int row, int colMin, int colMax, short thickness) {
    Row r = sheet.getRow(row);/*from  w w  w .ja  v a2s . c o m*/
    if (r == null)
        r = sheet.createRow(row);
    for (int col = colMin; col <= colMax; col++) {
        Cell c = r.getCell(col);
        if (c == null)
            c = r.createCell(col);
        CellStyle style = styleWithBordersUnder.get((c.getCellStyle().getIndex() << 4) + thickness);
        if (style == null) {
            style = sheet.getWorkbook().createCellStyle();
            style.cloneStyleFrom(c.getCellStyle());
            style.setBorderBottom(thickness);
            styleWithBordersUnder.put((c.getCellStyle().getIndex() << 4) + thickness, style);
        }
        c.setCellStyle(style);

    }
}

---

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected void drawLineAbove(Sheet sheet, int row, int colMin, int colMax, short thickness) {
    Row r = sheet.getRow(row);/*from  ww w  .ja  v a2 s.  c  om*/
    if (r == null)
        r = sheet.createRow(row);
    for (int col = colMin; col <= colMax; col++) {
        Cell c = r.getCell(col);
        if (c == null)
            c = r.createCell(col);
        CellStyle style = styleWithBordersAbove.get(c.getCellStyle().getIndex() << 4 + thickness);
        if (style == null) {
            style = sheet.getWorkbook().createCellStyle();
            style.cloneStyleFrom(c.getCellStyle());
            style.setBorderTop(thickness);
            styleWithBordersAbove.put(c.getCellStyle().getIndex() << 4 + thickness, style);
        }
        c.setCellStyle(style);
    }
}

---

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) {
    ListHashMap<Integer, Integer> col2lens = new ListHashMap<>();
    for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) {
        Row r = sheet.getRow(row);//from w  w  w  .  j  a v a 2s  . c om
        if (r == null || r.getFirstCellNum() < 0)
            continue;
        short maxH = 0;

        for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) {
            Cell c = r.getCell(col);
            if (c == null
                    || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC))
                continue;

            Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex());
            String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue()
                    : "" + c.getNumericCellValue();
            String[] lines = MiscUtils.split(s, "\n");
            int maxLen = 1;
            for (int i = 0; i < lines.length; i++) {
                maxLen = Math.max(lines[i].length(), maxLen);
            }
            if (font.getFontHeightInPoints() < 12) {
                col2lens.add(col, 700
                        + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20);
            }
            maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2));
        }
        if (resizeHeight)
            r.setHeight(maxH);
    }

    for (int col : col2lens.keySet()) {
        List<Integer> lens = col2lens.get(col);
        Collections.sort(lens);
        int len = lens.get(lens.size() - 1);
        if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) {
            len = lens.get(lens.size() - 2);
        }
        sheet.setColumnWidth(col,
                Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500));
    }
}

---

From source file:com.asakusafw.testdriver.excel.ExcelSheetSink.java

License:Apache License

/**
 * Creates a new instance./*from   w  w w .j a v a  2  s. c  o m*/
 * @param definition the data model definition
 * @param sheet target sheet
 * @param maxColumns the count of max columns
 * @throws IllegalArgumentException if some parameters were {@code null}
 */
public ExcelSheetSink(DataModelDefinition<?> definition, Sheet sheet, int maxColumns) {
    if (definition == null) {
        throw new IllegalArgumentException("definition must not be null"); //$NON-NLS-1$
    }
    if (sheet == null) {
        throw new IllegalArgumentException("sheet must not be null"); //$NON-NLS-1$
    }
    this.sheet = sheet;
    this.info = new WorkbookInfo(sheet.getWorkbook());
    this.engine = new Engine(definition, info, maxColumns);
    engine.createHeaderRow(sheet.createRow(0));
    this.rowIndex = 1;
}

---

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateWriter.java

License:Apache License

/**
 * Handles copying the formula for the provided sheet and active row
 * /*w  w w. j  av  a2 s. co m*/
 * @param sheet
 *            the provided sheet
 * @param targetCell
 *            the target cell to copy the formula
 * @param activeRow
 *            the active row
 * @param column
 *            the TemplateColumn to be used for
 */
private void copyFormula(Sheet sheet, Cell targetCell, Row activeRow, TemplateColumn column) {
    if (targetCell == null || sheet == null || targetCell.getCellType() != Cell.CELL_TYPE_FORMULA) {
        return;
    }

    String formula = column.getCellFormula();

    int shiftRows = activeRow.getRowNum() - 1;
    int shiftCols = 0;

    XSSFEvaluationWorkbook workbookWrapper = XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());

    Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL,
            sheet.getWorkbook().getSheetIndex(sheet));

    for (Ptg ptg : ptgs) {
        if (ptg instanceof RefPtgBase) {
            RefPtgBase ref = (RefPtgBase) ptg;
            if (ref.isColRelative()) {
                ref.setColumn(ref.getColumn() + shiftCols);
            }
            if (ref.isRowRelative()) {
                ref.setRow(ref.getRow() + shiftRows);
            }
        } else if (ptg instanceof AreaPtg) {
            AreaPtg ref = (AreaPtg) ptg;
            if (ref.isFirstColRelative()) {
                ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
            }
            if (ref.isLastColRelative()) {
                ref.setLastColumn(ref.getLastColumn() + shiftCols);
            }
            if (ref.isFirstRowRelative()) {
                ref.setFirstRow(ref.getFirstRow() + shiftRows);
            }
            if (ref.isLastRowRelative()) {
                ref.setLastRow(ref.getLastRow() + shiftRows);
            }
        }
    }

    formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
    targetCell.setCellFormula(formula);
    log.debug("Set Formula for row " + activeRow.getRowNum() + " : " + formula);
    targetCell.setAsActiveCell();
}

---

From source file:com.centurylink.mdw.common.service.JsonExport.java

License:Apache License

private CellStyle getDateCellStyle(Sheet sheet) {
    if (dateCellStyle == null) {
        dateCellStyle = sheet.getWorkbook().createCellStyle();
        CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy hh:mm:ss")); // TODO flexible
    }//  w ww  . j a va  2  s  . co  m
    return dateCellStyle;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• »