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.databene.benerator.template.xmlanon.XmlAnonInputReader.java

License:Open Source License

private static AnonymizationSetup parseXls(String xlsUri) throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(IOUtil.getInputStreamForURI(xlsUri));
    Sheet sheet = workbook.getSheetAt(0);

    // parse header information
    int varnameColumnIndex = -1;
    ArrayList<String> files = new ArrayList<String>();
    Row headerRow = sheet.getRow(0);
    Assert.notNull(headerRow, "header row");
    for (int i = 0; i <= headerRow.getLastCellNum(); i++) {
        String header = headerRow.getCell(i).getStringCellValue();
        if ("varname".equals(header)) {
            varnameColumnIndex = i;/*from   w w  w .  jav  a  2 s . co  m*/
            break;
        } else {
            if (StringUtil.isEmpty(header))
                throw new ConfigurationError(
                        "Filename missing in column header #" + i + " of Excel document " + xlsUri);
            files.add(header);
        }
    }
    if (varnameColumnIndex == -1)
        throw new ConfigurationError("No 'varname' header defined in Excel document " + xlsUri);
    if (files.size() == 0)
        throw new ConfigurationError("No files specified in Excel document " + xlsUri);

    // parse anonymization rows
    List<Anonymization> anonymizations = new ArrayList<Anonymization>();
    for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) {
        Row row = sheet.getRow(rownum);
        if (XLSUtil.isEmpty(row))
            continue;
        Cell varnameCell = row.getCell(varnameColumnIndex);
        if (varnameCell == null || StringUtil.isEmpty(varnameCell.getStringCellValue()))
            throw new ConfigurationError("'varname' cell empty in table row #" + (rownum + 1));
        Anonymization anon = new Anonymization(varnameCell.getStringCellValue());
        // parse locators
        for (int colnum = 0; colnum < varnameColumnIndex; colnum++) {
            Cell cell = row.getCell(colnum);
            String path = (cell != null ? cell.getStringCellValue() : null);
            if (!StringUtil.isEmpty(path)) {
                List<String> tokens = XPathTokenizer.tokenize(path);
                String entityPath = XPathTokenizer.merge(tokens, 0, tokens.size() - 2);
                String entity = normalizeXMLPath(XPathTokenizer.nodeName(tokens.get(tokens.size() - 2)));
                String attribute = normalizeXMLPath(tokens.get(tokens.size() - 1));
                anon.addLocator(new Locator(files.get(colnum), path, entityPath, entity, attribute));
            }
        }
        // parse settings
        for (int colnum = varnameColumnIndex + 1; colnum < row.getLastCellNum() - 1; colnum += 2) {
            String key = row.getCell(colnum).getStringCellValue();
            String value = row.getCell(colnum + 1).getStringCellValue();
            if (!StringUtil.isEmpty(key) && !StringUtil.isEmpty(value))
                anon.addSetting(key, value);
        }
        anonymizations.add(anon);
    }
    return new AnonymizationSetup(files, anonymizations);
}

From source file:org.databene.formats.xls.XLSLineIterator.java

License:Open Source License

@Override
public synchronized DataContainer<Object[]> next(DataContainer<Object[]> wrapper) {
    if (rowIterator == null || !rowIterator.hasNext())
        return null;
    Row row = rowIterator.next();
    int cellCount = row.getLastCellNum();
    Object[] result = new Object[cellCount];
    for (int cellnum = 0; cellnum < cellCount; cellnum++) {
        if (formatted)
            result[cellnum] = XLSUtil.resolveCellValueAsString(row.getCell(cellnum), emptyMarker, nullMarker,
                    stringPreprocessor);
        else/* w  ww .  j  av  a  2s.  co  m*/
            result[cellnum] = XLSUtil.resolveCellValue(row.getCell(cellnum), emptyMarker, nullMarker,
                    stringPreprocessor);
    }
    return wrapper.setData(result);
}

From source file:org.databene.formats.xls.XLSUtil.java

License:Open Source License

public static void autoSizeColumns(Workbook workbook) {
    int sheetCount = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        int firstRowNum = sheet.getFirstRowNum();
        if (firstRowNum >= 0) {
            Row firstRow = sheet.getRow(firstRowNum);
            for (int cellnum = firstRow.getFirstCellNum(); cellnum < firstRow.getLastCellNum(); cellnum++)
                sheet.autoSizeColumn(cellnum);
        }// w w w  . jav  a2 s  .co m
    }
}

From source file:org.databene.formats.xls.XLSUtil.java

License:Open Source License

public static boolean isEmpty(Row row) {
    if (row == null)
        return true;
    for (int i = 0; i < row.getLastCellNum(); i++)
        if (!isEmpty(row.getCell(i)))
            return false;
    return true;/*from w ww  .ja v  a  2 s. c  o  m*/
}

