Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:controller.DAORequest.java

private ArrayList<Resolution> readResolutions() {
    ArrayList<Resolution> resolutions = new ArrayList();
    try {/*from   w w  w .  ja v  a2 s  . co m*/
        FileInputStream fis = new FileInputStream(new File("src//files//DatosResolucion.xlsx"));
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet = wb.getSheetAt(0);
        for (Row row : sheet) {
            int id = 0;
            String attention = null;
            String title = null;
            String intro = null;
            String result = null;
            String resolve = null;
            String notify = null;
            String considerations = null;
            for (Cell cell : row) {
                if (row.getRowNum() != 0) {
                    switch (cell.getColumnIndex()) {
                    case 0:
                        id = (int) cell.getNumericCellValue();
                        break;
                    case 1:
                        attention = cell.getStringCellValue();
                        break;
                    case 2:
                        title = cell.getStringCellValue();
                        break;
                    case 3:
                        intro = cell.getStringCellValue();
                        break;
                    case 4:
                        result = cell.getStringCellValue();
                        break;
                    case 5:
                        resolve = cell.getStringCellValue();
                        break;
                    case 6:
                        notify = cell.getStringCellValue();
                        break;
                    case 7:
                        considerations = cell.getStringCellValue();
                        break;
                    }
                }
            }
            if (id != 0) {
                System.out.println("Resolution: [id: " + id + " attention: " + attention + "\ntitle: " + title
                        + " \nintro: " + intro + " \nresult: " + result + " \nresolve: " + resolve
                        + " \nnotify: " + notify + " \nconsiderations: " + considerations + "\n]");
                resolutions.add(
                        new Resolution(id, attention, title, intro, result, resolve, notify, considerations));
            }
        }

    }

    catch (FileNotFoundException e) {
        System.out.println("No hay archivo que cargar de Resolutions");
    } catch (IOException ex) {
        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    }

    return resolutions;

}

From source file:Controller.ThreadExcelImport.java

