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

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

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

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 . j av  a  2  s .  c om

    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:courtscheduler.persistence.CourtScheduleIO.java

License:Apache License

public short getColumnWidth(File file) throws Exception {

    FileInputStream fis = new FileInputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(fis);

    // Get worksheet by index
    XSSFSheet sh = wb.getSheetAt(0);//  w  ww . j  a  v a  2 s.  c om

    short columnWidth = 0;
    Integer rowCounter = 0;
    Integer rowCount = sh.getLastRowNum();

    while (rowCounter <= rowCount) {
        Row currentRow = sh.getRow(rowCounter);
        short columnCount = currentRow.getLastCellNum();

        if (columnCount > columnWidth)
            columnWidth = columnCount;
    }

    return columnWidth;
}

From source file:coverageqc.data.DoNotCall.java

public static DoNotCall populate(Row xslxHeadingRow, Row xslxDataRow, Integer calltype) {
    DoNotCall donotcall = new DoNotCall();
    int columnNumber;
    int cellIndex;
    String[] headerArray;/*from w w w. j a  v a2  s  .co  m*/
    HashMap<String, Integer> headings = new HashMap<String, Integer>();

    columnNumber = xslxHeadingRow.getLastCellNum();
    headerArray = new String[columnNumber];

    Iterator<Cell> cellIterator = xslxHeadingRow.cellIterator();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        cellIndex = cell.getColumnIndex();
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            headerArray[cellIndex] = Boolean.toString(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            headerArray[cellIndex] = Double.toString(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            headerArray[cellIndex] = cell.getStringCellValue();
            break;
        default:
            headerArray[cellIndex] = "";
        }

    } //end while celliterator

    for (int x = 0; x < headerArray.length; x++) {
        headings.put(headerArray[x].substring(0, headerArray[x].indexOf("_")), x);
    }

    //String[] dataArray = xslxDataLine.split("\t");
    if (xslxDataRow.getCell(headings.get("HGVSc")) != null) {
        donotcall.hgvsc = xslxDataRow.getCell(headings.get("HGVSc").intValue()).getStringCellValue();

    }
    //donotcall.hgvsp = xslxDataRow.getCell(headings.get("HGVSp").intValue()).getStringCellValue();
    if (xslxDataRow.getCell(headings.get("ENSP")) != null) {
        donotcall.ensp = xslxDataRow.getCell(headings.get("ENSP").intValue()).getStringCellValue();
    }
    if (xslxDataRow.getCell(headings.get("Transcript")) != null) {
        donotcall.transcript = xslxDataRow.getCell(headings.get("Transcript").intValue()).getStringCellValue();
    } else {
        System.out.println(
                "Transcript_27 column entry is negative!  This is essential to do not call! Do not call list needs to be fixed!  Crashing to prevent abnormal behavior!");
        System.exit(1);
    }
    donotcall.coordinate = (long) xslxDataRow.getCell(headings.get("Coordinate").intValue())
            .getNumericCellValue();

    // CallType is the page of the xlsx :
    // 1 => Always_Not_Real
    // 2 => Not_Real_When_Percentage_Low
    // 3 => Undetermined_Importance
    if (calltype == 1) {
        donotcall.callType = "Don't call, always";
    } else if (calltype == 2) {
        donotcall.callType = "If percentage low, don't call";

    } else {
        donotcall.callType = "On lab list, Unknown significance";
    }

    return donotcall;
}

From source file:csv.impl.ExcelReader.java

License:Open Source License

/**
 * Returns the row as Java objects.//  w w w . ja va 2s.  co  m
 * Values in the array are Java objects depending on the cell type. If the cell contained
 * a formula, the formula is evaluated before returning the row.
 * @return values in row
 * @param row row to read
 */
public Object[] getValues(Row row) {
    if (row == null)
        return null;
    List<Object> columns = new ArrayList<Object>();
    int colCount = row.getLastCellNum();
    for (int col = 0; col < colCount; col++) {
        Cell cell = row.getCell(col);
        columns.add(getValue(cell));
    }

    return CSVUtils.convertList(columns, getMinimumColumnCount());
}

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 ww w  .  j  av a 2s .  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.pmmlite.io.XlsReader.java

License:Open Source License

private Map<String, Integer> getColumns(Sheet sheet) {
    Map<String, Integer> columns = new LinkedHashMap<>();
    Row firstRow = sheet.getRow(sheet.getFirstRowNum());

    if (firstRow == null) {
        return columns;
    }/*from ww  w.  java2s . co m*/

    for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) {
        String name = getData(firstRow.getCell(i));

        if (name != null) {
            columns.put(name, i);
        }
    }

    return columns;
}

