List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:opn.greenwebs.FXMLDocumentController.java
private void getData() { try {/*from w w w. j a va2s. c om*/ fStockDir = new File(fUserDir, "Stock"); if (!fStockDir.exists()) { fStockDir.mkdirs(); } FilenameFilter xlsxFilter = (File dir, String name) -> { String lowercaseName = name.toLowerCase(); return lowercaseName.endsWith(".xlsx"); }; File[] fStock = fStockDir.listFiles(xlsxFilter); for (File excel : fStock) { try (FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); List<XSSFTable> lstTables = sheet.getTables(); if (!lstTables.isEmpty()) { shootTables(sheet, lstTables); } else { //System.out.println("we have one without a table"); Iterator<Row> itr = sheet.iterator(); boolean bData = false; int nRow = 0; while (itr.hasNext()) { Row row = itr.next(); int nCou = 0; Iterator<Cell> cellIterator = row.cellIterator(); List lstItem = new ArrayList(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String strCell = cell.getStringCellValue(); if (strCell.equalsIgnoreCase("Qty")) { nCou = nRow + 1; } if (bData) { lstItem.add(strCell); } break; case Cell.CELL_TYPE_NUMERIC: if (bData) { lstItem.add(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: if (bData) { lstItem.add(cell.getBooleanCellValue()); } break; case Cell.CELL_TYPE_BLANK: if (bData) { lstItem.add(""); } break; case Cell.CELL_TYPE_FORMULA: if (bData) { lstItem.add(cell.getCellFormula()); } break; default: } } if (bData) { System.out.print(lstItem); insertData(lstItem, true); } nRow++; if (nCou == nRow) { bData = true; } } //bIsGood = false; } } } } catch (FileNotFoundException fe) { //System.out.println("File not found exception " + fe.getMessage()); } catch (IOException ie) { //System.out.println("IO Exception " + ie.getMessage()); } }
From source file:opn.greenwebs.FXMLDocumentController.java
private List<ItemDB> createListFromTable(File fStock) { List<ItemDB> lstMain = new ArrayList<>(); logger.log(Level.INFO, "into the breach {0}", fStock.getAbsolutePath()); try (FileInputStream fis = new FileInputStream(fStock); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); logger.log(Level.INFO, "we have a sheet {0}", sheet.getSheetName()); FormulaEvaluator evaluator2 = book.getCreationHelper().createFormulaEvaluator(); List lstItem;/* w w w. ja va2s . c o m*/ List<XSSFTable> lstTables = sheet.getTables(); logger.log(Level.SEVERE, "we have a table? {0}", lstTables.size()); if (!lstTables.isEmpty()) { logger.log(Level.INFO, "the table is not empty"); for (int j = lstTables.get(0).getStartCellReference().getRow(); j <= lstTables.get(0) .getEndCellReference().getRow(); j++) { ItemDB itmLine = new ItemDB(); lstItem = new ArrayList(); logger.log(Level.INFO, "we have some rows"); if (j > lstTables.get(0).getStartCellReference().getRow()) { Row row = sheet.getRow(j); if (row != null) { Iterator<Cell> cellIt = row.cellIterator(); logger.log(Level.INFO, "we have some cells {0}", cellIt.hasNext()); while (cellIt.hasNext()) { Cell cell = cellIt.next(); CellValue cellvalue = evaluator2.evaluate(cell); if (cellvalue == null) { lstItem.add(""); } else { switch (cellvalue.getCellType()) { case Cell.CELL_TYPE_STRING: lstItem.add(cellvalue.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cellvalue.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cellvalue.getBooleanValue()); case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add("You found a formula"); break; default: } } } logger.log(Level.ALL, "lstItem is {0} and {1}", new Object[] { lstItem.size(), lstItem.toString() }); itmLine.setDblQty(Double.parseDouble(lstItem.get(0).toString())); logger.log(Level.INFO, "setting the quantity to {0}", lstItem.get(0).toString()); itmLine.setStrMfr(lstItem.get(1).toString()); itmLine.setStrSKU(lstItem.get(2).toString()); itmLine.setStrDescrip(lstItem.get(3).toString()); itmLine.setStrSupplier(lstItem.get(4).toString()); itmLine.setStrSupPart(lstItem.get(5).toString()); logger.log(Level.INFO, "setting the suppart to {0}", lstItem.get(5)); if (lstItem.size() > 6) { if (!lstItem.get(6).toString().isEmpty()) { itmLine.setDblSalePrice(Double.parseDouble(lstItem.get(6).toString())); } } //System.out.println("Added item " + itmLine.getStrDescrip()); lstMain.add(itmLine); } } } } } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } logger.log(Level.INFO, "returning {0}", lstMain.size()); return lstMain; }
From source file:opn.greenwebs.FXMLDocumentController.java
private void shootTables(XSSFSheet sheet, List<XSSFTable> list) { for (XSSFTable xTable : list) { for (int j = xTable.getStartCellReference().getRow(); j < xTable.getEndCellReference().getRow(); j++) { Row row = sheet.getRow(j + 1); List lstItem = new ArrayList(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String strCell = cell.getStringCellValue(); lstItem.add(strCell); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add(cell.getCellFormula()); break; default: }/* ww w.ja v a 2s.c o m*/ } insertData(lstItem, true); } } }
From source file:optimizador.optimizadordirecciones.co.com.optimizador.util.LeerExcel.java
/** * Lee un archivo y lo convierte en objetos RuteroVO. * /*w ww . ja v a 2 s . c o m*/ * @param fileName * el nombre del archivo. * @return la lista de objetos. */ public ArrayList<Predio> readExcelData(String fileName) { // Se crea un arraylist para guadar las consulta de las celdas // del archivo excel. // Y un objeto ruteroVO par guardadr la imagen exacta del archivo ArrayList<Predio> ruteros = new ArrayList<>(); Predio ruteroIni; FileInputStream fis = null; try { // Crea stream de entrada para un archivo xlsx/xls fis = new FileInputStream(fileName); // Se crea una instancia Workbook para manejar el archivo xlsx/xls // Se pregunta si el archivo a leer es xlsx o xls para instanciar // la clase correspondiente Workbook workbook = getWorkbook(fis, fileName); // Se obtiene la cantidad de libros que tiene el archivo excel int numberOfSheets = workbook.getNumberOfSheets(); // interacion a traves de cada libro for (int i = 0; i < numberOfSheets; i++) { // Se obtiene el libro Sheet sheet = (Sheet) workbook.getSheetAt(i); // Se crear iterador para seleccionar las filas de la // hoja del libro seleccionado Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { if (idRow == 0) { // Para quitar lafila de titulos idRow = 1; rowIterator.next(); } else { // Se ontiene la fila dentro de un objeto Row Row row = rowIterator.next(); // Se crea iterador para las columnas resultando // una celda Iterator<Cell> cellIterator = row.cellIterator(); ruteroIni = new Predio(); while (cellIterator.hasNext()) { // Se obtiene una celda dentro de un objeto // Cell Cell cell = cellIterator.next(); asignarDatos(ruteroIni, cell); } // Final del iterador de celda ruteros.add(ruteroIni); ruteros = obtenerCoordenadas(ruteros); } } // Final del iterador fila } // Final del Loop hoja } catch (IOException e) { LOGGER.error(e.getMessage(), e); } finally { if (fis != null) { try { fis.close(); } catch (IOException e) { LOGGER.error(e.getMessage(), e); } } } idRow = 0; return ruteros; }
From source file:org.adorsys.waguia.lightxls.generic.EasyXlsClazzLoader.java
License:Apache License
public List<T> loadClazz() throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, InstantiationException {/*ww w . ja va2 s . com*/ 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.agmip.ui.afsirs.others.ParseExcelToWeatherFileFormat.java
private static void ParseRain() { try {/*from www . j a v a 2 s . c o m*/ FileInputStream fs = new FileInputStream(rainFilePath); Workbook wb = new XSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); String str = "ORLANDO DAILY POTENTIAL RAIN DATA (INCHES), 1952-2015" + EOL; Iterator rowIterator = sheet.iterator(); int outputcol = 0; // This will go from 1 - 14 in a row int outputElems = 0; // This will go from 1-365 and once this is int overallRow = 0; // Total Number of Row processed int startYr = 0; // This will hold the First Year int endYr = 0; // This will hold the last Year while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); int month = 0; int date = 0; int year = 0; overallRow++; if (overallRow <= 4) { continue; } while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); int colIndex = cell.getColumnIndex(); if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { //This holds the Month Number if (colIndex == 3) { month = (int) cell.getNumericCellValue(); } // This Date of the month // Skip if this is a Leap yeaar else if (colIndex == 4) { date = (int) cell.getNumericCellValue(); if (year % 4 == 0 && month == 2 && date == 29) { break; } } // Year else if (colIndex == 2) { year = (int) cell.getNumericCellValue(); if (endYr != year) { endYr = year; outputElems = 0; outputcol = 0; str += year + EOL; } if (startYr == 0) { startYr = year; endYr = year; } //student.setEnglish(String.valueOf(cell.getNumericCellValue())); } else if (colIndex == 7) { Double val = cell.getNumericCellValue(); str += " " + String.format("%.3f", val); outputcol++; outputElems++; if (outputcol == 14) { str += EOL; outputcol = 0; } System.out.println("Value = " + val + EOL); if (outputElems == 365) { outputElems = 0; outputcol = 0; str += EOL; System.out.println("--------------" + EOL); } } } /*else if (colIndex == 6) { Double val = Double.valueOf(cell.getStringCellValue()); str+= " " + String.format("%.3f", val); outputcol++; outputElems++; // if 365 // if 14 if (outputcol==14) { str+=EOL; outputcol=0; } System.out.println ("Value = " + val + EOL); if (outputElems==365){ outputElems = 0; outputcol = 0; str+=EOL; System.out.println ("--------------" + EOL); } }*/ } //System.out.println (" Column " + outputcol + " Element Number " + outputElems + EOL); overallRow++; } //String line2 = String.format(" %d YEARS OF RECORD"+EOL, (endYr-startYr+1)); //str = line2+str; writer.print(str); } catch (Exception ioe) { ioe.printStackTrace(); } finally { writer.close(); } }
From source file:org.agmip.ui.afsirs.others.ParseExcelToWeatherFileFormat.java
private static void ParseETP() { try {/*www. j av a2 s . c o m*/ FileInputStream fs = new FileInputStream(etFilePath); Workbook wb = new XSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); writer = new PrintWriter("CLIM.ORL", "UTF-8"); String str = "ORLANDO DAILY POTENTIAL ET DATA (INCHES), 1952-2015" + EOL; writer.print(str); Iterator rowIterator = sheet.iterator(); // We will skip the leap year Entry int outputcol = 0; // This will go from 1 - 14 in a row int outputElems = 0; // This will go from 1-365 and once this is int overallRow = 1; // Total Number of Row processed int startYr = 0; // This will hold the First Year int endYr = 0; // This will hold the last Year //int yrEntry = 0; // This should go till 365; str = ""; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); int month = 0; int date = 0; int year = 0; if (overallRow <= 4) { overallRow++; continue; } while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); //The Cell Containing String will is name. if (Cell.CELL_TYPE_STRING == cell.getCellType()) { //student.setName(cell.getStringCellValue()); String val = cell.getStringCellValue(); writer.print(val + " "); //The Cell Containing numeric value will contain marks } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { int colIndex = cell.getColumnIndex(); //This holds the Month Number if (colIndex == 0) { month = (int) cell.getNumericCellValue(); } // This Date of the month // Skip if this is a Leap yeaar else if (colIndex == 1) { date = (int) cell.getNumericCellValue(); } // Year else if (colIndex == 2) { year = (int) cell.getNumericCellValue(); if (startYr == 0) { startYr = year; endYr = year; } else { endYr = year; } if (outputElems == 0) { str += year + EOL; } if (year % 4 == 0 && month == 2 && date == 29) { break; } //student.setEnglish(String.valueOf(cell.getNumericCellValue())); } else if (colIndex == 3) { Double val = cell.getNumericCellValue(); str += " " + String.format("%.3f", val); outputcol++; outputElems++; // if 365 // if 14 if (outputcol == 14) { str += EOL; outputcol = 0; } if (outputElems == 365) { outputElems = 0; outputcol = 0; str += EOL; } } } } overallRow++; } String line2 = String.format(" %d YEARS OF RECORD" + EOL, (endYr - startYr + 1)); str = line2 + str; writer.print(str); } catch (Exception ioe) { ioe.printStackTrace(); } finally { //writer.close(); } }
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(); Iterator<Cell> cellIT = row.cellIterator(); while (cellIT.hasNext()) { Cell cell = cellIT.next();//from w w w . j av a2 s. c o m 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"); }/* w ww.j a v a 2s.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(); Iterator<Cell> cellIt = row.cellIterator(); while (cellIt.hasNext()) { Cell cell = cellIt.next();/*from w ww . j a v a2 s .c o m*/ 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; }