List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getRowIndex
@Override public int getRowIndex()
From source file:com.centurylink.mdw.drools.Excel2007Parser.java
License:Apache License
private CellRangeAddress getRangeIfMerged(XSSFCell cell, CellRangeAddress[] mergedRanges) { for (int i = 0; i < mergedRanges.length; i++) { CellRangeAddress range = mergedRanges[i]; if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) return range; }/*from w w w .jav a2 s . co m*/ return null; }
From source file:de.escnet.ExcelTable.java
License:Open Source License
public ExcelTable(String excel, String sheetName) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(excel); evaluator = wb.getCreationHelper().createFormulaEvaluator(); theme = wb.getTheme();//w w w . j a va 2 s . c o m sheet = sheetName == null ? wb.getSheetAt(0) : wb.getSheet(sheetName); for (Iterator rowIt = sheet.rowIterator(); rowIt.hasNext();) { Row row = (Row) rowIt.next(); for (Iterator cellIt = row.cellIterator(); cellIt.hasNext();) { XSSFCell cell = (XSSFCell) cellIt.next(); int rowIndex = cell.getRowIndex(); rowMin = Math.min(rowMin, rowIndex); rowMax = Math.max(rowMax, rowIndex); int colIndex = cell.getColumnIndex(); colMin = Math.min(colMin, colIndex); colMax = Math.max(colMax, colIndex); } } }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
private boolean operation_Standard_Workingsection_Verification(int SORQ_tokenLocation, int first_Occurance_Of_Financial_Comparision, XSSFSheet Sheet, ArrayList<String> opeartion_standard_workingSectionList, ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) {//from www. j a va 2s .com int reporting_Qtr_OS_Working_SectionCount = 0; String reporting_Qtr_Formula_Cell_Formula; String reporting_Qtr_Formula_Cell_Value; boolean isError = false; XSSFRow row; XSSFCell cell; for (int start = (SORQ_tokenLocation - 1); start < (first_Occurance_Of_Financial_Comparision - 1); start++) { try { row = Sheet.getRow(start); cell = row.getCell(1); reporting_Qtr_Formula_Cell_Value = cell.getStringCellValue(); if (reporting_Qtr_Formula_Cell_Value.equalsIgnoreCase("Operational Comparison")) { start = start + 1; } else if (reporting_Qtr_OS_Working_SectionCount < opeartion_standard_workingSectionList.size()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Qtr_Formula_Cell_Formula = cell.getCellFormula(); if (reporting_Qtr_Formula_Cell_Formula.contains("$")) { reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } String retrive_Formula = opeartion_standard_workingSectionList .get(reporting_Qtr_OS_Working_SectionCount); if (reporting_Qtr_Formula_Cell_Formula.equals(retrive_Formula)) { reporting_Qtr_OS_Working_SectionCount++; } else { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc( "Sequence does not match Actual Sequence should be" + retrive_Formula); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Qtr_OS_Working_SectionCount++; isError = true; } break; case Cell.CELL_TYPE_BLANK: break; default: { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Cell does not contain formula"); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Qtr_OS_Working_SectionCount++; isError = true; } break; } } else { reporting_Qtr_OS_Working_SectionCount++; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } if (reporting_Qtr_OS_Working_SectionCount != opeartion_standard_workingSectionList.size()) { ErrorModel errorModel = new ErrorModel(); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Reporting_Qtr have " + Math.abs(reporting_Qtr_OS_Working_SectionCount - opeartion_standard_workingSectionList.size()) + " extra rows from Operation_Standard"); errorModel.setError_level("Error"); errorModelList.add(errorModel); isError = true; } return isError; }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
private boolean financial_Standard_Workingsection_Verification(int first_Occurance_Of_Financial_Comparision, int EORQ_tokenLocation, XSSFSheet Sheet, ArrayList<String> financial_standard_workingSectionList, ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) { int reporting_Qtr_FS_Working_SectionCount = 0; String reporting_Qtr_Formula_Cell_Formula = null; String reporting_Qtr_Formula_Cell_Value; XSSFRow row;//from ww w . ja v a 2 s . co m XSSFCell cell; boolean isError = false; for (int start = (first_Occurance_Of_Financial_Comparision - 1); start < EORQ_tokenLocation; start++) { try { row = Sheet.getRow(start); cell = row.getCell(1); reporting_Qtr_Formula_Cell_Value = cell.getStringCellValue(); if (reporting_Qtr_Formula_Cell_Value.equals("Financial Comparison")) { start = start + 3; } else if (reporting_Qtr_FS_Working_SectionCount < financial_standard_workingSectionList.size()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Qtr_Formula_Cell_Formula = cell.getCellFormula(); if (reporting_Qtr_Formula_Cell_Formula.contains("$")) { reporting_Qtr_Formula_Cell_Formula = reporting_Qtr_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } String retrive_Formula = financial_standard_workingSectionList .get(reporting_Qtr_FS_Working_SectionCount); if (reporting_Qtr_Formula_Cell_Formula.equals(retrive_Formula)) { reporting_Qtr_FS_Working_SectionCount++; } else { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc( "Sequence does not match Actual Sequence should be" + retrive_Formula); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Qtr_FS_Working_SectionCount++; isError = true; } break; case Cell.CELL_TYPE_BLANK: break; default: { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Cell does not contain formula "); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Qtr_FS_Working_SectionCount++; isError = true; } break; } } else { reporting_Qtr_FS_Working_SectionCount++; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } if (reporting_Qtr_FS_Working_SectionCount != financial_standard_workingSectionList.size()) { ErrorModel errorModel = new ErrorModel(); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Reporting_Qtr have " + Math.abs(reporting_Qtr_FS_Working_SectionCount - financial_standard_workingSectionList.size()) + " extra rows from Operation_Standard"); errorModel.setError_level("Error"); errorModelList.add(errorModel); isError = true; } return isError; }
From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java
private void genrateError(XSSFCell cell_ref, ArrayList<ErrorModel> errorModelList, String worksection_type) { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell_ref); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Qtr"); errorModel.setError_desc("Cell Formula is not linked correctly to the " + worksection_type); errorModel.setError_level("Error"); errorModel.setRow((cell_ref.getRowIndex() + 1)); errorModelList.add(errorModel);//w w w . j av a2 s. c om }
From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java
private boolean operation_Standard_Workingsection_Verification(int SORY_tokenLocation, int first_Occurance_Of_Financial_Comparision, XSSFSheet Sheet, ArrayList<String> opeartion_standard_workingSectionList, ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) {//from ww w . j a v a2 s. co m int reporting_Year_OS_Working_SectionCount = 0; String reporting_Year_Formula_Cell_Formula; String reporting_Year_Formula_Cell_Value; boolean isError = false; XSSFRow row; XSSFCell cell; for (int start = (SORY_tokenLocation - 1); start < (first_Occurance_Of_Financial_Comparision - 1); start++) { try { row = Sheet.getRow(start); cell = row.getCell(1); reporting_Year_Formula_Cell_Value = cell.getStringCellValue(); if (reporting_Year_Formula_Cell_Value.equalsIgnoreCase("Operational Comparison")) { start = start + 1; } else if (reporting_Year_OS_Working_SectionCount < opeartion_standard_workingSectionList.size()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Year_Formula_Cell_Formula = cell.getCellFormula(); if (reporting_Year_Formula_Cell_Formula.contains("$")) { reporting_Year_Formula_Cell_Formula = reporting_Year_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } String retrive_Formula = opeartion_standard_workingSectionList .get(reporting_Year_OS_Working_SectionCount); if (reporting_Year_Formula_Cell_Formula.equals(retrive_Formula)) { reporting_Year_OS_Working_SectionCount++; } else { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc( "Sequence does not match Actual Sequence should be" + retrive_Formula); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Year_OS_Working_SectionCount++; isError = true; } break; case Cell.CELL_TYPE_BLANK: break; default: { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Cell does not contain formula"); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Year_OS_Working_SectionCount++; isError = true; } break; } } else { reporting_Year_OS_Working_SectionCount++; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } if (reporting_Year_OS_Working_SectionCount != opeartion_standard_workingSectionList.size()) { ErrorModel errorModel = new ErrorModel(); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Reporting_Year have " + Math.abs( reporting_Year_OS_Working_SectionCount - opeartion_standard_workingSectionList.size()) + " extra rows from Operation_Standard"); errorModel.setError_level("Error"); errorModelList.add(errorModel); isError = true; } return isError; }
From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java
private boolean financial_Standard_Workingsection_Verification(int first_Occurance_Of_Financial_Comparision, int EORY_tokenLocation, XSSFSheet Sheet, ArrayList<String> financial_standard_workingSectionList, ArrayList<ErrorModel> errorModelList, XSSFWorkbook workbook) { int reporting_Year_FS_Working_SectionCount = 0; String reporting_Year_Formula_Cell_Formula = null; String reporting_Year_Formula_Cell_Value; XSSFRow row;//from www .ja v a 2 s .c om XSSFCell cell; boolean isError = false; for (int start = (first_Occurance_Of_Financial_Comparision - 1); start < EORY_tokenLocation; start++) { try { row = Sheet.getRow(start); cell = row.getCell(1); reporting_Year_Formula_Cell_Value = cell.getStringCellValue(); if (reporting_Year_Formula_Cell_Value.equalsIgnoreCase("Financial Comparison")) { start = start + 3; } else if (reporting_Year_FS_Working_SectionCount < financial_standard_workingSectionList.size()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: reporting_Year_Formula_Cell_Formula = cell.getCellFormula(); if (reporting_Year_Formula_Cell_Formula.contains("$")) { reporting_Year_Formula_Cell_Formula = reporting_Year_Formula_Cell_Formula .replaceAll("\\$", "").replaceAll(" ", ""); } String retrive_Formula = financial_standard_workingSectionList .get(reporting_Year_FS_Working_SectionCount); if (reporting_Year_Formula_Cell_Formula.equals(retrive_Formula)) { reporting_Year_FS_Working_SectionCount++; } else { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc( "Sequence does not match Actual Sequence should be" + retrive_Formula); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Year_FS_Working_SectionCount++; isError = true; } break; case Cell.CELL_TYPE_BLANK: break; default: { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Cell does not contain formula "); errorModel.setError_level("Error"); errorModel.setRow((cell.getRowIndex() + 1)); errorModelList.add(errorModel); reporting_Year_FS_Working_SectionCount++; isError = true; } break; } } else { reporting_Year_FS_Working_SectionCount++; } } catch (NullPointerException nullexcp) { continue; } catch (Exception e) { e.printStackTrace(); } } if (reporting_Year_FS_Working_SectionCount != financial_standard_workingSectionList.size()) { ErrorModel errorModel = new ErrorModel(); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Reporting_Year have " + Math.abs( reporting_Year_FS_Working_SectionCount - financial_standard_workingSectionList.size()) + " extra rows from Operation_Standard"); errorModel.setError_level("Error"); errorModelList.add(errorModel); isError = true; } return isError; }
From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java
private void genrateError(XSSFCell cell_ref, ArrayList<ErrorModel> errorModelList, String worksection_type) { ErrorModel errorModel = new ErrorModel(); CellReference cellRef = new CellReference(cell_ref); errorModel.setCell_ref(cellRef.formatAsString()); errorModel.setSheet_name("Reporting_Year"); errorModel.setError_desc("Cell Formula is not linked correctly to the " + worksection_type); errorModel.setError_level("Error"); errorModel.setRow((cell_ref.getRowIndex() + 1)); errorModelList.add(errorModel);//from www .j a v a 2 s . co m }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
private void copyRow(XSSFRow sourceRow, XSSFRow targetRow, XSSFCreationHelper factory, XSSFDrawing patriarch) { for (int j = 0; j < sourceRow.getPhysicalNumberOfCells(); j++) { XSSFCell cell = sourceRow.getCell(j); if (cell != null) { XSSFCell newCell = targetRow.createCell(j); int cellType = cell.getCellType(); newCell.setCellType(cellType); switch (cellType) { case XSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(cell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: newCell.setCellValue(cell.getRichStringCellValue()); break; default: newCell.setCellValue(formatter.formatCellValue(cell)); }//from w w w . ja v a 2s . c o m if (cell.getCellComment() != null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(newCell.getColumnIndex()); anchor.setCol2(newCell.getColumnIndex() + 4); anchor.setRow1(newCell.getRowIndex()); anchor.setRow2(newCell.getRowIndex() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); XSSFComment comment = patriarch.createCellComment(anchor); comment.setString(cell.getCellComment().getString()); newCell.setCellComment(comment); } newCell.setCellStyle(cell.getCellStyle()); newCell.getSheet().setColumnWidth(newCell.getColumnIndex(), cell.getSheet().getColumnWidth(cell.getColumnIndex())); } } }
From source file:utils.ReadWriteExcelFile.java
public static void readXLSXFile(String aFile, int SheetNo) throws IOException { InputStream ExcelFileToRead = new FileInputStream(aFile); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFWorkbook test = new XSSFWorkbook(); XSSFSheet sheet = wb.getSheetAt(SheetNo); XSSFRow row;// w ww . ja va 2 s .c om XSSFCell cell; CTSheetDimension dimension = sheet.getCTWorksheet().getDimension(); String sheetDimensions = dimension.getRef(); System.out.println(sheetDimensions); List<String> dimensions = StringUtils.split(sheetDimensions, ":", true); String[] Dimensions = dimensions.get(1).toString().split("(?<=\\D)(?=\\d)"); int Colums = CharToInt(Dimensions[0]); int Rows = Integer.parseInt(Dimensions[1]); System.out.println(); Iterator rows = sheet.rowIterator(); ArrayList[][] TableName = new ArrayList[Rows][Colums]; System.out.println(TableName.length); int currentRow = 0; while (rows.hasNext()) { row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); int currentCell = 0; // System.out.println("currentRow="+currentRow+" And Current Colum is ="+currentCell); while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (cell.getStringCellValue().isEmpty()) { TableName[cell.getRowIndex()][cell.getColumnIndex()].add(" - "); // System.out.print(cell.getStringCellValue()+" "); // TableName[currentRow][currentCell].add(" "); // System.out.println("Cell Value is : "+cell.toString()); // System.out.println("Empty cell currentRow="+currentRow+" And Current Colum is ="+currentCell); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { System.out.println("current Row=" + cell.getRowIndex() + " And Current Colum is =" + cell.getColumnIndex()); System.out.println(cell.getRichStringCellValue()); TableName[cell.getRowIndex()][cell.getColumnIndex()] .add(cell.getRichStringCellValue().toString()); // System.out.println("Cell Type is :"+cell.getCellType()); // System.out.println("Cell Value is : "+cell.getRichStringCellValue()); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { TableName[cell.getRowIndex()][cell.getColumnIndex()].add(cell.getNumericCellValue()); // System.out.println("Cell Type is :"+cell.getCellType()); // System.out.println("current Row="+cell.getRowIndex()+" And Current Colum is ="+cell.getColumnIndex()); // int numericValue = (int) cell.getNumericCellValue(); // System.out.println("Cell Value is : "+numericValue); } // else // { // //U Can Handel Boolean, Formula, Errors // } currentCell++; } System.out.println(); currentRow++; } for (int i = 0; TableName.length > i; i++) { for (int j = 0; TableName[i].length > j; j++) { System.out.println(TableName[i][j].toString()); } } }