Example usage for org.apache.poi.ss.usermodel Cell getRow

Introduction

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

Prototype

Row getRow();

Source Link

Document

Returns the Row this cell belongs to

Usage

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ??//from   ww  w .j  av  a2  s.c  o m
 * @param cell
 * @param name
 * @param mergedRegionHelper 
 * @return
 */
private Object[] getAllDataColumns(Cell cell, String name, MergedRegionHelper mergedRegionHelper) {
    List<ExcelForEachParams> columns = new ArrayList<ExcelForEachParams>();
    cell.setCellValue("");
    columns.add(getExcelTemplateParams(name.replace(END_STR, EMPTY), cell, mergedRegionHelper));
    int rowspan = 1, colspan = 1;
    if (!name.contains(END_STR)) {
        int index = cell.getColumnIndex();
        //?col 
        int startIndex = cell.getColumnIndex();
        Row row = cell.getRow();
        while (true) {
            int colSpan = columns.get(columns.size() - 1) != null ? columns.get(columns.size() - 1).getColspan()
                    : 1;
            index += colSpan;
            for (int i = 1; i < colSpan; i++) {
                //??,???,,?
                columns.add(null);
                continue;
            }
            cell = row.getCell(index);
            //???
            if (cell == null) {
                //?,
                columns.add(null);
                continue;
            }
            String cellStringString;
            try {//?? ?
                cellStringString = cell.getStringCellValue();
                if (StringUtils.isBlank(cellStringString) && colspan + startIndex <= index) {
                    throw new ExcelExportException("for each ,?");
                } else if (StringUtils.isBlank(cellStringString) && colspan + startIndex > index) {
                    //?,,?,?,?
                    columns.add(new ExcelForEachParams(null, cell.getCellStyle(), (short) 0));
                    continue;
                }
            } catch (Exception e) {
                throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR, e);
            }
            //?cell 
            cell.setCellValue("");
            if (cellStringString.contains(END_STR)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(END_STR, EMPTY), cell,
                        mergedRegionHelper));
                break;
            } else if (cellStringString.contains(WRAP)) {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
                //??,??
                colspan = index - startIndex + 1;
                index = startIndex - 1;
                row = row.getSheet().getRow(row.getRowNum() + 1);
                rowspan++;
            } else {
                columns.add(getExcelTemplateParams(cellStringString.replace(WRAP, EMPTY), cell,
                        mergedRegionHelper));
            }
        }
    }
    colspan = 0;
    for (int i = 0; i < columns.size(); i++) {
        colspan += columns.get(i) != null ? columns.get(i).getColspan() : 0;
    }
    colspan = colspan / rowspan;
    return new Object[] { rowspan, colspan, columns };
}

---

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ???/* w  ww  . j  a v a2  s . co  m*/
 * @param name
 * @param cell
 * @param mergedRegionHelper 
 * @return
 */
private ExcelForEachParams getExcelTemplateParams(String name, Cell cell,
        MergedRegionHelper mergedRegionHelper) {
    name = name.trim();
    ExcelForEachParams params = new ExcelForEachParams(name, cell.getCellStyle(), cell.getRow().getHeight());
    //??
    if (name.startsWith(CONST) && name.endsWith(CONST)) {
        params.setName(null);
        params.setConstValue(name.substring(1, name.length() - 1));
    }
    //?
    if (NULL.equals(name)) {
        params.setName(null);
        params.setConstValue(EMPTY);
    }
    //????
    if (mergedRegionHelper.isMergedRegion(cell.getRowIndex() + 1, cell.getColumnIndex())) {
        Integer[] colAndrow = mergedRegionHelper.getRowAndColSpan(cell.getRowIndex() + 1,
                cell.getColumnIndex());
        params.setRowspan(colAndrow[0]);
        params.setColspan(colAndrow[1]);
    }
    params.setNeedSum(templateSumHanlder.isSumKey(params.getName()));
    return params;
}

---

From source file:com.hauldata.dbpa.file.book.XlsxTargetSheet.java

License:Apache License

