List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue
double getNumericCellValue();
From source file:ia_app.PastPerformancesPanel.java
public PastPerformancesPanel() throws FileNotFoundException, IOException { initComponents();// w w w .j av a2 s .com FileInputStream fis = new FileInputStream(new File("testing.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator(); this.text = ""; for (Row row : sheet) { for (Cell cell : row) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.text = (text + cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: this.text = (text + cell.getStringCellValue() + "\t"); break; } } this.text = (text + "\n"); } this.jTextArea1.setText(text); }
From source file:ia_app.StatsPanel.java
/** * Creates new form StatsPanel/*from w w w. j a v a 2s. co m*/ */ public StatsPanel() throws FileNotFoundException, IOException { initComponents(); FileInputStream fis = new FileInputStream(new File("games.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator(); Row row1 = sheet.getRow(1); for (Cell cell : row1) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.makes += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextFieldMakes.setText(makes + ""); Row row2 = sheet.getRow(2); for (Cell cell : row2) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.attempts += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextFieldAttempts.setText(attempts + ""); this.jTextFieldFGPct.setText("58.3%"); Row row3 = sheet.getRow(3); for (Cell cell : row3) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.threeMakes += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } Row row4 = sheet.getRow(4); for (Cell cell : row4) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.threeAttempts += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextField3Pct.setText("80%"); /*for(Row column : sheet){ for(Cell cell : column){ switch(forEval.evaluateInCell(cell).getCellType()){ case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: break; } } } */ }
From source file:icalendarconverter.ExcelConverter.java
public List<ScheduleClass> readExcel() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);//from w w w. j av a 2s. c o m Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add; int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(f); if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("Nama Mata Kuliah")) { colMatkulIdx = cell.getColumnIndex(); break outerloop; } } } //System.out.println("col matkul = "+colMatkulIdx); System.out.println("sheet = " + sheet.getLastRowNum()); outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { row = sheet.getRow(i); if (row == null) { i = sheet.getLastRowNum(); break outerloop2; } Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && cell.getCellType() != Cell.CELL_TYPE_BLANK && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && cell.getCellType() == Cell.CELL_TYPE_BLANK) { i = i + 1; break outerloop; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { i = i + 1; break outerloop; } String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } if (sumary[l].equalsIgnoreCase("Okt")) { sumary[l] = "10"; } if (sumary[l].equalsIgnoreCase("`16")) { sumary[l] = "2016"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); // System.out.println("LC = "+lc); // sp = new SimpleDateFormat("EEEE, MMMM d, yyyy"); // String b = sumary[3] + "/" + sumary[2] + "/" + sumary[5]; // date = new Date(b); //System.out.println(sp.format(date)); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { if (cell.getStringCellValue().equalsIgnoreCase("Shift 1") || cell.getStringCellValue().equalsIgnoreCase("Shift 2")) { CellReference cr = new CellReference(cell.getRowIndex() + 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); String delimsJam = "[-]"; String[] arrJam = c.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } // indoFormatter = DateTimeFormatter // .ofLocalizedTime(FormatStyle.SHORT) // .withLocale(Locale.getDefault()); //System.out.println("I3 = " + i); lt = LocalTime.parse(arrJam[0]); //System.out.println(lt+"-"+lt.plusHours(2)); } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } // indoFormatter = DateTimeFormatter // .ofLocalizedTime(FormatStyle.SHORT) // .withLocale(Locale.getDefault()); //System.out.println("I3 = " + i); lt = LocalTime.parse(arrJam[0]); //System.out.println(lt+"-"+lt.plusHours(2)); } } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == colMatkulIdx) { subject = cell.getStringCellValue(); //System.out.println("Subject = "+subject); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colMatkulIdx + 1 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // location.add(String.valueOf((int)cell.getNumericCellValue())); // locationIdx.add(cell.getColumnIndex()); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); //System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } //System.out.print(cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); //System.out.println(splt2[l] + "= lab"); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } //System.out.print(c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } // scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j))); } // System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(i)+",location = "+location.get(i)); // scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j))); } for (int j = 0; j < dosen.size(); j++) { //System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(j)+",location = "+location.get(j)); scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return mergeringList(scheduleList); }
From source file:icalendarconverter.ReadExcel.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet1.iterator(); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in A3 CellReference cellReference = new CellReference("A3"); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); CellValue cellValue = evaluator.evaluate(cell); switch (evaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print((int) cell.getNumericCellValue() + " \t\t "); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + " \t\t "); break; }/*from w ww. j a v a 2s.c o m*/ } System.out.println(); } fis.close(); }
From source file:info.informationsea.tableio.excel.ExcelSheetReader.java
License:Open Source License
@Override protected Object[] readNextRow() { if (sheet.getLastRowNum() < currentRow) return null; Row row = sheet.getRow(currentRow);//from www . java 2 s . c o m Object[] rowObjects = new Object[row.getLastCellNum()]; for (Cell cell : row) { Object value; switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: value = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: default: value = cell.getStringCellValue(); break; } rowObjects[cell.getColumnIndex()] = value; } currentRow += 1; return rowObjects; }
From source file:info.toegepaste.www.service.ExcelServiceImpl.java
@Override @TransactionAttribute(REQUIRES_NEW)/*w w w .j av a2 s . co m*/ public String upload(Part file) { try { //declaratie variabelen //Variabelen voor tijdelijke gegevens int cellInt; String cellString; int cellNr; String infoCel = " "; int scoresTeller = 1; String fout = " "; //debug of overzetvariabelen String klasDebug = " "; String vakDebug = " "; String testDebug = " "; int totaalDebug = 0; List<Integer> studentennrDebug = new ArrayList<Integer>(); List<String> naamDebug = new ArrayList<String>(); List<Integer> scoreDebug = new ArrayList<Integer>(); Iterator<Row> rowIterator = null; if (file.getSubmittedFileName().contains("xlsx")) { //lees de content stream in naar de hssfworkbook XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream()); //kies de juiste pagina (eerste) XSSFSheet sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); } else if (file.getSubmittedFileName().contains("xls")) { //lees de content stream in naar de hssfworkbook HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream()); //kies de juiste pagina (eerste) HSSFSheet sheet = workbook.getSheetAt(0); rowIterator = sheet.iterator(); } else { fout = "Het geuploadde bestand heeft niet de juiste indeling"; } if (rowIterator != null) { while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Leest elke horizontale lijn van links naar rechts uit in de console Iterator<Cell> cellIterator = row.cellIterator(); cellNr = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cellNr++; //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); cellInt = (int) cell.getNumericCellValue(); if (infoCel.equals("totaal")) { totaalDebug = cellInt; } else { if (scoresTeller == 1) { studentennrDebug.add(cellInt); scoresTeller++; } else { scoreDebug.add(cellInt); scoresTeller = 1; } } break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue()); cellString = cell.getStringCellValue(); //Als er een titel in de cel zit en steek in infoCel if (cellString.toLowerCase().equals("vak") || cellString.toLowerCase().equals("klas") || cellString.toLowerCase().equals("test") || cellString.toLowerCase().equals("totaal") || cellString.toLowerCase().equals("score")) { infoCel = cellString.toLowerCase(); } else { //inhoud in de cell switch (infoCel) { case "klas": klasDebug = cellString; break; case "vak": vakDebug = cellString; break; case "test": testDebug = cellString; break; case "score": naamDebug.add(cellString); scoresTeller++; break; default: break; } } break; } } } } System.out.println(""); int testId = 0; int vakId = 0; int studentId = 0; try { //als er al een test is aangemaakt met die naam, voeg gewoon punten toe testId = getTestId(testDebug); } catch (Exception e) { try { //als er al een vak is aangemaakt met die naam, voeg gewoon een vak toe vakId = getVakId(vakDebug); } catch (Exception e1) { insertVak(vakDebug); vakId = getVakId(vakDebug); } //maak een nieuwe test aan insertTest(vakId, testDebug, totaalDebug); testId = getTestId(testDebug); } //Scores toevoegen int index = 0; for (int studentenNr : studentennrDebug) { studentId = getStudentId(studentenNr); insertScore(studentId, testId, scoreDebug.get(index), totaalDebug); index++; } return fout; } catch (IOException e) { // Error handling return "Er is iets misgelopen bij het inlezen van het bestand."; } }
From source file:invoiceapplication.CopyRowOriginal.java
public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) { // Get the source / new row Row newRow = worksheet.getRow(destRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); // If the row exists in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true); } else {//from ww w . j a v a 2 s . c o m newRow = worksheet.createRow(destRowNum); } copyAnyMergedRegions(worksheet, sourceRow, newRow); // Loops through source column 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) { newCell = null; continue; } // Use old cell style newCell.setCellStyle(oldCell.getCellStyle()); // If there is a cell comment, copy if (newCell.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: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellValue(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; } } }
From source file:IO.FILES.java
Persona toPersona(Row r) throws IOException { if (r == null) return null; Persona p = new Persona(); Iterator cells = r.cellIterator(); int i = 0;/*from www. jav a 2s . co m*/ String str = ""; while (cells.hasNext()) { Cell aux = (Cell) cells.next(); if ((i != 4) && (i != 5) && (i < 14)) str = aux.getStringCellValue(); switch (i) { case 0: p.setId(str); break; case 1: p.setName(str); break; case 2: p.setLastName(str); break; case 3: p.setLastName2(str); break; case 4: p.setNivel((int) aux.getNumericCellValue()); break; case 5: p.setEdad((int) aux.getNumericCellValue()); break; case 6: p.setMale(str.equalsIgnoreCase("M")); break; case 7: p.getCentro().setName(str); break; case 8: p.getCentro().setSiglas(str); break; case 9: p.getCentro().setPublic(str.equalsIgnoreCase("PUBLICO")); break; case 10: p.setCCA(str.equalsIgnoreCase("CCA"), " "); break; case 11: p.setCCA_DETALLE(str); case 12: p.setAdecuacion(str.equalsIgnoreCase("SI"), -1); break; case 13: p.setTipoDeAdecuacion(str); break; case 14: p.getRedaccion().setUT((int) aux.getNumericCellValue()); break; case 15: p.getRedaccion().setCL((int) aux.getNumericCellValue()); break; case 16: p.getRedaccion().setPAL((int) aux.getNumericCellValue()); break; case 17: p.getRedaccion().setLPUT((float) aux.getNumericCellValue()); break; case 18: p.getRedaccion().setLPCL((float) aux.getNumericCellValue()); break; case 19: p.getRedaccion().setINSUB((float) aux.getNumericCellValue()); } i++; } p.getRedaccion().setR(REDACCIONES.read(p.getId())); return p; }
From source file:io.github.jonestimd.finance.file.excel.SheetParser.java
License:Open Source License
private Map<String, String> getRow(Sheet sheet, int index, Map<Integer, String> columnNames, DataFormatter formatter) {/*from w w w . j a va2 s . c om*/ Map<String, String> values = new HashMap<>(); for (Cell cell : sheet.getRow(index)) { String key = columnNames.get(cell.getColumnIndex()); if (key != null) { if (cell.getCellTypeEnum() == CellType.NUMERIC && !DateUtil.isCellDateFormatted(cell)) values.put(key, String.valueOf(cell.getNumericCellValue())); else values.put(key, formatter.formatCellValue(cell)); } } return values; }
From source file:io.konig.spreadsheet.CellValue.java
License:Apache License
public static CellValue getValue(Cell cell) { if (cell == null) { return null; }/* w w w.jav a 2 s . c o m*/ switch (cell.getCellTypeEnum()) { case BOOLEAN: Boolean booleanValue = cell.getBooleanCellValue(); return new CellValue(booleanValue, null, booleanValue.toString()); case NUMERIC: Double doubleValue = cell.getNumericCellValue(); return new CellValue(null, doubleValue, doubleValue.toString()); case STRING: String stringValue = cell.getStringCellValue(); return new CellValue(null, null, stringValue); default: return null; } }