package com.api.test.controller;
import com.api.test.dao.UserRepository;
import com.api.test.entity.UserDO;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Autowired
private UserRepository userRepository;
@RequestMapping("/export")
public void exportExcel(HttpServletResponse response, HttpServletRequest request)
throws Exception {
//接收参数
String accounttime = request.getParameter("accounttime");
//查询数据,实际可通过传过来的参数当条件去数据库查询,在此我就用空集合(数据)来替代
List<UserDO> list = userRepository.findAll();
//创建poi导出数据对象
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
//创建sheet页
SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet页名字");
//创建表头
SXSSFRow headRow = sheet.createRow(0);
//设置表头信息
headRow.createCell(0).setCellValue("序号");
headRow.createCell(1).setCellValue("姓名");
headRow.createCell(2).setCellValue("年龄");
// 遍历上面数据库查到的数据
for (UserDO pm : list) {
//序号
int x = 1;
//填充数据
SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
//序号
dataRow.createCell(0).setCellValue(x);
//看你实体类在进行填充
dataRow.createCell(1).setCellValue(pm.getUserName());
dataRow.createCell(2).setCellValue(pm.getUserId());
x++;
}
// 下载导出
String filename = "记录";
// 设置头信息
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
//一定要设置成xlsx格式
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(filename + ".xlsx", "UTF-8"));
//创建一个输出流
ServletOutputStream outputStream = response.getOutputStream();
//写入数据
sxssfWorkbook.write(outputStream);
// 关闭
outputStream.close();
sxssfWorkbook.close();
}
}
pom 依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>