crm下市场活动 -导入导出excel文件

目录

一.批量导出市场活动

二.批量导入市场活动

---

一.批量导出市场活动

1.

<!-- 查询所有市场活动信息-->
    <select id="selectAllActivities" resultMap="BaseResultMap">
        select a.id ,u1.name as owner,a.name,a.start_date,a.end_date,a.cost,a.description,a.create_time,
               u2.name as create_by,a.edit_time,u3.name as edit_by

        from tbl_activity a
                 join tbl_user u1 on a.owner=u1.id
                 join tbl_user u2 on a.create_by=u2.id
                 left join tbl_user u3 on a.edit_by=u3.id

    </select>

 @RequestMapping("/workbench/activity/queryToExcel.do")
    public void queryToExcel(HttpServletResponse response) throws Exception {
        //调用service层方法 查询所有 市场活动集合
        ArrayList<MarketingActivities> activities = activityService.queryAllActivities();

        //创建一个excel文件 将 arryList里的内容放进去
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("市场活动表");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("ID");
        cell = row.createCell(1);
        cell.setCellValue("所有者");
        cell = row.createCell(2);
        cell.setCellValue("名称");
        cell = row.createCell(3);
        cell.setCellValue("开始日期");
        cell = row.createCell(4);
        cell.setCellValue("结束日期");
        cell = row.createCell(5);
        cell.setCellValue("成本");
        cell = row.createCell(6);
        cell.setCellValue("描述");
        cell = row.createCell(7);
        cell.setCellValue("创建者");
        cell = row.createCell(8);
        cell.setCellValue("创建时间");
        cell = row.createCell(9);
        cell.setCellValue("修改者");
        cell = row.createCell(10);
        cell.setCellValue("修改时间");

        //遍历activities集合 创建 HSSFRow对象 生成所有数据
        if (activities != null && activities.size() > 0) {
            MarketingActivities activity = null;
            for (int i = 0; i < activities.size(); i++) {
                activity = activities.get(i);

                row = sheet.createRow(i + 1);
                //每创建一行 数据都从 activity对象中取
                cell = row.createCell(0);
                cell.setCellValue(activity.getId());
                cell = row.createCell(1);
                cell.setCellValue(activity.getOwner());
                cell = row.createCell(2);
                cell.setCellValue(activity.getName());
                cell = row.createCell(3);
                cell.setCellValue(activity.getStartDate());
                cell = row.createCell(4);
                cell.setCellValue(activity.getEndDate());
                cell = row.createCell(5);
                cell.setCellValue(activity.getCost());
                cell = row.createCell(6);
                cell.setCellValue(activity.getDescription());
                cell = row.createCell(7);
                cell.setCellValue(activity.getCreateTime());
                cell = row.createCell(8);
                cell.setCellValue(activity.getCreateBy());
                cell = row.createCell(9);
                cell.setCellValue(activity.getEditTime());
                cell = row.createCell(10);
                cell.setCellValue(activity.getEditBy());

            }

        }

        //根据wb对象生成 excel文件
//      OutputStream os = new FileOutputStream("D:\\挂哈哈哈哈哈哈哈\\activityList.xls");
//        wb.write(os);
//        os.close();
//        wb.close();

        //把生成的Excel文件下载到客户端


        //文件 到用户电脑
        //1.设置响应信息
        //2.设置响应头
        //3.读文件 得到response 的输出流将文件 打出去
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=activityList.xls");
        OutputStream out = response.getOutputStream();
        //读文件
/*      InputStream is = new FileInputStream("D:\\挂哈哈哈哈哈哈哈\\activityList.xls");
        //设置缓冲区
        byte[] buff = new byte[256];
        int len = 0;
        while ((len = is.read(buff)) != -1){
            out.write(buff, 0, len);
        }
        is.close();
        out.flush();*/
        wb.write(out);
        wb.close();
        out.flush();

    }

  //给批量导出 按钮添加单击事件
            $("#exportActivityAllBtn").click(function () {
                window.location.href = "workbench/activity/queryToExcel.do";
            });

二.批量导入市场活动

用户只用写四段 剩下的 在controller中可以固定封装

