List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getNumericCellValue
@Override public double getNumericCellValue()
From source file:fr.unice.i3s.rockflows.experiments.automatictest.ExcelUtils.java
/** * Get the double value contianed in a given {@link XSSFCell} * * @param cell//from w w w .j a v a 2 s .c o m * The {@link XSSFCell}. * @return the double value if the {@link XSSFCell} if the type of the cell * is {@code Cell.CELL_TYPE_NUMERIC}, * otherwise {@code Double.NEGATIVE_INFINITY} */ public static double getDoubleValue(final XSSFCell cell) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return cell.getNumericCellValue(); } return Double.NEGATIVE_INFINITY; }
From source file:fr.unice.i3s.rockflows.experiments.automatictest.ExcelUtils.java
/** * Get the int value contianed in a given {@link XSSFCell} * * @param cell// ww w .ja va 2 s .c o m * The {@link XSSFCell}. * @return the int value if the {@link XSSFCell} if the type of the cell * is {@code Cell.CELL_TYPE_NUMERIC}, * otherwise {@code Integer.MIN_VALUE} */ public static int getIntValue(final XSSFCell cell) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return (int) cell.getNumericCellValue(); } return Integer.MIN_VALUE; }
From source file:Import.ImportData.java
protected BigDecimal getBigDecimalCellValue(XSSFCell cell) throws NullPointerException { try {//from w w w . ja va 2 s.c o m switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: { return new BigDecimal(cell.getNumericCellValue()); } case XSSFCell.CELL_TYPE_STRING: { String stringDouble = cell.getStringCellValue(); return new BigDecimal(stringDouble); } default: throw new NullPointerException(); } } catch (NumberFormatException e) { throw new NullPointerException(); } }
From source file:Import.ImportData.java
protected Object getCellValue(XSSFCell cell) throws NullPointerException { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: { return cell.getNumericCellValue(); }/* w w w. j a va2 s .c om*/ case XSSFCell.CELL_TYPE_STRING: { String value = cell.getStringCellValue(); if ("".equals(value)) throw new NullPointerException(); return value; } default: throw new NullPointerException(); } }
From source file:Import.ImportData.java
protected String getStringCellValue(XSSFCell cell) throws NullPointerException { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: { DecimalFormat f = new DecimalFormat("##");//"##"); return f.format(cell.getNumericCellValue()); }//from ww w . java 2 s . co m case XSSFCell.CELL_TYPE_STRING: { String value = cell.getStringCellValue(); if ("".equals(value)) throw new NullPointerException(); return value; } default: throw new NullPointerException(); } }
From source file:info.toegepaste.controller.UploadController.java
public void fileUploadListener(FileUploadEvent e) { // Get uploaded file from the FileUploadEvent to use with primefaces this.file = e.getFile(); // Get uploaded file to use with Apache POI InputStream POIFile;// w ww .j ava 2 s .co m XSSFWorkbook workbook = null; try { POIFile = e.getFile().getInputstream(); //Create workbook workbook = new XSSFWorkbook(POIFile); } catch (IOException ex) { } // Print out the information of the file System.out.println( "Uploaded File Name Is :: " + file.getFileName() + " :: Uploaded File Size :: " + file.getSize()); //Create a worksheet (needed to get rows) XSSFSheet worksheet = workbook.getSheetAt(0); //Divide worksheet into rows Iterator<Row> rowIterator = worksheet.iterator(); //Get Classgroup from line 1 cell 2 XSSFRow currentRow = (XSSFRow) rowIterator.next(); Iterator<Cell> klasIter = currentRow.cellIterator(); XSSFCell klasCell = currentRow.getCell(1); //Get Course from line 2 cell 2 currentRow = (XSSFRow) rowIterator.next(); Iterator<Cell> courseIter = currentRow.cellIterator(); XSSFCell courseCell = currentRow.getCell(1); //Get subject from line 3 cell 2 System.out.println("Stuff"); currentRow = (XSSFRow) rowIterator.next(); Iterator<Cell> subjectIter = currentRow.cellIterator(); XSSFCell subjectCell = currentRow.getCell(1); System.out.println("Subject:" + subjectCell.toString()); System.out.println("Subject:" + subjectCell.getStringCellValue()); //Get total possible score from line 4 cell 2 currentRow = (XSSFRow) rowIterator.next(); Iterator<Cell> totalScoreIter = currentRow.cellIterator(); XSSFCell totalScoreCell = currentRow.getCell(1); //Skip line 5 & 6 currentRow = (XSSFRow) rowIterator.next(); currentRow = (XSSFRow) rowIterator.next(); currentRow = (XSSFRow) rowIterator.next(); List<Classgroup> group = classgroupService.getWithName(klasCell.getStringCellValue()); //Persist new exam to database Exam newExam = new Exam(); Classgroup newGroup = new Classgroup(); //Check if classgroup already exists, create if it doesnt if (group.isEmpty()) { newGroup.setName(klasCell.getStringCellValue()); newGroup.setCourses(null); classgroupService.insert(newGroup); } else { newGroup = group.get(0); } newExam.setClassgroup(newGroup); List<Course> course = courseService.getWithName(courseCell.getStringCellValue()); Course newCourse = new Course(); //Check if course exists, if not create if (course.isEmpty()) { newCourse.setName(courseCell.getStringCellValue()); int year = Calendar.getInstance().get(Calendar.YEAR); newCourse.setYear(year); newCourse.setClassgroup(newGroup); int maand = Calendar.getInstance().get(Calendar.MONTH); if (maand <= 6 && maand >= 1) { newCourse.setSemester(2); } else { newCourse.setSemester(1); } courseService.insert(newCourse); } else { newCourse = course.get(0); } newExam.setCourse(newCourse); newExam.setName(subjectCell.getStringCellValue()); // double totalScoreValue = ; // String totalScoreWorkaround =String.valueOf(totalScoreValue); newExam.setTotal((int) totalScoreCell.getNumericCellValue()); examService.insert(newExam); //Read file to end, cell 0 student number, cell 1 name, cell 2 score while (rowIterator.hasNext()) { XSSFCell userNrCell = currentRow.getCell(0); System.out.println(userNrCell.toString()); int StudentNumber = 0; // String StudentNumberWorkaround =userNrCell.getStringCellValue(); StudentNumber = (int) userNrCell.getNumericCellValue(); List<Student> currentStudent = studentsService.getStudentInListByNumber(StudentNumber); Student newStudent = new Student(); XSSFCell userNameCell = currentRow.getCell(1); //Check if student exists, else create if (currentStudent.isEmpty()) { String fullName = userNameCell.getStringCellValue(); String nameArray[] = fullName.split(" "); newStudent.setFirstname(nameArray[0]); newStudent.setLastname(nameArray[1]); newStudent.setNumber(StudentNumber); newStudent.setEmail("r0" + StudentNumber + "@student.thomasmore.be"); newStudent.setClassgroup(newGroup); newStudent.setPassword(null); studentsService.insert(newStudent); } else { newStudent = currentStudent.get(0); } //Add score to student List<Score> currentScore = scoreService.checkIfScoreExists(newStudent, newExam); XSSFCell scoreCell = currentRow.getCell(2); Score scoreEntry = new Score(); if (currentScore.isEmpty()) { scoreEntry.setExam(newExam); scoreEntry.setScore((int) scoreCell.getNumericCellValue()); if (currentStudent.isEmpty()) { scoreEntry.setStudent(newStudent); } else { scoreEntry.setStudent(currentStudent.get(0)); } scoreService.insert(scoreEntry); } else { scoreEntry = currentScore.get(0); } currentRow = (XSSFRow) rowIterator.next(); } }
From source file:logic.Export.java
public boolean convertXls2() throws IOException, FileNotFoundException, IllegalArgumentException, ParseException { FileInputStream tamplateFile = new FileInputStream(templatePath); XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##")); double hours = 0.0; NumberFormat format = NumberFormat.getInstance(Locale.FRANCE); Number number;/*from ww w . java 2 s . c om*/ XSSFSheet sheet; XSSFSheet sheet2; Cell cell = null; ConvertData cd = new ConvertData(); for (int i = 0; i < cd.getSheetnames().size(); i++) { sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i)); sheet = workbook.getSheetAt(i + 1); cell = sheet.getRow(0).getCell(1); cell.setCellValue(cd.sheetnames.get(i)); ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i)); for (int Row = 0; Row < convert.size(); Row++) { for (int Cell = 0; Cell < convert.get(Row).length; Cell++) { cell = sheet.getRow(9 + Row).getCell(Cell + 1); String name; switch (Cell) { case 3: name = convert.get(Row)[Cell]; int parseInt = Integer.parseInt(name); cell.setCellValue(parseInt); cell.setCellType(CellType.NUMERIC); break; case 4: number = format.parse(convert.get(Row)[Cell]); cell.setCellValue(number.doubleValue()); // cell.setCellStyle(cellStyle); cell.setCellType(CellType.NUMERIC); break; default: cell.setCellValue(convert.get(Row)[Cell]); break; } } } for (String[] sa : convert) { number = format.parse(sa[4]); hours = hours + number.doubleValue(); } cell = sheet.getRow(6).getCell(5); cell.setCellValue(hours); cell = sheet.getRow(2).getCell(8); XSSFCell cellHourlyRate = sheet.getRow(1).getCell(8); double numericCellValue = cellHourlyRate.getNumericCellValue(); cell.setCellValue(hours * numericCellValue); } workbook.removeSheetAt(0); tamplateFile.close(); File exportFile = newPath.getSelectedFile(); if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) { } else { exportFile = new File(exportFile.getParentFile(), FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx"); } FileOutputStream outFile = new FileOutputStream(exportFile); workbook.write(outFile); outFile.close(); tamplateFile.close(); return true; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static List<SEPatient> readPatients(XSSFSheet xlSheet) { String property, value, unit; List<SEPatient> patients = new ArrayList<SEPatient>(); try {/* ww w . j ava2 s. c o m*/ int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); if (r == 0) {// Allocate the number of patients we have for (int i = 1; i < cells; i++) patients.add(new SEPatient()); } property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; Log.info("Processing Patient Field : " + property); for (int c = 1; c < cells; c++) { String cellValue = null; XSSFCell cell = row.getCell(c); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } if (cellValue == null || cellValue.isEmpty()) continue; int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } if (value.equals("INF")) value = "Infinity"; if (!setProperty(patients.get(c - 1), property, value, unit)) { Log.error("Error pulling" + property + " from " + cellValue); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return patients; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static Map<String, SESubstance> readSubstances(XSSFSheet xlSheet) { EnumAnatomy currCmpt = null;//from w w w . j av a 2s. com String property, value, unit; SESubstance substance = null; SESubstanceAnatomyEffect afx = null; List<SESubstance> substances = new ArrayList<SESubstance>(); Set<Integer> skipColumns = new HashSet<Integer>(); try { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); if (r == 0) { for (int c = 1; c < cells; c++) { property = row.getCell(c).getStringCellValue().trim(); if (property.equals("Reference Value") || property.equals("Reference Source") || property.equals("Notes/Page")) skipColumns.add(c); } } property = row.getCell(0).getStringCellValue().trim(); if (property == null || property.isEmpty()) continue; Log.info("Processing Substance Field : " + property); if (property.indexOf("Compartment Effects") > -1) { if (property.indexOf("Myocardium") > -1) currCmpt = EnumAnatomy.MYOCARDIUM; else if (property.indexOf("Fat") > -1) currCmpt = EnumAnatomy.FAT; else if (property.indexOf("Kidneys") > -1) currCmpt = EnumAnatomy.KIDNEYS; else if (property.indexOf("Brain") > -1) currCmpt = EnumAnatomy.BRAIN; else if (property.indexOf("Muscle") > -1) currCmpt = EnumAnatomy.MUSCLE; else if (property.indexOf("Skin") > -1) currCmpt = EnumAnatomy.SKIN; else if (property.indexOf("Bone") > -1) currCmpt = EnumAnatomy.BONE; else if (property.indexOf("Gut") > -1) currCmpt = EnumAnatomy.GUT; else if (property.indexOf("Splanchnic") > -1) currCmpt = EnumAnatomy.SPLANCHNIC; else if (property.indexOf("Spleen") > -1) currCmpt = EnumAnatomy.SPLEEN; else if (property.indexOf("Large Intestine") > -1) currCmpt = EnumAnatomy.LARGE_INTESTINE; else if (property.indexOf("Small Intestine") > -1) currCmpt = EnumAnatomy.SMALL_INTESTINE; else if (property.indexOf("Liver") > -1) currCmpt = EnumAnatomy.LIVER; else if (property.indexOf("Right Lung") > -1) currCmpt = EnumAnatomy.RIGHT_LUNG; else if (property.indexOf("Left Lung") > -1) currCmpt = EnumAnatomy.LEFT_LUNG; else { Log.error("Unsupported Anatomy Compartment : " + property); break; } } int s = -1; for (int c = 1; c < cells; c++) { if (skipColumns.contains(c)) continue; s++; String cellValue = null; XSSFCell cell = row.getCell(c); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_FORMULA: switch (evaluator.evaluateFormulaCell(cell)) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } } if (cellValue == null) continue; cellValue = cellValue.trim(); if (cellValue.isEmpty()) continue; if (property.equals("Name")) { substance = new SESubstance(); substances.add(substance); } int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } if (value.equals("INF")) value = "Infinity"; substance = substances.get(c - (3 * s) - 1); if (currCmpt == null) afx = null; else afx = substance.getAnatomyEffect(currCmpt); if (!setProperty(substance, afx, property, value, unit)) { Log.error("Error pulling" + property + " from " + cellValue); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } Map<String, SESubstance> map = new HashMap<String, SESubstance>(); for (SESubstance sub : substances) map.put(sub.getName(), sub); return map; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
protected static List<SESubstanceCompound> readCompounds(XSSFSheet xlSheet, Map<String, SESubstance> substances) { String property, value, unit; SESubstance s;/* w w w .j av a 2s . co m*/ SESubstanceCompound compound = null; SESubstanceCompoundComponent component = null; List<SESubstanceCompound> compounds = new ArrayList<SESubstanceCompound>(); try { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = row.getPhysicalNumberOfCells(); if (r == 0) {// Allocate the number of patients we have for (int i = 1; i < cells; i++) compounds.add(new SESubstanceCompound()); } property = row.getCell(0).getStringCellValue(); if (property == null || property.isEmpty()) continue; Log.info("Processing Patient Field : " + property); if (property.equals("Data Type")) continue;// Only one type at this point for (int c = 1; c < cells; c++) { String cellValue = null; XSSFCell cell = row.getCell(c); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } if (cellValue == null || cellValue.isEmpty()) continue; int split = cellValue.indexOf(" "); // Pull The Value if (split == -1) { value = cellValue; unit = ""; } else { value = cellValue.substring(0, split); unit = cellValue.substring(split + 1); } compound = compounds.get(c - 1); if (property.equals("Compound Name")) { compound.setName(value); continue; } if (property.equals("Component Name")) { s = substances.get(value); component = compound.getComponent(s); continue; } if (!setProperty(component, property, value, unit)) { Log.error("Error setting property"); break; } } } } catch (Exception ex) { Log.error("Error reading XLS", ex); return null; } return compounds; }