private void adjustAdjacentCellStyle(Cell cell, Styles styles, RowPosition rowPosition,
        ColumnPosition columnPosition, Styles leftStyles, Styles aboveStyles) {

    if (styles == null) {
        return;//  w  w  w  .j  a  v  a 2  s.  com
    }

    if ((leftStyles != null) && (columnPosition != ColumnPosition.LEFT)
            && (columnPosition != ColumnPosition.SINGLE) && !leftStyles.rightBorder.equals(styles.leftBorder)) {

        leftStyles.rightBorder = styles.leftBorder;

        Cell leftCell = cell.getRow().getCell(cell.getColumnIndex() - 1);

        leftCell.setCellStyle(composeCellStyle(leftCell, leftStyles));
    }

    if ((aboveStyles != null) && (rowPosition != RowPosition.HEADER) && (rowPosition != RowPosition.TOP)
            && !aboveStyles.bottomBorder.equals(styles.topBorder)) {

        aboveStyles.bottomBorder = styles.topBorder;

        Cell aboveCell = sheet.getRow(cell.getRowIndex() - 1).getCell(cell.getColumnIndex());

        aboveCell.setCellStyle(composeCellStyle(aboveCell, aboveStyles));
    }
}

---

From source file:com.hust.zsuper.DealWithPatent.Utils.java

License:Open Source License

public static ExcelType excelTypeToMySql(Cell cell) {
    final FormulaEvaluator evaluator = cell.getRow().getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator();//from ww w  . ja v  a2  s. c om
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_STRING:
        return ExcelType.STRING;
    case Cell.CELL_TYPE_BOOLEAN:
        return ExcelType.BOOLEAN;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return ExcelType.DATE;
        } else {
            return ExcelType.NUMERIC;
        }
    default:
        return null;
    }
}

---

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Put the next row in the dvd row given in parameter.
 * Return SCAN_COMPLETED if there is no more row in the spreadsheet, or GOT_ROW if a row was successfully put in the dvd row.
 * /*from  w w  w.  j  a  va 2 s .co m*/
 * Uses the attribute currentRow to save the previous row fetched.
 * 
 * @param sheet
 * @param dvdr
 * @param numberOfLogicalColumnsInvolved
 * @param columnIndexes
 * @return SCAN_COMPLETED or GOT_ROW
 * @throws SQLException
 */