@Override
public void run() {

    //******/*w  ww. j  ava 2s.  com*/
    // CRIA STREAM DAS PLANILHAS
    // *******************

    // stream planilha 1
    InputStream stream1 = null;
    try {
        stream1 = new FileInputStream(new File(srcFileP1));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook1 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
            .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
            .open(stream1);

    // stream planilha 2
    InputStream stream2 = null;
    try {
        stream2 = new FileInputStream(new File(srcFileP2));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook2 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
            .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
            .open(stream2);

    //******
    // VERIFICA OS CABECALHOS
    // *******************

    // cabealhos da planilha 1
    Sheet sheet1 = null;
    sheet1 = workbook1.getSheetAt(0);

    // Pega de acordo com o cabealho as opes
    for (Row r : sheet1) {
        if (r.getRowNum() > 0)
            break;
        for (Integer i = 0; i < headerP1.size(); i++) {
            for (Cell c : r) {
                if (c.getStringCellValue().toLowerCase()
                        .equals(headerP1.get(i).getColumnName().toLowerCase())) {
                    // Adiciona o numero da coluna ao header
                    headerP1.get(i).setColumnNumber(c.getColumnIndex());
                    break;
                }
            }

            if (headerP1.get(i).getColumnNumber() == null) {
                // Alguma coluna do template est ausente
                JOptionPane.showMessageDialog(null, "A coluna " + headerP1.get(i).getColumnName().toLowerCase()
                        + " do template no existe como cabealho na planilha 1");
                System.exit(0);
            }

        }
    }
    // cabealhos da planilha 2
    Sheet sheet2 = null;
    sheet2 = workbook2.getSheetAt(0);

    // Pega de acordo com o cabealho as opes
    for (Row r : sheet2) {
        if (r.getRowNum() > 0)
            break;
        for (Integer i = 0; i < headerP2.size(); i++) {
            for (Cell c : r) {
                if (c.getStringCellValue().toLowerCase()
                        .equals(headerP2.get(i).getColumnName().toLowerCase())) {
                    // Adiciona o numero da coluna ao header
                    headerP2.get(i).setColumnNumber(c.getColumnIndex());
                    break;
                }
            }

            if (headerP2.get(i).getColumnNumber() == null) {
                // Alguma coluna do template est ausente
                JOptionPane.showMessageDialog(null, "A coluna " + headerP2.get(i).getColumnName().toLowerCase()
                        + " do template no existe como cabealho na planilha 2");
                System.exit(0);
            }

        }
    }

    //******
    // GRAVA EM MEMRIA A PLANILHA 2 PARA EVITAR O REABRIMENTO DA MESMA A CADA ITERAO DA PLANILHA 1
    // *******************
    stream2 = null;
    try {
        stream2 = new FileInputStream(new File(srcFileP2));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    workbook2 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
            .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
            .open(stream2);

    sheet2 = null;
    sheet2 = workbook2.getSheetAt(0);

    for (Row rowP2 : sheet2) {

        if (rowP2.getRowNum() > 0) {
            InterfaceMigracao objInterfaceP2 = Factory.getInstance(templateName);

            // calcula o hash
            String hashChaveP2 = "";
            for (String chaveP2 : colunaChave) {
                Integer columIndex = -1;
                for (Header he2 : headerP2) {
                    if (he2.getColumnName().equals(chaveP2)) {
                        columIndex = he2.getColumnNumber();
                        break;
                    }
                }

                if (columIndex > -1) {
                    Cell cell = null;
                    cell = rowP2.getCell(columIndex, Row.CREATE_NULL_AS_BLANK);
                    // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 );
                    hashChaveP2 = DigestUtils
                            .sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP2);
                }

            }

            for (Header he2 : headerP2) {

                Cell cell = rowP2.getCell(he2.getColumnNumber(), Row.CREATE_NULL_AS_BLANK);
                objInterfaceP2.setString(he2.getColumnName(), cell.getStringCellValue().trim().toLowerCase());
                objInterfaceP2.setExcelRowNumber((rowP2.getRowNum() + 1));
                //System.out.println("Novo loop HeaderP2 da linhaP2 " + String.valueOf(rowP2.getRowNum()) + " coluna " + he2.getColumnName() );
            }

            if (hashChaveP2.equals("")) {
                JOptionPane.showMessageDialog(null, "A linha " + String.valueOf((rowP2.getRowNum() + 1))
                        + " da planilha 2 tem as colunas chaves nula");
                System.exit(0);
            } else
                listaP2.put(hashChaveP2, objInterfaceP2);

        }
    }

    // limpa da memoria a workbook2
    try {
        if (workbook2 != null)
            workbook2.close();
    } catch (IOException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }

    // limpa da memoria o stream com workbook2
    if (stream2 != null)
        try {
            stream2.close();
        } catch (IOException ex) {
            Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
        }

    //******
    // FAZ A VALIDAO
    // OBSERVE QUE POR TER FEITO O FOREACH NOS PLANILHAS SE TORNA NECESS?RIO RECRIAR O STREAMING
    // *******************

    // Executa o loop nas linhas da planilha

    stream1 = null;
    try {
        stream1 = new FileInputStream(new File(srcFileP1));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    workbook1 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
            .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
            .open(stream1);

    sheet1 = null;
    sheet1 = workbook1.getSheetAt(0);

    InterfaceMigracao objInterfaceP1 = null;

    for (Row rowP1 : sheet1) {

        // Pega o hash dos campos chaves da planilha 1 a fim de localizar na planilha 1
        String hashChaveP1 = "";
        for (String chaveP1 : colunaChave) {
            Integer columIndex = -1;
            for (Header he1 : headerP1) {
                if (he1.getColumnName().equals(chaveP1)) {
                    columIndex = he1.getColumnNumber();
                    break;
                }
            }

            if (columIndex > -1) {
                Cell cell = null;
                cell = rowP1.getCell(columIndex, Row.CREATE_NULL_AS_BLANK);
                // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 );
                hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1);
            }

        }

        objInterfaceP1 = Factory.getInstance(templateName);
        // objInterfaceP2 = Factory.getInstance(templateName);

        objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1));
        Notify notify = new Notify();

        if (hashChaveP1.equals(""))
            notify.setLocalizadoP1(false);
        else {
            notify.setLocalizadoP1(true);
            //seta o numero da linha no excel

            // Preenche o objeto de interface da planilha 1 com seus respectivos dados
            for (Header he1 : headerP1) {

                Cell cell = null;
                cell = rowP1.getCell(he1.getColumnNumber(), Row.CREATE_NULL_AS_BLANK);
                objInterfaceP1.setString(he1.getColumnName(), cell.getStringCellValue().trim().toLowerCase());
            }

            boolean p2Localizado = false;

            // Preenche o objeto de interface da planilha 2 com seus respectivos dados
            if (rowP1.getRowNum() > 0) {
                InterfaceMigracao objInterfaceMigracaoP2 = listaP2.get(hashChaveP1);
                if (objInterfaceMigracaoP2 != null) {
                    p2Localizado = true;
                    notify.setEntidadeP2(objInterfaceMigracaoP2);
                }

            }
            notify.setLocalizadoP2(p2Localizado);

        }

        isRunning = true;

        objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1));
        notify.setEntidadeP1(objInterfaceP1);
        notify.setTotalRow((sheet1.getLastRowNum() + 1));

        notify.setRunning(isRunning);
        notify.setHeaderP1(headerP1);
        notify.setHeaderP2(headerP2);

        setChanged();
        notifyObservers(notify);

    }

    isRunning = false;
    // Notifica os observadores de que a execuo terminou
    Notify notify = new Notify();
    notify.setRunning(false);
    setChanged();
    notifyObservers(notify);
    listaP2 = null;

}

