List of usage examples for org.apache.poi.ss.usermodel Cell getRowIndex
int getRowIndex();
From source file:net.unit8.axebomber.parser.TableHeader.java
License:Apache License
public TableHeader(Cell labelCell, Object labelPattern) { this.labelPattern = labelPattern; this.labelRowIndex = labelCell.getRowIndex() + 1; this.labelColumnIndex = labelCell.getColumnIndex(); this.sheet = labelCell.getSubstance().getSheet(); Cell beginCell = getCell(labelCell.getColumnIndex(), labelRowIndex); scanColumnLabel(beginCell);// w w w . j ava2 s . co m }
From source file:net.unit8.axebomber.parser.TableHeader.java
License:Apache License
private void scanColumnLabel(Cell beginCell) { int rowIndex = beginCell.getRowIndex(); org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex); String currentValue = null;/* w ww .ja v a2 s. co m*/ for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { Cell cell = getCell(i, rowIndex); if (cell == null) continue; String label = StringUtils.remove(StringUtils.trim(cell.toString()), "\n"); if (!cell.toString().equals("")) { labelColumns.put(label, i); currentValue = label; } if (currentValue != null) columnLabels.put(i, currentValue); } }
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 w w . j a va 2 s.c o m 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.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 {// w ww.j av a 2 s . co m 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 ww . ja 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 ww . ja v a 2s . co 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
/** * ?//from w w w. j a va2s . 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
/** * ?// w w w . j a va2 s . com * * @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; }
From source file:org.bbreak.excella.core.tag.excel2java.ListParser.java
License:Open Source License
/** * ?/*from w ww . jav a 2 s. c o m*/ * * @param sheet * @param tagCell ??? * @param data BookController?parseBook(), parseSheet()?<BR> * SheetParser?parseSheet?????<BR> * TagParser??????<BR> * @return ? * @throws ParseException */ @Override public List<?> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ?? List<Object> results = new ArrayList<Object>(); try { // int tagRowIdx = tagCell.getRowIndex(); int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); int valueColIdx = tagCell.getColumnIndex(); 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); } // Value?? valueColIdx = TagUtil.adjustValue(valueColIdx, paramDef, PARAM_VALUE_COLUMN, DEFAULT_VALUE_COLUMN_ADJUST); if (valueColIdx > PoiUtil.getLastColNum(sheet) || valueColIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_VALUE_COLUMN); } // ? for (int rowCnt = valueRowFromIdx; rowCnt <= valueRowToIdx; rowCnt++) { Row row = sheet.getRow(rowCnt); if (row != null) { Object value = PoiUtil.getCellValue(row.getCell(valueColIdx)); results.add(value); } } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } return results; }
From source file:org.bbreak.excella.core.tag.excel2java.MapParser.java
License:Open Source License
/** * ?/* ww w . java 2s . c om*/ * * @param sheet * @param tagCell ??? * @param data BookController?parseBook(), parseSheet()?<BR> * SheetParser?parseSheet?????<BR> * TagParser??????<BR> * @return ? * @throws ParseException */ @Override public Map<?, ?> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // int tagRowIdx = tagCell.getRowIndex(); // int tagColIdx = tagCell.getColumnIndex(); // int valueRowFromIdx; // int valueRowToIdx = sheet.getLastRowNum(); // int keyColIdx = 0; // int valueColIdx = 0; // boolean keyTagFlag; // boolean valueTagFlag; // String defKey = null; // String defValue = null; // boolean keyCellTagFlag; // boolean valueCellTagFlag; // int keyRowIdx = 0; // int valueRowIdx = 0; try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? checkParam(paramDef, tagCell); // ? 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); } keyTagFlag = paramDef.containsKey(PARAM_KEY); keyCellTagFlag = paramDef.containsKey(PARAM_KEY_CELL); if (keyTagFlag) { // ??? defKey = paramDef.get(PARAM_KEY); } else if (keyCellTagFlag) { // ??? String value = paramDef.get(PARAM_KEY_CELL); // ?? keyRowIdx = tagRowIdx + Integer.valueOf(value.split(PARAM_CELL_DELIM)[SPLIT_FIRST_INDEX]); if (keyRowIdx < 0 || keyRowIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_KEY_CELL); } // ?? keyColIdx = tagColIdx + Integer.valueOf(value.split(PARAM_CELL_DELIM)[SPLIT_LAST_INDEX]); if (keyColIdx > PoiUtil.getLastColNum(sheet) || keyColIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_KEY_CELL); } } else { // ???? // ?? keyColIdx = TagUtil.adjustValue(tagColIdx, paramDef, PARAM_KEY_COLUMN, DEFAULT_KEY_COLUMN_ADJUST); if (keyColIdx > PoiUtil.getLastColNum(sheet) || keyColIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_KEY_COLUMN); } } valueTagFlag = paramDef.containsKey(PARAM_VALUE); valueCellTagFlag = paramDef.containsKey(PARAM_VALUE_CELL); if (valueTagFlag) { // ??? defValue = paramDef.get(PARAM_VALUE); } else if (valueCellTagFlag) { // ??? String value = paramDef.get(PARAM_VALUE_CELL); // ?? valueRowIdx = tagRowIdx + Integer.valueOf(value.split(PARAM_CELL_DELIM)[SPLIT_FIRST_INDEX]); if (valueRowIdx < 0 || valueRowIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_VALUE_CELL); } // ?? valueColIdx = tagColIdx + Integer.valueOf(value.split(PARAM_CELL_DELIM)[SPLIT_LAST_INDEX]); if (valueColIdx > PoiUtil.getLastColNum(sheet) || valueColIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_VALUE_CELL); } } else { // ???? // ?? valueColIdx = TagUtil.adjustValue(tagColIdx, paramDef, PARAM_VALUE_COLUMN, DEFAULT_VALUE_COLUMN_ADJUST); if (valueColIdx > PoiUtil.getLastColNum(sheet) || valueColIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_VALUE_COLUMN); } } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } // ? Map<Object, Object> results = new LinkedHashMap<Object, Object>(); Row keyRow = null; Row valueRow = null; for (int rowCnt = valueRowFromIdx; rowCnt <= valueRowToIdx; rowCnt++) { Object key; Object value; if (keyTagFlag) { // ??? key = defKey; } else { // ???? // ?? if (keyCellTagFlag) { // ??? keyRow = sheet.getRow(keyRowIdx); } else { // ???? keyRow = sheet.getRow(rowCnt); } if (keyRow == null) { continue; } key = PoiUtil.getCellValue(keyRow.getCell(keyColIdx)); } if (valueTagFlag) { // ??? value = defValue; } else { // ???? // ?? if (valueCellTagFlag) { // ??? valueRow = sheet.getRow(valueRowIdx); } else { // ???? valueRow = sheet.getRow(rowCnt); } if (valueRow != null) { value = PoiUtil.getCellValue(valueRow.getCell(valueColIdx)); } else { value = null; } } results.put(key, value); } return results; }