Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:com.googlecode.bdoc.testsupport.excel.TestExcelExampleTable.java

License:Open Source License

public TestExcelExampleTable() throws FileNotFoundException, IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(DATATYPE_EXAMPLES));
    excelExampleTable = new ExcelExampleTable(workbook.getSheetAt(0), 0, 0);
}

From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java

License:Apache License

@Override
public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) {
    final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>();
    try {//w  w w  .  j a  va2s.  com
        final HSSFWorkbook myWorkBook = new HSSFWorkbook(
                EsvApiImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));

        for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
            final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr);

            addMinistryPerYearToMap(name, map, mySheet);
        }

        myWorkBook.close();
    } catch (

    final IOException e) {
        LOGGER.warn("Problem loading", e);
    }

    return map;
}

From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java

License:Apache License

@Override
public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(String name) {
    final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>();
    try {//from www .  j av a2s . c o  m
        final HSSFWorkbook myWorkBook = new HSSFWorkbook(
                EsvApiImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));

        for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
            final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr);

            addDataForYearToMap(name, map, mySheet);
        }
        myWorkBook.close();
    } catch (

    final IOException e) {
        LOGGER.warn("Problem loading", e);
    }

    return map;
}

From source file:com.haulmont.mp2xls.object.LocalizationsBatch.java

License:Apache License

protected void readLocalizationFile(File source, String lcFilePath) throws IOException {
    FileInputStream fis = new FileInputStream(source);
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet sheet = wb.getSheetAt(0);

    if (lcFilePath == null)
        projectDirectory = (String) XlsHelper.getCellValue(sheet.getRow(0).getCell(1));
    else/* w w w  .jav  a 2 s  .  c o  m*/
        projectDirectory = lcFilePath;

    int firstDataRowIndex = 6;
    int headingRow = 5;

    for (int currentRowIndex = firstDataRowIndex; !XlsHelper
            .eof(sheet.getRow(currentRowIndex)); currentRowIndex++) {
        HSSFRow row = sheet.getRow(currentRowIndex);

        String path = (String) XlsHelper.getCellValue(row.getCell(0));
        String param = (String) XlsHelper.getCellValue(row.getCell(1));

        Set<MessagesLocalization> localizations;
        if (messagesLocalizations.keySet().contains(path))
            localizations = messagesLocalizations.get(path);
        else {
            localizations = new HashSet<>();
            messagesLocalizations.put(path, localizations);
        }

        for (int col = 2; XlsHelper.getCellValue(sheet.getRow(headingRow).getCell(col)) != null; col++) {
            String localeId = (String) XlsHelper.getCellValue(sheet.getRow(headingRow).getCell(col));
            Object val = XlsHelper.getCellValue(row.getCell(col));
            String value = val != null ? val.toString() : null;
            localeId = localeId.equals("default") ? null : localeId;
            String relPath = path + (localeId == null ? "" : "_" + localeId) + ".properties";

            MessagesLocalization local = null;
            for (MessagesLocalization l : localizations) {
                if (l.getPath().equals(relPath)) {
                    local = l;
                    break;
                }
            }

            if (local == null) {
                local = new MessagesLocalization(localeId, relPath);
                localizationIds.add(localeId);
                localizations.add(local);
            }

            local.getMessages().put(param, value);
        }
    }
}

From source file:com.hl7.main.ImportDataFromXLStoXML.java

public void displayFromExcel(String xlsPath) {
    InputStream inputStream = null;
    try {//from  www  .java 2  s  . com
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;

    try {
        // Initializing the XML document
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document document = builder.newDocument();
        Element rootElement = document.createElement("categories");
        document.appendChild(rootElement);
        fileSystem = new POIFSFileSystem(inputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        Integer count = workBook.getNumberOfSheets();
        ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();
        for (int i = 0; i < count; i++) {
            HSSFSheet sheet = workBook.getSheetAt(i);
            Iterator<?> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator<?> cells = row.cellIterator();
                ArrayList<String> rowData = new ArrayList<String>();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    String richTextString = cell.getStringCellValue();
                    System.out.println("String: " + richTextString);
                    rowData.add(richTextString);
                } // end while
                data.add(rowData);
            } // end while
        }

        int numOfProduct = data.size();
        for (int i = 0; i < numOfProduct; i++) {
            Element productElement = document.createElement("categoryName");
            rootElement.appendChild(productElement);

            int index = 0;
            for (String s : data.get(i)) {
                String headerString = data.get(0).get(index);
                if (data.get(0).get(index).equals("image link")) {
                    headerString = "image_link";
                }

                if (data.get(0).get(index).equals("product type")) {
                    headerString = "product_type";
                }

                Element headerElement = document.createElement(headerString);
                productElement.appendChild(headerElement);
                headerElement.appendChild(document.createTextNode(s));
                index++;
            }
        }

        TransformerFactory tFactory = TransformerFactory.newInstance();

        Transformer transformer = tFactory.newTransformer();
        // Add indentation to output
        transformer.setOutputProperty(OutputKeys.INDENT, "yes");
        transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");

        DOMSource source = new DOMSource(document);
        StreamResult result = new StreamResult(new File("products.xml"));
        // StreamResult result = new StreamResult(System.out);
        transformer.transform(source, result);

    } catch (IOException e) {
        System.out.println("IOException " + e.getMessage());
    } catch (ParserConfigurationException e) {
        System.out.println("ParserConfigurationException " + e.getMessage());
    } catch (TransformerConfigurationException e) {
        System.out.println("TransformerConfigurationException " + e.getMessage());
    } catch (TransformerException e) {
        System.out.println("TransformerException " + e.getMessage());
    }
}

