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:adams.data.io.input.ExcelSpreadSheetReader.java

License:Open Source License

/**
 * Reads the spreadsheet content from the specified file.
 *
 * @param in      the input stream to read from
 * @return      the spreadsheets or null in case of an error
 *//*  ww w.  java  2 s.c  o  m*/
@Override
protected List<SpreadSheet> doReadRange(InputStream in) {
    List<SpreadSheet> result;
    int[] indices;
    Workbook workbook;
    Sheet sheet;
    SpreadSheet spsheet;
    Row exRow;
    Cell exCell;
    adams.data.spreadsheet.Row spRow;
    int i;
    int n;
    int cellType;
    DateFormat dformat;
    boolean numeric;
    int dataRowStart;
    int firstRow;
    int lastRow;
    List<String> header;

    result = new ArrayList<>();

    workbook = null;
    dformat = DateUtils.getTimestampFormatter();
    try {
        workbook = WorkbookFactory.create(in);
        m_SheetRange.setMax(workbook.getNumberOfSheets());
        indices = m_SheetRange.getIntIndices();
        firstRow = m_FirstRow - 1;
        dataRowStart = getNoHeader() ? firstRow : firstRow + 1;
        for (int index : indices) {
            if (m_Stopped)
                break;

            spsheet = m_SpreadSheetType.newInstance();
            spsheet.setDataRowClass(m_DataRowType.getClass());
            result.add(spsheet);

            if (isLoggingEnabled())
                getLogger().info("sheet: " + (index + 1));

            sheet = workbook.getSheetAt(index);
            if (sheet.getLastRowNum() == 0) {
                getLogger().severe("No rows in sheet #" + index);
                return null;
            }
            spsheet.setName(sheet.getSheetName());

            // header
            if (isLoggingEnabled())
                getLogger().info("header row");
            exRow = sheet.getRow(firstRow);
            if (exRow == null) {
                getLogger().warning("No data in sheet #" + (index + 1) + "?");
            } else if (exRow != null) {
                spRow = spsheet.getHeaderRow();
                m_TextColumns.setMax(exRow.getLastCellNum());
                if (getNoHeader()) {
                    header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders);
                    for (i = 0; i < header.size(); i++)
                        spRow.addCell("" + (i + 1)).setContent(header.get(i));
                } else {
                    if (!m_CustomColumnHeaders.trim().isEmpty()) {
                        header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders);
                        for (i = 0; i < header.size(); i++)
                            spRow.addCell("" + (i + 1)).setContent(header.get(i));
                    } else {
                        for (i = 0; i < exRow.getLastCellNum(); i++) {
                            if (m_Stopped)
                                break;
                            exCell = exRow.getCell(i);
                            if (exCell == null) {
                                spRow.addCell("" + (i + 1)).setMissing();
                                continue;
                            }
                            numeric = !m_TextColumns.isInRange(i);
                            switch (exCell.getCellType()) {
                            case Cell.CELL_TYPE_BLANK:
                            case Cell.CELL_TYPE_ERROR:
                                spRow.addCell("" + (i + 1)).setContent("column-" + (i + 1));
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(exCell))
                                    spRow.addCell("" + (i + 1)).setContent(new DateTime(
                                            HSSFDateUtil.getJavaDate(exCell.getNumericCellValue())));
                                else if (numeric)
                                    spRow.addCell("" + (i + 1)).setContent(exCell.getNumericCellValue());
                                else
                                    spRow.addCell("" + (i + 1)).setContentAsString(numericToString(exCell));
                                break;
                            default:
                                spRow.addCell("" + (i + 1)).setContentAsString(exCell.getStringCellValue());
                            }
                        }
                    }
                }
            }

            // data
            if (spsheet.getColumnCount() > 0) {
                if (m_NumRows < 1)
                    lastRow = sheet.getLastRowNum();
                else
                    lastRow = Math.min(firstRow + m_NumRows - 1, sheet.getLastRowNum());
                for (i = dataRowStart; i <= lastRow; i++) {
                    if (m_Stopped)
                        break;
                    if (isLoggingEnabled())
                        getLogger().info("data row: " + (i + 1));
                    spRow = spsheet.addRow("" + spsheet.getRowCount());
                    exRow = sheet.getRow(i);
                    if (exRow == null)
                        continue;
                    for (n = 0; n < exRow.getLastCellNum(); n++) {
                        // too few columns in header?
                        if ((n >= spsheet.getHeaderRow().getCellCount()) && m_AutoExtendHeader)
                            spsheet.insertColumn(spsheet.getColumnCount(), "");

                        m_TextColumns.setMax(spsheet.getHeaderRow().getCellCount());
                        exCell = exRow.getCell(n);
                        if (exCell == null) {
                            spRow.addCell(n).setMissing();
                            continue;
                        }
                        cellType = exCell.getCellType();
                        if (cellType == Cell.CELL_TYPE_FORMULA)
                            cellType = exCell.getCachedFormulaResultType();
                        numeric = !m_TextColumns.isInRange(n);
                        switch (cellType) {
                        case Cell.CELL_TYPE_BLANK:
                        case Cell.CELL_TYPE_ERROR:
                            if (m_MissingValue.isEmpty())
                                spRow.addCell(n).setMissing();
                            else
                                spRow.addCell(n).setContent("");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(exCell))
                                spRow.addCell(n).setContent(
                                        dformat.format(HSSFDateUtil.getJavaDate(exCell.getNumericCellValue())));
                            else if (numeric)
                                spRow.addCell(n).setContent(exCell.getNumericCellValue());
                            else
                                spRow.addCell(n).setContentAsString(numericToString(exCell));
                            break;
                        default:
                            if (m_MissingValue.isMatch(exCell.getStringCellValue()))
                                spRow.addCell(n).setMissing();
                            else
                                spRow.addCell(n).setContentAsString(exCell.getStringCellValue());
                        }
                    }
                }
            }
        }
    } catch (Exception ioe) {
        getLogger().log(Level.SEVERE, "Failed to read range '" + m_SheetRange + "':", ioe);
        result = null;
        m_LastError = "Failed to read range '" + m_SheetRange + "' from stream!\n"
                + Utils.throwableToString(ioe);
    }

    return result;
}

---

From source file:at.jku.xlwrap.spreadsheet.poi.PoiSheet.java

License:Apache License

@Override
public int getColumns() {
    int maxColumn = 0;
    for (Row row : sheet) {
        if (row.getLastCellNum() > maxColumn) {
            maxColumn = row.getLastCellNum();
        }//  ww  w .  ja v  a2s. c  o  m
    }
    return maxColumn;
}

---

From source file:athena.Controller.java

License:Open Source License

private void convertExceltoCSV(String inputFile, String outputFilePath) {
    InputStream inp = null;//from ww  w. j av  a2  s .  c  o  m
    try {
        inp = new FileInputStream(inputFile);
        wb = new XSSFWorkbook(inp);

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet thisSheet = wb.getSheetAt(i);
            int rowEnd = Math.max(1400, thisSheet.getLastRowNum());

            view.setOutput("Writting.. " + thisSheet.getSheetName());

            String csvRawString = "";
            String outputFileName = outputFilePath + thisSheet.getSheetName() + ".csv";

            try {
                OutputStream os;

                File testFile = new File(outputFileName);

                if (testFile.exists() && !testFile.isDirectory()) {
                    os = new FileOutputStream(outputFilePath + thisSheet.getSheetName() + "(1).csv");
                } else {
                    os = new FileOutputStream(outputFileName);
                }

                PrintWriter w = new PrintWriter(new OutputStreamWriter(os, "UTF-8"));

                for (int j = 0; j < rowEnd; j++) {
                    Row row = thisSheet.getRow(j);

                    if (row != null) {
                        for (int k = 0; k < row.getLastCellNum(); k++) {
                            if (k == (row.getLastCellNum() - 1)) {
                                if (row.getCell(k) != null) {
                                    csvRawString = csvRawString + row.getCell(k);
                                }
                            } else {
                                if (row.getCell(k) == null) {
                                    csvRawString = csvRawString + ",";
                                } else {
                                    csvRawString = csvRawString + row.getCell(k) + ",";
                                }
                            }
                        }
                    } else {
                        csvRawString = csvRawString + ",";
                    }

                    csvRawString = csvRawString + "\n";
                    w.print(csvRawString);
                    w.flush();
                    csvRawString = "";
                }

                w.close();
                view.setOutput("Done.. " + thisSheet.getSheetName());
            } catch (FileNotFoundException e) {
                view.setOutput("I'm confused.. File not found!");
            } catch (UnsupportedEncodingException e) {
                view.setOutput("Call 911.. or Jake");
            }
        }
    } catch (IOException e) {
        view.setOutput("Uh oh.. Fail to read file!");
    }

    finally {
        try {
            inp.close();
            view.setOutput("Done conversion.. " + model.getInputFilePath() + "\n");
            model.setInputFilePath(null);
            model.setOutputFilePath(null);
            view.refreshIntputPath();
            view.refreshOutputPath();
        } catch (IOException e) {
            view.setOutput("Damn input stream..");
        }
    }
}

---

From source file:bad.robot.excel.matchers.CellNumberMatcher.java

License:Apache License

@Override
protected boolean matchesSafely(Row actual, Description mismatch) {
    if (expected.getLastCellNum() != actual.getLastCellNum()) {
        mismatch.appendText("got ").appendValue(numberOfCellsIn(actual)).appendText(" cell(s) on row ")
                .appendValue(asExcelRow(expected)).appendText(" expected ")
                .appendValue(numberOfCellsIn(expected));
        return false;
    }//from   w  w  w.  j  av  a 2s. co m
    return true;
}

---

From source file:bad.robot.excel.matchers.CellNumberMatcher.java

License:Apache License

/** POI is zero-based */
private static int numberOfCellsIn(Row row) {
    return row.getLastCellNum();
}

---

From source file:bad.robot.excel.row.CopyRow.java

License:Apache License

/**
 * Copies a row from a row index on the given workbook and sheet to another row index. If the destination row is
 * already occupied, shift all rows down to make room.
 *
 *///from  w ww .  j  av a 2 s  .c o m
public static void copyRow(Workbook workbook, Sheet worksheet, RowIndex from, RowIndex to) {
    Row sourceRow = worksheet.getRow(from.value());
    Row newRow = worksheet.getRow(to.value());

    if (alreadyExists(newRow))
        worksheet.shiftRows(to.value(), worksheet.getLastRowNum(), 1);
    else
        newRow = worksheet.createRow(to.value());

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);
        if (oldCell != null) {
            copyCellStyle(workbook, oldCell, newCell);
            copyCellComment(oldCell, newCell);
            copyCellHyperlink(oldCell, newCell);
            copyCellDataTypeAndValue(oldCell, newCell);
        }
    }

    copyAnyMergedRegions(worksheet, sourceRow, newRow);
}

---

From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java

License:Apache License

public void findAndReplaceAll(Sheet sheet, String find, Object replace) {
    if (replace == null) {
        replace = "";
    }/*from   www . j  a v  a2 s  .  c  om*/
    int iLastRow = sheet.getLastRowNum();
    for (int i1 = 0; i1 < iLastRow; i1++) {
        Row currentRow = sheet.getRow(i1);
        if (currentRow != null) {
            int iLastCell = currentRow.getLastCellNum();
            for (int i = 0; i < iLastCell; i++) {
                Cell currentCell = currentRow.getCell(i);
                if (currentCell != null && currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (currentCell.getStringCellValue().contains(find)) {
                        currentCell.setCellValue(currentCell.getStringCellValue().replace(find, "" + replace));
                    }
                }
            }
        }
    }
}

---

From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java

License:Open Source License

/**
 * ???//from  w  w  w.  j  a  va2  s .  co  m
 * 
 * @param sheet
 *            
 * @throws SAXException
 *             SAX????
 */
private final void parseSheet(final Sheet sheet) throws SAXException {
    // ?????
    AttributesImpl attrImpl = new AttributesImpl();
    attrImpl.addAttribute("", "name", "name", "CDATA", sheet.getSheetName());
    getContentHandler().startElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET),
            (String) getProperty(URI_PROPERTY_NAME_SHEET), attrImpl);

    startSheet(sheet.getSheetName());

    //getLastRowNum()??0???? +1?
    int maxRows = sheet.getLastRowNum() + 1;

    for (int row = 0; row < maxRows; row++) {
        startRow(row + 1);
        Row line = sheet.getRow(row);
        if (line != null) {
            for (int column = 0; column < line.getLastCellNum(); column++) {

                startColumn(column + 1);
                Cell cell = line.getCell(column);
                // ?trim()??????????????
                String value = getCellValue(cell);
                fireCell(column + 1, row + 1, value);
                endColumn(column + 1);
            }
        }
        endRow(row + 1);
    }

    endSheet(sheet);

    // ?????
    getContentHandler().endElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET),
            (String) getProperty(URI_PROPERTY_NAME_SHEET));
}

---

From source file:Categorization.CategoriesLoader.java

License:Open Source License

public static void echoAsCSV(Sheet sheet) throws IOException {
    Row row;
    int startingRow = 2;
    Category category;/*  www .  j  av a  2s . co  m*/
    boolean breakNow = false;
    for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
        if (breakNow) {
            break;
        }
        row = sheet.getRow(i);
        if (row == null) {
            break;
        }
        category = new Category();

        for (int j = 1; j < row.getLastCellNum(); j++) {

            //label of the category
            if (j == 1) {
                if (row.getCell(j).getStringCellValue().isEmpty()
                        || row.getCell(j).getStringCellValue() == null) {
                    breakNow = true;
                    break;
                }
                category.setCategoryName("CAT_" + row.getCell(j).getStringCellValue());
            }
            //if a cell is null, the row is empty.
            if (row.getCell(j) == null) {
                continue;
            }

            //check the keywords
            if (j > 1 & j <= maxColKeyWords) {
                System.out.println(row.getCell(j).getStringCellValue());
                if (row.getCell(j).getStringCellValue().startsWith("NOT ")) {
                    category.addExclusionKeyword(
                            row.getCell(j).getStringCellValue().toLowerCase().substring(4).trim());
                } else {
                    category.addKeyword(row.getCell(j).getStringCellValue().toLowerCase().trim());
                }
            }
            //check the min number of keywords that should be present in the text to match a classification
            if (j == maxColKeyWords + 1) {
                System.out.println("min Words: " + row.getCell(j).getStringCellValue());
                category.setMinNumberKeywords(Integer.parseInt(row.getCell(j).getStringCellValue()));
            }

            //check if single terms can lead to a direct classification
            if (j == maxColKeyWords + 2) {
                String[] directWords = row.getCell(j).getStringCellValue().split(";");
                for (String string : directWords) {
                    category.addDecisiveKeyword(string.toLowerCase().trim());
                }
            }
            //what supercategory the category belongs to
            if (j == maxColKeyWords + 3) {
                category.setSuperCategory(row.getCell(j).getStringCellValue());
            }

        }
        if (!category.getMinNumberKeywords().equals(0)) {
            categories.add(category);
        }
    }
}

---

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

private Map<ColumnIdentifier, String> getWorksheetPointerStringMap(Map<ColumnIdentifier, Integer> cols,
        Row row) {
    Map<ColumnIdentifier, String> outputItem = new HashMap<ColumnIdentifier, String>(cols.size());
    for (ColumnIdentifier col : cols.keySet()) {
        int colIndex = cols.get(col);
        Cell cell;//from w w w  .  ja v a  2  s . c  om
        if (colIndex == lastNonEmptyColNb) {
            colIndex = row.getLastCellNum();
            cell = row.getCell(colIndex);
            for (; colIndex > 0; colIndex--) {
                cell = row.getCell(colIndex);
                if (StringUtils.isNotBlank(formatter.formatCellValue(cell))) {
                    String test = mapping.get(formatter.formatCellValue(cell));
                    if (test == null) {
                        LOGGER.error("Could not find mapping for " + formatter.formatCellValue(cell));
                    }
                    break;
                }
            }
        } else {
            cell = row.getCell(colIndex);
        }
        outputItem.put(col, getCellValueString(cell));
    }
    return outputItem;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• »