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.fota.fota3g.selffotaMgt.controller.ListManagementCTR.java

License:Open Source License

/**
 * confirm //  w  ww .  ja  va 2  s  .c o m
 * @param param
 * @param response
 * @return
 */
@RequestMapping(value = "/setXmlData_back")
@ResponseBody
public ModelAndView setXmlDataRead_back(HttpServletRequest request, HttpServletResponse response,
        @ModelAttribute("ListManagementVo") ListManagementVo vo, ModelMap model) throws Exception {
    logger.warn("#############setXmlData################");

    {
        Date now = new Date();
        logger.info("File update start : " + now.toString());

        String default_path = "F:\\servicefota\\jboss\\download\\";

        //    ? ?
        File temp_folder = new File(default_path);
        if (!temp_folder.exists() || !temp_folder.isDirectory()) {
            temp_folder.mkdirs();
        }

        // ?? : 500M
        int maxfilesize = 500 * 1024 * 1024;
        MultipartRequest multi = null;
        File upfile = null;

        try {
            //  ?
            logger.warn("#############multi start################");
            multi = new MultipartRequest(request, default_path, maxfilesize, "utf-8");
            upfile = new File(default_path + multi.getFilesystemName("file"));

            vo.setFileName(upfile.getName());
            //            logger.warn("#############multi end################" + upfile);
            //            logger.warn("#############multi end################" + upfile);
            File upfiles = new File(default_path + upfile.getName());

            FileInputStream file = new FileInputStream(upfiles);
            //            logger.warn("#############getCell model################");
            //.xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            //              logger.warn("#############getCell model################");
            // 
            XSSFSheet sheet = workbook.getSheetAt(0);
            //              logger.warn("#############getCell model################");
            //  
            Iterator<Row> rowIterator = sheet.iterator();
            //              logger.warn("#############getCell model################");
            //    ?? 
            List<ListManagementVo> rs = new ArrayList();

            // ? 
            rowIterator.next();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                logger.warn("#############getCell model################" + row.cellIterator());
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

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

                // model
                Cell cell = row.getCell(0);
                logger.warn("#############getCell model################" + cell);
                tmp.setDeviceModelName(cell.toString().trim());
                if (tmp.getDeviceModelName().length() > 50) {
                    tmp.setDeviceModelName(tmp.getDeviceModelName().substring(0, 49)); // ? 
                    logger.warn("#############getCell model################" + tmp.getDeviceModelName());
                }

                //before version
                cell = row.getCell(1);
                logger.warn("#############getCell before version################" + cell);
                tmp.setDevicePerVer(cell.toString().trim());
                if (tmp.getDevicePerVer().length() > 50) {
                    tmp.setDevicePerVer(tmp.getDevicePerVer().substring(0, 49)); // ? 
                }

                //after version
                cell = row.getCell(2);
                logger.warn("#############getCell after version################" + cell);
                tmp.setDeviceAppVer(cell.toString().trim());
                if (tmp.getDeviceAppVer().length() > 50) {
                    tmp.setDeviceAppVer(tmp.getDeviceAppVer().substring(0, 49)); // ? 
                }

                //ctn version
                cell = row.getCell(3);
                logger.warn("#############getCell ctn version################" + cell);
                tmp.setCtn(cell.toString().trim());
                if (tmp.getCtn().length() > 50) {
                    tmp.setCtn(tmp.getCtn().substring(0, 49)); // ? 
                }

                //imei version
                cell = row.getCell(4);
                logger.warn("#############getCell imei version################" + cell);
                tmp.setDeviceImei(cell.toString().trim());
                if (tmp.getDeviceImei().length() > 50) {
                    tmp.setDeviceImei(tmp.getDeviceImei().substring(0, 49)); // ? 
                }

            }

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

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

            file.close();
            upfile.delete();
            model.addAttribute("gridData", rs);
            //            vo.setBizTypeId(VoV.valid(multi.getParameter("bizTypeId"), 30));
            //            vo.setSvcId(VoV.valid(multi.getParameter("svcId"), 30, 4));
            //            vo.setMakerId(VoV.valid(multi.getParameter("makerId"), 30, 4));
            //            vo.setDevModelId(VoV.valid(multi.getParameter("devModelId"), 30, 4));
            //            vo.setFotaType(VoV.valid(multi.getParameter("fotaType"), 50));
            //            if (!vo.getFotaType().equals("") && !vo.getFotaType().equals("PUSH") && !vo.getFotaType().equals("POLLING") && !vo.getFotaType().equals("PUSHPOLLING"))
            //               throw new Exception();
            //            
            //            //vo.setFirmwareId(VoV.valid(multi.getParameter("firmwareId"), 30, 4));
            //            //vo.setFirmwareDesc(VoV.valid(multi.getParameter("firmwareDesc"), 1000, 9));
            //            vo.setFirmwareVer(VoV.valid(multi.getParameter("firmwareVer"), 100, 4));
            //            vo.setFirmwareMakerVer(VoV.valid(multi.getParameter("firmwareMakerVer"), 100, 8));
            //            vo.setVerMemo(VoV.valid(multi.getParameter("verMemo"), 1000, 9));
            //            
            //            //   ? ?
            //            String path = default_path + vo.getDevModelId() + "/";
            //            File pysicalfolder = new File(path);
            //            if(!pysicalfolder.exists() || !pysicalfolder.isDirectory()) {
            //               pysicalfolder.mkdirs();
            //            }
            //            vo.setFilePath(VoV.valid(path, 200));
            //            
            //            //  ???
            //            File destFile = new File(path + upfile.getName());
            //            if(destFile.exists()){
            //               destFile.delete();
            //            }
            //            FileUtils.moveFile(upfile, destFile);
            //            
            //            //CRC, '0' 
            //            vo.setCrc("0");
            //            vo.setCretNm(((UserLoginVO)request.getSession().getAttribute("userInfo")).getUserNm());
            //            
        } catch (Exception e) { //  ?? 
            // ??? ? ? 
            if (upfile != null && upfile.exists()) {
                upfile.delete();
            }

            String errorMSG = e.getMessage();
            if (errorMSG.indexOf("exceeds") != 0) {
                model.addAttribute("error", "?? 500MB ?.");
            }

            logger.info("File update error : " + errorMSG);
            response.setStatus(500);
            return new ModelAndView(ajaxMainView, model);
        }

        //      mySVC.regi(vo);

        now = new Date();
        logger.info("File update end : " + now.toString());

        //      return new ModelAndView (ajaxMainView, model);
    }
    //      int retValue = 0;
    //      vo.setModId("sooya118");
    //      vo.setConfirmState("2");
    //      
    //      retValue = listManagementSVC.updataConfirm(vo);
    //      retValue = listManagementSVC.updateHold(vo);
    //      
    //      model.addAttribute("retValue", retValue);
    //      
    return new ModelAndView(ajaxMainView, model);
}

