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

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. 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:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetFindCell.java

License:Open Source License

public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException {
    cfSpreadSheetData spreadsheet = (cfSpreadSheetData) parameters.get(1);
    Pattern pattern = Pattern.compile(parameters.get(0).getString());

    cfArrayData arr = cfArrayData.createArray(1);

    Iterator<Row> rowIT = spreadsheet.getActiveSheet().rowIterator();
    while (rowIT.hasNext()) {
        Row row = rowIT.next();//from  w ww . j a  v a  2s  . c om

        Iterator<Cell> cellIT = row.cellIterator();
        while (cellIT.hasNext()) {
            Cell cell = cellIT.next();
            String cellValue = null;

            if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                cellValue = cell.getStringCellValue();
            else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                cellValue = String.valueOf(cell.getNumericCellValue());
            else
                cellValue = cell.toString();

            if (pattern.matcher(cellValue).find()) {
                cfStructData s = new cfStructData();
                s.setData("row", new cfNumberData(cell.getRowIndex() + 1));
                s.setData("column", new cfNumberData(cell.getColumnIndex() + 1));
                s.setData("value", new cfStringData(cellValue));
                arr.addElement(s);
            }
        }
    }

    return arr;
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellComment.java

License:Open Source License

public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException {
    Collections.reverse(parameters);

    if (parameters.size() == 2) {
        throwException(_session, "please specify both a row and a column");
    }//ww w. ja va2  s.c  o m

    cfSpreadSheetData spreadsheet = (cfSpreadSheetData) parameters.get(0);
    Sheet sheet = spreadsheet.getActiveSheet();

    if (parameters.size() == 3) {
        int rowNo = parameters.get(1).getInt() - 1;
        int columnNo = parameters.get(0).getInt() - 1;

        if (rowNo < 0)
            throwException(_session, "row must be 1 or greater (" + rowNo + ")");
        if (columnNo < 0)
            throwException(_session, "column must be 1 or greater (" + columnNo + ")");

        cfStructData sd = new cfStructData();

        Row row = sheet.getRow(rowNo);
        if (row != null) {
            Cell cell = row.getCell(columnNo);
            if (cell != null) {
                Comment comment = cell.getCellComment();
                if (comment != null) {
                    sd.setData("column", new cfNumberData(columnNo));
                    sd.setData("row", new cfNumberData(rowNo));
                    sd.setData("author", new cfStringData(comment.getAuthor()));
                    sd.setData("comment", new cfStringData(comment.getString().getString()));
                }
            }
        }

        return sd;
    } else {
        cfArrayData arr = cfArrayData.createArray(1);

        Iterator<Row> rowIT = sheet.rowIterator();
        while (rowIT.hasNext()) {
            Row row = rowIT.next();

            Iterator<Cell> cellIT = row.cellIterator();
            while (cellIT.hasNext()) {
                Cell cell = cellIT.next();
                Comment comment = cell.getCellComment();
                if (comment != null) {
                    cfStructData sd = new cfStructData();
                    sd.setData("column", new cfNumberData(cell.getColumnIndex() + 1));
                    sd.setData("row", new cfNumberData(row.getRowNum() + 1));
                    sd.setData("author", new cfStringData(comment.getAuthor()));
                    sd.setData("comment", new cfStringData(comment.getString().getString()));
                    arr.addElement(sd);
                }
            }
        }

        return arr;
    }

}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellFormula.java

License:Open Source License

private cfData getAllFormulaForSheet(cfSession _session, cfSpreadSheetData spreadsheet)
        throws cfmRunTimeException {
    cfArrayData array = cfArrayListData.createArray(1);

    Iterator<Row> rowIt = spreadsheet.getActiveSheet().rowIterator();
    while (rowIt.hasNext()) {
        Row row = rowIt.next();/*from w  ww.j a  v a2  s.c o  m*/

        Iterator<Cell> cellIt = row.cellIterator();
        while (cellIt.hasNext()) {
            Cell cell = cellIt.next();

            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                cfStructData s = new cfStructData();
                s.setData("formula", new cfStringData(cell.getCellFormula()));
                s.setData("row", new cfNumberData(row.getRowNum() + 1));
                s.setData("column", new cfNumberData(cell.getColumnIndex() + 1));
                array.addElement(s);
            }
        }
    }

    return array;
}

From source file:org.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./*from  w ww.  j a va 2  s .  c om*/
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");
    String delimiterParam = req.getParameter(PARAM_REQ_DELIMITER);
    CSVStrategy reqCSVstrategy = null;
    if (delimiterParam != null && !delimiterParam.isEmpty()) {
        reqCSVstrategy = new CSVStrategy(delimiterParam.charAt(0), '"', CSVStrategy.COMMENTS_DISABLED);
    }
    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, reqCSVstrategy != null ? reqCSVstrategy : getCsvStrategy());
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:org.apache.any23.plugin.officescraper.ExcelExtractor.java

License:Apache License

private URI getCellURI(URI rowURI, Cell cell) {
    return RDFUtils.uri(rowURI + String.format("/%d/", cell.getColumnIndex()));
}

From source file:org.apache.any23.plugin.officescraper.XSSFWorkbookTest.java

License:Apache License

private void verifyResource(String resource) throws IOException {
    final InputStream document = this.getClass().getResourceAsStream(resource);
    final Workbook wb;
    if (resource.endsWith(".xlsx")) {
        wb = new XSSFWorkbook(document);
    } else if (resource.endsWith("xls")) {
        wb = new HSSFWorkbook(document);
    } else {/*from  w ww  .  ja  va  2  s  . com*/
        throw new IllegalArgumentException("Unsupported extension for resource " + resource);
    }
    Assert.assertEquals(2, wb.getNumberOfSheets());
    Sheet sheet;
    for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
        sheet = wb.getSheetAt(sheetIndex);
        int rowcount = 0;
        for (Row row : sheet) {
            rowcount++;
            int cellcount = 0;
            for (Cell cell : row) {
                cellcount++;
                logger.debug(String.format("cell [%d, %d]: %s", cell.getRowIndex(), cell.getColumnIndex(),
                        cell.getStringCellValue()));
                verifyContent(sheetIndex, cell.getRowIndex(), cell.getColumnIndex(), cell.getStringCellValue());
            }
            Assert.assertEquals(3, cellcount);
        }
        Assert.assertEquals(3, rowcount);
    }
}

From source file:org.apache.metamodel.excel.ExcelUtils.java

License:Apache License

public static String getCellValue(Workbook wb, Cell cell) {
    if (cell == null) {
        return null;
    }/*from w  w  w  .  j a  v a  2 s.  c o m*/

    final String cellCoordinate = "(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")";

    final String result;

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        result = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        String errorResult;
        try {
            byte errorCode = cell.getErrorCellValue();
            FormulaError formulaError = FormulaError.forInt(errorCode);
            errorResult = formulaError.getString();
        } catch (RuntimeException e) {
            logger.debug("Getting error code for {} failed!: {}", cellCoordinate, e.getMessage());
            if (cell instanceof XSSFCell) {
                // hack to get error string, which is available
                String value = ((XSSFCell) cell).getErrorCellString();
                errorResult = value;
            } else {
                logger.error("Couldn't handle unexpected error scenario in cell: " + cellCoordinate, e);
                throw e;
            }
        }
        result = errorResult;
        break;
    case Cell.CELL_TYPE_FORMULA:
        // result = cell.getCellFormula();
        result = getFormulaCellValue(wb, cell);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            if (date == null) {
                result = null;
            } else {
                result = DateUtils.createDateFormat().format(date);
            }
        } else {
            // TODO: Consider not formatting it, but simple using
            // Double.toString(...)
            result = _numberFormat.format(cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        result = cell.getRichStringCellValue().getString();
        break;
    default:
        throw new IllegalStateException("Unknown cell type: " + cell.getCellType());
    }

    logger.debug("cell {} resolved to value: {}", cellCoordinate, result);

    return result;
}

From source file:org.apache.metamodel.excel.ExcelUtils.java

License:Apache License

private static String getFormulaCellValue(Workbook wb, Cell cell) {
    // first try with a cached/precalculated value
    try {/*w w  w .  j a va  2s .  c  o m*/
        double numericCellValue = cell.getNumericCellValue();
        // TODO: Consider not formatting it, but simple using
        // Double.toString(...)
        return _numberFormat.format(numericCellValue);
    } catch (Exception e) {
        if (logger.isInfoEnabled()) {
            logger.info("Failed to fetch cached/precalculated formula value of cell: " + cell, e);
        }
    }

    // evaluate cell first, if possible
    try {
        if (logger.isInfoEnabled()) {
            logger.info("cell({},{}) is a formula. Attempting to evaluate: {}",
                    new Object[] { cell.getRowIndex(), cell.getColumnIndex(), cell.getCellFormula() });
        }

        final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        // calculates the formula and puts it's value back into the cell
        final Cell evaluatedCell = evaluator.evaluateInCell(cell);

        return getCellValue(wb, evaluatedCell);
    } catch (RuntimeException e) {
        logger.warn("Exception occurred while evaluating formula at position ({},{}): {}",
                new Object[] { cell.getRowIndex(), cell.getColumnIndex(), e.getMessage() });
        // Some exceptions we simply log - result will be then be the
        // actual formula
        if (e instanceof FormulaParseException) {
            logger.error("Parse exception occurred while evaluating cell formula: " + cell, e);
        } else if (e instanceof IllegalArgumentException) {
            logger.error("Illegal formula argument occurred while evaluating cell formula: " + cell, e);
        } else {
            logger.error("Unexpected exception occurred while evaluating cell formula: " + cell, e);
        }
    }

    // last resort: return the string formula
    return cell.getCellFormula();
}

From source file:org.bbreak.excella.core.handler.DebugErrorHandler.java

License:Open Source License

/**
 * ?/*  w w  w  .j a v  a2s. c  o m*/
 * 
 * @param workbook 
 * @param errorCell
 * @param exception
 */
protected void markupErrorCell(Workbook workbook, ParseException exception) {
    Cell errorCell = exception.getCell();
    if (errorCell == null) {
        return;
    }
    // ????
    workbook.setActiveSheet(workbook.getSheetIndex(errorCell.getSheet()));
    errorCell.setAsActiveCell();

    if (workbook instanceof XSSFWorkbook) {
        XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;

        CellStyle errorCellStyle = xssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // TODO:???????????????
        // XSSFComment xssfComment = ((XSSFSheet)sheet).createComment();
        // xssfComment.setRow( errorCell.getRowIndex());
        // xssfComment.setColumn( (short)errorCell.getColumnIndex());
        // XSSFRichTextString string = new XSSFRichTextString( ex.getMessage());
        // xssfComment.setString( ex.getMessage());
    } else {
        HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
        int sheetNum = hssfWorkbook.getNumberOfSheets();
        for (int cnt = 0; cnt < sheetNum; cnt++) {
            hssfWorkbook.getSheetAt(cnt).setSelected(false);
        }

        // ?
        CellStyle errorCellStyle = hssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // ?
        short commentColFrom = (short) (errorCell.getColumnIndex() + 1);
        short commentColTo = (short) (errorCell.getColumnIndex() + ERROR_COMENT_COL_SIZE);
        int commentRowFrom = errorCell.getRowIndex();
        int commentRowTo = errorCell.getRowIndex() + ERROR_COMENT_ROW_SIZE;

        HSSFSheet hssfSheet = (HSSFSheet) errorCell.getSheet();
        HSSFPatriarch patr = hssfSheet.createDrawingPatriarch();
        hssfSheet.setSelected(true);
        HSSFComment comment = patr.createComment(
                new HSSFClientAnchor(0, 0, 0, 0, commentColFrom, commentRowFrom, commentColTo, commentRowTo));
        comment.setVisible(true);
        comment.setString(new HSSFRichTextString(createCommentMessage(exception)));
        errorCell.setCellComment(comment);
    }
}

