List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluateInCell
Cell evaluateInCell(Cell cell);
From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java
License:Open Source License
public static String getCellValue(Cell cell) { // 2016.01.20 j.amano // ?jxl to poi ????? //------------------------ //??:\-1,000/* w ww .ja v a 2 s. c om*/ //jxl:($1,000)?$????????? //poi:-1000 //------------------------ //??:2016/1/20 //jxl:0020, 1 20, 2016 //poi:2016/01/20 00:00:00 //------------------------ //??:#REF!??? //jxl:#REF! //poi:#REF! //------------------------ //??:1,000 //jxl:" "1,000 //poi:-1000 //------------------------ if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_NUMERIC: // ?? if (DateUtil.isCellDateFormatted(cell)) { // ???? Date dt = cell.getDateCellValue(); // ???? DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); String sDate = df.format(dt); return sDate; } // ???.0 DecimalFormat format = new DecimalFormat("0.#"); return format.format(cell.getNumericCellValue()); case Cell.CELL_TYPE_FORMULA: Workbook wb = cell.getSheet().getWorkbook(); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator(); return getCellValue(evaluator.evaluateInCell(cell)); case Cell.CELL_TYPE_ERROR: byte errorCode = cell.getErrorCellValue(); FormulaError error = FormulaError.forInt(errorCode); String errorText = error.getString(); return errorText; default: return ""; } } return ""; }
From source file:com.asakusafw.testdriver.excel.ExcelSheetDataModelSource.java
License:Apache License
private void evaluateInCell(Cell cell) throws IOException { try {/*w ww . j ava 2 s . c o m*/ Workbook workbook = cell.getSheet().getWorkbook(); FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); formulaEvaluator.evaluateInCell(cell); } catch (RuntimeException e) { throw new IOException(MessageFormat.format( Messages.getString("ExcelSheetDataModelSource.errorFailedToResolveFormulaCell"), //$NON-NLS-1$ id, cell.getRowIndex() + 1, cell.getColumnIndex() + 1), e); } }
From source file:com.github.camaral.sheeco.processor.PayloadFiller.java
License:Apache License
public static <T> List<SpreadsheetViolation> fillAttributes(final T payload, final Row row, final List<Attribute> attributes, final FormulaEvaluator evaluator) { final List<SpreadsheetViolation> violations = new ArrayList<>(); for (final Attribute attr : attributes) { final Cell cell = row.getCell(attr.getColumnIndex(), Row.CREATE_NULL_AS_BLANK); evaluator.evaluateInCell(cell); if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { violations.add(new SpreadsheetViolation("serializer.spreadsheet.cell.error", cell)); continue; }//from w w w.j av a 2 s . c om try { attr.setValue(payload, cell); } catch (final InvalidCellValueException e) { violations.add(new SpreadsheetViolation("serializer.spreadsheet.cell.invalid", cell)); continue; } catch (final InvalidCellFormatException e) { violations.add(new SpreadsheetViolation("serializer.spreadsheet.cell.type.invalid", cell)); continue; } } return violations; }
From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java
License:Open Source License
private String createInsert(final String tableName, final List<Entry<String, ExcelType>> types, final Row row) { //Iterate/* w w w. j a va 2 s.c om*/ final FormulaEvaluator evaluator = row.getSheet().getWorkbook().getCreationHelper() .createFormulaEvaluator(); final Map<String, String> colVals = new HashMap<String, String>(); int columnCount = 0; for (Entry<String, ExcelType> sourceType : types) { if (isSet(sourceType)) { Cell cell = row.getCell(columnCount); if (cell != null) { cell = evaluator.evaluateInCell(cell); try { final String value; switch (sourceType.getValue()) { case DATE: value = "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(getCellValue(cell)) + "'"; colVals.put(sourceType.getKey(), value); break; case NUMERIC: value = String.valueOf(getCellValue(cell)); colVals.put(sourceType.getKey(), value); break; case BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); colVals.put(sourceType.getKey(), value); break; case STRING: value = String.valueOf(getCellValue(cell)).replaceAll("'", "\\\\'"); if (!value.isEmpty()) { colVals.put(sourceType.getKey(), "'" + value + "'"); } break; } } catch (Exception ex) { if (strict) { throw new RuntimeException("Failed to process cell value: " + getCellValue(cell) + ", of column:row " + columnCount + ":" + row.getRowNum() + ", expecting type: " + sourceType.getValue().toString(), ex); } } } } columnCount++; } return createInsertStatement(colVals, tableName); }
From source file:com.hust.zsuper.DealWithPatent.WorkhseetToMySQL.java
License:Open Source License
private String createInsertStatement(final Row row) { //Iterate//from www . j a v a2 s. c o m final StringBuilder columns = new StringBuilder(); final StringBuilder values = new StringBuilder(); final FormulaEvaluator evaluator = row.getSheet().getWorkbook().getCreationHelper() .createFormulaEvaluator(); int nullCount = 0; int columnCount = 0; for (Entry<String, ExcelType> sourceType : types) { if (isSet(sourceType)) { columns.append("`").append(sourceType.getKey()).append("`").append(","); Cell cell = row.getCell(columnCount); if (cell == null) { values.append("null").append(","); } else { cell = evaluator.evaluateInCell(cell); final String stringValue = getStringValue(sourceType.getValue(), cell); if (stringValue == null) { nullCount++; } values.append(stringValue).append(","); } } columnCount++; } columns.deleteCharAt(columns.length() - 1); values.deleteCharAt(values.length() - 1); if (nullCount >= columnCount) { return null; } return "INSERT INTO `" + tableName + "` (" + columns + ") VALUES (" + values + ");"; }
From source file:com.joalgoca.validatorLayout.layoutDefinition.XLSXDocumentLayout.java
@Override public ResponseValidator validateDocument(InputStream inputStream) { ResponseValidator response;//from www .ja v a 2 s. c o m 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.r573.enfili.common.doc.spreadsheet.SpreadsheetHelper.java
License:Apache License
public static void calculate(Sheet sheet) { FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); for (Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.hasNext();) { Row row = rowIterator.next();/*from w w w . j a v a2s. c o m*/ for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) { Cell cell = cellIterator.next(); if (cell != null && cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { evaluator.evaluateInCell(cell); } } } }
From source file:com.ssy.havefun.f3d.F3DDaoImpl.java
public String getCellValue(Cell cell) { String ret = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: ret = ""; break;/* w ww . java2s .co m*/ case Cell.CELL_TYPE_BOOLEAN: ret = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: ret = null; break; case Cell.CELL_TYPE_FORMULA: Workbook wb = cell.getSheet().getWorkbook(); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator(); ret = getCellValue(evaluator.evaluateInCell(cell)); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // Date theDate = cell.getDateCellValue(); // ret = simpleDateFormat.format(theDate); } else { ret = NumberToTextConverter.toText(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: ret = cell.getRichStringCellValue().getString(); break; default: ret = null; } return ret; //?trim }
From source file:controller.FileUploadView.java
public void uploadEstudiantes(FileUploadEvent event) throws SQLException, ClassNotFoundException { //// w w w . j a v a2s.c o m FacesMessage msg = new FacesMessage("xito! ", event.getFile().getFileName() + " est cargado."); FacesContext.getCurrentInstance().addMessage(null, msg); // Do what you want with the file if (event.getFile().getFileName().equals("Estudiantes.xlxs")) { FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); FacesContext.getCurrentInstance().addMessage(null, message); } else { FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_WARN, "Incorrect Username and Passowrd", "Please enter correct username and Password")); } // Do what you want with the file try { copyFile(event.getFile().getFileName(), event.getFile().getInputstream()); if (event.getFile().getFileName().equals("Estudiantes.xlxs")) { FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded."); FacesContext.getCurrentInstance().addMessage(null, message); } else { FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_WARN, "Incorrect Username and Passowrd", "Please enter correct username and Password")); } String fileName = destination + event.getFile().getFileName(); File File1 = new File(fileName); FileInputStream fileInputStream; Workbook workbook = null; Sheet sheet; Iterator<Row> rowIterator; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/horarios", "root", ""); con.setAutoCommit(false); PreparedStatement pstm = null; fileInputStream = new FileInputStream(File1); String fileExtension = fileName.substring(fileName.indexOf(".")); System.out.println(fileExtension); if (fileExtension.equals(".xls")) { workbook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream)); } else if (fileExtension.equals(".xlsx")) { workbook = new XSSFWorkbook(fileInputStream); } else { System.out.println("Wrong File Type"); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); Row row; int i; for (i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String APELLIDO_PATERNO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(0)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(0).getNumericCellValue() + " "); APELLIDO_PATERNO = Integer.toString((int) row.getCell(0).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(0).getStringCellValue() + " "); APELLIDO_PATERNO = row.getCell(0).getStringCellValue(); break; } String APELLIDO_MATERNO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(1)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(1).getNumericCellValue() + " "); APELLIDO_MATERNO = Integer.toString((int) row.getCell(1).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(1).getStringCellValue() + " "); APELLIDO_MATERNO = row.getCell(1).getStringCellValue(); break; } String NOMBRES = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(2)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(2).getNumericCellValue() + " "); NOMBRES = Integer.toString((int) row.getCell(2).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(2).getStringCellValue() + " "); NOMBRES = row.getCell(2).getStringCellValue(); break; } String CEDULA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(3)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(3).getNumericCellValue() + " "); CEDULA = Integer.toString((int) row.getCell(3).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(3).getStringCellValue() + " "); CEDULA = row.getCell(3).getStringCellValue(); break; } String CARRERA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(4)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(4).getNumericCellValue() + " "); CARRERA = Integer.toString((int) row.getCell(4).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(4).getStringCellValue() + " "); CARRERA = row.getCell(4).getStringCellValue(); break; } String CURSO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(5)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(5).getNumericCellValue() + " "); CURSO = Integer.toString((int) row.getCell(5).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(5).getStringCellValue() + " "); CURSO = row.getCell(5).getStringCellValue(); break; } String CODIGOMATRICULA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(6)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(6).getNumericCellValue() + " "); CODIGOMATRICULA = Integer.toString((int) row.getCell(6).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(6).getStringCellValue() + " "); CODIGOMATRICULA = row.getCell(6).getStringCellValue(); break; } String DIRECCION = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(7)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(7).getNumericCellValue() + " "); DIRECCION = Integer.toString((int) row.getCell(7).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(7).getStringCellValue() + " "); DIRECCION = row.getCell(7).getStringCellValue(); break; } String TELEFONO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(8)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(8).getNumericCellValue() + " "); TELEFONO = Integer.toString((int) row.getCell(8).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(8).getStringCellValue() + " "); TELEFONO = row.getCell(8).getStringCellValue(); break; } String CELULAR = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(9)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(9).getNumericCellValue() + " "); CELULAR = Integer.toString((int) row.getCell(9).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(9).getStringCellValue() + " "); CELULAR = row.getCell(9).getStringCellValue(); break; } String CORREO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(10)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(10).getNumericCellValue() + " "); CORREO = Integer.toString((int) row.getCell(10).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(10).getStringCellValue() + " "); CORREO = row.getCell(10).getStringCellValue(); break; } String CORREOUCE = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(11)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(11).getNumericCellValue() + " "); CORREOUCE = Integer.toString((int) row.getCell(11).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(11).getStringCellValue() + " "); CORREOUCE = row.getCell(11).getStringCellValue(); break; } String CORREOCONTACTO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(12)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(12).getNumericCellValue() + " "); CORREOCONTACTO = Integer.toString((int) row.getCell(12).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(12).getStringCellValue() + " "); CORREOCONTACTO = row.getCell(12).getStringCellValue(); break; } String ESTADOCIVIL = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(13)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(13).getNumericCellValue() + " "); ESTADOCIVIL = Integer.toString((int) row.getCell(13).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(13).getStringCellValue() + " "); ESTADOCIVIL = row.getCell(13).getStringCellValue(); break; } String PAIS = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(14)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(14).getNumericCellValue() + " "); PAIS = Integer.toString((int) row.getCell(14).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(14).getStringCellValue() + " "); PAIS = row.getCell(14).getStringCellValue(); break; } String PROVINCIA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(15)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(15).getNumericCellValue() + " "); PROVINCIA = Integer.toString((int) row.getCell(15).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(15).getStringCellValue() + " "); PROVINCIA = row.getCell(15).getStringCellValue(); break; } String CANTON = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(16)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(16).getNumericCellValue() + " "); CANTON = Integer.toString((int) row.getCell(16).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(16).getStringCellValue() + " "); CANTON = row.getCell(16).getStringCellValue(); break; } String SEXO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(17)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(17).getNumericCellValue() + " "); SEXO = Integer.toString((int) row.getCell(17).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(17).getStringCellValue() + " "); SEXO = row.getCell(17).getStringCellValue(); break; } String NACIONALIDAD = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(18)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(18).getNumericCellValue() + " "); NACIONALIDAD = Integer.toString((int) row.getCell(18).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(18).getStringCellValue() + " "); NACIONALIDAD = row.getCell(18).getStringCellValue(); break; } //CI= Integer.toString((int) row.getCell(1).getNumericCellValue()); String sql = "INSERT INTO ESTUDIANTE (APELLIDO_PATERNO,APELLIDO_MATERNO,NOMBRES,CEDULA,CARRERA,CURSO,CODIGO_MATRICULA,DIRECCION,TELEFONO,CELULAR,CORREO,CORREO_UCE,CORREO_CONTACTO,ESTADO_CIVIL,PAIS,PROVINCIA,CANTON,SEXO,NACIONALIDAD) " + "VALUES('" + APELLIDO_PATERNO + "','" + APELLIDO_MATERNO + "','" + NOMBRES + "','" + CEDULA + "','" + CARRERA + "','" + CURSO + "','" + CODIGOMATRICULA + "','" + DIRECCION + "','" + TELEFONO + "','" + CELULAR + "','" + CORREO + "','" + CORREOUCE + "','" + CORREOCONTACTO + "','" + ESTADOCIVIL + "','" + PAIS + "','" + PROVINCIA + "','" + CANTON + "','" + SEXO + "','" + NACIONALIDAD + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } i--; FacesMessage msg1 = new FacesMessage("Se han cargado : ", +i + " Docenntes"); FacesContext.getCurrentInstance().addMessage(null, msg1); con.commit(); pstm.close(); con.close(); fileInputStream.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } }
From source file:controller.FileUploadView.java
public void uploadDocente(FileUploadEvent event) throws SQLException, ClassNotFoundException { ///*from w w w . j ava 2 s . com*/ FacesMessage msg = new FacesMessage("xito! ", event.getFile().getFileName() + " est cargado."); FacesContext.getCurrentInstance().addMessage(null, msg); // Do what you want with the file // Do what you want with the file try { copyFile(event.getFile().getFileName(), event.getFile().getInputstream()); //readExcelFile(destination+event.getFile().getFileName()); String fileName = destination + event.getFile().getFileName(); File File1 = new File(fileName); FileInputStream fileInputStream; Workbook workbook = null; Sheet sheet; Iterator<Row> rowIterator; try { Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/horarios", "root", ""); con.setAutoCommit(false); PreparedStatement pstm = null; fileInputStream = new FileInputStream(File1); String fileExtension = fileName.substring(fileName.indexOf(".")); System.out.println(fileExtension); if (fileExtension.equals(".xls")) { workbook = new HSSFWorkbook(new POIFSFileSystem(fileInputStream)); } else if (fileExtension.equals(".xlsx")) { workbook = new XSSFWorkbook(fileInputStream); } else { System.out.println("Wrong File Type"); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); Row row; int i; for (i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); String NOMBRE = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(0)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(0).getNumericCellValue() + " "); NOMBRE = Integer.toString((int) row.getCell(0).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(0).getStringCellValue() + " "); NOMBRE = row.getCell(0).getStringCellValue(); break; } String CI = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(1)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(1).getNumericCellValue() + " "); CI = Integer.toString((int) row.getCell(1).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(1).getStringCellValue() + " "); CI = row.getCell(1).getStringCellValue(); break; } //CI= Integer.toString((int) row.getCell(1).getNumericCellValue()); String EMAIL_INST = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(2)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(2).getNumericCellValue() + " "); EMAIL_INST = Integer.toString((int) row.getCell(2).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(2).getStringCellValue() + " "); EMAIL_INST = row.getCell(2).getStringCellValue(); break; } String EMAIL_PERSO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(3)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(3).getNumericCellValue() + " "); EMAIL_PERSO = Integer.toString((int) row.getCell(3).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(3).getStringCellValue() + " "); EMAIL_PERSO = row.getCell(3).getStringCellValue(); break; } String CATEGORIA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(4)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(4).getNumericCellValue() + " "); CATEGORIA = Integer.toString((int) row.getCell(4).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(4).getStringCellValue() + " "); CATEGORIA = row.getCell(4).getStringCellValue(); break; } String DEDICACION = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(5)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(5).getNumericCellValue() + " "); DEDICACION = Integer.toString((int) row.getCell(5).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(5).getStringCellValue() + " "); DEDICACION = row.getCell(5).getStringCellValue(); break; } String ESTADO = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(6)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(6).getNumericCellValue() + " "); ESTADO = Integer.toString((int) row.getCell(6).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(6).getStringCellValue() + " "); ESTADO = row.getCell(6).getStringCellValue(); break; } String TIEMPO_HORA = ""; switch (evaluator.evaluateInCell((Cell) row.getCell(7)).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print("numero " + row.getCell(7).getNumericCellValue() + " "); TIEMPO_HORA = Integer.toString((int) row.getCell(7).getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.print("TEXTO " + row.getCell(7).getStringCellValue() + " "); TIEMPO_HORA = row.getCell(7).getStringCellValue(); break; } String sql = "INSERT INTO DOCENTE (NOMBRE,CI,EMAIL_INSTITUCIONAL,EMAIL_PERSONAL,CATEGORIA,DEDICACION,ESTADO,TIEMPOHORA) VALUES('" + NOMBRE + "','" + CI + "','" + EMAIL_INST + "','" + EMAIL_PERSO + "','" + CATEGORIA + "','" + DEDICACION + "','" + ESTADO + "','" + TIEMPO_HORA + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } i--; FacesMessage msg1 = new FacesMessage("Se han cargado : ", +i + " Docenntes"); FacesContext.getCurrentInstance().addMessage(null, msg1); con.commit(); pstm.close(); con.close(); fileInputStream.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } }