List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet
@Override
public XSSFSheet getSheet(String name)
From source file:achmad.rifai.admin.ui.Opener.java
private void penjualan() { try {/* ww w . j av a2 s . com*/ achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("penjualan"); List<achmad.rifai.erp1.entity.Penjualan> l = new java.util.LinkedList<>(); int x = 2; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.Penjualan p = new achmad.rifai.erp1.entity.Penjualan(); int y = x; org.apache.poi.xssf.usermodel.XSSFRow r1 = s.getRow(x), r2 = s.getRow(y); List<achmad.rifai.erp1.entity.ItemJual> l1 = new java.util.LinkedList<>(); boolean trus = true; while (trus || null == r2.getCell(0)) { achmad.rifai.erp1.entity.ItemJual i = new achmad.rifai.erp1.entity.ItemJual(); i.setBarang(r2.getCell(3).getStringCellValue()); i.setJumlah(Integer.parseInt(r2.getCell(4).getStringCellValue())); i.setUang(org.joda.money.Money.parse(r2.getCell(5).getStringCellValue())); trus = false; l1.add(i); y++; r2 = s.getRow(y); } p.setItems(l1); p.setNota(r1.getCell(0).getStringCellValue()); p.setPelanggan(r1.getCell(1).getStringCellValue()); p.setTgl(java.sql.Date.valueOf(r1.getCell(2).getStringCellValue())); p.setTotal(org.joda.money.Money.parse(r1.getCell(6).getStringCellValue())); p.setTerbayar(org.joda.money.Money.parse(r1.getCell(7).getStringCellValue())); p.setKet(r1.getCell(8).getStringCellValue()); p.setDeleted(Boolean.parseBoolean(r1.getCell(9).getStringCellValue())); x = y + 1; st = s.getRow(x).getCell(0).getStringCellValue(); l.add(p); } progPenjualan.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOPenjualan(d).insert(l.get(c)); progPenjualan.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progPenjualan.setValue(100); }
From source file:achmad.rifai.admin.ui.Opener.java
private void pesan() { try {/*from ww w.j a va 2 s . c om*/ achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("pesan"); List<achmad.rifai.erp1.entity.Pesan> l = new java.util.LinkedList<>(); int x = 2; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.Pesan p = new achmad.rifai.erp1.entity.Pesan(); int y = x; org.apache.poi.xssf.usermodel.XSSFRow r1 = s.getRow(x), r2 = s.getRow(y); List<achmad.rifai.erp1.entity.Penerima> l1 = new java.util.LinkedList<>(); boolean trus = true; while (trus || null == r2.getCell(0)) { achmad.rifai.erp1.entity.Penerima pe = new achmad.rifai.erp1.entity.Penerima(); pe.setAkun(r2.getCell(3).getStringCellValue()); pe.setTerbaca(Boolean.parseBoolean(r2.getCell(4).getStringCellValue())); trus = false; y++; r2 = s.getRow(y); l1.add(pe); } p.setKe(l1); p.setKode(r1.getCell(0).getStringCellValue()); p.setPengirim(r1.getCell(1).getStringCellValue()); p.setWaktu(org.joda.time.DateTime.parse(r1.getCell(2).getStringCellValue())); p.setPesan(r1.getCell(5).getStringCellValue()); p.setDeleted(Boolean.parseBoolean(r1.getCell(6).getStringCellValue())); x = y + 1; st = s.getRow(x).getCell(0).getStringCellValue(); l.add(p); } progPesan.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOPesan(d).insert(l.get(c)); progPesan.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progPesan.setValue(100); }
From source file:achmad.rifai.admin.ui.Opener.java
private void rekening() { try {// www.j av a 2 s . com achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("Rekening"); List<achmad.rifai.erp1.entity.Rekening> l = new java.util.LinkedList<>(); int x = 1; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.Rekening r = new achmad.rifai.erp1.entity.Rekening(); org.apache.poi.xssf.usermodel.XSSFRow ro = s.getRow(x); r.setKode(ro.getCell(0).getStringCellValue()); r.setGolongan(ro.getCell(1).getStringCellValue()); r.setPosisi(ro.getCell(2).getStringCellValue()); r.setKet(ro.getCell(3).getStringCellValue()); r.setDeleted(Boolean.parseBoolean(ro.getCell(4).getStringCellValue())); x++; st = s.getRow(x).getCell(0).getStringCellValue(); l.add(r); } progRekening.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAORekening(d).insert(l.get(c)); progRekening.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progRekening.setValue(100); }
From source file:achmad.rifai.admin.ui.Opener.java
private void suplier() { try {//ww w . j a v a 2 s . c o m achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("Pemasok"); List<achmad.rifai.erp1.entity.Suplier> l = new java.util.LinkedList<>(); int x = 1; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.Suplier su = new achmad.rifai.erp1.entity.Suplier(); int y = x, c, z = x; org.apache.poi.xssf.usermodel.XSSFRow r1 = s.getRow(x), r2 = s.getRow(y), r3 = s.getRow(z); List<String> l1 = new java.util.LinkedList<>(), l2 = new java.util.LinkedList<>(); boolean trus = true; while (trus || (null == r2.getCell(0) && !r2.getCell(2).getStringCellValue().isEmpty())) { trus = false; l1.add(r2.getCell(2).getStringCellValue()); y++; r2 = s.getRow(y); } su.setAlamat(l1); trus = true; while (trus || (null == r3.getCell(0) && !r3.getCell(3).getStringCellValue().isEmpty())) { trus = false; l2.add(r3.getCell(3).getStringCellValue()); z++; r3 = s.getRow(z); } su.setTelp(l2); if (y > z) c = y; else c = z; su.setKode(r1.getCell(0).getStringCellValue()); su.setNama(r1.getCell(1).getStringCellValue()); su.setDeleted(Boolean.parseBoolean(r1.getCell(4).getStringCellValue())); l.add(su); x = c + 1; st = s.getRow(x).getCell(0).getStringCellValue(); } progSuplier.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOSuplier(d).insert(l.get(c)); progSuplier.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progSuplier.setValue(100); }
From source file:achmad.rifai.admin.ui.Opener.java
private void income() { try {/*from ww w .j a va 2 s.co m*/ achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("Pemasukan"); List<achmad.rifai.erp1.entity.Terima> l = new java.util.LinkedList<>(); int x = 1; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.Terima t = new achmad.rifai.erp1.entity.Terima(); org.apache.poi.xssf.usermodel.XSSFRow r = s.getRow(x); t.setKode(r.getCell(0).getStringCellValue()); t.setJurnal(r.getCell(1).getStringCellValue()); t.setTgl(org.joda.time.DateTime.parse(r.getCell(2).getStringCellValue())); t.setUang(org.joda.money.Money.parse(r.getCell(3).getStringCellValue())); t.setDeleted(Boolean.parseBoolean(r.getCell(4).getStringCellValue())); x++; st = s.getRow(x).getCell(0).getStringCellValue(); l.add(t); } progIncome.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOTerima(d).insert(l.get(c)); progIncome.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progIncome.setValue(100); }
From source file:achmad.rifai.admin.ui.Opener.java
private void tracks() { try {/*from ww w . j a v a2 s .c o m*/ achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("Buku Perilaku"); List<achmad.rifai.erp1.entity.Tracks> l = new java.util.LinkedList<>(); int x = 2; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.Tracks t = new achmad.rifai.erp1.entity.Tracks(); int y = x; org.apache.poi.xssf.usermodel.XSSFRow r1 = s.getRow(x), r2 = s.getRow(y); List<achmad.rifai.erp1.entity.Jejak> l1 = new java.util.LinkedList<>(); boolean trus = true; while (trus || null == r2.getCell(0)) { achmad.rifai.erp1.entity.Jejak j = new achmad.rifai.erp1.entity.Jejak(); j.setAksi(r2.getCell(2).getStringCellValue()); j.setWaktu(org.joda.time.DateTime.parse(r2.getCell(3).getStringCellValue())); l1.add(j); trus = false; y++; r2 = s.getRow(y); } t.setL(l1); t.setKode(r1.getCell(0).getStringCellValue()); t.setId(r1.getCell(1).getStringCellValue()); t.setBln(Month.valueOf(r1.getCell(4).getStringCellValue())); t.setTahun(Integer.parseInt(r1.getCell(5).getStringCellValue())); t.setDeleted(Boolean.parseBoolean(r1.getCell(6).getStringCellValue())); l.add(t); x = y + 1; st = s.getRow(x).getCell(0).getStringCellValue(); } progTrack.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOTracks(d).insert(l.get(c)); progTrack.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progTrack.setValue(100); }
From source file:achmad.rifai.admin.ui.Opener.java
private void tugas() { try {/*from ww w. ja va 2s.c o m*/ achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("Tugas"); List<achmad.rifai.erp1.entity.Tugas> l = new java.util.LinkedList<>(); int x = 2; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.Tugas t = new achmad.rifai.erp1.entity.Tugas(); int y = x; org.apache.poi.xssf.usermodel.XSSFRow r1 = s.getRow(x), r2 = s.getRow(y); List<achmad.rifai.erp1.entity.Petugas> l1 = new java.util.LinkedList<>(); boolean trus = true; while (trus || null == r2.getCell(0)) { achmad.rifai.erp1.entity.Petugas p = new achmad.rifai.erp1.entity.Petugas(); p.setKaryawan(r2.getCell(3).getStringCellValue()); p.setDiambil(Boolean.parseBoolean(r2.getCell(4).getStringCellValue())); p.setSedang(Boolean.parseBoolean(r2.getCell(5).getStringCellValue())); p.setTerlaksana(Boolean.parseBoolean(r2.getCell(6).getStringCellValue())); l1.add(p); y++; r2 = s.getRow(y); trus = false; } t.setL(l1); t.setKode(r1.getCell(0).getStringCellValue()); t.setNo(Integer.parseInt(r1.getCell(1).getStringCellValue())); t.setTgl(java.sql.Date.valueOf(r1.getCell(2).getStringCellValue())); t.setKet(r1.getCell(7).getStringCellValue()); t.setBatal(Boolean.parseBoolean(r1.getCell(8).getStringCellValue())); t.setPending(Boolean.parseBoolean(r1.getCell(9).getStringCellValue())); t.setDeleted(Boolean.parseBoolean(r1.getCell(10).getStringCellValue())); x = y + 1; l.add(t); st = s.getRow(x).getCell(0).getStringCellValue(); } progTugas.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOTugas(d).insert(l.get(c)); progTugas.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progTugas.setValue(100); }
From source file:achmad.rifai.admin.ui.Opener.java
private void bonus() { try {/*from ww w . j a va 2 s. c om*/ achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("bonus"); List<achmad.rifai.erp1.entity.BulanBonus> l = new java.util.LinkedList<>(); int x = 2; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.BulanBonus b = new achmad.rifai.erp1.entity.BulanBonus(); int y = x; org.apache.poi.xssf.usermodel.XSSFRow r1 = s.getRow(x), r2 = s.getRow(y); List<achmad.rifai.erp1.entity.Bonusan> l1 = new java.util.LinkedList<>(); boolean trus = true; while (trus || null == r2.getCell(0)) { achmad.rifai.erp1.entity.Bonusan bo = new achmad.rifai.erp1.entity.Bonusan(); bo.setNomer(Integer.parseInt(r2.getCell(2).getStringCellValue())); bo.setJumlah(org.joda.money.Money.parse(r2.getCell(3).getStringCellValue())); l1.add(bo); y++; r2 = s.getRow(y); trus = false; } b.setL(l1); b.setKode(r1.getCell(0).getStringCellValue()); b.setPeg(r1.getCell(1).getStringCellValue()); b.setBln(Month.valueOf(r1.getCell(4).getStringCellValue())); b.setThn(java.time.Year.parse(r1.getCell(5).getStringCellValue())); b.setDeleted(Boolean.parseBoolean(r1.getCell(6).getStringCellValue())); l.add(b); x = y + 1; st = s.getRow(x).getCell(0).getStringCellValue(); } progBonus.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOBulanBonus(d).insert(l.get(c)); progBonus.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progBonus.setValue(100); }
From source file:be.thomasmore.controller.FileController.java
private void leesExcel() { try {//from ww w. j av 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 {/* w w w . ja v a 2s .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(); } }