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

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:mvp.presenter.OpenDataDialogPresenter.java

private void parseFile(String excelFilePath, boolean colHeader) throws IOException {
    if (view.shapeFilePathField.getText().isEmpty()
            || view.shapeFilePathField.getText().equalsIgnoreCase("Enter the shapefile path here")) {
        data.setShpPath("empty");
    }/*from   w ww .j ava  2  s. c  o m*/
    try (FileInputStream inputStream = new FileInputStream(new File(excelFilePath))) {
        GridBase grid = new GridBase(1000, 100);
        ObservableList<String> listHeader = FXCollections.observableArrayList();

        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet firstSheet = workbook.getSheetAt(0);
            data.setRowNumber(firstSheet.getLastRowNum());
            data.setColumnNumber(firstSheet.getRow(0).getLastCellNum());
            Iterator<Row> iterator = firstSheet.iterator();

            ObservableList<ObservableList<SpreadsheetCell>> rows = FXCollections.observableArrayList();
            for (int row = 0; row < grid.getRowCount(); row++) {
                final ObservableList<SpreadsheetCell> list = FXCollections.observableArrayList();
                for (int column = 0; column < grid.getColumnCount(); column++) {
                    list.add(SpreadsheetCellType.STRING.createCell(row, column, 1, 1, ""));
                }
                rows.add(list);
            }

            if (colHeader) {
                if (iterator.hasNext()) {
                    Row headerRow = iterator.next();
                    Iterator<Cell> cellIterator = headerRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        listHeader.add(cell.getStringCellValue());
                    }
                }

                ObservableList<String> variableType = FXCollections.observableArrayList();
                for (int i = 0; i < listHeader.size(); i++) {
                    variableType.add(null);
                }

                while (iterator.hasNext()) {
                    Row nextRow = iterator.next();
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            variableType.set(cell.getColumnIndex(), "String");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1, cell.getStringCellValue()));
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            variableType.set(cell.getColumnIndex(), "Boolean");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1,
                                            String.valueOf(cell.getBooleanCellValue())));
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            variableType.set(cell.getColumnIndex(), "Double");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.DOUBLE.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1, cell.getNumericCellValue()));
                            break;
                        }
                    }
                }

                ObservableList<Variable> variables = FXCollections.observableArrayList();
                for (int i = 0; i < listHeader.size() && i < variableType.size(); i++) {
                    Variable variable = new Variable(listHeader.get(i), variableType.get(i));
                    variables.add(variable);
                }
                data.setVariables(variables);
            } else if (!colHeader) {
                while (iterator.hasNext()) {
                    Row nextRow = iterator.next();
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1, cell.getStringCellValue()));
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1,
                                            String.valueOf(cell.getBooleanCellValue())));
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.DOUBLE.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1, cell.getNumericCellValue()));
                            break;
                        }
                    }
                }
            }
            grid.setRows(rows);
            mwview.drawTable(listHeader, grid);
        }
    }
    view.closeStage();
}

---

From source file:net.ceos.project.poi.annotated.core.CellFormulaHandler.java

License:Apache License

/**
 * Apply a formula value at the Cell.//from   ww w.  j  a v  a2 s.c om
 * 
 * @param configCriteria
 *            the {@link XConfigCriteria} object
 * @param cell
 *            the {@link Cell} to use
 * @throws ElementException
 */
private static boolean toFormula(final XConfigCriteria configCriteria, final Cell cell)
        throws ElementException {
    boolean isFormulaApplied = false;

    if (StringUtils.isNotBlank(configCriteria.getElement().formula())) {
        // calculate position according the propagation type
        int position = PredicateFactory.isPropagationHorizontal.test(configCriteria.getPropagation())
                ? cell.getRowIndex() + 1
                : cell.getColumnIndex();

        // calculate and apply formula
        cell.setCellFormula(CellFormulaConverter.calculateSimpleOrDynamicFormula(configCriteria, position));
        isFormulaApplied = true;
    }

    return isFormulaApplied;
}

---

From source file:net.mcnewfamily.rmcnew.shared.Util.java

License:Open Source License

public static void copyXSSFRow(XSSFRow srcRow, XSSFRow destRow) {
    for (Cell srcCell : srcRow) {
        XSSFCell destCell = destRow.createCell(srcCell.getColumnIndex());
        copyXSSFCell((XSSFCell) srcCell, destCell);
    }/*from  w  w w . j a  v  a  2  s. co m*/
    destRow.setHeight(srcRow.getHeight());
}

