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:net.sf.excelutils.WorkbookUtils.java

License:Apache License

public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) {

    if (shift == 0)
        return;//  w ww. ja v a 2 s  . c o  m

    // get the from & to row
    int fromRow = row.getRowNum();
    int toRow = row.getRowNum() + rowCount - 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() == row.getRowNum()) {
            if (r.getLastRow() > toRow) {
                toRow = r.getLastRow();
            }
            if (r.getFirstRow() < fromRow) {
                fromRow = r.getFirstRow();
            }
        }
    }

    for (int rownum = fromRow; rownum <= toRow; rownum++) {
        Row curRow = WorkbookUtils.getRow(rownum, sheet);
        int lastCellNum = curRow.getLastCellNum();
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);
            Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift);
            toCell.setCellType(fromCell.getCellType());
            toCell.setCellStyle(fromCell.getCellStyle());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                toCell.setCellErrorValue(fromCell.getErrorCellValue());
                break;
            }
            fromCell.setCellValue("");
            fromCell.setCellType(Cell.CELL_TYPE_BLANK);
            // Workbook wb = new Workbook();
            // CellStyle style = wb.createCellStyle();
            // fromCell.setCellStyle(style);
        }

        // process merged region
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);

            List shiftedRegions = new ArrayList();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress r = sheet.getMergedRegion(i);
                if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) {
                    r.setFirstColumn((short) (r.getFirstColumn() + shift));
                    r.setLastColumn((short) (r.getLastColumn() + shift));
                    // have to remove/add it back
                    shiftedRegions.add(r);
                    sheet.removeMergedRegion(i);
                    // we have to back up now since we removed one
                    i = i - 1;
                }
            }

            // readd so it doesn't get shifted again
            Iterator iterator = shiftedRegions.iterator();
            while (iterator.hasNext()) {
                CellRangeAddress region = (CellRangeAddress) iterator.next();
                sheet.addMergedRegion(region);
            }
        }
    }
}

From source file:net.sf.jasperreports.engine.data.AbstractPoiXlsDataSource.java

License:Open Source License

/**
 *
 *//*from ww w .  j  a v a 2s.c  o  m*/
private void readHeader() {
    Sheet sheet = workbook.getSheetAt(sheetSelection != null ? sheetIndex : 0);
    if (columnNames.size() == 0) {
        Row row = sheet.getRow(recordIndex);
        for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
            Cell cell = row.getCell(columnIndex);
            if (cell != null) {
                columnNames.put(cell.toString(), columnIndex);
            } else {
                columnNames.put("COLUMN_" + columnIndex, columnIndex);
            }
        }
    } else {
        Map<String, Integer> newColumnNames = new LinkedHashMap<String, Integer>();
        for (Iterator<Integer> it = columnNames.values().iterator(); it.hasNext();) {
            Integer columnIndex = it.next();
            Row row = sheet.getRow(recordIndex);
            Cell cell = row.getCell(columnIndex);
            if (cell != null) {
                newColumnNames.put(cell.toString(), columnIndex);
            }
        }
        columnNames = newColumnNames;
    }
}

From source file:net.sf.mzmine.util.io.XSSFExcelWriterReader.java

License:Open Source License

/**
 * Returns the index of the first empty column
 *
 * @param sheet the sheet to write on//from w  w w .ja v a  2 s.  c  om
 * @return a column index
 * @see
 */
public int getFirstEmptyColumn(XSSFSheet sheet) {
    Row row = sheet.getRow(0);
    if (row == null)
        return 0;
    int lastcoll = row.getLastCellNum();
    return (lastcoll == -1) ? 0 : lastcoll;
}

From source file:net.unit8.axebomber.parser.Sheet.java

License:Apache License

