十三、市场活动：全部导出

功能需求：批量导出市场活动

用户在市场活动主页面,点击"批量导出"按钮,把所有市场活动生成一个excel文件,弹出文件下载的对话框;

 用户选择要保存的目录,完成导出市场活动的功能.

*导出成功之后,页面不刷新

功能分析：导出市场活动

1.给批量“导出”按钮，单击事件。点击按钮，向后台发送导出请求

2.后台controller接收请求，查询所有的市场活动

3.创建一个excel文件，把查询出来的文件写道excel文件中

4.把生成的excel文件，输出到浏览器，进行下载

文件导出插件:apache-poi插件

【技术准备】

把办公文档的所有元素封装成普通java类，通过操作这些类进行文件导出

• HSSFRow------行
• HSSFCell-------列
• HSSFSheet-------页
• HSSFWorkbook-------文件
• HSSFCellStyle--------样式

【插件使用】

1.添加依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>

2.使用封装类生成excel

3.文件下载

全部导出流程图

 代码实现

一、ActivityMapper

1.ActivityMapper接口

    /**
     * 批量导出
     */
    List<Activity> selectAllActivities();

    <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
        order by a.create_time desc
    </select>

二、ActivityService

	/**
	 * 批量导出
	 */
	List<Activity> queryAllActivities();

	@Override
	public List<Activity> queryAllActivities() {
		return activityMapper.selectAllActivities();
	}

三、ActivityController

1.写一个下载文件的工具类HSSFUtils

/**
	 * 下载市场活动的Excel
	 * activityList 市场活动集合
	 * fileName文件名
	 */
	public static void createExcelByActivityList(List<Activity> activityList, String fileName, HttpServletResponse response) throws Exception {
		// 2.创建Excel
		// 2.1.1创建文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 2.1.2 创建表
		HSSFSheet sheet = wb.createSheet("市场活动");
		// 2.1.3 创建行和列
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell(0);
		// 2.1.4 列的设置
		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("修改者");
		if (activityList != null && activityList.size() > 0) {
			Activity activity = null;
			for (int i = 0; i < activityList.size(); i++) {
				activity = activityList.get(i);
				//生成行
				row = sheet.createRow(i + 1);
				//创建列
				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());
			}
		}
		// 3 调用工具函数生成Excel文件。
		// 4 文件下载
		// 4.1 设置响应类型  excel文件是application/octet-stream二进制文件
		response.setContentType("application/octet-stream;charset=UTF-8");
		// 激活文件下载窗口 Content-Disposition不打开，attachment附件
		response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
		// 4.2 获取输出流
		ServletOutputStream out = response.getOutputStream();
		wb.write(out);
		// 3.2 关闭资源
		wb.close();
		out.flush(); // 输出流缓存中的内容强制输出,但并不会关闭输出流
	}

 

	/**
	 * 批量导出
	 */
	@RequestMapping("/workbench/activity/exportAllActivities.do")
	public void exportAllActivities(HttpServletResponse response) throws Exception {
		// 1 调用service方法，查询所有的市场活动
		List<Activity> activityList = activityService.queryAllActivities();
		// 2.文件下载
		HSSFUtils.createExcelByActivityList(activityList,Contants.FILE_NAME_ACTIVITY,response);
	}

 四、前端index.jsp

给批量的按钮添加单击事件，跳转到controller层

            // 下载:全选
            $("#exportActivityAllBtn").click(function () {
                window.location.href = "workbench/activity/exportAllActivities.do";
            });