EasyExcel导出、按列设置样式、根据表头字数设置列宽、Converter转换

一、Excel导出实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.zjn.eng.data.controller.excel.converter.ProcessConverter;
import lombok.Data;

import java.util.Date;

@Data
//@ColumnWidth(value = 20)
//行高全部设为40
//@HeadRowHeight(value = -1)
//标题全部居中
//@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, fillBackgroundColor = 8, fillForegroundColor = 21)
//@ContentFontStyle(color =)
public class DailyData {
    /**
     * 问题描述
     */
    @ExcelProperty("问题描述")
    private String problemDescription;

    /**
     * 问题提交时间
     */
    @ExcelProperty("问题提交时间")
    @DateTimeFormat("yyyy/MM/dd HH:mm") //出参
    private Date submissionTime;

    /**
     * 问题提交人
     */
    @ExcelProperty("问题提交人")
    private String submitter;

    /**
     * 0 处理中 1 已处理
     */
    @ExcelProperty(value = "是否处理", converter = 	ProcessConverter.class)
    private Integer processingStatus;

    /**
     * 事件处理人
     */
    @ExcelProperty("事件处理人")
    private String transactorName;
}

二、自定义样式handler

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractVerticalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.Arrays;

public class StyleHandler extends AbstractVerticalCellStyleStrategy {
    private static String fontName = "等线";
    private static Integer[] columnIndex = {1, 2, 3, 4, 7, 8, 9};

    @Override
    protected WriteCellStyle headCellStyle(Head head) {
// 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
//        headWriteFont.setFontName("Frozen");
        headWriteFont.setFontName(fontName);
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);
        //自动换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式
        if (Arrays.asList(columnIndex).contains(head.getColumnIndex())) {
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        } else {
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        }
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteFont.setBold(true);
        return headWriteCellStyle;
    }

    @Override
    protected WriteCellStyle contentCellStyle(Head head) {
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        if (Arrays.asList(columnIndex).contains(head.getColumnIndex())) {
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        } else {
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        }
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
//        contentWriteFont.setFontName("Calibri");
        contentWriteFont.setFontName(fontName);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return contentWriteCellStyle;
    }
}

三、servicesImple类中实现方法

 public void downloadDaily(HttpServletResponse response, DailyVo dailyVo) {
        //获取当前时间
        String currentTimeStr = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        //文件名
        String fileName = currentTimeStr + "-日报";
        dailyVo.setCurrent(1);
        dailyVo.setSize(1000);
        //获取导出列表数据
        Map data = (Map) this.dailyList(dailyVo).getData();
        List<Map> records = (List<Map>) data.get("records");
        List<DailyData> list = JSON.parseObject(JSON.toJSONString(records), new TypeReference<List<DailyData>>() {
        });

        try {
            OutputStream outputStream = response.getOutputStream();
            //添加响应头信息
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String encodeFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            response.setHeader("Content-Disposition", "attachment;filename=" + encodeFileName + ".xlsx");

//            StyleHandler styleHandler = new StyleHandler();
            //实例化 ExcelWriter
            ExcelWriter writer = EasyExcel
                    .write(outputStream)
                    .registerWriteHandler(new StyleHandler())
//                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new LongestCellWidthHandler())
                    .build();
            WriteSheet sheet = EasyExcel.writerSheet(0, currentTimeStr + "-日报").head(DailyData.class).build();
            writer.write(list, sheet);
            writer.finish();
            outputStream.flush();
        } catch (IOException e) {
            log.error("导出用户列表方法失败", e);
        }

    }

四、表头设置列宽handler

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;

import java.util.List;

/**
 * 根据表头设置列宽
 */
public class LongestCellWidthHandler extends AbstractColumnWidthStyleStrategy {
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (Boolean.TRUE.equals(isHead)) {
            int columnWidth = cell.getStringCellValue().length();
            columnWidth = Math.max(columnWidth * 5, 20);
            if (columnWidth > 255) {
                columnWidth = 255;
            }
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }
}

五、Converter转换

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

public class ProcessConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

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

    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if ("处理中".equals(cellData.getStringValue())) {
            return 0;
        } else if ("已处理".equals(cellData.getStringValue())) {
            return 1;
        } else {
            return 2;
        }
    }

    @Override
    public WriteCellData<String> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        boolean b = context.getValue() == 0;
        if (0 == context.getValue()) {
            return new WriteCellData("处理中");
        } else if (1 == context.getValue()) {
            return new WriteCellData("已处理");
        } else {
            return new WriteCellData("未处理");
        }
    }
}