Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getStringCellValue

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

From source file:Dao.XlsWoDao.java

public ArrayList<WorkItemBean> ReadXLS(File f) {
    Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "READ XLS CALLED");
    FileInputStream fis = null;/*from   w  ww .  jav a 2 s.  com*/
    ArrayList<WorkItemBean> itm = new ArrayList<WorkItemBean>();
    try {
        XSSFRow row = null;
        //  fis = new FileInputStream(new File("D:\\CreateWO_Tmp.xlsx"));
        fis = new FileInputStream(f);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = spreadsheet.iterator();
        int i = 0;
        while (rowIterator.hasNext()) {
            //                System.out.println("NEW ROW");
            i++;
            row = (XSSFRow) rowIterator.next();
            if (i == 1) {
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getColumnIndex() == 0) {
                    }
                }
            }
            if (i > 3) {
                WorkItemBean bean = new WorkItemBean();
                // System.out.println("ROW" + i);
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    //                        System.out.println("NEW COLUMN");
                    Cell cell = cellIterator.next();
                    if (cell.getColumnIndex() >= 0) {
                        //   System.out.print("COLUMN");
                        if (cell.getColumnIndex() == 0) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setITEM_ID(String.valueOf(t));
                            } else {
                                bean.setITEM_ID(cell.getStringCellValue());
                            }

                        } else if (cell.getColumnIndex() == 1) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                bean.setRATE((float) cell.getNumericCellValue());
                            } else {
                                bean.setRATE(Float.parseFloat(cell.getStringCellValue()));
                            }
                        } else if (cell.getColumnIndex() == 2) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setPLANT(String.valueOf(t));
                            } else {
                                bean.setPLANT(cell.getStringCellValue());
                            }
                        } else if (cell.getColumnIndex() == 3) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setPROJ(String.valueOf(t));
                            } else {
                                bean.setPROJ(cell.getStringCellValue());
                            }

                        } else if (cell.getColumnIndex() == 4) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setTASK(String.valueOf(t));
                            } else {
                                bean.setTASK(cell.getStringCellValue());
                            }
                        } else if (cell.getColumnIndex() == 5) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setCMT(String.valueOf(t));
                            } else {
                                bean.setCMT(cell.getStringCellValue());
                            }
                        }
                    }
                }
                // if (bean.getITEM_ID() != null || !"".equals(bean.getITEM_ID())) {
                itm.add(bean);
                // }
            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } catch (IOException ex) {
        Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } finally {
        try {
            fis.close();
        } catch (IOException ex) {
            Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
        }
    }
    return itm;
}

From source file:data.control.dataSheet.java

public ArrayList<Patient> getPatients() {
    ArrayList<XSSFRow> theRows;
    ArrayList<Patient> thePatients = new ArrayList();
    boolean firstRowSkipped = false;

    connect();//from w  w  w. j  a va 2s  .  c  om
    theRows = fetchRows();

    // looping through the rows
    Iterator<XSSFRow> rowIterator = theRows.iterator();

    while (rowIterator.hasNext()) {

        // reading the row
        Row aRow = rowIterator.next();

        if (!firstRowSkipped) {
            firstRowSkipped = true;
            continue;
        }

        Patient aPatient = new Patient();

        // loading the cells
        Iterator<Cell> cellIterator = aRow.cellIterator();

        // looping through the cells
        while (cellIterator.hasNext()) {
            // reading the cell
            Cell cell = cellIterator.next();

            if (cell != null) {
                switch (cell.getColumnIndex()) {

                case 0:
                    // ID
                    aPatient.setID((int) cell.getNumericCellValue());
                    break;
                case 1:
                    // Name
                    aPatient.setName(cell.getStringCellValue());
                    break;
                case 2: // heart rate
                case 3: // heart rate
                case 4: // heart rate
                case 5: // heart rate
                case 6: // heart rate
                    //aPatient.addHeartRate(cell.getNumericCellValue());
                    break;
                case 7: // tempreature
                case 8: // tempreature
                case 9: // tempreature
                case 10:// tempreature
                case 11:// tempreature
                    //aPatient.addTempreature(cell.getNumericCellValue());
                    break;
                case 12:
                    // blood_type
                    aPatient.setBloodType(cell.getStringCellValue());

                    break;
                case 13:
                    // sex
                    aPatient.setSex(cell.getStringCellValue());
                    break;
                case 14:
                    // age
                    aPatient.setAge((int) cell.getNumericCellValue());
                    break;
                case 15:
                    // date_added
                    aPatient.setDateAdded(cell.getDateCellValue());
                    break;
                case 16:
                    // last_updated
                    aPatient.setLastUpdated(cell.getDateCellValue());
                    break;
                case 17:
                    // last_alarmed
                    aPatient.setLastAlarm(cell.getDateCellValue());
                default:
                    break;
                }
            }
        }
        // adding patient to the collection
        if (aPatient.getName() != null) {
            thePatients.add(aPatient);
        }
        //aPatient.printAll();
    }

    //closeConnection();
    return thePatients;
}

