List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:com.jmc.jfxxlsdiff.util.POIXlsUtil.java
public static Object getCellValue(Cell cell) { Object cv = null;//from w w w . java 2 s . c o m switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: { break; } case Cell.CELL_TYPE_BOOLEAN: { cv = cell.getBooleanCellValue(); break; } case Cell.CELL_TYPE_ERROR: { cv = cell.getErrorCellValue(); break; } case Cell.CELL_TYPE_FORMULA: { cv = getFormulaValue(cell); break; } case Cell.CELL_TYPE_NUMERIC: { if (DateUtil.isCellDateFormatted(cell)) { // format in form of M/D/YY //Calendar cal = Calendar.getInstance(); //cal.setTime( DateUtil.getJavaDate( d ) ); //cv = cal.getTime(); cv = cell.getDateCellValue(); } else { cv = cell.getNumericCellValue(); } break; } case Cell.CELL_TYPE_STRING: { cv = cell.getStringCellValue(); break; } default: { logger.log(Level.WARNING, "Unexpected cell type = {0}", cell.getCellType()); break; } } return cv; }
From source file:com.joalgoca.validatorLayout.layoutDefinition.XLSXDocumentLayout.java
@Override public ResponseValidator validateDocument(InputStream inputStream) { ResponseValidator response;/* ww w . ja va2s. c om*/ StringBuilder stringBuilder = new StringBuilder(); if (isReadyToValidate() && inputStream != null) { HashMap rowsType = new HashMap(); for (int i = 0; i < documentValidator.getListRowValidator().size(); i++) { rowsType.put(documentValidator.getListRowValidator().get(i).getName(), i); } try { int rownum = 0; int wrong = 0; int right = 0; int skip = 0; OPCPackage pkg = OPCPackage.open(inputStream); XSSFWorkbook workBook = new XSSFWorkbook(pkg); FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator(); XSSFSheet xssfSheet = workBook.getSheetAt(0); for (Row row : xssfSheet) { String rowType = row.getCell(0).getStringCellValue(); if (rowsType.containsKey(rowType)) { RowValidator rowValidator = documentValidator.getListRowValidator() .get((int) rowsType.get(rowType)); int columnNum = rowValidator.getListItemValidator().size(); if (row.getLastCellNum() == columnNum) { String[] values = new String[columnNum]; int i = 0; for (Cell cell : row) { switch (evaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: values[i] = cell.getNumericCellValue() + ""; break; case Cell.CELL_TYPE_STRING: values[i] = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: values[i] = ""; break; case Cell.CELL_TYPE_BLANK: values[i] = ""; break; } i++; } ResponseValidator responseValidator = rowValidator.validate(values); if (!responseValidator.isSuccess()) { wrong++; stringBuilder.append("{\"row\":").append(rownum).append(",\"message\":") .append(responseValidator.getMessage()).append(","); } else right++; } else { wrong++; stringBuilder.append("{\"row\":").append(rownum) .append(",\"success\":false,\"message\":\"Line wrong size\"},").toString(); } } else { skip++; stringBuilder.append("{\"row\":").append(rownum) .append(",\"success\":false,\"message\":\"Unknow row type\"},").toString(); } rownum++; } response = new ResponseValidator(wrong == 0, "{\"skip\":" + skip + ",\"wrong\":" + wrong + ",\"right\":" + right + ",\"count\":" + rownum + ",\"errorMessages\":[" + (stringBuilder.toString().length() > 0 ? stringBuilder.substring(0, stringBuilder.toString().length() - 1) : "") + "]}"); } catch (Exception ex) { Logger.getLogger(FlatFixedDocumentLayout.class.getName()).log(Level.SEVERE, null, ex); response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"") .append(ex.getMessage()).append("\"}").toString()); } finally { try { inputStream.close(); } catch (IOException ex) { response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"") .append(ex.getMessage()).append("\"}").toString()); } } } else { response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"No configuration loaded\"}").toString()); } return response; }
From source file:com.jogo.dao.RepositorioDao.java
@Override public List importar() { //arry de usuario para armazenar os meus usuarios que pega do excel na folha 1 List<Usuario> usuarios = new ArrayList<>(); //CAPTURA OS DADOS DO USUARIO NO EXCEL try {//from ww w .j a v a 2 s. co m //capturando o excel para meu wb wb = WorkbookFactory.create(new FileInputStream(patch)); //CAPTURAR A PRIMEIRA FOLHA DO EXCEL Sheet folha = wb.getSheetAt(0); //criO um iterator para interagir com as linhas Iterator filaIterator = folha.rowIterator(); //ENQUANTO HOUVER LINHAS O ITERATOR ME TRAZ. while (filaIterator.hasNext()) { //CAPTURO A LINHA DO EXCEL Row linha = (Row) filaIterator.next(); //CRIO UM INTERATOR PARA INTERAGIR COM AS COLUNAS Iterator colunaIterator = linha.cellIterator(); //CRIOU A CLASSE DE USUARIO E ADD DENTRO DO MEU ARRAY Usuario user = new Usuario(); usuarios.add(user); //ENAUQNTO HOUVER COLUNAS O INTERATOR ME TRAZ. while (colunaIterator.hasNext()) { //COM A LINHA E A COLUNA JA POSSO CRIAR UMA CELULA. Cell celula = (Cell) colunaIterator.next(); //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE USUARIO QUE CRIEI LOGO ACIMA. if (celula != null) { //CAPTURO O TIPO DA CELULA, NESSE CASO E STRING E NUMERICO(INT) switch (celula.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //CONVERTENDO O VALOR PARA INTEIRO. user.setPontuacao((int) Math.round(celula.getNumericCellValue())); break; case Cell.CELL_TYPE_STRING: user.setNome(celula.getStringCellValue()); break; } } } } } catch (IOException | InvalidFormatException | EncryptedDocumentException e) { } return usuarios; }
From source file:com.jogo.dao.RepositorioDao.java
@Override public List importarPerguntas(int index) { //CRIO UM ARRAY DE PERGUNTAS PARA ARMAZENAR AS PERGUNTAS BUSCAS DO EXCEL. List<Perguntas> perguntas = new ArrayList<>(); try {//from w w w .j a v a2 s.c o m //CAPTURANDO O EXCEL PARA MEU WB. wb = WorkbookFactory.create(new FileInputStream(patch)); //CAPTURO A FOLHA DO EXCEL PASSANDO O INDEX Sheet folha = wb.getSheetAt(index); //CRIO UM ITERATOR PARA INTERAGIR COM AS LINHAS. Iterator filaIterator = folha.rowIterator(); //ENQUANTO HOUVER INTERAO PEGA UMA LINHA. while (filaIterator.hasNext()) { //CAPTURO A LINHA DO EXCEL Row linha = (Row) filaIterator.next(); //CRIO UM ITERATOR PARA PEGAR AS COLUNAS Iterator colunaIterator = linha.cellIterator(); //AQUI DIGO QUE MINHAS COLUNAS NO PODE PASSAR DE 6, COMO TA A MINHA ESTRUTURA PARA O EXCEL //1 - PERGUNTA, 2- ALTERNATIVA, 3- ALTERNATIVA, 4- ATLTERNATIVA, 5- ALTERNATIVA, 6- RESPOSTA //CHEGOU MAIOR QUE 6 SAIU DO LOOP DE COLUNAS. if (linha.getLastCellNum() > 6) { break; } //CRIOU A CLASSE DE PERGUNTAS E ADD DENTRO DO MEU ARRAY Perguntas per = new Perguntas(); perguntas.add(per); //INTERAGIR COM AS COLUNAS, PEGAR AS COLUNAS DO EXCEL while (colunaIterator.hasNext()) { //TENDO A LINHA E COLUNA JA POSSO TER UMA CELULA. Cell celula = (Cell) colunaIterator.next(); //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE PERGUNTAS QUE CRIEI LOGO ACIMA. if (celula != null) { //CAPTURAR O TIPO DA CELULA, NO CASO TODAS AS PERGUNTAS E ALTERNATIVAS SO STRINGS. //OBS: OLHE QUE NESSE CASO S POSSO TRAZER STRING'S CASO CONTRARIO NO IR? FUNCIONAR.:/ switch (celula.getCellType()) { case Cell.CELL_TYPE_STRING: switch (celula.getColumnIndex()) { case 0: per.setPergunta(celula.getStringCellValue()); break; case 1: per.setAlt1(celula.getStringCellValue()); break; case 2: per.setAlt2(celula.getStringCellValue()); break; case 3: per.setAlt3(celula.getStringCellValue()); break; case 4: per.setAlt4(celula.getStringCellValue()); break; case 5: per.setResposta(celula.getStringCellValue()); break; default: break; } } } } } } catch (IOException | InvalidFormatException | EncryptedDocumentException e) { } return perguntas; }
From source file:com.jwm123.loggly.reporter.ReportGenerator.java
License:Apache License
public byte[] build(List<Map<String, String>> row) throws IOException { List<String> colLabels = new ArrayList<String>(); Sheet reportSheet = workbook.getSheet("Report"); if (reportSheet == null) { reportSheet = workbook.createSheet("Report"); }//www . ja va 2s . c o m Row firstRow = reportSheet.getRow(0); if (firstRow == null) { firstRow = reportSheet.createRow(0); int cellNum = 0; for (Map<String, String> col : row) { for (String key : col.keySet()) { Cell cell = firstRow.createCell(cellNum++); setCellValue(cell, key); } } } for (int i = 0; i < firstRow.getLastCellNum(); i++) { Cell cell = firstRow.getCell(i); if (cell != null) { colLabels.add(cell.getStringCellValue()); } } Row newRow = reportSheet.createRow(reportSheet.getLastRowNum() + 1); int lastIndex = -1; for (Map<String, String> col : row) { for (String key : col.keySet()) { int colNum = -1; Cell cell = null; if (colLabels.contains(key)) { colNum = colLabels.indexOf(key); lastIndex = colNum; } if (colNum == -1) { lastIndex++; colNum = lastIndex; shiftColumns(reportSheet, colNum, key); colLabels.add(colNum, key); } cell = newRow.getCell(colNum); if (cell == null) { cell = newRow.createCell(colNum); } setCellValue(cell, col.get(key)); } } for (int i = 0; i < firstRow.getLastCellNum(); i++) { reportSheet.autoSizeColumn(i); } ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); return baos.toByteArray(); }
From source file:com.jwm123.loggly.reporter.ReportGenerator.java
License:Apache License
private void shiftColumns(Sheet reportSheet, int colNum, String key) { boolean firstRow = true; for (Row row : reportSheet) { for (int i = row.getLastCellNum() + 1; i >= colNum; i--) { Cell nextCell = row.getCell(i + 1); Cell oldCell = row.getCell(i); if (oldCell != null) { if (nextCell == null) { nextCell = row.createCell(i + 1); }/* w w w .j a va 2 s .c om*/ setCellValue(nextCell, oldCell.getStringCellValue()); if (firstRow && i == colNum) { setCellValue(oldCell, key); firstRow = false; } else { setCellValue(oldCell, ""); } } else if (firstRow && i == colNum) { oldCell = row.createCell(i); setCellValue(oldCell, key); firstRow = false; } } } }
From source file:com.ksa.myanmarlottery.service.parser.ExcelFileParser.java
@Override public List<Result> getResult(InputStream in) throws FileNotFoundException, IOException, ParseException { List<Prize> prizes = null; List<Result> resultList = new ArrayList<>(); SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy"); // 01-May-2017 try {// w ww.j a v a 2 s . com Workbook workbook = new XSSFWorkbook(in); Sheet datatypeSheet = workbook.getSheetAt(0); Iterator<Row> iterator = datatypeSheet.iterator(); while (iterator.hasNext()) { Row currentRow = iterator.next(); Cell cell0 = currentRow.getCell(0); // get first cell. if (cell0.getCellTypeEnum() == CellType.NUMERIC) { int numberic = (int) cell0.getNumericCellValue(); log.info("Numberic - " + numberic); // check lottery type if (ConstantUtil.OLD_LOTTERY_TYPE == numberic || ConstantUtil.NEW_LOTTERY_TYPE == numberic) { // for lottery type result Result result = new Result(); result.setType(numberic); result.setNumberOfTimes((int) currentRow.getCell(1).getNumericCellValue()); // result.setResultFor(format.parse(currentRow.getCell(2).toString())); result.setResultFor(currentRow.getCell(2).getDateCellValue()); result.setDataProvider(currentRow.getCell(3).getStringCellValue()); result.setCompanyName(currentRow.getCell(4).getStringCellValue()); prizes = new ArrayList<>(); result.setPrizes(prizes); resultList.add(result); } } else if (cell0.getCellTypeEnum() == CellType.STRING) { // result data String character = cell0.getStringCellValue(); log.info("character - " + character); // check validation for character. String value = charMap.get(character); if (value == null) { throw new ParseException( "Character is Not valid at Row: " + currentRow.getRowNum() + " > column:" + 0, 400); } Cell cell1 = currentRow.getCell(1); if (cell1.getCellTypeEnum() != CellType.NUMERIC) { throw new ParseException( "Should be Number at Row: " + currentRow.getRowNum() + " > column:" + 1, 400); } log.info("Cell Type " + cell1.getCellTypeEnum()); int code = (int) cell1.getNumericCellValue(); log.info("code - " + code + " Row:" + currentRow.getRowNum() + " > column:" + 1); String prizeTitle = currentRow.getCell(2).getStringCellValue(); log.info("prizeTitle - " + prizeTitle); String prizeDesc = currentRow.getCell(4).getStringCellValue(); log.info("prizeDesc - " + prizeDesc); prizes.add(new Prize(character, code, prizeTitle, prizeDesc)); } } log.info("resultList size: " + resultList.size()); for (Result r : resultList) { log.info("prizeList size: " + r.getPrizes().size()); } } catch (FileNotFoundException e) { e.printStackTrace(); throw e; } catch (IOException e) { e.printStackTrace(); throw e; } return resultList; }
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Get the value contained in the cell.//w ww. j a va2 s . c o m * * @param cell the examined cell * @return the value as Boolean, Numeric, String, Blank, Error or Formula */ public static Object getCellValue(Cell cell) { return cell == null ? "" : cell.getCellType() == Cell.CELL_TYPE_BOOLEAN ? cell.getBooleanCellValue() : cell.getCellType() == Cell.CELL_TYPE_NUMERIC ? (DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue() : cell.getNumericCellValue()) : cell.getCellType() == Cell.CELL_TYPE_STRING ? cell.getStringCellValue() : cell.getCellType() == Cell.CELL_TYPE_BLANK ? cell.getStringCellValue() : cell.getCellType() == Cell.CELL_TYPE_ERROR ? cell.getErrorCellValue() : cell.getCellType() == Cell.CELL_TYPE_FORMULA ? cell.getCachedFormulaResultType() : cell.getStringCellValue(); }
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Insert a row at a given index.//w w w .j a va2s . co m * * @param createAtIndex row-number of the cell at which to create a new row * @param sourceRow the row to insert */ public void insertRowAt(int createAtIndex, Row sourceRow) { Row newRow = getRow(createAtIndex); if (newRow != null) { // shift all rows >= createAtIndex up by one getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1); } else { newRow = getSheet().createRow(createAtIndex); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { continue; } // Copy style from old cell and apply to new cell CellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < getSheet().getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); getSheet().addMergedRegion(newCellRangeAddress); } } }
From source file:com.larasolution.serverlts.FileUploadHandler.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // tablename=request.getParameter(tablename) //process only if its multipart content FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv"); String list = ""; List<List> allData = new ArrayList<List>(); List<String> parameters = new ArrayList<String>(); if (ServletFileUpload.isMultipartContent(request)) { try {/* w w w. j a v a 2s.c o m*/ StringBuilder data = new StringBuilder(); List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request); System.out.println(multiparts); for (FileItem item : multiparts) { if (item.isFormField()) { parameters.add(item.getFieldName()); System.out.println(parameters); } if (!item.isFormField()) { String name = new File(item.getName()).getName(); item.write(new File(UPLOAD_DIRECTORY + File.separator + name)); //System.out.println(File.separator); // Get the workbook object for XLSX file XSSFWorkbook wBook = new XSSFWorkbook( new FileInputStream(UPLOAD_DIRECTORY + File.separator + name)); XSSFSheet zz = wBook.getSheetAt(0); FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator(); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = zz.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.append(cell.getBooleanCellValue()).append(","); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { data.append( com.larasolution.modle.getDate.getDate5(cell.getDateCellValue())) .append(","); } else { data.append(cell.getNumericCellValue()).append(","); } break; case Cell.CELL_TYPE_STRING: data.append(cell.getStringCellValue()).append(","); break; case Cell.CELL_TYPE_BLANK: data.append("" + ","); break; case Cell.CELL_TYPE_FORMULA: Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString()); data.append(String.format("%.2f", value)).append(","); break; default: data.append(cell).append(""); } } data.append("\r\n"); //String k = data.substring(0, data.length() - 3); //ls.add(k); // data.setLength(0); } fos.write(data.toString().getBytes()); fos.close(); // } } savetosql(); request.setAttribute("message", "successfully uploaded "); } catch (Exception ex) { request.setAttribute("message", "File Upload Failed due to " + ex); } } else { request.setAttribute("message", "Sorry this Servlet only handles file upload request"); } request.setAttribute("arrayfile", allData); request.setAttribute("names", parameters); RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp"); disp.forward(request, response); // System.out.println(allData.size()); // response.sendRedirect("send.jsp?arrayfile=" + list + ""); //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response); }