From source file:de.interactive_instruments.ShapeChange.SBVR.SbvrRuleLoader.java

License:Open Source License

/**
 * @param sbvrXls/*from  w  ww .j  a v a2  s .com*/
 * @return mapping of schema package name to SBVR rules that apply to
 *         classes in this schema *
 *         <ul>
 *         <li>key: class name</li>
 *         <li>value: mapping of schema package name to SBVR rule info
 *         <ul>
 *         <li>key: schema package name (
 *         {@value #UNSPECIFIED_SCHEMA_PACKAGE_NAME} if no schema package
 *         name has been provided)</li>
 *         <li>value: list of SBVR rules that apply to classes in that
 *         schema (the list is sorted according to lexical order on a) the
 *         class name and b) the rule text)</li>
 *         </ul>
 *         </ul>
 */
private TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> parseSBVRRuleInfos(Workbook sbvrXls) {

    TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> rules = new TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>>();

    if (sbvrXls == null)
        return null;

    Sheet rulesSheet = null;

    for (int i = 0; i < sbvrXls.getNumberOfSheets(); i++) {

        String sheetName = sbvrXls.getSheetName(i);

        if (sheetName.equalsIgnoreCase("Constraints")) {
            rulesSheet = sbvrXls.getSheetAt(i);
            break;
        }
    }

    if (rulesSheet == null) {

        result.addError(this, 3);
        return null;
    }

    // read header row to determine which columns contain relevant
    // information
    Map<String, Integer> fieldIndexes = new HashMap<String, Integer>();

    Row header = rulesSheet.getRow(rulesSheet.getFirstRowNum());

    if (header == null) {
        result.addError(this, 4);
        return null;
    }

    boolean classNameFound = false;
    boolean commentsFound = false;
    boolean ruleNameFound = false;
    boolean ruleTextFound = false;
    boolean schemaPackageFound = false;

    for (short i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {

        Cell c = header.getCell(i, Row.RETURN_BLANK_AS_NULL);

        if (c == null) {
            // this is allowed
        } else {

            String value = c.getStringCellValue();

            if (value.equalsIgnoreCase(SbvrRuleInfo.CLASS_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.CLASS_COLUMN_NAME, (int) i);
                classNameFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.COMMENT_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.COMMENT_COLUMN_NAME, (int) i);
                commentsFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME, (int) i);
                schemaPackageFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME, (int) i);
                ruleTextFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_NAME_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.RULE_NAME_COLUMN_NAME, (int) i);
                ruleNameFound = true;
            }
        }
    }

    // if (fieldIndexes.size() != 5) {
    if (!ruleNameFound && !ruleTextFound) {
        // log message that required fields were not found
        result.addError(this, 5);
        return null;
    }

    /*
     * Read rule content
     */
    for (int i = rulesSheet.getFirstRowNum() + 1; i <= rulesSheet.getLastRowNum(); i++) {

        Row r = rulesSheet.getRow(i);
        int rowNumber = i + 1;

        if (r == null) {
            // ignore empty rows
            continue;
        }

        SbvrRuleInfo sri = new SbvrRuleInfo();

        // get rule name (required)
        Cell c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_NAME_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
        if (c == null) {
            // log message
            result.addWarning(this, 6, "" + rowNumber);
            continue;
        } else {
            String cellValue = c.getStringCellValue();
            if (cellValue != null) {
                if (cellValue.contains(":")) {
                    sri.setName(cellValue.substring(cellValue.lastIndexOf(":") + 1));
                } else {
                    sri.setName(cellValue);
                }
            }
        }

        // get rule text (required)
        c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
        if (c == null) {
            // log message
            result.addWarning(this, 7, "" + rowNumber);
            continue;
        } else {
            sri.setText(c.getStringCellValue());
        }

        // get comment (optional)
        if (commentsFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.COMMENT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c != null) {
                sri.setComment(c.getStringCellValue());
            }
        }

        // get schema package (optional)
        if (schemaPackageFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c == null) {
                sri.setSchemaPackageName(UNSPECIFIED_SCHEMA_PACKAGE_NAME);
            } else {
                sri.setSchemaPackageName(c.getStringCellValue());
            }
        }

        /*
         * get class name (optional when loading from excel because later we
         * can still try parsing it from the rule text)
         */
        if (classNameFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.CLASS_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c == null) {
                /*
                 * then after this we'll try to parse the class name from
                 * the rule text
                 */
            } else {
                sri.setClassName(c.getStringCellValue());
            }
        }

        if (sri.getClassName() == null) {

            /*
             * try parsing the main class name from the rule text
             */
            result.addInfo(this, 10, sri.getName());

            String mainClassName = parseClassNameFromRuleText(sri.getText());

            if (mainClassName == null) {
                result.addWarning(this, 8, sri.getName());
                continue;
            } else {
                sri.setClassName(mainClassName);
            }
        }

        List<SbvrRuleInfo> rulesList;
        TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName;

        if (rules.containsKey(sri.getClassName())) {

            rulesBySchemaPackageName = rules.get(sri.getClassName());

            if (rulesBySchemaPackageName.containsKey(sri.getSchemaPackageName())) {
                rulesList = rulesBySchemaPackageName.get(sri.getSchemaPackageName());
            } else {
                rulesList = new ArrayList<SbvrRuleInfo>();
                rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList);
            }

        } else {

            rulesBySchemaPackageName = new TreeMap<String, List<SbvrRuleInfo>>();
            rules.put(sri.getClassName(), rulesBySchemaPackageName);

            rulesList = new ArrayList<SbvrRuleInfo>();
            rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList);
        }

        rulesList.add(sri);
    }

    // now sort all lists contained in the map
    for (TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName : rules.values()) {
        for (List<SbvrRuleInfo> rulesList : rulesBySchemaPackageName.values()) {

            Collections.sort(rulesList, new Comparator<SbvrRuleInfo>() {

                @Override
                public int compare(SbvrRuleInfo o1, SbvrRuleInfo o2) {

                    int classNameComparison = o1.getClassName().compareTo(o2.getClassName());

                    if (classNameComparison != 0) {
                        return classNameComparison;
                    } else {
                        return o1.getText().compareTo(o2.getText());
                    }
                }
            });
        }
    }

    return rules;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.workbookdata.SheetContext.java

