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

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getRow

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java

License:Open Source License

private void verifyWorksheet(HSSFSheet sheet, String metricName, int expectedNumberOfDataRows,
        boolean hasTotalCount) {
    // 3 = title + blank row + column headers
    int expectedTotalRows = 3 + expectedNumberOfDataRows;
    if (hasTotalCount) {
        expectedTotalRows += 2;/*from   w  w  w  .  j  a va 2  s .c  o  m*/
    }
    assertThat(sheet.getPhysicalNumberOfRows(), equalTo(expectedTotalRows));

    // first row should have title in first cell
    HSSFRow row = sheet.getRow(0);
    assertThat(row, not(nullValue()));
    assertThat(row.getCell(0).getStringCellValue(), startsWith(metricName + " for"));

    // third row should have column headers in first and second cells
    row = sheet.getRow(2);
    assertThat(row.getCell(0).getStringCellValue(), equalTo("Timestamp"));
    assertThat(row.getCell(1).getStringCellValue(), equalTo("Value"));

    // verify rows with the sample data, i.e., timestamps and values
    int endRow = 3 + expectedNumberOfDataRows;
    for (int i = 3; i < endRow; i++) {
        row = sheet.getRow(i);
        assertThat(row.getCell(0).getStringCellValue(), not(nullValue()));
        assertThat(row.getCell(1).getNumericCellValue(), not(nullValue()));
    }

    row = sheet.getRow(sheet.getLastRowNum());
    if (hasTotalCount) {
        assertThat(row.getCell(0).getStringCellValue(), startsWith("Total Count:"));
        assertThat(row.getCell(1).getNumericCellValue(), not(nullValue()));
    } else {
        assertThat(row.getCell(0).getStringCellValue(), not(startsWith("Total Count:")));
    }
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei.//w w w  .  j  a  v  a2 s  . c  o  m
 *
 * @param excelSheet
 *            Die excel-Datei.
 * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei
 *         zurck.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<String[][]> exportWorkbook(final File excelSheet) throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);

    final int numberOfSheets = wb.getNumberOfSheets();
    final List<String[][]> sheetList = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();

        final int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows + 1][columns];
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue());
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            excelSheetInTDArray[i][j] = cell.getCellFormula();
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue());
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        sheetList.add(excelSheetInTDArray);
    }
    wb.close();
    return sheetList;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und
 * Listen von den Zeilen der sheets von der excel-Datei.
 *
 * @param excelSheet//from   ww w. j ava 2  s .c  o m
 *            Die excel-Datei.
 * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit
 *         den sheets beinhalten weitere Listen mit String die jeweils eine Zeile
 *         reprsentieren.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<List<List<String>>> exportWorkbookAsStringList(final File excelSheet)
        throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);
    final int numberOfSheets = wb.getNumberOfSheets();
    final List<List<List<String>>> sl = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();
        final int columns = sheet.getRow(0).getLastCellNum();
        final List<List<String>> excelSheetList = new ArrayList<>();
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                final List<String> reihe = new ArrayList<>();
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        reihe.add("");
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            reihe.add(Boolean.toString(cell.getBooleanCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            reihe.add(cell.getCellFormula());
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            reihe.add(Double.toString(cell.getNumericCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            reihe.add(cell.getRichStringCellValue().getString());
                        }
                    }
                }
                excelSheetList.add(reihe);
            }
        }
        sl.add(excelSheetList);
    }
    wb.close();
    return sl;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Replace null cells into empty cells./*from   w w  w  .j ava  2  s  .c  o m*/
 *
 * @param excelSheet
 *            the excel sheet
 * @return the HSSF workbook
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws FileNotFoundException
 *             the file not found exception
 */
public static HSSFWorkbook replaceNullCellsIntoEmptyCells(final File excelSheet)
        throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);
    final int numberOfSheets = wb.getNumberOfSheets();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();
        final int columns = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j, Cell.CELL_TYPE_BLANK);
                    }
                }
            }
        }
    }
    return wb;
}

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

License:Open Source License

public void mergeIDs() {
    System.err.println("Merging...");
    try (HSSFWorkbook wb = new HSSFWorkbook(
            new POIFSFileSystem(new FileInputStream(DBKernel.HSHDB_PATH + "mergeList.xls")))) {
        //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\mergeList.xls");
        HSSFSheet mergeSheet = wb.getSheet("mergeList");
        int numRows = mergeSheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            try {
                HSSFRow row = mergeSheet.getRow(i);
                if (row != null) {
                    HSSFCell cell = row.getCell(0);
                    if (cell != null) {
                        Integer oldEfsaID = (int) cell.getNumericCellValue();
                        if (cell != null) {
                            cell = row.getCell(1);
                            Integer newEfsaID = (int) cell.getNumericCellValue();
                            DBKernel.mergeIDs(DBKernel.getDBConnection(), "Station", oldEfsaID, newEfsaID);
                        }//from  w  w w .j ava 2 s  .com
                    }
                }
            } catch (Exception e) {
                System.err.println(e.getMessage() + "\t" + i);
            }
        }
    } catch (Exception e) {
    }
    System.err.println("Merging...Fin!");
}

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  ww  . j  av  a2  s .  c  o m
                    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 w w.  j  a  v  a2s.c o m*/
        progress.setValue(0);
        HSSFRow busRow = getRow(businessSheet, efsaID, 0);
        String idLST = getStrVal(busRow.getCell(0));
        String nameLST = getStrVal(busRow.getCell(1));
        String streetLST = getStrVal(busRow.getCell(2));
        String streetNoLST = getStrVal(busRow.getCell(3), 10);
        String zipLST = getStrVal(busRow.getCell(4), 10);
        String cityLST = getStrVal(busRow.getCell(5));
        String countyLST = getStrVal(busRow.getCell(6), 30);
        String countryLST = getStrVal(busRow.getCell(7));
        String vatLST = getStrVal(busRow.getCell(8));
        for (int i = 2; i < numRows; i++) {
            HSSFRow row = transactionSheet.getRow(i);
            if (row != null) {
                String product = getStrVal(row.getCell(2));
                String DeliveryDate_Day = getStrVal(row.getCell(14));
                String DeliveryDate_Month = getStrVal(row.getCell(15));
                String DeliveryDate_Year = getStrVal(row.getCell(16));
                String Amount = getStrVal(row.getCell(12));
                String Lot_Invoice = getStrVal(row.getCell(4));

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

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

License:Open Source License

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

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

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

License:Open Source License

private int[] doImportGaia(HSSFWorkbook wb, JProgressBar progress) {
    int numSuccess = 0;
    int numFails = 0;
    HSSFSheet transactionSheet = wb.getSheet("Transactions");
    HSSFSheet businessSheet = wb.getSheet("Business_List");
    int numRows = transactionSheet.getLastRowNum() + 1;
    progress.setMaximum(numRows);/* w  w  w .  jav  a2 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 va 2  s  . com*/
        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 };
}