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.eclipse.birt.report.data.oda.excel.impl.util.ExcelFileReader.java

License:Open Source License

public boolean checkXlsEndOfRows() {
    for (int cnt = currentRowIndex + 1; cnt <= (currentRowIndex + ExcelODAConstants.BLANK_LOOK_AHEAD); cnt++) {
        Row row = sheet.getRow(cnt);
        if (row != null) {
            if (maxColumnIndex == 0)
                maxColumnIndex = row.getLastCellNum();

            for (short colIx = 0; colIx < maxColumnIndex; colIx++) {
                Cell cell = row.getCell(colIx);
                String cellVal = getCellValue(cell);
                if (cell != null && cellVal != null && !ExcelODAConstants.EMPTY_STRING.equals(cellVal)) {
                    return false;
                }/*from w  ww  .  java2 s  .  c  o m*/
            }
        }
    }
    return true;
}

From source file:org.eclipse.birt.report.data.oda.excel.impl.util.ExcelFileReader.java

License:Open Source License

public List<String> readLine() throws IOException, OdaException {
    if (!isInitialised)
        initialise();// w w w.jav  a  2s  .  c  o  m

    if (currentRowIndex >= maxRowsInThisSheet) {
        if (!initialiseNextSheet())
            return null;
    }
    List<String> rowData = new ArrayList<String>();
    if (isXlsFile(fileExtension)) {
        Row row = sheet.getRow(currentRowIndex);
        if (row != null) {
            if (maxColumnIndex == 0)
                maxColumnIndex = row.getLastCellNum();

            boolean blankRow = true;
            for (short colIx = 0; colIx < maxColumnIndex; colIx++) {
                Cell cell = row.getCell(colIx);
                String cellVal = getCellValue(cell);
                if (cell != null && cellVal != null && !cellVal.equals(ExcelODAConstants.EMPTY_STRING)) {
                    blankRow = false;
                }
                rowData.add(cellVal);
            }
            if (blankRow) {
                if (checkXlsEndOfRows())
                    return null;
            }

        } else {
            return null;
        }
    } else if (isXlsxFile(fileExtension)) {
        rowData = callback.getRow(currentRowIndex);
    }

    currentRowIndex++;
    return rowData;

}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.EMFFormsSpreadsheetExporterImpl_ITest.java

License:Open Source License

