Example #1
0
 public static void main(String[] args) throws IOException, Exception {
   ImportExcel<Student> test = new ImportExcel<Student>(Student.class);
   File file = new File("/Users/yangdongdong/Downloads/叫中心没有推送人以及推送时间的人.xls");
   List<Student> results = (List<Student>) test.importExcel(file);
   int i = 1;
   for (Student student : results) {
     if (student.getName() != null) {
       String sql =
           "(select id from tb_p_e_user where org_id=1 and  full_name='"
               + student.getPushName()
               + "' limit 1)";
       //				System.out.println(student.getPhone());
       if (student.getMobile() != null && !student.getMobile().equals("")) {
         System.out.println(
             "update tb_e_student set push_time='"
                 + DateUtil.getDate(student.getPushDate(), "yyyy-MM-dd")
                 + "',push_id="
                 + sql
                 + " where mobile='"
                 + student.getMobile().trim()
                 + "';");
       } else if (student.getPhone() != null && !student.getPhone().equals("")) {
         System.out.println(
             "update tb_e_student set push_time='"
                 + DateUtil.getDate(student.getPushDate(), "yyyy-MM-dd")
                 + "',push_id="
                 + sql
                 + " where phone='"
                 + student.getPhone().trim()
                 + "';");
       }
     }
   }
 }
  /**
   * 导出学生集合
   *
   * @return
   * @throws Exception
   */
  @Action(
      value = "excel_export_list_student",
      results = {
        @Result(
            name = "success",
            type = "json",
            params = {"contentType", "text/json"})
      })
  public String excelExportListStudent() throws Exception {
    if (stuStage != null
        && !"".equals(stuStage)
        && !stuStage.equals("0")
        && student.getStatus() == 0) {
      List<StudentStatus> stuStatusList = this.stuStatusBiz.findStatusNamesByStageCode(stuStage);
      StringBuffer statusSB = new StringBuffer("");
      if (stuStatusList != null && stuStatusList.size() > 0) {
        for (StudentStatus ss : stuStatusList) {
          if (statusSB.toString().equals("")) {
            statusSB = new StringBuffer(ss.getId() + "");
          } else {
            statusSB.append("," + ss.getId());
          }
        }
        if (!statusSB.toString().equals("")) {
          student.setStatusIds(statusSB.toString());
        }
      }
    }

    PageResult<Student> pr = new PageResult<Student>();

    // 检查导出结果是否为空
    int count = studentBiz.findStudentsPageCountByBaseConditions(student);
    if (count == 0) {
      downloadUrl = "error" + Constants.EXPORT_EXCEL_ERROR_NO_3;
      return SUCCESS;
    }
    // 设置下载任务导出数据数量上限
    dataMaxCount = Constants.EXPORT_EXCEL_MAX_COUNT; // 如果配置文件异常则默认导出数据数量上限
    if (Config.getProperty("export.maxcount") != null) {
      try {
        dataMaxCount = Config.getIntProperty("export.maxcount");
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    // 检查导入结果是否超过上限
    if (count > dataMaxCount) {
      downloadUrl = "error" + Constants.EXPORT_EXCEL_ERROR_NO_4;
      return SUCCESS;
    }

    int $page = 1; // 起始索引
    // 设置一次导入的数据量
    int $pageSize = Constants.EXPORT_EXCEL_MAX_PAGE_SIZE; // 如果配置文件异常则默认导出数据数量上限
    if (Config.getProperty("export.maxpagesize") != null) {
      try {
        $pageSize = Config.getIntProperty("export.maxpagesize");
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    boolean only = true;
    OutputStream os = null;

    // 导出的excel数据集合
    List<StudentDataExportTemplate> studentDataExportTemplateList =
        new ArrayList<StudentDataExportTemplate>();

    // 导出的随机目录
    long ctm = System.currentTimeMillis();

    UserTask userTask = new UserTask();
    userTask.setCreateTime(new Date());
    userTask.setDownloadSumCount(0);
    userTask.setPath(ctm + "");
    userTask.setTitle("呼叫中心|在线客服|学生列表|学生导出");
    userTask.setUserId(super.getUser().getUserId());
    // 任务添加失败
    int errorno = taskBiz.addTask(userTask);
    if (errorno != Constants.EXPORT_EXCEL_FINISH) {
      downloadUrl = "error" + errorno;
      return SUCCESS;
    }

    String path = Config.getProperty("export.excel.tmp") + ctm;

    // 创建导出的工具类对象
    ExcelExport<StudentDataExportTemplate> ex = new ExcelExport<StudentDataExportTemplate>();
    // 创建随机目录
    FileUtil.CheckCreateDR(application.getRealPath(path));

    // 不分页
    pr.setPageSize($pageSize);
    pr.setPage(true);
    pr.setOrder("branchId");

    List<Student> studentList = null;

    while (only || (studentList != null && studentList.size() != 0)) {
      pr.setCurrentPageIndex($page);
      studentList = studentBiz.findStudentsPageListByBaseConditions(student, pr);
      if (studentList != null) {
        for (Student student : studentList) {
          // 市场途径
          BaseDict basedict = basedictBiz.findBaseDictById(student.getEnrollmentWay());
          // 合作方
          Channel channel = channelBiz.findChannel(student.getChannelId());
          StudentDataExportTemplate studentDataExportTemplate = new StudentDataExportTemplate();
          studentDataExportTemplate.setBranchName(student.getBranchName());
          studentDataExportTemplate.setFollowUpUserName(student.getFollowUpName());
          studentDataExportTemplate.setStudentName(student.getName());
          studentDataExportTemplate.setEnrollmentBatchName(
              student.getAcademyenrollbatchName()); // ???
          studentDataExportTemplate.setAcademyName(student.getSchoolName());
          studentDataExportTemplate.setLevelName(student.getLevelName());
          studentDataExportTemplate.setMajorName(student.getMajorName());
          studentDataExportTemplate.setStudentDataSource(
              ConstantsStudentDataSourceMap.getCode(student.getStudentDataSource()));
          studentDataExportTemplate.setEnrollmentSource(student.getEnrollmentSourceName1());
          studentDataExportTemplate.setChannelName(channel == null ? "未知" : channel.getName());
          studentDataExportTemplate.setEnrollmentWay(basedict == null ? "--" : basedict.getName());
          studentDataExportTemplate.setCallStatus(
              ConstantsCallStatusMap.getCode(student.getCallStatus()));
          studentDataExportTemplate.setIsChannelTypeChecked(
              ConstantsChannelTypeCheckedMap.getCode(student.getIsChannelTypeChecked()));
          studentDataExportTemplate.setStatus(
              ConstantsStudentStatusMap.getCode(student.getStatus()));
          studentDataExportTemplate.setCertNo(student.getCertNo());
          studentDataExportTemplate.setMobile(student.getMobile());
          studentDataExportTemplate.setPhone(student.getPhone());
          studentDataExportTemplate.setRegistrationTime(
              student.getRegistrationTime() == null
                  ? ""
                  : student.getRegistrationTime().toString().substring(0, 10));
          studentDataExportTemplate.setRemark(student.getRemark());
          studentDataExportTemplateList.add(studentDataExportTemplate);
        }
        // 随机产生的文件名称前缀
        String dateStr = DateUtil.dateToStringWithTime(new Date());
        // 随机产生的文件名称以及后缀
        String filePath = dateStr + ".xls";
        // 文件全目录
        String tempPath = application.getRealPath(path) + File.separator + filePath;
        // 创建流对象

        os = new FileOutputStream(tempPath);
        // 开始导出
        ex.exportExcel("导出结果", studentDataExportTemplateList, os);
        // 关闭流
        os.close();
        studentDataExportTemplateList.clear();
        os = null;
        $page++;
        only = false;
        if (studentList.size() < $pageSize) {
          break;
        }
      }
    }
    // 压缩文件
    File inFile = new File(application.getRealPath(path));
    ZipOutputStream zos =
        new ZipOutputStream(new FileOutputStream((application.getRealPath(path) + ".zip.tmp")));
    ZipUtil.zipFile(inFile, zos, "");

    zos.close();
    zos = null;
    // 执行改名
    FileUtil.renamed(application.getRealPath(path), ".zip.tmp", ".zip");
    System.out.println(downloadUrl = path + ".zip");
    return SUCCESS;
  }