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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. 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:org.semtix.gui.auszahlung.auszahlungsmodul.Datenabgleich.java

License:Open Source License

/**
 * Liest eine XLS Datei ein und gleicht sie mit der Datenbank ab
 *
 * @param path /Pfad/angabe/zur/Datei.xyz 
 *///  www  . j  av a 2s  . co  m
public void einlesen(String path) {

    try {

        FileInputStream file = new FileInputStream(new File(path));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file);

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

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

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            Cell[] cells = new Cell[row.getLastCellNum()];
            int i = 0;
            boolean exmatrikuliert = false;
            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();
                cells[i] = cell;

                i++;

                if (i == 5 && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().equalsIgnoreCase("X")) {
                        // if this cell contains an X we have to see if a)
                        // Antrag is invalid b) Person is Teilimmatrikuliert
                        // c) How many months Teilimmatrikuliert

                        exmatrikuliert = true;

                    }
                }
            }

            // we have to see if a) Antrag is invalid b) Person is
            // Teilimmatrikuliert c) How many months Teilimmatrikuliert
            if (exmatrikuliert) {
                Cell semesterCell = cells[6];

                // Semester aufschlsseln nach Jahr und Typ
                String semesterJahrPerson = null;
                String semesterTypPerson = null;
                if (semesterCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    String semester = "" + semesterCell.getNumericCellValue();
                    semesterJahrPerson = semester.substring(0, 4).trim();
                    semesterTypPerson = semester.substring(4, 5).trim();
                    if (semesterTypPerson.equals("1")) {
                        semesterTypPerson = "S";
                    } else if (semesterTypPerson.equals("2")) {
                        semesterTypPerson = "W";
                    }
                }

                // get current selected Semester
                String semesterTypAktuell = SemesterConf.getSemester().getSemesterArt().getBuchstabe().trim();
                String semesterJahrAktuell = SemesterConf.getSemester().getSemesterJahr().trim();

                // if its really the same semester get the date of
                // exmatriculation and check how many months
                if (semesterJahrAktuell.equalsIgnoreCase(semesterJahrPerson)
                        && semesterTypAktuell.equalsIgnoreCase(semesterTypPerson)) {
                    // get cell with exmatriculation date
                    Cell exmatriculationDateCell = cells[5];

                    // Find out date of exmatriculation / round up date to next month
                    int exmatriculationmonth = 0;
                    if (HSSFDateUtil.isCellDateFormatted(exmatriculationDateCell)) {
                        Date date = exmatriculationDateCell.getDateCellValue();

                        //                     Calendar cal = Calendar.getInstance();
                        //                     cal.setTime(date);
                        //                     int monat = cal.get(Calendar.MONTH);

                        SimpleDateFormat df = new SimpleDateFormat("MM");
                        exmatriculationmonth = Integer.parseInt(df.format(date));
                        exmatriculationmonth++;
                    }

                    int monateZuschuss = 0;

                    // Sommersemester
                    if (semesterTypAktuell.equals("S")) {

                        monateZuschuss = 6 - (10 - exmatriculationmonth);

                        // Wintersemester
                    } else if (semesterTypAktuell.equals("W")) {
                        if (exmatriculationmonth > 4) {
                            monateZuschuss = 6 - (16 - exmatriculationmonth);
                        } else {
                            monateZuschuss = 6 - (4 - exmatriculationmonth);
                        }
                    }

                    if (monateZuschuss < 6) {

                        Cell manrCell = cells[0];
                        Cell nachnameCell = cells[2];
                        Cell vornameCell = cells[3];

                        String matrikelnummer = "" + manrCell.getNumericCellValue();
                        matrikelnummer = matrikelnummer.substring(0, matrikelnummer.indexOf('.'));

                        String nachname = getStringFromCell(nachnameCell);
                        String vorname = getStringFromCell(vornameCell);

                        // write Teilzuschuss to DB
                        dbhandler.setAntragToTeilzuschuss(monateZuschuss, matrikelnummer);
                    }
                } else {

                    Cell manrCell = cells[0];
                    Cell nachnameCell = cells[2];
                    Cell vornameCell = cells[3];

                    String matrikelnummer = "" + manrCell.getNumericCellValue();
                    matrikelnummer = matrikelnummer.substring(0, matrikelnummer.indexOf('.'));

                    String nachname = getStringFromCell(nachnameCell);
                    String vorname = getStringFromCell(vornameCell);

                    //
                    dbhandler.denyAntrag(AntragAblehnungsgrund.EXMATRIKULIERT.getBegruendung(), matrikelnummer);

                }

            }

        }
        file.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:org.smart.migrate.util.ExcelUtils.java