@Test
public void testRender() throws DatatypeConfigurationException {
    final EMFFormsSpreadsheetExporter viewRenderer = new EMFFormsSpreadsheetExporterImpl(new ViewProvider() {
        @Override/* w  w w .  j  a  v a 2 s.c  o  m*/
        public VView getViewModel(EObject viewEobject, VViewModelProperties properties) {
            return getView();
        }
    });
    final User user = getDomainModel();
    final Workbook wb = viewRenderer.render(Collections.singleton(user), null, null);
    final Sheet sheet = wb.getSheetAt(0);
    final Row row = sheet.getRow(3);
    assertEquals(11, row.getLastCellNum());
    for (int i = 0; i < 10; i++) {
        final Cell cell = row.getCell(i + 1);
        switch (i) {
        case 0:
            assertEquals(user.getFirstName(), cell.getStringCellValue());
            break;
        case 1:
            assertEquals(user.getLastName(), cell.getStringCellValue());
            break;
        case 2:
            assertEquals(user.getGender().toString(), cell.getStringCellValue());
            break;
        case 3:
            assertEquals(user.isActive(), cell.getBooleanCellValue());
            break;
        case 4:
            assertEquals(user.getTimeOfRegistration(), cell.getDateCellValue());
            break;
        case 5:
            assertEquals(user.getWeight(), cell.getNumericCellValue(), 0);
            break;
        case 6:
            assertEquals(user.getHeigth(), Double.valueOf(cell.getNumericCellValue()).intValue());
            break;
        case 7:
            assertEquals(user.getNationality().toString(), cell.getStringCellValue());
            break;
        case 8:
            assertEquals(user.getDateOfBirth().toGregorianCalendar().getTime(),
                    DateUtil.getJavaCalendarUTC(cell.getNumericCellValue(), false).getTime());
            break;
        case 9:
            assertEquals(user.getEmail(), cell.getStringCellValue());
            break;
        default:
            fail();
        }
    }
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.transfer.EMFFormsSpreadsheetImporterImpl.java

License:Open Source License

/**
 * Extracts the information from the row and sets the value on the given root EObject.
 *//*  ww w .j  a  v a  2 s  .  c o m*/
// BEGIN COMPLEX CODE
private void extractRowInformation(final Row dmrRow, final Row eObjectRow, final EObject eObject,
        SpreadsheetImportResult errorReports, String sheetname, int sheetId,
        Map<String, VDomainModelReference> sheetColumnToDMRMap,
        Map<VDomainModelReference, EMFFormsSpreadsheetValueConverter> converterMap,
        MigrationInformation information) {
    for (int columnId = 1; columnId < dmrRow.getLastCellNum(); columnId++) {
        final String sheetColId = sheetId + "_" + columnId; //$NON-NLS-1$
        final Cell cell = dmrRow.getCell(columnId);
        if (!sheetColumnToDMRMap.containsKey(sheetColId)) {
            final VDomainModelReference dmr = getDomainModelReference(cell, errorReports, eObject, sheetname,
                    columnId, information);
            sheetColumnToDMRMap.put(sheetColId, dmr);
        }
        final VDomainModelReference dmr = sheetColumnToDMRMap.get(sheetColId);
        if (dmr == null) {
            continue;
        }
        /* resolve dmr */
        if (!resolveDMR(dmr, eObject)) {
            errorReports.reportError(Severity.ERROR,
                    LocalizationServiceHelper.getString(getClass(), "ImportError_DMRResolvementFailed"), //$NON-NLS-1$
                    ErrorFactory.eINSTANCE.createEMFLocation(eObject,
                            ErrorFactory.eINSTANCE.createDMRLocation(dmr)),
                    ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, 0,
                            getStringCellValue(cell)));
            continue;
        }

        /* initiate databinding */
        Setting setting;
        try {
            setting = getSetting(dmr, eObject);
        } catch (final DatabindingFailedException ex) {
            errorReports.reportError(Severity.ERROR,
                    LocalizationServiceHelper.getString(getClass(),
                            MessageFormat.format("ImportError_DatabindingFailed", ex.getMessage())), //$NON-NLS-1$
                    ErrorFactory.eINSTANCE.createEMFLocation(eObject,
                            ErrorFactory.eINSTANCE.createDMRLocation(dmr)),
                    ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, 0,
                            getStringCellValue(cell)));
            continue;
        }

        /* access value converter */
        if (!converterMap.containsKey(dmr)) {
            try {
                final EMFFormsSpreadsheetValueConverter converter = getValueConverter(dmr, eObject);
                converterMap.put(dmr, converter);
            } catch (final EMFFormsConverterException ex) {
                errorReports.reportError(Severity.ERROR,
                        LocalizationServiceHelper.getString(getClass(), "ImportError_NoValueConverter"), //$NON-NLS-1$
                        ErrorFactory.eINSTANCE.createEMFLocation(eObject,
                                ErrorFactory.eINSTANCE.createDMRLocation(dmr)),
                        ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, 0,
                                getStringCellValue(cell)));
                continue;
            }
        }
        final EMFFormsSpreadsheetValueConverter converter = converterMap.get(dmr);

        final EStructuralFeature feature = setting.getEStructuralFeature();

        /* access cell with value */
        Cell rowCell;
        if (feature.isUnsettable()) {
            rowCell = eObjectRow.getCell(columnId, Row.RETURN_NULL_AND_BLANK);
        } else {
            rowCell = eObjectRow.getCell(columnId, Row.CREATE_NULL_AS_BLANK);
        }

        if (rowCell == null) {
            /* no error -> unsettable feature */
            errorReports.getSettingToSheetMap()
                    .add(ErrorFactory.eINSTANCE.createSettingToSheetMapping(createSettingLocation(setting),
                            ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId,
                                    eObjectRow.getRowNum(), getStringCellValue(cell))));
            continue;
        }

        /* convert value */
        Object convertedValue;
        try {
            convertedValue = converter.getCellValue(rowCell, feature);
        } catch (final EMFFormsConverterException ex) {
            errorReports.reportError(Severity.ERROR,
                    MessageFormat.format(
                            LocalizationServiceHelper.getString(getClass(),
                                    "ImportError_ValueConversionFailed"), //$NON-NLS-1$
                            ex.getMessage()),
                    ErrorFactory.eINSTANCE.createEMFLocation(eObject, createSettingLocation(setting),
                            ErrorFactory.eINSTANCE.createDMRLocation(dmr)),
                    ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, eObjectRow.getRowNum(),
                            getStringCellValue(cell)));
            continue;
        }

        /* check converted value */
        if (convertedValue != null) {
            if (!checkTypes(feature, convertedValue)) {
                errorReports.reportError(Severity.ERROR,
                        LocalizationServiceHelper.getString(getClass(), "ImportError_InvalidType"), //$NON-NLS-1$
                        ErrorFactory.eINSTANCE.createEMFLocation(eObject, createSettingLocation(setting),
                                ErrorFactory.eINSTANCE.createDMRLocation(dmr)),
                        ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, eObjectRow.getRowNum(),
                                getStringCellValue(cell)));
                continue;
            }
        }

        /* set value */
        setting.set(convertedValue);

        errorReports.getSettingToSheetMap()
                .add(ErrorFactory.eINSTANCE.createSettingToSheetMapping(createSettingLocation(setting),
                        ErrorFactory.eINSTANCE.createSheetLocation(sheetname, columnId, eObjectRow.getRowNum(),
                                getStringCellValue(cell))));
    }
}