public Cell findCell(Pattern p, boolean scanAll) {
    int initialRowNum = (scanAll || tableHeader == null) ? sheet.getFirstRowNum()
            : tableHeader.getBodyRowIndex();
    for (int i = initialRowNum; i <= sheet.getLastRowNum(); i++) {
        org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);
        if (row == null)
            continue;
        for (short j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            org.apache.poi.ss.usermodel.Cell cell = row.getCell(j);
            if (cell == null)
                continue;
            Matcher m = p.matcher(cell.getStringCellValue());
            if (m.find()) {
                return new CellImpl(cell);
            }/*from  w  w  w. ja  va 2s  . c om*/
        }
    }
    throw new CellNotFoundException(p.pattern() + " is not found");
}

From source file:net.unit8.axebomber.parser.Sheet.java

License:Apache License

public Cell findCell(String value, boolean scanAll) {
    int initialRowNum = (scanAll || tableHeader == null) ? sheet.getFirstRowNum()
            : tableHeader.getBodyRowIndex();
    for (int i = initialRowNum; i <= sheet.getLastRowNum(); i++) {
        org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);
        if (row == null)
            continue;
        for (short j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            org.apache.poi.ss.usermodel.Cell cell = row.getCell(j);
            if (cell == null)
                continue;
            Cell cellImpl = new CellImpl(cell);
            if (value.equals(cellImpl.toString())) {
                return cellImpl;
            }/* ww w  .j a v  a2  s.  co  m*/
        }
    }
    throw new CellNotFoundException(value + " is not found");
}

From source file:net.unit8.axebomber.parser.TableHeader.java

License:Apache License

private void scanColumnLabel(Cell beginCell) {
    int rowIndex = beginCell.getRowIndex();
    org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex);
    String currentValue = null;/*from w  w w .j  a v  a2 s.  com*/
    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
        Cell cell = getCell(i, rowIndex);
        if (cell == null)
            continue;
        String label = StringUtils.remove(StringUtils.trim(cell.toString()), "\n");
        if (!cell.toString().equals("")) {
            labelColumns.put(label, i);
            currentValue = label;
        }
        if (currentValue != null)
            columnLabels.put(i, currentValue);
    }
}

From source file:no.hild1.bank.KonverterMottagerregister.java

License:Apache License

private void processInnland(Sheet sheet) throws InvalidFormatException {
    if (sheet.getPhysicalNumberOfRows() > 0) {
        int lastRowNum = 0;
        Row row = null;
        lastRowNum = sheet.getLastRowNum();
        System.out.println("Innland har " + lastRowNum + " rader");
        row = sheet.getRow(0);//from   ww w  . j  ava2  s. c  o m
        int lastCellNum = row.getLastCellNum();
        System.out.println("Innland rad 0 har " + lastCellNum + " celler");
        System.out.println("Kjrer tilregnelighetssjekk");

        String KO = text(row, INNLAND_KONTONR);
        String LE = text(row, INNLAND_LEVNR);
        String NA = text(row, INNLAND_NAVN);
        String A1 = text(row, INNLAND_ADDR1);
        String A2 = text(row, INNLAND_ADDR2);
        String NR = text(row, INNLAND_POSTNUMMER);
        String ST = text(row, INNLAND_POSTSTED);
        if (KO.equals("Kontonr") && LE.equals("Lev.nr") && NA.equals("Navn") && A1.equals("Adresse 1")
                && A2.equals("Adresse 2") && NR.equals("Postnr.") && ST.equals("Poststed")) {
            System.out.println("Frste rad ser OK ut, fortsetter");
            for (int j = 1; j <= lastRowNum; j++) {
                System.out.println("Prosesserer rad " + j + " av " + lastRowNum);
                row = sheet.getRow(j);
                this.rowToXML(row, j);
            }
        } else {
            throw new InvalidFormatException("Kjenner ikke igjen frste rad\n"
                    + "Skulle vrt (1), fant (2)\n(1) 'Kontonr' 'Lev.nr' 'Navn' 'Adresse 1' 'Adresse 2' 'Postnr.' 'Poststed'\n'"
                    + "" + KO + "' '" + LE + "' '" + NA + "' '" + A1 + "' '" + A2 + "' '" + NR + "' '" + ST
                    + "'");
        }
    }
}

