public static String getCellData(int RowNum, int ColNum) throws Exception {

    try {

      Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);

      int dataType = Cell.getCellType();

      if (dataType == 3) {

        return "";

      } else {

        String CellData = Cell.getStringCellValue();

        return CellData;
      }
    } catch (Exception e) {

      System.out.println(e.getMessage());

      throw (e);
    }
  }
Exemplo n.º 2
0
  // returns the data from a cell
  public String getCellData(String sheetName, int colNum, int rowNum) {
    try {
      if (rowNum <= 0) return "";
      int index = workBook.getSheetIndex(sheetName);
      if (index == -1) return "";
      sheet = workBook.getSheetAt(index);
      row = sheet.getRow(rowNum - 1);
      if (row == null) return "";
      cell = row.getCell(colNum);
      if (cell == null) return "";
      if (cell.getCellType() == Cell.CELL_TYPE_STRING) return cell.getStringCellValue();
      else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
          || cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        String cellText = String.valueOf(cell.getNumericCellValue());
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
          // format in form of M/D/YY
          double d = cell.getNumericCellValue();

          Calendar cal = Calendar.getInstance();
          cal.setTime(HSSFDateUtil.getJavaDate(d));
          cellText = (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
          cellText =
              cal.get(Calendar.MONTH) + 1 + "/" + cal.get(Calendar.DAY_OF_MONTH) + "/" + cellText;
          // System.out.println(cellText);
        }
        return cellText;
      } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return "";
      else return String.valueOf(cell.getBooleanCellValue());
    } catch (Exception e) {
      e.printStackTrace();
      return "row " + rowNum + " or column " + colNum + " does not exist  in xls";
    }
  }
Exemplo n.º 3
0
 public static void copyCell(XSSFCell oldCell, XSSFCell newCell) {
   try {
     switch (oldCell.getCellType()) {
       case XSSFCell.CELL_TYPE_STRING:
         newCell.setCellValue(oldCell.getStringCellValue());
         break;
       case XSSFCell.CELL_TYPE_NUMERIC:
         newCell.setCellValue(oldCell.getNumericCellValue());
         break;
       case XSSFCell.CELL_TYPE_BLANK:
         newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
         break;
       case XSSFCell.CELL_TYPE_BOOLEAN:
         newCell.setCellValue(oldCell.getBooleanCellValue());
         break;
       case XSSFCell.CELL_TYPE_ERROR:
         newCell.setCellErrorValue(oldCell.getErrorCellValue());
         break;
       case XSSFCell.CELL_TYPE_FORMULA:
         newCell.setCellFormula(oldCell.getCellFormula());
         break;
       default:
         break;
     }
   } catch (Exception ex) {
     System.out.println("Error in writing Cell in reject excel file :: " + ex.getMessage());
     ex.printStackTrace();
   }
 }
 // This method is to read the test data from the Excel cell, in this we are passing parameters as
 // Row num and Col num
 public static String getCellData(int RowNum, int ColNum) throws Exception {
   try {
     Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
     String CellData = Cell.getStringCellValue();
     return CellData;
   } catch (Exception e) {
     return "";
   }
 }
Exemplo n.º 5
0
  public List<String> getColumnNames(String sheetname) {
    XSSFSheet worksheet = _workbook.getSheet(sheetname);
    Iterator<Cell> cellIterator = worksheet.getRow(0).cellIterator();
    List<String> colnames = new ArrayList<String>();

    while (cellIterator.hasNext()) {
      XSSFCell cell = (XSSFCell) cellIterator.next();
      colnames.add(cell.getStringCellValue());
    }

    return colnames;
  }
Exemplo n.º 6
0
 /**
  * セルの値を取得する
  *
  * @param row 行データ
  * @param nColumn 列番号
  * @return セルの値
  */
 public static Object getData(XSSFRow row, int nColumn) {
   if (row != null) {
     XSSFCell cell = row.getCell(nColumn);
     if (cell != null) {
       if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
         return cell.getNumericCellValue();
       } else if (XSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
         return cell.getStringCellValue();
       }
     }
   }
   return null;
 }
