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:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

private int[] doImportErlenbacherFW(HSSFWorkbook wb, JProgressBar progress, String efsaID) {
    int numSuccess = 0;
    int numFails = 0;
    HSSFSheet businessSheet = wb.getSheet("Business_List");
    HSSFSheet transactionSheet = wb.getSheet("Receivers");
    if (transactionSheet != null) {
        int numRows = transactionSheet.getLastRowNum() + 1;
        progress.setMaximum(numRows);//from  www . ja v a  2  s.  c o  m
        progress.setValue(0);
        HSSFRow busRow = getRow(businessSheet, efsaID, 0);
        String idLST = getStrVal(busRow.getCell(0));
        String nameLST = getStrVal(busRow.getCell(1));
        String streetLST = getStrVal(busRow.getCell(2));
        String streetNoLST = getStrVal(busRow.getCell(3), 10);
        String zipLST = getStrVal(busRow.getCell(4), 10);
        String cityLST = getStrVal(busRow.getCell(5));
        String countyLST = getStrVal(busRow.getCell(6), 30);
        String countryLST = getStrVal(busRow.getCell(7));
        String vatLST = getStrVal(busRow.getCell(8));
        for (int i = 2; i < numRows; i++) {
            HSSFRow row = transactionSheet.getRow(i);
            if (row != null) {
                String product = getStrVal(row.getCell(2));
                String DeliveryDate_Day = getStrVal(row.getCell(14));
                String DeliveryDate_Month = getStrVal(row.getCell(15));
                String DeliveryDate_Year = getStrVal(row.getCell(16));
                String Amount = getStrVal(row.getCell(12));
                String Lot_Invoice = getStrVal(row.getCell(4));

                String id = null;
                String name = getStrVal(row.getCell(5)); //
                String street = null;
                String streetNo = null;
                String zip = null;
                String city = getStrVal(row.getCell(6)); //
                String county = null;
                String country = getStrVal(row.getCell(7)); // 
                String vat = getStrVal(row.getCell(8)); //
                getCharge_Lieferung(idLST, nameLST, streetLST, streetNoLST, zipLST, cityLST, countyLST,
                        countryLST, null, vatLST, product, null, null, Lot_Invoice, null, null, null, null,
                        null, null, null, DeliveryDate_Day, DeliveryDate_Month, DeliveryDate_Year, Amount, null,
                        null, id, name, street, streetNo, zip, city, county, country, null, vat,
                        "Erlenbacher" + efsaID + "_" + (i + 1), null, null, null, null, null);
            }
        }
    }
    return new int[] { numSuccess, numFails };
}

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

License:Open Source License

private int[] doImportMaciel(HSSFWorkbook wb, JProgressBar progress, String efsaID) {
    int numSuccess = 0;
    int numFails = 0;
    HSSFSheet businessSheet = wb.getSheet("Business_List");
    HSSFSheet transactionSheet = wb.getSheet("Receivers");
    if (transactionSheet != null) {
        int numRows = transactionSheet.getLastRowNum() + 1;
        progress.setMaximum(numRows);/* w w  w  .  j a v a2  s . c o  m*/
        progress.setValue(0);
        HSSFRow busRow = getRow(businessSheet, efsaID, 0);
        String idLST = getStrVal(busRow.getCell(0));
        String nameLST = getStrVal(busRow.getCell(1));
        String streetLST = getStrVal(busRow.getCell(2));
        String streetNoLST = getStrVal(busRow.getCell(3), 10);
        String zipLST = getStrVal(busRow.getCell(4), 10);
        String cityLST = getStrVal(busRow.getCell(5));
        String countyLST = getStrVal(busRow.getCell(6), 30);
        String countryLST = getStrVal(busRow.getCell(7));
        String vatLST = getStrVal(busRow.getCell(8));
        for (int i = 2; i < numRows; i++) {
            HSSFRow row = transactionSheet.getRow(i);
            if (row != null) {
                String addressOther = getStrVal(row.getCell(7));
                String product = getStrVal(row.getCell(8));
                String DeliveryDate_Day = getStrVal(row.getCell(9));
                String DeliveryDate_Month = getStrVal(row.getCell(10));
                String DeliveryDate_Year = getStrVal(row.getCell(11));
                String Amount = getStrVal(row.getCell(12));
                String Lot_Invoice = getStrVal(row.getCell(13));
                busRow = getRow(businessSheet, addressOther, 9);
                if (busRow != null) {
                    String id = getStrVal(busRow.getCell(0)); //
                    String name = getStrVal(busRow.getCell(1)); //
                    String street = getStrVal(busRow.getCell(2)); //
                    String streetNo = getStrVal(busRow.getCell(3), 10); //
                    String zip = getStrVal(busRow.getCell(4), 10); //
                    String city = getStrVal(busRow.getCell(5)); //
                    String county = getStrVal(busRow.getCell(6), 30);
                    String country = getStrVal(busRow.getCell(7)); // 
                    String vat = getStrVal(busRow.getCell(8)); //
                    getCharge_Lieferung(idLST, nameLST, streetLST, streetNoLST, zipLST, cityLST, countyLST,
                            countryLST, null, vatLST, product, null, null, Lot_Invoice, null, null, null, null,
                            null, null, null, DeliveryDate_Day, DeliveryDate_Month, DeliveryDate_Year, Amount,
                            null, null, id, name, street, streetNo, zip, city, county, country, null, vat,
                            "LSTZAKNoris" + efsaID + "_" + (i + 1), null, null, null, null, null);
                } else if (addressOther != null) {
                    System.err.println("busRow = null... addressOther: " + addressOther + "\tRow: " + (i + 1));
                }
            }
        }
    }

    transactionSheet = wb.getSheet("Suppliers");
    if (transactionSheet != null) {
        int numRows = transactionSheet.getLastRowNum() + 1;
        progress.setMaximum(numRows);
        progress.setValue(0);
        HSSFRow busRow = getRow(businessSheet, efsaID, 0);
        String idLST = getStrVal(busRow.getCell(0));
        String nameLST = getStrVal(busRow.getCell(1));
        String streetLST = getStrVal(busRow.getCell(2));
        String streetNoLST = getStrVal(busRow.getCell(3), 10);
        String zipLST = getStrVal(busRow.getCell(4), 10);
        String cityLST = getStrVal(busRow.getCell(5));
        String countyLST = getStrVal(busRow.getCell(6), 30);
        String countryLST = getStrVal(busRow.getCell(7));
        String vatLST = getStrVal(busRow.getCell(8));
        for (int i = 2; i < numRows; i++) {
            HSSFRow row = transactionSheet.getRow(i);
            if (row != null) {
                String addressOther = getStrVal(row.getCell(7));
                String product = getStrVal(row.getCell(8));
                String DeliveryDate_Day = getStrVal(row.getCell(9));
                String DeliveryDate_Month = getStrVal(row.getCell(10));
                String DeliveryDate_Year = getStrVal(row.getCell(11));
                String Amount = getStrVal(row.getCell(12));
                String Lot_Invoice = getStrVal(row.getCell(13));
                busRow = getRow(businessSheet, addressOther, 9);
                if (busRow != null) {
                    String id = getStrVal(busRow.getCell(0)); //
                    String name = getStrVal(busRow.getCell(1)); //
                    String street = getStrVal(busRow.getCell(2)); //
                    String streetNo = getStrVal(busRow.getCell(3), 10); //
                    String zip = getStrVal(busRow.getCell(4), 10); //
                    String city = getStrVal(busRow.getCell(5)); //
                    String county = getStrVal(busRow.getCell(6), 30);
                    String country = getStrVal(busRow.getCell(7)); // 
                    String vat = getStrVal(busRow.getCell(8)); //
                    getCharge_Lieferung(id, name, street, streetNo, zip, city, county, country, null, vat,
                            product, null, null, Lot_Invoice, null, null, null, null, null, null, null,
                            DeliveryDate_Day, DeliveryDate_Month, DeliveryDate_Year, Amount, null, null, idLST,
                            nameLST, streetLST, streetNoLST, zipLST, cityLST, countyLST, countryLST, null,
                            vatLST, "LSTZAKNoris" + efsaID + "_Sup_" + (i + 1), null, null, null, null, null);
                } else if (addressOther != null) {
                    System.err.println(
                            "suppliers busRow = null... addressOther: " + addressOther + "\tRow: " + (i + 1));
                }
            }
        }
    }
    return new int[] { numSuccess, numFails };
}

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

