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:guru.qas.martini.report.DefaultState.java

License:Apache License

public void updateLongestExecutions() {
    if (!longestExecutionCells.isEmpty()) {
        for (Cell cell : longestExecutionCells) {
            CellStyle original = cell.getCellStyle();
            Sheet sheet = cell.getSheet();
            Workbook workbook = sheet.getWorkbook();
            CellStyle newStyle = workbook.createCellStyle();
            newStyle.cloneStyleFrom(original);
            int originalFontIndex = original.getFontIndexAsInt();
            Font originalFont = workbook.getFontAt(originalFontIndex);

            Font font = workbook.createFont();
            font.setBold(true);/*from w  ww .j a  va 2 s  .c o  m*/
            font.setColor(IndexedColors.DARK_RED.getIndex());
            font.setFontHeight((short) Math.round(originalFont.getFontHeight() * 1.5));
            newStyle.setFont(font);
            cell.setCellStyle(newStyle);

            Row row = cell.getRow();
            short firstCellNum = row.getFirstCellNum();
            short lastCellNum = row.getLastCellNum();

            for (int i = firstCellNum; i < lastCellNum; i++) {
                Cell rowCell = row.getCell(i);
                original = rowCell.getCellStyle();
                CellStyle borderStyle = workbook.createCellStyle();
                borderStyle.cloneStyleFrom(original);
                borderStyle.setBorderTop(BorderStyle.MEDIUM);
                borderStyle.setBorderBottom(BorderStyle.MEDIUM);

                if (i == cell.getColumnIndex()) {
                    borderStyle.setBorderLeft(BorderStyle.MEDIUM);
                    borderStyle.setBorderRight(BorderStyle.MEDIUM);
                } else if (i == firstCellNum) {
                    borderStyle.setBorderLeft(BorderStyle.MEDIUM);
                } else if (i == lastCellNum - 1) {
                    borderStyle.setBorderRight(BorderStyle.MEDIUM);
                }
                rowCell.setCellStyle(borderStyle);
            }
        }
    }
}

From source file:guru.qas.martini.report.DefaultState.java

License:Apache License

protected void colorRow(short color, Row row) {
    short firstCellNum = row.getFirstCellNum();
    short lastCellNum = row.getLastCellNum();
    for (int i = firstCellNum; i <= lastCellNum; i++) {
        Cell cell = row.getCell(i);// w w w. j  a v  a  2s .c om
        if (null != cell) {
            CellStyle cellStyle = cell.getCellStyle();
            Workbook workbook = cell.getSheet().getWorkbook();
            CellStyle clone = workbook.createCellStyle();

            clone.cloneStyleFrom(cellStyle);
            clone.setFillForegroundColor(color);
            clone.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            BorderStyle borderStyle = cellStyle.getBorderLeftEnum();
            clone.setBorderLeft(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle);
            short borderColor = cellStyle.getLeftBorderColor();
            clone.setLeftBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor);

            borderStyle = cellStyle.getBorderRightEnum();
            clone.setBorderRight(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle);
            borderColor = cellStyle.getRightBorderColor();
            clone.setRightBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor);

            borderStyle = cellStyle.getBorderTopEnum();
            clone.setBorderTop(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle);
            borderColor = cellStyle.getTopBorderColor();
            clone.setTopBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor);

            borderStyle = cellStyle.getBorderBottomEnum();
            clone.setBorderBottom(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle);
            borderColor = cellStyle.getBottomBorderColor();
            clone.setBottomBorderColor(borderColor);
            cell.setCellStyle(clone);
        }
    }
}

From source file:helpers.Excel.ExcelDataFormat.java