private int createNextRow(Sheet sheet, DataValueDescriptor[] dvdr) {
    boolean gotData = false;

    /*
     * Find the next row to return.
     * 
     * currentRow should currently point to the last row returned.
     * If that's null, then start from first row.
     * Else, search for the next non-empty row (until we hit the end of the prescribed range).
     */
    if (currentRow == null)
        currentRow = sheet.getRow(firstRowIndex + (firstRowIsMetaData ? 1 : 0));
    else {
        int nextRowIndex = currentRow.getRowNum() + 1;
        currentRow = null;

        if (stopScanOnFirstEmptyRow) {
            currentRow = sheet.getRow(nextRowIndex);
        } else {
            while (currentRow == null && nextRowIndex <= lastRowIndex) {
                currentRow = sheet.getRow(nextRowIndex);
                nextRowIndex++;
            }
        }
    }

    /*
     * If we've run out of spreadsheet (currentRow == null) or gone out of the prescribed range,
     * then scan complete - return that.
     */
    if (currentRow == null || currentRow.getRowNum() > lastRowIndex) {
        return SCAN_COMPLETED;
    }

    /*
     * Get the offset of the first column in the spreadsheet.
     * Note: this is used when iterating below, so that we can correctly relate 
     * the actual column in the spreadsheet to the correct 'column' in the 
     * DataValueDescriptor [] representing the row.
     */
    int columnOffset = firstColumnIndex;

    //Figure out how many columns there are
    int numberOfColumns = lastColumnIndex - firstColumnIndex + 1;

    for (int i = 0; i < numberOfColumns; i++) {
        /*
         * Note: i is used to refer to the index of the DataValueDescriptor which represents
         * the actual spreadsheet column (at i + columnOffset) in the DataValueDescriptor[] 
         * representing this row. 
         */

        Cell cell = currentRow.getCell(i + columnOffset);

        if (cell == null) {
            dvdr[i].setToNull();
        } else {
            try {
                int cellValueType = cell.getCellType();

                if (cellValueType == Cell.CELL_TYPE_FORMULA)
                    cellValueType = cell.getCachedFormulaResultType();

                switch (cellValueType) {

                case Cell.CELL_TYPE_STRING:
                    dvdr[i].setValue(cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell))
                        dvdr[i].setValue(new java.sql.Date(cell.getDateCellValue().getTime()));
                    else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        dvdr[i].setValue(cell.getStringCellValue());
                    }
                    break;

                case Cell.CELL_TYPE_BOOLEAN:
                    dvdr[i].setValue(cell.getBooleanCellValue());
                    break;

                default:
                    dvdr[i].setToNull();
                    break;
                }

                //If a cell has data that is not null - then flag that we actually have data to return
                if (!dvdr[i].isNull())
                    gotData = true;

            } catch (Exception e) {
                dvdr[i].setToNull();
                logger.logWarning(GDBMessages.DSWRAPPER_GEXCEL_MAP_LT_ERROR, "Excel cell [spreadsheet "
                        + sheet.getSheetName() + "; row " + cell.getRow().getRowNum() + "; column "
                        + cell.getColumnIndex() + "; value " + cell
                        + "] could not be mapped into the logical table because of the column logical type: "
                        + e);
            }
        }
    }

    if (!gotData && stopScanOnFirstEmptyRow) {
        logger.logInfo(
                "Ending GExcel table scan on first empty row (as no row limit was specified in the ending cell config constraint)");
        return SCAN_COMPLETED;
    }

    return GOT_ROW;
}

---

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * foreach?// w w  w.j av  a  2  s .c  o  m
 * @param cell 
 * @param map
 * @param oldString
 * @throws Exception 
 */
private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = !name.contains(PoiElUtil.FOREACH_NOT_CREATE);
    boolean isShift = name.contains(PoiElUtil.FOREACH_AND_SHIFT);
    name = name.replace(PoiElUtil.FOREACH_NOT_CREATE, PoiElUtil.EMPTY)
            .replace(PoiElUtil.FOREACH_AND_SHIFT, PoiElUtil.EMPTY).replace(PoiElUtil.FOREACH, PoiElUtil.EMPTY)
            .replace(PoiElUtil.START_STR, PoiElUtil.EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) com.qihang.winter.poi.util.PoiPublicUtil.getParamsValue(keys[0], map);
    List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns = getAllDataColumns(cell,
            name.replace(keys[0], PoiElUtil.EMPTY));
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    Row row;
    int rowIndex = cell.getRow().getRowNum() + 1;
    //??
    if (its.hasNext()) {
        Object t = its.next();
        cell.getRow().setHeight(columns.get(0).getHeight());
        setForEeachCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map);
    }
    if (isShift) {
        cell.getRow().getSheet().shiftRows(cell.getRowIndex() + 1, cell.getRow().getSheet().getLastRowNum(),
                datas.size() - 1, true, true);
    }
    while (its.hasNext()) {
        Object t = its.next();
        if (isCreate) {
            row = cell.getRow().getSheet().createRow(rowIndex++);
        } else {
            row = cell.getRow().getSheet().getRow(rowIndex++);
            if (row == null) {
                row = cell.getRow().getSheet().createRow(rowIndex - 1);
            }
        }
        row.setHeight(columns.get(0).getHeight());
        setForEeachCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map);
    }
}

---

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ??//from   www  . jav a  2s. c om
 * @param cell
 * @param name
 * @return
 */