<!-- 导入市场活动-->
<insert id="insertActivitiesByExcel" parameterType="com.bjpowernode.ssm.workbench.domain.MarketingActivities">
insert into    tbl_activity(id, owner, name, start_date, end_date, cost, description, create_time, create_by)values
   <foreach collection="List" item="obj" separator=",">
       (#{obj.id},#{obj.owner},#{obj.name},#{obj.startDate},#{obj.endDate},#{obj.cost},#{obj.description},#{obj.createTime},#{obj.createBy})

   </foreach>



</insert>

 //导入市场活动
    @RequestMapping("/workbench/activity/importActivity.do")
    @ResponseBody
    public Object importActivity(MultipartFile activityFile, String userName, HttpSession session) throws Exception {
        User user = (User) session.getAttribute(contant.SESSION_USER);
        returnObject returnObject = new returnObject();
        System.out.println("======================dadadada==========" + userName);
        //1.根据文件 ，获取里面数据 将其封装到arrayList中
        try {
            InputStream is = activityFile.getInputStream();
            HSSFWorkbook wb = new HSSFWorkbook(is);
            HSSFSheet sheet = wb.getSheetAt(0);
            //根据sheet获取HSSFRow对象 封装一行的信息 到activity对象中
            HSSFRow row = null;
            HSSFCell cell = null;
            MarketingActivities activity = null;
            List<MarketingActivities> activityList = new ArrayList<>();

            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);
                activity = new MarketingActivities();
                activity.setId(UUIDUtils.getUUID());
                activity.setOwner(user.getId());
                activity.setCreateTime(DataUtils.format(new Date()));
                activity.setCreateBy(user.getId());

                //然后将Excel中的内容封装到对象中
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    //获取列中的数据
                    String cellValue = HSSFUtils.getCellValueForStr(cell);
                    if (j == 0) {
                        activity.setName(cellValue);
                    } else if (j == 1) {
                        activity.setStartDate(cellValue);
                    } else if (j == 2) {
                        activity.setEndDate(cellValue);
                    } else if (j == 3) {
                        activity.setCost(cellValue);
                    } else if (j == 4) {
                        activity.setDescription(cellValue);
                    }
                }
//每一行封装完后 加入list集合
                activityList.add(activity);

            }
            //调用service层方法 保存市场活动

            int saveCount = activityService.saveActivitiesByExcel(activityList);
            if (saveCount > 0) {
                returnObject.setCode(contant.RETURN_CODE_SUCCESS);
                returnObject.setRetData(saveCount);
            } else {
                returnObject.setCode(contant.RETURN_CODE_FAIL);
                returnObject.setMessage("系统忙~~请稍后");
            }


        } catch (Exception e) {
            e.printStackTrace();
            returnObject.setCode(contant.RETURN_CODE_FAIL);
            returnObject.setMessage("系统忙~~请稍后");
        }

        return returnObject;
    }

//给"导入"按钮添加单击事件
            $("#importActivityBtn").click(function () {
                //收集参数
                var activityFileName = $("#activityFile").val();
                //获取文件名
                var suffix = activityFileName.substr(activityFileName.lastIndexOf(".") + 1).toLocaleLowerCase();//xls,XLS,Xls,xLs,....
                if (suffix != "xls") {
                    alert("只支持xls文件");
                    return;
                }
                //得到文件
                var activityFile = $("#activityFile")[0].files[0];
                if (activityFile.size > 5 * 1024 * 1024) {
                    alert("文件大小不超过5MB");
                    return;
                }
                //利用异步请求  来向后端发送文件
                //FormData是ajax提供的接口,可以模拟键值对向后台提交参数;
                //FormData最大的优势是不但能提交文本数据，还能提交二进制数据
                var formData = new FormData();
                formData.append("activityFile", activityFile);
                formData.append("userName", "张三");
                $.ajax({
                    url: 'workbench/activity/importActivity.do',
                    data: formData,
                    processData: false,
                    contentType: false,
                    type: 'post',
                    dataType: 'json',
                    success: function (data) {
                        if (data.code = "1") {
                            //导入市场活动成功，关闭模态窗口，刷新列表
                            $("#importActivityModal").modal("hide");
                            alert(data.retData + "条数据导入");
                            queryActivityByCondition(1, $("#demo_pag1").bs_pagination('getOption', 'rowsPerPage'));

                        } else {
                            //提升失败信息
                            alert(data.message);
                            //模态窗口不关闭
                            $("#importActivityModal").modal("show");

                        }

                    }
                });
            });


        });

//利用异步请求  来向后端发送文件
//FormData是ajax提供的接口,可以模拟键值对向后台提交参数;
//FormData最大的优势是不但能提交文本数据，还能提交二进制数据