List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:nu.mine.kino.projects.utils.POITest.java
License:Open Source License
public void test2() throws InvalidFormatException, IOException { Sheet sheet = workbook.getSheetAt(0); Iterator<Row> e = sheet.rowIterator(); while (e.hasNext()) { Row row = e.next();//from w w w . ja va 2 s.c o m Cell cell = row.getCell(0); if (cell.getCellType() != Cell.CELL_TYPE_STRING) { } else { System.out.println(cell.getStringCellValue()); } } }
From source file:nu.mine.kino.projects.utils.PoiUtils.java
License:Open Source License
public static int getHeaderIndex(Sheet sheet) { int number = Integer.MIN_VALUE; Iterator<Row> e = sheet.rowIterator(); int counter = 0; while (e.hasNext()) { Row row = e.next();/*w w w .j a v a 2 s. c o m*/ Cell cell = row.getCell(0); // IteratornullCell // if (cell == null) { // break; // } if (cell.getCellType() != Cell.CELL_TYPE_STRING) { } else { if ("#".equals(cell.getStringCellValue())) { number = counter; } } counter++; } return number; }
From source file:nu.mine.kino.projects.utils.ProjectUtils.java
License:Open Source License
public static Map<String, ExcelPOIScheduleBean> createExcelPOIScheduleBeanMap(Workbook workbook, Date baseDate) throws ProjectException { Map<String, ExcelPOIScheduleBean> poiMap = new HashMap<String, ExcelPOIScheduleBean>(); Sheet sheet = workbook.getSheetAt(0); Iterator<Row> e = sheet.rowIterator(); int index = 0; int dataIndex = PoiUtils.getDataFirstRowNum(sheet); while (e.hasNext()) { // wb_?I?B if (index < dataIndex) { e.next();/*from w w w .jav a 2 s .c om*/ index++; continue; } // f?[^?? Row row = e.next(); Cell taskIdCell = row.getCell(1); String taskId = getTaskId(taskIdCell); ExcelPOIScheduleBean poiBean = createPOIBean(row); poiBean.setBaseDate(baseDate); poiMap.put(taskId, poiBean); } return poiMap; }
From source file:nu.mine.kino.projects.utils.ProjectUtils.java
License:Open Source License
public static Holiday[] createHolidays(Workbook workbook) { Sheet sheet = workbook.getSheet("xe?[u"); List<Holiday> arrayList = new ArrayList<Holiday>(); Iterator<Row> e = sheet.rowIterator(); while (e.hasNext()) { Row row = e.next();//from ww w . ja va 2 s .c o m Holiday holiday = new Holiday(); Cell dateCell = row.getCell(0); if (dateCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (PoiUtil.isCellDateFormatted(dateCell)) { Date dateCellValue = dateCell.getDateCellValue(); holiday.setDate(dateCellValue); } arrayList.add(holiday); } // Cell cell1 = row.getCell(1); // if (cell1 != null && cell1.getCellType() == // Cell.CELL_TYPE_FORMULA) { // holiday.setDayOfWeek((String) PoiUtils.getCellValue(cell1, // String.class)); // } Cell cell2 = row.getCell(2); if (cell2 != null && cell2.getCellType() == Cell.CELL_TYPE_STRING) { holiday.setName(cell2.getStringCellValue()); } Cell cell3 = row.getCell(3); if (cell3 != null && cell3.getCellType() == Cell.CELL_TYPE_STRING) { holiday.setRule(cell3.getStringCellValue()); } Cell cell4 = row.getCell(4); if (cell4 != null && cell4.getCellType() == Cell.CELL_TYPE_STRING) { holiday.setHurikae(cell4.getStringCellValue()); } } return arrayList.toArray(new Holiday[arrayList.size()]); }
From source file:org.adorsys.waguia.lightxls.generic.EasyXlsClazzLoader.java
License:Apache License
public List<T> loadClazz() throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, InstantiationException {//from ww w . ja v a 2 s . c om List<T> result = new ArrayList<T>(); Field[] declaredFields = clazz.getDeclaredFields(); int numberOfSheets = workbook.getNumberOfSheets(); Method[] declaredMethods = clazz.getDeclaredMethods(); if (classInSheetFinder == null) classInSheetFinder = new ClassInSheetFinder(); List<String> sheetNames = new ArrayList<String>(); for (int i = 0; i < numberOfSheets; i++) { sheetNames.add(workbook.getSheetAt(i).getSheetName()); } int position = classInSheetFinder.find(clazz.getSimpleName(), (String[]) sheetNames.toArray(new String[sheetNames.size()])); if (position == -1) throw new RuntimeErrorException(null, "Unable to find the class's sheet"); Sheet clazzSheet = workbook.getSheetAt(position); // assuming that the first row will contains class's properties. so this is // how to get columnNames. Row row = clazzSheet.getRow(HEADER_INDEX); Iterator<Cell> cellIterator = row.cellIterator(); List<String> columnNames = new ArrayList<String>(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); columnNames.add(cell.getStringCellValue()); } if (this.sheetColumnToClazzFieldMatching == null) this.sheetColumnToClazzFieldMatching = new SheetColumnToClazzFieldMatching(); if (sheetColumnToClazzFieldMatching.checkMatching( (String[]) columnNames.toArray(new String[columnNames.size()]), declaredFields, declaredMethods) == false) throw new RuntimeException("Matching Error. Please recheck matching rules"); Iterator<Row> rowIterator = clazzSheet.rowIterator(); if (fieldToColumnComparator == null) this.fieldToColumnComparator = new FieldToColumnComparator(); int numberOfIteration = 0; while (rowIterator.hasNext()) { Row nextRow = rowIterator.next(); Object newInstance = clazz.newInstance(); if (numberOfIteration == HEADER_INDEX) { numberOfIteration++; continue; } for (int i = 0; i < declaredFields.length; i++) { Field field = declaredFields[i]; if (!columnNames.contains(field.getName())) continue; String correspondinMethodName = "set" + StringUtils.capitalize(field.getName()); for (int j = 0; j < declaredMethods.length; j++) { Method method = declaredMethods[j]; if (!method.getName().equals(correspondinMethodName)) continue; int index = 0; //Find the correct field's range in the list of columns. for (String string : columnNames) { if (fieldToColumnComparator.compare(field.getName(), string) == 0) { Class<?> type = field.getType(); if (exelPropertyReader == null) { exelPropertyReader = new ExelPropertyReader(field, type, newInstance, nextRow.getCell(index), method); exelPropertyReader.readProperty(); } else { exelPropertyReader.setField(field); exelPropertyReader.setCell(nextRow.getCell(index)); exelPropertyReader.setMethod(method); exelPropertyReader.setNewInstance(newInstance); exelPropertyReader.setType(type); exelPropertyReader.readProperty(); } index++; continue; } index++; } } } result.add((T) newInstance); numberOfIteration++; } return result; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetDeleteColumn.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; String cols;//from w ww. jav a 2 s . com /* * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(1); cols = parameters.get(0).getString(); Sheet sheet = spreadsheet.getActiveSheet(); Set<Integer> numbers = tagUtils.getNumberSet(cols); Iterator<Row> rowIT = sheet.rowIterator(); while (rowIT.hasNext()) { Row row = rowIT.next(); Iterator<Integer> columnIndx = numbers.iterator(); while (columnIndx.hasNext()) { Cell cell = row.getCell(columnIndx.next() - 1); if (cell != null) { row.removeCell(cell); } } } return cfBooleanData.TRUE; }
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"); }/*from w w w . j a v a 2 s. co 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.SpreadsheetShiftColumns.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; int start = 0, end = 0, cols = 1; /*/* w w w . ja v a 2s. com*/ * Collect up the parameters */ if (parameters.size() == 2) { spreadsheet = (cfSpreadSheetData) parameters.get(1); start = parameters.get(0).getInt() - 1; end = start; } else if (parameters.size() == 3) { spreadsheet = (cfSpreadSheetData) parameters.get(2); start = parameters.get(1).getInt() - 1; end = start; cols = parameters.get(0).getInt(); } else if (parameters.size() == 4) { spreadsheet = (cfSpreadSheetData) parameters.get(3); start = parameters.get(2).getInt() - 1; end = parameters.get(1).getInt() - 1; cols = parameters.get(0).getInt(); } /* * Validate parameters */ if (start < 0) throwException(_session, "start must be 1 or greater (" + start + ")"); if (end < 0) throwException(_session, "end must be 1 or greater (" + end + ")"); if (start > end) throwException(_session, "end must be greater that start"); Sheet sheet = spreadsheet.getActiveSheet(); Iterator<Row> rowIT = sheet.rowIterator(); while (rowIT.hasNext()) { Row row = rowIT.next(); if (cols > 0) { // Moving to the right for (int x = 0; x < (end - start) + 1; x++) { // Remove Cell that is there Cell cell = row.getCell(end + cols - x); if (cell != null) row.removeCell(cell); Cell thisCell = row.getCell(end - x); if (thisCell != null) { Cell newCell = row.createCell(end + cols - x, thisCell.getCellType()); SheetUtility.cloneCell(newCell, thisCell); row.removeCell(thisCell); } } } else { // Moving to the left for (int x = 0; x < (end - start) + 1; x++) { // Remove Cell that is there if (start + cols - x < 0) continue; Cell cell = row.getCell(start + cols - x); if (cell != null) row.removeCell(cell); Cell thisCell = row.getCell(start - x); if (thisCell != null) { Cell newCell = row.createCell(start + cols - x, thisCell.getCellType()); SheetUtility.cloneCell(newCell, thisCell); row.removeCell(thisCell); } } } } return cfBooleanData.TRUE; }
From source file:org.apache.metamodel.excel.ExcelUtils.java
License:Apache License
public static Iterator<Row> getRowIterator(Sheet sheet, ExcelConfiguration configuration, boolean jumpToDataRows) { final Iterator<Row> iterator; if (configuration.isSkipEmptyLines()) { iterator = sheet.rowIterator(); } else {/*w w w.j a va2 s . c o m*/ iterator = new ZeroBasedRowIterator(sheet); } if (jumpToDataRows) { final int columnNameLineNumber = configuration.getColumnNameLineNumber(); if (columnNameLineNumber != ExcelConfiguration.NO_COLUMN_NAME_LINE) { // iterate past the column headers if (iterator.hasNext()) { iterator.next(); } for (int i = 1; i < columnNameLineNumber; i++) { if (iterator.hasNext()) { iterator.next(); } else { // no more rows! break; } } } } return iterator; }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ??/* w w w . j a v a 2s . c o m*/ * * @param sheet * @param rangeAddress */ public static void clearCell(Sheet sheet, CellRangeAddress rangeAddress) { int fromRowIndex = rangeAddress.getFirstRow(); int fromColumnIndex = rangeAddress.getFirstColumn(); int toRowIndex = rangeAddress.getLastRow(); int toColumnIndex = rangeAddress.getLastColumn(); // ??? List<Row> removeRowList = new ArrayList<Row>(); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (fromRowIndex <= row.getRowNum() && row.getRowNum() <= toRowIndex) { Set<Cell> removeCellSet = new HashSet<Cell>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (fromColumnIndex <= cell.getColumnIndex() && cell.getColumnIndex() <= toColumnIndex) { removeCellSet.add(cell); } } for (Cell cell : removeCellSet) { row.removeCell(cell); } } if (row.getLastCellNum() == -1) { removeRowList.add(row); } } for (Row row : removeRowList) { sheet.removeRow(row); } }