Exemplo n.º 7
0
 /**
  * 获取每个cell的数据
  *
  * @param cell
  * @return object
  */
 public Object getXssCellData(Object cell) {
   Object cellData = "";
   if (cell != null) {
     XSSFCell xssCell = (XSSFCell) cell;
     if (xssCell != null) {
       switch (xssCell.getCellType()) {
         case XSSFCell.CELL_TYPE_STRING:
           cellData = xssCell.getStringCellValue();
           if (cellData != null) {
             cellData = String.valueOf(cellData).trim();
           }
           break;
         case XSSFCell.CELL_TYPE_NUMERIC:
           if (DateUtil.isCellDateFormatted(xssCell)) {
             SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
             cellData = df.format(xssCell.getDateCellValue());
             df = null;
           } else {
             DecimalFormat df = new DecimalFormat("0");
             cellData = df.format(xssCell.getNumericCellValue());
             df = null;
           }
           break;
         case XSSFCell.CELL_TYPE_BLANK:
           cellData = "";
           break;
         case XSSFCell.CELL_TYPE_BOOLEAN:
           cellData = String.valueOf(xssCell.getBooleanCellValue());
           break;
         case XSSFCell.CELL_TYPE_ERROR:
           break;
         default:
           break;
       }
     }
     xssCell = null;
   }
   return cellData;
 }
 /**
  * @param oldCell
  * @param newCell
  * @param styleMap
  */
 public static void copyCell(
     XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
   if (styleMap != null) {
     if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
       newCell.setCellStyle(oldCell.getCellStyle());
     } else {
       int stHashCode = oldCell.getCellStyle().hashCode();
       XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
       if (newCellStyle == null) {
         newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
         newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
         styleMap.put(stHashCode, newCellStyle);
       }
       newCell.setCellStyle(newCellStyle);
     }
   }
   switch (oldCell.getCellType()) {
     case HSSFCell.CELL_TYPE_STRING:
       newCell.setCellValue(oldCell.getStringCellValue());
       break;
     case HSSFCell.CELL_TYPE_NUMERIC:
       newCell.setCellValue(oldCell.getNumericCellValue());
       break;
     case HSSFCell.CELL_TYPE_BLANK:
       newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
       break;
     case HSSFCell.CELL_TYPE_BOOLEAN:
       newCell.setCellValue(oldCell.getBooleanCellValue());
       break;
     case HSSFCell.CELL_TYPE_ERROR:
       newCell.setCellErrorValue(oldCell.getErrorCellValue());
       break;
     case HSSFCell.CELL_TYPE_FORMULA:
       newCell.setCellFormula(oldCell.getCellFormula());
       break;
     default:
       break;
   }
 }