public OneExcelSheet marshalAsStructure(Iterator<Row> sheet, FormulaEvaluator evaluator) {
    logger.info("Evaluating formulas.");
    evaluator.evaluateAll();/*from   www .j  av a  2  s  .  co  m*/
    logger.info("Done...");
    OneExcelSheet onesheet = new OneExcelSheet();

    ArrayList<String> headers = null;

    for (Iterator<Row> rowIterator = sheet; rowIterator.hasNext();) {
        Row row = rowIterator.next();

        if (headers == null) {
            headers = new ArrayList<String>();
            int coln = 0;
            for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) {
                try {
                    Cell cell = cellIterator.next();
                    logger.info("Header:" + cell.getStringCellValue());
                    String headn = cell.getStringCellValue().replace(" ", "");
                    headers.add(headn);
                    OneExcelColumn col = new OneExcelColumn(headn, coln);
                    onesheet.columns.add(col);
                } catch (Exception e) {
                    logger.error("Unable to decode cell header. Ex=" + e.getMessage(), e);
                }
                coln++;
            }
        } else {
            ArrayList<Object> newrow = new ArrayList<Object>();
            onesheet.data.add(newrow);

            int coln = 0;

            //for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();)
            for (int cn = 0; cn < row.getLastCellNum(); cn++) {
                Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);
                //Cell cell=cellIterator.next();
                //logger.info("Cell type:"+cell.getCellType());

                switch (evaluator.evaluateInCell(cell).getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        //logger.info(cell.getCellType()+"="+cell.getDateCellValue());
                        newrow.add(cell.getDateCellValue());
                        if (onesheet.columns.size() > coln)
                            onesheet.columns.get(coln).columnTypes[9]++;
                    } else {
                        //logger.info(cell.getCellType()+"="+cell.getNumericCellValue());
                        newrow.add(cell.getNumericCellValue());
                        if (onesheet.columns.size() > coln)
                            onesheet.columns.get(coln).columnTypes[cell.getCellType()]++;
                    }
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:

                    int value = evaluator.evaluateFormulaCell(cell);
                    value = cell.getCachedFormulaResultType();

                    newrow.add(value);
                    if (onesheet.columns.size() > coln)
                        onesheet.columns.get(coln).columnTypes[0]++;
                    break;
                default:
                    //logger.info(cell.getCellType()+"="+cell.getStringCellValue());

                    String cellstr = new String(cell.getStringCellValue().getBytes(), Charset.forName("UTF-8"));
                    newrow.add(cellstr);
                    if (onesheet.columns.size() > coln)
                        onesheet.columns.get(coln).columnTypes[cell.getCellType()]++;

                    break;

                }
                coln++;
            }
        }
    }

    return onesheet;
}

From source file:Import.SheetFrameController.java

@Override
public void handle(Event event) {

    if (event.getSource() == listSheet) {

        if (/*listSheet.getSelectionModel().getSelectedIndex() > -1*/ listSheet.getSelectionModel()
                .getSelectedItem() != null) {
            // clear de la liste des columns
            listColumn.getSelectionModel().clearSelection();
            // update de la liste des colonnes
            // rcupration du nom du sheet slectionn
            String sheetName = (String) listSheet.getSelectionModel().getSelectedItem();
            // rcupration du sheet
            Sheet sheet = book.getSheet(sheetName);
            // rcupration des colonnes du sheet
            int top = sheet.getFirstRowNum();
            Row row = sheet.getRow(top);
            // rcupration du nombre de cellule dans la row
            short first = row.getFirstCellNum();
            short last = row.getLastCellNum();
            // on parse la premiure row entre le first et le last
            // cration du arraylist
            ArrayList al = new ArrayList();
            al.clear();/*from   w w w.j  a  v a2  s . co  m*/

            // boolean exeption
            boolean catchException = false;

            for (int i = first; i < last; i++) {
                Cell cell = row.getCell(i);
                // on rcupre le nom de la cellule
                try {
                    if (cell.getCellType() == CellType.STRING.getCode()) {

                        String value = cell.getStringCellValue();
                        // on ajoute la valeur dans le arraylist
                        al.add(value);
                    }
                } catch (java.lang.NullPointerException nle) {
                    catchException = true;
                }
            }

            if (catchException)
                this.alertException(
                        "Un probleme est survenu dans la lecture d'une ou plusieurs cellules du fichier");

            // on transverse le arraylist dans le observable list
            ObservableList<String> ol = FXCollections.observableArrayList(al);
            // on attache le ol dans le listColumn
            listColumn.setItems(ol);

        }

    }

    if (event.getSource() == listColumn) {
        if (listColumn.getSelectionModel().getSelectedIndex() > -1) {
            // un item est slectionn dans la liste, on enable le bouton suivant
            bSuiv.setDisable(false);
        } else
            bSuiv.setDisable(true);
    }
}

