List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook
public XSSFWorkbook(PackagePart part) throws IOException
From source file:attendance.CheckIn.java
private static void fileImport() throws IOException { //variable assignment fileChosen = true;// w w w.ja va2s.c o m chooseError = ""; //creates filechooser with desktop as default directory String userhome = System.getProperty("user.home"); JFileChooser chooser = new JFileChooser(userhome + "\\Desktop"); //opens filechooser, assigns choice to status int status = chooser.showOpenDialog(null); File file = chooser.getSelectedFile(); filePath = file.getAbsolutePath(); //if they don't click approve, assume no file chosen if (status != JFileChooser.APPROVE_OPTION) { //System.out.println("No File Chosen"); fileChosen = false; chooseError = "No File Chosen"; confirmChoice(); } file1 = new FileInputStream(new File(filePath)); if (filePath.endsWith(".xls")) workbook = new HSSFWorkbook(file1); else if (filePath.endsWith(".xlsx")) workbook = new XSSFWorkbook(file1); for (int i = 0; i < NUM_SHEETS; i++) { sheets.add(workbook.getSheetAt(i)); } }
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;/*from w ww . ja v a2s . c om*/ } 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 {// www. j ava2 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:bandaru_excelreadwrite.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;// ww w .j av a2s.co 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 {//from ww w . j a v a 2s . c om //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 .j av a 2 s. c o m //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:be.thomasmore.service.FileUploadServiceImp1.java
@Override public void handleFileUpload(FileUploadEvent event) { clearVars();// w ww. ja v a2 s .c om InputStream file; XSSFWorkbook workbook = null; try { //geupload excel bestand inlezen file = event.getFile().getInputstream(); workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); //de excell overlopen en de gegevens eruit halen //klas ophalen int i = 0; XSSFRow row = sheet.getRow(i++); klas.setNaam(row.getCell(1).getStringCellValue());// =>naam klas (bv 5a) //vak ophalen row = sheet.getRow(i++); vak.setNaam(row.getCell(1).getStringCellValue());// => naam vak (bv java) //test ophalen row = sheet.getRow(i++); test.setNaam(row.getCell(1).getStringCellValue()); // => naam test row = sheet.getRow(i++); test.setTotaal((int) row.getCell(1).getNumericCellValue());// => totaal test //vak.setTesten(new ArrayList<Test>(test)); //test.setVak(vak); i += 2; //studenten +scores ophalen while (i <= sheet.getLastRowNum()) { row = sheet.getRow(i++); Student student = new Student(); student.setStudentennummer(String.valueOf((int) row.getCell(0).getNumericCellValue())); student.setNaam(row.getCell(1).getStringCellValue()); Score score = new Score(); score.setKlas(klas); score.setStudent(student); score.setTest(test); score.setScore((int) row.getCell(2).getNumericCellValue()); scores.add(score); } test.setScores(scores); klas.setScores(scores); // klas.setStudenten(studenten); } catch (IOException e) { // facesContext // .addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null)); e.printStackTrace(); } }
From source file:br.com.bb.intranet.supermt.pf.desembolso.service.ImportaDados.java
/** * * @param planilha objeto do tipo Planilha que contm todos os dados * necessrios para facilitar a operao na planilha *//*from w ww.j a va 2 s . co m*/ public void processaPlanilha(Planilha planilha) throws NegocioException { List<Desembolso> dadosParaSalvar = new ArrayList(); int linha = 0; Double d; try { //cria um workbook = planilha toda com todas as abas XSSFWorkbook workbook = new XSSFWorkbook(planilha.getFile()); //recuperamos apenas a aba mandada XSSFSheet sheet = workbook.getSheetAt(planilha.getNumeroDaPlanilha() - 1); //retorna todas as linhas da planilha selecioada Iterator<Row> rowIterator = sheet.iterator(); //varre todas as linhas da planilha selecionada while (rowIterator.hasNext() && linha < planilha.getUltimaLinha()) { linha++; //recebe cada linha da planilha Row row = rowIterator.next(); //andar as linhas que sero ignoradas no incio if (row.getRowNum() < planilha.getUltimaLinha()) { continue; } //pegamos todas as celulas desta linha Iterator<Cell> cellIterator = row.iterator(); //responsavel por definir qual coluna esta sendo trabalhada no intante int coluna = 1; Cell cell; Desembolso desembolso = new Desembolso(); //varremos todas as celulas da linha atual while (cellIterator.hasNext()) { //criamos uma celula cell = cellIterator.next(); //TODO O CDIGO DE PERSISTENCIA AQUI!! switch (coluna) { case ColunasDesembolso.GRUPO: desembolso.setGrupo(cell.getStringCellValue()); break; case ColunasDesembolso.PREFIXO_SUPER_INTENDENCIA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setPrefixoSuperintendencia(cell.getStringCellValue()); break; case ColunasDesembolso.NOME_SUPER_INTENDENCIA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setNomeSuperintendencia(cell.getStringCellValue()); break; case ColunasDesembolso.PREFIXO_REGIONAL: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setPrefixoRegional(cell.getStringCellValue()); break; case ColunasDesembolso.NOME_AGENCIA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setNomeAgencia(cell.getStringCellValue()); break; case ColunasDesembolso.ORCAMENTO_PROPOSTO_ACUMULADO: desembolso .setOrcamentoPropostoAcumulado(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_ATUAL: desembolso.setRealizadoAtual(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_UM: desembolso.setPercentualAtingimentoUm(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_D_MENOS_UM: desembolso.setRealizadoDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.NECESSIDADE_DIA_MENOS_UM: cell.setCellType(Cell.CELL_TYPE_NUMERIC); desembolso.setNecessidadeDiaDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.META_CONTATOS_ACUMULADA: cell.setCellType(Cell.CELL_TYPE_NUMERIC); desembolso.setMetaContatosAcumulada(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_CONTATOS_MES: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setRealizadoContatosMes(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_CONTATOS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setPercentualAtingimentoContatos( this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.CONTATOS_D_MENOS_UM: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setContatosDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.CONTATOS_D_MENOS_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setContatosDmenosDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PREFIXO_REPETE: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setPrefixoRepete(cell.getStringCellValue()); break; case ColunasDesembolso.AGENCIA_REPETE: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setAgenciaRepete(cell.getStringCellValue()); break; case ColunasDesembolso.CODIDGO_CARTEIRA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setCodigoCarteira(cell.getStringCellValue()); break; case ColunasDesembolso.CARTEIRA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setCarteira(cell.getStringCellValue()); break; case ColunasDesembolso.ORCAMENTO_PROPORCIONAL_ACUMULADO_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setOrcamentoProporcionalAcumuladoDois( this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_ATUAL_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setRealizadoAtualDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso .setPercentualAgintimentoDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.META_CONTATOS_ACUMULADA_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso .setMetaContatosAcumuladaDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_CONTATOS_MES_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setRealizadoContatosMesDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_CONTATOS_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setPercentualAtingimentoContatosDois( this.doubleToBigDecimal(cell.getNumericCellValue())); break; } System.out.println("valor = " + cell.toString()); coluna++; } dadosParaSalvar.add(desembolso); } this.salvar(dadosParaSalvar); } catch (FileNotFoundException ex) { throw new NegocioException("Arquivo com Erro Tente novamente!!"); } catch (IOException ex) { throw new NegocioException("Arquivo com Erro Tente novamente!!"); } }
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();/*from ww w . j a v a2 s .co m*/ 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")) { } } 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; }
From source file:br.com.tiagods.model.Arquivo.java
public Workbook validarWorkbook(Workbook workbook, File file, String ext, InputStream inputStream) { switch (ext) { case "xls": try {/*from w w w .j av a 2 s. c om*/ if (file.getName().equals("Cadastro.xls")) {//formula para abrir arquivo com senha Biff8EncryptionKey.setCurrentUserPassword("PLKCONTRATOS"); NPOIFSFileSystem fs = new NPOIFSFileSystem(file, true); return workbook = new HSSFWorkbook(fs.getRoot(), true); } else { POIFSFileSystem fileSystem = new POIFSFileSystem(inputStream); return workbook = new HSSFWorkbook(fileSystem); } } catch (IOException e) { JOptionPane.showMessageDialog(null, e); return null; } case "xlsx": try { return workbook = new XSSFWorkbook(inputStream); } catch (IOException e) { JOptionPane.showMessageDialog(null, e); return null; } default: JOptionPane.showMessageDialog(null, "Formato de arquivo no permitido!"); return null; } }