From source file:com.fota.fotastatMgt.controller.FotastatCTR.java

License:Open Source License

/**
 *  ? /*  www  .j  ava  2 s. c om*/
 * ?  ?.
 * @param vo
 * @param model
 * @return
 */
@RequestMapping(value = "/checkUploadExcelFile")
public ModelAndView checkUploadExcelFile(HttpServletRequest request, HttpServletResponse response,
        ModelMap model) throws Exception {

    //      logger.warn(" >>>>>>>>>> FotastatCTR - uploadExcelFil() start ... ");

    String retMsg = "";

    //   ? ?
    File pysicalFolder = new File(PATH);
    if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) {
        pysicalFolder.mkdirs();
    }

    //  ?
    MultipartRequest multi = new MultipartRequest(request, PATH, SIZE, "utf-8");
    String upFileName = multi.getFilesystemName("file");
    //      logger.warn("upFile : " + upFile);      

    FotastatSearchVO vo = new FotastatSearchVO();
    vo.setBizTypeId(VoV.valid(multi.getParameter("bizTypeId"), 30, 4));
    vo.setSvcId(VoV.valid(multi.getParameter("svcId"), 30, 4));
    vo.setDevMakerId(VoV.valid(multi.getParameter("devMakerId"), 30, 4));
    vo.setDevModelId(VoV.valid(multi.getParameter("devModelId"), 30, 4));
    vo.setModemMakerId(VoV.valid(multi.getParameter("modemMakerId"), 30, 4));
    vo.setModemModelId(VoV.valid(multi.getParameter("modemModelId"), 30, 4));
    vo.setDealerNm(VoV.valid(multi.getParameter("dealerNm"), 50, 2));

    //      logger.warn("value check...\r\n"
    //            + "bizTypeId : " + vo.getBizTypeId() + "\r\n"
    //            + "svcId : " + vo.getSvcId() + "\r\n"
    //            + "DevMakerId : " + vo.getDevMakerId() + "\r\n"
    //            + "DevModelId : " + vo.getDevModelId() + "\r\n"
    //            + "ModemMakerId : " + vo.getModemMakerId() + "\r\n"
    //            + "ModemModelId : " + vo.getModemModelId() + "\r\n"
    //            + "DealerNm : " + vo.getDealerNm() + "\r\n"
    //            );

    //  
    vo.setPage("1");
    vo.setRowsPerPage("1000");

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

    // 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();
            ctnList.add(tempCtn);
        }

        // ? ctn  ? 
        // db ?   ctn  
        vo = mySVC.search(vo);
        List<String> checkedCtnList = new ArrayList<String>();

        int checkCount = 0;
        int fotaPossibilityCount = 0;
        for (int i = 0; i < ctnList.size(); i++) {
            for (int j = 0; j < vo.getResults().size(); j++) {

                if (ctnList.get(i).equals(vo.getResults().get(j).getCtn())) {
                    checkedCtnList.add(ctnList.get(i));
                    checkCount++;

                    // P(), S(), V(?)  FOTA  
                    //    W(), E(?), F() 
                    if (!((vo.getResults().get(j).getProcessStatCd().equals("")
                            || vo.getResults().get(j).getProcessStatCd().equals("P"))
                            || (vo.getResults().get(j).getProcessStatCd().equals("")
                                    || vo.getResults().get(j).getProcessStatCd().equals("S"))
                            || (vo.getResults().get(j).getProcessStatCd().equals("?")
                                    || vo.getResults().get(j).getProcessStatCd().equals("V")))) {
                        fotaPossibilityCount++;
                    }
                }
            }
        }

        retMsg = "  : " + ctnList.size() + "\r\n" + "?  : " + checkCount + "\r\n"
                + " ?  : " + (ctnList.size() - checkCount) + "\r\n" + " (?   : "
                + fotaPossibilityCount + ")";

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

    return new ModelAndView(ajaxMainView, model);
}

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

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java./* ww w. java  2 s. co 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.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java./*from  www. j av  a2s  .  co  m*/
 * 
 * @param uploadFileName
 *            
 * @param titleList
 *            ???
 * @param beanType
 *            ?
 * @return
 * @throws Exception
 *             2015723
 */
