Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getRow.

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

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;/*w  ww  .j  ava  2  s .  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.mycompany.exceldatetimetest.test.java

public static void main(String[] args) throws Exception {
    //get date-lime from excel
    File file = new File("Book1.xlsx");
    XSSFWorkbook xssfwb = new XSSFWorkbook(file);
    Sheet sh = xssfwb.getSheet("?1");
    Date date = sh.getRow(1).getCell(0).getDateCellValue();
    System.err.println(date);/*  w  w  w.  j a v a 2  s  . com*/

    //save to db

}

From source file:com.mycompany.peram_inclassexam.ReadExcelFile.java

public List getAccountListFromExcel() throws FileNotFoundException {
    List accountList = new ArrayList();
    FileInputStream fis = null;//w w w .  ja v a  2s .  c  o m
    try {
        fis = new FileInputStream(FILE_PATH);
        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                AccountDetails account = new AccountDetails();
                Row row = (Row) sheet.getRow(j);

                Iterator cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = (Cell) cellIterator.next();

                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {

                        if (cell.getColumnIndex() == 1) {
                            account.setLastName(cell.getStringCellValue());
                        }

                        if (cell.getColumnIndex() == 2) {
                            account.setAccountNo(cell.getStringCellValue());
                        }

                        if (cell.getColumnIndex() == 0) {
                            account.setFirstName(cell.getStringCellValue());
                        }

                    } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        if (cell.getColumnIndex() == 3) {
                            account.setAccountBalance((int) cell.getNumericCellValue());
                        }
                    }

                }

                accountList.add(account);
            }

        }

        fis.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return accountList;
}

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

License:Open Source License

/**
 * <pre>//from ww w .j  a  v  a  2s .c  o  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>//from  w  w  w.  j a  v  a  2 s.com
 * 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.ExcelUtil.java

License:Apache License

public List<Row> readExcel(Workbook wb) {
    Sheet sheet = null;

    if (onlyReadOneSheet) {//??sheet

        System.out.println("selectedSheetName:" + selectedSheetName);
        // ??sheet(?????)  
        sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
        System.out.println(sheet.getSheetName());

    } else {// w  w w . j  av a2s. c o m
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {//??Sheet

            sheet = wb.getSheetAt(i);
            logger.info(sheet.getSheetName());

            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//??
                Row row = sheet.getRow(j);
                for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//???
                    System.out.print(row.getCell(k) + "\t");
                }
                System.out.println("---Sheet" + i + "?---");
            }
        }
    }
    return null;
}

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

License:Apache License

/** 
 * ?Excel //from www.j a  v 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.  co 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.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * ???Excel /*w w w .j  a  v  a 2 s .c o  m*/
 *  
 * @Title: findInExcel 
 * @Date : 2014-9-11 ?02:23:12 
 * @param sheet 
 * @param row 
 * @return 
 */
private int findInExcel(Sheet sheet, Row row) {
    int pos = -1;

    try {
        // ??  
        if (isOverWrite || !isNeedCompare) {
            return pos;
        }
        for (int i = startReadPos; i <= sheet.getLastRowNum() + endReadPos; i++) {
            Row r = sheet.getRow(i);
            if (r != null && row != null) {
                String v1 = getCellValue(r.getCell(comparePos));
                String v2 = getCellValue(row.getCell(comparePos));
                if (v1.equals(v2)) {
                    pos = i;
                    break;
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return pos;
}

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

License:Open Source License

private void _generateID(Sheet sheet) {
    // Generate name.
    {/* w  ww .j a v  a 2s  . 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);
    }
}