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

Introduction

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

Prototype

@Override
public int getLastRowNum() 

Source Link

Document

Gets the number last row on the sheet.

Usage

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

License:Open Source License

private HashMap<String, Integer> loadNodeIDs10000() {
    System.err.println("loadNodeIDs10000...");

    nodeIds = new HashMap<String, Integer>();
    try (HSSFWorkbook wb = new HSSFWorkbook(
            new POIFSFileSystem(new FileInputStream(DBKernel.HSHDB_PATH + "nodesids10000.xls")))) {
        //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\nodesids10000.xls");
        HSSFSheet defaultSheet = wb.getSheet("default");
        int numRows = defaultSheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            try {
                HSSFRow row = defaultSheet.getRow(i);
                if (row != null) {
                    HSSFCell cell = row.getCell(0);
                    Integer id = (int) cell.getNumericCellValue();
                    if (id > maxNodeID)
                        maxNodeID = id;/*  w  w  w  . j av  a  2 s.com*/
                    cell = row.getCell(1);
                    String name = cell.getStringCellValue();
                    nodeIds.put(name, id);
                }
            } catch (Exception e) {
                System.err.println(e.getMessage() + "\t" + i);
            }
        }
    } catch (Exception e) {
    }

    System.err.println("loadNodeIDs10000...Fin!");
    return nodeIds;
}

---

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 w ww. j  ava  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);/*from   w  w  w  .  jav  a 2s .  c om*/
        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  av  a  2 s.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 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   w  w  w .j a 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);//from  w w  w. ja  v  a2 s .  co m
    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 ww w. jav a2 s.com
    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;//  w w w .j  a  v  a 2s .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;
}

---

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

License:Open Source License