/**
 * Add indendity column data for excel sheet
 * @param filename Excel name/*from  w w  w  .j a  v a 2s  .c o  m*/
 * @param sheetName Sheet name
 */
public static void addIndendityColumnData(String filename, String sheetName) {
    try {
        InputStream inp = new FileInputStream(filename);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) {
            Row row = sheet.getRow(sheet.getFirstRowNum());

            if (row != null) {
                int idColumn = cellIndexInRow("id", row);
                if (idColumn == -1) {
                    idColumn = row.getLastCellNum();
                    row.createCell(idColumn).setCellValue("id");
                    for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                        Row r = sheet.getRow(i);
                        Cell cell = r.getCell(idColumn);
                        if (cell == null) {
                            cell = r.createCell(idColumn);
                        }
                        cell.setCellValue(i);
                    }
                    FileOutputStream fileOut = new FileOutputStream(filename);
                    wb.write(fileOut);
                    fileOut.close();
                }
                inp.close();
            }
        }

    } catch (IOException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:org.specrunner.source.excel.SourceFactoryExcel.java

License:Open Source License

/**
 * Read header information./*from  w  w w. j  ava  2s  .  com*/
 * 
 * @param table
 *            The target table.
 * @param caption
 *            The caption element.
 * @param spanMap
 *            Map of span cells.
 * @param ignore
 *            Set of cells to ignore.
 * @param ite
 *            The row iterator.
 * @return The number of columns to read.
 */
protected int headers(Element table, Element caption, Map<String, Dimension> spanMap, Set<String> ignore,
        Iterator<Row> ite) {
    int result = 0;
    Element thead = new Element("thead");
    table.appendChild(thead);
    {
        Element tr = new Element("tr");
        thead.appendChild(tr);
        {
            if (ite.hasNext()) {
                Row row = ite.next();
                result = row.getLastCellNum();
                for (int i = 0; i < result; i++) {
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        String key = cell.getRowIndex() + "," + cell.getColumnIndex();
                        if (ignore.contains(key)) {
                            continue;
                        }
                        Element th = new Element("th");
                        tr.appendChild(th);
                        th.appendChild(String.valueOf(extractVal(cell)));
                        addAttributes(table, caption, tr, th, cell, spanMap.get(key));
                    }
                }
            }
        }
    }
    return result;
}

From source file:org.spionen.james.jamesfile.ExcelJamesFile.java

License:Open Source License

public Map<Long, Subscriber> readFile(File file) throws IOException {
    Map<Long, Subscriber> subscribers = new TreeMap<Long, Subscriber>();
    try {/*from  www. ja v a2s  .  c om*/
        Workbook wb = WorkbookFactory.create(file);
        Sheet s = wb.getSheetAt(0);

        // Take first row, use to check order of fields
        Row firstRow = s.getRow(s.getFirstRowNum());
        FieldType[] order = new FieldType[firstRow.getLastCellNum() - firstRow.getFirstCellNum()];
        int j = 0;
        for (int i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++, j++) {
            Cell c = firstRow.getCell(i);
            if (c != null) {
                if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    String val = new Integer(new Double(c.getNumericCellValue()).intValue()).toString();
                    order[j] = FieldType.getFieldType(val);
                } else {
                    order[j] = FieldType.getFieldType(c.getStringCellValue());
                }
            }
        }
        // Then iterate through the rest of the rows
        if (s.getLastRowNum() > 0) {
            // LastRowNum is 0-indexed, so add 1
            for (int i = s.getFirstRowNum() + 1; i < s.getLastRowNum() + 1; i++) {
                Row r = s.getRow(i);
                Subscriber sub = new Subscriber();
                j = 0;
                // LastCellNum is also 0-indexed
                for (int k = r.getFirstCellNum(); k < r.getLastCellNum() + 1; k++, j++) {
                    Cell c = r.getCell(k);
                    if (c != null) {
                        if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            String val = new Integer(new Double(c.getNumericCellValue()).intValue()).toString();
                            sub.setByField(order[j], val);
                        } else {
                            sub.setByField(order[j], c.getStringCellValue());
                        }
                    }
                }
                subscribers.put(sub.getAbNr(), sub);
            }
        }
        return subscribers;
    } catch (InvalidFormatException ife) {
        // If the file was badly formatted
        throw new IOException(ife);
    }
}

From source file:org.squashtest.tm.service.internal.batchexport.ExcelExporter.java

License:Open Source License

