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.pentaho.di.trans.steps.excelinput.poisax.SaxPoiSheet.java

License:Apache License

public KCell[] getRow(int rownr) {
    if (rownr < sheet.getFirstRowNum()) {
        return new KCell[] {};
    } else if (rownr > sheet.getLastRowNum()) {
        throw new ArrayIndexOutOfBoundsException("Read beyond last row: " + rownr);
    }//w w  w .  j  a  va  2  s .  c om
    Row row = sheet.getRow(rownr);
    if (row == null) { // read an empty row
        return new KCell[] {};
    }
    int cols = row.getLastCellNum();
    if (cols < 0) { // this happens if a row has no cells, POI returns -1 then
        return new KCell[] {};
    }
    SaxPoiCell[] xlsCells = new SaxPoiCell[cols];
    for (int i = 0; i < cols; i++) {
        Cell cell = row.getCell(i);
        if (cell != null) {
            xlsCells[i] = new SaxPoiCell(cell);
        }
    }
    return xlsCells;
}

From source file:org.pentaho.reporting.ui.datasources.table.ImportFromFileTask.java

License:Open Source License

private void importFromFile(final File file, final boolean firstRowIsHeader) {
    final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length()));
    try {//from w  w w  .j  a  v a 2  s .  co m
        final InputStream fin = new FileInputStream(file);
        try {
            IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout);
        } finally {
            fin.close();
        }

        if (Thread.currentThread().isInterrupted()) {
            return;
        }

        final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
        int sheetIndex = 0;
        if (workbook.getNumberOfSheets() > 1) {
            final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent);
            if (selectorDialog.performSelection()) {
                sheetIndex = selectorDialog.getSelectedIndex();
            } else {
                return;
            }
        }

        final TypedTableModel tableModel = new TypedTableModel();
        final Sheet sheet = workbook.getSheetAt(sheetIndex);
        final Iterator rowIterator = sheet.rowIterator();

        if (firstRowIsHeader) {
            if (rowIterator.hasNext()) {
                final Row headerRow = (Row) rowIterator.next();
                final short cellCount = headerRow.getLastCellNum();
                for (short colIdx = 0; colIdx < cellCount; colIdx++) {
                    final Cell cell = headerRow.getCell(colIdx);
                    if (cell != null) {
                        while (colIdx > tableModel.getColumnCount()) {
                            tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
                                    String.valueOf(tableModel.getColumnCount())), Object.class);
                        }

                        final RichTextString string = cell.getRichStringCellValue();
                        if (string != null) {
                            tableModel.addColumn(string.getString(), Object.class);
                        } else {
                            tableModel.addColumn(
                                    Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)),
                                    Object.class);
                        }
                    }
                }
            }
        }

        Object[] rowData = null;
        while (rowIterator.hasNext()) {
            final Row row = (Row) rowIterator.next();
            final short cellCount = row.getLastCellNum();
            if (cellCount == -1) {
                continue;
            }
            if (rowData == null || rowData.length != cellCount) {
                rowData = new Object[cellCount];
            }

            for (short colIdx = 0; colIdx < cellCount; colIdx++) {
                final Cell cell = row.getCell(colIdx);

                final Object value;
                if (cell != null) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        final RichTextString string = cell.getRichStringCellValue();
                        if (string != null) {
                            value = string.getString();
                        } else {
                            value = null;
                        }
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        final CellStyle hssfCellStyle = cell.getCellStyle();
                        final short dataFormat = hssfCellStyle.getDataFormat();
                        final String dataFormatString = hssfCellStyle.getDataFormatString();
                        if (isDateFormat(dataFormat, dataFormatString)) {
                            value = cell.getDateCellValue();
                        } else {
                            value = cell.getNumericCellValue();
                        }
                    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                        value = cell.getBooleanCellValue();
                    } else {
                        value = cell.getStringCellValue();
                    }
                } else {
                    value = null;
                }

                if (value != null && "".equals(value) == false) {
                    while (colIdx >= tableModel.getColumnCount()) {
                        tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
                                String.valueOf(tableModel.getColumnCount())), Object.class);
                    }
                }

                rowData[colIdx] = value;
            }

            if (Thread.currentThread().isInterrupted()) {
                return;
            }

            tableModel.addRow(rowData);
        }

        final int colCount = tableModel.getColumnCount();
        final int rowCount = tableModel.getRowCount();
        for (int col = 0; col < colCount; col++) {
            Class type = null;
            for (int row = 0; row < rowCount; row += 1) {
                final Object value = tableModel.getValueAt(row, col);
                if (value == null) {
                    continue;
                }
                if (type == null) {
                    type = value.getClass();
                } else if (type != Object.class) {
                    if (type.isInstance(value) == false) {
                        type = Object.class;
                    }
                }
            }

            if (Thread.currentThread().isInterrupted()) {
                return;
            }

            if (type != null) {
                tableModel.setColumnType(col, type);
            }
        }

        parent.importComplete(tableModel);
    } catch (Exception e) {
        parent.importFailed(e);
        logger.error("Failed to import spreadsheet", e); // NON-NLS
    }
}

From source file:org.powertools.engine.sources.ExcelTestSource.java

License:Open Source License

