List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:at.mukprojects.exclycore.dao.ExclyCoreReaderTest.java
License:Open Source License
/** * Tests the XLSXReader readLongCellValue function. *///from www .j a va 2 s.c o m @Test public void testXLSXReaderLong() throws Exception { log.debug("Start test for testXLSXReaderLong."); int counter = 0; ReaderTestImpl reader = new ReaderTestImpl(); while (inputRowIterator.hasNext()) { Row inputRow = inputRowIterator.next(); Iterator<Cell> inputCellInterator = inputRow.cellIterator(); while (inputCellInterator.hasNext()) { Cell inputCell = inputCellInterator.next(); log.debug("RowIndex: " + inputCell.getRowIndex() + " - RowExcel: " + (inputCell.getRowIndex() + 1)); switch (counter) { case 0: log.debug("0 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(0L, reader.readLongCellValue(inputCell).getData()); break; case 1: log.debug("1 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(1L, reader.readLongCellValue(inputCell).getData()); break; case 2: log.debug("100 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(100L, reader.readLongCellValue(inputCell).getData()); break; case 3: log.debug("ExclyLongError: ture / " + reader.readLongCellValue(inputCell).isError()); assertTrue("Should be an ExclyLongError.", reader.readLongCellValue(inputCell) instanceof ExclyLongError); break; case 4: log.debug("1000 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(1000L, reader.readLongCellValue(inputCell).getData()); break; case 5: log.debug("2000 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(2000L, reader.readLongCellValue(inputCell).getData()); break; case 6: log.debug("2 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(2L, reader.readLongCellValue(inputCell).getData()); break; case 7: log.debug("1 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(1L, reader.readLongCellValue(inputCell).getData()); break; case 8: log.debug("0 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(0L, reader.readLongCellValue(inputCell).getData()); break; case 9: log.debug("ExclyLongError: ture / " + reader.readLongCellValue(inputCell).isError()); assertTrue("Should be an ExclyLongError.", reader.readLongCellValue(inputCell) instanceof ExclyLongError); break; case 10: log.debug("42297 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(42297L, reader.readLongCellValue(inputCell).getData()); break; case 11: log.debug("ExclyLongError: ture / " + reader.readLongCellValue(inputCell).isError()); assertTrue("Should be an ExclyLongError.", reader.readLongCellValue(inputCell) instanceof ExclyLongError); break; case 12: log.debug("ExclyLongError: false / " + reader.readLongCellValue(inputCell).isError()); assertTrue("Should be no ExclyLongError.", reader.readLongCellValue(inputCell) instanceof ExclyLongBlank); break; case 13: log.debug("42297 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(42297L, reader.readLongCellValue(inputCell).getData()); break; case 14: log.debug("500 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(500L, reader.readLongCellValue(inputCell).getData()); break; case 15: log.debug("ExclyLongError: ture / " + reader.readLongCellValue(inputCell).isError()); assertTrue("Should be an ExclyLongError.", reader.readLongCellValue(inputCell) instanceof ExclyLongError); break; case 16: log.debug("100 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(100L, reader.readLongCellValue(inputCell).getData()); break; case 17: log.debug("1 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(1L, reader.readLongCellValue(inputCell).getData()); break; case 18: log.debug("ExclyLongError: false / " + reader.readLongCellValue(inputCell).isError()); assertTrue("Should be no ExclyLongError.", reader.readLongCellValue(inputCell) instanceof ExclyLongBlank); break; case 19: log.debug("42372 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(42372, reader.readLongCellValue(inputCell).getData()); break; case 20: log.debug("20 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(20, reader.readLongCellValue(inputCell).getData()); break; case 21: log.debug("20 / " + reader.readLongCellValue(inputCell).getData()); assertEquals(20, reader.readLongCellValue(inputCell).getData()); break; case 22: log.debug("ExclyLongError: ture / " + reader.readLongCellValue(inputCell).getData()); assertTrue("Should be an ExclyLongError.", reader.readLongCellValue(inputCell) instanceof ExclyLongError); break; } counter++; } } }
From source file:at.mukprojects.exclycore.dao.ExclyCoreReaderTest.java
License:Open Source License
/** * Tests the XLSXReader readDateCellValue function. *//* ww w. j a v a2s.c o m*/ @Test public void testXLSXReaderDate() throws Exception { log.debug("Start test for testXLSXReaderDate."); int counter = 0; ReaderTestImpl reader = new ReaderTestImpl(); while (inputRowIterator.hasNext()) { Row inputRow = inputRowIterator.next(); Iterator<Cell> inputCellInterator = inputRow.cellIterator(); while (inputCellInterator.hasNext()) { Cell inputCell = inputCellInterator.next(); log.debug("RowIndex: " + inputCell.getRowIndex() + " - RowExcel: " + (inputCell.getRowIndex() + 1)); switch (counter) { case 3: log.debug("true / " + reader.readDateCellValue(inputCell).isError()); assertTrue("Should be an ExclyDateError.", reader.readDateCellValue(inputCell) instanceof ExclyDateError); break; case 10: log.debug("Tue Oct 20 00:00:00 CEST 2015 / " + reader.readDateCellValue(inputCell).getData().toString()); assertTrue("Tue Oct 20 00:00:00 CEST 2015" .equals(reader.readDateCellValue(inputCell).getData().toString())); break; case 11: log.debug("Tue Oct 20 00:00:00 CEST 2015 / " + reader.readDateCellValue(inputCell).getData().toString()); assertTrue("Sat Oct 10 00:00:00 CEST 2015" .equals(reader.readDateCellValue(inputCell).getData().toString())); break; case 13: log.debug("Tue Oct 20 00:00:00 CEST 2015 / " + reader.readDateCellValue(inputCell).getData().toString()); assertTrue("Tue Oct 20 00:00:00 CEST 2015" .equals(reader.readDateCellValue(inputCell).getData().toString())); break; case 19: log.debug("Sun Jan 03 00:00:00 CET 2016 / " + reader.readDateCellValue(inputCell).getData().toString()); assertEquals("Sun Jan 03 00:00:00 CET 2016", reader.readDateCellValue(inputCell).getData().toString()); break; } counter++; } } }
From source file:attandance.standalone.utils.ExcelUtils.java
public static List<AttandanceRecord> readDataFromExcel(String fileName) { List<AttandanceRecord> result = new ArrayList<>(); try {//from w ww.j av a 2 s .c o m FileInputStream file = new FileInputStream(new File(fileName)); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int lineIndex = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int columnIndex = 1; AttandanceRecord lineBean = new AttandanceRecord(); //skip header if (lineIndex == 1) { lineIndex += 1; continue; } while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: cellValue = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; default: cellValue = cell.getStringCellValue(); } lineBean.setAttribute(columnIndex, cellValue); columnIndex = columnIndex + 1; } result.add(lineBean); } file.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return result; }
From source file:au.com.onegeek.lambda.parser.Excel2SeleniumParser.java
License:Apache License
private void parse(InputStream stream) throws CannotCompileException, NotFoundException, CannotCreateTestClassException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { logger.debug("Parsing..."); if (this.dataMap != null && this.tests != null) { return;// ww w.jav a 2s .c o m } this.dataMap = new ArrayList<Map<String, Object>>(); this.tests = new ArrayList<Class<Test>>(); Workbook workbook = null; try { workbook = new XSSFWorkbook(stream); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.debug("workbook" + workbook.toString()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet.getSheetName().startsWith("data")) { // parse key\value pairs HashMap<String, Object> map = new HashMap<String, Object>(); dataMap.add(map); boolean done = false; Row row = sheet.getRow(sheet.getFirstRowNum()); while (!done && row != null && row.getPhysicalNumberOfCells() > 0) { // TODO: parse numerics correctly (i.e. don't add decimal points if not needed) String key = (String) XslxUtil.objectFrom(workbook, row.getCell(0)); String value = null; try { value = (String) XslxUtil.objectFrom(workbook, row.getCell(1)); logger.debug("Adding variable to map: " + key + ":" + value); map.put(key, value); row = sheet.getRow(row.getRowNum() + 1); if (row == null || (row.getRowNum() == sheet.getLastRowNum() + 1)) { done = true; } } catch (NullPointerException e) { //throw new CannotCreateVariableException("No value found for variable '" + key + "' in dataset: " + sheet.getSheetName()); done = true; } } } } JavassistTestBuilderImpl builder = JavassistTestBuilderImpl.getInstance(); // Parse Test sheets into Test objects for (int s = 0; s < workbook.getNumberOfSheets(); s++) { Sheet sheet = workbook.getSheetAt(s); int i = 0; // Ignore data sheets if (sheet.getSheetName().startsWith("suite")) { int maxRows = sheet.getPhysicalNumberOfRows(); int currentRow = sheet.getFirstRowNum(); logger.debug("Nr rows in sheet: " + maxRows); // Create Test Class String testCaseName = "Test" + Excel2SeleniumParser.toCamelCase(sheet.getSheetName()); logger.debug("Creating Test class with name: " + testCaseName); builder.makeTestClass(testCaseName, this.dataMap); boolean testCaseInProgress = false; boolean dataProviderAdded = false; // Get First row, containing the test name and the data to be injected while (i < maxRows) { logger.debug("i: " + i); logger.debug("currentRow: " + currentRow); Row row = sheet.getRow(currentRow); TestCommand command = null; // Check for empty row if (row != null && row.getPhysicalNumberOfCells() != 0) { i++; // Get Cells Iterator<Cell> iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); String cellValue = (cell == null || cell.toString() == "") ? "" : XslxUtil.objectFrom(workbook, cell).toString(); logger.debug("Cell: " + cellValue); if (cellValue.startsWith("test")) { logger.debug("Test case found: " + cellValue + ". Creating Test Case"); // Create new Test CASE try { builder.addTest(cellValue); testCaseInProgress = true; dataProviderAdded = false; } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } break; } else { if (command == null & !cellValue.equals("")) { logger.debug("Command found: " + cellValue + ". Creating new TestCommand"); command = new TestCommand(cellValue); } else if (!cellValue.equals("")) { logger.debug("Command argument found: " + cellValue); command.addParameter(cellValue); } } } } else { // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider if (testCaseInProgress && !dataProviderAdded) { try { logger.debug("In Progress Test Case now being closed off and added to class..."); builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } } try { if (command != null) { logger.debug("Adding command to method"); builder.appendTestToLastMethod(command); } } catch (CannotModifyTestMethodException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class as a variable was not found. Embedded exception: " + e.getMessage()); } currentRow++; } // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider logger.debug( "End of rows...Checking if In Progress Test Case now being closed off and added to class..."); if (testCaseInProgress && !dataProviderAdded) { logger.debug(" In Progress Test Case now being closed off and added to class..."); try { builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } if (testCaseInProgress) { logger.debug("Generating class file"); try { this.tests.add(builder.getCreatedClass()); } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } testCaseInProgress = false; } } } try { stream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.info("Looking at our classes..."); // Look at the Test Objects for (Class<Test> clazz : tests) { logger.info("Class: " + clazz.getName()); for (Method m : clazz.getMethods()) { logger.info("Method: " + m); if (m.getName().equalsIgnoreCase("testRetailDataProvider")) { logger.info("invoking data provider"); Test test = clazz.newInstance(); Object[][] data = (Object[][]) m.invoke(test); for (Object[] obs : data) { for (Object o : obs) { logger.info("data value: " + o); } } } } } }
From source file:automatedhgl.AutomatedHGL.java
public static void main(String[] args) { try {/*w ww. ja v a2 s. c o m*/ FileInputStream excelFile = new FileInputStream(new File(INFILE_NAME)); //create workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(excelFile); //get first desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //create workbook instance to output excel file XSSFWorkbook workbookHGL = new XSSFWorkbook(); //create sheet in output excel file XSSFSheet sheetHGL = workbookHGL.createSheet("HGL"); //iterate through each row one by one Iterator<Row> rowiterator = sheet.iterator(); while (rowiterator.hasNext()) { Row row = rowiterator.next(); //for each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (row.getRowNum() > 7 && count < 23) //to filter column headings { //check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: count++; //assign get value to correct variable if (count == 1) { InletStr = cell.getNumericCellValue(); } else if (count == 2) { OutWSE = cell.getNumericCellValue(); } System.out.print(cell.getNumericCellValue() + " (" + count + ") "); break; case Cell.CELL_TYPE_STRING: count++; /*//assign get value to correct variable if( count == 1 ){InletStr = cell.getStringCellValue();}*/ System.out.print(cell.getStringCellValue() + " (" + count + ") "); break; case Cell.CELL_TYPE_FORMULA: count++; /*//assign get value to correct variable if( count == 1 ){InletStr = cell.getCachedFormulaResultType();}*/ System.out.print(cell.getCachedFormulaResultType() + " (" + count + ") "); break; } } else { count = 0; //reset the count at the end of the row } } System.out.println("return"); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //Output Excel file XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Datatypes in Java"); Object[][] datatypes = { { "Datatype", "Type", "Size(in bytes)" }, { "int", "Primitive", 2 }, { "float", "Primitive", 4 }, { "double", "Primitive", 8 }, { "char", "Primitive", 1 }, { "String", "Non-Primitive", "No fixed size" } }; int rowNum = 0; System.out.println("Creating excel"); for (Object[] datatype : datatypes) { Row row = sheet.createRow(rowNum++); int colNum = 0; for (Object field : datatype) { Cell cell = row.createCell(colNum++); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } try { FileOutputStream outputStream = new FileOutputStream(FILE_NAME); workbook.write(outputStream); workbook.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } System.out.print(InletStr + " "); System.out.print(OutWSE + " "); System.out.println("HGL Done"); }
From source file:b01.officeLink.excel.FocExcelDocument.java
License:Apache License
public void exportLocally(FocObject object) { try {/*from ww w . j a va2s .co m*/ Sheet sheet = workbook.getSheetAt(0); // Iterate over each row in the sheet Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = (Row) rows.next(); System.out.println("Row #" + row.getRowNum()); // Iterate over each cell in the row and print out the cell's content Iterator cells = row.cellIterator(); while (cells.hasNext()) { Cell cell = (Cell) cells.next(); System.out.println("Cell #" + cell.getColumnIndex()); /* System.out.println(String.valueOf(cell.getRichStringCellValue())); */ String str = null; try { str = String.valueOf(cell.getRichStringCellValue()); } catch (Exception e) { Globals.logExceptionWithoutPopup(e); str = ""; } String result = analyseContent(str, object); if (result != null) { if (getWorkbook() != null) { cell.setCellValue(getWorkbook().getCreationHelper().createRichTextString(result)); } } /* * switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: * System.out.println(cell.getNumericCellValue()); String result = * analyseContent(String.valueOf(cell.getNumericCellValue()), object); * if (result != null){ cell.setCellValue(Double.valueOf(result)); } * break; case HSSFCell.CELL_TYPE_STRING: * System.out.println(cell.getRichStringCellValue()); result = * analyseContent(String.valueOf(cell.getRichStringCellValue()), * object); if (result != null){ cell.setCellValue(new * HSSFRichTextString(result)); } break; default: * System.out.println("unsuported cell type"); break; } */} } } catch (Exception e) { e.printStackTrace(); } }
From source file:bandaru_excelreadwrite.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//from w ww. j a va2s . c o m try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { Song song = new Song(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return songList; }
From source file:be.thomasmore.controller.FileController.java
private void leesExcel() { try {/*w ww. j a v a 2 s . c o m*/ //Excelbestand in RAM steken voor Apache POI InputStream fileInputStream = part.getInputStream(); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); XSSFSheet worksheet = workbook.getSheet("Blad1"); EntityManagerFactory emf = Persistence.createEntityManagerFactory("ScoreTrackerPU"); EntityManager em = emf.createEntityManager(); //Iterator om door de worksheets te gaan (enkel nodig om het eerste worksheet door te gaan) Iterator<Row> rowIterator = worksheet.iterator(); //Klas zoeken en persisten //Door de rijen itereren while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Over de kolommen van deze rij itereren Iterator<Cell> cellIterator = row.cellIterator(); if (row.getRowNum() == 0) { while (cellIterator.hasNext()) { //Cell vastnemen Cell cell = cellIterator.next(); //Kijken of er in de eerste cell 'klas' staat switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getStringCellValue().equalsIgnoreCase("klas")) { //breaken zodat hij doorgaat naar de volgende cell break; //Checken of de cell niet leeg is } else if (!cell.getStringCellValue().equalsIgnoreCase("")) { if (klas.getNummer() == null) { //Klas werkt Query q = em.createNamedQuery("Klas.findByNummer"); q.setParameter("nummer", cell.getStringCellValue()); if (q.getResultList().size() == 0) { klas.setNummer(cell.getStringCellValue()); defaultService.addKlas(klas); } else { klas = (Klas) q.getSingleResult(); } } } break; } } //Einde van celliterator } else if (row.getRowNum() == 1) { while (cellIterator.hasNext()) { //Cell vastnemen Cell cell = cellIterator.next(); //Kijken of in de allereerste cel 'vak' staat switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getStringCellValue().equalsIgnoreCase("vak")) { //breaken zodat hij doorgaat naar de volgende cell break; } else if (!cell.getStringCellValue().equalsIgnoreCase("")) { if (vak.getNaam() == null) { Query q = em.createNamedQuery("Vak.findByNaam"); q.setParameter("naam", cell.getStringCellValue()); if (q.getResultList().size() == 0) { vak.setNaam(cell.getStringCellValue()); defaultService.addVak(vak); } else { vak = (Vak) q.getSingleResult(); } } } } } //Einde van celliterator } else if (row.getRowNum() == 2) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 1) { test.setBeschrijving(cell.getStringCellValue()); } } } else if (row.getRowNum() == 3) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("totaal")) { } if (cell.getColumnIndex() == 1) { test.setTotaalScore((int) cell.getNumericCellValue()); test.setVakId(vak); /// Query q = em.createNamedQuery("Test.findByBeschrijving"); q.setParameter("beschrijving", test.getBeschrijving()); if (q.getResultList().size() == 0) { defaultService.addTest(test); } else { test = (Test) q.getSingleResult(); } /// klasTest.setKlasId(klas); klasTest.setTestId(test); Query q2 = em.createNamedQuery("Klastest.findByKlasIdTestId"); q2.setParameter("klasId", klasTest.getKlasId()); q2.setParameter("testId", klasTest.getTestId()); if (q2.getResultList().size() == 0) { if (klasTest.getKlasId().getId() != null) { defaultService.addKlastest(klasTest); } } else { klasTest = (Klastest) q2.getSingleResult(); } } } } else if (row.getRowNum() > 5) { Student student = new Student(); Score score = new Score(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("zit al in de DB")) { break; } if (cell.getColumnIndex() == 0) { if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { student.setStudentenNr((int) cell.getNumericCellValue()); } } if (cell.getColumnIndex() == 1) { String[] voorenachternaam = cell.getStringCellValue().split("\\s+"); student.setVoornaam(voorenachternaam[0]); if (voorenachternaam.length >= 3) { if (voorenachternaam.length >= 4) { student.setNaam( voorenachternaam[1] + voorenachternaam[2] + voorenachternaam[3]); student.setEmail(voorenachternaam[0] + "." + voorenachternaam[1] + voorenachternaam[2] + voorenachternaam[3] + "@student.thomasmore.be"); } else { student.setNaam(voorenachternaam[1] + voorenachternaam[2]); student.setEmail(voorenachternaam[0] + "." + voorenachternaam[1] + voorenachternaam[2] + "@student.thomasmore.be"); } } else { student.setNaam(voorenachternaam[1]); student.setEmail( voorenachternaam[0] + "." + voorenachternaam[1] + "@student.thomasmore.be"); } student.setKlasId(klas); } if (cell.getColumnIndex() == 2) { score.setScore((int) cell.getNumericCellValue()); score.setTestId(test); score.setStudentId(student); break; } } if (student.getStudentenNr() != null) { studenten.add(student); } if (score.getTestId() != null && score.getStudentId().getStudentenNr() != null) { scores.add(score); } } } //einde van rowiterator for (Student student : studenten) { Query q = em.createNamedQuery("Student.findByStudentenNr"); q.setParameter("studentenNr", student.getStudentenNr()); if (q.getResultList().size() == 0) { defaultService.addStudent(student); } else { Student st = (Student) q.getSingleResult(); student.setId(st.getId()); } } for (Score score : scores) { Query q = em.createNamedQuery("Score.findByTestIdStudentIdScore"); q.setParameter("testId", score.getTestId()); q.setParameter("studentId", score.getStudentId()); q.setParameter("score", score.getScore()); if (q.getResultList().size() == 0) { defaultService.addScore(score); } else { score = (Score) q.getSingleResult(); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:be.thomasmore.controller.InputBean.java
private void leesExcel(String path) { try {/*from w ww. ja va 2 s .c om*/ //declaratie en blad uit excel selecteren enzo FileInputStream fileInputStream = new FileInputStream(path); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); XSSFSheet worksheet = workbook.getSheet("Blad1"); // XSSFRow row1 = worksheet.getRow(0); // XSSFCell cellA1 = row1.getCell((short) 0); // String a1Val = cellA1.getStringCellValue(); // XSSFCell cellB1 = row1.getCell((short) 1); // String b1Val = cellB1.getStringCellValue(); // // XSSFRow row2 = worksheet.getRow(1); // XSSFCell cellA2 = row2.getCell((short) 0); // String a2Val = cellA2.getStringCellValue(); // XSSFCell cellB2 = row2.getCell((short) 1); // String b2Val = cellB2.getStringCellValue(); // // XSSFRow row7 = worksheet.getRow(6); // int a7Val = (int) row7.getCell((short) 0).getNumericCellValue(); // String b7Val = row7.getCell((short) 1).getStringCellValue(); // int c7Val = (int) row7.getCell((short) 2).getNumericCellValue(); // // XSSFRow row8 = worksheet.getRow(7); // int a8Val = (int) row8.getCell((short) 0).getNumericCellValue(); // String b8Val = row8.getCell((short) 1).getStringCellValue(); // int c8Val = (int) row8.getCell((short) 2).getNumericCellValue(); // // XSSFRow row9 = worksheet.getRow(8); // int a9Val = (int) row9.getCell((short) 0).getNumericCellValue(); // String b9Val = row9.getCell((short) 1).getStringCellValue(); // int c9Val = (int) row9.getCell((short) 2).getNumericCellValue(); // System.out.println("A1: " + a1Val); // System.out.println("B1: " + b1Val); // System.out.println("A2: " + a2Val); // System.out.println("B2: " + b2Val); // System.out.println("Studentnr - naam - score"); // System.out.println(a7Val + " " + b7Val + " " + c7Val); // System.out.println(a8Val + " " + b8Val + " " + c8Val); // System.out.println(a9Val + " " + b9Val + " " + c9Val); //iterator dat door alle rijen gaat van het excel-blad Iterator<Row> rowIterator = worksheet.iterator(); Test test = new Test(); //test aanmaken String klasNaam = ""; Long klasId = 0L; while (rowIterator.hasNext()) { //als er nog een rij bestaat die niet leeg is Row row = rowIterator.next(); //row-object aanmaken van huidige rij if (row.getRowNum() == 0) { //als de nummer van de rij = 0 (dus de 0de rij van het excel bestand = klas) Iterator<Cell> cellIterator = row.cellIterator(); //voor deze rij elke cel in deze rij afgaan met een iterator while (cellIterator.hasNext()) { //als er nog een cell bestaat die niet leeg is Cell cell = cellIterator.next(); //cell-object aanmaken van huidige cell if (!cell.getStringCellValue().equals("klas")) { //als er het woord "klas" in de cell staat, deze overslaan. Als de cel van de 0de rij (klas-rij) iets anders is dan "klas" dus (=A1 in excel) switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: //als het type van de cel een string is Klas klas = new Klas(); // klas-object aanmaken klasNaam = cell.getStringCellValue(); klas.setNaam(cell.getStringCellValue()); //naam van klas instellen op de waarde van de cell List<Klas> alleKlassen = javaProject7Service.getAllKlassen(); boolean bestaatAl = false; for (Klas alleKlas : alleKlassen) { if (alleKlas.getNaam().equals(klasNaam)) { bestaatAl = true; } } if (bestaatAl) { klasId = javaProject7Service.addKlas(klas); } break; } } } } //volgende if is hetzelfde principe als vorige enkel voor een andere rij if (row.getRowNum() == 1) { //nummer van de rij = 1 (dus eigenlijk in excel de 2de rij) Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (!cell.getStringCellValue().equals("Vak")) { //als er het woord "Vak" in de cell staat, deze overslaan switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: //hier moet nog code komen om het vak toe te voegen aan het Test-object (zie regel 196) break; } } } } //weer hetzelfde principe als hierboven if (row.getRowNum() > 5) { // enkel voor de rijen 5 en hoger (dus enkel studenten) Iterator<Cell> cellIterator = row.cellIterator(); Student student = new Student(); //nieuw student-object aanmaken per rij while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //als de cell een int is student.setStudentnr((int) cell.getNumericCellValue()); //Klas klas = javaProject7Service.getKlasByNaam(klasNaam); //klas ophalen uit db adhv klasnaam student.setKlasId(klasId); break; case Cell.CELL_TYPE_STRING: //als de cell een string is if (cell.getStringCellValue().equals("zit al in de DB") || cell.getStringCellValue() != null || cell.getStringCellValue().equals("")) { //als de cell "zit al in de DB" bevat, niets doen (zie excel; laatste regel) break; } else { String volledigeNaam = cell.getStringCellValue(); String[] delen = volledigeNaam.split(" "); student.setVoornaam(delen[0]); student.setAchternaam(delen[1]); break; } } javaProject7Service.addStudent(student); //student toevoegen aan studenten list } } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:br.com.gartech.nfse.integrador.view.ImportarView.java
public List<FaturamentoImport> readFaturamentoExcelFile(String excelFilePath) throws IOException { List<FaturamentoImport> listaFaturamento = new ArrayList<>(); FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); FaturamentoImport aFaturamentoImport = new FaturamentoImport(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: if (nextRow.getRowNum() == 0) { if (!getCellValue(nextCell).equals("CAMPUS")) { }//from ww w . j a va2 s. c o m } else { aFaturamentoImport.setCampus((String) getCellValue(nextCell)); } break; case 1: aFaturamentoImport.setCampus((String) getCellValue(nextCell)); break; case 2: aFaturamentoImport.setNumero((String) getCellValue(nextCell)); break; // case 3: // aFaturamentoImport.setTipo_fatura((String) getCellValue(nextCell)); // break; // case 4: // aFaturamentoImport.setAnocompetencia((Integer) getCellValue(nextCell)); // break; // case 5: // aFaturamentoImport.setMescompetencia((int) getCellValue(nextCell)); // break; // case 6: // aFaturamentoImport.setCodaluno((int) getCellValue(nextCell)); // break; // case 7: // aFaturamentoImport.setAlunome((String) getCellValue(nextCell)); // break; // case 8: // aFaturamentoImport.setCodcurso((int) getCellValue(nextCell)); // break; // case 9: // aFaturamentoImport.setCurso((String) getCellValue(nextCell)); // break; // case 10: // aFaturamentoImport.setTipocurso((String) getCellValue(nextCell)); // break; // case 11: // aFaturamentoImport.setAluendereco((String) getCellValue(nextCell)); // break; // case 12: // aFaturamentoImport.setAlubairro((String) getCellValue(nextCell)); // break; // case 13: // aFaturamentoImport.setAlucep((String) getCellValue(nextCell)); // break; // case 14: // aFaturamentoImport.setCiddesc((String) getCellValue(nextCell)); // break; // case 15: // aFaturamentoImport.setCiduf((String) getCellValue(nextCell)); // break; // case 16: // aFaturamentoImport.setAlutelefone((String) getCellValue(nextCell)); // break; // case 17: // aFaturamentoImport.setAlutelefone2((String) getCellValue(nextCell)); // break; // case 18: // aFaturamentoImport.setAluemail((String) getCellValue(nextCell)); // break; // case 19: // aFaturamentoImport.setAlucpf((String) getCellValue(nextCell)); // break; case 20: aFaturamentoImport.setValor((float) getCellValue(nextCell)); break; } } listaFaturamento.add(aFaturamentoImport); } //workbook.close; inputStream.close(); return listaFaturamento; }