private int registerCuf(Sheet sheet, String code) {

    Row headers = sheet.getRow(0);
    int nextIdx = headers.getLastCellNum();
    headers.createCell(nextIdx).setCellValue(code);

    cufColumnsByCode.put(code, nextIdx);

    return nextIdx;
}

From source file:org.squashtest.tm.service.internal.batchexport.SearchTestCaseExcelExporter.java

License:Open Source License

@Override
protected void createOptionalTestCaseSheetHeaders() {
    Sheet dsSheet = workbook.getSheet(TC_SHEET);
    Row h = dsSheet.getRow(0);
    int cIdx = h.getLastCellNum();
    if (milestonesEnabled) {
        h.createCell(cIdx++).setCellValue(MILESTONE_SEARCH_TC_COLUMNS.getHeader());
    }//from ww w . ja v a  2  s  . c o  m

    for (TemplateColumn t : SEARCH_TC_COLUMNS) {
        h.createCell(cIdx++).setCellValue(t.getHeader());
    }
}

From source file:org.squashtest.tm.service.internal.batchimport.testcase.excel.ExcelWorkbookParserBuilder.java

License:Open Source License

/**
 * Reads the given sheet and appends {@link ColumnDef} to the
 * {@link WorksheetDef} accordingly./*from   ww  w. j a v  a 2  s .  c o  m*/
 *
 * @param wd
 * @param ws
 * @return {@link WorksheetFormatStatus}
 */
private WorksheetFormatStatus populateColumnDefs(WorksheetDef<?> wd, Sheet ws) {
    Row headerRow = findHeaderRow(ws);
    WorksheetFormatStatus worksheetFormatStatus = new WorksheetFormatStatus(wd.getWorksheetType());

    if (headerRow == null) {
        worksheetFormatStatus.addWorksheetMismatch(WorksheetMismatch.MISSING_HEADER);
        return worksheetFormatStatus;
    }

    for (int iCell = 0; iCell < headerRow.getLastCellNum(); iCell++) {
        Cell cell = headerRow.getCell(iCell);

        if (cell != null) {
            try {
                String header = cell.getStringCellValue();
                wd.addColumnDef(header, iCell);
            } catch (IllegalStateException e) {
                // seems this cell aint a string cell...
                LOGGER.trace(
                        "We expected a string cell, but it was not. Not an error case so we silently skip it. Exception message : {}",
                        e.getMessage());
            } catch (ColumnMismatchException cme) {
                worksheetFormatStatus.addMismatch(cme.getType(), cme.getColType());
            }
        }
    }

    return worksheetFormatStatus;
}

From source file:org.squashtest.tm.service.internal.importer.ExcelRowReaderUtils.java

License:Open Source License

public static Map<String, Integer> mapColumns(Sheet sheet) {
    Map<String, Integer> columnsMapping = new HashMap<>();

    Row firstRow = sheet.getRow(0);
    for (int c = 0; c < firstRow.getLastCellNum(); c++) {
        Cell headerCell = firstRow.getCell(c);
        if (headerCell != null) {
            String headerTag = headerCell.getStringCellValue();
            columnsMapping.put(headerTag.toUpperCase(), c);
        }/*from ww  w. j  av  a2s  .co m*/
    }
    return columnsMapping;

}

From source file:org.squashtest.tm.service.internal.importer.ExcelTestCaseParserImpl.java

License:Open Source License

private boolean validateRegularRow(Row row) {

    boolean validated;

    int lastCell = row.getLastCellNum();
    int nbCell = row.getPhysicalNumberOfCells();

    if (lessThan2Cells(lastCell, nbCell)) {
        validated = false;//from  w w  w.  ja va  2s. c o  m
    } else {
        validated = checkCellsContent(row);
    }

    return validated;
}

From source file:org.squashtest.tm.service.internal.importer.ExcelTestCaseParserImpl.java

License:Open Source License

private boolean validateStepRow(Row row) {

    boolean validated;

    int lastCell = row.getLastCellNum();
    int nbCell = row.getPhysicalNumberOfCells();

    String text1 = row.getCell(0) != null ? row.getCell(0).getStringCellValue() : "";
    String text2 = "";
    Cell cell2 = row.getCell(1);/*from  w  w  w  .j a v a2s  .  c  om*/

    if (cell2 != null && Cell.CELL_TYPE_STRING == cell2.getCellType()) {
        text2 = cell2.getStringCellValue();
    } else {
        LOGGER.debug("validateStepRow : Cell 1 of row {} was not of string type, empty string will be used",
                row);
    }

    validated = text1.equals(ACTION_STEP_TAG) && !text2.isEmpty() && lastCell >= 3 && nbCell >= 3;

    return validated;

}