本文介绍了在Spring Boot项目中如何实现数据导出为Excel表格的详细步骤，包括添加相关依赖、控制器、服务层和前端代码的实现。
今天接到一个需求，让把当前页面或者当前的查询条件下查询到的数据，导出到excel表格，下面说一下我是怎么做的。（我的项目是spring-boot）
1.先在pom.xml中添加两个依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
2.cintroller层
/**
* 根据条件导出excel
*
* @param video_title
* @param start_date
* @param end_date
* @param state
* @param receive
* @param video_priority
* @param orderByClause
* @param session
* @param response
*/
@GetMapping("excel")
public void getExcel(String video_title, String start_date, String end_date, Integer state, Boolean receive, String video_priority, String orderByClause, HttpSession session, HttpServletResponse response) {
SessionUser sessionUser = (SessionUser) session.getAttribute(SessionUser.getSessionKey());
Integer spid = sessionUser.getSpid();
String userId = sessionUser.getUserId();
if (spid == 0) spid = null;
List<ShyVideos> shyVideosList = videosService.findByCondition(video_title, start_date, end_date, state, receive, video_priority, orderByClause, spid, userId);
String fileName = "点播数据";
String auditor = "";//审核员名字
String state1 = "";//审核状态 1已审核 2审核拒绝 0未审核
XSSFWorkbook wb = new XSSFWorkbook();
ServletOutputStream out = null;
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
XSSFSheet sheetlist = wb.createSheet(fileName);
XSSFRow row = sheetlist.createRow(0);//行
XSSFCell 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("视频id");
cell = row.createCell(11);//列
cell.setCellValue("创建时间");
cell = row.createCell(12);//列
cell.setCellValue("视频上传时间");
cell = row.createCell(13);//列
cell.setCellValue("视频时长(秒)");
cell = row.createCell(14);//列
cell.setCellValue("spid");
for (int i = 0; i < shyVideosList.size(); i++) {
row = sheetlist.createRow((short) (i + 1));//行
cell = row.createCell(0);//列
cell.setCellValue(shyVideosList.get(i).getId());
cell = row.createCell(1);//列
cell.setCellValue(shyVideosList.get(i).getVideo_title());
cell = row.createCell(2);//列
cell.setCellValue(shyVideosList.get(i).getVideo_url());
cell = row.createCell(3);//列
cell.setCellValue(shyVideosList.get(i).getVideo_poster());
cell = row.createCell(4);//列
cell.setCellValue(shyVideosList.get(i).getVideo_tags());
cell = row.createCell(5);//列
/* 审核员需要做处理*/
if (shyVideosList.get(i).getState() != CommonConst.AUDIT_INTT) {
auditor = "白名单";
String auditor_id = shyVideosList.get(i).getAuditor_id();
if (CommonUtils.isNotEmpty(auditor_id)) {
ShyUsers user = new ShyUsers();
user.setUser_id(auditor_id);
user = usersService.select(user);
if (CommonUtils.isNotNull(user)) {
String userName = CommonUtils.isEmpty(user.getUsername()) ? "" : user.getUsername();
String email = CommonUtils.isEmpty(user.getEmail()) ? "" : user.getEmail();
auditor = userName + email;
}
}
}
cell.setCellValue(auditor);
cell = row.createCell(6);//列
cell.setCellValue(shyVideosList.get(i).getMsg());
cell = row.createCell(7);//列
/*状态需要处理成用户可读*/
if (shyVideosList.get(i).getState() == 0) {
state1 = "未审核";
} else if (shyVideosList.get(i).getState() == 1) {
state1 = "已审核";
} else {
state1 = "审核拒绝";
}
cell.setCellValue(state1);
cell = row.createCell(8);//列
cell.setCellValue(shyVideosList.get(i).getVideo_keyword());
cell = row.createCell(9);//列
cell.setCellValue(shyVideosList.get(i).getVideo_desc());
cell = row.createCell(10);//列
cell.setCellValue(shyVideosList.get(i).getVideo_id());
cell = row.createCell(11);//列
cell.setCellValue(CommonUtils.formatDate(shyVideosList.get(i).getCreated_at(), "M/d/yyyy HH:mm:SS"));
cell = row.createCell(12);//列
cell.setCellValue(CommonUtils.formatDate(shyVideosList.get(i).getVideo_upload_time(), "M/d/yyyy HH:mm:SS"));
cell = row.createCell(13);//列
cell.setCellValue(shyVideosList.get(i).getDuration());
cell = row.createCell(14);//列
cell.setCellValue(shyVideosList.get(i).getSpid());
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
out = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final Exception e) {
e.printStackTrace();
try {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
} catch (Exception e1) {
e1.printStackTrace();
}
} finally {
try {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
3.service层
/**
* 根据条件导出数据excel
*
* @param video_title
* @param start_date
* @param end_date
* @param state
* @param receive
* @param video_priority
* @param orderByClause
* @param spid
* @param userId
* @return
*/
public List<ShyVideos> findByCondition(String video_title, String start_date, String end_date, Integer state, Boolean receive, String video_priority, String orderByClause, Integer spid, String userId) {
Example example = new Example(ShyVideos.class);
Example.Criteria criteria = example.createCriteria();
Example.Criteria criteria2 = example.createCriteria();
if (CommonUtils.isNotEmpty(orderByClause)) {
example.setOrderByClause(orderByClause);
}
if (CommonUtils.isNotEmpty(start_date) && CommonUtils.isNotEmpty(end_date)) {
Timestamp startdate = null;
Timestamp enddate = null;
try {
startdate = CommonUtils.parseDate(start_date, "yyyy-MM-dd HH:mm:ss");
enddate = CommonUtils.parseDate(end_date, "yyyy-MM-dd HH:mm:ss");
} catch (ParseException e) {
e.printStackTrace();
}
criteria.andGreaterThanOrEqualTo("video_upload_time", startdate);
criteria.andLessThanOrEqualTo("video_upload_time", enddate);
}
if (CommonUtils.isNotEmpty(video_title)) {
criteria.andLike("video_title", "%" + video_title + "%");
}
if (CommonUtils.isNotNull(state) && state == 0) {
criteria.andEqualTo("state", state);
} else if (CommonUtils.isNotNull(state) && state == 1) {
criteria.andEqualTo("state", state);
criteria2.andEqualTo("state", 2);
example.or(criteria2);
}
if (CommonUtils.isNotEmpty(video_priority)) {
criteria.andEqualTo("video_priority", video_priority);
}
if (CommonUtils.isNotNull(spid)) {
criteria.andEqualTo("spid", spid);
}
if (CommonUtils.isNotNull(receive)) {
criteria.andEqualTo("receive", receive);
if (receive == true) {
criteria.andEqualTo("receive_user_id", userId);
}
}
//默认按上传时间倒序排序
//example.setOrderByClause("video_upload_time desc");
List<ShyVideos> shyVideosList = shyVideosMapper.selectByExample(example);
return shyVideosList;
}
4.前台代码
<button type="button" th:onclick="'javascript:daochu()'" value="" id="excel">
导出excel
</button>
//导出excel
function daochu() {
var param = location.search;
window.location.href = "/videos/excel" + param;
}
ojbk，就这样就完事了。
扫一扫
489
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