From source file:data.services.FreeOptionService.java

public void updateFromXml(File fl) {
    try {//from  w w w .  j a  v a2 s  .  c om
        FileInputStream fi = new FileInputStream(fl);

        int i = 1;
        String listName = "? .";
        int s = 39191091;
        List<FreeOption> foForSave = new ArrayList();
        List<FreeOption> foForUpd = new ArrayList();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(fi);
            int sheetNumber = workbook.getNumberOfSheets();
            while (i < sheetNumber) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                i++;
                listName = sheet.getSheetName();
                Iterator<Row> it = sheet.iterator();
                Car car = new Car();
                s = 0;
                while (it.hasNext()) {
                    Row row = it.next();
                    s++;
                    Cell idCell = row.getCell(0);
                    if (idCell.getCellType() == Cell.CELL_TYPE_STRING) {
                        String nameCell = idCell.getStringCellValue().trim();
                        if (nameCell.equals("CAR_ID")) {
                            row = it.next();
                            s++;
                            Cell carIdCell = row.getCell(0);
                            String carIdstr = StringAdapter.HSSFSellValue(carIdCell);
                            if (carIdstr.contains(".")) {
                                int point = carIdstr.indexOf(".");
                                carIdstr = carIdstr.substring(0, point);
                            }
                            Long carId = StringAdapter.toLong(carIdstr);
                            car = carDao.find(carId);
                            //throw new Exception(" carIdstr="+carIdstr+"; carId="+carId+"; ");
                        } else if (nameCell.equals("OPTION_ID")) {
                            while (it.hasNext()) {
                                row = it.next();
                                s++;
                                Cell optIdCell = row.getCell(0);
                                if (optIdCell != null) {
                                    if (optIdCell.getCellType() == Cell.CELL_TYPE_STRING) {
                                        String optIdstr = optIdCell.getStringCellValue().trim();
                                        if (optIdstr.equals("CAR_ID")) {
                                            /*it.remove();
                                             break;*/
                                            row = it.next();
                                            s++;
                                            Cell carIdCell = row.getCell(0);
                                            String carIdstr = StringAdapter.HSSFSellValue(carIdCell);
                                            if (carIdstr.contains(".")) {
                                                int point = carIdstr.indexOf(".");
                                                carIdstr = carIdstr.substring(0, point);
                                            }
                                            Long carId = StringAdapter.toLong(carIdstr);
                                            car = carDao.find(carId);
                                            it.next();
                                            s++;
                                        } else {
                                            String oIdstr = StringAdapter.HSSFSellValue(optIdCell);
                                            if (oIdstr.contains(".")) {
                                                int point = oIdstr.indexOf(".");
                                                oIdstr = oIdstr.substring(0, point);
                                            }
                                            Long optId = Long.valueOf(oIdstr);
                                            FreeOption fored = freeOptionDao.find(optId);
                                            if (fored != null) {
                                                FreeOption supfo = getOptFromRow(row);
                                                fored.setAudial(supfo.getAudial());
                                                fored.setDescription(supfo.getDescription());
                                                fored.setKinestetic(supfo.getKinestetic());
                                                fored.setParamValue(supfo.getParamValue());
                                                fored.setPercentValue(supfo.getPercentValue());
                                                fored.setPrice(supfo.getPrice());
                                                fored.setRadical(supfo.getRadical());
                                                fored.setTitle(supfo.getTitle());
                                                fored.setType(supfo.getType());
                                                fored.setUid(supfo.getUid());
                                                fored.setVisual(supfo.getVisual());
                                                //throw new Exception("4!");
                                                if (validate(fored)) {
                                                    //freeOptionDao.update(fored);
                                                    foForUpd.add(fored);
                                                }
                                            } else {
                                                FreeOption fo = getOptFromRow(row);
                                                if (car != null) {
                                                    fo.setCar(car);
                                                    if (validate(fo)) {
                                                        foForSave.add(fo);
                                                    }
                                                }
                                                //addError(":  ?    ,     .");
                                            }
                                        }
                                    } else if (optIdCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                        String oIdstr = StringAdapter.HSSFSellValue(optIdCell);
                                        if (oIdstr.contains(".")) {
                                            int point = oIdstr.indexOf(".");
                                            oIdstr = oIdstr.substring(0, point);
                                        }
                                        Long optId = Long.valueOf(oIdstr);
                                        //Long optId = StringAdapter.toLong(StringAdapter.getString(optIdCell.getNumericCellValue()));
                                        FreeOption fored = freeOptionDao.find(optId);
                                        if (fored != null) {
                                            FreeOption supfo = getOptFromRow(row);
                                            fored.setAudial(supfo.getAudial());
                                            fored.setDescription(supfo.getDescription());
                                            fored.setKinestetic(supfo.getKinestetic());
                                            fored.setParamValue(supfo.getParamValue());
                                            fored.setPercentValue(supfo.getPercentValue());
                                            fored.setPrice(supfo.getPrice());
                                            fored.setRadical(supfo.getRadical());
                                            fored.setTitle(supfo.getTitle());
                                            fored.setType(supfo.getType());
                                            fored.setUid(supfo.getUid());
                                            fored.setVisual(supfo.getVisual());
                                            //throw new Exception("3!");
                                            if (validate(fored)) {
                                                //freeOptionDao.update(fored);
                                                foForUpd.add(fored);
                                            }
                                        } else {
                                            FreeOption fo = getOptFromRow(row);
                                            if (car != null) {
                                                fo.setCar(car);
                                                if (validate(fo)) {
                                                    foForSave.add(fo);
                                                }
                                            }
                                        }
                                    } else if (optIdCell.getCellType() == Cell.CELL_TYPE_BLANK) {
                                        if (car != null) {
                                            FreeOption fo = getOptFromRow(row);
                                            fo.setCar(car);
                                            //throw new Exception("2!");
                                            if (validate(fo)) {
                                                foForSave.add(fo);
                                            }
                                        }
                                    }
                                } else {
                                    if (car != null) {
                                        FreeOption fo = getOptFromRow(row);
                                        fo.setCar(car);
                                        //throw new Exception("1! rad="+fo.getRadical()+"; a="+fo.getAudial()+"; v="+fo.getVisual()+"; perc="+fo.getPercentValue()+"; val="+fo.getParamValue()+"; price="+fo.getPrice()+"; ");
                                        if (validate(fo)) {
                                            foForSave.add(fo);
                                        }
                                    }
                                }
                            }
                        }
                    }

                }
            }
            workbook.close();
        } catch (Exception e) {
            addError(": ?:" + i + ", " + listName + ", ?:" + s + ", "
                    + StringAdapter.getStackTraceException(e));
        }
        fi.close();
        for (FreeOption fo : foForSave) {
            freeOptionDao.save(fo);
        }
        for (FreeOption fo : foForUpd) {
            freeOptionDao.update(fo);
        }
    } catch (Exception e) {
        addError("  xml");
        addError(e.getMessage());
    }
}