License:Open Source License

public short getColumnFullParentNameColumnNumber() {
    Row row = getSheet().getRow(getHeaderRowNumber());
    for (short cellnum = row.getFirstCellNum(); cellnum <= row.getLastCellNum(); cellnum++) {
        Cell cell = row.getCell(cellnum);
        if (SheetContext.FULL_PARENT_NAME_COLUMN.equals(cell.getStringCellValue())) {
            return cellnum;
        }/*from  ww w. ja  v  a  2 s . c o  m*/
    }
    return -1;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.sheets.SheetImporter.java

License:Open Source License

/**
 * Last cells must not contain an empty String
 *
 * @param row// w ww .ja va2 s.  c  o  m
 *          a POI Row
 * @return int number of cells in row
 */
private int getRowSize(Row row) {
    int size = 0;
    for (int i = row.getLastCellNum(); i >= 0; i--) {
        Cell cell = row.getCell(i - 1);
        if (!ExcelImportUtilities.isEmpty(cell)) {
            size = i;
            break;
        }
    }

    return size;
}

From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java

License:Apache License

@Test
public void testExportToManyRelation() throws Exception {
    File tempFile = File.createTempFile("taskExportTest", ".xlsx");
    EntityExportSource<Task> tasks = new EntityExportSource<>(PersistentWork.idsFrom(Task.class), Task.class);
    EntityExportSource<Tag> tags = new EntityExportSource<>(PersistentWork.idsFrom(Tag.class), Tag.class);
    XlsxExporter exporter = new XlsxExporter();
    exporter.export(tempFile, tasks, tags);

    Workbook wb = WorkbookFactory.create(tempFile);
    Sheet taskSheet = wb.getSheet(Task.class.getName());
    Sheet tagSheet = wb.getSheet(Tag.class.getName());
    assertNotNull(taskSheet);/*  w  w  w  .java 2 s. co  m*/
    assertNotNull(tagSheet);

    Row firstRow = taskSheet.getRow(0);
    int pos = 0;
    Iterator<Cell> cellIterator = firstRow.cellIterator();

    String property = PropertyPath.property(Task.class, t -> t.getTags());
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        if (cell.getStringCellValue().equals(property)) {
            break;
        }
        pos++;
    }
    assertNotEquals(Task.class.getSimpleName() + "." + property + " not exported", firstRow.getLastCellNum(),
            pos);

    Cell cell = taskSheet.getRow(1).getCell(pos);
    String[] split = StringUtils.split(cell.getStringCellValue(), ToManyColumn.SEPARATOR);
    assertEquals(2, split.length);
    assertTrue(Arrays.asList(split).contains("tag" + ToManyColumn.SEPARATOR_REPLACEMENT + "1"));
    assertTrue(Arrays.asList(split).contains("tag2"));
}