Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

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

License:Apache License

/**
 * ????/*from   w  w  w . j a  v a2s . co  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 w  ww  . j a  v a 2s .  co m*/
 * @param rowIndex
 * @param colIndex
 * @return
 */
protected boolean cellExist(int rowIndex, int colIndex) {
    Sheet sheet = excelSheet.getSheet();
    if (rowIndex < 0 || rowIndex > sheet.getLastRowNum())
        return false;
    Row row = sheet.getRow(rowIndex);
    Cell cell = row.getCell(colIndex);
    return cell != null;
}

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

License:Apache License

public void setTitle(String title) {
    Sheet sheet = excelSheet.getSheet();
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue(title);/*from  ww  w  .  j a  v a 2  s .c  o  m*/
}

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

License:Apache License

/**
 * {@inheritDoc}// w w w . j  a v  a2 s.  c o m
 */
public void setCellValue(int rowIndex, int colIndex, Object value) {
    Sheet sheet = excelSheet.getSheet();
    if (cellExist(rowIndex, colIndex)) {
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        setCellValue(cell, value);
    }
}

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

License:Apache License

/**
 * ??//  ww  w  . j  av a 2  s .  com
 * @param sheet
 * @param columnIndex
 * @param style
 * @return
 */
public boolean setColumnStyle(int columnIndex, int rowFirstIndex, int rowLastIndex, CellStyle style) {
    Sheet sheet = excelSheet.sheet;
    if (sheet == null)
        return false;

    int rowNum = sheet.getLastRowNum();
    CellStyle newCellStyle = sheet.getWorkbook().createCellStyle();
    // ??
    if (rowFirstIndex > rowLastIndex) {
        int temp = rowFirstIndex;
        rowFirstIndex = rowLastIndex;
        rowLastIndex = temp;
    }
    if (rowNum < rowFirstIndex) {// ?
        return false;
    }
    // 
    for (int i = rowFirstIndex; i <= rowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null)
            return false;
        Cell cell = row.getCell(columnIndex);
        if (cell == null)
            return false;
        newCellStyle.cloneStyleFrom(cell.getCellStyle());// ??
        newCellStyle.cloneStyleFrom(style); //   ??
        cell.setCellStyle(newCellStyle);

    }

    return true;
}

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

License:Apache License

