1.下载Excel的步骤
1.配置jar包依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
2.在controller的方法上配置请求映射的参数;
@RequestMapping(value="/downloadExcel.do", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
@ResponseBody
3.在方法中手动设置下载的响应头:
response.setHeader("Content-Disposition", "attactionment;filename=\"ok.xlsx\"");
4.将Excel文件转换为一个byte[]发送给客户端页面;
例:
@RequestMapping(value="/downloadExcel.do", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
@ResponseBody
public byte[] dowmloadExcel(HttpServletResponse response) throws IOException {
//手工设置下载头:Content-Disposition
response.setHeader("Content-Disposition", "attactionment;filename=\"ok.xlsx\"");
byte[] bytes = createExcel();
return bytes;
}
5.生成一个Excel的方法:
6.从数据库中读取数据生成一个Excel表格给用户
1.配置jar包依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
2.在controller的方法上配置请求映射的参数;
@RequestMapping(value="/downloadExcel.do", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
@ResponseBody
3.在方法中手动设置下载的响应头:
response.setHeader("Content-Disposition", "attactionment;filename=\"ok.xlsx\"");
4.将Excel文件转换为一个byte[]发送给客户端页面;
例:
@RequestMapping(value="/downloadExcel.do", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
@ResponseBody
public byte[] dowmloadExcel(HttpServletResponse response) throws IOException {
//手工设置下载头:Content-Disposition
response.setHeader("Content-Disposition", "attactionment;filename=\"ok.xlsx\"");
byte[] bytes = createExcel();
return bytes;
}
5.生成一个Excel的方法:
public byte[] createExcel() throws IOException {
//创建一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//在工作薄中添加工作表
XSSFSheet sheet1 = workbook.createSheet("花名册");
//在工作表中添加两行
XSSFRow head = sheet1.createRow(0);
XSSFRow row = sheet1.createRow(1);
//第一行作为表头
XSSFCell c0 = head.createCell(0);
//表头第一个格子添加“编号”
c0.setCellValue("编号");
head.createCell(1).setCellValue("姓名");
head.createCell(2).setCellValue("年龄");
//第一行的内容
row.createCell(0).setCellValue(1);
row.createCell(1).setCellValue("小明");
row.createCell(2).setCellValue(12);
//将Excel保存为bytes
ByteArrayOutputStream out = new ByteArrayOutputStream();
//表放到流里面
workbook.write(out);
//将流转换为byte数组
byte[] bytes = out.toByteArray();
return bytes;
}
6.从数据库中读取数据生成一个Excel表格给用户
@RequestMapping(value="/userExcel.do", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
@ResponseBody
public byte[] dowmloadUserExcel(HttpServletResponse response) throws IOException {
//手工设置下载头:Content-Disposition
response.setHeader("Content-Disposition", "attactionment;filename=\"ok.xlsx\"");
byte[] bytes = userContentExcel();
return bytes;
}
/**
* 读取数据库中的用户信息生成一个Excel返回给客户端
* @throws IOException
*/
public byte[] userContentExcel() throws IOException {
//获取用户的信息
List<User> users = userService.getAllUser();
System.out.println("获取到的所有用户信息为:"+users);
//创建一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//在工作薄中添加工作表
XSSFSheet sheet1 = workbook.createSheet("用户信息");
//在工作表中添加一行作为表头
XSSFRow head = sheet1.createRow(0);
//设置表头
head.createCell(0).setCellValue("id");
head.createCell(1).setCellValue("username");
head.createCell(2).setCellValue("password");
head.createCell(3).setCellValue("phone");
head.createCell(4).setCellValue("email");
//将用户信息读取出来循环设置每一行
for (int i=0;i<users.size();i++) {
//添加一行
XSSFRow row = sheet1.createRow(i+1);
//设置这一行的内容
User user = users.get(i);
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getUsername());
row.createCell(2).setCellValue(user.getPassword());
row.createCell(3).setCellValue(user.getPhone());
row.createCell(4).setCellValue(user.getEmail());
}
//将Excel保存为bytes
ByteArrayOutputStream out = new ByteArrayOutputStream();
//表放到流里面
workbook.write(out);
//将流转换为byte数组
byte[] bytes = out.toByteArray();
return bytes;
}