前言
SpringBoot项目实现Excel文件导出功能,可以使用alibaba开源项目EasyExcel实现。默认导出的Excel表头为宋体14加粗,表内容为宋体11。
一、引入EasyExcel依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency>
二、代码实现
1.Controller层
/**
* @param user 查询条件
* @param column 前端选择的导出列,逗号分隔,如果前端不指定导出列,该参数省略
*/
@GetMapping("/exportExcel")
@ApiOperation("查询记录导出")
public void exportExcel(HttpServletResponse response, User user, String column) throws IOException {userService.exportExcel(response, user,column);
}
2.Service层
public interface userService {/*** 导出excel*/void exportExcel(HttpServletResponse response, User user, String column) throws IOException;
}
3.ServiceImpl层
@Override
public void exportExcel(HttpServletResponse response, User user, String column) throws IOException{//查询导出数据List<User> userList = userMapper.selectList(user);//前端传入的需要导出的列,前端不指定导出列时,该步省略Set<String> columns = new HashSet<String>(Arrays.asList(column.split(",")));//设置ContentTyperesponse.setContentType("application/vnd.ms-excel");//设置字符集response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("用户列表", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");//如果前端不指定导出列,.includeColumnFiledNames(columns)可省略EasyExcel.write(response.getOutputStream(),User.class).includeColumnFiledNames(columns).sheet(fileName).doWrite(records);
}
4.Entity层
//@ExcelIgnoreUnannotated 可忽略类中的属性变量不加@ExcelProperty注解的不参与读写
//@ContentRowHeight(20)//表体行高,默认13.5,可不设置
//@HeadRowHeight(20)//表头行高,默认56.25,可不设置
//@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER)//表头居中,默认表头水平垂直居中,如果类或字段用了@ContentStyle注解,类上要加该注解,不然表头样式就没了
//@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)//内容居中
@ColumnWidth(11) 列宽,值为wps打开excel查看的列宽,在类上用作用于所有字段,也可在字段上单独设置。
@Data
public class User {@ExcelIgnore //忽略该字段private int id;@ExcelProperty("姓名")private String name;@ExcelProperty("编号")private String code;@ExcelProperty("年龄")private String age;@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER) //默认水平左对齐,垂直居中对齐@ExcelProperty("性别")private String sex;@ColumnWidth(15)@ExcelProperty("地址")private String address;@ExcelProperty("出生时间")@DateTimeFormat("yyyy-MM-dd HH:mm:ss")private Date birthday;
}
5.指定模版导出
@Override
public void exportExcel(HttpServletResponse response, User user, String column) throws IOException{//查询导出数据List<User> userList = userMapper.selectList(user);//前端传入的需要导出的列,前端不指定导出列时,该步省略Set<String> columns = new HashSet<String>(Arrays.asList(column.split(",")));//设置ContentTyperesponse.setContentType("application/vnd.ms-excel");//设置字符集response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("用户列表", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");//指定导出模版InputStream templateFile = new ClassPathResource("templates/excel/user.xlsx").getInputStream();// 这里 会填充到第一个sheet, 然后文件流会自动关闭EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(data());
}
填充模版
三、导出结果展示
参考资料:EasyExcel官网:https://easyexcel.opensource.alibaba.com/