EasyExcel实现导出功能

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>