List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue
double getNumericCellValue();
From source file:com.mycompany.mavenproject1.MainExecutor.java
static Map<String, Object[]> getNewWorkbookData(HSSFWorkbook inputExcel, Iterator<String> playersIterator) { Map<String, Object[]> data = new HashMap<String, Object[]>(); data.put("1", new Object[] { "Player", "FSP.1", "FSW.1", "SSP.1", "SSW.1", "ACE.1", "DBF.1", "WNR.1", "UFE.1", "BPC.1", "BPW.1", "NPA.1", "NPW.1" }); int key = 2;/*from ww w.j a va 2 s. c o m*/ while (playersIterator.hasNext()) { String player = playersIterator.next(); for (int i = 0; i < inputExcel.getNumberOfSheets(); i++) { Iterator<Row> rowIterator = inputExcel.getSheetAt(i).iterator(); rowIterator.next(); int row_counter = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Object[] rowValues = new Object[13]; String player_1_cell = row.getCell(0).getStringCellValue(); String player_2_cell = row.getCell(1).getStringCellValue(); int dataRangeLow = 0; int dataRangeHigh = 0; if (player.equals(player_1_cell)) { dataRangeLow = 6; dataRangeHigh = 17; rowValues[0] = player; } else if (player.equals(player_2_cell)) { dataRangeLow = 24; dataRangeHigh = 35; rowValues[0] = player; } else { // Go to another row. } int cell_counter = 0; for (int j = dataRangeLow; j < dataRangeHigh; j++) { Cell cell = row.getCell(j); System.out.println(cell_counter); if (cell != null) rowValues[j - dataRangeLow + 1] = cell.getNumericCellValue(); if (cell_counter == 5) { Object debug = new Object(); } cell_counter++; data.put("" + key, rowValues); key++; } System.out.println(row_counter); row_counter++; } } } return data; }
From source file:com.mycompany.peram_inclassexam.ReadExcelFile.java
public List getAccountListFromExcel() throws FileNotFoundException { List accountList = new ArrayList(); FileInputStream fis = null;/* ww w.jav a2 s . co m*/ try { fis = new FileInputStream(FILE_PATH); Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) { AccountDetails account = new AccountDetails(); Row row = (Row) sheet.getRow(j); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); if (Cell.CELL_TYPE_STRING == cell.getCellType()) { if (cell.getColumnIndex() == 1) { account.setLastName(cell.getStringCellValue()); } if (cell.getColumnIndex() == 2) { account.setAccountNo(cell.getStringCellValue()); } if (cell.getColumnIndex() == 0) { account.setFirstName(cell.getStringCellValue()); } } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { if (cell.getColumnIndex() == 3) { account.setAccountBalance((int) cell.getNumericCellValue()); } } } accountList.add(account); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return accountList; }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>// w ww. ja va2 s . co m * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ) * </pre> * * @method Name : readExcel * @param strFullFilePath, serviceType * @return List<Map<String, Object>> * @throws Exception * */ @SuppressWarnings("deprecation") public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Map<String, Object> map = null; try { file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */ /* Sheet ? , ? ?? */ /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */ for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1 ? */ Sheet sheet = wb.getSheetAt(sheetIdx); /* ?? ? ?, */ /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new HashMap<String, Object>(); if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; // WBS ? ? ?? if (serviceType.equals("order")) { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); value = formatter.format(cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); } break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: ; } } else { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } } map.put("colName" + cellIdx, value); } else { map.put("colName" + cellIdx, ""); } } list.add(map); } } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>// w w w . j ava 2 s .c o m * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ), sheet ? * </pre> * * @method Name : readExcelMulti * @param strFullFilePath, serviceType, sheetNo * @return * @throws * */ @SuppressWarnings("deprecation") public static List<LinkedHashMap<String, Object>> readExcelMulti(String strFullFilePath, String serviceType, int sheetNo) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>(); LinkedHashMap<String, Object> map = null; try { if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= file path : {} =", strFullFilePath); log.debug("= tmp file : {} =", tmpFile); log.debug( "=========================================================================================="); } file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */ Sheet sheet = wb.getSheetAt(sheetNo); /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new LinkedHashMap<String, Object>(); if (rowIdx == 0) { if (log.isDebugEnabled()) { log.debug( "=================================================================================="); log.debug("= sheet no : {} =", sheetNo); log.debug("= row count : {} =", sheet.getLastRowNum()); log.debug("= col count : {} =", cellCount); log.debug( "=================================================================================="); } } if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } map.put("item" + String.valueOf(cellIdx), value); } else { map.put("item" + String.valueOf(cellIdx), ""); } } if (log.isDebugEnabled()) { log.debug( "=================================================================================="); log.debug("= map : {} =", map); log.debug( "=================================================================================="); } list.add(map); } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; }
From source file:com.ncc.excel.ExcelUtil.java
License:Apache License
public static String getCellValue(Cell cell) { // private String getCellValue(Cell cell){ String str = null;/*from w w w .j a va 2 s. com*/ if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //0 //?? 1. 2. if (HSSFDateUtil.isCellDateFormatted(cell)) { //?? str = DateUtil.formatDateForExcelDate(cell.getDateCellValue()); } else { double dValue = cell.getNumericCellValue(); //E if (String.valueOf(dValue).contains("E")) { str = new DecimalFormat("#").format(dValue); } else { str = String.valueOf(dValue); } } break; case Cell.CELL_TYPE_STRING: //1 str = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: //2 ? str = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: //3 str = ""; break; case Cell.CELL_TYPE_BOOLEAN: //4 str = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: //5 str = ""; break; default: str = null; break; } } return str; }
From source file:com.ncc.excel.ExcelUtil2.java
License:Apache License
public static String getCellValue(Cell cell) { // private String getCellValue(Cell cell){ String str = null;/*ww w.j a va2 s. c om*/ if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //0 //?? 1. 2. if (HSSFDateUtil.isCellDateFormatted(cell)) { //?? str = DateUtil.formatDateForExcelDate(cell.getDateCellValue()); } else { double dValue = cell.getNumericCellValue(); //E if (String.valueOf(dValue).contains("E")) { str = new DecimalFormat("#").format(dValue); } else { str = String.valueOf(dValue); } } break; case Cell.CELL_TYPE_STRING: //1 str = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: //2 ? str = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: //3 str = ""; break; case Cell.CELL_TYPE_BOOLEAN: //4 str = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: //5 str = ""; break; default: str = null; break; } } return str; }
From source file:com.ncc.excel.test.ExcelUtil.java
License:Apache License
/*** * ?? // w ww . ja v a 2 s . c om * * @Title: getCellValue * @Date : 2014-9-11 ?10:52:07 * @param cell * @return */ private String getCellValue(Cell cell) { Object result = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: result = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: result = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_BLANK: break; default: break; } } return result.toString(); }
From source file:com.nfa.drs.data.StudentWindTunnelFormatXls.java
private List<String> readXlsLines(Path file) { List<String> lines = new ArrayList<>(); try {/*from w w w. j a v a 2 s . c om*/ FileInputStream stream = new FileInputStream(file.toFile()); HSSFWorkbook book = new HSSFWorkbook(stream); HSSFSheet sheet = book.getSheetAt(0); for (Row row : sheet) { int rowIndex = row.getRowNum(); while (rowIndex > lines.size() - 1) { lines.add(""); } StringBuilder line = new StringBuilder(); for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { line.append(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { line.append(cell.getNumericCellValue()); } line.append(","); } lines.add(line.toString()); } } catch (IOException ex) { } return lines; }
From source file:com.nikoo28.excel.parser.ExcelParser.java
License:Apache License
public String parseExcelData(InputStream is) { try {/* www. j ava2 s . c om*/ HSSFWorkbook workbook = new HSSFWorkbook(is); // Taking first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); currentString = new StringBuilder(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: bytesRead++; currentString.append(cell.getBooleanCellValue() + "\t"); break; case Cell.CELL_TYPE_NUMERIC: bytesRead++; currentString.append(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: bytesRead++; currentString.append(cell.getStringCellValue() + "\t"); break; } } currentString.append("\n"); } is.close(); } catch (IOException e) { LOG.error("IO Exception : File not found " + e); } return currentString.toString(); }
From source file:com.ocs.dynamo.importer.impl.BaseXlsImporter.java
License:Apache License
/** * Retrieves the numeric value of a cell * /*from ww w .j a v a 2 s . co m*/ * @param cell * @return */ protected Double getNumericValue(Cell cell) { if (cell != null && (Cell.CELL_TYPE_NUMERIC == cell.getCellType() || Cell.CELL_TYPE_BLANK == cell.getCellType())) { try { return cell.getNumericCellValue(); } catch (NullPointerException nex) { // cannot return null from getNumericCellValue - so if the cell // is empty we // have to handle it in this ugly way return null; } catch (Exception ex) { throw new OCSImportException("Found an invalid numeric value: " + cell.getStringCellValue(), ex); } } else if (cell != null && Cell.CELL_TYPE_STRING == cell.getCellType()) { // in case the value is not numeric, simply output a warning. If the // field is required, this will trigger // an error at a later stage if (!StringUtils.isEmpty(cell.getStringCellValue().trim())) { throw new OCSImportException("Found an invalid numeric value: " + cell.getStringCellValue()); } } return null; }