From source file:info.informationsea.tableio.excel.ExcelSheetReader.java

License:Open Source License

@Override
protected Object[] readNextRow() {
    if (sheet.getLastRowNum() < currentRow)
        return null;

    Row row = sheet.getRow(currentRow);
    Object[] rowObjects = new Object[row.getLastCellNum()];
    for (Cell cell : row) {
        Object value;//  ww  w  .  j a  v a2s .c  om
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            value = cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_STRING:
        default:
            value = cell.getStringCellValue();
            break;
        }

        rowObjects[cell.getColumnIndex()] = value;
    }
    currentRow += 1;
    return rowObjects;
}

From source file:invoiceapplication.CopyRowOriginal.java

public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) {
    // Get the source / new row
    Row newRow = worksheet.getRow(destRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exists in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true);
    } else {/* w  w  w.  j  av a  2  s .c om*/
        newRow = worksheet.createRow(destRowNum);
    }
    copyAnyMergedRegions(worksheet, sourceRow, newRow);
    // Loops through source column to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        //Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // if the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Use old cell style
        newCell.setCellStyle(oldCell.getCellStyle());

        // If there is a cell comment, copy
        if (newCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellValue(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        }
    }
}

From source file:invoiceapplication.CopyRowOriginal.java

private static boolean checkIfRowIsEmpty(Row row) {
    if (row == null)
        return true;
    if (row.getLastCellNum() <= 0)
        return true;
    boolean isEmptyRow = true;
    for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
        Cell cell = row.getCell(cellNum);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            isEmptyRow = false;//from   w  w  w.  jav a  2 s  .  c o  m
        }
    }
    return isEmptyRow;
}

From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java

License:Apache License

private void assignColumnIndexes(WorkbookSheet s, List<SheetColumn> undeclaredColumns) {
    logger.debug("assignColumnIndexes({})", s.getSheet().getSheetName());
    undeclaredColumns.clear();/*from  ww  w  .  ja  v  a 2 s  .  c  o m*/
    Sheet sheet = s.getSheet();
    SheetProcessor p = s.getProcessor();

    for (SheetColumn c : p.getColumns()) {
        c.setIndex(-1);
    }

    int firstRow = sheet.getFirstRowNum();
    Row row = sheet.getRow(firstRow);

    int colSize = row.getLastCellNum() + 1;
    for (int i = row.getFirstCellNum(); i < colSize; i++) {

        Cell cell = row.getCell(i);
        if (cell != null) {

            String columnName = cellStringValue(cell);
            if (columnName != null) {
                SheetColumn column = p.findColumnByName(columnName);
                if (column != null) {
                    column.setIndex(i);
                    logger.debug("assignColumnIndexes - {} index = {}", column, i);

                } else {
                    SheetColumn c = new SheetColumn(columnName);
                    c.setIndex(i);
                    undeclaredColumns.add(c);
                }
            }
        }
    }

}

From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java

License:Apache License

private int rank(Sheet sheet, SheetProcessor p) throws SpreadsheetException {

    int count = 0;

    int firstRow = sheet.getFirstRowNum();
    Row row = sheet.getRow(firstRow);

    int colSize = row.getLastCellNum() + 1;
    for (int i = row.getFirstCellNum(); i < colSize; i++) {

        Cell cell = row.getCell(i);//from w w w .  j  a  v  a 2  s .  c o m
        if (cell != null) {
            String text = cellStringValue(cell);
            if (text != null) {
                SheetColumn column = p.findColumnByName(text);
                if (column != null) {
                    count++;
                }
            }
        }
    }

    return count;
}

From source file:iscas.tca.ake.demoapp.mvc.module.tools.fileoperator.ExcelWriter2.java

License:Open Source License

/**
 * TODO:<row>/*from w w  w . j  a v  a  2  s .  com*/
 * @param row
 * @return -1col
 */
public int getLastColNum(int row) {
    Row sheetRow = this.sheet.getRow(row);
    if (sheetRow == null) {
        return 0;
    } else {
        return sheetRow.getLastCellNum();
    }
}