List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:be.thomasmore.controller.InputBean.java
private void leesExcel(String path) { try {//from ww w. java2s .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:beans.ReviewsBeans.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from w w w. j av a 2s. c o m CellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); for (Row row : sheet) { for (Cell cell : row) { cell.setCellValue(cell.getStringCellValue().toUpperCase()); cell.setCellStyle(style); } } }
From source file:biz.deinum.multitenant.batch.item.excel.poi.PoiSheet.java
License:Apache License
/** * {@inheritDoc}//from w w w.j a v a 2 s .c o m */ public String[] getRow(final int rowNumber) { if (rowNumber > this.delegate.getLastRowNum()) { return null; } final Row row = this.delegate.getRow(rowNumber); final List<String> cells = new LinkedList<String>(); final Iterator<Cell> cellIter = row.iterator(); while (cellIter.hasNext()) { final Cell cell = cellIter.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cells.add(String.valueOf(cell.getNumericCellValue())); break; case Cell.CELL_TYPE_BOOLEAN: cells.add(String.valueOf(cell.getBooleanCellValue())); break; case Cell.CELL_TYPE_STRING: case Cell.CELL_TYPE_BLANK: cells.add(cell.getStringCellValue()); break; default: throw new IllegalArgumentException("Cannot handle cells of type " + cell.getCellType()); } } return cells.toArray(new String[cells.size()]); }
From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java
License:Apache License
public void findAndReplaceAll(Sheet sheet, String find, Object replace) { if (replace == null) { replace = ""; }// w ww. j a v a 2 s. c om int iLastRow = sheet.getLastRowNum(); for (int i1 = 0; i1 < iLastRow; i1++) { Row currentRow = sheet.getRow(i1); if (currentRow != null) { int iLastCell = currentRow.getLastCellNum(); for (int i = 0; i < iLastCell; i++) { Cell currentCell = currentRow.getCell(i); if (currentCell != null && currentCell.getCellType() == Cell.CELL_TYPE_STRING) { if (currentCell.getStringCellValue().contains(find)) { currentCell.setCellValue(currentCell.getStringCellValue().replace(find, "" + replace)); } } } } } }
From source file:bo.com.offercruzmail.utils.HojaExcelHelper.java
/** * */*from w ww . jav a 2s . c o m*/ * Devuelve el valor de la celda en cadena * * @param celda La celda que contien el valor * @return El valor de la celda en cadena, si la celda es nula, devuelve una * cadena vaca */ public static String getValorCelda(Cell celda) { if (celda == null) { return ""; } switch (celda.getCellType()) { case Cell.CELL_TYPE_STRING: return celda.getStringCellValue(); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(celda.getBooleanCellValue()); case Cell.CELL_TYPE_NUMERIC: double valor = celda.getNumericCellValue(); if (valor % 1 == 0) { return String.valueOf((int) valor); } return String.valueOf(valor); case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_BLANK: default: return ""; } }
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 *//* w w w . jav a2s . 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.util.ExcelHelper.java
private String cellToString(Cell cell) { String result = null;/* w w w. j av a 2 s . c om*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue().toString(); } else { int i = (int) cell.getNumericCellValue(); double d = cell.getNumericCellValue(); if (i == d) { result = String.valueOf(i); } else { result = String.valueOf(d); } } break; case Cell.CELL_TYPE_BOOLEAN: result = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; default: result = null; } return result; }
From source file:br.com.gartech.nfse.integrador.view.ImportarView.java
private Object getCellValue(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_NUMERIC: return cell.getNumericCellValue(); }//from w w w . j a v a 2 s . c o m return null; }
From source file:br.com.tecsinapse.dataio.util.WorkbookUtil.java
License:LGPL
public static boolean isStringEmpty(Cell cell) { return isString(cell) && cell.getStringCellValue().trim().isEmpty(); }
From source file:br.com.tiagods.model.Arquivo.java
public String tratarTipo(Cell celula) { //metodo usado para tratar as celulas switch (celula.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celula)) return sdf.format(celula.getDateCellValue());//campo do tipo data formatando no ato else {/*from ww w .j a va2 s . c o m*/ return String.valueOf((long) celula.getNumericCellValue());//campo do tipo numerico, convertendo double para long } case Cell.CELL_TYPE_STRING: return String.valueOf(celula.getStringCellValue());//conteudo do tipo texto case Cell.CELL_TYPE_BOOLEAN: return "";//conteudo do tipo booleano case Cell.CELL_TYPE_BLANK: return "";//em branco default: return ""; } }