/**
 * ????/*from  www  . jav a  2s .  c  o 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.fjn.helper.common.io.file.office.excel.ListExcelSheetReader.java

License:Apache License

private <T> T getBean(Class<T> clazz, Row row, List<String> fieldList) {
    Field[] fields = new Field[fieldList.size()];
    for (int i = 0; i < fields.length; i++) {
        Field field = null;/*  w  ww  . j  a v a 2  s . c  o m*/
        try {
            field = clazz.getDeclaredField(fieldList.get(i));
            // field.getAnnotation();
            fields[i] = field;
            field.setAccessible(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    T t = null;
    try {
        t = clazz.newInstance();
    } catch (Exception e) {
        e.printStackTrace();
    }

    for (int i = 0; i < fields.length; i++) {
        Cell cell = null;
        cell = row.getCell(i);
        if (cell != null) {
            int cellType = cell.getCellType();
            Object value = null;
            switch (cellType) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                    Class fieldClass = fields[i].getType();
                    if (fieldClass == Integer.class || fieldClass == int.class) {
                        value = ((Double) value).intValue();
                    } else if (fieldClass == Short.class || fieldClass == short.class) {
                        value = ((Double) value).shortValue();
                    } else if (fieldClass == Byte.class || fieldClass == byte.class) {
                        value = ((Double) value).byteValue();
                    } else if (fieldClass == Long.class || fieldClass == long.class) {
                        value = ((Double) value).longValue();
                    } else if (fieldClass == Float.class || fieldClass == float.class) {
                        value = ((Double) value).floatValue();
                    } else if (fieldClass == Double.class || fieldClass == double.class) {
                        value = (Double) value;
                    }
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                break;
            }

            try {
                fields[i].set(t, value);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    return t;
}

From source file:com.fota.devMgt.controller.DevOpenMgtCTR.java

License:Open Source License

/**
 *  ?  , ? ?   .//  ww  w. j  a  v a  2 s  .com
 * ?  ?.
 * @param vo
 * @param model
 * @return
 */
@RequestMapping(value = "/openInfoUpload")
public ModelAndView uploadTest(HttpServletRequest request, HttpServletResponse response,
        @ModelAttribute("devSearchVO") DevSearchVO vo, ModelMap model) throws Exception {
    if (!request.getServletPath().equals("/commonDevice/devMgt/openInfoUpload")) {
        response.setStatus(403);
        return null;
    }
    //   ? ?
    String path = "/jb_log/excelUpload/devOpenInfoFiles_tmp/";
    File pysicalFolder = new File(path);
    if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) {
        pysicalFolder.mkdirs();
    }

    //  ?
    MultipartRequest multi = new MultipartRequest(request, path, 10 * 1024 * 1024, "utf-8");
    String upFile = multi.getFilesystemName("file");

    if (upFile == null || upFile.equals("")) {
        model.addAttribute("msg", "?? .");
        return new ModelAndView(ajaxMainView, model);
    }

    // poi    ? ? ?
    File upfile = new File(path + upFile);

    // poi 
    try {
        FileInputStream file = new FileInputStream(upfile);

        //.xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        // 
        XSSFSheet sheet = workbook.getSheetAt(0);

        //  
        Iterator<Row> rowIterator = sheet.iterator();

        //    ?? 
        List<DevSearchVO> rs = new ArrayList();

        // ? 
        rowIterator.next();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            DevSearchVO tmp = new DevSearchVO();
            rs.add(tmp);

            // 
            Cell cell = row.getCell(0);
            tmp.setBizNm(cell.toString().trim());
            if (tmp.getBizNm().length() > 50) {
                tmp.setBizNm(tmp.getBizNm().substring(0, 49)); // ? 
            }

            // 
            cell = row.getCell(1);
            tmp.setSvcNm(cell.toString().trim());
            if (tmp.getSvcNm().length() > 50) {
                tmp.setSvcNm(tmp.getSvcNm().substring(0, 49)); // ? 
            }

            // ??
            cell = row.getCell(2);
            tmp.setDevModelNm(cell.toString().trim());
            if (tmp.getDevModelNm().length() > 50) {
                tmp.setDevModelNm(tmp.getDevModelNm().substring(0, 49)); // ? 
            }

            // CTN
            cell = row.getCell(3);
            tmp.setCtn(cell.toString().trim());
            if (tmp.getCtn().length() > 20) {
                tmp.setCtn(tmp.getCtn().substring(0, 19)); // ? 
            }

            // 
            cell = row.getCell(4);
            tmp.setDealerNm(cell.toString().trim());
            if (tmp.getDealerNm().length() > 50) {
                tmp.setDealerNm(tmp.getDealerNm().substring(0, 49)); // ? 
            }

            //  
            cell = row.getCell(5, row.CREATE_NULL_AS_BLANK);
            tmp.setCustomTag(cell.toString().trim());
            if (tmp.getCustomTag().length() > 30) {
                tmp.setCustomTag(tmp.getCustomTag().substring(0, 29)); // ? 
            }

            // ? ?? 
            cell = row.getCell(6, row.CREATE_NULL_AS_BLANK);
            tmp.setApprovalYn(cell.toString().trim());
            if (tmp.getApprovalYn().equals(""))
                tmp.setApprovalYn("Y");
            if (!tmp.getApprovalYn().equals("Y"))
                tmp.setApprovalYn("N");

            tmp.setMemo("");
        }

        for (DevSearchVO tmp : rs) {
            mySVC.addDevInfo(tmp);
        }

        DevSearchVO footer = new DevSearchVO();
        rs.add(footer);

        file.close();
        upfile.delete();

        DevSearchVO tempVo;
        // -  ? java-Ctrl ?  - DMS ?
        if (rs != null && rs.size() > 0) {
            for (int i = 0; i < rs.size(); i++) {
                tempVo = rs.get(i);
                if (tempVo.getCtn() != null && !tempVo.getCtn().isEmpty()) {
                    tempVo.setCtn(Masking.convertCtn(tempVo.getCtn()));
                }
            }
        }

        model.addAttribute("gridData", rs);
    } catch (Exception e) {
        e.printStackTrace();
        upfile.delete();
        model.addAttribute("msg", "?? ?   .");
    }

    return new ModelAndView(ajaxMainView, model);
}

From source file:com.fota.fota3g.deviceMgt.controller.TargetGroupMgtCTR.java

License:Open Source License

/**
 * targetGroup insert - Excel//from ww w.  j  a v a2s.  c  om
 * @param param
 * @param response
 * @return
 */
@RequestMapping(value = "/addTargetGroupExcel")
@ResponseBody
public ModelAndView addTargetGroupExcel(HttpServletRequest request, HttpServletResponse response,
        ModelMap model) throws Exception {
    //      logger.warn(" >>> TargetGroupMgtCTR - addTargetGroupExcel() start... ");

    String retMsg = "";

    //   ? ?
    String path = "/jb_log/excelUpload/targetGroup/";
    File pysicalFolder = new File(path);
    if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) {
        pysicalFolder.mkdirs();
    }

    //  ?
    MultipartRequest multi = new MultipartRequest(request, path, 10 * 1024 * 1024, "utf-8");
    String upFileName = multi.getFilesystemName("deviceIdF");

    //      logger.warn("path : " + path + " ### name : " + upFileName);

    // user  
    HttpSession httpSession = request.getSession(false);
    UserLoginVO userVo = (UserLoginVO) httpSession.getAttribute("userInfo");
    String userId = userVo.getUserId();

    TargetGroupVo vo = new TargetGroupVo();

    vo.setMoId(userId);
    vo.setListGroupName(multi.getParameter("listGroupName"));
    vo.setDescription(multi.getParameter("description"));
    vo.setDelYN(multi.getParameter("delYN"));
    vo.setDeviceType(multi.getParameter("deviceType"));
    vo.setInputType(multi.getParameter("inputType"));

    //      logger.warn(" >>> TargetGroupMgtCTR - addTargetGroupExcel() vo info\r\n"
    //            + "moId : " + vo.getMoId() + "\r\n"
    //            + "listGroupName : " + vo.getListGroupName() + "\r\n"
    //            + "description : " + vo.getDescription() + "\r\n"
    //            + "delYN : " + vo.getDelYN() + "\r\n"
    //            + "deviceType : " + vo.getDeviceType() + "\r\n"
    //            + "inputType : " + vo.getInputType() + "\r\n"
    //            );

    // poi    ? ? ?
    File upfile = new File(path + upFileName);

    // poi 
    try {
        FileInputStream file = new FileInputStream(upfile);

        //.xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        // 
        XSSFSheet sheet = workbook.getSheetAt(0);

        //  
        Iterator<Row> rowIterator = sheet.iterator();

        // ? ? ctn list ?
        List<String> ctnList = new ArrayList<String>();

        // ? 
        //           rowIterator.next();
        String tempCtn = "";
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();
            tempCtn = "";

            // ctn
            Cell cell = row.getCell(0);
            tempCtn = cell.toString().trim();
            if (tempCtn != null && !tempCtn.isEmpty()) {
                ctnList.add(tempCtn);
            }
        }

        //           logger.warn("-----------------------------------------------------------------");
        //           for(int i=0; i<ctnList.size(); i++) {
        //              logger.warn("ctnList[" + i + "] : " + ctnList.get(i));
        //           }
        //           logger.warn("-----------------------------------------------------------------");

        // ? (console)   ?
        //  ? ?  '?'   ?? 
        vo.setInputType("console");
        StringBuffer sbTempDevIdC = new StringBuffer();
        for (int i = 0; i < ctnList.size(); i++) {
            sbTempDevIdC.append(ctnList.get(i) + "\n");
        }

        //         logger.warn("ctn str >>>>>\r\n" + sbTempDevIdC.toString());
        vo.setDeviceIdC(sbTempDevIdC.toString());

        vo = targetSVC.insertTargetGroup(vo);
    } catch (Exception e) {
        logger.warn("?? ?   .");
        e.printStackTrace();

        model.addAttribute("msg", "?? ?   .");
    }

    // db  ?  ? 
    upfile.delete();

    return new ModelAndView(ajaxMainView, model);
}

