Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum)

Source Link

Document

Get the hssfcell representing a given column (logical cell) 0-based.

Usage

From source file:eu.cassandra.training.entities.Installation.java

License:Apache License

/**
 * This is the parser for the measurement file. It parses through the file and
 * creates the arrays of the active and reactive power consumptions.
 *//*from  w ww . ja va 2 s  .c om*/
public void parseMeasurementsFile() throws IOException {

    ArrayList<Double> temp = new ArrayList<Double>();
    ArrayList<Double> temp2 = new ArrayList<Double>();

    String extension = measurementsFile.substring(measurementsFile.length() - 3, measurementsFile.length());

    switch (extension) {

    case "csv":

        boolean startFlag = true;

        File file = new File(measurementsFile);
        Scanner scanner = new Scanner(file);

        int counter = 0;

        while (scanner.hasNext()) {

            String line = scanner.nextLine();
            // System.out.println(line);

            if (startFlag) {
                if (line.split(",")[0].equalsIgnoreCase("1")) {

                    startDate = new DateTime(2012, 01, 01, 00, 00);

                } else {

                    int year = Integer.parseInt(line.split(",")[0].substring(0, 4));
                    int month = Integer.parseInt(line.split(",")[0].substring(4, 6));
                    int day = Integer.parseInt(line.split(",")[0].substring(6, 8));
                    int hour = 0;
                    int minute = 0;

                    startDate = new DateTime(year, month, day, hour, minute);

                }

                // System.out.println(startDate.toString());
                startFlag = false;
            }

            temp.add(Double.parseDouble(line.split(",")[1]));

            if (!activeOnly)
                temp2.add(Double.parseDouble(line.split(",")[2]));

            counter++;
        }

        endDate = startDate.plusMinutes(counter);

        // System.out.println(endDate.toString());

        scanner.close();
        break;

    case "xls":

        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(measurementsFile));

        // Get the first sheet.
        HSSFSheet sheet = workbook.getSheetAt(0);
        for (int i = 0; i < sheet.getLastRowNum(); i++) {

            // Set value of the first cell.
            HSSFRow row = sheet.getRow(i + 1);
            temp.add(row.getCell(1).getNumericCellValue());
            if (!activeOnly)
                temp2.add(row.getCell(2).getNumericCellValue());
        }

        break;

    }

    activePower = new double[temp.size()];

    for (int i = 0; i < temp.size(); i++)
        activePower[i] = temp.get(i);

    if (!activeOnly) {
        reactivePower = new double[temp2.size()];
        for (int i = 0; i < temp2.size(); i++)
            reactivePower[i] = temp2.get(i);
    }

}

From source file:eu.cassandra.training.utils.Utils.java

License:Apache License

/**
 * This is the parser for the measurement file. It parses through the file and
 * checks for errors. It can parse through .csv and .xls file and uses
 * different libraries for each file type.
 * /*from ww  w .  j  a  va  2s . c  om*/
 * @param measurementsFile
 *          The file name of the measurements file.
 * @param power
 *          The type of data sets contained within the file (only active or
 *          active and reactive power)
 * @return the line of error or -1 if no error is found.
 * @throws IOException
 */
public static int parseMeasurementsFile(String measurementsFile, boolean power) throws IOException {

    int result = -1;

    String extension = measurementsFile.substring(measurementsFile.length() - 3, measurementsFile.length());

    switch (extension) {

    case "csv":

        File file = new File(measurementsFile);
        Scanner scanner = new Scanner(file);
        scanner.nextLine();
        int counter = 2;
        while (scanner.hasNext()) {

            String line = scanner.nextLine();

            String[] testString = line.split(",");

            if (power) {
                if (testString.length != 2)
                    result = counter;
                try {
                    Double.parseDouble(testString[1]);
                } catch (NumberFormatException e) {
                    result = counter;
                }
            } else {
                if (testString.length != 3)
                    result = counter;
                try {
                    Double.parseDouble(testString[1]);
                    Double.parseDouble(testString[2]);
                } catch (NumberFormatException e) {
                    result = counter;
                }

                if (result != -1)
                    break;
            }
            counter++;
        }

        scanner.close();
        System.out.println("Your csv file has been read!");
        break;

    case "xls":

        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(measurementsFile));

        // Get the first sheet.
        HSSFSheet sheet = workbook.getSheetAt(0);
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            // Set value of the first cell.
            HSSFRow row = sheet.getRow(i + 1);

            if (power) {
                if (row.getCell(2) != null)
                    result = i + 2;
                try {
                    Double.parseDouble(row.getCell(1).toString());
                } catch (NumberFormatException e) {
                    result = i + 2;
                }
            } else {
                if (row.getCell(3) != null)
                    result = i + 2;
                try {
                    Double.parseDouble(row.getCell(1).toString());
                    Double.parseDouble(row.getCell(2).toString());
                } catch (NumberFormatException e) {
                    result = i + 2;
                }
            }

            if (result != -1)
                break;
        }

        System.out.println("Your excel file has been read!");
        break;

    }

    return result;

}

