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:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

private boolean rowIsBlank(Row row) {
    // returns true if a row has cells but all cells are 'BLANK' type.
    boolean isBlank = true;
    for (int columnNum = row.getFirstCellNum(); columnNum < row.getLastCellNum(); columnNum++) {
        Cell c = row.getCell(columnNum);
        isBlank = isBlank && (c == null || c.getCellTypeEnum() == CellType.BLANK);
        if (!isBlank)
            break;
    }/*from  www  .j  av  a2s.c  om*/
    return isBlank;
}

---

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

private void updateRecordWithCellValues(Row row, Record record) throws DataParserException {
    LinkedHashMap<String, Field> output = new LinkedHashMap<>();
    String sheetName = row.getSheet().getSheetName();
    String columnHeader;//from   w w w. ja  v a2  s. com
    Set<String> unsupportedCellTypes = new HashSet<>();
    for (int columnNum = row.getFirstCellNum(); columnNum < row.getLastCellNum(); columnNum++) {
        if (headers.isEmpty()) {
            columnHeader = String.valueOf(columnNum);
        } else {
            if (columnNum >= headers.get(sheetName).size()) {
                columnHeader = String.valueOf(columnNum); // no header for this column.  mismatch
            } else {
                columnHeader = headers.get(sheetName).get(columnNum).getValueAsString();
            }
        }

        Cell cell = row.getCell(columnNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        try {
            output.put(columnHeader, Cells.parseCell(cell, this.evaluator));
        } catch (ExcelUnsupportedCellTypeException e) {
            output.put(columnHeader, Cells.parseCellAsString(cell));
            unsupportedCellTypes.add(e.getCellType().name());
        }
    }

    // Set interesting metadata about the row
    Record.Header hdr = record.getHeader();
    hdr.setAttribute("worksheet", row.getSheet().getSheetName());
    hdr.setAttribute("row", Integer.toString(row.getRowNum()));
    hdr.setAttribute("firstCol", Integer.toString(row.getFirstCellNum()));
    hdr.setAttribute("lastCol", Integer.toString(row.getLastCellNum()));
    record.set(Field.createListMap(output));
    if (unsupportedCellTypes.size() > 0) {
        throw new RecoverableDataParserException(record, Errors.EXCEL_PARSER_05,
                StringUtils.join(unsupportedCellTypes, ", "));
    }
}

---

From source file:com.svi.uzabase.logic.ExtractData.java

private List<String> extractCompanyNames() {
    List<String> companyList = new ArrayList<>();
    try {/*  w  w w .  j  a  va  2  s  .  c  o m*/
        FileInputStream inputStream = new FileInputStream(new File(COMPANY_EXCEL_PATH));
        Workbook workbook = new HSSFWorkbook(inputStream);
        Row row;
        Cell cell;
        Sheet sheet;
        sheet = workbook.getSheetAt(0);
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            row = sheet.getRow(rowIndex);
            for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                cell = row.getCell(colIndex);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                if (colIndex == 1) {
                    companyList.add(cell.getStringCellValue().toUpperCase());
                }
            }
        }
        //Closes opened documents
        inputStream.close();
        workbook.close();
    } catch (IOException ex) {
        Logger.getLogger(ExtractData.class.getName()).log(Level.SEVERE, null, ex);
    }
    return companyList;
}

---

From source file:com.tecacet.jflat.excel.PoiExcelReader.java

License:Apache License

protected String[] readRow(Row row) {
    List<String> tokens = new ArrayList<String>();
    for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c);/*from   w w w .  j  a va2  s .  co  m*/
        String cellValue = "";
        if (cell != null) {
            cellValue = getCellContentAsString(cell);
        }
        tokens.add(cellValue);
    }
    return tokens.toArray(new String[tokens.size()]);
}

---

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

/**
 * Gets cell data for cells within the given bounds.
 *
 * @param firstRow/*from   www. j  a v a 2 s  .  c  o m*/
 *            Starting row index, 1-based
 * @param firstColumn
 *            Starting column index, 1-based
 * @param lastRow
 *            Ending row index, 1-based
 * @param lastColumn
 *            Ending column index, 1-based
 * @return A list of CellData for the cells in the given area.
 */
protected ArrayList<CellData> loadCellDataForRowAndColumnRange(int firstRow, int firstColumn, int lastRow,
        int lastColumn) {
    ArrayList<CellData> cellData = new ArrayList<CellData>();
    Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Map<String, String> componentIDtoCellKeysMap = spreadsheet.getState(false).componentIDtoCellKeysMap;
    @SuppressWarnings("unchecked")
    final Collection<String> customComponentCells = (Collection<String>) (componentIDtoCellKeysMap == null
            ? Collections.emptyList()
            : componentIDtoCellKeysMap.values());
    for (int r = firstRow - 1; r < lastRow; r++) {
        Row row = activeSheet.getRow(r);
        if (row != null && row.getLastCellNum() != -1 && row.getLastCellNum() >= firstColumn) {
            for (int c = firstColumn - 1; c < lastColumn; c++) {
                final String key = SpreadsheetUtil.toKey(c + 1, r + 1);
                if (!customComponentCells.contains(key) && !sentCells.contains(key)
                        && !sentFormulaCells.contains(key)) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        final CellData cd = createCellDataForCell(cell);
                        if (cd != null) {
                            int cellType = cell.getCellType();
                            if (cellType == Cell.CELL_TYPE_FORMULA) {
                                sentFormulaCells.add(key);
                            } else {
                                sentCells.add(key);
                            }
                            cellData.add(cd);
                        }
                    }
                }
            }
        }
    }
    return cellData;
}

---

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

private int getLastNonBlankRow(Sheet sheet) {
    for (int r = sheet.getLastRowNum(); r >= 0; r--) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (short c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    return r;
                }//from  w ww.  j a  v  a  2s . co m
            }
        }
    }
    return 0;
}

---

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java

License:Open Source License

/**
 * Calculate size-related values for the sheet. Includes row and column
 * counts, actual row heights and column widths, and hidden row and column
 * indexes./*www.  j av a2s  . c  o  m*/
 * 
 * @param spreadsheet
 * @param sheet
 */
static void calculateSheetSizes(final Spreadsheet spreadsheet, final Sheet sheet) {
    // Always have at least the default amount of rows
    int rows = sheet.getLastRowNum() + 1;
    if (rows < spreadsheet.getDefaultRowCount()) {
        rows = spreadsheet.getDefaultRowCount();
    }
    spreadsheet.getState().rows = rows;

    final float[] rowHeights = new float[rows];
    int cols = 0;
    int tempRowIndex = -1;
    final ArrayList<Integer> hiddenRowIndexes = new ArrayList<Integer>();
    for (Row row : sheet) {
        int rIndex = row.getRowNum();
        // set the empty rows to have the default row width
        while (++tempRowIndex != rIndex) {
            rowHeights[tempRowIndex] = spreadsheet.getState().defRowH;
        }
        if (row.getZeroHeight()) {
            rowHeights[rIndex] = 0.0F;
            hiddenRowIndexes.add(rIndex + 1);
        } else {
            rowHeights[rIndex] = row.getHeightInPoints();
        }
        int c = row.getLastCellNum();
        if (c > cols) {
            cols = c;
        }
    }
    if (rows > sheet.getLastRowNum() + 1) {
        float defaultRowHeightInPoints = sheet.getDefaultRowHeightInPoints();

        int lastRowNum = sheet.getLastRowNum();
        // if sheet is empty, also set height for 'last row' (index
        // zero)
        if (lastRowNum == 0) {
            rowHeights[0] = defaultRowHeightInPoints;
        }

        // set default height for the rest
        for (int i = lastRowNum + 1; i < rows; i++) {
            rowHeights[i] = defaultRowHeightInPoints;
        }
    }
    spreadsheet.getState().hiddenRowIndexes = hiddenRowIndexes;
    spreadsheet.getState().rowH = rowHeights;

    // Always have at least the default amount of columns
    if (cols < spreadsheet.getDefaultColumnCount()) {
        cols = spreadsheet.getDefaultColumnCount();
    }
    spreadsheet.getState().cols = cols;

    final int[] colWidths = new int[cols];
    final ArrayList<Integer> hiddenColumnIndexes = new ArrayList<Integer>();
    for (int i = 0; i < cols; i++) {
        if (sheet.isColumnHidden(i)) {
            colWidths[i] = 0;
            hiddenColumnIndexes.add(i + 1);
        } else {
            colWidths[i] = ExcelToHtmlUtils.getColumnWidthInPx(sheet.getColumnWidth(i));
        }
    }
    spreadsheet.getState().hiddenColumnIndexes = hiddenColumnIndexes;
    spreadsheet.getState().colW = colWidths;
}

---

From source file:com.waveconn.Excel2MySQL.java

License:Apache License

private void rowToData(Row row) {
    Cell cell = null;/*from   www .j  a v  a 2 s  . co  m*/
    int lastCellNum = 0;
    ArrayList<String> line = new ArrayList();
    ArrayList<Object> correctLine = new ArrayList();

    boolean error = false;

    // Check to ensure that a row was recovered from the sheet as it is
    // possible that one or more rows between other populated rows could be
    // missing - blank. If the row does contain cells then...
    if (row != null) {

        // Get the index for the right most cell on the row and then
        // step along the row from left to right recovering the contents
        // of each cell, converting that into a formatted String and
        // then storing the String into the line ArrayList.
        lastCellNum = row.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            cell = row.getCell(i);
            if (cell == null) {
                line.add("");
            } else {
                if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
                    line.add(this.formatter.formatCellValue(cell));
                } else {
                    line.add(this.formatter.formatCellValue(cell, this.evaluator));
                }
            }
        }

        //check if there is an error cell in this line and set a flag.
        error = false;
        for (int i = 0; i <= lastCellNum; i++) {
            //ignore the column if it is not in db table
            if (!Mapping.isDb_Col(dbMap, i))
                continue;

            Mapping m = Mapping.getMapping(dbMap, i);

            switch (m.getType()) {
            case INTEGER: //INT (int or long)
                try {
                    int tmp = Integer.parseInt(line.get(i));
                    correctLine.add(tmp);
                } catch (NumberFormatException e) {
                    try {
                        long tmp = Long.parseLong(line.get(i));
                        correctLine.add(tmp);
                        break;
                    } catch (NumberFormatException e1) {
                        error = true;
                        break;
                    }
                }
                break;
            case NUMBER: //NUM (int or long or float or double)
                try {
                    int tmp = Integer.parseInt(line.get(i));
                    correctLine.add(tmp);
                } catch (NumberFormatException e) {
                    try {
                        long tmp = Long.parseLong(line.get(i));
                        correctLine.add(tmp);
                        break;
                    } catch (NumberFormatException e1) {
                        try {
                            Float tmp = Float.parseFloat(line.get(i));
                            correctLine.add(tmp);
                            break;
                        } catch (NumberFormatException e2) {
                            try {
                                Double tmp = Double.parseDouble(line.get(i));
                                correctLine.add(tmp);
                                break;
                            } catch (NumberFormatException e3) {
                                error = true;
                                break;
                            }
                        }
                    }
                }
                break;
            case STRING: //STR
                int len = m.getLen();
                if (len == -1)
                    len = DB_STRING_LEN_DEFAULT;

                String v = line.get(i);
                if (v.length() > len)
                    v = v.substring(0, len);
                correctLine.add(v);
                break;
            case DATE: //DATE not validated currently
                v = line.get(i);
                if (v.length() > DB_DATE_LEN)
                    v = v.substring(0, DB_DATE_LEN);
                correctLine.add(v);
                break;
            case BOOLEAN: //BOOL
                v = line.get(i);
                if (v.length() > DB_BOOL_LEN)
                    v = v.substring(0, DB_BOOL_LEN);
                if ("true".equalsIgnoreCase(v) || "false".equalsIgnoreCase(v) || "t".equalsIgnoreCase(v)
                        || "f".equalsIgnoreCase(v) || "yes".equalsIgnoreCase(v) || "no".equalsIgnoreCase(v)
                        || "y".equalsIgnoreCase(v) || "n".equalsIgnoreCase(v)) {
                    correctLine.add(v);
                    break;
                } else {
                    error = true;
                    break;
                }
            }
        }
    }

    if (error)
        this.errorRows.add(line);
    else
        this.correctRows.add(line);
}

---

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?/* w  w w .  j a v a2 s  .  c  o m*/
 * @param FileName ???????excel
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (FileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // jsonkey
        String[] cellNames;
        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);
        // ???
        cellNames = new String[curCellNum];
        // ????JSONkey
        for (int m = 0; m < curCellNum; m++) {
            Cell cell = fisrtRow.getCell(m);
            // ?
            cell.setCellStyle(cellStyle);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            // ?
            cellNames[m] = getCellValue(cell);
        }
        for (String s : cellNames) {
            System.out.print("" + i + " sheet " + s + ",");
        }
        System.out.println();

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);

                cell.setCellStyle(cellStyle);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // ???
                rowMap.put(cellNames[k], getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

---

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?// w  ww . ja va2 s.  c o m
 * @param FileName ???????excel
 * @param headers list,String-->Arrays.asList();
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (fileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);
                // ???
                rowMap.put(headers.get(k), getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

---

• «
• 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
• »