Springboot整合EasyExcel 导出excel

1：在pom.xml 引入jar包：

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.vsersion}</version>
</dependency>

2：实体bean中设置excel表头信息

@ExcelProperty(value = "性别",index = 2,converter = SexConverter.class)

@ExcelProperty 表示excel表头，
value ：表示表头描述；
index：表示第几列；
converter：表示转换对应关系

@ExcelIgnore：excel中忽略该字段信息

 

@ContentRowHeight(int):

设置 row 高度，不包含表头 标记在 类上 @ContentRowHeight(15) //设置行高

@HeadRowHeight(int)：

设置 表头 高度（与 @ContentRowHeight 相反） 标记在 类上 @HeadRowHeight(20) //设置表头高度

@ColumnWidth(int)：

设置列宽 标记在属性上 @ColumnWidth(20) //设置列宽

/**
 * @Date 2021/4/22 16:21
 * 导出男女转换对应关系
 */
public class SexConverter implements Converter<Integer> {
    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return "男".equals(cellData.getStringValue()) ? 1 : 2;
    }

    @Override
    public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(value.equals(1) ? "男" : "女");
    }

}

controller对应：

@GetMapping("dlHospitalizationCard")
public void download(HttpServletResponse response,@RequestBody Map<String, Object> mapList) throws IOException {

    HospitalizationCard hospitalizationCard = new HospitalizationCard();
    hospitalizationCard.setIssuingDepartment(issuingDepartment);
    hospitalizationCard.setIssuingDoctor(issuingDoctor);
    hospitalizationCard.setName(name);

    List<HospitalizationCard> hospitalizationCardList = hospitalizationCardService.getHospitalizationCard(beginTime,endTime,hospitalizationCard);
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("文件名称", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), HospitalizationCard.class).sheet("Sheet1").registerWriteHandler(new CustomizeColumnWidth()).doWrite(hospitalizationCardList);
}

registerWriteHandler(new CustomizeColumnWidth()) ：设置表格宽，高

public class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
            int columnWidth = 10;
            int cellIndex = cell.getColumnIndex();
            switch (cellIndex) {
                case 0:
                case 1:
                case 5:
                case 6:
                case 7:
                case 8:
                    columnWidth = 12;
                    break;
                case 9:
                case 10:
                case 11:
                case 12:
                case 13:
                    columnWidth = 50;
                    break;
                case 2:
                case 3:
                    columnWidth = 8;
                    break;
                case 4:
                    columnWidth = 20;
                    break;
                default:
                    break;
            }

            if (columnWidth > 255) {
                columnWidth = 255;
            }
            writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);

    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        // 设置行高测试
        int rowIndex = row.getRowNum();
        short height = 600;
        row.setHeight(height);
    }
}

参考资料：

https://github.com/alibaba/easyexcel/blob/master/docs/API.md   官方api

https://github.com/alibaba/easyexcel

http://www.yihaomen.com/article/1851.html