public boolean doImport(final String filename, final JProgressBar progress, final boolean showResults) {
    Runnable runnable = new Runnable() {
        public void run() {
            try {
                if (progress != null) {
                    progress.setVisible(true);
                    progress.setStringPainted(true);
                    progress.setString("Importiere Excel Datei...");
                    progress.setMinimum(0);
                }//from ww  w  . j  a va2 s. c  o m

                InputStream is = null;
                System.out.println(filename);
                if (filename.startsWith("http://")) {
                    URL url = new URL(filename);
                    URLConnection uc = url.openConnection();
                    is = uc.getInputStream();
                } else if (filename.startsWith("/de/bund/bfr/knime/openkrise/db/res/")) {
                    is = this.getClass().getResourceAsStream(filename);
                } else {
                    is = new FileInputStream(filename);
                }

                try (HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is))) {
                    HSSFSheet sheet;
                    HSSFRow row;

                    int numSuccess = 0;
                    int numFailed = 0;
                    String unusedFields = "";
                    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                        sheet = wb.getSheetAt(i);
                        String tableName = sheet.getSheetName();
                        MyTable myT = DBKernel.myDBi.getTable(tableName);
                        if (myT != null) {
                            int numRows = sheet.getLastRowNum();
                            if (progress != null) {
                                progress.setMaximum(numRows);
                                progress.setValue(0);
                            }

                            row = sheet.getRow(0);
                            String sql1 = "";
                            String sql2 = "";
                            String sql3 = "";
                            Vector<String> codeSql1 = new Vector<>();
                            Vector<String> codeSql2 = new Vector<>();
                            LinkedHashMap<MyTable, Vector<Integer>> foreignTables = new LinkedHashMap<>();
                            int numCols = row.getLastCellNum();
                            String[] fieldNames = new String[numCols];
                            String[] fieldTypes = new String[numCols];//getTypes(fieldNames, myT);
                            String[] ffieldTypes = new String[numCols];
                            MyTable[] myForeignTables = new MyTable[numCols];
                            String[] kzS = new String[numCols];
                            String[] dbFieldnames = new String[numCols];
                            int lfdCol = 0;
                            Hashtable<String, String> dbFieldNames = new Hashtable<>();
                            for (int j = 0; j < numCols; j++) {
                                String fieldName = row.getCell(j).getStringCellValue();
                                fieldNames[j] = fieldName;
                                int ffe;
                                String dbFieldName = getDBFieldName(fieldName, myT, takecareofID);
                                if (dbFieldName != null) {
                                    String ft = getForeignTable(dbFieldName, myT);
                                    if (ft != null && ft.equals("DoubleKennzahlen")) {
                                        kzS[j] = getKZ(fieldName, dbFieldName);
                                        dbFieldnames[j] = dbFieldName;
                                    } else if (!dbFieldNames.containsKey(dbFieldName)) {
                                        dbFieldNames.put(dbFieldName, dbFieldName);
                                        sql1 += DBKernel.delimitL(dbFieldName) + ",";
                                        sql2 += "?,";
                                        sql3 += DBKernel.delimitL(dbFieldName) + "=?,";
                                        lfdCol++;
                                    }
                                    fieldTypes[j] = getType(dbFieldName, myT, takecareofID);
                                } else if ((ffe = foreignFieldExists(fieldName, myT)) >= 0) {
                                    if (!foreignTables.containsKey(myT.getForeignFields()[ffe]))
                                        foreignTables.put(myT.getForeignFields()[ffe], new Vector<Integer>());
                                    ffieldTypes[j] = getType(fieldName, myT.getForeignFields()[ffe], false);
                                    foreignTables.get(myT.getForeignFields()[ffe]).add(j);
                                    myForeignTables[j] = myT.getForeignFields()[ffe];
                                } else if (DBKernel.showHierarchic(tableName)
                                        && fieldName.toLowerCase().endsWith("-code")) {
                                    codeSql1.add(DBKernel.delimitL("CodeSystem") + ","
                                            + DBKernel.delimitL("Code") + "," + DBKernel.delimitL("Basis"));
                                    codeSql2.add(
                                            "'" + fieldName.substring(0, fieldName.length() - "-code".length())
                                                    + "',?,?");
                                } else if (!fieldName.equalsIgnoreCase("id")) {
                                    unusedFields += "," + fieldName;
                                }
                            }
                            if (sql1.length() > 0 && sql2.length() > 0) {
                                String sql = "INSERT INTO " + DBKernel.delimitL(tableName) + " ("
                                        + sql1.substring(0, sql1.length() - 1) + ") VALUES ("
                                        + sql2.substring(0, sql2.length() - 1) + ")";
                                PreparedStatement ps = DBKernel.getDBConnection().prepareStatement(sql,
                                        Statement.RETURN_GENERATED_KEYS);
                                int idCol = lfdCol + 1;
                                sql = "UPDATE " + DBKernel.delimitL(tableName) + " SET "
                                        + sql3.substring(0, sql3.length() - 1) + " WHERE "
                                        + DBKernel.delimitL("ID") + "=?";
                                PreparedStatement psUpdate = DBKernel.getDBConnection().prepareStatement(sql);
                                PreparedStatement[] psCodes = new PreparedStatement[codeSql1.size()];
                                boolean doCode[] = new boolean[codeSql1.size()];
                                int codesI;
                                for (codesI = 0; codesI < codeSql1.size(); codesI++) {
                                    sql = "INSERT INTO " + DBKernel.delimitL(DBKernel.getCodesName(tableName))
                                            + " (" + codeSql1.get(codesI) + ") VALUES (" + codeSql2.get(codesI)
                                            + ")";
                                    psCodes[codesI] = DBKernel.getDBConnection().prepareStatement(sql);
                                }
                                LinkedHashMap<MyTable, PreparedStatement> psForeign = new LinkedHashMap<>();
                                LinkedHashMap<MyTable, PreparedStatement> psForeignUpdate = new LinkedHashMap<>();
                                for (Map.Entry<MyTable, Vector<Integer>> entry : foreignTables.entrySet()) {
                                    Vector<Integer> vs = entry.getValue();
                                    String ssql1 = "", ssql2 = "", ssql3 = "";
                                    for (int ii = 0; ii < vs.size(); ii++) {
                                        ssql1 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]);
                                        ssql2 += ",?";
                                        ssql3 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]) + "=?";
                                    }
                                    if (ssql1.length() > 0 && ssql2.length() > 0 && ssql3.length() > 0) {
                                        sql = "INSERT INTO " + DBKernel.delimitL(entry.getKey().getTablename())
                                                + " (" + ssql1.substring(1) + ") VALUES (" + ssql2.substring(1)
                                                + ")";
                                        psForeign.put(entry.getKey(), DBKernel.getDBConnection()
                                                .prepareStatement(sql, Statement.RETURN_GENERATED_KEYS));
                                        sql = "UPDATE " + DBKernel.delimitL(entry.getKey().getTablename())
                                                + " SET " + ssql3.substring(1) + " WHERE "
                                                + DBKernel.delimitL("ID") + "=?";
                                        psForeignUpdate.put(entry.getKey(),
                                                DBKernel.getDBConnection().prepareStatement(sql));
                                    }
                                }

                                LinkedHashMap<Object, String> hashBL = null;
                                Iterator<Row> rows = sheet.rowIterator();
                                int lfd = 0;
                                while (rows.hasNext()) {
                                    row = (HSSFRow) rows.next();
                                    boolean setID = false;
                                    Integer lastID = null;
                                    ps.clearParameters();
                                    psUpdate.clearParameters();
                                    for (codesI = 0; codesI < codeSql1.size(); codesI++) {
                                        psCodes[codesI].clearParameters();
                                        doCode[codesI] = false;
                                    }
                                    LinkedHashMap<MyTable, Integer> lfdColsForeign = new LinkedHashMap<>();
                                    for (Map.Entry<MyTable, PreparedStatement> entry : psForeignUpdate
                                            .entrySet()) {
                                        MyTable myT1 = entry.getKey();
                                        psForeign.get(myT1).clearParameters();
                                        psForeignUpdate.get(myT1).clearParameters();
                                        lfdColsForeign.put(myT1, 0);
                                    }

                                    if (row.getRowNum() > 0) {
                                        lfdCol = 0;
                                        codesI = 0;
                                        Object[] kzVal = new Object[numCols];
                                        for (int j = 0; j < numCols; j++) {
                                            if (fieldTypes[j] != null) {
                                                /*
                                                 * if (fieldNames[j].equals(
                                                 * "Bundesland")) { hashBL =
                                                 * DBKernel
                                                 * .myDBi.getHashMap("County");
                                                 * } else { hashBL = null; }
                                                 */
                                                lfdCol++;
                                                if (fieldTypes[j].startsWith("VARCHAR(")
                                                        || fieldTypes[j].startsWith("CHAR(")
                                                        || fieldTypes[j].startsWith("BLOB(")
                                                                && !tableName.equals("DateiSpeicher"))
                                                    manageString(ps, psUpdate, lfdCol, row.getCell(j), hashBL);
                                                else if (fieldTypes[j].equals("BOOLEAN"))
                                                    manageBoolean(ps, psUpdate, lfdCol, row.getCell(j));
                                                else if (fieldTypes[j].equals("INTEGER"))
                                                    manageInteger(ps, psUpdate, lfdCol, row.getCell(j));
                                                else if (fieldTypes[j].equals("BIGINT"))
                                                    manageBigInteger(ps, psUpdate, lfdCol, row.getCell(j));
                                                else if (fieldTypes[j].equals("DATE"))
                                                    manageDate(ps, psUpdate, lfdCol, row.getCell(j));
                                                else if (fieldTypes[j].equals("DOUBLE")) {
                                                    if (kzS[j] != null) {
                                                        lfdCol--;
                                                        //System.err.println(dbFieldnames[j] + "\t" + kzS[j]);
                                                        if (DBKernel.kzIsString(kzS[j]))
                                                            kzVal[j] = manageString(null, null, lfdCol,
                                                                    row.getCell(j));
                                                        else if (DBKernel.kzIsBoolean(kzS[j]))
                                                            kzVal[j] = manageBoolean(null, null, lfdCol,
                                                                    row.getCell(j));
                                                        else
                                                            kzVal[j] = manageDouble(null, null, lfdCol,
                                                                    row.getCell(j));
                                                    } else {
                                                        manageDouble(ps, psUpdate, lfdCol, row.getCell(j));
                                                    }
                                                } else
                                                    System.err.println(
                                                            "Wasn hier los? Undefinierter Feldtyp???? ->\t"
                                                                    + fieldNames[j]);
                                            } else if (myForeignTables[j] != null && ffieldTypes[j] != null) {
                                                lfdColsForeign.put(myForeignTables[j],
                                                        lfdColsForeign.get(myForeignTables[j]) + 1);
                                                if (ffieldTypes[j].startsWith("VARCHAR(")
                                                        || ffieldTypes[j].startsWith("CHAR(")
                                                        || ffieldTypes[j].startsWith("BLOB(")
                                                                && !tableName.equals("DateiSpeicher"))
                                                    manageString(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j), hashBL);
                                                else if (ffieldTypes[j].equals("BOOLEAN"))
                                                    manageBoolean(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                else if (ffieldTypes[j].equals("INTEGER"))
                                                    manageInteger(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                else if (ffieldTypes[j].equals("BIGINT"))
                                                    manageBigInteger(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                else if (fieldTypes[j].equals("DATE"))
                                                    manageDate(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                else if (ffieldTypes[j].equals("DOUBLE")) {
                                                    manageDouble(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                } else
                                                    System.err.println(fieldNames[j] + " Feldtype????");
                                            } else if (fieldNames[j].equals("ID")) {
                                                lastID = manageInteger(null, null, 0, row.getCell(j));
                                                if (lastID != null) {
                                                    if (DBKernel.hasID(tableName, lastID.intValue())) {
                                                        psUpdate.setInt(idCol, lastID.intValue());
                                                        setID = true;
                                                    }
                                                }
                                            } else if (DBKernel.showHierarchic(tableName)
                                                    && fieldNames[j].toLowerCase().endsWith("-code")) {
                                                String code = manageString(psCodes[codesI], null, 1,
                                                        row.getCell(j));
                                                if (code != null && code.length() > 0)
                                                    doCode[codesI] = true;
                                                codesI++;
                                            } else {
                                                //System.out.println(fieldNames[j]);                                 
                                            }
                                        }
                                        try {
                                            if (setID) {
                                                psUpdate.execute();
                                            } else {
                                                if (ps.executeUpdate() > 0) {// execute()
                                                    lastID = DBKernel.getLastInsertedID(ps);
                                                } else {
                                                    System.err.println("W");
                                                }
                                            }
                                            numSuccess++;
                                            if (lastID != null) {
                                                for (int j = 0; j < numCols; j++) {
                                                    if (dbFieldnames[j] != null && kzVal[j] != null) {
                                                        DBKernel.insertDBL(myT.getTablename(), dbFieldnames[j],
                                                                lastID, null, kzS[j], kzVal[j]);
                                                    }
                                                }
                                                for (codesI = 0; codesI < codeSql1.size(); codesI++) {
                                                    if (doCode[codesI]) {
                                                        psCodes[codesI].setInt(2, lastID);
                                                        try {
                                                            psCodes[codesI].execute();
                                                            numSuccess++;
                                                        } catch (SQLException e1) {
                                                            numFailed++;
                                                            System.err.println(psCodes[codesI]);
                                                        }
                                                    }
                                                }

                                                for (Map.Entry<MyTable, PreparedStatement> entry : psForeign
                                                        .entrySet()) {
                                                    MyTable myT1 = entry.getKey();
                                                    MyTable[] foreignTs = myT.getForeignFields();
                                                    for (int ii = 0; ii < foreignTs.length; ii++) {
                                                        if (foreignTs[ii] != null
                                                                && foreignTs[ii].equals(myT1)) {
                                                            if (psForeign.get(myT1).executeUpdate() > 0) { // INSERT
                                                                int lID = DBKernel
                                                                        .getLastInsertedID(psForeign.get(myT1));
                                                                // Das erstbeste Feld, das auf den Fremdtable verweist, wird mit dem Neueintrag verlinkt
                                                                DBKernel.sendRequest("UPDATE "
                                                                        + DBKernel.delimitL(tableName) + " SET "
                                                                        + DBKernel.delimitL(
                                                                                myT.getFieldNames()[ii])
                                                                        + "=" + lID + " WHERE "
                                                                        + DBKernel.delimitL("ID") + "="
                                                                        + lastID, false);
                                                            }
                                                            break;
                                                        }
                                                    }
                                                }
                                                /*
                                                 * for (int j=0;j<numCols;j++) {
                                                 * if (myForeignTables[j] !=
                                                 * null && ffieldTypes[j] !=
                                                 * null) { MyTable[] foreignTs =
                                                 * myT.getForeignFields(); for
                                                 * (int
                                                 * ii=0;ii<foreignTs.length;
                                                 * ii++) { if (foreignTs[ii] !=
                                                 * null && foreignTs[ii].equals(
                                                 * myForeignTables[j])) { if
                                                 * (psForeign
                                                 * .get(myForeignTables
                                                 * [j]).executeUpdate() > 0) {
                                                 * // INSERT int lID =
                                                 * DBKernel.getLastInsertedID
                                                 * (psForeign
                                                 * .get(myForeignTables[j]));
                                                 * DBKernel
                                                 * .sendRequest("UPDATE " +
                                                 * DBKernel.delimitL(tableName)
                                                 * + " SET " +
                                                 * DBKernel.delimitL(
                                                 * myT.getFieldNames()[ii]) +
                                                 * "=" + lID + " WHERE " +
                                                 * DBKernel.delimitL("ID") + "="
                                                 * + lastID, false); } break; }
                                                 * } } }
                                                 */
                                            }
                                        } catch (Exception e1) {
                                            numFailed++;
                                            MyLogger.handleMessage(ps.toString());
                                            MyLogger.handleException(e1);
                                        }
                                    }
                                    if (progress != null) {
                                        lfd++;
                                        progress.setValue(lfd);
                                    }
                                }
                            }

                            myT.doMNs();
                            if (progress != null) {
                                // Refreshen:
                                MyDBTable myDB = DBKernel.mainFrame.getMyList().getMyDBTable();
                                if (myDB.getActualTable() != null) {
                                    String actTablename = myDB.getActualTable().getTablename();
                                    if (actTablename.equals(tableName)
                                            || actTablename.equals(DBKernel.getCodesName(tableName))) {
                                        myDB.setTable(myDB.getActualTable());
                                    }
                                }
                                MyDBTree myTR = DBKernel.mainFrame.getMyList().getMyDBTree();
                                if (myTR.getActualTable() != null) {
                                    String actTablename = myTR.getActualTable().getTablename();
                                    if (actTablename.equals(tableName)
                                            || actTablename.equals(DBKernel.getCodesName(tableName))) {
                                        myTR.setTable(myTR.getActualTable());
                                    }
                                }
                            }
                        } else {
                            System.err.println(tableName + " nicht in DB???");
                        }
                    }
                    if (progress != null) {
                        progress.setVisible(false);
                    }
                    String log = numSuccess + " erfolgreiche Importe.\n";
                    log += numFailed + " fehlgeschlagene Importe.\n";
                    if (unusedFields.length() > 0)
                        log += "Unbekannte Felder: " + unusedFields.substring(1) + "\n";
                    if (showResults) {
                        InfoBox ib = new InfoBox(log, true, new Dimension(400, 300), null);
                        ib.setVisible(true);
                    } else {
                        System.out.println("GeneralXLSImporter (" + filename + "):\n" + log);
                    }
                } catch (Exception e) {
                    MyLogger.handleException(e);
                }
            } catch (Exception e) {
                MyLogger.handleException(e);
            }
        }
    };

    Thread thread = new Thread(runnable);
    thread.start();
    try {
        thread.join();
    } catch (InterruptedException e) {
        MyLogger.handleException(e);
    }
    return true;
}

