客户需求：
用户在市场活动主页面,点击"批量导出"按钮,把所有市场活动生成一个excel文件,弹出文件下载的对话框;
用户选择要保存的目录,完成导出市场活动的功能.
*导出成功之后,页面不刷新
功能开发知识点：
1,导出市场活动：
1)给"批量导出"按钮添加单击事件，发送导出请求
2)查询所有的市场活动
3)创建一个excel文件，并且把市场活动写到excel文件中
4)把生成的excel文件输出到浏览器(文件下载)
技术准备：
1)使用java生成excel文件：iText,apache-poi
关于办公文档插件使用的基本思想：把办公文档的所有元素封装成普通的Java类，
程序员通过操作这些类达到操作办公文档目的。
文件---------HSSFWorkbook
页-----------HSSFSheet
行-----------HSSFRow
列-----------HSSFCell
样式---------HSSFCellStyle
使用apache-poi生成excel：
a)添加依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
b)使用封装类生成excel文件：
2)文件下载：
filedownloadtest.jsp
ActivityController
|->fileDownload()
*所有文件下载的请求只能发送同步请求。
2,导入市场活动：
1)把用户计算机上的excel文件上传到服务器(文件上传)
2)使用java解析excel文件，获取excel文件中的数据
3)把解析出来的数据添加数据库中
4)返回响应信息
技术准备：
1)文件上传：
fileuploadtest.jsp
ActivityController
|->fileUpload()
2)使用java解析excel文件：iText,apache-poi
关于办公文档插件使用的基本思想：把办公文档的所有元素封装成普通的Java类，
程序员通过操作这些类达到操作办公文档目的。
文件---------HSSFWorkbook
页-----------HSSFSheet
行-----------HSSFRow
列-----------HSSFCell
功能开发：
1.根据客户需求绘画出UML时序图
2.功能开发，mapper层
ActivityMapper接口
ActivityMapper.xml
3.service层
ActivityService接口
ActivityServiceImpl
4.Controller层
ActivityController
@RequestMapping(value = "/workbench/activity/exportAllActivitys.do")
public void exportAllActivitys(HttpServletResponse response) throws IOException {
//调用service层的方法，查询所有的市场活动
List<Activity> activityList = activityService.queryAllActivitys();
//创建excel文件，并且把activityList写入到excel文件中
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet = workbook.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("修改者");
if (activityList.size()>0 && activityList!=null) {
//遍历activityList，创建HSSFRow对象，生成所有的数据行
Activity activity = null;
for (int i = 0; i < activityList.size(); i++) {
activity = activityList.get(i);
//每遍历出一个activity，生成一行
row = sheet.createRow(i + 1);
//每一行创建11列，每一列的数据从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.getCreateTime());
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());
}
}
//根据web对象生成excel文件
OutputStream os =new FileOutputStream("D:\\SSM框架\\SSM版CRM项目资料\\serverDir\\activity.xls");
workbook.write(os);
//关闭资源
os.close();
workbook.close();
//把生成的excel文件下载到客户端
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:\\SSM框架\\SSM版CRM项目资料\\serverDir\\activity.xls");
byte[] buff=new byte[256];
int len=0;
while ((len=is.read(buff))!=-1){
out.write(buff,0,len);
}
is.close();
out.flush();
}由于
workbook.write(os)；
workbook.write(out);
这两个写入会从内存到磁盘再从磁盘到内存存取，同样的数据极大的消耗了反应时间，因此可以利用SHHFWorkBook建立的对象workbook自带的write方法，可以实现从内存到内存的存取，避免了从内存到磁盘之间传输信息照成的时间浪费。下面是调优后的方法
package com.it.crm.workbench.web.controller;
import com.it.crm.commons.contants.Contants;
import com.it.crm.commons.entity.ReturnObject;
import com.it.crm.commons.utils.DateUtils;
import com.it.crm.commons.utils.UUIDUtils;
import com.it.crm.settings.entity.User;
import com.it.crm.settings.service.UserService;
import com.it.crm.workbench.entity.Activity;
import com.it.crm.workbench.service.ActivityService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Controller
public class ActivityController {
@Autowired
private UserService userService;
@Autowired
private ActivityService activityService;
@RequestMapping(value = "/workbench/activity/index.do")
public String index(HttpServletRequest request) {
//调用service层方法，查询所有的用户
List<User> userList = userService.queryAllUsers();
//把数据保存到request中
request.setAttribute("userList", userList);
//请求转发到市场活动主页面
return "workbench/activity/index";
}
@RequestMapping(value = "/workbench/activity/saveCreateActivity.do")
@ResponseBody
public ReturnObject saveCreateActivity(Activity activity, HttpSession session) {
User user = (User) session.getAttribute(Contants.SESSION_USER);
//封装参数
activity.setId(UUIDUtils.getUUID());
activity.setCreateTime(DateUtils.formateDateTime(new Date()));
activity.setCreateBy(user.getId());
ReturnObject returnObject = new ReturnObject();
try {
//调用service层方法，保存创建的市场活动
int i = activityService.saveCreateActivity(activity);
if (i > 0) {
returnObject.setCode(Contants.RETURN_OBJECT_CODE_SUCCESS);
} else {
returnObject.setCode(Contants.RETURN_OBJECT_CODE_FAIL);
returnObject.setMessage("系统忙，请稍后重试.....");
}
} catch (Exception e) {
e.printStackTrace();
returnObject.setCode(Contants.RETURN_OBJECT_CODE_FAIL);
returnObject.setMessage("系统忙，请稍后重试.....");
}
return returnObject;
}
@RequestMapping(value = "/workbench/activity/queryActivityByConditionForPage.do")
@ResponseBody
public Object queryActivityByConditionForPage(String name, String owner, String startDate, String endDate,
int pageNo, int pageSize) {
//封装参数
Map<String, Object> map = new HashMap<>();
map.put("name", name);
map.put("owner", owner);
map.put("startDate", startDate);
map.put("endDate", endDate);
map.put("pageNo", (pageNo - 1) * pageSize);
map.put("pageSize", pageSize);
//调用service层方法，查询数据
List<Activity> activityList = activityService.queryActivityByConditionForPage(map);
int totalRows = activityService.queryCountOfActivityByCondition(map);
//根据查询结果生成响应信息
Map<String, Object> retMap = new HashMap<>();
retMap.put("activityList", activityList);
retMap.put("totalRows", totalRows);
return retMap;
}
@RequestMapping(value = "/workbench/activity/removeActivityByIds.do")
@ResponseBody
public Object removeActivityByIds(String[] id) {
ReturnObject returnObject = new ReturnObject();
try {
//调用service层的方法，删除市场活动
int ret = activityService.removeActivityByIds(id);
if (ret > 0) {
returnObject.setCode(Contants.RETURN_OBJECT_CODE_SUCCESS);
} else {
returnObject.setCode(Contants.RETURN_OBJECT_CODE_FAIL);
returnObject.setMessage("系统忙，请稍后重试...");
}
} catch (Exception e) {
e.printStackTrace();
returnObject.setCode(Contants.RETURN_OBJECT_CODE_FAIL);
returnObject.setMessage("系统忙，请稍后重试...");
}
return returnObject;
}
@RequestMapping(value = "/workbench/activity/queryActivityById.do")
@ResponseBody
public Object queryActivityById(String id) {
//调用service层方法，查询市场活动
Activity activity = activityService.queryActivityById(id);
//根据查询结果，返回响应信息
return activity;
}
@RequestMapping(value = "/workbench/activity/saveEditActivity.do")
@ResponseBody
public Object saveEditActivity(Activity activity, HttpSession session) {
User user = (User) session.getAttribute(Contants.SESSION_USER);
//封装参数
activity.setEditTime(DateUtils.formateDateTime(new Date()));
activity.setEditBy(user.getId());
ReturnObject returnObject=new ReturnObject();
try {
//调用service层方法，保存修改市场活动
int ret = activityService.saveEditActivity(activity);
if (ret>0){
returnObject.setCode(Contants.RETURN_OBJECT_CODE_SUCCESS);
}else {
returnObject.setCode(Contants.RETURN_OBJECT_CODE_FAIL);
returnObject.setMessage("系统忙，请稍后重试！");
}
} catch (Exception e) {
e.printStackTrace();
returnObject.setCode(Contants.RETURN_OBJECT_CODE_FAIL);
returnObject.setMessage("系统忙，请稍后重试！");
}
return returnObject;
}
@RequestMapping(value = "/workbench/activity/exportAllActivitys.do")
public void exportAllActivitys(HttpServletResponse response) throws IOException {
//调用service层的方法，查询所有的市场活动
List<Activity> activityList = activityService.queryAllActivitys();
//创建excel文件，并且把activityList写入到excel文件中
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet = workbook.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("修改者");
if (activityList.size()>0 && activityList!=null) {
//遍历activityList，创建HSSFRow对象，生成所有的数据行
Activity activity = null;
for (int i = 0; i < activityList.size(); i++) {
activity = activityList.get(i);
//每遍历出一个activity，生成一行
row = sheet.createRow(i + 1);
//每一行创建11列，每一列的数据从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.getCreateTime());
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());
}
}
//根据web对象生成excel文件
/* OutputStream os =new FileOutputStream("D:\\SSM框架\\SSM版CRM项目资料\\serverDir\\activity.xls");
workbook.write(os);*/
//关闭资源
/*os.close();
workbook.close();*/
//把生成的excel文件下载到客户端
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:\\SSM框架\\SSM版CRM项目资料\\serverDir\\activity.xls");
byte[] buff=new byte[256];
int len=0;
while ((len=is.read(buff))!=-1){
out.write(buff,0,len);
}
is.close();*/
workbook.write(out);
workbook.close();
out.flush();
}
}
5.activity的index.jsp页面
功能测试：
进入市场活动列表页面，点击下载列表数据（批量导出），会自动在自定义的serverDir生成excel文件，然后再下载到客户端，该excel中的文件完全来源于数据库。
现在该文件夹中没有文件
点击批量导出
下载完成后，自动打开excel文件，这些数据完全来自数据库
serverDir文件中也有了excel文件
686
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
