文章目录
导入相关坐标
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
Controller代码(设置文件名,输出流,文件返回格式)
/**
* 数据导出Excel
* @param response
* @throws IOException
*/
@GetMapping("/excelExportUserInfo")
public void batchExportUserInfo(HttpServletResponse response) throws IOException {
String fileName = "用户信息表";
OutputStream outputStream = null;
Workbook wb = userService.batchExportUserInfo();
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes("gb2312"),"iso-8859-1") + ".xlsx");
outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
}
Service代码(创建sheet表,导入数据)
/**
* 数据导出
* @return
*/
@Override
public Workbook batchExportUserInfo() {
List<User> user = userDao.selectAll();
if(CollectionUtils.isEmpty(user)){
throw new RuntimeException("用户信息表空白");
}
//创建workbook
Workbook workbook = new XSSFWorkbook();
//创建sheet
Sheet sheet = workbook.createSheet("用户信息表");
//在sheet中添加表头第0行标题
Row row1 = sheet.createRow(0);
//第一列标题
Cell cell11 = row1.createCell(0);
cell11.setCellValue("用户名");
//第二列标题
Cell cell12 = row1.createCell(1);
cell12.setCellValue("姓名");
//第三列标题
Cell cell13 = row1.createCell(2);
cell13.setCellValue("手机号");
//第四列标题
Cell cell14 = row1.createCell(3);
cell14.setCellValue("身份证");
//需要封装的数据的个数
int length = user.size();
//开始封装数据
for (int i = 0; i < length; i++) {
Row row = sheet.createRow(i+1);
row.createCell(0).setCellValue(user.get(i).getLoginName()==null?"":user.get(i).getLoginName());
row.createCell(1).setCellValue(user.get(i).getUserName()==null?"":user.get(i).getUserName());
row.createCell(2).setCellValue(user.get(i).getPhoneNo()==null?"":user.get(i).getPhoneNo());
row.createCell(3).setCellValue(user.get(i).getIdNo()==null?"":user.get(i).getIdNo());
}
return workbook;
}
Dao代码(查询所有用户信息)
<!--查询所有用户信息-->
<select id="selectAll" resultType="com.fm.pojo.User">
SELECT * FROM tb_user ORDER BY id ASC
</select>
前端代码(使用链接发送请求,返回excel表格)
<a id="user_import" href="/financeManager/user/excelExportUserInfo">导出用户信息</a>
最终结果