From source file:eu.squadd.timesheets.eolas.TimeTemplate.java

public String prepareTimesheet(String[] args) {
    String response = null;/*from w  w  w.java  2  s. c o m*/
    try {
        String[] ym = args[0].split("/");
        month = Integer.parseInt(ym[0]);
        year = Integer.parseInt(ym[1]);

        Calendar cal = Calendar.getInstance(TimeZone.getDefault());
        cal.set(Calendar.YEAR, year);
        cal.set(Calendar.MONTH, month - 1);
        int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        monthName = cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.ENGLISH);
        String periodName = monthName + "-" + year;
        cal.set(Calendar.DATE, 1);
        String dayOfWeek = new SimpleDateFormat("EE").format(cal.getTime());

        System.out.println("Month: " + periodName);
        System.out.println("Days in month: " + days);
        System.out.println("Month starts in: " + dayOfWeek);

        Map<String, String> bankHolidays = year == 2016 ? publicHolidays2016 : publicHolidays2017;
        Map<String, String> holidays = this.extractHolidays(args);

        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(template));
        HSSFSheet sheet = wb.getSheet("timesheet"); //getSheetAt(0);
        HSSFRow currentRow;
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        sheet.getRow(4).getCell(1).setCellValue(periodName);
        int row = 7;
        int startRow = 0;
        int i = 1;
        while (i <= days) {
            currentRow = sheet.getRow(row);
            if (currentRow.getRowNum() > 47)
                break;
            String day = currentRow.getCell(0).getStringCellValue();

            if (day.startsWith("Total")) {
                evaluator.evaluateFormulaCell(currentRow.getCell(2));
                evaluator.evaluateFormulaCell(currentRow.getCell(4));
                row++;
                continue;
            }

            if (startRow == 0) {
                if (dayOfWeek.equals(day.substring(0, 3))) {
                    startRow = currentRow.getRowNum();
                    System.out.println("Starting row found: " + startRow + 1);
                } else {
                    row++;
                    continue;
                }
            }
            cal.set(Calendar.DATE, i);
            String date = sdf.format(cal.getTime());
            if (!day.equals("Saturday") && !day.equals("Sunday") && bankHolidays.get(date) == null
                    && holidays.get(date) == null) {
                currentRow.getCell(1).setCellValue(date);
                currentRow.getCell(2).setCellValue(defaultHours); // regular hours
                //currentRow.getCell(3).setCellValue(defaultHours);   // overtime hours
                currentRow.getCell(4).setCellValue(defaultHours); // total hours                    
            }
            i++;
            row++;
        }
        currentRow = sheet.getRow(46);
        evaluator.evaluateFormulaCell(currentRow.getCell(2));
        evaluator.evaluateFormulaCell(currentRow.getCell(4));
        currentRow = sheet.getRow(47);
        evaluator.evaluateFormulaCell(currentRow.getCell(2));
        evaluator.evaluateFormulaCell(currentRow.getCell(4));
        response = outFilePath.replace("#MONTH#", periodName);
        wb.write(new FileOutputStream(response));

    } catch (IOException ex) {
        Logger.getLogger(Timesheets.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Timesheet created.");
    return response;
}

From source file:excel.scanner.ExcelScanner.java

private void jButtonSearchActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonSearchActionPerformed
    if (file == null) {
        jLabelResults.setText("No File Found");
        return;//  www  .java 2 s .  com
    }
    HSSFRow row;
    HSSFCell cell;
    String selectedSheet;
    int index = 0;
    int resultCount = 0;
    String curCellText;

    try {
        jLabelResults.setText("");
        listModel.removeAllElements();
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                curCellText = cell.getStringCellValue();
                if (curCellText.contains(jTextFieldSearch.getText())) {
                    listModel.addElement(" " + CellReference.convertNumToColString(j) + (i + 1));
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:file.open.util.parse.XlsParser.java

License:Open Source License

public String[] splitLine() throws Exception {
    if (m_iCurrentRow == m_iNbRows) {
        return null;
    }/*from  www .  ja  v  a 2s  . c o m*/

    HSSFRow row = m_sheet.getRow(m_iCurrentRow);
    if (row == null) {
        return null;
    } else {
        int cellIndex = 0;
        int noOfCells = row.getPhysicalNumberOfCells();
        String[] values = new String[noOfCells];
        short firstCellNum = row.getFirstCellNum();
        short lastCellNum = row.getLastCellNum();

        if (firstCellNum >= 0 && lastCellNum >= 0) {
            for (short iCurrent = firstCellNum; iCurrent < lastCellNum; iCurrent++) {
                HSSFCell cell = (HSSFCell) row.getCell((int) iCurrent);
                if (cell == null) {
                    values[iCurrent] = "";
                    cellIndex++;
                    continue;
                } else {
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        double value = cell.getNumericCellValue();
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            if (HSSFDateUtil.isValidExcelDate(value)) {
                                Date date = HSSFDateUtil.getJavaDate(value);
                                SimpleDateFormat dateFormat = new SimpleDateFormat(JAVA_TOSTRING);
                                values[iCurrent] = dateFormat.format(date);
                            } else {
                                throw new Exception("Invalid Date value found at row number " + row.getRowNum()
                                        + " and column number " + cell.getNumericCellValue());
                            }
                        } else {
                            values[iCurrent] = value + "";
                        }
                        break;

                    case HSSFCell.CELL_TYPE_STRING:
                        values[iCurrent] = cell.getStringCellValue();
                        break;

                    case HSSFCell.CELL_TYPE_BLANK:
                        values[iCurrent] = null;
                        break;

                    default:
                        values[iCurrent] = null;
                    }
                }
            }
        }
        m_iCurrentRow++;
        return values;
    }

}

From source file:FormatConvert.exceloperation.Excel2csv.java

public static void copySheets2CSV(HSSFSheet sheet, String csvfile) {
    int maxColumnNum = 0;

    try {//www  . j a  va  2  s .c o  m
        FileWriter fw = new FileWriter(csvfile);

        String str = "";
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow srcRow = sheet.getRow(i);

            if (srcRow != null) {
                for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
                    if (j != srcRow.getLastCellNum()) {
                        str = str + srcRow.getCell(j).getStringCellValue() + ",";
                    } else {
                        str = str + srcRow.getCell(j).getStringCellValue() + "\r\n";
                    }

                }
                fw.append(str);
            }
            str = "";
        }

        fw.flush();
        fw.close();
    } catch (IOException ex) {

    }
    //Util.copyPictures(newSheet,sheet) ;
}

