Example usage for org.apache.poi.hssf.usermodel HSSFSheet rowIterator

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet rowIterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. rowIterator

Introduction

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

Prototype

@Override
public Iterator<Row> rowIterator()

Source Link

Usage

From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java

License:Open Source License

/**
 * @param treeDef//ww  w .j  a  v a 2s.co m
 * @return
 */
public GeologicTimePeriod convertChronoStratFromXLS(final GeologicTimePeriodTreeDef treeDef,
        final Agent userAgent) {
    startTx();

    GeologicTimePeriodTreeDefItem root = createGeologicTimePeriodTreeDefItem(null, treeDef, "Root", 0);
    GeologicTimePeriodTreeDefItem era = createGeologicTimePeriodTreeDefItem(root, treeDef, "Erathem/Era", 100);
    GeologicTimePeriodTreeDefItem period = createGeologicTimePeriodTreeDefItem(era, treeDef, "System/Period",
            200);
    GeologicTimePeriodTreeDefItem series = createGeologicTimePeriodTreeDefItem(period, treeDef, "Series/Epoch",
            300);
    @SuppressWarnings("unused")
    GeologicTimePeriodTreeDefItem member = createGeologicTimePeriodTreeDefItem(series, treeDef, "Stage/Age",
            400);
    persist(root);
    commitTx();

    series.setIsInFullName(true);

    frame.setDesc("Building ChronoStratigraphy Tree...");

    Hashtable<String, GeologicTimePeriod> chronoHash = new Hashtable<String, GeologicTimePeriod>();

    chronoHash.clear();

    String fileName = "chronostrat_tree.xls";
    File file = XMLHelper.getConfigDir("../demo_files/" + fileName);
    if (!file.exists()) {
        log.error("Couldn't file[" + file.getAbsolutePath() + "] checking the config dir");
        file = XMLHelper.getConfigDir(fileName);
        if (!file.exists()) {
            file = new File("Specify/demo_files/" + fileName);
        }
    }

    if (file == null || !file.exists()) {
        log.error("Couldn't file[" + file.getAbsolutePath() + "]");
        return null;
    }

    // setup the root ChronoStrat record (planet Earth)
    GeologicTimePeriod rootNode = new GeologicTimePeriod();
    rootNode.initialize();
    rootNode.setName(getResourceString("Root"));
    rootNode.setFullName(rootNode.getName());
    rootNode.setRankId(0);
    rootNode.setDefinition(treeDef);
    rootNode.setDefinitionItem(root);
    rootNode.setCreatedByAgent(userAgent);

    int counter = 0;

    try {
        startTx();

        persist(rootNode);

        String[] cells = new String[4];
        InputStream input = new FileInputStream(file);
        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook workBook = new HSSFWorkbook(fs);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator<?> rows = sheet.rowIterator();

        int lastRowNum = sheet.getLastRowNum();
        if (frame != null) {
            final int mx = lastRowNum;
            SwingUtilities.invokeLater(new Runnable() {
                public void run() {
                    frame.setProcess(0, mx);
                }
            });
        }

        while (rows.hasNext()) {
            if (counter == 0) {
                counter = 1;
                continue;
            }
            if (counter % 100 == 0) {
                if (frame != null)
                    frame.setProcess(counter);
                log.info("Converted " + counter + " ChronoStrat records");
            }

            HSSFRow row = (HSSFRow) rows.next();
            Iterator<?> cellsIter = row.cellIterator();
            int i = 0;
            while (cellsIter.hasNext() && i < 4) {
                HSSFCell cell = (HSSFCell) cellsIter.next();
                if (cell != null) {
                    cells[i] = StringUtils.trim(cell.getRichStringCellValue().getString());
                    i++;
                }
            }
            for (int j = i; j < 4; j++) {
                cells[j] = null;
            }
            //System.out.println();
            @SuppressWarnings("unused")
            GeologicTimePeriod newGeo = convertChronoStratRecord(cells[0], cells[1], cells[2], cells[3],
                    rootNode, userAgent);

            counter++;
        }

        input.close();

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

    if (frame != null)
        frame.setProcess(counter);

    log.info("Converted " + counter + " ChronoStrat records");

    TreeHelper.fixFullnameForNodeAndDescendants(rootNode);
    rootNode.setNodeNumber(1);
    fixNodeNumbersFromRoot(rootNode);

    commitTx();

    log.info("Converted " + counter + " Stratigraphy records");

    // set up ChronoStrat foreign key mapping for locality
    chronoHash.clear();

    return rootNode;
}

From source file:Excel.LeerExcel.java

public void leerExcel1(String fileName) throws SQLException {
    tra = new ConeccionLocal();
    List cellDataList = new ArrayList();
    try {/* w  w w .  j  a v a2  s.  c  o m*/
        /**
        * Create a new instance for FileInputStream class
        */
        FileInputStream fileInputStream = new FileInputStream(fileName);
        /**
        * Create a new instance for POIFSFileSystem class
        */
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        /*
        * Create a new instance for HSSFWorkBook Class
        */
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet hssfSheet = workBook.getSheetAt(0);
        /**
        * Iterate the rows and cells of the spreadsheet
        * to get all the datas.
        */
        Iterator rowIterator = hssfSheet.rowIterator();
        while (rowIterator.hasNext()) {
            HSSFRow hssfRow = (HSSFRow) rowIterator.next();
            Iterator iterator = hssfRow.cellIterator();
            List cellTempList = new ArrayList();
            while (iterator.hasNext()) {
                HSSFCell hssfCell = (HSSFCell) iterator.next();
                cellTempList.add(hssfCell);
            }
            cellDataList.add(cellTempList);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    /**
    * Call the printToConsole method to print the cell data in the
    * console.
    */
    printToConsole(cellDataList);
}

From source file:fitlibrary.runner.SpreadsheetRunner.java

License:Open Source License

@SuppressWarnings("unchecked")
private String collectTables(CustomRunner runner, HSSFWorkbook workbook) {
    HSSFSheet sheet = workbook.getSheetAt(0);
    String preText = "";
    for (Iterator<HSSFRow> it = sheet.rowIterator(); it.hasNext();) {
        HSSFRow row = it.next();/*from w w  w  .j  a  va  2  s .  c o  m*/
        HSSFCell[] cells = getCells(row);
        String[] borderedCellValues = getBorderedCellValues(cells, workbook);
        if (borderedCellValues.length > 0) {
            addRow(runner, borderedCellValues, preText);
            preText = "";
        } else {
            String text = allText(cells, workbook) + "\n";
            if (preText.equals("") && !text.equals(""))
                preText = text;
            else
                preText += "<BR>" + text;
        }
    }
    return preText;
}

From source file:fr.ens.transcriptome.aozan.io.CasavaDesignXLSReader.java

License:Open Source License

@Override
public CasavaDesign read() throws IOException {

    // create a POIFSFileSystem object to read the data
    final POIFSFileSystem fs = new POIFSFileSystem(this.is);

    // Create a workbook out of the input stream
    final HSSFWorkbook wb = new HSSFWorkbook(fs);

    // Get a reference to the worksheet
    final HSSFSheet sheet = wb.getSheetAt(0);

    // When we have a sheet object in hand we can iterator on
    // each sheet's rows and on each row's cells.
    final Iterator<Row> rows = sheet.rowIterator();
    final List<String> fields = new ArrayList<>();

    while (rows.hasNext()) {
        final HSSFRow row = (HSSFRow) rows.next();
        final Iterator<Cell> cells = row.cellIterator();

        while (cells.hasNext()) {
            final HSSFCell cell = (HSSFCell) cells.next();
            while (fields.size() != cell.getColumnIndex()) {
                fields.add("");
            }//  ww w. jav a2s  .c o  m

            // Convert cell value to String
            fields.add(parseCell(cell));
        }

        // Parse the fields
        if (!isFieldsEmpty(fields)) {
            parseLine(fields);
        }
        fields.clear();

    }

    this.is.close();

    return getDesign();
}

From source file:fr.univrouen.poste.services.ExcelParser.java

License:Apache License

public List<List<String>> getCells(InputStream xslFileInput) {

    List<List<String>> cellVectorHolder = new Vector<List<String>>();

    try {/*from   w  ww  .j  a va  2s .  c o m*/

        POIFSFileSystem fileSystem = new POIFSFileSystem(xslFileInput);
        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator<Row> rowIter = sheet.rowIterator();

        while (rowIter.hasNext()) {
            HSSFRow myRow = (HSSFRow) rowIter.next();
            List<String> cellStoreVector = new Vector<String>();
            // take care of blank cell !
            // @see http://stackoverflow.com/questions/4929646/how-to-get-an-excel-blank-cell-value-in-apache-poi
            int max = myRow.getLastCellNum();
            for (int i = 0; i < max; i++) {
                HSSFCell myCell = (HSSFCell) myRow.getCell(i, Row.CREATE_NULL_AS_BLANK);
                if (Cell.CELL_TYPE_STRING == myCell.getCellType())
                    cellStoreVector.add(myCell.getStringCellValue());
                else if ((Cell.CELL_TYPE_NUMERIC == myCell.getCellType()))
                    cellStoreVector.add(Long.toString(new Double(myCell.getNumericCellValue()).longValue()));
                else if ((Cell.CELL_TYPE_BLANK == myCell.getCellType()))
                    cellStoreVector.add("");
                else {
                    logger.debug("This cell is not numeric or string ... : " + myCell + " \n ... cellType : "
                            + myCell.getCellType());
                    cellStoreVector.add("");
                }
            }
            cellVectorHolder.add(cellStoreVector);
        }
    } catch (Exception e) {
        logger.error("Error during parsing the XSL File", e);
        throw new RuntimeException("Error during parsing the XSL File", e);
    }

    return cellVectorHolder;
}

From source file:gatebass.utils.exel.POIExcelReader.java

/**
 * 41 This method is used to display the Excel content to command line. 42 *
 *
 * @param xlsPath/*www.  ja va  2  s.  c o  m*/
 */
@SuppressWarnings("unchecked")
public void displayFromExcel(String xlsPath) {
    //        end_row = 2242;
    InputStream inputStream = null;

    try {
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;
    int dd = 0;

    try {
        fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        List<Individuals> individualses = new ArrayList<>();
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            //                if (row.getRowNum() >= end_row) {
            //                    break;
            //                }

            if (row.getRowNum() <= start_row) {
                continue;
            }

            dd = row.getRowNum();
            //                if (row.getRowNum() == 0
            //                        || row.getRowNum() < 195 || row.getRowNum() > 250
            //                        ) {
            //                    continue;
            //                }
            Individuals individuals = null;

            // display row number in the console.
            //                System.out.println("Row No.: " + row.getRowNum());
            // once get a row its time to iterate through cells.
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();

                //                    System.out.println("Cell No.: " + cell.getCellNum());

                /*
                 * Now we will get the cell type and display the values
                 * accordingly.
                 */
                switch (cell.getCellNum()) {
                case 0:
                    individuals = new Individuals();
                    //                            individuals = new Individuals(Integer.parseInt(cell.getRichStringCellValue().getString()));
                    break;
                case 1:
                    try {
                        individuals.setCard_id(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setCard_id(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    break;
                //                        case 2:
                //                            if (!cell.getRichStringCellValue().getString().isEmpty()) {
                //                            }
                //                            break;
                case 3:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setFirst_name(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 4:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setLast_name(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 6:
                    try {
                        individuals.setNational_id(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setNational_id(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }

                    break;
                case 10:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setFirst_name_ENG(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 16:
                    try {
                        individuals.setPostal_code(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setPostal_code(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    break;
                case 17:
                    try {
                        individuals.setId_number(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setId_number(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    break;
                case 18:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setBirth_day(databaseHelper.historyDao.getFirst("date",
                                cell.getRichStringCellValue().getString().substring(2)));
                    }
                    break;
                case 19:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setFather_first_name(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 20:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setBirth_state(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 21:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setIssued(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 22:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setStreet_address(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 23:
                    String ss = cell.getRichStringCellValue().getString();
                    individuals.setVeteran_status(BEDONE_KART);
                    if (!ss.isEmpty()) {
                        if (ss.contains("?")) {
                            individuals.setVeteran_status(MOAF);
                        } else if (ss.contains("")) {
                            individuals.setVeteran_status(PAYAN_KHEDMAT);
                        }
                    }
                    break;
                case 25:
                    try {
                        individuals.setMobile(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setMobile(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }

                    break;
                case 26:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setAcademic_degree(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 27:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setField_of_study(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 28:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setReligion(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 34:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setHave_soe_pishine(true);
                    }
                    break;
                case 35:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setComments(cell.getRichStringCellValue().getString());
                    }
                    break;
                }

                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    // cell type numeric.
                    //                            System.out.println("Numeric value: " + cell.getNumericCellValue());
                    break;
                }

                case HSSFCell.CELL_TYPE_STRING: {
                    // cell type string.
                    HSSFRichTextString richTextString = cell.getRichStringCellValue();
                    //                            System.out.println("String value: " + richTextString.getString());
                    break;
                }

                default: {
                    // types other than String and Numeric.
                    //                            System.out.println("Type not supported.");
                    break;
                }
                }
            }
            String split = FileSystems.getDefault().getSeparator();

            individuals.setFilesPatch(
                    "data" + split + "1394" + split + dd / 50 + split + individuals.getNational_id() + split);
            File imageFile = new File(
                    "d://test//Images-Personal-Gatepass//" + individuals.getCard_id() + ".jpg");

            if (imageFile.exists()) {
                individuals.setPicture_address(individuals.getNational_id() + "-pic");
                copyImageFile(imageFile.getAbsolutePath(), server + individuals.getFilesPatch(),
                        individuals.getPicture_address());
                individuals.setPicture_address(
                        individuals.getPicture_address() + getFileExtension(imageFile.getAbsolutePath()));
            }
            individualses.add(individuals);
            //                databaseHelper.individualsDao.createOrUpdate(individuals, dd);
        }
        databaseHelper.individualsDao.insertList(individualses);

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

}

From source file:gatebass.utils.exel.POIExcelReader.java

@SuppressWarnings("unchecked")
public void compnaiesFromExcel(String xlsPath) {
    InputStream inputStream = null;

    try {//from  w w  w .j  a  v a  2  s  . co m
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;

    try {
        fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        boolean check = false;
        while (rows.hasNext()) {
            check = false;
            HSSFRow row = (HSSFRow) rows.next();
            //                if (row.getRowNum() >= end_row) {
            //                    break;
            //                }
            if (row.getRowNum() <= start_row) {
                continue;
            }

            Companies companies = new Companies();

            //                System.out.println("Row No.: " + row.getRowNum());
            String companyName = row.getCell(2).getRichStringCellValue().getString();
            if (!companyName.isEmpty()) {
                Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_fa", companyName);
                if (companiesTEMP == null) {
                    check = true;
                    companies.setCompany_fa(companyName);
                    companies.setActive(true);
                }
            } else {
                continue;
            }
            try {
                String companyNameEn = row.getCell(9).getRichStringCellValue().getString();
                if (!companyNameEn.isEmpty()) {
                    Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_en", companyNameEn);
                    if (companiesTEMP == null) {
                        companies.setCompany_en(companyNameEn);
                    }
                }
            } catch (Exception e) {
            }

            if (check) {
                //                    companieses.add(companies);
                Manage mm = databaseHelper.manageDao.getFirst("key", "company_folder_count");
                int jj = Integer.parseInt(mm.getValue());
                companies.setFolder_name("C" + jj);
                databaseHelper.companiesDao.createOrUpdate(companies);
                ++jj;
                mm.setValue(jj + "");
                databaseHelper.manageDao.createOrUpdate(mm);
            }
        }
        //            databaseHelper.companiesDao.insertList(companieses);
    } catch (Exception e) {
        Logger.getLogger(POIExcelReader.class.getName()).log(Level.SEVERE, e.getMessage(), e);
    }

}

From source file:gatebass.utils.exel.POIExcelReader.java

@SuppressWarnings("unchecked")
public void historyFromExcel(String xlsPath) {
    InputStream inputStream = null;

    try {//w  ww.jav a 2  s .  c  o m
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;

    try {
        fileSystem = new POIFSFileSystem(inputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        boolean check;
        List<History> historys = new ArrayList<>();
        while (rows.hasNext()) {
            check = false;
            HSSFRow row = (HSSFRow) rows.next();
            if (row.getRowNum() <= start_row) {
                continue;
            }
            History historyH = null;

            //                System.out.println("Row No.: " + row.getRowNum());
            String history = "";

            try {
                history = row.getCell(7).getRichStringCellValue().getString();
                if (!history.isEmpty()) {
                    history = history.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history);
                    if (HistoryTEMP == null) {
                        check = true;
                        historyH = new History(history.substring(0, history.indexOf("/")),
                                history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")),
                                history.substring(history.lastIndexOf("/") + 1));
                    }
                }
                if (check) {
                    historys.add(historyH);
                    //                        databaseHelper.historyDao.createOrUpdate(historyH);
                }
            } catch (Exception e) {
            }

            check = false;
            historyH = null;
            history = "";

            try {
                history = row.getCell(18).getRichStringCellValue().getString();
                if (!history.isEmpty()) {
                    history = history.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history);
                    if (HistoryTEMP == null) {
                        check = true;
                        historyH = new History(history.substring(0, history.indexOf("/")),
                                history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")),
                                history.substring(history.lastIndexOf("/") + 1));
                    }
                }
                if (check) {
                    historys.add(historyH);
                    //                        databaseHelper.historyDao.createOrUpdate(historyH);
                }
            } catch (Exception e) {
            }

            check = false;
            historyH = null;
            history = "";

            try {
                history = row.getCell(24).getRichStringCellValue().getString();
                if (!history.isEmpty()) {
                    history = history.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history);
                    if (HistoryTEMP == null) {
                        check = true;
                        historyH = new History(history.substring(0, history.indexOf("/")),
                                history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")),
                                history.substring(history.lastIndexOf("/") + 1));
                    }
                }
                if (check) {
                    historys.add(historyH);
                    //                        databaseHelper.historyDao.createOrUpdate(historyH);
                }
            } catch (Exception e) {
            }

            check = false;
            historyH = null;
            history = "";

            try {
                history = row.getCell(29).getRichStringCellValue().getString();
                if (!history.isEmpty()) {
                    history = history.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history);
                    if (HistoryTEMP == null) {
                        check = true;
                        historyH = new History(history.substring(0, history.indexOf("/")),
                                history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")),
                                history.substring(history.lastIndexOf("/") + 1));
                    }
                }
                if (check) {
                    historys.add(historyH);
                    //                        databaseHelper.historyDao.createOrUpdate(historyH);
                }
            } catch (Exception e) {
            }

            check = false;
            historyH = null;
            history = "";

            try {
                history = row.getCell(30).getRichStringCellValue().getString();
                if (!history.isEmpty()) {
                    history = history.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history);
                    if (HistoryTEMP == null) {
                        check = true;
                        historyH = new History(history.substring(0, history.indexOf("/")),
                                history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")),
                                history.substring(history.lastIndexOf("/") + 1));
                    }
                }
                if (check) {
                    historys.add(historyH);
                    //                        databaseHelper.historyDao.createOrUpdate(historyH);
                }
            } catch (Exception e) {
            }

            check = false;
            historyH = null;
            history = "";

            try {
                history = row.getCell(31).getRichStringCellValue().getString();
                if (!history.isEmpty()) {
                    history = history.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history);
                    if (HistoryTEMP == null) {
                        check = true;
                        historyH = new History(history.substring(0, history.indexOf("/")),
                                history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")),
                                history.substring(history.lastIndexOf("/") + 1));
                    }
                }
                if (check) {
                    historys.add(historyH);
                    //                        databaseHelper.historyDao.createOrUpdate(historyH);
                }
            } catch (Exception e) {
            }

            check = false;
            historyH = null;
            history = "";

            try {
                history = row.getCell(32).getRichStringCellValue().getString();
                if (!history.isEmpty()) {
                    history = history.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history);
                    if (HistoryTEMP == null) {
                        check = true;
                        historyH = new History(history.substring(0, history.indexOf("/")),
                                history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")),
                                history.substring(history.lastIndexOf("/") + 1));
                    }
                }
                if (check) {
                    historys.add(historyH);
                    //                        databaseHelper.historyDao.createOrUpdate(historyH);
                }
            } catch (Exception e) {
            }

            check = false;
            historyH = null;
            history = "";

            try {
                history = row.getCell(33).getRichStringCellValue().getString();
                if (!history.isEmpty()) {
                    history = history.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", history);
                    if (HistoryTEMP == null) {
                        check = true;
                        historyH = new History(history.substring(0, history.indexOf("/")),
                                history.substring(history.indexOf("/") + 1, history.lastIndexOf("/")),
                                history.substring(history.lastIndexOf("/") + 1));
                    }
                }
                if (check) {
                    historys.add(historyH);
                    //                        databaseHelper.historyDao.createOrUpdate(historyH);
                }
            } catch (Exception e) {
            }
        }
        databaseHelper.historyDao.insertList(historys);
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:gatebass.utils.exel.POIExcelReader.java

@SuppressWarnings("unchecked")
public void worksFromExcel(String xlsPath) {
    InputStream inputStream = null;

    try {//from   w  w  w.ja v  a2 s.  c o  m
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;

    List<WorkHistory> historys = new ArrayList<>();
    try {
        fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        boolean check = false;
        while (rows.hasNext()) {
            check = false;
            HSSFRow row = (HSSFRow) rows.next();
            //                if (row.getRowNum() >= end_row) {
            //                    break;
            //                }
            if (row.getRowNum() <= start_row) {
                continue;
            }

            WorkHistory workHistory = new WorkHistory();

            //                System.out.println("Row No.: " + row.getRowNum());
            String row_value = row.getCell(2).getRichStringCellValue().getString();
            if (!row_value.isEmpty()) {
                Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_fa", row_value);
                String ss1 = companiesTEMP.getCompany_fa();
                if (ss1.equals("   ")
                        || ss1.equals("    ")
                        || ss1.equals("  ")
                        || ss1.equals("   ")
                        || ss1.equals("  ")) {
                    workHistory.setGate_type(WorkHistory.EMPLOYER);
                } else {
                    workHistory.setGate_type(WorkHistory.CONTRACTOR);
                }

                workHistory.setCompanies(companiesTEMP);
            }

            row_value = row.getCell(5).getRichStringCellValue().getString();
            if (!row_value.isEmpty()) {
                workHistory.setJobTitle(row_value);
            }

            try {
                row_value = row.getCell(7).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    row_value = row_value.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value);
                    workHistory.setCardExpirationDateId(HistoryTEMP);
                }
            } catch (Exception e) {
            }

            try {
                row_value = row.getCell(11).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    workHistory.setJobTitleENG(row_value);
                }
            } catch (Exception e) {
            }

            try {
                row_value = row.getCell(24).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    row_value = row_value.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value);
                    workHistory.setEmploymentDateId(HistoryTEMP);
                }
            } catch (Exception e) {
            }

            try {
                row_value = row.getCell(29).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    row_value = row_value.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value);
                    workHistory.setCardIssuedDateId(HistoryTEMP);
                }
            } catch (Exception e) {
            }

            try {
                row_value = row.getCell(30).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    row_value = row_value.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value);
                    workHistory.setCardIssuedDateId(HistoryTEMP);
                }
            } catch (Exception e) {
            }

            try {
                row_value = row.getCell(31).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    row_value = row_value.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value);
                    workHistory.setCardIssuedDateId(HistoryTEMP);
                }
            } catch (Exception e) {
            }

            try {
                row_value = row.getCell(33).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    row_value = row_value.substring(2);
                    History HistoryTEMP = databaseHelper.historyDao.getFirst("date", row_value);

                    workHistory.setCardDeliveryDate(HistoryTEMP);
                }
            } catch (Exception e) {
            }
            //                System.out.println("Row No.: " + row.getRowNum() + " CardDelivery.: " + (workHistory.getCardDeliveryDate() == null ? "null" : workHistory.getCardDeliveryDate().getDate()));

            try {
                row_value = ((long) row.getCell(6).getNumericCellValue()) + "";
                if (!row_value.isEmpty()) {
                    Individuals individuals = databaseHelper.individualsDao.getFirst("national_id", row_value);
                    workHistory.setIndividualsId(individuals);
                }
            } catch (Exception e) {
            }
            try {
                row_value = row.getCell(6).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    Individuals individuals = databaseHelper.individualsDao.getFirst("national_id", row_value);
                    workHistory.setIndividualsId(individuals);
                }
            } catch (Exception e) {
            }

            historys.add(workHistory);

            //                    databaseHelper.manageDao.createOrUpdate(mm);
        }
        databaseHelper.workHistoryDao.insertList(historys);
    } catch (Exception e) {
        Logger.getLogger(POIExcelReader.class.getName()).log(Level.SEVERE, e.getMessage(), e);
    }

}

From source file:gatebass.utils.exel.POIExcelReader.java

@SuppressWarnings("unchecked")
public void compnaiesFromExcel2(String xlsPath) {
    InputStream inputStream = null;

    try {//from w  w w.  j a v  a  2s. c  om
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;

    try {
        fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        boolean check = false;
        while (rows.hasNext()) {
            check = false;
            HSSFRow row = (HSSFRow) rows.next();
            //                if (row.getRowNum() >= end_row) {
            //                    break;
            //                }
            if (row.getRowNum() <= start_row) {
                continue;
            }

            Companies companies = new Companies();

            //                System.out.println("Row No.: " + row.getRowNum());
            String companyName = row.getCell(2).getRichStringCellValue().getString();
            if (!companyName.isEmpty()) {
                Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_fa", companyName);
                if (companiesTEMP == null) {
                    check = true;
                    companies.setCompany_fa(companyName);
                    companies.setActive(true);
                }
            } else {
                continue;
            }
            try {
                String companyNameEn = row.getCell(9).getRichStringCellValue().getString();
                if (!companyNameEn.isEmpty()) {
                    //                        Companies companiesTEMP = databaseHelper.companiesDao.getFirst("company_en", companyNameEn);
                    //                        if (companiesTEMP == null) {
                    //                            companies.setCompany_en(companyNameEn);
                    //                        }
                }
            } catch (Exception e) {
            }

            if (check) {
                //                    companieses.add(companies);
                Manage mm = databaseHelper.manageDao.getFirst("key", "company_folder_count");
                int jj = Integer.parseInt(mm.getValue());
                companies.setFolder_name("C" + jj);
                databaseHelper.companiesDao.createOrUpdate(companies);
                ++jj;
                mm.setValue(jj + "");
                databaseHelper.manageDao.createOrUpdate(mm);
            }
        }
        //            databaseHelper.companiesDao.insertList(companieses);
    } catch (Exception e) {
        Logger.getLogger(POIExcelReader.class.getName()).log(Level.SEVERE, e.getMessage(), e);
    }

}