License:Open Source License

private int[] doImportGaia(HSSFWorkbook wb, JProgressBar progress) {
    int numSuccess = 0;
    int numFails = 0;
    HSSFSheet transactionSheet = wb.getSheet("Transactions");
    HSSFSheet businessSheet = wb.getSheet("Business_List");
    int numRows = transactionSheet.getLastRowNum() + 1;
    progress.setMaximum(numRows);//from  ww  w . j a v  a  2s . c o m
    progress.setValue(0);
    for (int i = 1; i < numRows; i++) {
        HSSFRow row = transactionSheet.getRow(i);
        if (row != null) {
            String idRec = getStrVal(row.getCell(0));
            String adressRec = getStrVal(row.getCell(1));
            String countryRec = getStrVal(row.getCell(2));
            String nameRec = adressRec;
            String streetRec = null;
            String streetNoRec = null;
            String zipRec = null;
            String cityRec = null;
            String countyRec = null;
            String vatRec = null;
            HSSFRow busRow = getRow(businessSheet, idRec, 0);
            if (busRow != null) {
                nameRec = getStrVal(busRow.getCell(1)); //
                streetRec = getStrVal(busRow.getCell(2)); //
                streetNoRec = getStrVal(busRow.getCell(3), 10); //
                zipRec = getStrVal(busRow.getCell(4), 10); //
                cityRec = getStrVal(busRow.getCell(5)); //
                countyRec = getStrVal(busRow.getCell(6), 30);
                countryRec = getStrVal(busRow.getCell(7)); // 
                vatRec = getStrVal(busRow.getCell(8)); //
                if (!adressRec.startsWith(nameRec)) {
                    //System.err.println("Id issue on recs... " + nameRec + " <> " + adressRec);
                }
            } else if (idRec != null) {
                System.err.println("business not there??? Row: " + (i + 1) + "\tidReceived: " + idRec);
            } else {
                System.err.println("idRec is null??? Row: " + (i + 1) + "\t" + nameRec
                        + (nameRec != null ? "" : " -> Station not defined"));
            }

            String prodName = getStrVal(row.getCell(3));
            String type = getStrVal(row.getCell(4));
            if (type != null)
                prodName += "(" + type + ")";

            String idSup = getStrVal(row.getCell(5));
            String adressSup = getStrVal(row.getCell(6));
            String countrySup = getStrVal(row.getCell(7));
            String nameSup = adressSup;
            String streetSup = null;
            String streetNoSup = null;
            String zipSup = null;
            String citySup = null;
            String countySup = null;
            String vatSup = null;
            busRow = getRow(businessSheet, idSup, 0);
            if (busRow != null) {
                nameSup = getStrVal(busRow.getCell(1)); //
                streetSup = getStrVal(busRow.getCell(2)); //
                streetNoSup = getStrVal(busRow.getCell(3), 10); //
                zipSup = getStrVal(busRow.getCell(4), 10); //
                citySup = getStrVal(busRow.getCell(5)); //
                countySup = getStrVal(busRow.getCell(6), 30);
                countrySup = getStrVal(busRow.getCell(7)); // 
                vatSup = getStrVal(busRow.getCell(8)); //
                if (!adressSup.startsWith(nameSup)) {
                    //System.err.println("Id issue on sups... Row: " + (i+1) + "\t" + nameSup + " <> " + adressSup);
                }
            } else if (idSup != null) {
                System.err.println("business not there??? Row: " + (i + 1) + "\tidSupplier: " + idSup);
            } else {
                System.err.println("idSup is null??? Row: " + (i + 1) + "\t" + nameSup
                        + (nameSup != null ? "" : " -> Station not defined"));
            }

            String anno = getStrVal(row.getCell(8)); // anno
            String sitoweb = getStrVal(row.getCell(9)); // Sito Web
            String oc = getStrVal(row.getCell(10)); // OriginCountry
            String cqr = (anno == null ? "" : anno) + (sitoweb == null ? "" : "\t" + sitoweb);

            String serial = "Gaia_" + (i + 1);
            Integer c1 = null;
            if (nameSup != null && !nameSup.trim().isEmpty()) {
                Integer[] c = getCharge_Lieferung(idSup, nameSup.trim(), streetSup, streetNoSup, zipSup,
                        citySup, countySup, countrySup, null, vatSup, prodName, null, null, null, null, null,
                        null, null, null, null, oc, null, null, null, null, null, null, idRec, nameRec.trim(),
                        streetRec, streetNoRec, zipRec, cityRec, countyRec, countryRec, null, vatRec, serial,
                        cqr, null, null, null, null);
                if (c != null)
                    c1 = c[2];
            }
            if (c1 == null) {
                System.err.println("Fehlerchenchen_1!! Row: " + (i + 1));
                numFails++;
            }
        }
    }
    return new int[] { numSuccess, numFails };
}

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

