List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:be.thomasmore.controller.InputBean.java
private void leesExcel(String path) { try {// w w w . j a v a 2 s . c om //declaratie en blad uit excel selecteren enzo FileInputStream fileInputStream = new FileInputStream(path); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); XSSFSheet worksheet = workbook.getSheet("Blad1"); // XSSFRow row1 = worksheet.getRow(0); // XSSFCell cellA1 = row1.getCell((short) 0); // String a1Val = cellA1.getStringCellValue(); // XSSFCell cellB1 = row1.getCell((short) 1); // String b1Val = cellB1.getStringCellValue(); // // XSSFRow row2 = worksheet.getRow(1); // XSSFCell cellA2 = row2.getCell((short) 0); // String a2Val = cellA2.getStringCellValue(); // XSSFCell cellB2 = row2.getCell((short) 1); // String b2Val = cellB2.getStringCellValue(); // // XSSFRow row7 = worksheet.getRow(6); // int a7Val = (int) row7.getCell((short) 0).getNumericCellValue(); // String b7Val = row7.getCell((short) 1).getStringCellValue(); // int c7Val = (int) row7.getCell((short) 2).getNumericCellValue(); // // XSSFRow row8 = worksheet.getRow(7); // int a8Val = (int) row8.getCell((short) 0).getNumericCellValue(); // String b8Val = row8.getCell((short) 1).getStringCellValue(); // int c8Val = (int) row8.getCell((short) 2).getNumericCellValue(); // // XSSFRow row9 = worksheet.getRow(8); // int a9Val = (int) row9.getCell((short) 0).getNumericCellValue(); // String b9Val = row9.getCell((short) 1).getStringCellValue(); // int c9Val = (int) row9.getCell((short) 2).getNumericCellValue(); // System.out.println("A1: " + a1Val); // System.out.println("B1: " + b1Val); // System.out.println("A2: " + a2Val); // System.out.println("B2: " + b2Val); // System.out.println("Studentnr - naam - score"); // System.out.println(a7Val + " " + b7Val + " " + c7Val); // System.out.println(a8Val + " " + b8Val + " " + c8Val); // System.out.println(a9Val + " " + b9Val + " " + c9Val); //iterator dat door alle rijen gaat van het excel-blad Iterator<Row> rowIterator = worksheet.iterator(); Test test = new Test(); //test aanmaken String klasNaam = ""; Long klasId = 0L; while (rowIterator.hasNext()) { //als er nog een rij bestaat die niet leeg is Row row = rowIterator.next(); //row-object aanmaken van huidige rij if (row.getRowNum() == 0) { //als de nummer van de rij = 0 (dus de 0de rij van het excel bestand = klas) Iterator<Cell> cellIterator = row.cellIterator(); //voor deze rij elke cel in deze rij afgaan met een iterator while (cellIterator.hasNext()) { //als er nog een cell bestaat die niet leeg is Cell cell = cellIterator.next(); //cell-object aanmaken van huidige cell if (!cell.getStringCellValue().equals("klas")) { //als er het woord "klas" in de cell staat, deze overslaan. Als de cel van de 0de rij (klas-rij) iets anders is dan "klas" dus (=A1 in excel) switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: //als het type van de cel een string is Klas klas = new Klas(); // klas-object aanmaken klasNaam = cell.getStringCellValue(); klas.setNaam(cell.getStringCellValue()); //naam van klas instellen op de waarde van de cell List<Klas> alleKlassen = javaProject7Service.getAllKlassen(); boolean bestaatAl = false; for (Klas alleKlas : alleKlassen) { if (alleKlas.getNaam().equals(klasNaam)) { bestaatAl = true; } } if (bestaatAl) { klasId = javaProject7Service.addKlas(klas); } break; } } } } //volgende if is hetzelfde principe als vorige enkel voor een andere rij if (row.getRowNum() == 1) { //nummer van de rij = 1 (dus eigenlijk in excel de 2de rij) Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (!cell.getStringCellValue().equals("Vak")) { //als er het woord "Vak" in de cell staat, deze overslaan switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: //hier moet nog code komen om het vak toe te voegen aan het Test-object (zie regel 196) break; } } } } //weer hetzelfde principe als hierboven if (row.getRowNum() > 5) { // enkel voor de rijen 5 en hoger (dus enkel studenten) Iterator<Cell> cellIterator = row.cellIterator(); Student student = new Student(); //nieuw student-object aanmaken per rij while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //als de cell een int is student.setStudentnr((int) cell.getNumericCellValue()); //Klas klas = javaProject7Service.getKlasByNaam(klasNaam); //klas ophalen uit db adhv klasnaam student.setKlasId(klasId); break; case Cell.CELL_TYPE_STRING: //als de cell een string is if (cell.getStringCellValue().equals("zit al in de DB") || cell.getStringCellValue() != null || cell.getStringCellValue().equals("")) { //als de cell "zit al in de DB" bevat, niets doen (zie excel; laatste regel) break; } else { String volledigeNaam = cell.getStringCellValue(); String[] delen = volledigeNaam.split(" "); student.setVoornaam(delen[0]); student.setAchternaam(delen[1]); break; } } javaProject7Service.addStudent(student); //student toevoegen aan studenten list } } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:br.com.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 ww w . ja v a 2s.c o 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:businessCharts.readExcell.java
public TreeMap<Double, String> getcontentList(String colName, String colName2) throws Exception { TreeMap<Double, String> treemap = new TreeMap<Double, String>(); initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row = (XSSFRow) rowIterator.next(); int setColno = getColNo(colName, row); int setColno2 = getColNo(colName2, row); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Cell cell = row.getCell(setColno); Cell cell2 = row.getCell(setColno2); try {/* w ww .j a va2 s.co m*/ treemap.put(cell.getNumericCellValue(), new SimpleDateFormat("yyyy-MM-dd").format(cell2.getDateCellValue())); } catch (Exception ex) { treemap.put(cell.getNumericCellValue(), "nodate"); } } return treemap; }
From source file:businessCharts.readExcell.java
public String[] queryByRowKey(String[] keyStringArray, String colName, String resultColName) throws Exception { String[] resultStringArray = new String[keyStringArray.length]; initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row = (XSSFRow) rowIterator.next(); int getcellNo = getColNo(colName, row); int getReturncellKey = getColNo(resultColName, row); System.out.println(//from ww w . j a va 2 s . co m "Inside queryByRowKey" + " CellNocolname" + getcellNo + "ReturnCellColName" + getReturncellKey); for (int i = 0; i < keyStringArray.length; i++) { System.out.println("resultstring array elements" + keyStringArray[i]); rowIterator = spreadsheet.iterator(); row = (XSSFRow) rowIterator.next(); int flag = 0; while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Cell cell = row.getCell(getcellNo); Cell returnCell = row.getCell(getReturncellKey); // System.out.println("Date is " + returnCell.getDateCellValue()); // try{ if (cell.getNumericCellValue() == Double.parseDouble(keyStringArray[i])) { flag = 1; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // System.out.println("Date is " + sdf.format(returnCell.getDateCellValue())); try { if (returnCell.getDateCellValue() != null) resultStringArray[i] = sdf.format(returnCell.getDateCellValue()); } catch (Exception ex) { resultStringArray[i] = "No Deadline Available"; } } // } // catch(Exception ex){ // continue; //} } if (flag == 0) { resultStringArray[i] = "Bug Not Available/Resolved"; flag = 0; } else flag = 0; } return resultStringArray; }
From source file:businessCharts.readExcell.java
public Pair<Integer, Integer> countColKeywordClone(String keyword, String colName) throws Exception { initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row = (XSSFRow) rowIterator.next(); int getcellNo = getColNo(colName, row); int getcellNokey = getColNo("keywords", row); int countCells = 0; int notCloned = 0; System.out.println("CellNokey" + getcellNokey + "getqurycell" + getcellNo); while (rowIterator.hasNext()) { totalrow++;/*w w w .java 2s . c om*/ row = (XSSFRow) rowIterator.next(); Cell cell = row.getCell(getcellNo); Cell cellkey = row.getCell(getcellNokey); try { if (isContain(cell, keyword)) notCloned++; if (isContain(cell, keyword) && isContain(cellkey, "clone")) countCells++; } //System.out.println(); catch (NullPointerException ex) { continue; } } Pair<Integer, Integer> pair = new Pair<Integer, Integer>(countCells, notCloned - countCells); System.out.println(totalrow); return pair; }
From source file:businessCharts.readExcell.java
public TreeMap<String, TreeMap<String, Pair<Integer, Integer>>> groupBycount(String par1, String par2) throws Exception { //so that mapping could be done in O(n*log n*log n) initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow tempRow = (XSSFRow) rowIterator.next(); int cell_branch = getColNo(par1, tempRow); int cell_assignee = getColNo(par2, tempRow); int cell_keyword = getColNo("Keywords", tempRow); System.out.println("columns no" + cell_assignee + " " + cell_branch + " " + cell_keyword); TreeMap<String, TreeMap<String, Pair<Integer, Integer>>> list = new TreeMap<>(); while (rowIterator.hasNext()) { XSSFRow row = (XSSFRow) rowIterator.next(); Cell Branchcell = row.getCell(cell_branch); Cell Assigneecell = row.getCell(cell_assignee); if (list.containsKey(Branchcell.getStringCellValue())) { if (list.get(Branchcell.getStringCellValue()).containsKey(Assigneecell.getStringCellValue())) { try { Cell keycell = row.getCell(cell_keyword); if (isContain(keycell, "clone")) { int key = list.get(Branchcell.getStringCellValue()) .get(Assigneecell.getStringCellValue()).getKey(); int value = list.get(Branchcell.getStringCellValue()) .get(Assigneecell.getStringCellValue()).getValue(); //System.out.println("key->" + key); key++;// w ww . ja v a 2s . c o m list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(key, value)); } else { int key = list.get(Branchcell.getStringCellValue()) .get(Assigneecell.getStringCellValue()).getKey(); int value = list.get(Branchcell.getStringCellValue()) .get(Assigneecell.getStringCellValue()).getValue(); //System.out.println("value->" + value); value++; list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(key, value)); } } catch (NoSuchElementException ex) { int key = list.get(Branchcell.getStringCellValue()).get(Assigneecell.getStringCellValue()) .getKey(); int value = list.get(Branchcell.getStringCellValue()).get(Assigneecell.getStringCellValue()) .getValue(); value++; list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(key, value)); } } else { try { Cell keycell = row.getCell(cell_keyword); if (isContain(keycell, "clone")) list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(1, 0)); else list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(0, 1)); } catch (NoSuchElementException ex) { list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(0, 1)); } } } else { if (isContain(row.getCell(cell_keyword), "clone")) { TreeMap<String, Pair<Integer, Integer>> tp = new TreeMap<>(); tp.put(Assigneecell.getStringCellValue(), new Pair<>(1, 0)); list.put(Branchcell.getStringCellValue(), tp); } else { TreeMap<String, Pair<Integer, Integer>> tp = new TreeMap<>(); tp.put(Assigneecell.getStringCellValue(), new Pair<>(0, 1)); list.put(Branchcell.getStringCellValue(), tp); } } } return list; }
From source file:clases.excel.EXCELreader.java
private void prepareList(String path) { excelString = ""; listaEXCEL = new ArrayList<>(); int sw = 0;//0 no,1 si, 2 ready XSSFWorkbook workbook;//from ww w . j av a 2 s. c om try { //Create Workbook instance holding reference to .xlsx file workbook = new XSSFWorkbook(new FileInputStream(new File(path))); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { String[] linea = new String[parametros]; Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); // if (rowcont2 ==16) { // excelString += ++rowcont + "***"; int rowcont = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //JOptionPane.showMessageDialog(null, "Tipo *" + cell.getCellType() + "*"); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //leyendo tabla if (sw == 2) { Double d = cell.getNumericCellValue();//String.format("%d", d.intValue()) excelString += ((d % 1 != 0) ? d.toString() : d.toString()) + "\t"; linea[rowcont++] = ((d % 1 != 0) ? d.toString() : d.toString()); } break; case Cell.CELL_TYPE_STRING: // JOptionPane.showMessageDialog(null, "String *" + cell.getStringCellValue().trim() + "*"); if (sw == 0) { if (cell.getStringCellValue().trim().equals(init_detalle)) { sw = 1; } } //leyendo tabla if (sw == 2) { excelString += cell.getStringCellValue() + "\t"; linea[rowcont++] = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BLANK: //si estaba leyendo tabla, pero encuentra vacio // termina de leer la tabla if (rowcont == 0 && sw == 2) { sw = 4; } break; case Cell.CELL_TYPE_FORMULA: //leyendo tabla if (sw == 2) { Double d = cell.getNumericCellValue(); excelString += ((d % 1 != 0) ? d.toString() : d.toString()) + "\t"; linea[rowcont++] = ((d % 1 != 0) ? d.toString() : d.toString()); } break; } //si ya es 1 termina el ciclo, para iniciar el detalle // en la siguiente linea if (sw == 1) { sw = 2; break; } //si ya no leera mas tabla, termina de leer la row if (sw == 4) { break; } } //guarda si esta leyendo tabla if (sw == 2 && linea[0] != null) { listaEXCEL.add(linea); excelString += "\n"; } //termino de leer la tabla, cierra todo if (sw == 4) { break; } // } } int as = 0; } catch (Exception e) { e.printStackTrace(); } }
From source file:CODIGOS.Planilha.java
public static void lerPlanilha(String arquivo) { FileInputStream fisPlanilha = null; try {//from w w w.j a va 2 s .c o m File dir = new File("C:\\GA6"); File file = new File(dir, arquivo + ".xlsx"); fisPlanilha = new FileInputStream(file); /*CRIA UM WORKBOOK = PLANILHA TODA COM TODAS AS ABAS*/ XSSFWorkbook workbook = new XSSFWorkbook(fisPlanilha); /*RECUPERAMOS APENAS A PRIMEIRA ABA OU PRIMEIRA PLANILHA*/ XSSFSheet sheet = workbook.getSheetAt(0); /*RETORNA TODAS AS LINHAS DA PLANILHA 0 */ Iterator<Row> rowIterator = sheet.iterator(); /*VARRE TODAS AS LINHAS DA PLANILHA 0*/ while (rowIterator.hasNext()) { //recebe cada linha da planilha Row row = rowIterator.next(); //pegamos todas as celulas desta linha Iterator<Cell> cellIterator = row.iterator(); //varremos todas as celulas da linha atual while (cellIterator.hasNext()) { //criamos uma celula Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: PS[count] = "" + cell.getStringCellValue(); count++; break; case Cell.CELL_TYPE_NUMERIC: PS[count] = "" + cell.getNumericCellValue(); PS[count] = PS[count].replace(".0", ""); count++; break; } } } } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Arquivo " + arquivo + " no encontrado.", "Warning", JOptionPane.WARNING_MESSAGE); System.exit(0); } catch (IOException ex) { Logger.getLogger(Planilha.class.getName()).log(Level.SEVERE, null, ex); } finally { try { fisPlanilha.close(); } catch (IOException ex) { Logger.getLogger(Planilha.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:com.anritsu.mcrepositorymanager.packageinfoparser.RSSParser.java
public void parseRSS() { try {/*from www . j av a 2s . c o m*/ FileInputStream file = new FileInputStream(new File(this.filePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get package list sheet from the workbook String sheetName = "MC " + mcVersion; XSSFSheet sheet = workbook.getSheet(sheetName); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getRowNum() < 3) { continue; } McPackage p = new McPackage(); p.setMcVersion(mcVersion); p.setName(row.getCell(2).getStringCellValue()); HashSet<String> downloadLinks = new HashSet<>(); try { String link = row.getCell(7).getHyperlink().getAddress(); downloadLinks.add(link); int urlIndex = link.split("/").length; String fileName = link.split("/")[urlIndex - 1]; p.setFileName(fileName); } catch (NullPointerException exp) { exp.printStackTrace(); } finally { p.setDownloadLinks(downloadLinks); } p.setPackageVersion(row.getCell(3).getStringCellValue()); p.setAvailability(row.getCell(4).getStringCellValue()); availability.add(row.getCell(4).getStringCellValue()); // Set customers list ArrayList<String> cusList = new ArrayList<>(); String[] customerCell = row.getCell(5).getStringCellValue().split(","); for (int i = 0; i < customerCell.length; i++) { customers.add(customerCell[i]); cusList = new ArrayList<>(Arrays.asList(customerCell)); } //System.out.println("Parsing line " + row.getRowNum()); LOGGER.log(Level.INFO, "Parsing " + this.filePath + "/" + row.getRowNum()); p.setCustomerList(new HashSet<>(cusList)); packageList.add(p); } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.beans.AddressBookMBean.java
private ArrayList<Addressbook> readExcel(UploadedFile file) { ArrayList<Addressbook> adbooks = new ArrayList<Addressbook>(); InputStreamReader reader = null; try {//from w ww. jav a 2 s . c om File myFile = new File(file.getFileName()); FileUtils.copyInputStreamToFile(file.getInputstream(), myFile); FileInputStream fis = new FileInputStream(myFile); // Finds the workbook instance for XLSX file XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); // Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Addressbook add = new Addressbook(); Row row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int i = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cell.setCellType(Cell.CELL_TYPE_STRING); System.out.print("Cell:" + i + ":" + cell.getStringCellValue() + "\t"); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (i == 0) { add.setPhonenumber(cell.getStringCellValue()); } else { add.setName(cell.getStringCellValue()); } break; case Cell.CELL_TYPE_NUMERIC: if (i == 0) { cell.setCellType(Cell.CELL_TYPE_STRING); add.setPhonenumber(cell.getStringCellValue()); } System.out.print(cell.getStringCellValue() + "\t"); break; default: } i++; } if (add.getPhonenumber() != null) { adbooks.add(add); } } System.out.println(""); } catch (IOException ex) { Logger.getLogger(AddressBookMBean.class.getName()).log(Level.SEVERE, null, ex); } finally { } return adbooks; }