---

From source file:net.morphbank.loadexcel.SheetReader.java

License:Open Source License

public Cell[] getRowCells(String sheetName, int rowNum) {
    Sheet sheet = getSheet(sheetName);/*from   w ww.  jav a  2s.co m*/
    Row row = sheet.getRow(rowNum);
    Cell allCellsAtRow[] = new Cell[row.getLastCellNum()];
    for (Cell cell : row) {
        allCellsAtRow[cell.getColumnIndex()] = cell;
    }
    return allCellsAtRow;
}

---

From source file:net.morphbank.mbsvc3.mapsheet.XlsFieldMapper.java

License:Open Source License

public void readHeaders() {

    Row row = views.getRow(0);// w  ww.  j  av a2 s.c o  m
    numFields = views.getRow(0).getLastCellNum();
    if (numFields > 0) {
        headers = new String[numFields];
    } else {
        headers = new String[1];
    }
    for (Cell cell : row) {
        int index = cell.getColumnIndex();
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            headers[index] = cell.getStringCellValue().toLowerCase();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                headers[index] = cell.getDateCellValue().toString();
            } else {
                headers[index] = Integer.toString((int) cell.getNumericCellValue());
            }
        }
    }
    currentLine = 0;
}

---

From source file:net.sf.ahtutils.report.util.DataUtil.java

public static Object getCellValue(Cell cell) {
    Object value = new Object();

    // Prevent a NullPointerException
    if (cell != null) {
        if (cell.getHyperlink() != null) {
            Workbook workbook = new XSSFWorkbook();
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Hyperlink link = cell.getHyperlink();
            String address = link.getAddress();
            if (logger.isTraceEnabled()) {
                logger.trace("Found a Hyperlink to " + cell.getHyperlink().getAddress() + " in cell "
                        + cell.getRowIndex() + "," + cell.getColumnIndex());
            }//from   w w w.jav  a  2s  .  c  o  m
            cell = evaluator.evaluateInCell(cell);
        }
        // Depending on the cell type, the value is read using Apache POI methods

        switch (cell.getCellType()) {

        // String are easy to handle
        case Cell.CELL_TYPE_STRING:
            logger.trace("Found string " + cell.getStringCellValue());
            value = cell.getStringCellValue();
            break;

        // Since date formatted cells are also of the numeric type, this needs to be processed
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                DateFormat df = SimpleDateFormat.getDateInstance();
                logger.trace("Found date " + df.format(date));
                value = date;
            } else {
                logger.trace("Found general number " + cell.getNumericCellValue());
                value = cell.getNumericCellValue();
            }
            break;
        }
    } else {
        logger.trace("Found cell with NULL value");
    }
    return value;
}

---

From source file:net.sf.excelutils.ExcelParser.java

License:Apache License

/**
 * parse the cell/*  ww w. j a v  a  2  s  .  co  m*/
 * 
 * @param context data object
 * @param cell excel cell
 */