License:Open Source License

private boolean isNew(HSSFRow row) {
    if (row != null) {
        System.err.println(getStrVal(row.getCell(26)));
        String Y0 = getStrVal(row.getCell(27));
        if (Y0 == null || Y0.isEmpty())
            return true;
        String A0 = getStrVal(row.getCell(0));
        return A0.equals("BackwardSerial");
    }/*from w  ww .  j  a va  2s. co  m*/
    return false;
}

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

License:Open Source License

private boolean isBVL(HSSFRow row) {
    String A0 = getStrVal(row.getCell(0));
    return A0.equals("Beispieleintrag");
}

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

License:Open Source License

private boolean isSimple(HSSFRow row) {
    if (row != null) {
        System.err.println(getStrVal(row.getCell(23)));
        String Y0 = getStrVal(row.getCell(24));
        if (Y0 == null || Y0.isEmpty())
            return true;
    }//from   www.j a v a  2  s.c o  m
    return false;
}

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

License:Open Source License

private int[] doImportStandard(HSSFWorkbook wb, JProgressBar progress, String filename) {
    int numSuccess = 0;
    int numFails = 0;
    HSSFSheet transactionSheet = wb.getSheet("Transactions");

    boolean isSimpleFormat = transactionSheet != null && isSimple(transactionSheet.getRow(0));
    boolean isNewFormat = transactionSheet == null && wb.getSheet("NewTransactions") != null
            || transactionSheet != null && isNew(transactionSheet.getRow(0));

    if (isNewFormat && !isSimpleFormat) {
        doImportNewFormat(wb, progress);
    } else {/*from www. ja  v a2  s  . c  o  m*/
        boolean isBvl = isBVL(transactionSheet.getRow(0));
        HSSFSheet businessSheet = wb.getSheet("Business_List");
        int numRows = transactionSheet.getLastRowNum() + 1;
        progress.setMaximum(numRows);
        progress.setValue(0);
        for (int i = isBvl ? 6 : 1; i < numRows; i++) {
            HSSFRow row = transactionSheet.getRow(i);
            if (row != null) {
                String serial = getStrVal(row.getCell(0)); // Serial_number
                String BL0 = getStrVal(row.getCell(1)); // Contact_Region
                String KP = getStrVal(row.getCell(2)); // Contact_person

                String idRec = getStrVal(row.getCell(3)); // ID_Address
                String adressRec = getStrVal(row.getCell(4)); // Address
                if ((serial == null || serial.trim().isEmpty())
                        && (adressRec == null || adressRec.trim().isEmpty())) {
                    continue;//break;
                }
                if (serials.containsKey(serial)) {
                    String msg = "Row: " + (i + 1) + "\tSerial '" + serial + "' already defined in file '"
                            + serials.get(serial) + "' -> not importing this row!";
                    System.err.println(msg);
                    logMessages += msg + "\n";
                    continue;
                }
                serials.put(serial, filename);
                String activityRec = getStrVal(row.getCell(5)); // Activity                  
                String nameRec = adressRec;
                String streetRec = null;
                String streetNoRec = null;
                String zipRec = null;
                String cityRec = null;
                String countyRec = null;
                String countryRec = null;
                String vatRec = null;
                HSSFRow busRow = getRow(businessSheet, idRec, 0);
                if (busRow != null) {
                    nameRec = getStrVal(busRow.getCell(1)); //
                    streetRec = getStrVal(busRow.getCell(2)); //
                    streetNoRec = getStrVal(busRow.getCell(3), 10); //
                    zipRec = getStrVal(busRow.getCell(4), 10); //
                    cityRec = getStrVal(busRow.getCell(5)); //
                    countyRec = getStrVal(busRow.getCell(6), 30);
                    countryRec = getStrVal(busRow.getCell(7)); // 
                    vatRec = getStrVal(busRow.getCell(8)); //
                    if (!adressRec.toUpperCase().startsWith(nameRec.toUpperCase())) {
                        String msg = "Row: " + (i + 1) + "\tId issue on recs...\t" + nameRec + " <> "
                                + adressRec;
                        System.err.println(msg);
                        logMessages += msg + "\n";
                    }
                } else if (idRec != null) {
                    String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidReceived: " + idRec;
                    System.err.println(msg);
                    logMessages += msg + "\n";
                } else {
                    String msg = "Row: " + (i + 1) + "\tidRec is null???\t" + adressRec
                            + (adressRec != null ? "" : " -> Station not defined");
                    System.err.println(msg);
                    logMessages += msg + "\n";
                }

                String prodNameOut = getStrVal(row.getCell(6)); // ProductName
                String prodNumOut = getStrVal(row.getCell(7)); // ProductNo
                String dayOut = getStrVal(row.getCell(8)); // Day
                String monthOut = getStrVal(row.getCell(9)); // Month
                String yearOut = getStrVal(row.getCell(10)); // Year
                String amountKG_Out = getStrVal(row.getCell(11)); // amountKG
                String typePUOut = getStrVal(row.getCell(12)); // typePU
                String numPUOut = getStrVal(row.getCell(13)); // numPU
                String lotNo_Out = getStrVal(row.getCell(14)); // 
                String dayMHDOut = getStrVal(row.getCell(15));
                String monthMHDOut = getStrVal(row.getCell(16));
                String yearMHDOut = getStrVal(row.getCell(17)); // 
                String dayPDOut = getStrVal(row.getCell(18));
                String monthPDOut = getStrVal(row.getCell(19));
                String yearPDOut = getStrVal(row.getCell(20));
                //Date dateOut = getDate(dayOut, monthOut, yearOut);
                //Date dateMHDOut = getDate(dayMHDOut, monthMHDOut, yearMHDOut);
                //Date datePDOut = getDate(dayPDOut, monthPDOut, yearPDOut);

                String idInsp = getStrVal(row.getCell(21)); // ID_Address
                String adressInsp = getStrVal(row.getCell(22)); // Address
                String activityInsp = getStrVal(row.getCell(23)); // Activity
                String nameInsp = adressInsp;
                String streetInsp = null;
                String streetNoInsp = null;
                String zipInsp = null;
                String cityInsp = null;
                String countyInsp = null;
                String countryInsp = null;
                String vatInsp = null;
                busRow = getRow(businessSheet, idInsp, 0);
                if (busRow != null) {
                    nameInsp = getStrVal(busRow.getCell(1)); //
                    streetInsp = getStrVal(busRow.getCell(2)); //
                    streetNoInsp = getStrVal(busRow.getCell(3), 10); //
                    zipInsp = getStrVal(busRow.getCell(4), 10); //
                    cityInsp = getStrVal(busRow.getCell(5)); //
                    countyInsp = getStrVal(busRow.getCell(6), 30);
                    countryInsp = getStrVal(busRow.getCell(7)); // 
                    vatInsp = getStrVal(busRow.getCell(8)); //
                    if (!adressInsp.toUpperCase().startsWith(nameInsp.toUpperCase())) {
                        String msg = "Row: " + (i + 1) + "\tId issue on insps...\t" + nameInsp + " <> "
                                + adressInsp;
                        System.err.println(msg);
                        logMessages += msg + "\n";
                    }
                } else if (idInsp != null) {
                    String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidInspected: " + idInsp;
                    System.err.println(msg);
                    logMessages += msg + "\n";
                } else {
                    String msg = "Row: " + (i + 1) + "\tidInsp is null???\t" + adressInsp
                            + (adressInsp != null ? "" : " -> Station not defined");
                    System.err.println(msg);
                    logMessages += msg + "\n";
                }

                String oc = "";
                String cqr = "";
                if (!isSimpleFormat) {
                    oc = getStrVal(row.getCell(44)); // OriginCountry
                    cqr = getStrVal(row.getCell(45)); // Contact_Questions_Remarks               
                }
                Integer c1 = null;
                if (nameInsp != null && !nameInsp.trim().isEmpty()) {
                    Integer[] c = getCharge_Lieferung(idInsp, nameInsp, streetInsp, streetNoInsp, zipInsp,
                            cityInsp, countyInsp, countryInsp, activityInsp, vatInsp, prodNameOut, prodNumOut,
                            null, lotNo_Out, dayMHDOut, monthMHDOut, yearMHDOut, dayPDOut, monthPDOut,
                            yearPDOut, oc, dayOut, monthOut, yearOut, amountKG_Out, typePUOut, numPUOut, idRec,
                            nameRec, streetRec, streetNoRec, zipRec, cityRec, countyRec, countryRec,
                            activityRec, vatRec, serial, cqr, null, null, null, null);
                    if (c != null)
                        c1 = c[2];
                }

                if (isSimpleFormat)
                    continue;

                String prodNameIn = getStrVal(row.getCell(24)); // ProductName
                String prodNumIn = getStrVal(row.getCell(25)); // ProductNo
                String dayIn = getStrVal(row.getCell(26)); // Day
                String monthIn = getStrVal(row.getCell(27)); // Month
                String yearIn = getStrVal(row.getCell(28)); // Year
                String amountKG_In = getStrVal(row.getCell(29)); // amountKG
                String typePUIn = getStrVal(row.getCell(30)); // typePU
                String numPUIn = getStrVal(row.getCell(31)); // numPU
                String lotNo_In = getStrVal(row.getCell(32)); // 
                String dayMHDIn = getStrVal(row.getCell(33));
                String monthMHDIn = getStrVal(row.getCell(34));
                String yearMHDIn = getStrVal(row.getCell(35)); // 
                String dayPDIn = getStrVal(row.getCell(36));
                String monthPDIn = getStrVal(row.getCell(37));
                String yearPDIn = getStrVal(row.getCell(38));
                //Date dateIn = getDate(dayIn, monthIn, yearIn);
                //Date dateMHDIn = getDate(dayMHDIn, monthMHDIn, yearMHDIn);
                //Date datePDIn = getDate(dayPDIn, monthPDIn, yearPDIn);

                String idSup = getStrVal(row.getCell(39)); // ID_Address
                String adressSup = getStrVal(row.getCell(40)); // Address
                String activitySup = getStrVal(row.getCell(41)); // Activity
                String nameSup = adressSup;
                String streetSup = null;
                String streetNoSup = null;
                String zipSup = null;
                String citySup = null;
                String countySup = null;
                String countrySup = null;
                String vatSup = null;
                busRow = getRow(businessSheet, idSup, 0);
                if (busRow != null) {
                    nameSup = getStrVal(busRow.getCell(1)); //
                    streetSup = getStrVal(busRow.getCell(2)); //
                    streetNoSup = getStrVal(busRow.getCell(3), 10); //
                    zipSup = getStrVal(busRow.getCell(4), 10); //
                    citySup = getStrVal(busRow.getCell(5)); //
                    countySup = getStrVal(busRow.getCell(6), 30);
                    countrySup = getStrVal(busRow.getCell(7)); // 
                    vatSup = getStrVal(busRow.getCell(8)); //
                    if (!adressSup.toUpperCase().startsWith(nameSup.toUpperCase())) {
                        String msg = "Row: " + (i + 1) + "\tId issue on sups...\t" + nameSup + " <> "
                                + adressSup;
                        System.err.println(msg);
                        logMessages += msg + "\n";
                    }
                } else if (idSup != null) {
                    String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidSupplier: " + idSup;
                    System.err.println(msg);
                    logMessages += msg + "\n";
                } else {
                    String msg = "Row: " + (i + 1) + "\tidSup is null???\t" + adressSup
                            + (adressSup != null ? "" : " -> Station not defined");
                    System.err.println(msg);
                    logMessages += msg + "\n";
                }

                String ec = getStrVal(row.getCell(42)); // EndChain
                String ece = getStrVal(row.getCell(43)); // Explanation_EndChain
                String ft = getStrVal(row.getCell(46)); // Further_Traceback
                String ms = getStrVal(row.getCell(47)); // MicrobiologicalSample

                //if (amountKG_Out != null && amountKG_In != null && Integer.parseInt(amountKG_Out) > Integer.parseInt(amountKG_In)) System.err.println("amountOut > aomountIn!!! Row " + i + "; amountKG_Out: " + amountKG_Out + "; amountKG_In: " + amountKG_In);
                if (is1SurelyNewer(dayIn, monthIn, yearIn, dayOut, monthOut, yearOut)) {
                    String msg = "Row: " + (i + 1) + "\tDates not in temporal order, dateOut < dateIn!!! , KP: "
                            + KP + ", BL0: " + BL0 + "; dateOut: " + sdfFormat(dayOut, monthOut, yearOut)
                            + "; dateIn: " + sdfFormat(dayIn, monthIn, yearIn);
                    System.err.println(msg);
                    logMessages += msg + "\n";
                }

                Integer c2 = null;
                if (nameSup != null && !nameSup.trim().isEmpty()) {
                    Integer[] c = getCharge_Lieferung(idSup, nameSup, streetSup, streetNoSup, zipSup, citySup,
                            countySup, countrySup, activitySup, vatSup, prodNameIn, prodNumIn, null, lotNo_In,
                            dayMHDIn, monthMHDIn, yearMHDIn, dayPDIn, monthPDIn, yearPDIn, oc, dayIn, monthIn,
                            yearIn, amountKG_In, typePUIn, numPUIn, idInsp, nameInsp, streetInsp, streetNoInsp,
                            zipInsp, cityInsp, countyInsp, countryInsp, activityInsp, vatInsp, serial, cqr, ec,
                            ece, ft, ms);
                    if (c != null)
                        c2 = c[3];
                }
                if (c1 == null) { // Chargen
                    String msg = "Row: " + (i + 1) + "\tError Type 1 (Batches)!!"; // Fehlerchenchen_1
                    System.err.println(msg);
                    logMessages += msg + "\n";
                    numFails++;
                } else if (c2 == null) { // Lieferungen
                    String msg = "Row: " + (i + 1) + "\tError Type 2 (Deliveries)!! E.g. Station not defined?"; // Fehlerchenchen_2
                    System.err.println(msg);
                    logMessages += msg + "\n";
                    /*
                     * getCharge_Lieferung(nameSup, streetSup, streetNoSup,
                     * zipSup, citySup, countySup, countrySup, activitySup,
                     * vatSup, prodNameIn, prodNumIn, lotNo_In, dateMHDIn,
                     * datePDIn, oc, dateIn, amountKG_In, typePUIn, numPUIn,
                     * nameSup, streetSup, streetNoSup, zipSup, citySup,
                     * countySup, countrySup, activityInsp, vatInsp, comment,
                     * false);
                     */
                    numFails++;
                } else {
                    if (c2 != null) {
                        Integer cvID = getID("ChargenVerbindungen", new String[] { "Zutat", "Produkt" },
                                new String[] { c2.toString(), c1.toString() }, null, null);
                        if (cvID == null) {
                            String msg = "Row: " + (i + 1) + "\tError Type 4 (Links)!!"; // Fehlerchenchen_4
                            System.err.println(msg);
                            logMessages += msg + "\n";
                            numFails++;
                        } else {
                            numSuccess++;
                        }
                    }
                }
            }
        }
    }
    return new int[] { numSuccess, numFails };
}

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

