List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:helpers.Excel.ExcelDataFormat.java
public Object marshalAsArray(Iterator<Row> sheet) { ArrayList<ArrayList<Object>> results = new ArrayList<ArrayList<Object>>(); for (Iterator<Row> rowIterator = sheet; rowIterator.hasNext();) { ArrayList newrow = new ArrayList(); results.add(newrow);//from w w w. ja va 2s. co m Row row = rowIterator.next(); for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) { Cell cell = cellIterator.next(); logger.info("Cell type:" + cell.getCellType()); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { //logger.info(cell.getCellType()+"="+cell.getDateCellValue()); newrow.add(cell.getDateCellValue()); } else { //logger.info(cell.getCellType()+"="+cell.getNumericCellValue()); newrow.add(cell.getNumericCellValue()); } break; default: //logger.info(cell.getCellType()+"="+cell.getStringCellValue()); newrow.add(cell.getStringCellValue()); break; } } } return results; }
From source file:helpers.Excel.ExcelDataFormat.java
public OneExcelSheet marshalAsStructure(Iterator<Row> sheet, FormulaEvaluator evaluator) { logger.info("Evaluating formulas."); evaluator.evaluateAll();//from ww w. ja v a2s . c om logger.info("Done..."); OneExcelSheet onesheet = new OneExcelSheet(); ArrayList<String> headers = null; for (Iterator<Row> rowIterator = sheet; rowIterator.hasNext();) { Row row = rowIterator.next(); if (headers == null) { headers = new ArrayList<String>(); int coln = 0; for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) { try { Cell cell = cellIterator.next(); logger.info("Header:" + cell.getStringCellValue()); String headn = cell.getStringCellValue().replace(" ", ""); headers.add(headn); OneExcelColumn col = new OneExcelColumn(headn, coln); onesheet.columns.add(col); } catch (Exception e) { logger.error("Unable to decode cell header. Ex=" + e.getMessage(), e); } coln++; } } else { ArrayList<Object> newrow = new ArrayList<Object>(); onesheet.data.add(newrow); int coln = 0; //for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) for (int cn = 0; cn < row.getLastCellNum(); cn++) { Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK); //Cell cell=cellIterator.next(); //logger.info("Cell type:"+cell.getCellType()); switch (evaluator.evaluateInCell(cell).getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { //logger.info(cell.getCellType()+"="+cell.getDateCellValue()); newrow.add(cell.getDateCellValue()); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[9]++; } else { //logger.info(cell.getCellType()+"="+cell.getNumericCellValue()); newrow.add(cell.getNumericCellValue()); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[cell.getCellType()]++; } break; case HSSFCell.CELL_TYPE_FORMULA: int value = evaluator.evaluateFormulaCell(cell); value = cell.getCachedFormulaResultType(); newrow.add(value); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[0]++; break; default: //logger.info(cell.getCellType()+"="+cell.getStringCellValue()); String cellstr = new String(cell.getStringCellValue().getBytes(), Charset.forName("UTF-8")); newrow.add(cellstr); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[cell.getCellType()]++; break; } coln++; } } } return onesheet; }
From source file:hrytsenko.gscripts.io.XlsFiles.java
License:Apache License
private static List<String> cellValues(Row row) { return StreamSupport .stream(Spliterators.spliteratorUnknownSize(row.cellIterator(), Spliterator.ORDERED), false) .map(XlsFiles::cellValue).collect(Collectors.toList()); }
From source file:info.toegepaste.www.service.ExcelServiceImpl.java
@Override @TransactionAttribute(REQUIRES_NEW)//from ww w .j a v a 2 s . c o m public String upload(Part file) { try { //declaratie variabelen //Variabelen voor tijdelijke gegevens int cellInt; String cellString; int cellNr; String infoCel = " "; int scoresTeller = 1; String fout = " "; //debug of overzetvariabelen String klasDebug = " "; String vakDebug = " "; String testDebug = " "; int totaalDebug = 0; List<Integer> studentennrDebug = new ArrayList<Integer>(); List<String> naamDebug = new ArrayList<String>(); List<Integer> scoreDebug = new ArrayList<Integer>(); Iterator<Row> rowIterator = null; if (file.getSubmittedFileName().contains("xlsx")) { //lees de content stream in naar de hssfworkbook XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream()); //kies de juiste pagina (eerste) XSSFSheet sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); } else if (file.getSubmittedFileName().contains("xls")) { //lees de content stream in naar de hssfworkbook HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream()); //kies de juiste pagina (eerste) HSSFSheet sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); } else { fout = "Het geuploadde bestand heeft niet de juiste indeling"; } if (rowIterator != null) { while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Leest elke horizontale lijn van links naar rechts uit in de console Iterator<Cell> cellIterator = row.cellIterator(); cellNr = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cellNr++; //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); cellInt = (int) cell.getNumericCellValue(); if (infoCel.equals("totaal")) { totaalDebug = cellInt; } else { if (scoresTeller == 1) { studentennrDebug.add(cellInt); scoresTeller++; } else { scoreDebug.add(cellInt); scoresTeller = 1; } } break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue()); cellString = cell.getStringCellValue(); //Als er een titel in de cel zit en steek in infoCel if (cellString.toLowerCase().equals("vak") || cellString.toLowerCase().equals("klas") || cellString.toLowerCase().equals("test") || cellString.toLowerCase().equals("totaal") || cellString.toLowerCase().equals("score")) { infoCel = cellString.toLowerCase(); } else { //inhoud in de cell switch (infoCel) { case "klas": klasDebug = cellString; break; case "vak": vakDebug = cellString; break; case "test": testDebug = cellString; break; case "score": naamDebug.add(cellString); scoresTeller++; break; default: break; } } break; } } } } System.out.println(""); int testId = 0; int vakId = 0; int studentId = 0; try { //als er al een test is aangemaakt met die naam, voeg gewoon punten toe testId = getTestId(testDebug); } catch (Exception e) { try { //als er al een vak is aangemaakt met die naam, voeg gewoon een vak toe vakId = getVakId(vakDebug); } catch (Exception e1) { insertVak(vakDebug); vakId = getVakId(vakDebug); } //maak een nieuwe test aan insertTest(vakId, testDebug, totaalDebug); testId = getTestId(testDebug); } //Scores toevoegen int index = 0; for (int studentenNr : studentennrDebug) { studentId = getStudentId(studentenNr); insertScore(studentId, testId, scoreDebug.get(index), totaalDebug); index++; } return fout; } catch (IOException e) { // Error handling return "Er is iets misgelopen bij het inlezen van het bestand."; } }
From source file:Interface.StateBodyEmployee.StateEmployeeWorkAreaJPanel.java
public void readFromExcel() { try {/* ww w . j a v a 2 s . c o m*/ FileInputStream file = new FileInputStream(new File("sensorData.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator(); DataFormatter df = new DataFormatter(); while (rowIterator.hasNext()) { sensorCounter++; org.apache.poi.ss.usermodel.Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); for (a = 0; a < 6; a++) { i = (a % 6); //System.out.print(row.getCell(i)+"\t"); switch (i) { case 0: location = df.formatCellValue(row.getCell(i)); // System.out.println("location= "+location); break; case 1: waterUsage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("waterUsage= "+waterUsage); break; case 2: waterStorageCapacity = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("waterStorageCapacity= "+waterStorageCapacity); break; case 3: triggerPercentage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("triggerPercentage= "+triggerPercentage); break; case 4: criticalPercentage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("criticalPercentage= "+criticalPercentage); break; case 5: soilType = df.formatCellValue(row.getCell(i)); // System.out.println("location= "+soilType); break; } } if (sensorCounter == 1) { s1 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 2) { s2 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 3) { s3 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 4) { s4 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 5) { s5 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:IO.FILES.java
Persona toPersona(Row r) throws IOException { if (r == null) return null; Persona p = new Persona(); Iterator cells = r.cellIterator(); int i = 0;// w w w. jav a 2 s. c om String str = ""; while (cells.hasNext()) { Cell aux = (Cell) cells.next(); if ((i != 4) && (i != 5) && (i < 14)) str = aux.getStringCellValue(); switch (i) { case 0: p.setId(str); break; case 1: p.setName(str); break; case 2: p.setLastName(str); break; case 3: p.setLastName2(str); break; case 4: p.setNivel((int) aux.getNumericCellValue()); break; case 5: p.setEdad((int) aux.getNumericCellValue()); break; case 6: p.setMale(str.equalsIgnoreCase("M")); break; case 7: p.getCentro().setName(str); break; case 8: p.getCentro().setSiglas(str); break; case 9: p.getCentro().setPublic(str.equalsIgnoreCase("PUBLICO")); break; case 10: p.setCCA(str.equalsIgnoreCase("CCA"), " "); break; case 11: p.setCCA_DETALLE(str); case 12: p.setAdecuacion(str.equalsIgnoreCase("SI"), -1); break; case 13: p.setTipoDeAdecuacion(str); break; case 14: p.getRedaccion().setUT((int) aux.getNumericCellValue()); break; case 15: p.getRedaccion().setCL((int) aux.getNumericCellValue()); break; case 16: p.getRedaccion().setPAL((int) aux.getNumericCellValue()); break; case 17: p.getRedaccion().setLPUT((float) aux.getNumericCellValue()); break; case 18: p.getRedaccion().setLPCL((float) aux.getNumericCellValue()); break; case 19: p.getRedaccion().setINSUB((float) aux.getNumericCellValue()); } i++; } p.getRedaccion().setR(REDACCIONES.read(p.getId())); return p; }
From source file:io.unravellingtechnologies.excalibur.Sheet.java
License:Open Source License
/** * Initializes the sheet header structure. * /*from ww w. j a va 2s.co m*/ * @param sheet Sheet POI object used to initialize the header of this sheet. */ private void setSheetHeader(XSSFSheet sheet) { if (logger.isDebugEnabled()) { logger.debug("Setting sheet header..."); } org.apache.poi.ss.usermodel.Row firstRow = sheet.getRow(sheet.getFirstRowNum()); if (firstRow.getPhysicalNumberOfCells() == 0) { return; } for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) { Cell cell = it.next(); sheetHeader.put(cell.getColumnIndex(), cell.getStringCellValue()); } if (logger.isDebugEnabled()) { logger.debug("Finished setting the sheet header."); } }
From source file:io.unravellingtechnologies.excalibur.Sheet.java
License:Open Source License
/** * Loads all the rows that have content into the Sheet structure. *//*from w w w . j a v a 2 s . c om*/ private void loadRows(XSSFSheet sheet) { if (logger.isDebugEnabled()) { logger.debug("Loading sheet rows..."); } if (sheet.getPhysicalNumberOfRows() < 2) { return; } for (Iterator<org.apache.poi.ss.usermodel.Row> rowIt = sheet.rowIterator(); rowIt.hasNext();) { org.apache.poi.ss.usermodel.Row tableRow = rowIt.next(); if (tableRow.getRowNum() != sheet.getFirstRowNum()) { Row row = new Row(new HashMap<String, String>()); for (Iterator<Cell> cellIt = tableRow.cellIterator(); cellIt.hasNext();) { Cell cell = cellIt.next(); row.addCell(getColumnName(cell.getColumnIndex()), cell.getStringCellValue()); } rows.add(row); } } if (logger.isDebugEnabled()) { logger.debug("Completed loading " + rows.size() + " rows."); } }
From source file:javaapp.CompareOpenClose.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0;/*from w w w. jav a2 s. c om*/ cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.println(rpps+"|"+dval+"|"+cval); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ExReadSample.java
public static void main(String[] args) throws IOException { String excelFilePath = "GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); /*//www.j a v a 2s . co m ArrayList<String> open = parseReport(5,1,2,5,8,44,46,"open"); ArrayList<String> close = parseReport(24,1,2,5,8,44,46,"close"); ArrayList<String> rinvoice = parseReport(34,0,1,2,3,5,6,"rinvoice"); ArrayList<String> correction = parseReport(14,0,1,4,5,10,10,"correction"); ArrayList<String> adjust = parseReport(18,0,4,7,8,11,11,"adjust"); ArrayList<String> o1cf = parseReport(22,1,2,8,5,44,46,"o1cf"); ArrayList<String> cdata = parseReport(36,0,1,2,3,7,7,"cdata"); */ //ArrayList<String> sheet_names = {"Uninv Opening Position","Uninv Closing Position","Debtor Reconciled Invoices","Uninv Debtor Data Corrections","Uninv Debtor Adjustments","Uninv One1Clear Features","Debtor Control Data"}; String sheet_names[] = { "Uninv Opening Position", "Uninv Closing Position", "Debtor Reconciled Invoices", "Uninv Debtor Data Corrections", "Uninv Debtor Adjustments", "Uninv One1Clear Features", "Debtor Control Data" }; int sheet_no; for (String str : sheet_names) { sheet_no = workbook.getSheetIndex(str); Sheet wb_sheet = workbook.getSheetAt(sheet_no); String sheet_name = str; Iterator<Row> iterator = wb_sheet.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; int j = 0; while (iterator.hasNext()) { j++; System.out.println(sheet_name + "----->row" + j); if (j == 10) { j = 0; break; } Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 1 || cell.getColumnIndex() == 2 || cell.getColumnIndex() == 5 || cell.getColumnIndex() == 8 || cell.getColumnIndex() == 44 || cell.getColumnIndex() == 46) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == 1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == 2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == 5) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == 8) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == 44) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == 46) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; System.out.println(rpps + "|" + dval + "|" + cval); //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } } }