List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex
int getColumnIndex();
From source file:bad.robot.excel.PoiToExcelCoercions.java
License:Apache License
public static String asExcelColumn(Cell cell) { return ExcelColumnIndex.from(cell.getColumnIndex()).name(); }
From source file:bandaru_excelreadwrite.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;/*from w ww . ja va 2 s .com*/ 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 {// ww w. j av a2 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: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();// w w w .ja v a 2 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 void enviarValores(Cell celula, PlanilhaBean bean) { switch (celula.getColumnIndex()) {//retorna o numero das colunas, nesse caso defini como 5 colunas case 0:/*from w ww.j a v a 2s. co m*/ String valor = tratarTipo(celula); switch (valor.length()) { case 1: valor = "000" + valor; break; case 2: valor = "00" + valor; break; case 3: valor = "0" + valor; break; } bean.setC0(valor); break; case 1: bean.setC1(tratarTipo(celula)); break; case 2: bean.setC2(tratarTipo(celula)); break; case 3: bean.setC3(tratarTipo(celula)); break; case 4: bean.setC4(tratarTipo(celula)); break; case 5: bean.setC5(tratarTipo(celula)); break; case 6: bean.setC6(tratarTipo(celula)); break; case 7: bean.setC7(tratarTipo(celula)); break; case 8: bean.setC8(tratarTipo(celula)); break; case 9: bean.setC9(tratarTipo(celula)); break; case 10: bean.setC10(tratarTipo(celula)); break; case 11: bean.setC11(tratarTipo(celula)); break; case 12: bean.setC12(tratarTipo(celula)); break; case 13: bean.setC13(tratarTipo(celula)); break; case 14: bean.setC14(tratarTipo(celula)); break; case 15: bean.setC15(tratarTipo(celula)); break; case 16: bean.setC16(tratarTipo(celula)); break; case 17: bean.setC17(tratarTipo(celula)); break; case 18: bean.setC18(tratarTipo(celula)); break; case 19: bean.setC19(tratarTipo(celula)); break; case 20: bean.setC20(tratarTipo(celula)); break; case 21: bean.setC21(tratarTipo(celula)); break; case 22: bean.setC22(tratarTipo(celula)); break; case 23: bean.setC23(tratarTipo(celula)); break; case 24: bean.setC24(tratarTipo(celula)); break; case 25: bean.setC25(tratarTipo(celula)); break; default: break; } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
@Override public List<Funcionario> readExcelDocument() { try {/* www. j av a 2 s . c om*/ List<Funcionario> funcionariosExcel = new ArrayList<>(); ClassLoader classLoader = Thread.currentThread().getContextClassLoader(); // FileInputStream fl = new FileInputStream(new File("C:\\Matriz1.xlsx")); Workbook wb = new XSSFWorkbook(classLoader.getResourceAsStream("Matriz1.xlsx")); Sheet firstSheet = wb.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); int row = nextRow.getRowNum(); // System.out.println("Row start" + row); Iterator<Cell> cellIterator = nextRow.cellIterator(); Funcionario f = new Funcionario(); Formacao fm = new Formacao(); Idioma id = new Idioma(); int column = 0; while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); column = columnIndex; // System.out.println("Valor" + getCellValue(nextCell)); // System.out.println("Index: " + columnIndex); if (row > 0) { switch (columnIndex) { case 1: f.setArea((String) getCellValue(nextCell)); break; case 2: Date dt = new Date(); if (!getCellValue(nextCell).toString().isEmpty()) { try { dt = DateUtil.getJavaDate((Double) getCellValue(nextCell)); } catch (ClassCastException cce) { SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy"); dt = formatter.parse((String) getCellValue((nextCell))); } ; } f.setDtAdmissao(dt); break; case 3: f.setCargo((String) getCellValue(nextCell)); break; case 4: f.setNome((String) getCellValue(nextCell)); break; case 5: f.setGestor((String) getCellValue(nextCell)); break; case 9: fm.setNivel((String) getCellValue(nextCell)); break; case 10: fm.setCurso((String) getCellValue(nextCell)); break; case 11: fm.setInstituicaoo((String) getCellValue(nextCell)); break; case 12: String typeEnum = (String) getCellValue(nextCell); if (!typeEnum.isEmpty()) { id.setNome(Language.valueOf(typeEnum.trim())); } break; case 13: String typeEnumNivel = (String) getCellValue(nextCell); if (!typeEnumNivel.isEmpty()) { id.setNivel(Nivel.valueOf(typeEnumNivel.trim())); } break; } } } List<Formacao> listFm = new ArrayList<>(); listFm.add(fm); f.setFormacoes(listFm); List<Idioma> listId = new ArrayList<>(); listId.add(id); f.setIdiomas(listId); if (row > 0) { funcionariosExcel.add(f); } } wb.close(); // fl.close(); // for (Funcionario fc : funcionariosExcel) { // System.out.println(fc.getNome()); // } return funcionariosExcel; } catch (Exception e) { e.printStackTrace(); return null; } }
From source file:br.ufmt.periscope.importer.impl.ESPACENETPatentImporter.java
private void parseLineXLS() { patent.setLanguage(lang);/*from w ww. ja v a 2 s . com*/ row = rowIterator.next(); //Percorrendo cada linha (patente) // Para cada linha (patente), pega cada atributo (Titulo, Publicao, Autor ...) Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); // Pegando cada coluna (atributo) switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: fillPatentXLS(cell.getColumnIndex(), cell.getStringCellValue()); break; default: break; } } }
From source file:cherry.goods.excel.ExcelReader.java
License:Apache License
/** * ?<br />/*from ww w.j a va2 s . c om*/ * ???1(1) * * @return 1(1) */ public String[] read() { if (!rowIterator.hasNext()) { return null; } Row row = rowIterator.next(); int lastCellNum = row.getLastCellNum(); if (lastCellNum < 0) { return new String[0]; } String[] record = new String[lastCellNum]; for (Cell cell : row) { record[cell.getColumnIndex()] = getCellValueAsString(cell); } return record; }
From source file:cherry.goods.telno.SoumuExcelParser.java
License:Apache License
/** * ?/* w w w . j a va 2s . c om*/ * * @param in ? * @return ?????? (6?)????? * @throws InvalidFormatException ??? * @throws IOException ?? */ public Map<String, Pair<String, String>> parse(InputStream in) throws InvalidFormatException, IOException { Map<String, Pair<String, String>> map = new LinkedHashMap<>(); try (Workbook workbook = WorkbookFactory.create(in)) { for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Integer numberCol = null; Integer areaCodeCol = null; Integer localCodeCol = null; boolean preparing = true; for (Row row : sheet) { if (preparing) { for (Cell cell : row) { String value = cell.getStringCellValue(); if (numberLabel.equals(value)) { numberCol = cell.getColumnIndex(); } if (areaCodeLabel.equals(value)) { areaCodeCol = cell.getColumnIndex(); } if (localCodeLabel.equals(value)) { localCodeCol = cell.getColumnIndex(); } } if (numberCol != null && areaCodeCol != null && localCodeCol != null) { preparing = false; } } else { String number = getCellValue(row, numberCol.intValue()); String areaCode = getCellValue(row, areaCodeCol.intValue()); String localCode = getCellValue(row, localCodeCol.intValue()); if (isNotEmpty(number) && isNotEmpty(areaCode) && isNotEmpty(localCode)) { map.put(number, Pair.of(areaCode, localCode)); } } } } } return map; }
From source file:cherry.parser.worksheet.RowBasedParser.java
License:Apache License
private List<TypeDef> parseSheet(Sheet sheet) { boolean configured = false; int coldefFirstCellNum = -1; Map<Integer, String> coldef = new TreeMap<Integer, String>(); Map<String, TypeDef> map = new LinkedHashMap<String, TypeDef>(); TypeDef typeDef = null;//www .j a va 2 s . c o m for (Row row : sheet) { int firstCellNum = row.getFirstCellNum(); if (firstCellNum < 0) { continue; } if (!configured) { String directive = getCellValueAsString(row.getCell(firstCellNum)); if ("##COLDEF".equals(directive)) { for (Cell cell : row) { if (cell.getColumnIndex() == firstCellNum) { continue; } if (cell.getCellType() != Cell.CELL_TYPE_STRING) { continue; } coldef.put(cell.getColumnIndex(), getCellValueAsString(cell)); } coldefFirstCellNum = firstCellNum; configured = true; } else { // IGNORE UNKNOWN DIRECTIVES } } else { ItemDef item = null; for (Cell cell : row) { if (cell.getColumnIndex() == coldefFirstCellNum) { item = new ItemDef(); continue; } if (item == null) { continue; } String key = coldef.get(cell.getColumnIndex()); if (key == null) { continue; } String value = getCellValueAsString(cell); if (value != null) { item.put(key, value); } } if (item != null) { String fqcn = item.get(TypeDef.FULLY_QUALIFIED_CLASS_NAME); if (fqcn != null) { TypeDef td = map.get(fqcn); if (td != null) { typeDef = td; } else { typeDef = new TypeDef(); typeDef.setSheetName(sheet.getSheetName()); map.put(fqcn, typeDef); } } if (typeDef != null) { typeDef.getItemDef().add(item); } } } } return new ArrayList<TypeDef>(map.values()); }