License:Open Source License

private int[] doImportNewFormat(HSSFWorkbook wb, JProgressBar progress) {
    int numSuccess = 0;
    int numFails = 0;
    HSSFSheet transactionSheet = wb.getSheet("NewTransactions");
    HSSFSheet businessSheet = wb.getSheet("Business_List");
    HashMap<String, Integer[]> storedIDs = new HashMap<String, Integer[]>();
    HashMap<String, String> storedBackS = new HashMap<String, String>();
    int numRows = transactionSheet.getLastRowNum() + 1;
    progress.setMaximum(numRows);//ww w .  j a  v a2 s  .com
    progress.setValue(0);

    for (int i = 1; i < numRows; i++) {
        HSSFRow row = transactionSheet.getRow(i);
        if (row != null) {
            String backSerial = getStrVal(row.getCell(0), 16383);
            String serial = getStrVal(row.getCell(1));

            if ((serial == null || serial.trim().isEmpty())) {
                continue;//break;
            }

            String adressSup = getStrVal(row.getCell(2));
            String idSup = null;
            String nameSup = null;
            String streetSup = null;
            String streetNoSup = null;
            String zipSup = null;
            String citySup = null;
            String countySup = null;
            String countrySup = null;
            String vatSup = null;
            String tobSup = null;
            HSSFRow busRow = getRow(businessSheet, adressSup, 9);
            if (busRow != null) {
                idSup = getStrVal(busRow.getCell(0));
                nameSup = getStrVal(busRow.getCell(1));
                streetSup = getStrVal(busRow.getCell(2));
                streetNoSup = getStrVal(busRow.getCell(3), 10);
                zipSup = getStrVal(busRow.getCell(4), 10);
                citySup = getStrVal(busRow.getCell(5));
                countySup = getStrVal(busRow.getCell(6), 30);
                countrySup = getStrVal(busRow.getCell(7));
                vatSup = getStrVal(busRow.getCell(8));
                tobSup = getStrVal(busRow.getCell(10));
            } else if (adressSup != null) {
                //System.err.println("business not there??? Row: " + (i + 1) + "\tadressSup: " + adressSup);
                String msg = "Row: " + (i + 1) + "\tbusiness not there???\tadressSup: " + adressSup;
                System.err.println(msg);
                logMessages += msg + "\n";
            } else {
                //System.err.println("adressSup is null??? Row: " + (i + 1) + "\t" + adressSup + (adressSup != null ? "" : " -> Station not defined"));
                String msg = "Row: " + (i + 1) + "\tadressSup is null???\t" + adressSup
                        + (adressSup != null ? "" : " -> Station not defined");
                System.err.println(msg);
                logMessages += msg + "\n";
            }

            String prodName = getStrVal(row.getCell(3));
            String prodNum = getStrVal(row.getCell(4));
            String prodTreatment = getStrVal(row.getCell(5));

            String lotNo_ = getStrVal(row.getCell(6));
            String dayPD = getStrVal(row.getCell(7));
            String monthPD = getStrVal(row.getCell(8));
            String yearPD = getStrVal(row.getCell(9));
            String dayMHD = getStrVal(row.getCell(10));
            String monthMHD = getStrVal(row.getCell(11));
            String yearMHD = getStrVal(row.getCell(12));

            String day = getStrVal(row.getCell(13));
            String month = getStrVal(row.getCell(14));
            String year = getStrVal(row.getCell(15));
            String amountKG_ = getStrVal(row.getCell(16));
            String numPU = getStrVal(row.getCell(17));
            String typePU = getStrVal(row.getCell(18));

            String adressRec = getStrVal(row.getCell(19));
            String idRec = null;
            String nameRec = adressRec;
            String streetRec = null;
            String streetNoRec = null;
            String zipRec = null;
            String cityRec = null;
            String countyRec = null;
            String countryRec = null;
            String vatRec = null;
            String tobRec = null;
            busRow = getRow(businessSheet, adressRec, 9);
            if (busRow != null) {
                idRec = getStrVal(busRow.getCell(0));
                nameRec = getStrVal(busRow.getCell(1));
                streetRec = getStrVal(busRow.getCell(2));
                streetNoRec = getStrVal(busRow.getCell(3), 10);
                zipRec = getStrVal(busRow.getCell(4), 10);
                cityRec = getStrVal(busRow.getCell(5));
                countyRec = getStrVal(busRow.getCell(6), 30);
                countryRec = getStrVal(busRow.getCell(7));
                vatRec = getStrVal(busRow.getCell(8));
                tobRec = getStrVal(busRow.getCell(10));
            } else if (adressRec != null) {
                //System.err.println("business not there??? Row: " + (i + 1) + "\tadressRec: " + adressRec);
                String msg = "Row: " + (i + 1) + "\tbusiness not there???\tadressRec: " + adressRec;
                System.err.println(msg);
                logMessages += msg + "\n";
            } else {
                //System.err.println("adressRec is null??? Row: " + (i + 1) + "\t" + adressRec + (adressRec != null ? "" : " -> Station not defined"));
                String msg = "Row: " + (i + 1) + "\tadressRec is null???\t" + adressRec
                        + (adressRec != null ? "" : " -> Station not defined");
                System.err.println(msg);
                logMessages += msg + "\n";
            }

            String ec = getStrVal(row.getCell(21)); // EndChain
            String ece = getStrVal(row.getCell(22)); // Explanation_EndChain
            String oc = getStrVal(row.getCell(23)); // OriginCountry
            String cqr = getStrVal(row.getCell(24), 16383); // Contact_Questions_Remarks
            String ft = getStrVal(row.getCell(25)); // Further_Traceback
            String ms = getStrVal(row.getCell(26)); // MicrobiologicalSample

            Integer[] c = null;
            if (nameSup != null && !nameSup.trim().isEmpty()) {
                c = getCharge_Lieferung(idSup, nameSup, streetSup, streetNoSup, zipSup, citySup, countySup,
                        countrySup, tobSup, vatSup, prodName, prodNum, prodTreatment, lotNo_, dayMHD, monthMHD,
                        yearMHD, dayPD, monthPD, yearPD, oc, day, month, year, amountKG_, typePU, numPU, idRec,
                        nameRec, streetRec, streetNoRec, zipRec, cityRec, countyRec, countryRec, tobRec, vatRec,
                        serial, cqr, ec, ece, ft, ms);
                storedIDs.put(serial, c);
                storedBackS.put(serial, backSerial);
            }
        }
    }
    for (String serial : storedIDs.keySet()) {
        Integer[] c = storedIDs.get(serial);
        String backSerials = storedBackS.get(serial);
        if (backSerials != null && !backSerials.isEmpty()) {
            StringTokenizer tok = new StringTokenizer(backSerials, "\n");
            while (tok.hasMoreTokens()) {
                String backS = tok.nextToken();
                if (storedIDs.containsKey(backS)) {
                    Integer[] cBack = storedIDs.get(backS);
                    if (is1SurelyNewer(cBack[3], c[3])) {
                        //System.err.println("- Dates not in temporal order, dateOut < dateIn!!! Serial: " + serial + "; PreviousSerial: " + backS);
                        String msg = "Dates not in temporal order, dateOut < dateIn!!! Serial: " + serial
                                + "; PreviousSerial: " + backS;
                        System.err.println(msg);
                        logMessages += msg + "\n";
                    }
                    if (c[2] == null) {
                        //System.err.println("Fehlerchenchen_1!! Serial: " + backS);
                        String msg = "Error Type 1 (Batches)!! Serial: " + backS; // Fehlerchenchen_1
                        System.err.println(msg);
                        logMessages += msg + "\n";
                        numFails++;
                    } else if (cBack[3] == null) {
                        //System.err.println("Fehlerchenchen_2!! E.g. Station not defined? Serial: " + serial);
                        String msg = "Error Type 2 (Deliveries)!! E.g. Station not defined? Serial: " + backS; // Fehlerchenchen_1
                        System.err.println(msg);
                        logMessages += msg + "\n";
                        numFails++;
                    } else if (cBack[4].intValue() != c[0].intValue()) {
                        //System.err.println("Fehlerchenchen_3!! Recipient and Supplier different... Serial: " + serial);
                        String msg = "Error Type 3!! Recipient and Supplier different... Serial: " + serial;
                        System.err.println(msg);
                        logMessages += msg + "\n";
                    } else {
                        if (getID("ChargenVerbindungen", new String[] { "Zutat", "Produkt" },
                                new String[] { cBack[3].toString(), c[2].toString() }, null, null) == null) {
                            //System.err.println("Fehlerchenchen_4!! Serial/PreviousSerial: " + serial + " / " + backS);
                            String msg = "Error Type 4 (Links)!! Serial/PreviousSerial: " + serial + " / "
                                    + backS;
                            System.err.println(msg);
                            logMessages += msg + "\n";
                            numFails++;
                        } else {
                            numSuccess++;
                        }
                    }
                } else {
                    //System.err.println("backSerial not there..." + backS);
                    String msg = "backSerial not there..." + backS;
                    System.err.println(msg);
                    logMessages += msg + "\n";
                }
            }
        }
    }
    return new int[] { numSuccess, numFails };
}

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

