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:gatebass.utils.exel.POIExcelReader.java

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

    try {//from   ww w .jav  a2s . 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;
        List<History> historys = new ArrayList<>();
        while (rows.hasNext()) {
            check = false;
            HSSFRow row = (HSSFRow) rows.next();

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

            try {
                history = row.getCell(6).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(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(8).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(9).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(10).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(11).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(12).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(13).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 displayFromExcel2(String xlsPath) {
    InputStream inputStream = null;

    try {//www.j  a v 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;
    int dd = 0;

    try {
        fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        List<Cars> carses = 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;
            //                }
            Cars cars = 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:
                    cars = new Cars();
                    //                            individuals = new Individuals(Integer.parseInt(cell.getRichStringCellValue().getString()));
                    break;
                case 1:
                    try {
                        cars.setCard_id(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        cars.setCard_id(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    cars.setShasi_number(cars.getCard_id());
                    break;
                //                        case 2:
                //                            if (!cell.getRichStringCellValue().getString().isEmpty()) {
                //                            }
                //                            break;
                case 3:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        cars.setCar_name(cell.getRichStringCellValue().getString());
                    }
                    break;
                //                        case 4:
                //                            if (!cell.getRichStringCellValue().getString().isEmpty()) {
                //                                cars.setLast_name(cell.getRichStringCellValue().getString());
                //                            }
                //                            break;
                //                        case 5:
                //                            if (!cell.getRichStringCellValue().getString().isEmpty()) {
                //                                cars.set(cell.getRichStringCellValue().getString());
                //                            }
                //                            break;
                case 13:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        cars.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();

            cars.setFilesPatch(
                    "data" + split + "1394" + split + dd / 50 + split + cars.getShasi_number() + "_c" + split);
            carses.add(cars);
            //                databaseHelper.individualsDao.createOrUpdate(individuals, dd);
        }
        databaseHelper.carDao.insertList(carses);

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

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

@SuppressWarnings("unchecked")
public void worksFromExcel2(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;

    List<CarHistory> 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;
            }

            CarHistory carHistory = new CarHistory();

            //                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);
                carHistory.setCompanies(companiesTEMP);
            }

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

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

            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);
                    carHistory.setCardIssuedDateId(HistoryTEMP);
                }
            } catch (Exception e) {
            }

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

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

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

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

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

            try {
                row_value = ((long) row.getCell(1).getNumericCellValue()) + "";
                if (!row_value.isEmpty()) {
                    Cars cr1 = databaseHelper.carDao.getFirst("card_id", row_value);
                    carHistory.setCar_id(cr1);
                }
            } catch (Exception e) {
            }
            try {
                row_value = row.getCell(1).getRichStringCellValue().getString();
                if (!row_value.isEmpty()) {
                    Cars cr1 = databaseHelper.carDao.getFirst("card_id", row_value);
                    carHistory.setCar_id(cr1);
                }
            } catch (Exception e) {
            }

            historys.add(carHistory);

            //                    databaseHelper.manageDao.createOrUpdate(mm);
        }
        databaseHelper.carsHistoryDao.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 historyFromExcel3(String xlsPath) {
    InputStream inputStream = null;

    try {//  ww w  .ja  v 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();

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

            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(10).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(11).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 displayFromExcel3(String xlsPath) {
    InputStream inputStream = null;

    try {/* www. j ava2s . 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;
    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<>();

        //            Manage manage = databaseHelper.manageDao.getFirst("key", "card_id_count");
        //            long card_sequential = Long.parseLong(manage.getValue());
        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.
            String meli = "";

            {
                try {
                    meli = (((long) row.getCell(6).getNumericCellValue()) + "");
                } catch (Exception e) {
                }
                try {
                    meli = (row.getCell(6).getRichStringCellValue().getString());
                } catch (Exception e) {
                }
                //                    System.out.println("meli = " + meli);
                individuals = databaseHelper.individualsDao.getFirst("national_id", meli);
                if (individuals == null) {

                    individuals = new Individuals();
                    individuals.setNational_id(meli);

                    //                        try {
                    //                            individuals.setCard_id(((long) row.getCell(0).getNumericCellValue()) + "");
                    //                        } catch (Exception e) {
                    //                        }
                    //                        try {
                    //                            individuals.setCard_id(row.getCell(0).getRichStringCellValue().getString());
                    //                        } catch (Exception e) {
                    //                        }
                    if (!row.getCell(1).getRichStringCellValue().getString().isEmpty()) {
                        individuals.setFirst_name(row.getCell(1).getRichStringCellValue().getString());
                    }
                    try {
                        if (!row.getCell(2).getRichStringCellValue().getString().isEmpty()) {
                            individuals
                                    .setFather_first_name(row.getCell(2).getRichStringCellValue().getString());
                        }
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setId_number(((long) row.getCell(3).getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setId_number(row.getCell(3).getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    try {
                        if (!row.getCell(4).getRichStringCellValue().getString().isEmpty()) {
                            individuals.setBirth_day(databaseHelper.historyDao.getFirst("date",
                                    row.getCell(4).getRichStringCellValue().getString().substring(2)));
                        }
                    } catch (Exception e) {
                    }
                    try {
                        if (!row.getCell(5).getRichStringCellValue().getString().isEmpty()) {
                            individuals.setBirth_state(row.getCell(5).getRichStringCellValue().getString());
                        }
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setComments("   : "
                                + ((long) row.getCell(0).getNumericCellValue())
                                + (individuals.getComments() != null ? "\n" + individuals.getComments() : ""));
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setComments("   : "
                                + row.getCell(0).getRichStringCellValue().getString()
                                + (individuals.getComments() != null ? "\n" + individuals.getComments() : ""));
                    } catch (Exception e) {
                    }

                    String split = FileSystems.getDefault().getSeparator();
                    individuals.setFilesPatch("data" + split + "1394" + split + dd / 50 + split
                            + individuals.getNational_id() + split);
                    //                        individuals.setCard_id(card_sequential + "");
                    //                        card_sequential++;

                    individualses.add(individuals);
                }

            }
            //                databaseHelper.individualsDao.createOrUpdate(individuals, dd);
        }

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

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

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

    try {//from   w w  w  .  ja 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;
    int dd = 0;

    try {
        fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        List<WorkHistory> whs = 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.
            String meli = "";

            {
                try {
                    meli = (((long) row.getCell(6).getNumericCellValue()) + "");
                } catch (Exception e) {
                }
                try {
                    meli = (row.getCell(6).getRichStringCellValue().getString());
                } catch (Exception e) {
                }

                //                    System.out.println("meli = " + meli);
                individuals = databaseHelper.individualsDao.getFirst("national_id", meli);
            }
            whs.add(works_add(row, individuals));
        }
        databaseHelper.workHistoryDao.insertList(whs);

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

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

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

    try {//  w  w w .  j  ava 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();

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

            try {
                history = row.getCell(1).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 replica_FromExcel(String xlsPath) {
    InputStream inputStream = null;

    try {//from   ww  w. ja 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();
        List<IndividualReplica> individualReplicas = new ArrayList<>();

        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            //                if (row.getRowNum() >= end_row) {
            //                    break;
            //                }

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

            //                if (row.getRowNum() == 0
            //                        || row.getRowNum() < 195 || row.getRowNum() > 250
            //                        ) {
            //                    continue;
            //                }
            IndividualReplica individualReplica = new IndividualReplica();

            // display row number in the console.
            //                System.out.println("Row No.: " + row.getRowNum());
            // once get a row its time to iterate through cells.
            String meli = "";
            try {
                meli = (((long) row.getCell(3).getNumericCellValue()) + "");
            } catch (Exception e) {
            }
            try {
                meli = (row.getCell(3).getRichStringCellValue().getString());
            } catch (Exception e) {
            }
            Individuals individuals = databaseHelper.individualsDao.getFirst("card_id", meli);
            if (individuals != null) {
                //                    individualReplica.setIndividual_id(individuals);

                try {
                    if (!row.getCell(1).getRichStringCellValue().getString().isEmpty()) {
                        individualReplica.setHistory_id(databaseHelper.historyDao.getFirst("date",
                                row.getCell(1).getRichStringCellValue().getString().substring(2)));
                    }
                } catch (Exception e) {
                }

                try {
                    individualReplica.setMablagh(((long) row.getCell(5).getNumericCellValue()) + "");
                } catch (Exception e) {
                }
                try {
                    individualReplica.setMablagh(row.getCell(5).getRichStringCellValue().getString());
                } catch (Exception e) {
                }

                try {
                    individualReplica.setDescription(((long) row.getCell(6).getNumericCellValue()) + "");
                } catch (Exception e) {
                }
                try {
                    individualReplica.setDescription(row.getCell(6).getRichStringCellValue().getString());
                } catch (Exception e) {
                }
                individualReplicas.add(individualReplica);
            } else {
                System.out.println("replica ID : " + meli);
            }
        }
        databaseHelper.individualReplicaDao.insertList(individualReplicas);

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

From source file:gda.hrpd.data.HSSF.java

License:Apache License

/**
 * Method main Given 1 argument takes that as the filename, inputs it and dumps the cell values/types out to sys.out
 * given 2 arguments where the second argument is the word "write" and the first is the filename - writes out a
 * sample (test) spreadsheet (see public HSSF(String filename, boolean write)). given 2 arguments where the first is
 * an input filename and the second an output filename (not write), attempts to fully read in the spreadsheet and
 * fully write it out. given 3 arguments where the first is an input filename and the second an output filename (not
 * write) and the third is "modify1", attempts to read in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at
 * row 39, col 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you take the output from
 * the write test, you'll have a valid scenario.
 *
 * @param args/*www  . j  av a 2s  .c o  m*/
 */

public static void main(String[] args) {
    if (args.length < 2) {

        /*
         * try { HSSF hssf = new HSSF(args[ 0 ]); System.out.println("Data dump:\n"); HSSFWorkbook wb =
         * hssf.hssfworkbook; for (int k = 0; k < wb.getNumberOfSheets(); k++) { System.out.println("Sheet " + k);
         * HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows;
         * r++) { HSSFRow row = sheet.getPhysicalRowAt(r); int cells = row.getPhysicalNumberOfCells();
         * System.out.println("ROW " + row.getRowNum()); for (int c = 0; c < cells; c++) { HSSFCell cell =
         * row.getPhysicalCellAt(c); String value = null; switch (cell.getCellType()) { case
         * HSSFCell.CELL_TYPE_FORMULA : value = "FORMULA "; break; case HSSFCell.CELL_TYPE_NUMERIC : value =
         * "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING : value = "STRING
         * value=" + cell.getStringCellValue(); break; default : } System.out.println("CELL col=" +
         * cell.getCellNum() + " VALUE=" + value); } } } } catch (Exception e) { e.printStackTrace(); }
         */
    } else if (args.length == 2) {
        if (args[1].toLowerCase().equals("write")) {
            System.out.println("Write mode");
            try {
                long time = System.currentTimeMillis();
                // HSSF hssf = new HSSF(args[ 0 ], true);

                System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("readwrite test");
            try {
                HSSF hssf = new HSSF(args[0]);

                // HSSFStream hssfstream = hssf.hssfstream;
                HSSFWorkbook wb = hssf.hssfworkbook;
                FileOutputStream stream = new FileOutputStream(args[1]);

                // HSSFCell cell = new HSSFCell();
                // cell.setCellNum((short)3);
                // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                // cell.setCellValue(-8009.999);
                // hssfstream.modifyCell(cell,0,(short)6);
                wb.write(stream);
                stream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    } else if ((args.length == 3) && args[2].toLowerCase().equals("modify1")) {
        try // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
        {
            HSSF hssf = new HSSF(args[0]);

            // HSSFStream hssfstream = hssf.hssfstream;
            HSSFWorkbook wb = hssf.hssfworkbook;
            FileOutputStream stream = new FileOutputStream(args[1]);
            HSSFSheet sheet = wb.getSheetAt(0);

            for (int k = 0; k < 25; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            for (int k = 74; k < 100; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            HSSFRow row = sheet.getRow(39);
            HSSFCell cell = row.getCell((short) 3);

            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString("MODIFIED CELL!!!!!"));

            // HSSFCell cell = new HSSFCell();
            // cell.setCellNum((short)3);
            // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            // cell.setCellValue(-8009.999);
            // hssfstream.modifyCell(cell,0,(short)6);
            wb.write(stream);
            stream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

From source file:gov.nih.nci.cabig.caaers.dataimport.AgentSpecificTermsImporter.java

License:BSD License

public Map<String, Object> importFile() throws Exception {

    POIFSFileSystem poifs;//from  www .ja v a 2 s  .c om
    HSSFWorkbook wb;
    HSSFSheet sh = null;

    boolean isExcel = file.getName().endsWith(".xls");
    boolean isCSV = file.getName().endsWith(".csv");

    Map<String, Object> results = new HashMap<String, Object>();
    int rowCount = 0;
    int columnsCount = 0;
    Map<String, Agent> agents = new HashMap<String, Agent>();
    Map<String, Agent> missingAgents = new HashMap<String, Agent>();
    Set<String> missingTerms = new HashSet<String>();
    Map<String, String> asaelCache = new HashMap<String, String>();
    int asael;

    // wipe out the table
    agentSpecificTermDao.deleteAll();
    studyDao.deleteAllExpectedTerms();
    // if (true) return null;

    // get needed headers
    if (isExcel) {
        poifs = new POIFSFileSystem(new FileInputStream(file));
        wb = new HSSFWorkbook(poifs);
        sh = wb.getSheetAt(0);
        rowCount = sh.getLastRowNum();
        columnsCount = sh.getRow(0).getLastCellNum();

        for (byte i = 0; i < columnsCount; i++) {
            HSSFCell cell = sh.getRow(0).getCell(i);
            if (headers.containsKey(cell.getStringCellValue())) {
                headers.remove(cell.getStringCellValue());
                headers.put(cell.getStringCellValue(), Short.valueOf(i));
            }
        }
    }

    InputStream ir = null;
    Reader r = null;
    BufferedReader br = null;

    if (isCSV) {
        // readLines
        rowCount = 0;
        ir = new FileInputStream(file);
        r = new InputStreamReader(ir);
        br = new BufferedReader(r);
        String s = br.readLine();
        while (s != null) {
            if (rowCount == 0) {
                String[] _s = s.split("[\\|]{1}");
                for (byte j = 0; j < _s.length; j++) {
                    // System.out.println(_s[j]);
                    if (headers.containsKey(_s[j])) {
                        headers.remove(_s[j]);
                        headers.put(_s[j], Short.valueOf(j));
                    }
                }
            }
            rowCount++;
            s = br.readLine();
        }
        br.close();
        r.close();
        ir.close();

        ir = new FileInputStream(file);
        r = new InputStreamReader(ir);
        br = new BufferedReader(r);
    }

    /*
            System.out.println(rowCount);
            for (Map.Entry e : headers.entrySet()) {
    System.out.println(e.getKey() + "=>" + e.getValue());
            }
    */

    agents.clear();
    missingTerms.clear();
    missingAgents.clear();
    asael = 0;
    int duplicateAgentTerms = 0;

    //
    String nsc = "";
    String ctcae_category = "";
    String ctcae_version = "0.0";
    String ae_term = "";
    String other_toxicity = "";

    // Loading ASAE list
    // if (true) {  return null; }

    int i = 1;
    while (i <= rowCount) {

        nsc = "";

        if (isExcel) {
            HSSFRow row = sh.getRow(i);
            if (row != null) {
                nsc = getCellData("", i, row.getCell((short) headers.get("NSC")));
                ctcae_category = getCellData("", i, row.getCell((short) headers.get("CTCAE_CATEGORY")));
                ctcae_version = getCellData("", i, row.getCell((short) headers.get("CTCAE_VERSION")));
                ae_term = getCellData("", i, row.getCell((short) headers.get("AE_TERM")));
                other_toxicity = getCellData("", i, row.getCell((short) headers.get("OTHER_TOXICITY")));
            }
        } else {
            String s;
            s = br.readLine();
            if (s != null) {
                String[] _s = s.split("[\\|]{1}");
                if (i > 1 && _s.length > 1) {
                    nsc = _s[headers.get("NSC")];
                    ctcae_category = _s[headers.get("CTCAE_CATEGORY")];
                    try {
                        ctcae_version = _s[headers.get("CTCAE_VERSION")].trim();
                    } catch (NumberFormatException e) {
                        //                                System.out.println(s);
                        return null;
                    }
                    ae_term = _s[headers.get("AE_TERM")];
                    if (_s.length - 1 >= headers.get("OTHER_TOXICITY"))
                        other_toxicity = _s[headers.get("OTHER_TOXICITY")];
                    else
                        other_toxicity = "";
                }
            }
        }

        if (nsc.trim().equals("")) {
            i++;
            continue;
        } else {
            // System.out.println(String.format("%s). NSC:%s,   V:%s,   C:%s,   T:%s", i, nsc, ctcae_version, ctcae_category, ae_term));
        }

        Agent a = agents.get(nsc);
        if (a == null) {
            a = agentDao.getByNscNumber(nsc);
            // System.out.println(asael + ". OK. Found agent [" + a.getName() + "] for NSC: [" + nsc + "]");
            agents.put(nsc, a);
        }

        if (a != null) {
            AgentSpecificCtcTerm t = new AgentSpecificCtcTerm();
            t.setAgent(a);
            t.setOtherToxicity(other_toxicity);

            List<CtcTerm> list = terminologyRepository.getCtcTerm(ctcae_category, ctcae_version, ae_term);
            if (list.size() == 0) {
                // System.out.println("<ERROR>: Term not found: " + ae_term + ", Category: " + ctcae_category + ", CTCAE Version: " + ctcae_version);
                missingTerms.add("Term not found: " + ae_term + ", Category: " + ctcae_category
                        + ", CTCAE Version: " + ctcae_version);
            } else {
                t.setCtcTerm(list.get(0));
                if (persistASAE(t))
                    asael++;
                else
                    duplicateAgentTerms++;
            }

            agentSpecificTermDao.evict(t);

        } else {
            if (!missingAgents.containsKey(nsc)) {
                // System.out.println("<ERROR>: The agent was not found by its NSC: " + nsc);
                missingAgents.put(nsc, null);
            }
        }

        i++;
    }

    if (isCSV) {
        br.close();
        r.close();
        ir.close();
    }

    results.put(KEY_MISSING_TERMS, missingTerms);
    results.put(KEY_PROCESSED_AGENTS, agents.size() - missingAgents.size());
    results.put(KEY_PROCESSED_AGENTTERMS, asael);
    results.put(KEY_MISSING_AGENTS, missingAgents);
    results.put(KEY_DUPLICATE_AGENT_TERMS, duplicateAgentTerms);

    return results;
}