Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getSheetAt.

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.mycompany.peram_inclassexam.ReadExcelFile.java

public List getAccountListFromExcel() throws FileNotFoundException {
    List accountList = new ArrayList();
    FileInputStream fis = null;//from   w w  w  .j  a  v a 2 s .  com
    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  ww  .j av  a2 s  .c  o 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  a  v  a  2  s. com*/
 * 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 List<Row> readExcel(Workbook wb) {
    Sheet sheet = null;/*from  www .  java 2s  .  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 // ww w . j a va  2 s . co 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.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * Excel? //from  w  w  w. j av a  2  s.c  o  m
 *  
 * @Title: WriteExcel 
 * @Date : 2014-9-11 ?01:33:59 
 * @param wb 
 * @param rowList 
 * @param xlsPath 
 */
private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) {

    if (wb == null) {
        out("???");
        return;
    }

    Sheet sheet = wb.getSheetAt(0);// sheet  

    // ???????  
    int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1;
    int t = 0;//  
    out("???" + rowList.size());
    for (Row row : rowList) {
        if (row == null)
            continue;
        // ???  
        int pos = findInExcel(sheet, row);

        Row r = null;// ??????  
        if (pos >= 0) {
            sheet.removeRow(sheet.getRow(pos));
            r = sheet.createRow(pos);
        } else {
            r = sheet.createRow(lastRowNum + t++);
        }

        //??  
        CellStyle newstyle = wb.createCellStyle();

        //?  
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = r.createCell(i);// ??  
            cell.setCellValue(getCellValue(row.getCell(i)));// ???  
            // cell.setCellStyle(row.getCell(i).getCellStyle());//  
            if (row.getCell(i) == null)
                continue;
            copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // ????  
            cell.setCellStyle(newstyle);// ?  
            // sheet.autoSizeColumn(i);//  
        }
    }
    out("???:" + (rowList.size() - t) + " ?" + t);

    // ??  
    setMergedRegion(sheet);

    try {
        // ??Excel  
        FileOutputStream outputStream = new FileOutputStream(xlsPath);
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (Exception e) {
        out("Excel?? ");
        e.printStackTrace();
    }
}

From source file:com.netxforge.netxstudio.models.importer.MasterDataImporter_xssf.java

License:Open Source License

public void process(InputStream is) {
    Workbook workBook;
    try {//from   ww w .  j  av  a  2s.  c om
        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 {/*from ww  w . j  av  a2s.  c o m*/
        Sheet sheet = wb.getSheetAt(0);
        return sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
    }
}

From source file:com.ocs.dynamo.importer.impl.BaseXlsImporterTest.java

License:Apache License

/**
 * Test that creating a simple (non-streaming) workbook works
 * //ww w. j av a 2  s .c o m
 * @throws IOException
 */
@Test
public void testCreateWorkbook() throws IOException {
    byte[] bytes = readFile("importertest.xlsx");
    Workbook wb = importer.createWorkbook(bytes);

    Sheet sheet = wb.getSheetAt(0);

    PersonDTO dto = importer.processRow(0, sheet.getRow(0), PersonDTO.class);
    Assert.assertNotNull(dto);
    Assert.assertEquals(0, dto.getRowNum());
    Assert.assertEquals("Bas", dto.getName());
    Assert.assertEquals(1, dto.getNumber().intValue());
    Assert.assertEquals(2.4, dto.getFactor().doubleValue(), 0.001);
    Assert.assertEquals("abc", dto.getRandom());
    Assert.assertEquals(Gender.M, dto.getGender());
    Assert.assertEquals(1.50, dto.getPercentage().doubleValue(), 0.001);
    Assert.assertTrue(dto.getAbool());

    // check that default values are set
    dto = importer.processRow(1, sheet.getRow(1), PersonDTO.class);
    Assert.assertNotNull(dto);
    Assert.assertEquals(1, dto.getRowNum());
    Assert.assertEquals("Unknown", dto.getName());
    Assert.assertEquals(2, dto.getNumber().intValue());
    Assert.assertEquals(1.0, dto.getFactor().doubleValue(), 0.001);
    Assert.assertFalse(dto.getAbool());

    // check negative values
    dto = importer.processRow(2, sheet.getRow(2), PersonDTO.class);
    Assert.assertNotNull(dto);
    Assert.assertEquals(2, dto.getRowNum());
    Assert.assertEquals("Endy", dto.getName());
    Assert.assertEquals(-3, dto.getNumber().intValue());

}

From source file:com.ocs.dynamo.importer.impl.BaseXlsImporterTest.java

License:Apache License

/**
 * @throws IOException//from  www  . ja  v a 2 s .  c  o m
 */
@Test
public void testCreateWorkbook_WrongNumericValue() throws IOException {
    byte[] bytes = readFile("importertest_wrongnumeric.xlsx");
    Workbook wb = importer.createWorkbook(bytes);

    Sheet sheet = wb.getSheetAt(0);

    try {
        importer.processRow(0, sheet.getRow(0), PersonDTO.class);
    } catch (OCSImportException ex) {
        Assert.assertEquals("Found an invalid numeric value: xyz", ex.getMessage());
    }

}