private List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> getAllDataColumns(Cell cell,
        String name) {
    List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams>();
    cell.setCellValue("");
    if (name.contains(PoiElUtil.END_STR)) {
        columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(
                name.replace(PoiElUtil.END_STR, PoiElUtil.EMPTY).trim(), cell.getCellStyle(),
                cell.getRow().getHeight()));
        return columns;
    }
    columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(name.trim(),
            cell.getCellStyle(), cell.getRow().getHeight()));
    int index = cell.getColumnIndex();
    Cell tempCell;
    while (true) {
        tempCell = cell.getRow().getCell(++index);
        if (tempCell == null) {
            break;
        }
        String cellStringString;
        try {//?,??,?
            cellStringString = tempCell.getStringCellValue();
            if (StringUtils.isBlank(cellStringString)) {
                break;
            }
        } catch (Exception e) {
            throw new ExcelExportException("for each ,?");
        }
        //?cell 
        tempCell.setCellValue("");
        if (cellStringString.contains(PoiElUtil.END_STR)) {
            columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(
                    cellStringString.trim().replace(PoiElUtil.END_STR, ""), tempCell.getCellStyle(),
                    tempCell.getRow().getHeight()));
            break;
        } else {
            if (cellStringString.trim().contains(teplateParams.getTempParams())) {
                columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(
                        cellStringString.trim(), tempCell.getCellStyle(), tempCell.getRow().getHeight()));
            } else {
                //?
                break;
            }
        }

    }
    return columns;
}

---

From source file:com.squid.core.poi.ExcelWriter.java

License:Open Source License

private void writeNext(final Object[] nextLine, final boolean isHeader, String[] columnsFormat,
        int[] cellsType) {
    Row row = getRow();//from w ww  .  jav a2s  .  c o  m
    for (int iCell = 0; iCell < nextLine.length; iCell++) {
        Cell cell = row.createCell(iCell); // Create cell
        if (isHeader) { // Apply header and footer style
            cell.getRow().setHeight((short) 500);
        }

        Object value = nextLine[iCell];
        String format = "General";
        if (columnsFormat != null && columnsFormat.length > iCell && columnsFormat[iCell] != null) {
            cell.setCellStyle(getStyle(columnsFormat[iCell]));
        } else {
            cell.setCellStyle(getStyle(format));
        }

        // Guess cell data type
        if (cellsType != null && cellsType.length > iCell) {
            cell.setCellType(cellsType[iCell]);
        } else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }

        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Number) {
            String durationFormat = "";
            if (columnsFormat != null && columnsFormat.length > iCell && columnsFormat[iCell] != null) {
                durationFormat = columnsFormat[iCell];
            }
            if (DurationFormatUtils.isDurationFormatPattern(durationFormat)) {
                cell.setCellValue(DurationFormatUtils.format(durationFormat, ((Number) value).doubleValue()));
            } else {
                cell.setCellValue(((Number) value).doubleValue());
            }
        }

    }
}

---

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.DataExportIntroSheetGenerator.java

License:Open Source License

/**
 * Creates a section of the summary, containing links to one of the types of sheets 
 * /*  www .  j  a  v a 2s  .  co  m*/
 * @param sheetContexts list of sheets to link to. Only contains one type of {@link NamedExpression}
 * @param startAt the row to start at in the introduction sheet
 * @param sectionHeader the header of the section
 * 
 * @return the row number that was reached while inserting the links
 */
private int createSummarySection(List<SheetContext> sheetContexts, int startAt, String sectionHeader) {
    int rowNr = startAt;
    Map<IteraExcelStyle, CellStyle> styles = wbContext.getStyles();
    Workbook workbook = wbContext.getWb();
    CreationHelper createHelper = workbook.getCreationHelper();

    // header
    Sheet introSheet = getIntroductionSheet();
    Cell headerCell = introSheet.createRow(rowNr++).createCell(SUMMARY_COL);
    headerCell.setCellValue(sectionHeader);
    headerCell.setCellStyle(styles.get(IteraExcelStyle.HEADER));
    headerCell.getRow().createCell(SUMMARY_COL + 1).setCellStyle(styles.get(IteraExcelStyle.HEADER));

    for (SheetContext sheetContext : sheetContexts) {
        String sheetName = sheetContext.getSheetName();
        String extraInfo = sheetContext.getExpression().getName();
        // name is empty, we assume it's a relationship, we need to get the name of the relationship ends
        if (StringUtils.isEmpty(extraInfo)) {
            NamedExpression expression = sheetContext.getExpression();
            if (expression instanceof RelationshipExpression) {
                extraInfo = createRelationshipExtrainfo((RelationshipExpression) expression);
            }
        }

        Row entryRow = introSheet.createRow(rowNr++);
        Cell hyperlinkCell = entryRow.createCell(SUMMARY_COL);
        hyperlinkCell.setCellValue(sheetName);
        entryRow.createCell(SUMMARY_COL + 1).setCellValue(extraInfo);

        Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
        link.setAddress("'" + sheetName + "'!A1");
        hyperlinkCell.setHyperlink(link);
        hyperlinkCell.setCellStyle(styles.get(IteraExcelStyle.HYPERLINK));
    }

    //spacing between sections
    introSheet.createRow(rowNr++);

    return rowNr;
}