From source file:DB.TopStockDescriptionList.java

public static List<TopStockDescription> readFromFileExcel(String fileName) {

    List<TopStockDescription> tempTSD = new ArrayList();
    String longName = "";
    String shortName = "";
    String explanation = "";

    try {/* w w  w .  jav a2 s.c  o m*/
        ForcastUi.consoleLog("Opening filename: " + fileName);

        FileInputStream fIP = openExcelFileOrCreate(fileName);
        //Get the workbook instance for XLSX file 
        XSSFWorkbook workbook = new XSSFWorkbook(fIP);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = spreadsheet.iterator();
        XSSFRow row;
        while (rowIterator.hasNext()) {
            row = (XSSFRow) rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            // ?
            Cell cell = cellIterator.next();
            longName = cell.getStringCellValue();
            if (cellIterator.hasNext()) {
                cell = cellIterator.next();
                shortName = cell.getStringCellValue();
                if (shortName.isEmpty())
                    continue;
            } else
                continue;
            if (cellIterator.hasNext()) {
                cell = cellIterator.next();
                explanation = cell.getStringCellValue();
                if (shortName.isEmpty())
                    continue;
            } else
                continue;

            tempTSD.add(new TopStockDescription(shortName.trim(), longName.trim(), explanation.trim(), false));
        }
        fIP.close();

    } catch (FileNotFoundException e) {
        ErrorMessages.printErrorMsg(ErrorMessages.FILENOTFOUND, fileName);
        ForcastUi.consoleLog(e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        ErrorMessages.printErrorMsg(ErrorMessages.FILECOR, fileName);
        ForcastUi.consoleLog(e.getMessage());
        e.printStackTrace();
    } catch (Exception ex) {
        ErrorMessages.printErrorMsg(ErrorMessages.FILECOR, fileName);
        ForcastUi.consoleLog(ex.getMessage());
        Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex);
    }

    return tempTSD;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private void checkStationsFirst(List<Exception> exceptions, Sheet businessSheet) {
    HashSet<String> stationIDs = new HashSet<>();
    int numRows = businessSheet.getLastRowNum() + 1;
    for (int i = 1; i < numRows; i++) {
        Row row = businessSheet.getRow(i);
        if (row != null) {
            Cell cell = row.getCell(0); // ID
            Cell cell1 = row.getCell(1); // Name
            if ((cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
                    && (cell1 == null || cell1.getCellType() == Cell.CELL_TYPE_BLANK))
                return;
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
                exceptions.add(new Exception("Station has no ID -> Row " + (i + 1)));
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String val = cell.getStringCellValue();
            if (stationIDs.contains(val))
                exceptions.add(//  w ww  .ja  v  a2 s. c  om
                        new Exception("Station ID '" + val + "' is defined more than once -> Row " + (i + 1)));
            stationIDs.add(val);
        }
    }
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private void checkDeliveriesFirst(List<Exception> exceptions, Sheet deliverySheet) {
    HashSet<String> deliveryIDs = new HashSet<>();
    int numRows = deliverySheet.getLastRowNum() + 1;
    for (int i = 2; i < numRows; i++) {
        Row row = deliverySheet.getRow(i);
        if (row != null) {
            Cell cell = row.getCell(0); // ID
            Cell cell1 = row.getCell(1); // Station
            if ((cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
                    && (cell1 == null || cell1.getCellType() == Cell.CELL_TYPE_BLANK))
                return;
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
                exceptions.add(new Exception("Delivery has no ID -> Row " + (i + 1)));
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String val = cell.getStringCellValue();
            if (deliveryIDs.contains(val))
                exceptions.add(/*  www. ja va  2  s  .  com*/
                        new Exception("Delivery ID '" + val + "' is defined more than once -> Row " + (i + 1)));
            deliveryIDs.add(val);
        }
    }
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private void checkTraceDeliveries(List<Exception> exceptions, Sheet deliverySheet,
        int borderRowBetweenTopAndBottom, boolean isForTracing, boolean isNewFormat_151105) {
    HashMap<String, HashSet<Row>> deliveryIDs = new HashMap<>();
    int numRows = deliverySheet.getLastRowNum() + 1;
    for (int i = 2; i < numRows; i++) {
        Row row = deliverySheet.getRow(i);
        if (row != null) {
            Cell cellM = row.getCell(12); // DeliveryID in DB
            //if ((cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) && (cell1 == null || cell1.getCellType() == Cell.CELL_TYPE_BLANK)) return;
            if (isCellEmpty(cellM)) {
                //exceptions.add(new Exception("Delivery has no ID -> Row " + (i+1)));
            } else {
                cellM.setCellType(Cell.CELL_TYPE_STRING);
                String val = cellM.getStringCellValue().trim();
                if (val.isEmpty() || val.equals("DeliveryID in DB")) {

                } else {
                    if (!deliveryIDs.containsKey(val))
                        deliveryIDs.put(val, new HashSet<Row>());
                    HashSet<Row> hs = deliveryIDs.get(val);
                    hs.add(row);//from  ww  w .j  a v  a2 s .  c  o  m
                }
            }

            /*
            String key = getRowKey(row, borderRowBetweenTopAndBottom, isForTracing);
               if (!duplicateRows.containsKey(key)) duplicateRows.put(key, new HashSet<Row>());
               HashSet<Row> hs = duplicateRows.get(key);
               hs.add(row);
               */
        }
    }
    for (String val : deliveryIDs.keySet()) {
        HashSet<Row> hs = deliveryIDs.get(val);
        if (hs.size() > 1) {
            String rows = "", key = null;
            boolean different = false;
            for (Row tmp : hs) {
                if (isNewFormat_151105 || tmp.getRowNum() < borderRowBetweenTopAndBottom) {
                    String tkey = getRowKey(tmp, borderRowBetweenTopAndBottom, isForTracing);
                    if (key == null)
                        key = tkey;
                    else if (!key.equals(tkey))
                        different = true;
                    rows += ";" + (tmp.getRowNum() + 1);
                }
            }
            if (different)
                exceptions.add(new Exception("Delivery ID '" + val + "' is defined more than once -> Rows: "
                        + rows.substring(1)
                        + ". If you have copy/pasted a new row, please clear the cell for the DeliveryID of the new Row in Column 'M' (expand it firstly to be able to see it)."));
        }
    }
    /*
    for (String val : duplicateRows.keySet()) {
       HashSet<Integer> hs = duplicateRows.get(val);
       if (hs.size() > 1) {
    String rows = "";
    for (Integer tmp : hs) {
       rows += ";" + tmp; 
    }
    exceptions.add(new Exception("Rows are identical, but Delivery IDs are different -> Rows: " + rows.substring(1) + ". You may want to give them the same ID in Column 'M'."));
       }
    }
    */
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private String getRowKey(Row row, int borderRowBetweenTopAndBottom, boolean isForTracing) {
    boolean isProductsOut = row.getRowNum() < borderRowBetweenTopAndBottom && !isForTracing
            || isForTracing && row.getRowNum() > borderRowBetweenTopAndBottom;
    String key = "";
    for (int j = isProductsOut ? 0 : 1; j < row.getLastCellNum(); j++) { // Start with Lot Number or after
        Cell cell = row.getCell(j);
        if (!isCellEmpty(cell)) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            key += cell.getStringCellValue().trim();
        }/*w ww  .j av a2s .  com*/
        key += ";";
    }
    while (key.endsWith(";;")) {
        key = key.substring(0, key.length() - 1);
    }
    return key;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private void loadLookupSheet(Sheet lookupSheet) {
    LookUp lu = new LookUp();
    int numRows = lookupSheet.getLastRowNum() + 1;
    for (int i = 1; i < numRows; i++) {
        Row row = lookupSheet.getRow(i);
        if (row != null) {
            Cell cell = row.getCell(0); // Sampling
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                lu.addSampling(cell.getStringCellValue());
            }//from  www . j a  v a  2s.c  o  m
            cell = row.getCell(1); // TypeOfBusiness
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                lu.addTypeOfBusiness(cell.getStringCellValue());
            }
            cell = row.getCell(2); // Treatment
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                lu.addTreatment(cell.getStringCellValue());
            }
            cell = row.getCell(3); // Units
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                lu.addUnit(cell.getStringCellValue());
            }
        }
    }
    lu.intoDb(mydbi);
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private List<Exception> doTheImport(Workbook wb, String filename) { //  throws Exception
    List<Exception> exceptions = new ArrayList<>();

    Sheet stationSheet = wb.getSheet("Stations");
    Sheet deliverySheet = wb.getSheet("Deliveries");
    Sheet d2dSheet = wb.getSheet("Deliveries2Deliveries");
    Sheet transactionSheet = wb.getSheet("BackTracing");
    Sheet lookupSheet = wb.getSheet("LookUp");
    Sheet forwardSheet = wb.getSheet("Opt_ForwardTracing");
    Sheet forwardSheetNew = wb.getSheet("ForwardTracing_Opt");
    Sheet forSheet = wb.getSheet("ForTracing");
    Sheet fwdSheet = wb.getSheet("FwdTracing");
    if (forSheet == null)
        forSheet = fwdSheet;// w w w  .ja  va2s.  c o m

    boolean isForTracing = forSheet != null;
    if (isForTracing)
        transactionSheet = forSheet;

    if (stationSheet == null || transactionSheet == null && deliverySheet == null) {
        exceptions.add(new Exception("Wrong template format!"));
        return exceptions;
    }

    checkStationsFirst(exceptions, stationSheet);

    if (deliverySheet != null) {
        checkDeliveriesFirst(exceptions, deliverySheet);
        // load all Stations
        HashMap<String, Station> stations = new HashMap<>();
        int numRows = stationSheet.getLastRowNum() + 1;
        Row titleRow = stationSheet.getRow(0);
        for (classRowIndex = 1; classRowIndex < numRows; classRowIndex++) {
            Station s = getStation(titleRow, stationSheet.getRow(classRowIndex));
            if (s == null)
                break;
            if (stations.containsKey(s.getId()))
                exceptions.add(new Exception("Station defined twice -> Row " + (classRowIndex + 1)
                        + "; Station Id: '" + s.getId() + "'"));
            stations.put(s.getId(), s);
        }
        // load all Deliveries
        HashMap<String, Delivery> deliveries = new HashMap<>();
        numRows = deliverySheet.getLastRowNum() + 1;
        titleRow = deliverySheet.getRow(0);
        HashMap<String, String> definedLots = new HashMap<>();
        HashMap<String, Integer> deliveryRows = new HashMap<>();
        for (classRowIndex = 2; classRowIndex < numRows; classRowIndex++) {
            Delivery d = getMultiOutDelivery(exceptions, stations, titleRow,
                    deliverySheet.getRow(classRowIndex), definedLots, classRowIndex, filename,
                    d2dSheet != null);
            if (d == null)
                break;
            if (deliveries.containsKey(d.getId()))
                exceptions.add(new Exception("Delivery defined twice -> in Row " + (classRowIndex + 1)
                        + " and in Row " + deliveryRows.get(d.getId()) + "; Delivery Id: '" + d.getId() + "'"));
            else
                deliveryRows.put(d.getId(), classRowIndex + 1);
            deliveries.put(d.getId(), d);
        }

        // load Recipes
        HashSet<D2D> recipes = new HashSet<>();
        if (d2dSheet != null) {
            numRows = d2dSheet.getLastRowNum() + 1;
            titleRow = d2dSheet.getRow(0);
            for (classRowIndex = 1; classRowIndex < numRows; classRowIndex++) {
                D2D dl = getD2D(exceptions, deliveries, titleRow, d2dSheet.getRow(classRowIndex),
                        classRowIndex);
                if (dl == null)
                    break;
                recipes.add(dl);
            }
        }

        MetaInfo mi = new MetaInfo();
        mi.setFilename(filename);

        if (lookupSheet != null)
            loadLookupSheet(lookupSheet);
        Integer miDbId = null;
        try {
            miDbId = mi.getID(mydbi);
        } catch (Exception e) {
            exceptions.add(e);
        }
        if (miDbId == null)
            exceptions.add(new Exception("File already imported"));
        for (Delivery d : deliveries.values()) {
            try {
                d.getID(miDbId, false, mydbi);
            } catch (Exception e) {
                exceptions.add(e);
            }
            //if (!d.getLogMessages().isEmpty()) logMessages += d.getLogMessages() + "\n";
            if (d.getExceptions().size() > 0)
                exceptions.addAll(d.getExceptions());

        }

        HashMap<Delivery, HashSet<Integer>> ingredients = new HashMap<>();
        for (D2D dl : recipes) {
            try {
                dl.getId(miDbId, mydbi);
            } catch (Exception e) {
                exceptions.add(e);
            }

            // collect data for checks if data is missing...
            Delivery d = dl.getTargetDelivery();
            if (!ingredients.containsKey(d))
                ingredients.put(d, new HashSet<Integer>());
            HashSet<Integer> hd = ingredients.get(d);
            if (dl.getIngredient() != null)
                hd.add(dl.getIngredient().getDbId());
        }

        return exceptions;
    }
    int borderRowLotStart = 0;

    Row row = transactionSheet.getRow(0);
    Row titleRow;
    Cell cell;
    HashMap<String, Delivery> outDeliveries = new HashMap<>();
    HashMap<String, Lot> outLots = new HashMap<>();
    Station sif;
    MetaInfo mi;

    boolean isNewFormat_151105 = false;
    if (forwardSheet != null) {
        // Station in focus
        cell = row.getCell(1);
        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
            exceptions.add(new Exception("Station in Focus not defined"));
        cell.setCellType(Cell.CELL_TYPE_STRING);
        sif = getStation(exceptions, stationSheet, cell.getStringCellValue(), row);

        // Delivery(s) Outbound
        classRowIndex = 5;
        titleRow = transactionSheet.getRow(classRowIndex - 2);
        for (;; classRowIndex++) {
            row = transactionSheet.getRow(classRowIndex);
            if (row == null)
                continue;
            if (isBlockEnd(row, 13, "Reporter Information"))
                break;
            Delivery d = getDelivery(exceptions, stationSheet, sif, row, true, titleRow, filename, false, null,
                    outDeliveries, false, isNewFormat_151105);
            if (d == null)
                continue;
            outDeliveries.put(d.getId(), d);
            outLots.put(d.getLot().getNumber(), d.getLot());
        }

        // Metadata on Reporter
        classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, "Reporter Information") + 2;
        row = transactionSheet.getRow(classRowIndex);
        mi = getMetaInfo(exceptions, row, transactionSheet.getRow(classRowIndex - 1));
        mi.setFilename(filename);
    } else { // Reporter shifted to the top
        // Metadata on Reporter
        classRowIndex = getNextBlockRowIndex(transactionSheet, 0, "Reporter Information") + 2;
        row = transactionSheet.getRow(classRowIndex);
        mi = getMetaInfo(exceptions, row, transactionSheet.getRow(classRowIndex - 1));
        mi.setFilename(filename);

        // Station in focus
        classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, "Station in Focus:");
        row = transactionSheet.getRow(classRowIndex);
        cell = row.getCell(1);
        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
            exceptions.add(new Exception("Station in Focus not defined"));
        cell.setCellType(Cell.CELL_TYPE_STRING);
        sif = getStation(exceptions, stationSheet, cell.getStringCellValue(), row);

        String label = "Products Out";
        if (isForTracing)
            label = "Ingredients In for Lot(s)";
        // Delivery(s) Outbound
        classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, label) + 3;
        titleRow = transactionSheet.getRow(classRowIndex - 2);
        cell = titleRow.getCell(0);
        isNewFormat_151105 = cell.getStringCellValue().equals("Product Lot Number");
        for (;; classRowIndex++) {
            row = transactionSheet.getRow(classRowIndex);
            if (row == null)
                continue;
            if (isBlockEnd(row, 13, "Lot Information"))
                break;
            Delivery d = getDelivery(exceptions, stationSheet, sif, row, !isForTracing, titleRow, filename,
                    isForTracing, outLots, outDeliveries, false, isNewFormat_151105);
            if (d == null)
                continue;
            outDeliveries.put(d.getId(), d);
            if (!isForTracing)
                outLots.put(d.getLot().getNumber(), d.getLot());
        }
    }

    String label = "Ingredients In for Lot(s)";
    if (isForTracing)
        label = "Products Out";
    // Lot(s)
    classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, "Lot Information") + 3;
    borderRowLotStart = classRowIndex;
    titleRow = transactionSheet.getRow(classRowIndex - 2);
    for (;; classRowIndex++) {
        row = transactionSheet.getRow(classRowIndex);
        if (row == null)
            continue;
        if (isBlockEnd(row, 13, label))
            break;
        if (!fillLot(exceptions, row, sif, outLots, titleRow, isForTracing ? outDeliveries : null,
                classRowIndex + 1, isNewFormat_151105)) {
            exceptions.add(new Exception("Lot number unknown in Row number " + (classRowIndex + 1)));
        }
    }

    checkTraceDeliveries(exceptions, transactionSheet, borderRowLotStart, isForTracing, isNewFormat_151105);

    // Deliveries/Recipe Inbound
    boolean hasIngredients = false;
    label = "Ingredients for Lot(s)";
    if (isForTracing)
        label = "Products Out";
    classRowIndex = getNextBlockRowIndex(transactionSheet, classRowIndex, label) + 3;
    HashMap<String, Delivery> inDeliveries = new HashMap<>();
    int numRows = transactionSheet.getLastRowNum() + 1;
    titleRow = transactionSheet.getRow(classRowIndex - 2);
    for (; classRowIndex < numRows; classRowIndex++) {
        row = transactionSheet.getRow(classRowIndex);
        if (row == null)
            continue;
        if (isBlockEnd(row, 13, null))
            break;
        Delivery d = getDelivery(exceptions, stationSheet, sif, row, isForTracing, titleRow, filename,
                isForTracing, outLots, inDeliveries, false, isNewFormat_151105);
        if (d == null)
            continue;
        if (!isForTracing && d.getTargetLotIds().size() == 0)
            exceptions.add(new Exception("Lot number unknown in Row number " + (classRowIndex + 1)));
        inDeliveries.put(d.getId(), d);
        hasIngredients = true;
    }
    if (!hasIngredients) {
        warns.put("No " + (isForTracing ? "Products Out" : "ingredients") + " defined...", null);
    }

    // Opt_ForwardTracing
    HashSet<Delivery> forwDeliveries = new HashSet<>();
    if (!isForTracing) {
        if (forwardSheet == null)
            forwardSheet = forwardSheetNew;
        numRows = forwardSheet.getLastRowNum() + 1;
        titleRow = forwardSheet.getRow(0);
        for (classRowIndex = 2; classRowIndex < numRows; classRowIndex++) {
            row = transactionSheet.getRow(classRowIndex);
            if (row == null)
                continue;
            Delivery d = getForwardDelivery(exceptions, stationSheet, outLots, titleRow,
                    forwardSheet.getRow(classRowIndex), isNewFormat_151105);
            if (d == null)
                continue;
            forwDeliveries.add(d);
        }
    }

    if (lookupSheet != null)
        loadLookupSheet(lookupSheet);
    Integer miDbId = null;
    try {
        miDbId = mi.getID(mydbi);
    } catch (Exception e) {
        exceptions.add(e);
    }
    if (miDbId == null)
        exceptions.add(new Exception("File already imported"));
    if (isForTracing)
        try {
            insertForIntoDb(exceptions, miDbId, inDeliveries, outDeliveries);
        } catch (Exception e) {
            exceptions.add(e);
        }
    else
        try {
            insertIntoDb(exceptions, miDbId, inDeliveries, outDeliveries, forwDeliveries);
        } catch (Exception e) {
            exceptions.add(e);
        }

    return exceptions;
}