From source file:com.hp.action.CustomerAction.java

public String addCustomerFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//from w  ww. j  a v  a2  s  .co m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    int total = 0;
    int totalFail = 0;
    String reason = "";
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the customer id null
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0

                ) {
                    continue;
                }

                //Init Customer Object
                Customer customer = new Customer();
                //                    customer.setmStt((int)row.getCell(tmp++).getNumericCellValue());

                //System.out.println(row.getCell(1).getStringCellValue());

                if (row.getCell(1) != null && !row.getCell(1).getStringCellValue().equals("")) {
                    Customer customer2 = customerDAO.loadCustomer(row.getCell(1).getStringCellValue());
                    if (customer2 != null) {
                        reason += "Khch hng <b>" + row.getCell(1).getStringCellValue()
                                + "</b>  tn ti. ";
                    }
                } else
                    reason += "M Khch hng hng " + (i + 1) + " khng c trng. ";

                if (row.getCell(9) != null && !row.getCell(9).getStringCellValue().equals("")) {
                    Staff staff = staffDAO.loadStaff(row.getCell(9).getStringCellValue());
                    if (staff == null)
                        reason += "M Nhn vin <b>" + row.getCell(9).getStringCellValue()
                                + "</b> khng tn ti. ";
                } else
                    reason += "M Nhn vin hng " + (i + 1) + " khng c trng.";

                try {
                    if (row.getCell(1) != null)
                        customer.setMaDoiTuong(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) != null)
                        customer.setDoiTuong(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) != null)
                        customer.setTinhThanh(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        customer.setX(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        customer.setDiaChi(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null)
                        customer.setTuyenBanHangThu(row.getCell(6).getStringCellValue());

                    if (row.getCell(7) != null)
                        customer.setDienThoai(row.getCell(7).getStringCellValue());
                    //System.out.println(row.getCell(7).getStringCellValue());

                    if (row.getCell(8) != null)
                        customer.setFax(row.getCell(8).getStringCellValue());
                    if (row.getCell(9) != null)
                        customer.setMaNhanVien(row.getCell(9).getStringCellValue());

                    if (row.getCell(10) != null)
                        customer.setGhiChu(row.getCell(10).getStringCellValue());

                    if (row.getCell(11) != null) {
                        if (row.getCell(11).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            customer.setCoordinateX(row.getCell(11).getNumericCellValue());
                        else
                            customer.setCoordinateX(Double.parseDouble(row.getCell(12).getStringCellValue()));
                    }

                    if (row.getCell(12) != null) {
                        if (row.getCell(12).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            customer.setCoordinateY(row.getCell(12).getNumericCellValue());
                        else
                            customer.setCoordinateY(Double.parseDouble(row.getCell(12).getStringCellValue()));
                    }

                    //Add to database
                    if (customerDAO.saveOrUpdate(customer)) {
                        System.out.println("Add Object " + i);
                        total++;
                        customersTotal = total;
                    } else {
                        totalFail++;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    totalFail++;
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }

    reasonFail = reason;
    customersTotal = total;
    importFail = totalFail;

    return SUCCESS;
}

From source file:com.hp.action.ProductsAction.java

public String addProductFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*from   www.  j a va2  s.c om*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-product");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the product id null
                if (row.getCell(1) == null ||

                        (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getStringCellValue().compareTo("") == 0)
                        || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getNumericCellValue() <= 0)

                ) {
                    continue;
                }

                //Init Product Object
                Product product = new Product();
                //                  
                try {

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setProductID(
                                    (new BigDecimal(row.getCell(1).getNumericCellValue())).toString());
                        else
                            product.setProductID(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setBarcode(
                                    (new BigDecimal(row.getCell(2).getNumericCellValue())).toString());
                        else
                            product.setBarcode(row.getCell(2).getStringCellValue());
                    }

                    if (row.getCell(3) != null)
                        product.setProductName(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        product.setBrand(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        product.setOrigin(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setPackingSpecifications(row.getCell(6).getNumericCellValue() + "");
                        else
                            product.setPackingSpecifications(row.getCell(6).getStringCellValue());

                    }
                    if (row.getCell(7) != null) {

                        product.setQuantification(row.getCell(7).getStringCellValue());

                    }

                    if (row.getCell(8) != null) {
                        if (row.getCell(8).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setVatTax((float) row.getCell(8).getNumericCellValue());
                        else
                            product.setVatTax(Float.parseFloat(row.getCell(8).getStringCellValue()));

                    }

                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setImportPrices((float) row.getCell(9).getNumericCellValue());
                        else
                            product.setImportPrices(Float.parseFloat(row.getCell(9).getStringCellValue()));

                    }

                    if (row.getCell(10) != null) {
                        if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setExportPrices((float) row.getCell(10).getNumericCellValue());
                        else
                            product.setExportPrices(Float.parseFloat(row.getCell(10).getStringCellValue()));

                    }
                    if (row.getCell(11) != null)
                        product.setProvider(row.getCell(11).getStringCellValue());

                    if (row.getCell(12) != null)
                        product.setDescription(row.getCell(12).getStringCellValue());
                    if (row.getCell(13) != null)
                        product.setProductImage(row.getCell(13).getStringCellValue());

                    //product.setMProvider("nhacungcap1");
                    //Add to database
                    if (productDAO.saveOrUpdate(product)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        productsTotal = total;
                    } else {
                        int t = 0;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }
    productsTotal = total;
    return SUCCESS;
}

From source file:com.hp.action.ProviderAction.java

public String addProviderFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*from   ww  w  .  ja v a2s.  co m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-provider");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the product id null
                if (row.getCell(1) == null ||

                        (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getStringCellValue().compareTo("") == 0)
                        || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getNumericCellValue() <= 0)

                ) {
                    continue;
                }

                //Init Product Object
                Provider provider = new Provider();
                //                  
                try {

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setId((new BigDecimal(row.getCell(1).getNumericCellValue())).toString());
                        else
                            provider.setId(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setName((new BigDecimal(row.getCell(2).getNumericCellValue())).toString());
                        else
                            provider.setName(row.getCell(2).getStringCellValue());
                    }

                    if (row.getCell(3) != null)
                        provider.setAddress(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        provider.setPhoneNumber(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        provider.setFax(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setNote(row.getCell(6).getNumericCellValue() + "");
                        else
                            provider.setNote(row.getCell(6).getStringCellValue());

                    }

                    //Add to database
                    if (providerDAO.saveOrUpdate(provider)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        providersTotal = total;
                    } else {
                        int t = 0;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }
    providersTotal = total;
    return SUCCESS;
}

From source file:com.hp.action.StaffsAction.java

public String addStaffFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*from   w ww  . j a va  2 s.c  o  m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-staff");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the staff id null
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0

                ) {
                    continue;
                }

                //Init Staff Object                   
                Staff staff = new Staff();
                try {

                    if (row.getCell(1) != null)
                        staff.setId(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) != null)
                        staff.setPw(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) != null)
                        staff.setName(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        staff.setAdress(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        staff.setJob(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null)
                        staff.setPhone(row.getCell(6).getStringCellValue());

                    if (row.getCell(7) != null && row.getCell(7).getStringCellValue().compareTo("") != 0) {

                        staff.setDate(df.parse(row.getCell(7).getStringCellValue()));

                    }
                    if (row.getCell(8) != null)
                        staff.setManager(row.getCell(8).getStringCellValue());
                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            staff.setStatus(row.getCell(9).getNumericCellValue() == 1 ? true : false);
                        else
                            staff.setStatus(
                                    row.getCell(9).getStringCellValue().compareTo("1") == 0 ? true : false);
                    }

                    if (row.getCell(10) != null) {
                        if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            staff.setPermission((int) row.getCell(10).getNumericCellValue());
                        else
                            staff.setPermission(Integer.parseInt(row.getCell(10).getStringCellValue()));
                    }

                    //Add to database
                    if (staffDAO.saveOrUpdate(staff)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        staffsTotal = total;
                    } else
                        continue;
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }

    staffsTotal = total;
    return SUCCESS;
}

From source file:com.hp.excelhandle.GetData.java

public void readExcel() {
    try {/*from   w  w  w .  jav a 2  s  . com*/
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("database/customer.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns (max)
        int tmp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols)
                    cols = tmp;
            }
        }

        //            for(int r = 0; r < rows; r++) {
        //                row = sheet.getRow(r);
        //                if(row != null) {
        //                    for(int c = 0; c < cols; c++) {
        //                        cell = row.getCell((short)c);
        //                        if(cell != null) {
        //                            // Your code here
        //                        }
        //                    }
        //                }
        //            }

        row = sheet.getRow(8);
        if (row != null) {
            cell = row.getCell(1);
            if (cell != null)
                System.out.println("Row: " + 9 + ", Data: " + cell.getStringCellValue());
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}