From source file:no.hild1.excelsplit.ES.java

private void processSheet(Sheet sheet) throws IOException {
    // Er det 2 eller flere rows (dvs minst header + 1) i arket? 
    if (sheet.getPhysicalNumberOfRows() >= 2) {
        int lastRowNum = 0;
        Row header = null;

        lastRowNum = sheet.getLastRowNum(); // hent siste row det er skrevet i

        System.out.println("Regnearket har " + lastRowNum + " rader");

        header = sheet.getRow(0);/*from w w  w .  j av a 2 s  .  c  om*/

        int lastCellNum = header.getLastCellNum();

        System.out.println("Header har " + lastCellNum + " kolonner");

        String header1 = text(header, 0);
        String header2 = text(header, 1);
        String header3 = text(header, 2);
        String header4 = text(header, 3);

        if (header1.equals("Header 1") && header2.equals("Header 2") && header3.equals("Header 3")
                && header4.equals("Header 4")) {
            System.out.println("Frste rad ser OK ut, fortsetter");
            Row row = null;

            Map<String, XSSFWorkbook> header2types = null;
            for (int j = 1; j <= lastRowNum; j++) {
                System.out.println("Prosesserer rad " + j + " av " + lastRowNum);
                row = sheet.getRow(j);
                handleRow(row, j, header, header2types);
            }
            for (Map.Entry<String, XSSFWorkbook> entry : header2types.entrySet()) {
                FileOutputStream out = new FileOutputStream("Some_name_" + entry.getKey() + ".xlss");
                entry.getValue().write(out);
                out.close();
            }
        }
    }
}

From source file:nu.mine.kino.projects.ExcelScheduleBeanSheet.java

License:Open Source License

/**
 * POI?Af?[^??Ig?AOl?A//from   ww  w. ja  va2  s.c o m
 * 
 * @param headerRow
 * @return
 */
private int findDateLastCellnum(Row headerRow) {
    int initNumber = headerRow.getLastCellNum();
    int ans = initNumber;
    for (int tmpNum = initNumber; tmpNum >= 0; tmpNum--) {
        Object cellValue = PoiUtil.getCellValue(headerRow.getCell(tmpNum));
        if (cellValue != null) {
            ans = tmpNum;
            break;
        }
    }
    return ans;
}

From source file:org.abhishek.simplicitas.util.common.ExcelUtils.java

License:Apache License

/**
 * Called to convert a row of cells into a line of data that can later be
 * output to the CSV file.//from w  w w. j  ava2s  .  co  m
 *
 * @param row
 *            An instance of either the HSSFRow or XSSFRow classes that
 *            encapsulates information about a row of cells recovered from
 *            an Excel workbook.
 */
private void rowToCSV(Row row) {
    Cell cell = null;
    int lastCellNum = 0;
    ArrayList<String> csvLine = new ArrayList<String>();

    // Check to ensure that a row was recovered from the sheet as it is
    // possible that one or more rows between other populated rows could be
    // missing - blank. If the row does contain cells then...
    if (row != null) {

        // Get the index for the right most cell on the row and then
        // step along the row from left to right recovering the contents
        // of each cell, converting that into a formatted String and
        // then storing the String into the csvLine ArrayList.
        lastCellNum = row.getLastCellNum();
        for (int i = 0; i <= lastCellNum; i++) {
            cell = row.getCell(i);
            if (cell == null) {
                csvLine.add("");
            } else {
                if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
                    csvLine.add(this.formatter.formatCellValue(cell));
                } else {
                    csvLine.add(this.formatter.formatCellValue(cell, this.evaluator));
                }
            }
        }
        // Make a note of the index number of the right most cell. This
        // value
        // will later be used to ensure that the matrix of data in the CSV
        // file
        // is square.
        if (lastCellNum > this.maxRowWidth) {
            this.maxRowWidth = lastCellNum;
        }
    }
    this.csvData.add(csvLine);
}