From source file:controllers.TargetController.java

License:Open Source License

private static void excelParser(File inputFile) throws Throwable {

    FileInputStream file = new FileInputStream(inputFile);

    //Create Workbook instance holding reference to .xls[x] file
    Workbook workbook = WorkbookFactory.create(file);

    //Get first/desired sheet from the workbook
    Sheet sheet = workbook.getSheetAt(0);

    // Check total row:
    if (sheet.getPhysicalNumberOfRows() <= 1) {
        throw new Exception("Sheet should have at least one row.");
    }//from   w  ww .j a v a  2  s .  c  o m
    Logger.debug("Sheet has " + sheet.getPhysicalNumberOfRows() + " rows.");

    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();

    // Header row:
    Row header = rowIterator.next();
    Logger.debug("HEADER: " + header);
    // TODO Check header row is right.

    // And the rest:
    StringBuilder sb = new StringBuilder();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        // Get
        Target target = new Target();
        target.title = row.getCell(0).getStringCellValue();
        target.fieldUrls = new ArrayList<FieldUrl>();
        // Check URL
        FieldUrl url = new FieldUrl(row.getCell(1).getStringCellValue());
        target.fieldUrls.add(url);
        FieldUrl existingFieldUrl = FieldUrl.findByUrl(url.url);
        if (existingFieldUrl != null) {
            String error = "Row # " + row.getRowNum() + ": CONFLICT - URL " + existingFieldUrl.url
                    + " is already part of target " + existingFieldUrl.target.id + "\n";
            Logger.debug(error);
            sb.append(error);
            continue;
        }
        //Collection c = new Collection();
        //c.name = 

        // 
        System.out.println(target);

        // TODO Merge with controllers.ApplicationController.bulkImport() code to avoid repetition.
        target.revision = Const.INITIAL_REVISION;
        target.active = true;

        target.selectionType = Const.SelectionType.SELECTION.name();

        if (target.noLdCriteriaMet == null) {
            target.noLdCriteriaMet = Boolean.FALSE;
        }

        if (target.keySite == null) {
            target.keySite = Boolean.FALSE;
        }

        if (target.ignoreRobotsTxt == null) {
            target.ignoreRobotsTxt = Boolean.FALSE;
        }

        // Save - disabled right now, as we do not want this live as yet.
        /*
        target.runChecks();
        target.save();
        */

        //
        System.out.println(target);
    }
    workbook.close();
    file.close();

    // And report errors
    if (sb.length() > 0) {
        throw (new Exception(sb.toString()));
    }
}

From source file:courtscheduler.persistence.CourtScheduleIO.java

License:Apache License