---

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

private void writeCellValue(Cell cell, Object value, int dataColumnIndex, int dataRowIndex) {
    if (value instanceof String) {
        String s = (String) value;
        boolean isPlainValue = true;
        if (isToWriteAsComment(dataColumnIndex)) {
            // if this schema data column is dedicated as comment 
            isPlainValue = false;/*from   w  w w .  jav  a2s  . c o  m*/
            if (firstRowIsHeader == false || dataRowIndex > 0) {
                // avoid set comment for the header line
                setCellComment(cell, s);
            }
        }
        if (isToWriteAsLink(dataColumnIndex)) {
            // if this schema data column is dedicated as hyper link
            if (firstRowIsHeader == false || dataRowIndex > 0) {
                // avoid set hyper-links for the header line
                setCellHyperLink(cell, s);
                isPlainValue = false;
            }
        }
        if (isPlainValue) {
            if (s.startsWith("=")) {
                int rowNum = cell.getRow().getRowNum();
                cell.setCellFormula(getFormular(s, rowNum));
                cell.setCellType(CellType.FORMULA);
            } else {
                cell.setCellValue(s);
                cell.setCellType(CellType.STRING);
            }
        }
    } else if (value instanceof Integer) {
        cell.setCellValue((Integer) value);
        cell.setCellType(CellType.NUMERIC);
    } else if (value instanceof Boolean) {
        cell.setCellValue((Boolean) value);
        cell.setCellType(CellType.BOOLEAN);
    } else if (value instanceof Long) {
        cell.setCellValue((Long) value);
        cell.setCellType(CellType.NUMERIC);
    } else if (value instanceof BigInteger) {
        cell.setCellValue(((BigInteger) value).longValue());
        cell.setCellType(CellType.NUMERIC);
    } else if (value instanceof BigDecimal) {
        cell.setCellValue(((BigDecimal) value).doubleValue());
        cell.setCellType(CellType.NUMERIC);
    } else if (value instanceof Double) {
        cell.setCellValue((Double) value);
        cell.setCellType(CellType.NUMERIC);
    } else if (value instanceof Float) {
        cell.setCellValue((Float) value);
        cell.setCellType(CellType.NUMERIC);
    } else if (value instanceof Short) {
        cell.setCellValue((Short) value);
        cell.setCellType(CellType.NUMERIC);
    } else if (value instanceof Number) {
        cell.setCellValue(Double.valueOf(((Number) value).doubleValue()));
        cell.setCellType(CellType.NUMERIC);
    } else if (value instanceof Date) {
        if (writeZeroDateAsNull && GenericDateUtil.isZeroDate((Date) value)) {
            cell.setCellType(CellType.BLANK);
        } else {
            cell.setCellValue((Date) value);
            cell.setCellType(CellType.NUMERIC);
        }
    } else if (value != null) {
        cell.setCellValue(value.toString());
        cell.setCellType(CellType.STRING);
    } else if (writeNullValues && value == null) {
        cell.setCellType(CellType.BLANK);
    }
    if (isDataRow(dataRowIndex)) {
        setupStyle(cell, dataRowIndex);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• »