From source file:org.eclipse.jubula.client.core.businessprocess.importfilter.ExcelImportFilter.java

License:Open Source License

/**
 * parses a file and returns the data as DataTable structure
 * //from  www  .ja v  a  2  s. c o  m
 * @param dataDir
 *      directory for data files
 * @param file
 *      data source File
 * @return
 *      filled TestDataManager with new data
 * @throws IOException
 *      error occurred while reading data source
 */
public DataTable parse(File dataDir, String file) throws IOException, DataReadException {

    DataTable filledDataTable;
    final FileInputStream inStream = findDataFile(dataDir, file);
    try {
        Workbook wb;
        if (file.endsWith(".xls")) { //$NON-NLS-1$
            POIFSFileSystem fs = new POIFSFileSystem(inStream);
            wb = new HSSFWorkbook(fs);
        } else {
            wb = new XSSFWorkbook(inStream);
        }
        // Open the first sheet
        Sheet sheet = wb.getSheetAt(0);
        final int lastRowNum = sheet.getLastRowNum();
        final int firstRowNum = sheet.getFirstRowNum();
        // iterate over rows
        if (sheet.getRow(firstRowNum) == null) {
            return new DataTable(0, 0);
        }
        final int height = lastRowNum - firstRowNum + 1;
        final int width = sheet.getRow(firstRowNum).getLastCellNum()
                - sheet.getRow(firstRowNum).getFirstCellNum();
        filledDataTable = new DataTable(height, width);
        for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            final short lastCellNum = row.getLastCellNum();
            final short firstCellNum = row.getFirstCellNum();
            for (int cellNr = firstCellNum; cellNr < lastCellNum; cellNr++) {
                Cell cell = row.getCell(cellNr);
                String cellString = getExcelCellString(cell);
                filledDataTable.updateDataEntry(rowNum, cellNr, cellString);
            }
        }
    } catch (IOException e) {
        throw e; // just pass on, don't fall through to Throwable
    } catch (Throwable t) {
        throw new DataReadException(t);
    } finally {
        inStream.close();
    }

    /* fix issues with documents saved via open office 
     * if the document has been saved via open office it contains one ore many
     * "null" columns at the end of the data table; these columns are truncated 
     */
    while ((filledDataTable.getColumnCount() > 0)
            && (StringUtils.isBlank(filledDataTable.getData(0, filledDataTable.getColumnCount() - 1)))) {
        int newHeight = filledDataTable.getRowCount();
        int newWidth = filledDataTable.getColumnCount() - 1;
        DataTable cleanedFilledDataTable = new DataTable(newHeight, newWidth);
        for (int i = 0; i < newHeight; i++) {
            for (int j = 0; j < newWidth; j++) {
                cleanedFilledDataTable.updateDataEntry(i, j, filledDataTable.getData(i, j));
            }
        }
        filledDataTable = cleanedFilledDataTable;
    }

    return filledDataTable;
}

From source file:org.eclipse.rcptt.ecl.data.apache.poi.impl.internal.commands.ReadExcelFileService.java

