List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue
double getNumericCellValue();
From source file:at.mukprojects.exclycore.dao.XLSXReader.java
License:Open Source License
private ExclyLong readLong(Cell cell, int type) throws Exception { ExclyLong output = null;// w w w . j a v a 2s . c o m if (type == Cell.CELL_TYPE_STRING) { String data = cell.getStringCellValue(); if (isNumericGerman(data)) { Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data); output = new ExclyLong(number.intValue()); } else if (isNumericUK(data)) { Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data); output = new ExclyLong(number.intValue()); } else if (data.equals("") || data.equals(" ") || data.equals("-")) { output = new ExclyLongBlank(); } else { output = new ExclyLongError(); log.warn("The reader has expected a numeric value, but found a string value. [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); } } else if (type == Cell.CELL_TYPE_BLANK) { output = new ExclyLongBlank(); } else if (type == Cell.CELL_TYPE_FORMULA) { int formulaType = cell.getCachedFormulaResultType(); output = readLong(cell, formulaType); } else if (type == Cell.CELL_TYPE_BOOLEAN) { Boolean data = cell.getBooleanCellValue(); if (data) { output = new ExclyLong(1); } else { output = new ExclyLong(0); } } else if (type == Cell.CELL_TYPE_NUMERIC) { double data = cell.getNumericCellValue(); output = new ExclyLong(data); } else if (type == Cell.CELL_TYPE_ERROR) { output = new ExclyLongError(); } else { log.warn("The reader was unable to find a valid parser for the cell [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); output = new ExclyLongError(); } return output; }
From source file:at.mukprojects.exclycore.dao.XLSXReader.java
License:Open Source License
private ExclyDate readDate(Cell cell, int type) throws Exception { ExclyDate output = null;/*from w w w. j a va 2 s .co m*/ if (type == Cell.CELL_TYPE_STRING) { String data = cell.getStringCellValue(); if (isNumericGerman(data)) { Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data); output = new ExclyDate(DateUtil.getJavaDate(number.intValue())); } else if (isNumericUK(data)) { Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data); output = new ExclyDate(DateUtil.getJavaDate(number.intValue())); } else if (data.equals("") || data.equals(" ") || data.trim().equals("-")) { output = new ExclyDateBlank(); } else { ExclyDate parsedDate = parse(cell.getStringCellValue()); output = parsedDate; } } else if (type == Cell.CELL_TYPE_BLANK) { output = new ExclyDateBlank(); } else if (type == Cell.CELL_TYPE_FORMULA) { int formulaType = cell.getCachedFormulaResultType(); output = readDate(cell, formulaType); } else if (DateUtil.isCellDateFormatted(cell)) { Date data = cell.getDateCellValue(); output = new ExclyDate(data); } else if (type == Cell.CELL_TYPE_NUMERIC) { double data = cell.getNumericCellValue(); output = new ExclyDate(DateUtil.getJavaDate(data)); } else if (type == Cell.CELL_TYPE_ERROR) { output = new ExclyDateError(); } else { log.warn("The reader was unable to find a valid parser for the cell [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); output = new ExclyDateError(); } return output; }
From source file:attandance.standalone.utils.ExcelUtils.java
public static List<AttandanceRecord> readDataFromExcel(String fileName) { List<AttandanceRecord> result = new ArrayList<>(); try {//from w w w. ja v a 2 s. c om FileInputStream file = new FileInputStream(new File(fileName)); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int lineIndex = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int columnIndex = 1; AttandanceRecord lineBean = new AttandanceRecord(); //skip header if (lineIndex == 1) { lineIndex += 1; continue; } while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: cellValue = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; default: cellValue = cell.getStringCellValue(); } lineBean.setAttribute(columnIndex, cellValue); columnIndex = columnIndex + 1; } result.add(lineBean); } file.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return result; }
From source file:au.com.onegeek.lambda.parser.XslxUtil.java
License:Apache License
public static Object getNumericCellValue(final Cell cell) { Object cellValue;// w w w.j av a 2 s . c o m if (DateUtil.isCellDateFormatted(cell)) { cellValue = new Date(cell.getDateCellValue().getTime()); } else { cellValue = cell.getNumericCellValue(); } return cellValue; }
From source file:automatedhgl.AutomatedHGL.java
public static void main(String[] args) { try {/*from ww w . ja va2s. c o m*/ FileInputStream excelFile = new FileInputStream(new File(INFILE_NAME)); //create workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(excelFile); //get first desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //create workbook instance to output excel file XSSFWorkbook workbookHGL = new XSSFWorkbook(); //create sheet in output excel file XSSFSheet sheetHGL = workbookHGL.createSheet("HGL"); //iterate through each row one by one Iterator<Row> rowiterator = sheet.iterator(); while (rowiterator.hasNext()) { Row row = rowiterator.next(); //for each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (row.getRowNum() > 7 && count < 23) //to filter column headings { //check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: count++; //assign get value to correct variable if (count == 1) { InletStr = cell.getNumericCellValue(); } else if (count == 2) { OutWSE = cell.getNumericCellValue(); } System.out.print(cell.getNumericCellValue() + " (" + count + ") "); break; case Cell.CELL_TYPE_STRING: count++; /*//assign get value to correct variable if( count == 1 ){InletStr = cell.getStringCellValue();}*/ System.out.print(cell.getStringCellValue() + " (" + count + ") "); break; case Cell.CELL_TYPE_FORMULA: count++; /*//assign get value to correct variable if( count == 1 ){InletStr = cell.getCachedFormulaResultType();}*/ System.out.print(cell.getCachedFormulaResultType() + " (" + count + ") "); break; } } else { count = 0; //reset the count at the end of the row } } System.out.println("return"); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //Output Excel file XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Datatypes in Java"); Object[][] datatypes = { { "Datatype", "Type", "Size(in bytes)" }, { "int", "Primitive", 2 }, { "float", "Primitive", 4 }, { "double", "Primitive", 8 }, { "char", "Primitive", 1 }, { "String", "Non-Primitive", "No fixed size" } }; int rowNum = 0; System.out.println("Creating excel"); for (Object[] datatype : datatypes) { Row row = sheet.createRow(rowNum++); int colNum = 0; for (Object field : datatype) { Cell cell = row.createCell(colNum++); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } try { FileOutputStream outputStream = new FileOutputStream(FILE_NAME); workbook.write(outputStream); workbook.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } System.out.print(InletStr + " "); System.out.print(OutWSE + " "); System.out.println("HGL Done"); }
From source file:b01.officeLink.excel.ExcelRefiller.java
License:Apache License
public void fillGroupContent(String groupStr, FocObject object) { ExcelGroupDefinition grpDef = getGroupDefinition(groupStr); Sheet srcSheet = getSourceSheet();//from w w w . j ava2 s . c om Sheet tarSheet = getTargetSheet(); if (grpDef != null) { for (int i = 0; i < grpDef.getRowCount(); i++) { int rowIdx = grpDef.getRowAt(i); Row sRow = srcSheet.getRow(rowIdx); if (sRow != null) { Row tRow = tarSheet.getRow(currentRow); if (tRow == null) { tRow = tarSheet.createRow(currentRow); } if (tRow != null) { tRow.setHeight(sRow.getHeight()); for (int c = 0; c < 20; c++) { Cell sCell = sRow.getCell(c + 1); if (sCell != null) { Cell tCell = tRow.getCell(c); if (tCell == null) { tCell = tRow.createCell(c); } if (tCell != null) { tCell.setCellStyle(sCell.getCellStyle()); String str = ""; if (sCell.getCellType() == Cell.CELL_TYPE_STRING) { RichTextString rts = sCell.getRichStringCellValue(); str = rts.getString(); str = analyseContent(str, object); } else if (sCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { str = String.valueOf(sCell.getNumericCellValue()); } if (str != null && !str.isEmpty()) { int iVal = convertString2Integer(str); double dVal = convertString2Double(str); if (iVal != Integer.MAX_VALUE) { tCell.setCellValue(iVal); } else if (!Double.isNaN(dVal)) { tCell.setCellValue(dVal); } else { if (getFocExcelDocument() != null && getFocExcelDocument().getWorkbook() != null) { tCell.setCellValue(getFocExcelDocument().getWorkbook() .getCreationHelper().createRichTextString(str)); } } } } } } } currentRow++; } } } }
From source file:b01.officeLink.excel.FocExcelSheet.java
License:Apache License
public double getCellNum(int coord0, int coord1) { Row row = sheet.getRow(coord0);/*from ww w. j a v a2 s . co m*/ Cell cell = row != null ? row.getCell(coord1) : null; double dVal = 0; if (cell != null && (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA)) { dVal = cell.getNumericCellValue(); } return dVal; }
From source file:b01.officeLink.excel.FocExcelSheet.java
License:Apache License
public String getCellString(int coord0, int coord1) { String str = null;// w w w .j a va2s .c o m try { Row row = sheet.getRow(coord0); Cell cell = row != null ? row.getCell(coord1) : null; if (cell != null) { int type = cell.getCellType(); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try { double dVal = cell.getNumericCellValue(); if (Double.isNaN(dVal)) { type = Cell.CELL_TYPE_STRING; } else { type = Cell.CELL_TYPE_NUMERIC; } } catch (Exception e) { Globals.logString("This EXCEPTION is Handles"); Globals.logExceptionWithoutPopup(e); } } if (type == Cell.CELL_TYPE_STRING) { str = cell.getRichStringCellValue().getString(); } else if (type == Cell.CELL_TYPE_NUMERIC) { // str = String.valueOf((int) cell.getNumericCellValue()); str = cell.getNumericCellValue() + ""; } } } catch (Exception e) { Globals.logException(e); Globals.logString("Could Not get Value for cell [" + coord0 + "," + coord1 + "]"); } return str; }
From source file:bad.robot.excel.row.CopyRow.java
License:Apache License
private static void setCellDataValue(Cell oldCell, Cell newCell) { switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break;//from w w w . ja v a 2 s .co m 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; } }
From source file:bandaru_excelreadwrite.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;/*w w w . j av a 2s.c o m*/ try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { Song song = new Song(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return songList; }