功能需求：批量导出市场活动
用户在市场活动主页面,点击"批量导出"按钮,把所有市场活动生成一个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_byfrom tbl_activity ajoin tbl_user u1 on a.owner = u1.idjoin tbl_user u2 on a.create_by = u2.idleft join tbl_user u3 on a.edit_by = u3.idorder by a.create_time desc</select>二、ActivityService
/*** 批量导出*/List<Activity> queryAllActivities(); @Overridepublic 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";});