License:Open Source License

private void readRow(Table table, Row row) {
    org.eclipse.rcptt.ecl.data.objects.Row tableRow = ObjectsFactory.eINSTANCE.createRow();
    int maxCellnum = row == null ? 0 : row.getLastCellNum();
    for (int cellnum = 0; cellnum < maxCellnum; cellnum++) {
        Cell cell = row.getCell(cellnum);
        tableRow.getValues().add(ExcelFileService.getCellValue(cell));
    }/* ww w  . ja  va2s  .co m*/
    table.getRows().add(tableRow);
}

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy sheets./*from w w  w .  j av a 2  s  . c o m*/
 *
 * @param _newSheet the new sheet
 * @param _sheet the sheet
 * @param _copyStyle the copy style
 */
protected void copySheets(final Sheet _newSheet, final Sheet _sheet, final boolean _copyStyle) {
    int maxColumnNum = 0;
    final Map<Integer, CellStyle> styleMap = _copyStyle ? new HashMap<>() : null;
    for (int i = _sheet.getFirstRowNum(); i <= _sheet.getLastRowNum(); i++) {
        final Row srcRow = _sheet.getRow(i);
        final Row destRow = _newSheet.createRow(i);
        if (srcRow != null) {
            copyRow(_sheet, _newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        _newSheet.setColumnWidth(i, _sheet.getColumnWidth(i));
    }
}

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy row./* w ww . j a  v  a  2 s. c om*/
 *
 * @param _srcSheet the src sheet
 * @param _destSheet the dest sheet
 * @param _srcRow the src row
 * @param _destRow the dest row
 * @param _styleMap the style map
 */
protected void copyRow(final Sheet _srcSheet, final Sheet _destSheet, final Row _srcRow, final Row _destRow,
        final Map<Integer, CellStyle> _styleMap) {
    final Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<>();
    _destRow.setHeight(_srcRow.getHeight());
    final int deltaRows = _destRow.getRowNum() - _srcRow.getRowNum();
    for (int j = _srcRow.getFirstCellNum(); j <= _srcRow.getLastCellNum(); j++) {
        final Cell oldCell = _srcRow.getCell(j); // ancienne cell
        Cell newCell = _destRow.getCell(j); // new cell
        if (oldCell != null) {
            if (newCell == null) {
                newCell = _destRow.createCell(j);
            }
            copyCell(oldCell, newCell, _styleMap);
            final CellRangeAddress mergedRegion = getMergedRegion(_srcSheet, _srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                final CellRangeAddress newMergedRegion = new CellRangeAddress(
                        mergedRegion.getFirstRow() + deltaRows, mergedRegion.getLastRow() + deltaRows,
                        mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                final CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    _destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }
}

From source file:org.formiz.core.input.xls.RowUtils.java

License:Open Source License

/**
 * Check is row is not blank.//from   w  ww  . j  a  v  a2 s .  com
 *
 * @param row
 * @return true if row is not blank.
 */
public static boolean isNotBlank(Row row) {
    boolean nonBlankRowFound = false;
    for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);
        if (cell != null && row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
            nonBlankRowFound = true;
            break;
        }
    }
    return nonBlankRowFound;
}

From source file:org.generationcp.middleware.operation.parser.WorkbookParser.java

License:Open Source License

public void removeObsoleteColumnsInExcelWorkbook(final org.apache.poi.ss.usermodel.Workbook excelWorkbook,
        final List<String> obsoleteVariableNames) {

    // Get the Observation sheet
    final Sheet observationSheet = excelWorkbook.getSheetAt(WorkbookParser.OBSERVATION_SHEET);

    // The first row is the header that contains column names
    final Row headerRow = observationSheet.getRow(observationSheet.getFirstRowNum());

    for (int columnIndex = 0; columnIndex <= headerRow.getLastCellNum(); columnIndex++) {
        final Cell cell = headerRow.getCell(columnIndex);
        if (cell != null) {
            final String columnName = cell.getStringCellValue();
            if (obsoleteVariableNames.contains(columnName)) {
                // Delete the column of the obsolete variable.
                PoiUtil.deleteColumn(observationSheet, columnIndex);
                // Decrement the column index since we deleted a column
                columnIndex--;/*from   www .j  a v a2s . co  m*/
            }
        }

    }

}