List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:action.ResultAction.java
public String callReadExecl() { System.out.println("we are here"); String flag = "success"; try {/*w w w . j av a 2 s.c o m*/ XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myExcelSheet)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 0; row = rowIterator.next(); while (rowIterator.hasNext()) { row = rowIterator.next(); System.out.println("iterator Strated"); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int pra[] = new int[4]; int sub[] = new int[5]; int j = 0, k = 0, total = 0; while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: int a = (int) cell.getNumericCellValue(); total = total + a; if (j < 5) { sub[j] = a; j++; } else if (k < 4) { pra[k] = a; k++; } if (i == 2) { stdR.setSemester(a); } else if (i == 3) { stdR.setSub1(a); } else if (i == 4) { stdR.setSub2(a); } else if (i == 5) { stdR.setSub3(a); } else if (i == 6) { stdR.setSub4(a); } else if (i == 7) { stdR.setSub5(a); } else if (i == 8) { stdR.setPra1(a); } else if (i == 9) { stdR.setPra2(a); } else if (i == 10) { stdR.setPra3(a); } else if (i == 11) { stdR.setPra4(a); } else if (i == 12) { stdR.setTotal(a); } else if (i == 13) { stdR.setPercentage((long) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: { if (i == 0) { stdR.setStdRoll(cell.getStringCellValue()); } else if (i == 1) { stdR.setBranch(cell.getStringCellValue()); } } break; } i++; } i = 0; stdR.setTotal(total); System.out.println("Total"); System.out.println("Student marks : " + stdR); subId.setSemid(stdR.getSemester()); subId.setBranch(stdR.getBranch()); subject = subDao.getAllSubject(subId); stdEx.setStdRollEx(stdR.getStdRoll()); getStudentExSubject(stdEx); if (stdR.getPra1() < 13) { list.add(subject.getPra1() + "_"); } if (stdR.getPra2() < 13) { list.add(subject.getPra2() + "_"); } if (stdR.getPra3() < 13) { list.add(subject.getPra3() + "_"); } if (stdR.getPra4() < 13) { list.add(subject.getPra4() + "_"); } if (stdR.getSub1() < 25) { list.add(subject.getSub1() + "_"); } if (stdR.getSub2() < 25) { list.add(subject.getSub2() + "_"); } if (stdR.getSub3() < 25) { list.add(subject.getSub3() + "_"); } if (stdR.getSub4() < 25) { list.add(subject.getSub4() + "_"); } if (stdR.getSub5() < 25) { list.add(subject.getSub5() + "_"); } String subj = ""; for (int ii = 0; ii < list.size(); ii++) { subj += list.get(ii); } System.out.println("Subject Ka object : " + subject); System.err.println("Student Object : " + stdDetails); System.err.println("Student Marks Object : " + stdR); System.err.println("Student Ex : " + stdEx); stdEx.setStudentExDetailscol(subj); stdDetails.setStdRollnumber(stdR.getStdRoll()); stdDetails = stdDao.getSingleSudentData(stdDetails); stdDetails.setStdMarks(stdR); stdDetails.setStudentexdetails(stdEx); stdR.setStudentDetails(stdDetails); stdEx.setStudentDetails(stdDetails); //flag = stdDao.updateStudent(stdDetails); System.out.println("Subject Ka object : " + subject); System.err.println("Student Object : " + stdDetails); System.err.println("Student Marks Object : " + stdR); System.err.println("Student Ex : " + stdEx); callsaveMarks(); System.out.println("iteration ended"); } System.out.println(""); } catch (Exception ex) { ex.printStackTrace(); } System.out.println("We end"); return flag; }
From source file:action.StudentAction.java
public String callGetStudentExcel() { System.out.println("we are here"); try {//from w ww. j ava2 s . co m XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myExcelSheet)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 0; row = rowIterator.next(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (i == 5) { stdDetails.setStdSemester((int) cell.getNumericCellValue()); } else if (i == 7) { stdDetails.setStdContact((long) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: { if (i == 0) { stdDetails.setStdRollnumber(cell.getStringCellValue()); } else if (i == 1) { stdDetails.setStdName(cell.getStringCellValue()); } else if (i == 2) { stdDetails.setStdEmail(cell.getStringCellValue()); } else if (i == 3) { stdDetails.setStdBatch(cell.getStringCellValue()); } else if (i == 4) { stdDetails.setStdBranch(cell.getStringCellValue()); } else if (i == 6) { stdDetails.setStdAddress(cell.getStringCellValue()); } } break; } i++; } i = 0; callSaveStudent(); System.out.println("Our Obeject"); System.out.println(stdDetails); } System.out.println(""); } catch (Exception ex) { ex.printStackTrace(); } System.out.println("We end"); return SUCCESS; }
From source file:ADP_Streamline.MatrixReader.java
public String check() throws Exception { List<String> ClientRoles = new ArrayList<>(); List<String> PartnerRoles = new ArrayList<>(); List<String> ADPRoles = new ArrayList<>(); List<String> Rights = new ArrayList<>(); String rightscolumn = ""; String rights = ""; String roles = ""; String cellwithx;//w w w . j ava2s.co m int righstrow = 0; int columncount; int rowcount; int roleslenght; int rightslenght = 0; Boolean client = false; Boolean partner = false; Boolean adp; Boolean rightstart = false; Iterator<Row> rowIterator; Iterator<Cell> cellIterator; Row row; Cell cell; OracleJDBC oracle = new OracleJDBC(); try { FileInputStream file = new FileInputStream( new File("C:\\Users\\frodri1\\Documents\\SPM 1.2_RoleMatrix_Demo.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 rowIterator = sheet.iterator(); rowcount = 2; columncount = 0; while (rowIterator.hasNext()) { row = rowIterator.next(); //if(adp) {break;} adp = false; //For each row, iterate through all the columns cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); if (cell.getStringCellValue().contentEquals("Right")) { rightscolumn = CellReference.convertNumToColString(cell.getColumnIndex()); righstrow = cell.getRowIndex(); rightstart = true; } if (cell.getStringCellValue().contentEquals("Client")) { client = true; } if (cell.getStringCellValue().contentEquals("Partner")) { partner = true; client = false; } if (cell.getStringCellValue().contentEquals("ADP")) { partner = false; client = false; adp = true; } if (client) { ClientRoles.add( CellIteration(sheet, CellReference.convertNumToColString(cell.getColumnIndex()), righstrow, columncount, 0).trim()); } if (partner) { PartnerRoles.add( CellIteration(sheet, CellReference.convertNumToColString(cell.getColumnIndex()), righstrow, columncount, 0).trim()); } if (adp) { ADPRoles.add( CellIteration(sheet, CellReference.convertNumToColString(cell.getColumnIndex()), righstrow, columncount, 0).trim()); } } if (rightstart) { rights = CellIteration(sheet, rightscolumn, righstrow, columncount, rowcount); if (!"".equals(rights)) { Rights.add(rights.trim()); rightslenght++; rowcount++; } else break; } } roleslenght = ClientRoles.size() + PartnerRoles.size() + ADPRoles.size(); for (int i = 0; i < rightslenght; i++) { for (int l = 0; l < roleslenght; l++) { cellwithx = CellIteration(sheet, rightscolumn, righstrow, l + 1, i + 2); if ("x".equals(cellwithx)) { if (l < ClientRoles.size()) { rights = Rights.get(i); roles = ClientRoles.get(l); oracle.check(rights, roles); } if (l >= ClientRoles.size() && l < (ClientRoles.size() + PartnerRoles.size())) { rights = Rights.get(i); roles = PartnerRoles.get(l - ClientRoles.size()); } if (l >= ClientRoles.size() + PartnerRoles.size()) { rights = Rights.get(i); roles = ADPRoles.get(l - (ClientRoles.size() + PartnerRoles.size())); } } } } } catch (Exception e) { e.printStackTrace(); } return ""; }
From source file:Algorithm.Method1.java
public String[] MethodTest(String Path) { StringBuffer keyword1 = new StringBuffer(); try {//from w w w . j av a 2 s. c o m Hashtable hash = new Hashtable(); FileInputStream file = new FileInputStream(new File(Path)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet1 = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet1.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Row rowNew =sheetNew.createRow(rowNumNew++); //For each row, iterate through all the columns Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); // Cell cellNew =rowNew.createCell(cellNumNew++); //Check the cell type and format accordingly switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: String result = cell.getStringCellValue(); if (hash.containsKey(result)) { int f = Integer.parseInt(hash.get(result).toString()); f++; hash.put(result, f); } else { hash.put(result, 1); } } } //System.out.println(""); } XSSFWorkbook workbookNew = new XSSFWorkbook(); XSSFSheet sheetNew = workbookNew.createSheet("test"); int rowNum = 0; Set s = hash.keySet(); String key = new String(); int t = 0; for (Iterator<String> i = s.iterator(); i.hasNext();) { key = i.next(); Row rowNew = sheetNew.createRow(rowNum); org.apache.poi.ss.usermodel.Cell cellNew = rowNew.createCell(0); cellNew.setCellValue(key); keyword1.append(key + " "); org.apache.poi.ss.usermodel.Cell cellNew2 = rowNew.createCell(1); cellNew2.setCellValue(hash.get(key).toString()); rowNum++; //sheet2.addCell(new Label(0,t , key)); //System.out.println(hash.get(key)); //sheet2.addCell(new Label(1,t , hash.get(key).toString())); t++; } FileOutputStream fileOut = new FileOutputStream(new File(Path.replace("???.xlsx", "method1.xlsx")));//new file workbookNew.write(fileOut); fileOut.close(); file.close(); // Workbook book = Workbook.getWorkbook(new File("n.xls")); //WritableWorkbook book2 = Workbook.createWorkbook(new File("method1.xls")); // // WritableSheet sheet2 = book2.createSheet("num1", 0); // //Sheet sheet = book.getSheet(0); //int rownum = sheet.getRows();// /** Cell cell; for(int i = 0;i<rownum;i++){ cell = sheet.getCell(0,i); String result = cell.getContents(); if(hash.containsKey(result)){ int f = Integer.parseInt(hash.get(result).toString()); f++; hash.put(result, f); }else{ hash.put(result, 1); } } */ //?? /* Set s=hash.keySet(); String key = new String(); int t = 0; for(Iterator<String> i=s.iterator();i.hasNext();){ key = i.next(); sheet2.addCell(new Label(0,t , key)); //System.out.println(hash.get(key)); sheet2.addCell(new Label(1,t , hash.get(key).toString())); t++; } book2.write(); book2.close(); */ // book.close(); System.out.print("method1"); } catch (Exception e) { System.out.println(e); } return keyword1.toString().split(" "); }
From source file:Algorithm.Method1.java
public String[] method1Out(String filepath) throws Exception { File fileout = new File("pp.txt"); PrintWriter out1 = new PrintWriter(fileout); String[] out = null;/* w w w . j a v a2s.c o m*/ ArrayList outList = new ArrayList<String>(); String result; Hashtable hash = new Hashtable(); FileInputStream file = new FileInputStream(new File(filepath)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet1 = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet1.iterator(); //???excel????? while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); result = cell.getStringCellValue().trim(); if (hash.containsKey(result)) { int f = Integer.parseInt(hash.get(result).toString()); f++; hash.put(result, f); } else { hash.put(result, 1); } } //end while } //end while //???tf-idf Hashtable hash_2 = new Hashtable(); File filein = new File("tfidf.txt"); Scanner input = new Scanner(filein); String s; double d; while (input.hasNext()) { s = input.next().trim(); d = input.nextDouble(); hash_2.put(s, d); } //?? Set ss = hash.keySet(); String key = new String(); //tf-idf for (Iterator<String> i = ss.iterator(); i.hasNext();) { key = i.next().trim(); if (hash_2.containsKey(key)) { int tf = Integer.parseInt(hash.get(key).toString()); double idf = Double.parseDouble(hash_2.get(key).toString()); hash.put(key, tf * idf); //out1.println(key+" "+tf*idf); } else { hash.put(key, 0.1); //out1.println(key+" "+0.1); } } key = null; //tf-idf? Set ss2 = hash.keySet(); String tmp = ""; int size = hash.size(); for (int j = 0; j < size; j++) { ss2 = hash.keySet(); double max = 0; for (Iterator<String> i = ss2.iterator(); i.hasNext();) { key = i.next().trim(); double xxoo = Double.parseDouble(hash.get(key).toString()); if (xxoo > max) { //?? max = xxoo; tmp = key; } } hash.remove(tmp); ss = hash.keySet(); if (!outList.contains(tmp)) outList.add(tmp); } //arraylist? out = (String[]) outList.toArray(new String[1]); //out1.close(); /* for(int i=0;i<out.length;i++) { System.out.print(out[i]); } */ return out; }
From source file:Algorithm.Method2.java
public String[] MethodTest(String Path) { StringBuffer keyword1 = new StringBuffer(); try {/*www . java 2s . com*/ /** * ??excel * src * map? * result */ String srcFilePath = Path; String mapFilePath = "??.xlsx"; String newFilePath = Path.replace("???.xlsx", "method2.xlsx"); FileInputStream srcFile = new FileInputStream(new File(srcFilePath)); FileInputStream mapFile = new FileInputStream(new File(mapFilePath)); XSSFWorkbook srcWorkbook = new XSSFWorkbook(srcFile); XSSFWorkbook mapWorkbook = new XSSFWorkbook(mapFile); XSSFWorkbook newWorkbook = new XSSFWorkbook(); XSSFSheet srcSheet = srcWorkbook.getSheetAt(0); XSSFSheet mapSheet = mapWorkbook.getSheetAt(0); XSSFSheet newSheet = newWorkbook.createSheet("test"); Iterator<Row> srcRowIterator = srcSheet.iterator(); Iterator<Row> mapRowIterator = mapSheet.iterator(); //map.xlst???mapList ArrayList mapList = new ArrayList(); while (mapRowIterator.hasNext()) { Row row = mapRowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String test = cell.getStringCellValue(); mapList.add(test); } } //???mapArray,? int mapListLength = mapList.size(); String[] mapArray = (String[]) mapList.toArray(new String[mapListLength]); //??mapResult,mapArray?src //,mapArray[i]src,mapResult[i]1?0 int[] mapResult = new int[mapListLength]; for (int i = 0; i < mapListLength; i++) { mapResult[i] = 0; } //?src.xlsx,?mapArray while (srcRowIterator.hasNext()) { Row row = srcRowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String test = cell.getStringCellValue().trim(); for (int i = 0; i < mapListLength; i++) { if (test.equals(mapArray[i])) mapResult[i] = 1; } } } //?mapResult,mapArrayresult int newRowNum = 0; for (int i = mapListLength - 1; i >= 0; i--) { if (mapResult[i] == 1) { Row newRow = newSheet.createRow(newRowNum++); Cell newCell = newRow.createCell(0); String test = mapArray[i]; keyword1.append(test + " "); newCell.setCellValue(test); } } srcFile.close(); mapFile.close(); //FileOutputStream fileOut = new FileOutputStream(new File(newFilePath)); // newWorkbook.write(fileOut); // fileOut.close(); // System.out.print("method2"); } catch (Exception e) { System.out.println(e); } return keyword1.toString().split(" "); }
From source file:Algorithm.SegmentationAndNounFilter.java
public String[] SegmentationNounFilter(String filepath, String filename) { try {//ww w . java 2 s.c o m String mingciPath = filepath.replace(filename, "???.xlsx"); ICTCLAS50 testICTCLAS50 = new ICTCLAS50(); String argu = "."; if (testICTCLAS50.ICTCLAS_Init(argu.getBytes("GB2312")) == false) { System.out.println("Init Fail"); } else { System.out.println("Init Succeed!"); } StringBuffer input = new StringBuffer(); FileInputStream file = new FileInputStream(new File(filepath)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet1 = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet1.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Row rowNew =sheetNew.createRow(rowNumNew++); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); // Cell cellNew =rowNew.createCell(cellNumNew++); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: break; case Cell.CELL_TYPE_STRING: input.append(cell.getStringCellValue()); } } //System.out.println(""); } //? byte nativeBytes[] = testICTCLAS50.ICTCLAS_ParagraphProcess(input.toString().getBytes("GB2312"), 0, 1); String nativeStr = new String(nativeBytes, 0, nativeBytes.length, "GB2312"); //xlsx XSSFWorkbook workbookNew = new XSSFWorkbook(); XSSFSheet sheetNew = workbookNew.createSheet("test"); int rowNum = 0; // WritableWorkbook book = Workbook.createWorkbook(new File("n.xls")); // WritableSheet sheet = book.createSheet("num1", 0); Scanner in = new Scanner(nativeStr); int i = 0;// while (in.hasNext()) { String ss = in.next(); Pattern pattern = Pattern.compile("(.+?)/n.*"); Matcher matcher = pattern.matcher(ss); if (matcher.find() && matcher.group(1).length() > 1 && !isDigit(matcher.group(1))) { //label = new jxl.write.Label(0, i, matcher.group(1));//? //sheet.addCell(label); Row rowNew = sheetNew.createRow(rowNum++); Cell cellNew = rowNew.createCell(0); cellNew.setCellValue(matcher.group(1)); //i++; } } // book.write(); // book.close(); FileOutputStream fileOut = new FileOutputStream(new File(mingciPath));//new file workbookNew.write(fileOut); fileOut.close(); //?? file.close(); testICTCLAS50.ICTCLAS_Exit(); } catch (Exception ex) { } return null; }
From source file:at.mukprojects.exclycore.dao.ExclyCoreReaderTest.java
License:Open Source License
@Before public void setUp() throws FileNotFoundException, IOException { log.debug("SetUp ExclyCoreTest."); File inputFile = new File(RESFILE); inputStream = new FileInputStream(inputFile); inputWorkbook = new XSSFWorkbook(inputStream); XSSFSheet inputSheet = inputWorkbook.getSheet("Test"); inputRowIterator = inputSheet.iterator(); }
From source file:automatedhgl.AutomatedHGL.java
public static void main(String[] args) { try {/* w w w .j a v a 2s. 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:be.thomasmore.controller.FileController.java
private void leesExcel() { try {/*from w w w .ja v a 2 s .co 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(); } }