From source file:com.fota.fota3g.selffotaMgt.controller.ListManagementCTR.java

License:Open Source License

/**
 * confirm //from   w  w  w.ja  v a2s.  c om
 * @param param
 * @param response
 * @return
 */
@RequestMapping(value = "/registListMgt")
@ResponseBody
public ModelAndView registListMgt(HttpServletRequest request, HttpServletResponse response, ModelMap model)
        throws Exception {
    //      logger.warn(" >>>>> Self Fota - ListManagementCTR - registListMgt() start...");

    String retMsg = "-1";

    //   ? ?
    String path = "/jb_log/excelUpload/selfFotaList/";
    File pysicalFolder = new File(path);
    if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) {
        pysicalFolder.mkdirs();
    }

    //  ?
    MultipartRequest multi = new MultipartRequest(request, path, 10 * 1024 * 1024, "utf-8");
    String upFileName = multi.getFilesystemName("file");

    // ?  ?  
    if (upFileName == null || upFileName.equals("")) {
        model.addAttribute("msg", "?? .");
        return new ModelAndView(ajaxMainView, model);
    }

    // db  Map
    Map<String, String> entity = new HashMap<String, String>();

    // user  
    HttpSession httpSession = request.getSession(false);
    UserLoginVO userVo = (UserLoginVO) httpSession.getAttribute("userInfo");
    String userId = userVo.getUserId();
    //      int devCnt = 0;

    entity.put("reg_id", userId);
    entity.put("file_name", upFileName);
    entity.put("file_path", path); // 3G FOTA src ? path  ? (path + name )
    entity.put("company_name", VoV.replaceRoundBracket(multi.getParameter("maker")));
    //      entity.put("device_cnt", devCnt + "");    // device count  excel file ? count   ?.

    // poi    ? ? ?
    File upfile = new File(path + upFileName);

    List<Map<String, String>> devList = new ArrayList<Map<String, String>>();

    // poi 
    try {
        FileInputStream file = new FileInputStream(upfile);

        //.xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        // 
        XSSFSheet sheet = workbook.getSheetAt(0);

        //  
        Iterator<Row> rowIterator = sheet.iterator();

        // ? ? 
        Map<String, String> devInfo = null;

        // ? 
        rowIterator.next();

        while (rowIterator.hasNext()) {
            devInfo = new HashMap<String, String>();

            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            // model
            Cell cell = row.getCell(0);
            devInfo.put("device_model_name", cell.toString().trim());

            // previous version
            cell = row.getCell(1);
            devInfo.put("device_pre_ver", cell.toString().trim());

            // next version
            cell = row.getCell(2);
            devInfo.put("device_app_ver", cell.toString().trim());

            // ctn
            cell = row.getCell(3);
            devInfo.put("ctn", cell.toString().trim());

            // imei
            cell = row.getCell(4);
            devInfo.put("device_imei", cell.toString().trim());

            devList.add(devInfo);
        }

        entity.put("device_cnt", devList.size() + "");

        //           logger.warn("insertXls data : " + entity.toString());
        int insertRet = listManagementSVC.insertXls(entity);
        //           logger.warn("insertXls ret : " + insertRet);

        String master_idx = "";

        if (insertRet > 0) {
            master_idx = listManagementSVC.getInsertedMasterIdx(entity);
            //            logger.warn("getInsertedMasterIdx ret (master_idx) : " + master_idx);
        }

        int count = 0;

        // ?  db ? insert
        for (int i = 0; i < devList.size(); i++) {
            devList.get(i).put("reg_id", userId);
            devList.get(i).put("master_idx", master_idx);
            devList.get(i).put("company_name", entity.get("company_name").toString());
            devList.get(i).put("ncn", sdpApi.getNcnInfo(devList.get(i).get("ctn").toString()));
            //            devList.get(i).put("ncn", devList.get(i).get("ctn").toString() + "0001");         // test 

            //            logger.warn("devList[" + i + "] insertXlsFotaMng data : " + devList.get(i).toString());
            insertRet = listManagementSVC.insertXlsFotaMng(devList.get(i));
            //            logger.warn("devList[" + i + "] insertXlsFotaMng ret : " + insertRet);
            count++;
        }

        String msg = "? [ " + devList.size() + " ]   [ " + count
                + " ] ? ? ??.";

        //IMEI , NCN    ?  
        //         count = devList.size() - count;
        //         msg = msg + "(IMEI  : " + count + " , NCN  : " + ncnErrCnt + " )";

        retMsg = "1";
        model.addAttribute("msg", msg);

    } catch (Exception e) {
        logger.warn("?? ?   .");
        e.printStackTrace();
        upfile.delete();
        model.addAttribute("msg", "?? ?   .");
    }

    model.addAttribute("retMsg", retMsg);

    return new ModelAndView(ajaxMainView, model);
}