private int getNrOfParts(Row row) {
    int nrOfParts = row.getLastCellNum();

    while (nrOfParts > 0) {
        Cell cell = row.getCell(nrOfParts - 1);
        if (cell != null && !cell.toString().isEmpty()) {
            break;
        } else {/*from www . j a  va  2s .  co  m*/
            --nrOfParts;
        }
    }
    return nrOfParts;
}

From source file:org.primefaces.component.export.ExcelExporter.java

License:Open Source License

protected void addColumnValue(Row row, String value) {
    int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
    Cell cell = row.createCell(cellIndex);

    cell.setCellValue(createRichTextString(value));

    if (facetStyle != null) {
        cell.setCellStyle(facetStyle);//from  w w  w  . j av a2 s  . c om
    }
}

From source file:org.primefaces.component.export.ExcelExporter.java

License:Open Source License

protected void addColumnValue(Row row, List<UIComponent> components, UIColumn column) {
    int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
    Cell cell = row.createCell(cellIndex);
    FacesContext context = FacesContext.getCurrentInstance();

    if (column.getExportFunction() != null) {
        cell.setCellValue(createRichTextString(exportColumnByFunction(context, column)));
    } else {//w w w  .  ja v  a  2s . c o  m
        StringBuilder builder = new StringBuilder();
        for (UIComponent component : components) {
            if (component.isRendered()) {
                String value = exportValue(context, component);

                if (value != null)
                    builder.append(value);
            }
        }

        cell.setCellValue(createRichTextString(builder.toString()));
    }

    if (cellStyle != null) {
        cell.setCellStyle(cellStyle);
    }
}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();/* w ww.jav  a  2 s .  com*/
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();

    facetStyleLeftAlign = wb.createCellStyle();
    facetStyleCenterAlign = wb.createCellStyle();
    facetStyleRightAlign = wb.createCellStyle();
    cellStyleLeftAlign = wb.createCellStyle();
    cellStyleCenterAlign = wb.createCellStyle();
    cellStyleRightAlign = wb.createCellStyle();

    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

            if (table.getHeader() != null && !subTable) {
                tableFacet(context, sheet, table, columnsCount, "header");

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable)
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

protected void exportCells(DataList list, Sheet sheet) {
    int sheetRowIndex = sheet.getLastRowNum() + 1;
    Row row = sheet.createRow(sheetRowIndex);

    facetStyleLeftAlign.setAlignment((short) CellStyle.ALIGN_LEFT);
    facetStyleCenterAlign.setAlignment((short) CellStyle.ALIGN_CENTER);
    facetStyleRightAlign.setAlignment((short) CellStyle.ALIGN_RIGHT);
    cellStyleLeftAlign.setAlignment((short) CellStyle.ALIGN_LEFT);
    cellStyleCenterAlign.setAlignment((short) CellStyle.ALIGN_CENTER);
    cellStyleRightAlign.setAlignment((short) CellStyle.ALIGN_RIGHT);

    for (UIComponent component : list.getChildren()) {
        if (component instanceof Column) {
            UIColumn column = (UIColumn) component;
            for (UIComponent childComponent : column.getChildren()) {
                int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
                Cell cell = row.createCell(cellIndex);
                if (component.isRendered()) {
                    String value = component == null ? ""
                            : exportValue(FacesContext.getCurrentInstance(), childComponent);
                    cell.setCellValue(new XSSFRichTextString(value));
                    cell.setCellStyle(cellStyle);
                }/*from w  w w  .  j  av a  2  s.c o  m*/
            }

        } else {
            int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
            Cell cell = row.createCell(cellIndex);
            if (component.isRendered()) {
                String value = component == null ? ""
                        : exportValue(FacesContext.getCurrentInstance(), component);
                cell.setCellValue(new XSSFRichTextString(value));
                cell.setCellStyle(cellStyle);
            }
        }
    }

}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

protected void addColumnValue(Row row, UIComponent component, String type) {
    int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
    Cell cell = row.createCell(cellIndex);
    String value = component == null ? "" : exportValue(FacesContext.getCurrentInstance(), component);
    cell.setCellValue(new XSSFRichTextString(value));
    if (type.equalsIgnoreCase("facet")) {
        addFacetAlignments(component, cell);
    } else {//from   w ww .  j a  v  a2  s .co m
        addColumnAlignments(component, cell);
    }

}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

protected void addColumnValue(Row row, List<UIComponent> components, String type) {
    int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
    Cell cell = row.createCell(cellIndex);
    StringBuilder builder = new StringBuilder();
    FacesContext context = FacesContext.getCurrentInstance();

    for (UIComponent component : components) {
        if (component.isRendered()) {
            String value = exportValue(context, component);

            if (value != null) {
                builder.append(value);/* w ww.  ja  va 2  s.  com*/
            }
        }
    }

    cell.setCellValue(new XSSFRichTextString(builder.toString()));

    if (type.equalsIgnoreCase("facet")) {
        for (UIComponent component : components) {
            addFacetAlignments(component, cell);
        }
    } else {
        for (UIComponent component : components) {
            addColumnAlignments(component, cell);
        }
    }

}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();//w w  w  . j  a  va2s.  com
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();
    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBold(true);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

            if (table.getHeader() != null && !subTable) {
                tableFacet(context, sheet, table, columnsCount, "header");

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable) {
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }
    }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}