private Team processRow(Row currentRow, CourtScheduleInfo info) {
    short columnCount = currentRow.getLastCellNum();
    int columnCounter = 0;

    currentRowNum = currentRow.getRowNum();
    currentColumnNum = 0;// w  ww .jav  a2  s.  c o m

    Integer teamId = null;
    String teamName = "";
    Integer conference = null;
    String year = "";
    String gender = "";
    String grade = "";
    String level = "";
    String requests = "";
    String notSameTimeAs = "";
    Team team = new Team();

    while (columnCounter < columnCount) {

        Cell cell = currentRow.getCell(columnCounter);

        if (cell == null) {
            if (teamId == null) {
                System.out.println(
                        "================================================================================");
                break;
            } else {
                columnCounter++;
                continue; // if the cell is null just jump to the next iteration
            }
        }

        currentColumnNum = cell.getColumnIndex();
        if (columnCounter == 0) {
            int index = cell.toString().indexOf(".");
            String teamString = cell.toString().substring(0, index);
            try {
                teamId = Integer.parseInt(teamString);
                team.setTeamId(teamId);
                team.getDontPlay().addSharedTeam(teamId);
            } catch (NumberFormatException e) {
                //not sure what we should do here, this means a team's id is not being captured
                String niceMessage = String.format("Could not determine the team id from '%s'", teamString);
                niceMessage = niceMessage + "\tFound in " + currentCell();
                Main.error(niceMessage, e.toString());
            }
        } else if (columnCounter == 1) {
            team.setConference(getStringValueOfInt(cell.toString()));
        } else if (columnCounter == 2) {
            teamName = cell.toString();
            team.setTeamName(teamName);
        } else if (columnCounter == 3) {
            year = cell.toString();
            team.setYear(year);
        } else if (columnCounter == 4) {
            gender = cell.toString();
            team.setGender(gender);
        } else if (columnCounter == 5) {
            team.setGrade(getStringValueOfInt(cell.toString()));
            if (team.getGrade().trim().equals("")) {
                warning("Team \"" + teamId + "\" has no grade!" + "\tFound in " + currentCell());
            }
        } else if (columnCounter == 6) {
            level = cell.toString();
            team.setLevel(level);
        } else if (columnCounter == 7) {
            requests = cell.toString();
            //debug(team.getTeamId().toString()+":"+requests);
            System.out.println(team.getTeamId() + ": " + requests);
            processRequestConstraints(team, requests, info);
        } else if (columnCounter == 8) {
            notSameTimeAs = cell.toString();
            String[] tempSplit = notSameTimeAs.split(",");

            for (String teamIdStr : tempSplit) {
                try {
                    int index = teamIdStr.indexOf(".");
                    if (index > -1) {
                        teamId = Integer.parseInt(teamIdStr.substring(0, index));
                        team.getAvailability().getNotSameTimeAs().addSharedTeam(teamId);
                        team.getDontPlay().addSharedTeam(teamId);
                    }
                } catch (NumberFormatException nfe) {
                    warning("Unable to add team \"" + teamIdStr
                            + "\" to shared team list because it is not a number" + "\tFound in "
                            + currentCell());
                } catch (NullPointerException npe) {
                    warning("team.availability or team.availability.notSameTimeAs is null for " + teamIdStr
                            + "\tFound in " + currentCell());
                }
            }
        }

        columnCounter += 1;
    }
    return team;
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

/**
 * Este metodo se encarga de escribir o llenar todos los productos en la hoja de destino u objetivo,
 * que se encuentran en la hoja fuente./*from w  w  w.  j  a  va 2s .  c  o  m*/
 * 
 * @param sheet
 *          Intancia que modela la hoja objetivo o la hoja que se desea trabajar.
 * 
 * @param source
 *          Instancia que modela la hoja que contiene todos los productos.
 * 
 * @param start 
 *          Indica la posicion de la primer fila donde se encuentran los productos en
 *          la hoja fuente.
 */
private void writeProducts(Sheet sheet, Sheet source, int start) {
    AtomicInteger rowIndex = new AtomicInteger(3);

    final CreationHelper helper = sheet.getWorkbook().getCreationHelper();

    Stream<Row> rows = getRows(source, start).stream().filter((Row r) -> {
        Cell type = r.getCell(2);

        return type != null && type.getRichStringCellValue().getString().equals("Prds.");
    });

    if (rows != null) {
        rows.forEach(r -> {
            String product = r.getCell(6).getRichStringCellValue().getString()
                    .replaceFirst("\\[[0-9]+/[0-9]+\\]", "");

            int numbeerOfSubNodes = getNumberOfSubNodes(source, r.getRowNum(), "Packs", "Prds.");

            for (int subNodes = 0; subNodes < numbeerOfSubNodes; subNodes++) {
                Row row = sheet.getRow(rowIndex.getAndIncrement());

                if (row != null) {
                    Cell cellProductSubNode = row.createCell(5);

                    cellProductSubNode.setCellType(Cell.CELL_TYPE_STRING);
                    cellProductSubNode.setCellValue(helper.createRichTextString(product));

                    cellProductSubNode = null;
                    row = null;
                }
            }
        });
    }
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

private void writeRegions(XmlContry contry, Sheet sheet, int startRow, int column) {
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    XmlRegion region = Util.getRegionByContry(contry);

    if (region != null) {
        for (int index = startRow; index < sheet.getLastRowNum() + 1; index++) {
            Row r = sheet.getRow(index);

            try {
                if (r != null) {
                    Cell cellRegion = r.createCell(column);

                    cellRegion.setCellType(Cell.CELL_TYPE_STRING);
                    cellRegion.setCellValue(helper.createRichTextString(region.getName()));

                    updateMessages(String.format("Escribiendo la region: %s en la fila: %d", region.getName(),
                            r.getRowNum() + 1));
                }//from   ww  w.  j  a v  a  2s  . com
            } catch (Exception ex) {
                Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error al escribir la columna Region",
                        ex);

                Util.showException("Error al escribir la columna Region", ex);
            }
        }
    }
}

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

License:Open Source License

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

                } else {
                    if (!deliveryIDs.containsKey(val))
                        deliveryIDs.put(val, new HashSet<Row>());
                    HashSet<Row> hs = deliveryIDs.get(val);
                    hs.add(row);// ww  w . jav  a 2  s  . c om
                }
            }

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

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

