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.dituiba.excel.ImportTableService.java

License:Apache License

/**
 * ExcelTableBean/*from  w ww .ja  va2  s .co  m*/
 */
public void doImport() {
    int rowNum = sheet.getLastRowNum() + 1;
    int columnNum = 0;
    for (int i = 0; i < rowNum; i++) {
        if (sheet.getRow(i) != null) {
            int last = sheet.getRow(i).getLastCellNum();
            columnNum = last > columnNum ? last : columnNum;
        }
    }
    tableBean = new TableBean(rowNum, columnNum);
    Collection<CellBean> cellBeans = new ArrayList<CellBean>();
    for (int r = startRow; r < rowNum; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null) {
                    String cellValue = null;
                    if (cellHandlerMap.containsKey(c)) {
                        cellValue = cellHandlerMap.get(c).readCell(cell) + "";
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        Integer type = forceCellType.get(c);
                        if (type != null) {
                            cell.setCellType(type);
                        }
                        if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                            cellValue = cell.getBooleanCellValue() + "";
                        } else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
                            try {
                                cellValue = String.valueOf(cell.getNumericCellValue());
                            } catch (IllegalStateException e) {
                                cellValue = String.valueOf(cell.getRichStringCellValue()).trim();
                            }
                        } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                            if (DateUtil.isCellDateFormatted(cell)) {
                                Date date2 = cell.getDateCellValue();
                                SimpleDateFormat dff = new SimpleDateFormat(dateFormat);
                                cellValue = dff.format(date2); //
                            } else {
                                cellValue = String.valueOf(cell.getNumericCellValue());
                            }
                        } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                            cellValue = cell.getStringCellValue();
                        }
                        if (cellValue != null && cellValue instanceof String) {
                            cellValue = cellValue.toString().trim();
                        }
                    }
                    CellBean cellBean = new CellBean(cellValue, r, c);
                    cellBean.setCell(cell);
                    cellBeans.add(cellBean);
                }
            }
        }
    }
    tableBean.setCellBeans(cellBeans);
}

---

From source file:com.ebay.xcelite.reader.SimpleSheetReader.java

License:Apache License

@Override
public Collection<Collection<Object>> read() {
    List<Collection<Object>> rows = Lists.newArrayList();
    Iterator<Row> rowIterator = sheet.getNativeSheet().iterator();
    boolean firstRow = true;
    short cellsNum = -1;
    while (rowIterator.hasNext()) {
        Row excelRow = rowIterator.next();
        if (firstRow) {
            cellsNum = excelRow.getLastCellNum();
            firstRow = false;/*  w  w w.  ja va 2 s.  c om*/
            if (skipHeader)
                continue;
        }
        List<Object> row = Lists.newArrayList();

        boolean blankRow = true;
        for (int i = 0; i < cellsNum; i++) {
            Object value = readValueFromCell(excelRow.getCell(i, Row.MissingCellPolicy.RETURN_NULL_AND_BLANK));

            if (blankRow && value != null && !String.valueOf(value).isEmpty()) {
                blankRow = false;
            }
            row.add(value);
        }
        if (blankRow)
            continue;
        boolean keepRow = true;
        for (RowPostProcessor<Collection<Object>> rowPostProcessor : rowPostProcessors) {
            keepRow = rowPostProcessor.process(row);
            if (!keepRow)
                break;
        }
        if (keepRow) {
            rows.add(row);
        }
    }
    return rows;
}

---

From source file:com.eleven0eight.xls2json.App.java

License:Open Source License

public String convertXlsToJson(FileInputStream fis) throws Exception {

    Workbook workbook = WorkbookFactory.create(fis);
    Sheet sheet = workbook.getSheetAt(0);
    JSONObject json = new JSONObject();
    JSONArray items = new JSONArray();
    ArrayList cols = new ArrayList();

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        JSONObject item = new JSONObject();

        for (short colIndex = row.getFirstCellNum(); colIndex <= row.getLastCellNum(); colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                continue;
            }//from  w ww .  j  av a2 s  . com
            if (i == 0) { // header
                cols.add(colIndex, cell.getStringCellValue());
            } else {
                item.put((String) cols.get(colIndex), cell.getStringCellValue());
            }
        }
        if (item.length() > 0) {
            items.put(item);
        }
    }
    json.put("items", items);
    return json.toString();

}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * Get contents of a sheet into text rows and columns
 * /* w ww .  ja va  2 s  .c o  m*/
 * @param sheet
 * @return
 */
private String[][] getRawData(Sheet sheet, boolean expectValueInFirstColumn) {

    // let us get a normalized rows/columns out of this sheet.
    int firstRowIdx = sheet.getFirstRowNum();
    Row firstRow = sheet.getRow(firstRowIdx);
    int firstCellIdx = firstRow.getFirstCellNum();
    int lastCellAt = firstRow.getLastCellNum();
    int nbrCells = lastCellAt - firstCellIdx;

    int lastRow = sheet.getLastRowNum();

    List<String[]> rawData = new ArrayList<String[]>();
    for (int rowNbr = firstRowIdx; rowNbr <= lastRow; rowNbr++) {
        Row row = sheet.getRow(rowNbr);
        if (row == null || row.getPhysicalNumberOfCells() == 0) {
            Spit.out(
                    "row at " + rowNbr + "is empty. while this is not an error, we certianly discourage this.");
            continue;
        }

        String[] rowData = this.getTextValues(row, firstCellIdx, nbrCells);
        if (rowData == null) {
            continue;
        }
        if (expectValueInFirstColumn) {
            String firstData = rowData[0];
            if (firstData == null || firstData.length() == 0) {
                Spit.out("row at" + rowNbr + " has its first column empty, and hence the row is ignored");
                continue;
            }
        }
        rawData.add(rowData);
    }

    if (rawData.size() > 0) {
        return rawData.toArray(new String[0][0]);
    }
    return null;
}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * extract fields from spread sheet into dc
 * //w  w w . j  ava 2  s  .  com
 * @param table
 *            table element of spread sheet
 * @param dc
 *            dc
 * @param useDictionaryForDataType
 *            refer to data dictionary or use DataType as present in spread
 *            sheet
 */
private void extractValues(Sheet sheet, DataCollection dc, boolean useDictionaryForDataType) {
    int n = sheet.getLastRowNum();

    // if there are no values, following for loop will not execute..
    for (int i = 1; i <= n; i++) // first row is header
    {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        // value row should have just two cells in it
        int nbrCells = row.getLastCellNum();
        if (nbrCells < 1) {
            continue;
        }

        String fieldName = row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue();
        if (fieldName.length() == 0) {
            continue; // no name
        }

        Cell dataCell = null;
        String fieldValue = EMPTY_STRING;
        if (nbrCells > 1) // value is present
        {
            dataCell = row.getCell(1, Row.CREATE_NULL_AS_BLANK);
            fieldValue = this.getTextValue(dataCell);
        }

        if (useDictionaryForDataType) {
            dc.addValueAfterCheckingInDictionary(fieldName, fieldValue);
        } else {
            dc.addValue(fieldName, fieldValue, this.getExilityType(dataCell));
        }
    }
}

---

From source file:com.faizod.aem.component.core.servlets.datasources.impl.ExcelDatasourceParser.java

License:Apache License

@Override
public Map<Object, List<Object>> parseMultiColumn(InputStream inputStream) {
    Map<Object, List<Object>> map = new LinkedHashMap<Object, List<Object>>();

    // read in the Excel file
    try {/* w w w . j  a v  a  2s  .  c  o  m*/
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rows = sheet.iterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            List<Cell> cells = new ArrayList<Cell>();
            short lineMin = row.getFirstCellNum();
            short lineMax = row.getLastCellNum();

            for (short index = lineMin; index < lineMax; index++)
                cells.add(row.getCell(index));

            Object label = "";
            switch (cells.get(0).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                label = cells.get(0).getNumericCellValue();
                break;
            case Cell.CELL_TYPE_STRING:
                label = "" + (cells.get(0).getStringCellValue());
                break;
            default:
                break;
            }

            List<Object> values = new ArrayList<Object>();

            for (short index = 1; index < (lineMax - lineMin); index++) {
                Object value;

                switch (cells.get(index).getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cells.get(index).getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    value = cells.get(index).getNumericCellValue();
                    break;
                default:
                    value = new Object();
                    break;
                }
                values.add(value);
            }
            map.put(label, values);
        }
    } catch (IOException e) {
        LOG.error("Unable to read datasource.", e);
        throw new DatasourceException("Unable to read datasource.", e);
    } catch (InvalidFormatException e) {
        LOG.error("File Format not supported.", e);
        throw new DatasourceException("File Format not supported.", e);
    }
    return map;
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ????//from   ww w  .  ja  v  a 2s.  c  o m
 *
 * @param sheet
 * @param rowIndex
 * @param style
 * @return
 */
public static boolean setRowStyle(Sheet sheet, int rowIndex, CellStyle style) {
    if (sheet != null) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            short firstColumnIndex = row.getFirstCellNum();
            short lastColumnIndex = row.getLastCellNum();
            for (short colunmIndex = firstColumnIndex; colunmIndex < lastColumnIndex; colunmIndex++) {
                CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                Cell cell = row.getCell(colunmIndex);
                if (cell != null) {
                    cellStyle.cloneStyleFrom(cell.getCellStyle());
                    cellStyle.cloneStyleFrom(style);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    return true;
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java

License:Apache License

/**
 * ????//from  www . ja v a  2s .co m
 * @param sheet
 * @param rowIndex
 * @param style
 * @return
 */
public boolean setRowStyle(int rowIndex, CellStyle style) {
    Sheet sheet = excelSheet.sheet;
    if (sheet != null) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            short firstColumnIndex = row.getFirstCellNum();
            short lastColumnIndex = row.getLastCellNum();
            for (short colunmIndex = firstColumnIndex; colunmIndex < lastColumnIndex; colunmIndex++) {
                CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                Cell cell = row.getCell(colunmIndex);
                if (cell != null) {
                    cellStyle.cloneStyleFrom(cell.getCellStyle());
                    cellStyle.cloneStyleFrom(style);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    return true;
}

---

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java.//  w ww. ja va  2s .  c  o  m
 * 
 * @param uploadFileName
 *            
 * @param titleList
 *            ???
 * @param beanType
 *            ?
 * @return
 * @throws Exception
 *             2015723
 */
public static <T> List<T> parseExcel(MultipartFile uploadFileName, List<String> titleList, Class<T> beanType)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // ??Workbook
    Workbook wb = getWorkbookByFileContentType(uploadFileName);

    // ?
    List<T> datas = new ArrayList<T>();

    // ????
    ClassInfo classInfo = ClassUtil.getClassInfo(beanType);

    // ?
    Sheet sheet = (Sheet) wb.getSheetAt(0);

    // ?
    int rowNum = sheet.getLastRowNum();
    Row titleRow = sheet.getRow(0);
    int colNum = titleRow.getLastCellNum();

    for (int i = 2; i <= rowNum; i++) {
        Row row = sheet.getRow(i);

        if (row == null) {
            continue;
        }

        // 
        T retObject = beanType.newInstance();

        for (int j = 0; j < colNum; j++) {

            Cell cell = row.getCell(j);

            // ???
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(titleList.get(j));
            if (reflexField == null)
                continue;
            if (cell != null) {

                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                String dd = row.getCell(j).getStringCellValue().trim();

                if (StringUtil.isNotEmpty(dd)) {

                    // ??beanExcel??
                    if (reflexField.getPropertyType().getName().equals("java.sql.Date")) {
                        // 
                        Date date = sdf.parse(dd);
                        reflexField.setValue(retObject, new java.sql.Date(date.getTime()));
                    } else {

                        reflexField.setValue(retObject,
                                ValueObjectUtil.typeCast(dd, reflexField.getPropertyType()));
                    }

                }
            }

        }

        datas.add(retObject);
    }

    return datas;
}

---

From source file:com.github.camaral.sheeco.Sheeco.java

License:Apache License

private boolean isBlankRow(final Row row) {
    for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
        final Cell cell = row.getCell(i);
        if (cell != null && row.getCell(i).getCellType() != Cell.CELL_TYPE_BLANK) {
            return false;
        }/*from w  w w  .  j  a  v a 2 s .c o m*/
    }
    return true;
}

---

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