From source file:framework.retrieval.engine.index.create.impl.file.parse.ExcelFileContentParser.java

License:Apache License

public String getContent(RFileDocument document, String charsetName) {

    InputStream fileInputStream = null;
    StringBuffer content = new StringBuffer();

    try {/*from  ww w .  j  a va2  s.  c o m*/
        fileInputStream = new FileInputStream(document.getFile());
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);// Excel

        if (workbook != null) {
            int numSheetsNumber = workbook.getNumberOfSheets();
            for (int numSheets = 0; numSheets < numSheetsNumber; numSheets++) {
                HSSFSheet aSheet = workbook.getSheetAt(numSheets);// sheet
                if (aSheet != null) {
                    int lastRowNum = aSheet.getLastRowNum();
                    for (int rowNumOfSheet = 0; rowNumOfSheet <= lastRowNum; rowNumOfSheet++) {
                        HSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 
                        if (aRow != null) {
                            int lastCellNum = aRow.getLastCellNum();
                            for (int cellNumOfRow = 0; cellNumOfRow <= lastCellNum; cellNumOfRow++) {
                                HSSFCell aCell = aRow.getCell(cellNumOfRow);// 
                                if (aCell != null) {
                                    int cellType = aCell.getCellType();
                                    if (cellType == HSSFCell.CELL_TYPE_STRING) {
                                        String value = StringClass.getString(aCell.getStringCellValue());
                                        content.append(value);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        RetrievalUtil.errorLog(log, document.getFile().getAbsolutePath(), e);
    } finally {
        if (fileInputStream != null) {
            try {
                fileInputStream.close();
            } catch (Exception e) {

            }
        }
    }

    return content.toString();

}

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

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

    try {//from   w  ww .  ja  v a 2s. 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 {//from w  w w . j  av 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;
        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 {//w w  w.ja  v a 2s  .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);
    }

}