From source file:org.bbreak.excella.core.tag.excel2java.ArraysParser.java

License:Open Source License

/**
 * ?//from  w  w  w. j  a v  a2s.c om
 * 
 * @param sheet 
 * @param tagCell ???
 * @param data BookController?parseBook(), parseSheet()?<BR>
 *              SheetParser?parseSheet?????<BR>
 *              TagParser??????<BR>
 * @return ?
 * @throws ParseException 
 */
@Override
public List<Object[]> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {

    List<Object[]> resultList = new ArrayList<Object[]>();

    // 
    int tagRowIdx = tagCell.getRowIndex();
    // 
    int tagColIdx = tagCell.getColumnIndex();
    // 
    int valueRowFromIdx;
    // 
    int valueRowToIdx = sheet.getLastRowNum();
    // 
    int valueColumnFromIdx;
    // 
    int valueColumnToIdx = 0;

    // 
    boolean valueColumnToFlag = false;

    try {

        Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

        // ?
        valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM,
                DEFAULT_VALUE_ROW_FROM_ADJUST);
        if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM);
        }

        // ?
        valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx);
        if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO);
        }

        // ???
        if (valueRowFromIdx > valueRowToIdx) {
            throw new ParseException(tagCell,
                    "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO);
        }

        // ?
        valueColumnFromIdx = TagUtil.adjustValue(tagColIdx, paramDef, PARAM_DATA_CLOMUN_FROM,
                DEFAULT_VALUE_COLUMN_FROM_ADJUST);
        if (valueColumnFromIdx < 0 || valueColumnFromIdx > PoiUtil.getLastColNum(sheet)) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_CLOMUN_FROM);
        }

        // 
        valueColumnToFlag = paramDef.containsKey(PARAM_DATA_CLOMUN_TO);
        if (valueColumnToFlag) {
            // ?
            valueColumnToIdx = tagColIdx + Integer.valueOf(paramDef.get(PARAM_DATA_CLOMUN_TO));
            if (valueColumnToIdx < 0 || valueColumnToIdx > PoiUtil.getLastColNum(sheet)) {
                throw new ParseException(tagCell, "?" + PARAM_DATA_CLOMUN_TO);
            }

            // ???
            if (valueColumnFromIdx > valueColumnToIdx) {
                throw new ParseException(tagCell,
                        "?" + PARAM_DATA_CLOMUN_FROM + "," + PARAM_DATA_CLOMUN_TO);
            }
        }
    } catch (Exception e) {
        if (e instanceof ParseException) {
            throw (ParseException) e;
        } else {
            throw new ParseException(tagCell, e);
        }
    }

    // ??
    for (int rowCnt = valueRowFromIdx; rowCnt <= valueRowToIdx; rowCnt++) {

        List<Object> objList = new ArrayList<Object>();

        Row row = sheet.getRow(rowCnt);
        if (row == null) {
            // ?null??
            continue;
        }
        if (!valueColumnToFlag) {
            // ?????
            // ????
            valueColumnToIdx = row.getLastCellNum() - 1;
        }
        for (int cellCnt = valueColumnFromIdx; cellCnt <= valueColumnToIdx; cellCnt++) {

            Cell cell = row.getCell(cellCnt);
            Object cellValue = PoiUtil.getCellValue(cell);
            objList.add(cellValue);
        }

        resultList.add(objList.toArray());
    }
    return resultList;
}