---

From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java

/**
 * Gets a XLS file and parse it//from  w  ww . j a v a  2  s  . com
 * @param file The XLS File that you want to get parsed
 * @return A ArrayList where the first object is a Array containing the Data and the Second the Header
 */
public ArrayList<Object> parseXLSFile(File file) {
    int index = -1;
    HSSFWorkbook workbook = null;
    try {
        try {
            FileInputStream inputStream = new FileInputStream(file);
            workbook = new HSSFWorkbook(inputStream);
        } catch (IOException ex) {
            ex.printStackTrace();
        }

        assert workbook != null;
        String[] strings = new String[workbook.getNumberOfSheets()];
        //get all sheet names from selected workbook
        for (int i = 0; i < strings.length; i++) {
            strings[i] = workbook.getSheetName(i);
        }
        JFrame frame = new JFrame("Input Dialog");

        String selectedsheet = (String) JOptionPane.showInputDialog(frame,
                "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null,
                strings, strings[0]);

        if (selectedsheet != null) {
            for (int i = 0; i < strings.length; i++) {
                if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
                    index = i;
            }
            HSSFSheet sheet = workbook.getSheetAt(index);
            HSSFRow row = sheet.getRow(0);

            if (row != null) {
                headers = new String[row.getLastCellNum()];

                for (int i = 0; i < row.getLastCellNum(); i++) {
                    headers[i] = row.getCell(i).toString();
                }
            }

            data = new String[sheet.getLastRowNum()][];
            for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                row = sheet.getRow(j);
                int rowCount = row.getLastCellNum();
                String[] dataRow = new String[rowCount];
                for (int i = 0; i < rowCount; i++) {
                    HSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);
                    dataRow[i] = cell.toString();
                }
                data[j - 1] = dataRow;
            }
        } else {
            return null;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    ArrayList<Object> returnData = new ArrayList<>();
    returnData.add(data);
    returnData.add(headers);

    return returnData;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• »