License:Open Source License

private void transformFormat(HSSFWorkbook wb, HSSFWorkbook wbNew) {
    HSSFSheet transactionSheet = wb.getSheet("Transactions");
    HSSFSheet businessSheet = wb.getSheet("Business_List");
    int numRows = transactionSheet.getLastRowNum() + 1;
    HSSFSheet transactionSheetNew = wbNew.getSheet("NewTransactions");
    HSSFRow newRow;/*from  w  ww  .  jav  a  2  s.c  o  m*/
    HSSFCell newCell;
    HSSFSheet lookupNew = wbNew.getSheet("NewLookUp");
    HashMap<Long, HSSFRow> storedRows = new HashMap<Long, HSSFRow>();
    HashMap<Long, String> storedSerials = new HashMap<Long, String>();
    CRC32 crc32 = new CRC32();
    CellStyle cs = wbNew.createCellStyle();
    cs.setWrapText(true);

    int newRowLfd = 0;
    int i = 1;
    for (; i < numRows; i++) {
        HSSFRow row = transactionSheet.getRow(i);
        if (row != null) {
            String serial = getStrVal(row.getCell(0)); // Serial_number
            String contactPerson = getStrVal(row.getCell(2)); // person
            String adressRec = getStrVal(row.getCell(4)); // Address
            if ((serial == null || serial.trim().isEmpty())) {
                if (contactPerson != null && !contactPerson.isEmpty()
                        || adressRec != null && !adressRec.isEmpty()) {
                    System.err.println("serial is seriously null... " + (i + 1));
                }
            } else {
                /*
                int index = serial.lastIndexOf("_");
                if (index <= 0) {
                   System.err.println("index error ... no '_' there... " + (i + 1));
                }
                serial = serial.substring(0, index) + "_" + (i + 1);
                 */

                HSSFRow busRow = getRow(businessSheet, adressRec, 9);
                if (busRow == null) {
                    System.err.println("Id issue on recs...Row: " + (i + 1) + "\t" + adressRec);
                } else {
                    String s1 = getStrVal(busRow.getCell(1));
                    if (s1 == null)
                        s1 = "";
                    String s2 = getStrVal(busRow.getCell(2));
                    if (s2 == null)
                        s2 = "";
                    String s3 = getStrVal(busRow.getCell(3), 10);
                    if (s3 == null)
                        s3 = "";
                    String s4 = getStrVal(busRow.getCell(5));
                    if (s4 == null)
                        s4 = "";
                    String s5 = getStrVal(busRow.getCell(7));
                    if (s5 == null)
                        s5 = "";
                    adressRec = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3
                }

                String prodNameOut = getStrVal(row.getCell(6)); // ProductName
                String prodNumOut = getStrVal(row.getCell(7)); // ProductNo
                String dayOut = getStrVal(row.getCell(8)); // Day
                String monthOut = getStrVal(row.getCell(9)); // Month
                String yearOut = getStrVal(row.getCell(10)); // Year
                String amountKG_Out = getStrVal(row.getCell(11)); // amountKG
                String typePUOut = getStrVal(row.getCell(12)); // typePU
                String numPUOut = getStrVal(row.getCell(13)); // numPU
                String lotNo_Out = getStrVal(row.getCell(14)); // 
                String dayMHDOut = getStrVal(row.getCell(15));
                String monthMHDOut = getStrVal(row.getCell(16));
                String yearMHDOut = getStrVal(row.getCell(17)); // 
                String dayPDOut = getStrVal(row.getCell(18));
                String monthPDOut = getStrVal(row.getCell(19));
                String yearPDOut = getStrVal(row.getCell(20));

                String adressInsp = getStrVal(row.getCell(22)); // Address
                String activityInsp = getStrVal(row.getCell(23)); // Activity
                busRow = getRow(businessSheet, adressInsp, 9);
                if (busRow == null) {
                    System.err.println("Id issue on insps...Row: " + (i + 1) + "\t" + adressInsp);
                } else {
                    String s1 = getStrVal(busRow.getCell(1));
                    if (s1 == null)
                        s1 = "";
                    String s2 = getStrVal(busRow.getCell(2));
                    if (s2 == null)
                        s2 = "";
                    String s3 = getStrVal(busRow.getCell(3), 10);
                    if (s3 == null)
                        s3 = "";
                    String s4 = getStrVal(busRow.getCell(5));
                    if (s4 == null)
                        s4 = "";
                    String s5 = getStrVal(busRow.getCell(7));
                    if (s5 == null)
                        s5 = "";
                    adressInsp = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3
                }

                String prodNameIn = getStrVal(row.getCell(24)); // ProductName
                String prodNumIn = getStrVal(row.getCell(25)); // ProductNo
                String dayIn = getStrVal(row.getCell(26)); // Day
                String monthIn = getStrVal(row.getCell(27)); // Month
                String yearIn = getStrVal(row.getCell(28)); // Year
                String amountKG_In = getStrVal(row.getCell(29)); // amountKG
                String typePUIn = getStrVal(row.getCell(30)); // typePU
                String numPUIn = getStrVal(row.getCell(31)); // numPU
                String lotNo_In = getStrVal(row.getCell(32)); // 
                String dayMHDIn = getStrVal(row.getCell(33));
                String monthMHDIn = getStrVal(row.getCell(34));
                String yearMHDIn = getStrVal(row.getCell(35)); // 
                String dayPDIn = getStrVal(row.getCell(36));
                String monthPDIn = getStrVal(row.getCell(37));
                String yearPDIn = getStrVal(row.getCell(38));

                String adressSup = getStrVal(row.getCell(40)); // Address
                String activitySup = getStrVal(row.getCell(41)); // Activity
                busRow = getRow(businessSheet, adressSup, 9);
                if (busRow == null) {
                    System.err.println("Id issue on susps...Row: " + (i + 1) + "\t" + adressSup);
                } else {
                    String s1 = getStrVal(busRow.getCell(1));
                    if (s1 == null)
                        s1 = "";
                    String s2 = getStrVal(busRow.getCell(2));
                    if (s2 == null)
                        s2 = "";
                    String s3 = getStrVal(busRow.getCell(3), 10);
                    if (s3 == null)
                        s3 = "";
                    String s4 = getStrVal(busRow.getCell(5));
                    if (s4 == null)
                        s4 = "";
                    String s5 = getStrVal(busRow.getCell(7));
                    if (s5 == null)
                        s5 = "";
                    adressSup = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3
                }

                String ec = getStrVal(row.getCell(42)); // EndChain
                String ece = getStrVal(row.getCell(43)); // Explanation_EndChain
                String oc = getStrVal(row.getCell(44)); // OriginCountry
                String cqr = getStrVal(row.getCell(45)); // Contact_Questions_Remarks
                String ft = getStrVal(row.getCell(46)); // Further_Traceback
                String ms = getStrVal(row.getCell(47)); // MicrobiologicalSample

                busRow = getRow(lookupNew, activityInsp, 9);
                String treatmentIn = null, treatmentOut = null;
                if (busRow != null)
                    treatmentOut = busRow.getCell(13).getStringCellValue();
                busRow = getRow(lookupNew, activitySup, 9);
                if (busRow != null)
                    treatmentIn = busRow.getCell(13).getStringCellValue();

                String sOut = adressInsp + "_" + prodNameOut + "_" + prodNumOut + "_" + lotNo_Out + "_"
                        + dayPDOut + "_" + monthPDOut + "_" + yearPDOut + "_" + dayMHDOut + "_" + monthMHDOut
                        + "_" + yearMHDOut + "_" + dayOut + "_" + monthOut + "_" + yearOut + "_" + amountKG_Out
                        + "_" + numPUOut + "_" + typePUOut + "_" + adressRec;
                crc32.reset();
                crc32.update(sOut.getBytes());
                long crc32Out = crc32.getValue();
                //System.err.println(crc32Out + " -> " + sOut);
                String sIn = adressSup + "_" + prodNameIn + "_" + prodNumIn + "_" + lotNo_In + "_" + dayPDIn
                        + "_" + monthPDIn + "_" + yearPDIn + "_" + dayMHDIn + "_" + monthMHDIn + "_" + yearMHDIn
                        + "_" + dayIn + "_" + monthIn + "_" + yearIn + "_" + amountKG_In + "_" + numPUIn + "_"
                        + typePUIn + "_" + adressInsp;
                crc32.reset();
                crc32.update(sIn.getBytes());
                long crc32In = crc32.getValue();
                //System.err.println(crc32In + " -> " + sIn);                     

                String backSerial = serial + ".1";
                if (storedRows.containsKey(crc32In)) {
                    //HSSFRow r = storedRows.get(crc32In); backSerial = r.getCell(1).getStringCellValue();
                    backSerial = storedSerials.get(crc32In);
                }

                if (storedRows.containsKey(crc32Out)) {
                    HSSFRow r = storedRows.get(crc32Out);
                    HSSFCell c = r.getCell(0);
                    if (c == null) {
                        c = r.createCell(0);
                        c.setCellStyle(cs);
                        c.setCellValue(backSerial);
                    } else
                        add2Cell(c, backSerial);
                    add2Cell(r.getCell(20), contactPerson);
                    add2Cell(r.getCell(23), oc);
                    add2Cell(r.getCell(24), cqr);
                    add2Cell(r.getCell(26), ms);
                } else {
                    newRowLfd++;
                    newRow = transactionSheetNew.createRow(newRowLfd);
                    newCell = newRow.createCell(0, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(backSerial);
                    newCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(serial + ".0");
                    newCell = newRow.createCell(2, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(adressInsp);
                    newCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(prodNameOut);
                    newCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(prodNumOut);
                    if (treatmentOut != null) {
                        newCell = newRow.createCell(5, HSSFCell.CELL_TYPE_STRING);
                        newCell.setCellValue(treatmentOut);
                    }
                    newCell = newRow.createCell(6, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(lotNo_Out);
                    newCell = newRow.createCell(7, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayPDOut);
                    newCell = newRow.createCell(8, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthPDOut);
                    newCell = newRow.createCell(9, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearPDOut);
                    newCell = newRow.createCell(10, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayMHDOut);
                    newCell = newRow.createCell(11, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthMHDOut);
                    newCell = newRow.createCell(12, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearMHDOut);
                    newCell = newRow.createCell(13, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayOut);
                    newCell = newRow.createCell(14, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthOut);
                    newCell = newRow.createCell(15, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearOut);
                    newCell = newRow.createCell(16, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(amountKG_Out);
                    newCell = newRow.createCell(17, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(numPUOut);
                    newCell = newRow.createCell(18, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(typePUOut);
                    newCell = newRow.createCell(19, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(adressRec);
                    newCell = newRow.createCell(20, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(contactPerson);
                    newCell = newRow.createCell(23, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(oc);
                    newCell = newRow.createCell(24, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(cqr);
                    newCell = newRow.createCell(26, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ms);
                    storedRows.put(crc32Out, newRow);
                    storedSerials.put(crc32Out, serial + ".0");
                }

                if (storedRows.containsKey(crc32In)) {
                    HSSFRow r = storedRows.get(crc32In);
                    add2Cell(r.getCell(20), contactPerson);
                    add2Cell(r.getCell(21), ec);
                    add2Cell(r.getCell(22), ece);
                    add2Cell(r.getCell(23), oc);
                    add2Cell(r.getCell(24), cqr);
                    add2Cell(r.getCell(25), ft);
                    add2Cell(r.getCell(26), ms);
                } else {
                    newRowLfd++;
                    newRow = transactionSheetNew.createRow(newRowLfd);
                    newCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(serial + ".1");
                    newCell = newRow.createCell(2, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(adressSup);
                    newCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(prodNameIn);
                    newCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(prodNumIn);
                    if (treatmentIn != null) {
                        newCell = newRow.createCell(5, HSSFCell.CELL_TYPE_STRING);
                        newCell.setCellValue(treatmentIn);
                    }
                    newCell = newRow.createCell(6, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(lotNo_In);
                    newCell = newRow.createCell(7, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayPDIn);
                    newCell = newRow.createCell(8, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthPDIn);
                    newCell = newRow.createCell(9, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearPDIn);
                    newCell = newRow.createCell(10, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayMHDIn);
                    newCell = newRow.createCell(11, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthMHDIn);
                    newCell = newRow.createCell(12, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearMHDIn);
                    newCell = newRow.createCell(13, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayIn);
                    newCell = newRow.createCell(14, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthIn);
                    newCell = newRow.createCell(15, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearIn);
                    newCell = newRow.createCell(16, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(amountKG_In);
                    newCell = newRow.createCell(17, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(numPUIn);
                    newCell = newRow.createCell(18, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(typePUIn);
                    newCell = newRow.createCell(19, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(adressInsp);
                    newCell = newRow.createCell(20, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(contactPerson);
                    newCell = newRow.createCell(21, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ec);
                    newCell = newRow.createCell(22, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ece);
                    newCell = newRow.createCell(23, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(oc);
                    newCell = newRow.createCell(24, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(cqr);
                    newCell = newRow.createCell(25, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ft);
                    newCell = newRow.createCell(26, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ms);
                    storedRows.put(crc32In, newRow);
                    storedSerials.put(crc32In, serial + ".1");
                }
            }
        }
    }
    System.err.println("last row: " + i);
}

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

License:Open Source License

private HSSFRow getRow(HSSFSheet sheet, String value, int column) {
    HSSFRow result = null;//from   w  ww  . j a  v a2s .c o m
    if (value != null && !value.trim().isEmpty()) {
        int numRows = sheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row != null) {
                String val = getStrVal(row.getCell(column));
                if (val != null && !val.trim().isEmpty()) {
                    if (value.trim().equalsIgnoreCase(val.trim())) {
                        result = row;
                        break;
                    }
                }
            }
        }
    }
    return result;
}