easyExcel后端相关内容

1.添加依赖

<!--easyExcel-->
<dependency>
　　<groupId>com.alibaba</groupId>
　　<artifactId>easyexcel</artifactId>
　　<version>3.0.5</version>
</dependency>

 

2.编写ExcelWidthStyleStrategy工具类继承AbstractColumnWidthStyleStrategy完成对导出excel表格内容宽度设置

 

public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {



// 统计setColumnWidth被调用多少次
private static int count = 0;
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 简单设置
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 5000);
System.out.println(count++);




    }
}

 

 

 3.自定义工具类EasyExcelUtil 设置相关参数

 

public class EasyExcelUtil {



    /**
     * 下载文件
     * @param response
     * @param exportFileName 文件名
     * @param head 实体类
     * @param dataList  数据
     * @throws IOException
     */
    public static void exportDefaultExcel(HttpServletResponse response, String exportFileName, Class head, List<?> dataList) throws IOException {
            // 这里注意 使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(exportFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), head)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("Sheet1").doWrite(dataList);
    }
}

 

 

4.实体类中添加@ExcelProperty注解并设置导出excel表头和表头顺序

（多表连接直接自定义----实体Excel）

 index可以控制顺序  必须要有0否则出现不对齐

 

 

 

@Data
public class UserExcel {
    @ExcelProperty(value = "用户编号",index = 0)
    private int userId;



    @ExcelProperty(value = "城市名",index = 1)
    private String cityName;



    @ExcelProperty(value = "车辆名",index = 2)
    private String carName;



    @ExcelProperty(value = "日期",index = 3)
    @DateTimeFormat("yyyy年MM月dd日")
    private Date date;



    @ExcelProperty(value = "联系人",index = 5)
    private String linkman;



    @ExcelProperty(value = "手机号",index = 4)
    private String phone;



    @ExcelProperty(value = "状态",index = 6)
    private String state;
}

 

 

5.编写相关dao层、mapper层（实体类中不引用------mapper直接连表）

 

    <!-- 导出excel-->
    <select id="exportUser" resultType="com.example.movebooking.entity.UserExcel">
        select user_id,city.city_Name,car.car_Name,date,linkman,phone,state
        from user,car,city
        where user.city_id=city.city_id and user.car_id=car.car_id
    </select>

 

 

 

6.Controller   注意传哪些参到自定义工具类调用的方法上 EasyExcelUtil.exportDefaultExcel(HttpServletResponse response, String exportFileName, Class head, List<?> dataList)

 

@RequestMapping(value = "/exportUser",method = RequestMethod.GET)
public void exportFlight(HttpServletResponse response,String fileName) throws IOException {
//此时的List返回的列表就是要导出的数据  直接返回select语句即可无需对应数据库表
    List<UserExcel>users=userService.exportUser();
    EasyExcelUtil.exportDefaultExcel(response,fileName,UserExcel.class,users);
}

 

 

 

 

 

 
 
posted @ 2022-05-24 08:56  快了星球  阅读(167)  评论(0)    收藏  举报