Example usage 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:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetAddColumn.java

License:Open Source License

private void addColumn(Sheet sheet, cfArrayData data, boolean bInsert, int rowNo, int column)
        throws dataNotSupportedException {
    /*/* w  w  w  .ja  v  a 2  s.  c o m*/
    * Run around the loop
    */
    for (int r = 0; r < data.size(); r++) {
        int rowCurrent = rowNo + r;

        // Create the necessary row
        Row row = sheet.getRow(rowCurrent);
        if (row == null) {
            SheetUtility.insertRow(sheet, rowCurrent);
            row = sheet.getRow(rowCurrent);
        }

        // We will have to shift the cells up one
        if (bInsert && column < row.getLastCellNum()) {
            SheetUtility.shiftCellRight(row, column);
        }

        Cell cell = row.createCell(column);

        // Set the data; trying to see if its a number
        SheetUtility.setCell(cell, data.getElement(r + 1));
    }
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetAddColumn.java

License:Open Source License

private void addColumn(Sheet sheet, String[] colData, boolean bInsert, int rowNo, int column) {
    /*/*from  www.  j  ava  2  s  .  c  om*/
    * Run around the loop
    */
    for (int r = 0; r < colData.length; r++) {
        int rowCurrent = rowNo + r;

        // Create the necessary row
        Row row = sheet.getRow(rowCurrent);
        if (row == null) {
            SheetUtility.insertRow(sheet, rowCurrent);
            row = sheet.getRow(rowCurrent);
        }

        // We will have to shift the cells up one
        if (bInsert && column < row.getLastCellNum()) {
            SheetUtility.shiftCellRight(row, column);
        }

        Cell cell = row.createCell(column);

        // Set the data; trying to see if its a number
        try {
            cell.setCellValue(Double.valueOf(colData[r]));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } catch (Exception e) {
            cell.setCellValue(colData[r]);
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
    }
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetFormatRow.java

License:Open Source License

private void formatRow(Sheet sheet, CellStyle style, int rowNo) {
    Row row = sheet.getRow(rowNo);
    if (row == null)
        return;/*  www .j av a 2  s . c o  m*/

    int cellInRow = row.getLastCellNum() + 1;

    for (int c = 0; c < cellInRow; c++) {
        Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);
        cell.setCellStyle(style);
    }
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java

License:Open Source License

public static int getMaxColumn(Sheet sheet) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;// w  w w.  j  a  va 2 s  .  c  o m
    }
    return maxColumn;
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java

License:Open Source License

/**
 * Given a sheet, this method inserts a row to a sheet and moves
 * all the rows to the bottom down one//from w w  w  . j  a va  2  s .c o m
 * 
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param rowPosition
 */
public static void insertRow(Sheet sheet, int rowPosition) {

    //Row Position maybe beyond the last
    if (rowPosition > sheet.getLastRowNum()) {
        sheet.createRow(rowPosition);
        return;
    }

    //Create a new Row at the end
    sheet.createRow(sheet.getLastRowNum() + 1);
    Row row;

    for (int r = sheet.getLastRowNum(); r > rowPosition; r--) {
        row = sheet.getRow(r);
        if (row == null)
            row = sheet.createRow(r);

        //Clear the row
        for (int c = 0; c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null)
                row.removeCell(cell);
        }

        //Move the row
        Row previousRow = sheet.getRow(r - 1);
        if (previousRow == null) {
            sheet.createRow(r - 1);
            continue;
        }

        for (int c = 0; c < previousRow.getLastCellNum(); c++) {
            Cell cell = previousRow.getCell(c);
            if (cell != null) {
                Cell newCell = row.createCell(c, cell.getCellType());
                cloneCell(newCell, cell);
            }
        }
    }

    //Clear the newly inserted row
    row = sheet.getRow(rowPosition);
    for (int c = 0; c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c);
        if (cell != null)
            row.removeCell(cell);
    }
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java

License:Open Source License

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * /*  www  . j  a  va2 s  .com*/
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;

        // if the row doesn't have this many columns then we are good; next!
        if (lastColumn < columnToDelete)
            continue;

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null)
                row.removeCell(oldCell);

            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int c = 0; c < maxColumn; c++) {
        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
    }
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java

License:Open Source License

/**
 * Shifts all the cells from the specified column to the right
 * @param row/*from   w  w w. j  a  va  2 s  .c o  m*/
 * @param column
 */
public static void shiftCellRight(Row row, int column) {
    int lastColumnCell = row.getLastCellNum();

    if (column > lastColumnCell)
        return;

    for (int x = lastColumnCell; x > column; --x) {
        Cell cell = row.getCell(x - 1);
        if (cell == null)
            continue;

        Cell newCell = row.createCell(x, cell.getCellType());
        cloneCell(newCell, cell);
        row.removeCell(cell);
    }
}

From source file:org.aludratest.app.excelwizard.WorkbookTracker.java

License:Apache License

void validate() {
    CellStyle warningCellStyle = this.workbook.createCellStyle();
    warningCellStyle.setFillForegroundColor(HSSFColor.RED.index);
    warningCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (Map.Entry<String, List<String>> headerConfig : expectedSheetColumnHeaders.entrySet()) {
        String sheetName = headerConfig.getKey();
        Sheet sheet = this.workbook.getSheet(sheetName);
        Row headerRow = sheet.getRow(0);
        List<String> expectedHeaders = headerConfig.getValue();
        int lastCellNum = headerRow.getLastCellNum();
        int firstCellNum = headerRow.getFirstCellNum();
        for (int i = firstCellNum; i < lastCellNum; i++) {
            Cell headerCell = headerRow.getCell(i);
            if (headerCell != null) {
                String actualHeader = headerCell.getStringCellValue();
                if (actualHeader != null && actualHeader.trim().length() > 0) {
                    if (!expectedHeaders.contains(actualHeader)) {
                        this.warnings.add("Unmappable column '" + actualHeader + "' in sheet '" + sheetName
                                + "' of file '" + file.getName() + "'");
                        headerCell.setCellStyle(warningCellStyle);
                        this.status = STATUS_MODIFIED;
                    }/* w  w  w .  j  a v  a 2  s.co m*/
                }
            }
        }
    }
}

From source file:org.aludratest.testcase.data.impl.TestConfigInfoHelper.java

License:Apache License

private static int findConfigColumn(Sheet sheet, String excelFilePath) {
    int configColumn = -1;
    Row headerRow = sheet.getRow(0);
    if (headerRow == null) {
        throw new AutomationException(
                "Config tab '" + CONFIG_TAB_NAME + "' is empty in Excel document " + excelFilePath);
    }//from   w  w  w .j a  v  a  2 s  .  c om
    for (int i = 0; i <= headerRow.getLastCellNum(); i++) {
        if (CONFIG_COLUMN_NAME.equals(String.valueOf(headerRow.getCell(i)))) {
            configColumn = i;
            break;
        }
    }
    if (configColumn == -1) {
        throw new AutomationException("No '" + CONFIG_COLUMN_NAME + "' column found" + " in '" + CONFIG_TAB_NAME
                + "' tab of file " + excelFilePath);
    }
    return configColumn;
}

From source file:org.aludratest.testcase.data.impl.TestConfigInfoHelper.java

License:Apache License

private static int findIgnoreColumnIndex(Sheet sheet, String excelFilePath) {
    int index = -1;
    Row headerRow = sheet.getRow(0);
    if (headerRow == null) {
        throw new AutomationException(
                "Config tab '" + CONFIG_TAB_NAME + "' is empty in Excel document " + excelFilePath);
    }// www .jav  a 2  s  .co m
    for (int i = 0; i <= headerRow.getLastCellNum(); i++) {
        if (IGNORE_COLUMN_NAME.equals(String.valueOf(headerRow.getCell(i)))) {
            index = i;
            break;
        }
    }
    return index;
}