public static <T> List<T> parseExcel(MultipartFile uploadFileName, int titlerow, int datarow, 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);
    PropertieDescription rowidField = classInfo.getPropertyDescriptor("rowid");
    // ?
    Sheet sheet = (Sheet) wb.getSheetAt(0);

    // ?
    int rowNum = sheet.getLastRowNum();
    Row titleRow = sheet.getRow(titlerow);

    int colNum = titleRow.getLastCellNum();
    String[] titles = new String[colNum];
    for (int i = 0; i < colNum; i++) {
        titles[i] = titleRow.getCell(i).getStringCellValue().trim();
    }

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

        if (row == null) {
            continue;
        }

        // 
        T retObject = beanType.newInstance();
        if (rowidField != null)
            rowidField.setValue(retObject, i);
        for (int j = 0; j < colNum; j++) {

            Cell cell = row.getCell(j);

            // ???
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(titles[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.funtl.framework.smoke.core.commons.excel.ImportExcel.java

License:Apache License

/**
 * ??/*from w w w . j ava2 s.  co m*/
 *
 * @param row    ?
 * @param column ???
 * @return ?
 */
@SuppressWarnings("deprecation")
public Object getCellValue(Row row, int column) {
    Object val = "";
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                val = cell.getNumericCellValue();
            } else if (cell.getCellTypeEnum() == CellType.STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                val = cell.getCellFormula();
            } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                val = cell.getErrorCellValue();
            }
        }
    } catch (Exception e) {
        return val;
    }
    return val;
}

From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to update the embedded Excel workbook. As the format and structire
 * of the workbook are known in advance, all this code attempts to do is
 * write a new value into the first cell on the first row of the first
 * worksheet. Prior to executing this method, that cell will contain the
 * value 1./*from  www  .ja  v a 2s . co m*/
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {

                // Get an InputStream from the pacage part and pass that
                // to the create method of the WorkbookFactory class. Update
                // the resulting Workbook and then stream that out again
                // using an OutputStream obtained from the same PackagePart.
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                cell.setCellValue(NEW_VALUE);
                workbook.write(pPart.getOutputStream());
            }
        }

        // Finally, write the newly modified Word document out to file.
        this.doc.write(new FileOutputStream(this.docFile));
    }
}

From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to test whether or not the embedded workbook was correctly
 * updated. This method simply recovers the first cell from the first row
 * of the first workbook and tests the value it contains.
 * <p/>// ww w .  ja  v a2  s  .  c o m
 * Note that execution will not continue up to the assertion as the
 * embedded workbook is now corrupted and causes an IllegalArgumentException
 * with the following message
 * <p/>
 * <em>java.lang.IllegalArgumentException: Your InputStream was neither an
 * OLE2 stream, nor an OOXML stream</em>
 * <p/>
 * to be thrown when the WorkbookFactory.createWorkbook(InputStream) method
 * is executed.
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void checkUpdatedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                //assertEquals(cell.getNumericCellValue(), NEW_VALUE, 0.0001);
            }
        }
    }
}

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

License:Apache License

private void createCell(final CreationHelper creationHelper, final Row row, Attribute attribute) {
    if (row.getCell(attribute.getColumnIndex()) == null) {
        final Cell cell = row.createCell(attribute.getColumnIndex());
        RichTextString cellName = creationHelper.createRichTextString(attribute.getColumnName());
        cell.setCellValue(cellName);/*w ww  .  j a  va2  s  .  c  om*/
    }
}

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;
        }// w  ww  .j  a v  a 2s.com
    }
    return true;
}

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

License:Apache License

private void assertCatHeaders(Workbook wb) {
    Sheet sheet = wb.getSheet("Cat");
    Assert.assertNotNull(sheet, "Sheet must be created");
    Row row = sheet.getRow(0);
    Assert.assertNotNull(sheet, "Header row must be created");
    assertHeader(row.getCell(0), "name");
    assertHeader(row.getCell(1), "Male?");
    assertHeader(row.getCell(2), "Birth date");
    assertHeader(row.getCell(3), "hairLength");
    assertHeader(row.getCell(4), "hairColor");
    assertHeader(row.getCell(5), "hairLength");
    assertHeader(row.getCell(6), "hairColor");
}