public static void parseCell(Object context, Sheet sheet, Row row, Cell cell) {

    String str = cell.getStringCellValue();
    if (null == str || "".equals(str)) {
        return;
    }

    if (str.indexOf(VALUED_DELIM) < 0)
        return;

    boolean bJustExpr = str.length() == (str.length() - str.lastIndexOf(VALUED_DELIM));
    boolean bMerge = "!".equals(str.substring(str.indexOf(VALUED_DELIM) + VALUED_DELIM.length(),
            str.indexOf(VALUED_DELIM) + VALUED_DELIM.length() + 1));

    if (str.indexOf(VALUED_DELIM) < 0)
        return;

    Object value = parseStr(context, str);

    // replace the cell
    if (null != value) {
        if (bJustExpr && "java.lang.Integer".equals(value.getClass().getName())) {
            cell.setCellValue(Double.parseDouble(value.toString()));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } else if (bJustExpr && "java.lang.Double".equals(value.getClass().getName())) {
            cell.setCellValue(((Double) value).doubleValue());
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } else if (bJustExpr && "java.util.Date".equals(value.getClass().getName())) {
            cell.setCellValue((Date) value);
        } else if (bJustExpr && "java.lang.Boolean".equals(value.getClass().getName())) {
            cell.setCellValue(((Boolean) value).booleanValue());
            cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
        } else if (bJustExpr && Number.class.isAssignableFrom(value.getClass())) {
            cell.setCellValue(((Number) (value)).doubleValue());
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } else {
            // cell.setEncoding(Workbook.ENCODING_UTF_16); POI3.2?
            cell.setCellValue(value.toString());
        }
    } else {
        cell.setCellValue("");
    }

    // merge the cell that has a "!" character at the expression
    if (row.getRowNum() - 1 >= sheet.getFirstRowNum() && bMerge) {
        Row lastRow = WorkbookUtils.getRow(row.getRowNum() - 1, sheet);
        Cell lastCell = WorkbookUtils.getCell(lastRow, cell.getColumnIndex());
        boolean canMerge = false;
        if (lastCell.getCellType() == cell.getCellType()) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                canMerge = lastCell.getStringCellValue().equals(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                canMerge = lastCell.getBooleanCellValue() == cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                canMerge = lastCell.getNumericCellValue() == cell.getNumericCellValue();
                break;
            }
        }
        if (canMerge) {
            CellRangeAddress region = new CellRangeAddress(lastRow.getRowNum(), row.getRowNum(),
                    lastCell.getColumnIndex(), cell.getColumnIndex());
            sheet.addMergedRegion(region);
        }
    }

}

---

From source file:net.sf.excelutils.tags.EachTag.java

License:Apache License

public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell)
        throws ExcelException {
    String expr = "";
    String each = curCell.getStringCellValue();

    LOG.debug("EachTag:" + each);

    StringTokenizer st = new StringTokenizer(each, " ");
    String widthstr = "";
    String onstr = "";
    int pos = 0;//  w w  w  .jav  a2s.  c  o m
    while (st.hasMoreTokens()) {
        String str = st.nextToken();
        if (pos == 1) {
            expr = str;
        }
        if (pos == 2 && !"on".equals(str)) {
            widthstr = str;
        }
        if (pos == 3 && !"on".equals(str)) {
            onstr = str;
        }
        if (pos == 4) {
            onstr = str;
        }
        pos++;
    }

    int[] widths = new int[0];
    if (null != widthstr && !"".equals(widthstr)) {
        Object o = ExcelParser.parseStr(context, widthstr);
        if (null != o) {
            String[] s = o.toString().split(",");
            widths = new int[s.length];
            for (int i = 0; i < widths.length; i++) {
                widths[i] = Integer.parseInt(s[i]);
            }
        }
    }

    Object obj = ExcelParser.parseExpr(context, expr);
    if (null == obj)
        return new int[] { 0, 0, 0 };

    // by onstr get the property
    if (!"".equals(onstr)) {
        obj = ExcelParser.parseExpr(context, onstr);
        if (null == obj)
            return new int[] { 0, 0, 0 };
    }

    // iterator properties
    Iterator it = ExcelParser.getIterator(obj);
    if (null == it) {
        if (obj instanceof DynaBean) {
            it = ExcelParser.getIterator(ExcelParser.getBeanProperties(((DynaBean) obj).getDynaClass()));
        } else {
            it = ExcelParser.getIterator(ExcelParser.getBeanProperties(obj.getClass()));
        }
    }
    if (null == it) {
        return new int[] { 0, 0, 0 };
    }

    int index = 0;
    int arrayIndex = 0;
    int eachPos = curCell.getColumnIndex();
    String modelName = expr.substring(ExcelParser.VALUED_DELIM.length(),
            expr.length() - ExcelParser.VALUED_DELIM2.length());

    // restore the obj
    obj = ExcelParser.parseExpr(context, expr);
    while (it.hasNext()) {
        Object o = it.next();
        String property = "";
        if (o instanceof Field) {
            property = ((Field) o).getName();
        } else if (o instanceof Map.Entry) {
            property = ((Map.Entry) o).getKey().toString();
        } else if (o instanceof DynaProperty) {
            property = ((DynaProperty) o).getName();
        } else if (null != o) {
            property = o.toString();
        }

        // test the object is array/list or other
        if (obj.getClass().isArray() || obj instanceof Collection) {
            property = modelName + "[" + arrayIndex++ + "]";
        } else {
            property = modelName + "." + property;
        }

        Object value = ExcelParser.getValue(context, property);
        if (null == value)
            value = "";

        if (ExcelUtils.isCanShowType(value)) {

            // get cell merge count
            int width = 1;
            if (index < widths.length) {
                width = widths[index];
            } else if (1 == widths.length) {
                width = widths[0];
            }

            // get row merged of the curCell
            int rowMerged = 1;
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress r = sheet.getMergedRegion(i);
                if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == curCell.getColumnIndex()
                        && r.getLastColumn() == curCell.getColumnIndex()) {
                    rowMerged = r.getLastRow() - r.getFirstRow() + 1;
                    break;
                }
            }

            Cell cell = WorkbookUtils.getCell(curRow, eachPos);

            // shift the after cell
            if (index > 0) {
                WorkbookUtils.shiftCell(sheet, curRow, cell, 1, rowMerged);
            }
            if (width > 1) {
                Cell nextCell = WorkbookUtils.getCell(curRow, eachPos + 1);
                WorkbookUtils.shiftCell(sheet, curRow, nextCell, width - 1, rowMerged);
            }

            // copy the style of curCell
            for (int rownum = curRow.getRowNum(); rownum < curRow.getRowNum() + rowMerged; rownum++) {
                for (int i = 0; i < width; i++) {
                    Row r = WorkbookUtils.getRow(rownum, sheet);
                    Cell c = WorkbookUtils.getCell(r, eachPos + i);
                    Cell cc = WorkbookUtils.getCell(r, curCell.getColumnIndex());
                    c.setCellStyle(cc.getCellStyle());
                }
            }

            // merge cells
            if (width > 1 || rowMerged > 1) {
                sheet.addMergedRegion(
                        new CellRangeAddress(curRow.getRowNum(), curRow.getRowNum() + rowMerged - 1,
                                cell.getColumnIndex(), cell.getColumnIndex() + width - 1));
            }

            cell.setCellValue("${" + property + "}");
            ExcelParser.parseCell(context, sheet, curRow, cell);

            eachPos += width;
            index++;
        }
    }

    return new int[] { 0, 0, 0 };
}

