EasyExcel导出自定义列
1.首先定义导出的列
entity:
@ContentRowHeight(10) //内容行高
@HeadRowHeight(20) //头部行高
@ColumnWidth(25) //列宽
public class OrderExcelBO extends BaseRowModel {
/**
*index:显示在第几列的下标值
*value值是数组,写两个显示两行标题,如果两个字段第一个标题一样则显示两个字段总的标题
*/
@ExcelProperty(value = {"订单号","订单ID"}, index = 0)
private String id;
@ColumnWidth(50) //也可单独设置某列宽
@ExcelProperty(value = {"订单号","订单描述"}, index = 2)
private String description;
@ExcelProperty(value = {"产品ID"}, index = 2)
private Integer productId;
//对时间格式进行转换
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty(value = {"时间"}, index = 3)
private String time;
}
2.创建接口,可以前端传动态导出哪些列,以及传入导出excel名字
controller:
public static void export(String fileNameL,List<String> listName) throws IOException {
//文件名字带后缀,对文件名进行处理
String name=buildResponse(fineName.split("\\."));
//查询出导出的数据
List<User> user=service.list();
//includeColumnFiledNames()导出指定列的集合,不加该方法则导出User类的列
EasyExcel.write(response.getOutputStream(),User.class)includeColumnFiledNames(listName).sheet(name).doWrite(user);
}
//对文件名进行处理
private String buildResponse(String... names) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String preName = "数据模板";
String end = "xlsx";
if (names != null && names.length > 0) {
if (!StringUtils.isEmpty(names[0])) {
preName = names[0];
}
if (names.length > 1 && !StringUtils.isEmpty(names[1])) {
end = names[1];
}
}
String fileName = URLEncoder.encode(preName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + "." + end);
return preName;
}
@Autowired
protected HttpServletResponse response;
3.在导出时需要将User转换为其他导出模板对象时需要进行对象转换
List<UserExc> list=user.list().stream().map(converter::modelToEx).collect(Collectors.toList());
//doWrite()方法输出转换后的list对象
EasyExcel.write(response.getOutputStream(),User.class)includeColumnFiledNames(listName).sheet(name).doWrite(list);
下载导入模板:
ManufacturerManagementVo为导出的模板
GetMapping("/download/template/{fileName}")
public void download(@PathVariable String fileName) throws Exception {
String name = buildResponse(fileName.split("\\."));
EasyExcel.write(response.getOutputStream(), ManufacturerManagementVo.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(name).doWrite(Collections.emptyList());
}
4.定义转换器接口
import org.mapstruct.Mapper
@Mapper(componentModel="spring")
public interface UserConverter{
//user对象转userEx
UserEx modelToEx(User user);
}
5.依赖包-------------
<properties>
<java.version>1.8</java.version>
<mapstruct.version>1.3.1.Final</mapstruct.version>
</properties>
<dependency>
<groupId>org.mapstruct</groupId>
<artifactId>mapstruct</artifactId>
<version>${mapstruct.version}</version>
<scope>provided</scope>
</dependency>