Exemplo n.º 9
0
  /** 导入Excel表格 */
  @SuppressWarnings("unused")
  public String intoDB() throws IOException {
    String uploadPath = ServletActionContext.getServletContext().getRealPath("/upload");
    isImpSuccess = false; // 判断数据导入是否成功
    errCount = 0; // 判断导入数据模板是否对应
    // 基于myFile创建一个文件输入流
    InputStream is = new FileInputStream(myFile);
    // 设置目标文件
    File toFile = new File(uploadPath, this.getMyFileFileName());

    String caseName = null; // 活动名称
    String caseDesc = null; // 活动描述
    Timestamp caseSt = null; // 活动开始时间
    Timestamp caseEt = null; // 活动结束时间
    String sysUserId = null; // 创建/修改用户ID
    Timestamp sysDt = null; // 修改时间
    Integer status = null; // 活动状态
    String caseCode = null; // 活动编码
    Double ratioNew = null; // 新款占比
    Integer num = null; // 参与款数

    Date date = new Date(); // 创建一个时间对象,获取到当前的时间
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 设置时间显示格式
    String str = sdf.format(date); // 将当前时间格式化为需要的类型
    sysDt = Timestamp.valueOf(str);
    sysUserId = ParaCasePAction.getCurrentUserName();

    /** 2007版的读取方法 */
    int k = 0;
    int flag = 0; // 指示指针所访问的位置
    if (myFile != null) {
      try {
        Workbook workbook = WorkbookFactory.create(toFile);
        intolist.clear();
        // Workbook workbook = new XSSFWorkbook(is);//初始化workbook对象
        for (int numSheets = 0;
            numSheets < workbook.getNumberOfSheets();
            numSheets++) { // 读取每一个sheet
          if (null != workbook.getSheetAt(numSheets)) {
            XSSFSheet aSheet = (XSSFSheet) workbook.getSheetAt(numSheets); // 定义Sheet对象

            for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
              // 进入当前sheet的行的循环
              if (null != aSheet.getRow(rowNumOfSheet)) {
                XSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 定义行,并赋值

                for (int cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {

                  // 读取rowNumOfSheet值所对应行的数据
                  XSSFCell xCell = aRow.getCell(cellNumOfRow); // 获得行的列数
                  // //获得列值
                  // System.out.println("type="+xCell.getCellType());
                  if (null != aRow.getCell(cellNumOfRow)) {
                    // 如果rowNumOfSheet的值为0,则读取表头,判断excel的格式和预定格式是否相符
                    if (rowNumOfSheet == 1) {
                      if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        /**
                         * 一下根据从Excel的各列命名是否符合要求:如下面匹配: 活动名称,活动描述,活动开始时间, 活动结束时间,操作用户,
                         * 修改时间,活动状态,活动编码,新款占比 ,参与款数,备注开始时间,备注结束时间
                         */
                        if (cellNumOfRow == 0) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动名称")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 1) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动描述")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 2) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动开始时间")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 3) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动结束时间")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 4) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动状态")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 5) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动编码")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 6) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("新款占比")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 7) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("参与款数")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        }
                      }
                    } else {
                      // rowNumOfSheet != 0 即开始打印内容
                      // 获取excel中每列的值,并赋予相应的变量,如下的赋值的ID,name,sex,
                      // Dormitory,sept;
                      if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { // 为数值型
                        System.out.println(
                            "===============进入XSSFCell .CELL_TYPE_NUMERIC模块============");
                        switch (cellNumOfRow) {
                          case 2:
                            Date St = (Date) xCell.getDateCellValue(); // 对日期处理
                            caseSt = new Timestamp(St.getTime());
                            break;
                          case 3:
                            Date Et = (Date) xCell.getDateCellValue(); // 对日期处理
                            caseEt = new Timestamp(Et.getTime());
                            break;
                          case 4:
                            status = (int) xCell.getNumericCellValue();
                            break;
                          case 6:
                            ratioNew = xCell.getNumericCellValue();
                            break;
                          case 7:
                            num = (int) xCell.getNumericCellValue();
                            break;
                        }
                      } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { // 为字符串型
                        System.out.println(
                            "===============进入XSSFCell .CELL_TYPE_STRING模块============");
                        switch (cellNumOfRow) {
                          case 0:
                            caseName =
                                xCell
                                    .getStringCellValue()
                                    .replace('\t', ' ')
                                    .replace('\n', ' ')
                                    .replace('\r', ' ')
                                    .trim();
                            break;
                          case 1:
                            caseDesc =
                                xCell
                                    .getStringCellValue()
                                    .replace('\t', ' ')
                                    .replace('\n', ' ')
                                    .replace('\r', ' ')
                                    .trim();
                            break;
                          case 5:
                            caseCode =
                                xCell
                                    .getStringCellValue()
                                    .replace('\t', ' ')
                                    .replace('\n', ' ')
                                    .replace('\r', ' ')
                                    .trim();
                            break;
                        }
                      } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                        System.out.println(
                            "提示:在Sheet"
                                + (numSheets + 1)
                                + "中的第"
                                + (rowNumOfSheet + 1)
                                + "行的第"
                                + (cellNumOfRow + 1)
                                + "列的值为空,请查看核对是否符合约定要求");
                        switch (cellNumOfRow) {
                          case 0:
                            caseName = "";
                            break;
                          case 1:
                            caseDesc = "";
                            break;
                          case 2:
                            caseSt = null;
                            break;
                          case 3:
                            caseEt = null;
                            break;
                          case 4:
                            status = null;
                            break;
                          case 5:
                            caseCode = "";
                            break;
                        }
                      }
                    }
                  }
                }
                // 判断各个元素被赋值,如果放入数据库,就直接使用数据的插入的函数就可以了。
                if (aRow.getRowNum() > 1) {
                  ParaDt pd = new ParaDt();

                  pd.setCaseName(caseName);
                  pd.setCaseDesc(caseDesc);
                  pd.setCaseEt(caseEt);
                  pd.setCaseSt(caseSt);
                  pd.setSysUserId(sysUserId);
                  pd.setSysDt(sysDt);
                  pd.setStatus(status);
                  pd.setCaseCode(caseCode);
                  pd.setRatioNew(ratioNew);
                  pd.setNum(num);
                  pd.setCaseCode(caseCode);

                  intolist.add(pd);
                }
              } // 获得一行,即读取每一行
            }
            // 读取每一个sheet
          }
        }
        refreshList = "paraCaseDtgetParaDtAll";
        titleName = "营销活动实例";
        if (errCount > 0) {
          msg = "导入数据与模板不符,导入失败!";
        } else {
          // 调用sever方法
          isImpSuccess = paraDtService.addOneBoat(intolist, 500);
          if (isImpSuccess) {
            msg = "营销活动实例导入成功!";
          } else {
            msg = "营销活动实例导入失败!";
          }
        }

      } catch (Exception e) {
        e.printStackTrace();
      }
    }

    return "importExcel";
  }