---

From source file:net.sf.excelutils.WorkbookUtils.java

License:Apache License

public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) {

    if (shift == 0)
        return;//from   w  w w.  j  a  va2  s  .  c o  m

    // get the from & to row
    int fromRow = row.getRowNum();
    int toRow = row.getRowNum() + rowCount - 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() == row.getRowNum()) {
            if (r.getLastRow() > toRow) {
                toRow = r.getLastRow();
            }
            if (r.getFirstRow() < fromRow) {
                fromRow = r.getFirstRow();
            }
        }
    }

    for (int rownum = fromRow; rownum <= toRow; rownum++) {
        Row curRow = WorkbookUtils.getRow(rownum, sheet);
        int lastCellNum = curRow.getLastCellNum();
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);
            Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift);
            toCell.setCellType(fromCell.getCellType());
            toCell.setCellStyle(fromCell.getCellStyle());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                toCell.setCellErrorValue(fromCell.getErrorCellValue());
                break;
            }
            fromCell.setCellValue("");
            fromCell.setCellType(Cell.CELL_TYPE_BLANK);
            // Workbook wb = new Workbook();
            // CellStyle style = wb.createCellStyle();
            // fromCell.setCellStyle(style);
        }

        // process merged region
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);

            List shiftedRegions = new ArrayList();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress r = sheet.getMergedRegion(i);
                if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) {
                    r.setFirstColumn((short) (r.getFirstColumn() + shift));
                    r.setLastColumn((short) (r.getLastColumn() + shift));
                    // have to remove/add it back
                    shiftedRegions.add(r);
                    sheet.removeMergedRegion(i);
                    // we have to back up now since we removed one
                    i = i - 1;
                }
            }

            // readd so it doesn't get shifted again
            Iterator iterator = shiftedRegions.iterator();
            while (iterator.hasNext()) {
                CellRangeAddress region = (CellRangeAddress) iterator.next();
                sheet.addMergedRegion(region);
            }
        }
    }
}

---

From source file:net.unit8.axebomber.parser.CellImpl.java

License:Apache License

public CellImpl(org.apache.poi.ss.usermodel.Cell cell) {
    this.cell = cell;
    setColumnIndex(cell.getColumnIndex());
    setRowIndex(cell.getRowIndex());
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »