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:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public int[] getBoundingBox(int sheetIndex, int startRow, int startCol, int endRow, int endCol,
        boolean autofitRow, boolean autofitCol) {
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    final int mark = Integer.MAX_VALUE - 1;

    if (startRow < 0) {
        startRow = sheet.getFirstRowNum();
        if (sheet.getRow(startRow) == null) {
            // There is no row in this sheet
            startRow = -1;/*from   w w w . j  av  a  2s .  c o  m*/
        }
    }

    if (endRow < 0) {
        endRow = sheet.getLastRowNum();
        if (sheet.getRow(endRow) == null) {
            // There is no row in this sheet
            endRow = -1;
        }
    }

    int minRow = startRow;
    int maxRow = endRow;
    int minCol = startCol;
    int maxCol = endCol < 0 ? mark : endCol;

    startCol = startCol < 0 ? mark : startCol;
    endCol = endCol < 0 ? -1 : endCol;
    Cell topLeft = null, bottomRight = null;
    boolean anyCell = false;
    for (int i = minRow; i > -1 && i <= maxRow; i++) {
        Row r = sheet.getRow(i);
        if (r != null) {
            // Determine column boundaries
            int start = Math.max(minCol, r.getFirstCellNum());
            int end = Math.min(maxCol + 1, r.getLastCellNum()); // NOTE: getLastCellNum is 1-based!
            boolean anyNonBlank = false;
            for (int j = start; j > -1 && j < end; j++) {
                Cell c = r.getCell(j);
                if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) {
                    anyCell = true;
                    anyNonBlank = true;
                    if ((autofitCol || minCol < 0) && (topLeft == null || j < startCol)) {
                        startCol = j;
                        topLeft = c;
                    }
                    if ((autofitCol || maxCol == mark) && (bottomRight == null || j > endCol)) {
                        endCol = j;
                        bottomRight = c;
                    }
                }
            }
            if (autofitRow && anyNonBlank) {
                endRow = i;
                if (sheet.getRow(startRow) == null) {
                    startRow = i;
                }
            }
        }
    }

    if ((autofitRow || startRow < 0) && !anyCell) {
        startRow = endRow = -1;
    }
    if ((autofitCol || startCol == mark) && !anyCell) {
        startCol = endCol = -1;
    }

    return new int[] { startRow, startCol, endRow, endCol };
}

From source file:com.movielabs.availslib.AvailSS.java

License:Open Source License

/**
 * Add a sheet from an Excel spreadsheet to a spreadsheet object
 * @param wb an Apache POI workbook object
 * @param sheet an Apache POI sheet object
 * @return created sheet object// w w w. ja v  a  2  s .c om
 */
private AvailsSheet addSheetHelper(Workbook wb, Sheet sheet) throws Exception {
    AvailsSheet as = new AvailsSheet(this, sheet.getSheetName());

    //        int qq = 0;
    for (Row row : sheet) {
        //           qq++;
        int len = row.getLastCellNum();
        if (len < 0)
            continue;
        String[] fields = new String[len];
        for (int i = 0; i < len; i++) // XXX: don't want nulls
            fields[i] = "";
        for (Cell cell : row) {
            int idx = cell.getColumnIndex();
            int type = cell.getCellType();
            switch (type) {
            case 0: // Numeric
                double v = cell.getNumericCellValue();
                if (v < 0.5) { // XXX hack: assume TotalRunTime
                    java.util.Date d = cell.getDateCellValue();
                    fields[idx] = String.format("%02d:%02d:%02d", d.getHours(), d.getMinutes(), d.getSeconds());
                    //System.out.println("run=" + tmp);
                } else {
                    fields[idx] = cell.toString();
                }
                break;
            case 1: // String
            case 3: // Blank
                fields[idx] = cell.getStringCellValue().trim();
                break;
            default:
                //logger.warn("Cell[" + i + "," + idx + "]: invalid type (" + type + ")");
                fields[idx] = cell.toString();
                break;
            }
        } /* cell */
        if (as.isAvail(fields))
            as.addRow(fields, row.getRowNum() + 1);
    } /* row */
    sheets.add(as);
    return as;
}

From source file:com.mto.excel.model.MergeWorkbook.java

License:Open Source License

private void addRow(Row row, int cellOffset) {
    Row newRow = sheet.createRow(rowOffset);
    rowOffset++;// w  w  w .j  a v  a 2 s  .c o m
    for (int i = cellOffset; i < row.getLastCellNum(); i++) {
        Cell c = row.getCell(i);
        if (c == null) {
            continue;
        }

        Cell newCell = newRow.createCell(i, c.getCellType());

        switch (c.getCellType()) {
        case CELL_TYPE_STRING:
            newCell.setCellValue(helper.createRichTextString(c.getStringCellValue()));
            break;
        case CELL_TYPE_BOOLEAN:
            newCell.setCellValue(c.getBooleanCellValue());
            break;
        case CELL_TYPE_NUMERIC:
            newCell.setCellValue(c.getNumericCellValue());
            break;
        default:
            return;
        }
    }
}

From source file:com.murilo.excel.ExcelHandler.java

public String getLine(int sheetIndex, int lineIndex, char separator) {
    Row linha = sheets[sheetIndex].getRow(lineIndex);
    String aux = "";
    for (int i = linha.getFirstCellNum(); i < linha.getLastCellNum(); i++) {
        Cell campo = linha.getCell(i);//from   w w w .j  a v a2  s . co m
        if ((i + 1) != linha.getLastCellNum()) {
            aux = aux + "\"" + stringrizeCell(campo) + "\"" + separator;
        } else {
            aux = aux + "\"" + stringrizeCell(campo) + "\"\n";
        }
    }

    return aux;
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>/*from   w ww .j a  va2 s  .  co m*/
 * 1.  : POI Util
 * 2.  : Excel ? ?(*.xls, *.xlsx ? )
 * </pre>
 *
 * @method Name : readExcel
 * @param strFullFilePath, serviceType
 * @return List<Map<String, Object>>
 * @throws Exception
 * 
 */
@SuppressWarnings("deprecation")
public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception {
    String tmpFile = strFullFilePath;
    File wbfile = new File(tmpFile);

    Workbook wb = null;
    FileInputStream file = null;

    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    Map<String, Object> map = null;

    try {
        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */

        /* Sheet  ? ,  ?  ??  */
        /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */
        for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1  ? */

            Sheet sheet = wb.getSheetAt(sheetIdx);

            /*   ?? ?    ?,   */

            /* row  ?  */
            int cellCount = 0;
            for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {

                Row row = sheet.getRow(rowIdx);
                cellCount = row.getLastCellNum();
                map = new HashMap<String, Object>();

                if (row != null) {
                    // cell  ? 
                    for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) {

                        Cell cell = row.getCell(cellIdx);
                        if (cell != null) {

                            int cellType = cell.getCellType();
                            String value = "";

                            //  WBS  ? ? ??
                            if (serviceType.equals("order")) {
                                switch (cellType) {
                                case HSSFCell.CELL_TYPE_FORMULA: //??
                                    value = cell.getStringCellValue();//cell.getCellFormula();
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC://?
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                        value = formatter.format(cell.getDateCellValue());
                                    } else {
                                        cell.setCellType(Cell.CELL_TYPE_STRING);
                                        value = cell.getStringCellValue();
                                    }
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: //?
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: //
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: //BYTE
                                    value = cell.getErrorCellValue() + "";
                                    break;

                                default:
                                    ;
                                }
                            } else {
                                switch (cellType) {
                                case HSSFCell.CELL_TYPE_FORMULA: //??
                                    value = cell.getStringCellValue();//cell.getCellFormula();
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC://?
                                    value = cell.getNumericCellValue() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: //?
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: //
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: //BYTE
                                    value = cell.getErrorCellValue() + "";
                                    break;

                                default:
                                }
                            }
                            map.put("colName" + cellIdx, value);
                        } else {
                            map.put("colName" + cellIdx, "");
                        }
                    }

                    list.add(map);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readExcel ?");
    } finally {

        /* ? ??  */
        file.close();
        wb.close();
    }

    return list;
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>// w ww . j  a  va 2s  .  c  om
 * 1.  : POI Util
 * 2.  : Excel ? ?(*.xls, *.xlsx ? ), sheet  ?
 * </pre>
 *
 * @method Name : readExcelMulti
 * @param strFullFilePath, serviceType, sheetNo
 * @return 
 * @throws
 * 
 */
@SuppressWarnings("deprecation")
public static List<LinkedHashMap<String, Object>> readExcelMulti(String strFullFilePath, String serviceType,
        int sheetNo) throws Exception {
    String tmpFile = strFullFilePath;
    File wbfile = new File(tmpFile);

    Workbook wb = null;
    FileInputStream file = null;

    List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>();
    LinkedHashMap<String, Object> map = null;

    try {
        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= file path : {} =", strFullFilePath);
            log.debug("= tmp file  : {} =", tmpFile);
            log.debug(
                    "==========================================================================================");
        }

        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */

        Sheet sheet = wb.getSheetAt(sheetNo);

        /* row  ?  */
        int cellCount = 0;
        for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {

            Row row = sheet.getRow(rowIdx);
            cellCount = row.getLastCellNum();
            map = new LinkedHashMap<String, Object>();

            if (rowIdx == 0) {
                if (log.isDebugEnabled()) {
                    log.debug(
                            "==================================================================================");
                    log.debug("= sheet no  : {} =", sheetNo);
                    log.debug("= row count : {} =", sheet.getLastRowNum());
                    log.debug("= col count : {} =", cellCount);
                    log.debug(
                            "==================================================================================");
                }
            }

            if (row != null) {
                // cell  ? 
                for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) {

                    Cell cell = row.getCell(cellIdx);
                    if (cell != null) {

                        int cellType = cell.getCellType();
                        String value = "";

                        switch (cellType) {
                        case HSSFCell.CELL_TYPE_FORMULA: //??
                            value = cell.getStringCellValue();//cell.getCellFormula();
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC://?
                            value = cell.getNumericCellValue() + "";
                            break;

                        case HSSFCell.CELL_TYPE_STRING: //?
                            value = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_BLANK: //
                            value = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_ERROR: //BYTE
                            value = cell.getErrorCellValue() + "";
                            break;

                        default:
                        }

                        map.put("item" + String.valueOf(cellIdx), value);
                    } else {
                        map.put("item" + String.valueOf(cellIdx), "");
                    }
                }

                if (log.isDebugEnabled()) {
                    log.debug(
                            "==================================================================================");
                    log.debug("= map : {} =", map);
                    log.debug(
                            "==================================================================================");
                }

                list.add(map);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readExcel ?");
    } finally {

        /* ? ??  */
        file.close();
        wb.close();
    }

    return list;
}

From source file:com.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * ?Excel /*from   w w w.j av a  2 s  . c  o m*/
 *  
 * @Title: readExcel 
 * @Date : 2014-9-11 ?11:26:53 
 * @param wb 
 * @return 
 */
private List<Row> readExcel(Workbook wb) {
    List<Row> rowList = new ArrayList<Row>();

    int sheetCount = 1;//??sheet?  

    Sheet sheet = null;
    if (onlyReadOneSheet) { //??sheet  
        // ??sheet(?????)  
        sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
    } else { //?sheet  
        sheetCount = wb.getNumberOfSheets();//????  
    }

    // ?sheet  
    for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
        // ??sheet  
        if (!onlyReadOneSheet) {
            sheet = wb.getSheetAt(t);
        }

        //???  
        int lastRowNum = sheet.getLastRowNum();

        if (lastRowNum > 0) { //>0?  
            out("\n????" + sheet.getSheetName() + "");
        }

        Row row = null;
        // ?  
        for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                rowList.add(row);
                out("" + (i + 1) + "", false);
                // ???  
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    String value = getCellValue(row.getCell(j));
                    if (!value.equals("")) {
                        out(value + " | ", false);
                    }
                }
                out("");
            }
        }
    }
    return rowList;
}

From source file:com.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * Excel? //  w w  w . j a va  2s. c o  m
 *  
 * @Title: WriteExcel 
 * @Date : 2014-9-11 ?01:33:59 
 * @param wb 
 * @param rowList 
 * @param xlsPath 
 */
private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) {

    if (wb == null) {
        out("???");
        return;
    }

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

    // ???????  
    int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1;
    int t = 0;//  
    out("???" + rowList.size());
    for (Row row : rowList) {
        if (row == null)
            continue;
        // ???  
        int pos = findInExcel(sheet, row);

        Row r = null;// ??????  
        if (pos >= 0) {
            sheet.removeRow(sheet.getRow(pos));
            r = sheet.createRow(pos);
        } else {
            r = sheet.createRow(lastRowNum + t++);
        }

        //??  
        CellStyle newstyle = wb.createCellStyle();

        //?  
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = r.createCell(i);// ??  
            cell.setCellValue(getCellValue(row.getCell(i)));// ???  
            // cell.setCellStyle(row.getCell(i).getCellStyle());//  
            if (row.getCell(i) == null)
                continue;
            copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // ????  
            cell.setCellStyle(newstyle);// ?  
            // sheet.autoSizeColumn(i);//  
        }
    }
    out("???:" + (rowList.size() - t) + " ?" + t);

    // ??  
    setMergedRegion(sheet);

    try {
        // ??Excel  
        FileOutputStream outputStream = new FileOutputStream(xlsPath);
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (Exception e) {
        out("Excel?? ");
        e.printStackTrace();
    }
}

From source file:com.netxforge.netxstudio.models.export.MasterDataExporterRevenge.java

License:Open Source License

private void _generateID(Sheet sheet) {
    // Generate name.
    {//from   w ww  .ja  v  a 2 s. c o m
        // Style, cell color.
        CellStyle attributeStyle = workBook.createCellStyle();
        attributeStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        attributeStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // Style, font
        HSSFFont attributeFont = workBook.createFont();
        attributeFont.setFontName("Verdana");
        attributeFont.setColor(HSSFColor.BLUE.index);
        attributeStyle.setFont(attributeFont);

        // Style, border.
        attributeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);

        Row row = sheet.getRow(0);
        if (row == null) {
            row = sheet.createRow(0);
        }
        short lastCellNum = row.getLastCellNum();
        if (lastCellNum == -1) {
            lastCellNum = 0;
        }
        Cell cell = row.createCell(lastCellNum);
        cell.setCellValue("ID");
        cell.setCellStyle(attributeStyle);
    }
    // Generate type
    {

        CellStyle typeStyle = workBook.createCellStyle();
        typeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);

        Row row = sheet.getRow(1);
        if (row == null) {
            row = sheet.createRow(1);
        }
        short lastCellNum = row.getLastCellNum();
        if (lastCellNum == -1) {
            lastCellNum = 0;
        }
        Cell cell = row.createCell(lastCellNum);
        cell.setCellStyle(typeStyle);
    }
}

From source file:com.netxforge.netxstudio.models.export.MasterDataExporterRevenge.java

License:Open Source License

private void _generateCell(EAttribute eAttribute, Sheet sheet) {

    // Generate name.
    {/*  w  w w  .  j  a va2 s .  com*/
        // Style, cell color.
        CellStyle attributeStyle = workBook.createCellStyle();
        attributeStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        attributeStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // Style, font
        HSSFFont attributeFont = workBook.createFont();
        attributeFont.setFontName("Verdana");
        attributeFont.setColor(HSSFColor.BLUE.index);
        attributeStyle.setFont(attributeFont);

        // Style, border.
        attributeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);

        Row row = sheet.getRow(0);
        if (row == null) {
            row = sheet.createRow(0);
        }
        short lastCellNum = row.getLastCellNum();
        if (lastCellNum == -1) {
            lastCellNum = 0;
        }
        Cell cell = row.createCell(lastCellNum);
        cell.setCellValue(StringUtils.capitalize(eAttribute.getName()));
        cell.setCellStyle(attributeStyle);
    }
    // Generate type
    {

        CellStyle typeStyle = workBook.createCellStyle();
        typeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);

        Row row = sheet.getRow(1);
        if (row == null) {
            row = sheet.createRow(1);
        }
        short lastCellNum = row.getLastCellNum();
        if (lastCellNum == -1) {
            lastCellNum = 0;
        }
        Cell cell = row.createCell(lastCellNum);
        cell.setCellValue(eAttribute.getEType().getName());
        cell.setCellStyle(typeStyle);
    }
}