From source file:org.deri.tarql.XLSToValues.java

License:Apache License

private String[] getRow(Row row) {
    int i = 0;//String array
    String[] csvdata = new String[row.getLastCellNum()];
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {

        Cell cell = cellIterator.next(); //Fetch CELL
        if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
            csvdata[i] = this.formatter.formatCellValue(cell);
        } else {/*from w w w  . j ava2s. co m*/
            csvdata[i] = this.formatter.formatCellValue(cell, this.evaluator);
        }
        i = i + 1;
    }
    return csvdata;
}

From source file:org.dhatim.fastexcel.reader.FastExcelReaderTest.java

License:Apache License

@ParameterizedTest
@ValueSource(strings = { "/xlsx/AutoFilter.xlsx", "/xlsx/calendar_stress_test.xlsx",
        "/xlsx/cell_style_simple.xlsx", "/xlsx/comments_stress_test.xlsx", "/xlsx/custom_properties.xlsx",
        "/xlsx/dates.xlsx", "/xlsx/defined_names_simple.xlsx", "/xlsx/ErrorTypes.xlsx",
        "/xlsx/formula_stress_test.xlsx", "/xlsx/formulae_test_simple.xlsx", "/xlsx/hyperlink_no_rels.xlsx",
        "/xlsx/hyperlink_stress_test_2011.xlsx", "/xlsx/interview.xlsx", "/xlsx/issue.xlsx",
        // "/xlsx/large_strings.xlsx",
        "/xlsx/LONumbers-2010.xlsx", "/xlsx/LONumbers-2011.xlsx", "/xlsx/LONumbers.xlsx",
        "/xlsx/merge_cells.xlsx", "/xlsx/mixed_sheets.xlsx", "/xlsx/named_ranges_2011.xlsx",
        "/xlsx/number_format_entities.xlsx", "/xlsx/phonetic_text.xlsx", "/xlsx/pivot_table_named_range.xlsx",
        "/xlsx/rich_text_stress.xlsx", "/xlsx/RkNumber.xlsx", "/xlsx/smart_tags_2007.xlsx", "/xlsx/sushi.xlsx",
        "/xlsx/text_and_numbers.xlsx", "/xlsx/world.xlsx", "/xlsx/write.xlsx",
        // "/xlsx/xlsx-stream-d-date-cell.xlsx"
})
public void testFile(String file) {
    LOGGER.info("Test " + file);
    try (InputStream inputStream = open(file); InputStream inputStream2 = open(file)) {
        try (ReadableWorkbook excel = new ReadableWorkbook(inputStream);
                Workbook workbook = WorkbookFactory.create(inputStream2)) {
            Iterator<Sheet> it = excel.getSheets().iterator();
            while (it.hasNext()) {
                Sheet sheetDef = it.next();

                org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(sheetDef.getIndex());

                try (Stream<Row> data = sheetDef.openStream()) {
                    Iterator<Row> rowIt = data.iterator();
                    Iterator<org.apache.poi.ss.usermodel.Row> itr = sheet.iterator();

                    while (rowIt.hasNext()) {
                        Row row = rowIt.next();
                        org.apache.poi.ss.usermodel.Row expected = itr.next();

                        assertThat(row.getPhysicalCellCount()).as("physical cell")
                                .isEqualTo(expected.getPhysicalNumberOfCells());
                        assertThat(row.getCellCount()).as("logical cell")
                                .isEqualTo(expected.getLastCellNum() == -1 ? 0 : expected.getLastCellNum());

                        for (int i = 0; i < row.getCellCount(); i++) {
                            Cell cell = row.getCell(i);
                            org.apache.poi.ss.usermodel.Cell expCell = expected.getCell(i);

                            assertThat(cell == null).as("cell defined " + i).isEqualTo(expCell == null);
                            if (cell != null) {
                                String cellAddr = cell.getAddress().toString();
                                assertThat(toCode(cell.getType())).as("cell type code " + cellAddr)
                                        .isEqualTo(expCell.getCellTypeEnum().getCode());

                                if (cell.getType() == CellType.NUMBER) {
                                    BigDecimal n = cell.asNumber();
                                    BigDecimal expN = new BigDecimal(getRawValue(expCell));
                                    assertThat(n).as("Number " + cellAddr).isEqualTo(expN);
                                } else if (cell.getType() == CellType.STRING) {
                                    String s = cell.asString();
                                    String expS = expCell.getStringCellValue();
                                    assertThat(s).as("String " + cellAddr).isEqualTo(expS);
                                }/*w  w w  .j  a  v  a 2  s  . c om*/
                            }
                        }
                    }
                } catch (Throwable e) {
                    throw new RuntimeException("On sheet " + sheetDef.getId() + " " + sheetDef.getName(), e);
                }
            }

        }
    } catch (Throwable e) {
        throw new RuntimeException("On file " + file, e);
    }
}