License:Open Source License

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

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

License:Open Source License

private Station getStation(List<Exception> exceptions, Sheet businessSheet, String lookup, Row srcrow) {
    Station result = null;// w ww.jav a 2s.  com
    int numRows = businessSheet.getLastRowNum() + 1;
    for (int i = 0; i < numRows; i++) {
        Row row = businessSheet.getRow(i);
        if (row != null) {
            Cell cell = row.getCell(0);
            if (cell.getStringCellValue().equals(lookup)) {
                result = getStation(businessSheet.getRow(0), row);
                break;
            }
        }
    }
    if (result == null)
        exceptions.add(new Exception(
                "Station '" + lookup + "' is not correctly defined in Row " + (srcrow.getRowNum() + 1)));
    return result;
}

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

License:Open Source License

private Delivery getDelivery(List<Exception> exceptions, Sheet businessSheet, Station sif, Row row,
        boolean outbound, Row titleRow, String filename, boolean isForTracing, HashMap<String, Lot> outLots,
        HashMap<String, Delivery> existingDeliveries, boolean ignoreMissingLotnumbers,
        boolean isNewFormat_151105) {
    Cell cell;/* ww  w.  j  a  va 2s .  c  o  m*/
    if (isNewFormat_151105) {
        cell = row.getCell(0);
        if (isCellEmpty(cell)) {
            Cell cell10 = row.getCell(10);
            if (!isForTracing || !isCellEmpty(cell10)) {
                exceptions.add(
                        new Exception("It is essential to choose the associated Lot number ('Lot Number of "
                                + (isForTracing ? "" : " \"") + "Product" + (isForTracing ? "" : " Out\"")
                                + "') to the delivery in Row number " + (classRowIndex + 1)));
            }
            return null;
        }
        cell = row.getCell(12);
    } else {
        cell = row.getCell(12);
        if (isCellEmpty(cell)) {
            Cell cell10 = row.getCell(10);
            Cell cell0 = row.getCell(0);
            if ((!isForTracing && !isCellEmpty(cell0)) || !isCellEmpty(cell10)) {
                exceptions.add(
                        new Exception("It is essential to choose the associated Lot number ('Lot Number of "
                                + (isForTracing ? "" : " \"") + "Product" + (isForTracing ? "" : " Out\"")
                                + "') to the delivery in Row number " + (classRowIndex + 1)));
            }
            return null;
        }
    }
    Delivery result = new Delivery();
    String lotDelNumber = null;
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        lotDelNumber = getStr(cell.getStringCellValue());
        //if (isForTracing && outbound) {cell.setCellType(Cell.CELL_TYPE_STRING); result.setTargetLotId(getStr(cell.getStringCellValue()));}
        if (isNewFormat_151105) {
            result.setId(lotDelNumber);
        } else {
            if (!isForTracing && outbound) {
                result.setId(lotDelNumber);
            }
            if (isForTracing && !outbound) {
                result.setId(lotDelNumber);
            }
            if (!isForTracing && !outbound) {
                result.addTargetLotId(lotDelNumber);
            }
        }
    }
    Lot l;
    if (isForTracing && outbound) {
        cell = row.getCell(0);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            lotDelNumber = getStr(cell.getStringCellValue());
        }
        if (lotDelNumber == null && !ignoreMissingLotnumbers) {
            exceptions
                    .add(new Exception("Please, do always provide a lot number as this is most helpful! -> Row "
                            + (row.getRowNum() + 1) + " in '" + filename + "'\n"));
        }
        l = outLots.get(lotDelNumber);
    } else {
        Product p = new Product();
        if (outbound)
            p.setStation(sif);
        l = new Lot();
        l.setProduct(p);
        String lotNumber = null;
        if (isNewFormat_151105) {
            if (outbound) {
                cell = row.getCell(0);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    lotNumber = getStr(cell.getStringCellValue());
                }
                if (lotNumber == null && !ignoreMissingLotnumbers) {
                    exceptions.add(new Exception(
                            "Please, do always provide a lot number as this is most helpful! -> Row "
                                    + (row.getRowNum() + 1) + " in '" + filename + "'\n"));
                }
                l.setNumber(lotNumber);
                if (outLots.containsKey(lotNumber)) {
                    l = outLots.get(lotNumber);
                }
            } else {
                cell = row.getCell(0);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    lotNumber = getStr(cell.getStringCellValue());
                }
                if (lotNumber != null)
                    result.addTargetLotId(lotNumber);

                cell = row.getCell(1);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    p.setName(getStr(cell.getStringCellValue()));
                }
                cell = row.getCell(2);
                lotNumber = null;
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    lotNumber = getStr(cell.getStringCellValue());
                }
                if (lotNumber == null && !ignoreMissingLotnumbers) {
                    exceptions.add(new Exception(
                            "Please, do always provide a lot number as this is most helpful! -> Row "
                                    + (row.getRowNum() + 1) + " in '" + filename + "'\n"));
                }
                l.setNumber(lotNumber);
                if (lotNumber == null && p.getName() == null) {
                    exceptions.add(new Exception("Lot number undefined in Row number " + (classRowIndex + 1)));
                }
            }
        } else {
            cell = row.getCell(0);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                p.setName(getStr(cell.getStringCellValue()));
            }
            cell = row.getCell(1);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                lotNumber = getStr(cell.getStringCellValue());
            } else if (!ignoreMissingLotnumbers) {
                exceptions.add(
                        new Exception("Please, do always provide a lot number as this is most helpful! -> Row "
                                + (row.getRowNum() + 1) + " in '" + filename + "'\n"));
            }
            l.setNumber(lotNumber);
            if (lotNumber == null && p.getName() == null) {
                exceptions.add(new Exception(
                        "Lot number and product name undefined in Row number " + (classRowIndex + 1)));
            }
        }
    }
    //cell = row.getCell(1); if (cell != null) {cell.setCellType(Cell.CELL_TYPE_STRING); l.setNumber(getStr(cell.getStringCellValue()));}
    result.setLot(l);
    if (!outbound)
        result.setReceiver(sif);
    int startCol = isNewFormat_151105 ? 3 : 2;
    cell = row.getCell(startCol);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureDay(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 1);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureMonth(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 2);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureYear(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 3);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalDay(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 4);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalMonth(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 5);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalYear(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 6);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setUnitNumber(getDbl(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 7);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setUnitUnit(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 8);
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK
            || (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().isEmpty()))
        return null;
    if (outbound && cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setReceiver(getStation(exceptions, businessSheet, cell.getStringCellValue(), row));
    }
    if (!outbound && cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        l.getProduct().setStation(getStation(exceptions, businessSheet, cell.getStringCellValue(), row));
        l.setNumber(l.getNumber());
    }

    if (!isForTracing && !outbound || isForTracing && outbound) {
        if (!isNewFormat_151105 || result.getId() == null) {
            result.setId(getNewSerial(l, result));
            result.setNewlyGeneratedID(true);
        }
        if (existingDeliveries != null && existingDeliveries.containsKey(result.getId())) {
            result.getTargetLotIds().addAll(existingDeliveries.get(result.getId()).getTargetLotIds());
        }
    }

    // Further flexible cells
    for (int i = 13; i < 20; i++) {
        Cell tCell = titleRow.getCell(i);
        if (tCell != null && tCell.getCellType() != Cell.CELL_TYPE_BLANK) {
            tCell.setCellType(Cell.CELL_TYPE_STRING);
            cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                result.addFlexibleField(tCell.getStringCellValue(), cell.getStringCellValue());
            }
        }
    }
    return result;
}