List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:com.mycompany.javaapplicaton3.LerArquivo.java
public static void main(String args[]) { logger.info("Hello World!"); try {//ww w .j a va 2s .co m //File excel = new File ("C:/Users/lprates/Documents/arquivo2013.xlsx"); //FileInputStream fis = new FileInputStream(excel); OPCPackage pkg = OPCPackage.open("C:/Users/lprates/Documents/arquivo2013.xlsx"); XSSFWorkbook myWorkBook = new XSSFWorkbook(pkg); // Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file 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_STRING: System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t"); break; default: System.out.print("Nada"); } } System.out.println(""); } } catch (Exception ex) { logger.error(ex.toString()); } }
From source file:com.mycompany.mavenproject1.MainExecutor.java
/** * * @param inputExcel/*from w w w .j av a 2 s . co m*/ * @return */ static LinkedHashSet<String> getAllPlayerNames(HSSFWorkbook inputExcel) { LinkedHashSet<String> playerlist = new LinkedHashSet<String>(); for (int i = 0; i < inputExcel.getNumberOfSheets(); i++) { Iterator<Row> rowIterator = inputExcel.getSheetAt(i).iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); for (int n = 0; n < 2; n++) { Cell cell = cellIterator.next(); String possibleNewName = cell.getStringCellValue(); playerlist.add(possibleNewName); } } } return playerlist; }
From source file:com.mycompany.peram_inclassexam.ReadExcelFile.java
public List getAccountListFromExcel() throws FileNotFoundException { List accountList = new ArrayList(); FileInputStream fis = null;//from w ww . ja v a2s. c om 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>/*from w w w.j a v a 2s .c om*/ * 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>//www . j a va2 s . co 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 ww . ja v a2 s .c o m*/ 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;//from w ww . j a v a2s . 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.test.ExcelUtil.java
License:Apache License
/*** * ?? //from www. ja v a 2s . com * * @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.netxforge.netxstudio.models.importer.MasterDataImporter.java
License:Open Source License
private boolean isEmptyRow(HSSFRow row) { if (row == null) { return true; }//ww w .java 2 s . c o m final Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { final Cell cell = cellIterator.next(); final String value = cell.getStringCellValue(); if (value != null && value.trim().length() > 0) { return false; } } return true; }
From source file:com.netxforge.netxstudio.models.importer.MasterDataImporter_xssf.java
License:Open Source License
private boolean isEmptyRow(Row row) { if (row == null) { return true; }//from w w w .ja va 2 s .co m final Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { final Cell cell = cellIterator.next(); final String value = cell.getStringCellValue(); if (value != null && value.trim().length() > 0) { return false; } } return true; }