List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets
int getNumberOfSheets();
From source file:com.mycompany.bandaru_exam.ReadfromExcel.java
public List getAccountListFromExcel() { List accountList = new ArrayList(); FileInputStream fis = null;//from ww w .ja v a 2s. co 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()) { Account account = new Account(); 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 0 contains first name */ if (cell.getColumnIndex() == 0) { account.setFirstName(cell.getStringCellValue()); } /* Cell with index 1 contains last name */ if (cell.getColumnIndex() == 1) { account.setLastName(cell.getStringCellValue()); } /* Cell with index 2 contains account number */ if (cell.getColumnIndex() == 2) { account.setAccNumber(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 3 contains account number */ if (cell.getColumnIndex() == 3) { account.setBalance(cell.getNumericCellValue()); } } } /* end iterating a row, add all the elements of a row in list */ accountList.add(account); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //Remove Headers accountList.remove(0); return accountList; }
From source file:com.mycompany.gannaraputakehomeexam.ReadingFromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//from ww w. j a v a 2 s .co 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()) { SongsList song = new SongsList(); 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; }
From source file:com.mycompany.gayamtakehomeexam.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//w w w. jav a2s . c om 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) { } catch (IOException e) { } return songList; }
From source file:com.mycompany.peram_inclassexam.ReadExcelFile.java
public List getAccountListFromExcel() throws FileNotFoundException { List accountList = new ArrayList(); FileInputStream fis = null;// ww w . ja v a 2 s .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>// w w w .j av a 2s . c om * 1. : POI Util * 2. : Excel ? ? (? ?), ? ?? ? * </pre> * * @method Name : readWriteExcel * @param response, codeList, serviceType, templateFile * @return none * @throws Exception * */ public static void readWriteExcel(HttpServletResponse response, List<Map<String, Object>> codeList, String serviceType, String templateFile) throws Exception { OutputStream fileOut = null; /* ? ? */ File wbfile = new File(templateFile); Workbook wb = null; FileInputStream file = null; try { file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */ for (int i = 0; i < wb.getNumberOfSheets(); i++) { wb.removeSheetAt(wb.getNumberOfSheets() - 1); } /* Sheet ?, ? , ? ? ? */ /* ?*/ /* ? : wb = createWorkBook(wb, "sheet Name", "dataList", "header column List");*/ } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readWriteExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } String fileName = serviceType + ".xlsm"; /* ? */ response.setHeader("Content-Disposition", "attachment; filename=" + fileName); /* , to file */ fileOut = response.getOutputStream(); wb.write(fileOut); fileOut.close(); fileOut.flush(); }
From source file:com.ncc.excel.ExcelUtil.java
License:Apache License
public List<Row> readExcel(Workbook wb) { Sheet sheet = null;// ww w . jav a 2 s. com if (onlyReadOneSheet) {//??sheet System.out.println("selectedSheetName:" + selectedSheetName); // ??sheet(?????) sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName); System.out.println(sheet.getSheetName()); } else { for (int i = 0; i < wb.getNumberOfSheets(); i++) {//??Sheet sheet = wb.getSheetAt(i); logger.info(sheet.getSheetName()); for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//?? Row row = sheet.getRow(j); for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//??? System.out.print(row.getCell(k) + "\t"); } System.out.println("---Sheet" + i + "?---"); } } } return null; }
From source file:com.ncc.excel.test.ExcelUtil.java
License:Apache License
/** * ?Excel // w w w. ja va2 s .c o m * * @Title: readExcel * @Date : 2014-9-11 ?11:26:53 * @param wb * @return */ private List<Row> readExcel(Workbook wb) { List<Row> rowList = new ArrayList<Row>(); int sheetCount = 1;//??sheet? Sheet sheet = null; if (onlyReadOneSheet) { //??sheet // ??sheet(?????) sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName); } else { //?sheet sheetCount = wb.getNumberOfSheets();//???? } // ?sheet for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) { // ??sheet if (!onlyReadOneSheet) { sheet = wb.getSheetAt(t); } //??? int lastRowNum = sheet.getLastRowNum(); if (lastRowNum > 0) { //>0? out("\n????" + sheet.getSheetName() + ""); } Row row = null; // ? for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) { row = sheet.getRow(i); if (row != null) { rowList.add(row); out("" + (i + 1) + "", false); // ??? for (int j = 0; j < row.getLastCellNum(); j++) { String value = getCellValue(row.getCell(j)); if (!value.equals("")) { out(value + " | ", false); } } out(""); } } } return rowList; }
From source file:com.netxforge.netxstudio.models.importer.MasterDataImporter_xssf.java
License:Open Source License
public void process(InputStream is) { Workbook workBook; try {//from w ww . jav a2 s.c o m workBook = new XSSFWorkbook(is); // Multi pass the worksheets, do not add objects after the first // pass. // int passes = 2; for (int passIndex = 1; passIndex <= passes; passIndex++) { // Clear after each pass... unresolvedReferences.clear(); if (ImportActivator.DEBUG) { ImportActivator.TRACE.trace(ImportActivator.TRACE_IMPORT_OPTION, "pass=" + passIndex); } for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) { ProcessWorkSheet pw = new ProcessWorkSheet(passIndex, sheetIndex, workBook.getSheetAt(sheetIndex)); // We only add object on the first pass List<RowResult> sheetResult = pw.getSheetResult(); if (!pw.isMultiRefSheet() && passIndex == 1) { for (final RowResult rowResult : sheetResult) { resource.getContents().add(rowResult.getEObject()); this.addToGlobalIndex(rowResult.getIndex(), rowResult.getEObject()); } } } if (ImportActivator.DEBUG) { printResult(); // printIndex(); } } } catch (final Exception e) { throw new IllegalStateException(e); } }
From source file:com.ocs.dynamo.importer.impl.BaseXlsImporter.java
License:Apache License
@Override public int countRows(byte[] bytes, int row, int column) { Workbook wb = createWorkbook(bytes); if (wb.getNumberOfSheets() == 0) { return 0; } else {/* w w w. j ava 2 s .com*/ Sheet sheet = wb.getSheetAt(0); return sheet.getLastRowNum() - sheet.getFirstRowNum() + 1; } }
From source file:com.opendoorlogistics.core.tables.io.PoiIO.java
License:Open Source License
private static void addSchema(ODLDatastore<? extends ODLTableDefinition> ds, Workbook wb) { ODLTableReadOnly table = SchemaIO.createSchemaTable(ds); Sheet sheet = wb.createSheet(SCHEMA_SHEET_NAME); // write out key-value table Row row = sheet.createRow(0);//w w w . ja va 2 s.c om row.createCell(0).setCellValue(SchemaIO.KEY_COLUMN); row.createCell(1).setCellValue(SchemaIO.VALUE_COLUMN); row = sheet.createRow(1); row.createCell(0).setCellValue(SchemaIO.APP_VERSION_KEY); row.createCell(1).setCellValue(AppConstants.getAppVersion().toString()); // write schema table exportTable(sheet, table, sheet.getLastRowNum() + 2, null, null); // hide the sheet from users wb.setSheetHidden(wb.getNumberOfSheets() - 1, Workbook.SHEET_STATE_VERY_HIDDEN); }