From source file:org.diffkit.diff.sns.DKPoiSheet.java

License:Apache License

private String[] getColumnNames(int columnCount_) throws IOException {
    Row header = this.getHeaderRow();
    if (header == null)
        return createDefaultColumnNames(columnCount_);

    int headerWidth = header.getLastCellNum();
    if (columnCount_ != headerWidth)
        throw new RuntimeException(
                String.format("headerWidth->%s does not match columnCount_->%s", headerWidth, columnCount_));
    String[] columnNames = new String[columnCount_];
    for (int i = 0; i < columnCount_; i++) {
        Cell cell = header.getCell(i);/*  w w  w . j  a  v  a2 s . c o  m*/
        if (cell == null)
            continue;
        columnNames[i] = cell.toString();
    }
    return columnNames;
}

From source file:org.diffkit.diff.sns.DKPoiSheet.java

License:Apache License

@SuppressWarnings("unchecked")
private List<Type> discoverColumnTypes(List<Row> rows_) {
    if (CollectionUtils.isEmpty(rows_))
        return null;
    List<Type> columnTypes = GrowthList
            .decorate(LazyList.decorate(new ArrayList<Type>(), FactoryUtils.nullFactory()));
    int start = this.hasHeader() ? 1 : 0;
    for (int i = start; i < rows_.size(); i++) {
        Row aRow = rows_.get(i);
        int width = aRow.getLastCellNum();
        for (int j = 0; j < width; j++) {
            Cell cell = aRow.getCell(j);
            if (cell == null)
                continue;
            if (_isDebugEnabled) {
                _log.debug(String.format("cell->%s formatString->%s format->%s", cell.getColumnIndex(),
                        cell.getCellStyle().getDataFormatString(), cell.getCellStyle().getDataFormat()));
            }//from w w w. j a  va 2 s  . c o  m
            Type cellType = mapColumnType(cell);
            Type columnType = columnTypes.get(j);
            if (_isDebugEnabled)
                _log.debug("cellType->{} columnType->{}", cellType, columnType);
            if (columnType == null)
                columnTypes.set(j, cellType);
            else if (columnType != cellType)
                columnTypes.set(j, Type.MIXED);
        }
    }
    return columnTypes;
}

From source file:org.drools.decisiontable.parser.xls.ExcelParser.java

License:Apache License

private void processSheet(Sheet sheet, List<? extends DataListener> listeners) {
    int maxRows = sheet.getLastRowNum();

    CellRangeAddress[] mergedRanges = getMergedCells(sheet);
    DataFormatter formatter = new DataFormatter(Locale.ENGLISH);
    FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

    for (int i = 0; i <= maxRows; i++) {
        Row row = sheet.getRow(i);
        int lastCellNum = row != null ? row.getLastCellNum() : 0;
        newRow(listeners, i, lastCellNum);

        for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
            Cell cell = row.getCell(cellNum);
            if (cell == null) {
                continue;
            }// w ww.  j a  v a2 s.  c  om
            double num = 0;

            CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

            if (merged != null) {
                Cell topLeft = sheet.getRow(merged.getFirstRow()).getCell(merged.getFirstColumn());
                newCell(listeners, i, cellNum, formatter.formatCellValue(topLeft), topLeft.getColumnIndex());

            } else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    String cellValue = null;
                    try {
                        CellValue cv = formulaEvaluator.evaluate(cell);
                        cellValue = getCellValue(cv);
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    } catch (RuntimeException e) {
                        // This is thrown if an external link cannot be resolved, so try the cached value
                        log.warn("Cannot resolve externally linked value: " + formatter.formatCellValue(cell));
                        String cachedValue = tryToReadCachedValue(cell);
                        newCell(listeners, i, cellNum, cachedValue, DataListener.NON_MERGED);
                    }
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    num = cell.getNumericCellValue();
                default:
                    if (num - Math.round(num) != 0) {
                        newCell(listeners, i, cellNum, String.valueOf(num), DataListener.NON_MERGED);
                    } else {
                        newCell(listeners, i, cellNum, formatter.formatCellValue(cell),
                                DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

From source file:org.easybatch.extensions.msexcel.MsExcelRecord.java

License:Open Source License

private String dump(Row row) {
    StringBuilder stringBuilder = new StringBuilder();
    short lastCellNum = row.getLastCellNum();
    for (int i = 0; i < lastCellNum; i++) {
        Cell cell = row.getCell(i);/*from  www .  j a  v  a 2  s.  co  m*/
        stringBuilder.append("\"");
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            stringBuilder.append(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            stringBuilder.append(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            stringBuilder.append(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            stringBuilder.append("");
            break;
        }
        stringBuilder.append("\"");
        if (i < lastCellNum - 1) {
            stringBuilder.append(",");
        }
    }

    return stringBuilder.toString();
}