EasyExcel(动态表头导出)和正常导出动态表头样式设置

maven依赖:

 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>1.1.2-beta5</version>
</dependency>

导出工具类:

public class EasyExcelUtils<T> {
    // 默认行高为20
    static final short height = 400;

    //设置头信息
    public static void constResponse(HttpServletResponse response, String fileName) throws Exception {
//        response.setContentType("application/x-msdownload");
        response.setCharacterEncoding("utf-8");
        String name = URLEncoder.encode(fileName, "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");
    }

    public static void constResponse(HttpServletResponse response, String fileName, String errorMsg) throws Exception {
        response.setCharacterEncoding("utf-8");
        String name = URLEncoder.encode(fileName, "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");
        response.setHeader("Eaton-ResponseType", "blob");
        errorMsg = URLEncoder.encode(errorMsg, "utf-8");
        response.setHeader("Content-ErrorMsg", errorMsg);
    }

    public static <T> List<T> copyObject(List<?> list, Class<T> clazz) {
        String oldOb = JSON.toJSONString(list);
        return JSON.parseArray(oldOb, clazz);
    }

    /**
     * 合并首行和第二行 firstCol至lastCol
     * @param firstCol
     * @param lastCol
     * @return
     */
    public static List<CellRangeAddress> getCellRangeAddresss(int firstCol, int lastCol) {
        List<CellRangeAddress> list = new ArrayList<>();
        //合并第1行
        CellRangeAddress title1 = new CellRangeAddress(0, 0, firstCol, lastCol);
        //合并第2行
        CellRangeAddress title2 = new CellRangeAddress(1, 1, firstCol, lastCol);

        list.add(title1);
        list.add(title2);
        return list;
    }

    /**
     * 填写须知 填写须知的微软雅黑，字体大小10号，颜色黑色（000000）
     * @param cell
     * @param sheet
     * @param firstRowHeight
     */
    public static void setRow1(Cell cell, Sheet sheet, short firstRowHeight,CellStyle cellStyle) {
        Workbook workbook = sheet.getWorkbook();
        //CellStyle cellStyle = workbook.createCellStyle();
        Font font = getTitleCommonStyle(workbook);
        if (cell.getRowIndex() == 0) {
            font.setFontHeightInPoints((short)10);
        }
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setWrapText(true);
        if (cell.getRowIndex() == 0) {
            cell.getRow().setHeight(firstRowHeight);
        }else {
            cell.getRow().setHeight(height);
        }
        // 下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // 左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // 上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        cell.setCellStyle(cellStyle);
    }
    public static void setRow2(Cell cell, Sheet sheet, List<Integer> redCol,CellStyle cellStyle,short fontColor) {
        // 除了说明，设置行高为20
        cell.getRow().setHeight(height);

        Workbook workbook = sheet.getWorkbook();
        cellStyle = workbook.createCellStyle();
        Font headFont = getTitleCommonStyle(workbook);
        // 必填字体设为红色
        if (redCol != null && redCol.contains(cell.getColumnIndex())) {
            String stringCellValue = cell.getStringCellValue();
            if (StringUtils.isNotBlank(stringCellValue)) {
                if (!"*".equals(stringCellValue.substring(0,1))) {
                    cell.setCellValue("*" + stringCellValue);
                }
            }
            headFont.setColor(fontColor);
        }
        cellStyle.setFont(headFont);
        // 背景设置为灰色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // 左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // 上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 没通过校验的将未通过校验的字段进行字体标红返回到Excel中
     * @param cell
     * @param sheet
     */
    public static void setRow3(Cell cell, Sheet sheet,CellStyle cellStyle) {
        // 除了说明，设置行高为20
        cell.getRow().setHeight(height);

        Workbook workbook = sheet.getWorkbook();
        //CellStyle cellStyle = workbook.createCellStyle();
        Font headFont = getTitleCommonStyle(workbook);
        headFont.setColor(COLOR_RED);
        cellStyle.setFont(headFont);
        if (StringUtils.isEmpty(cell.getStringCellValue())) {
            // 背景设置为红色色
            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        cell.setCellStyle(cellStyle);
    }

    public static void setComment(Cell cell, Sheet sheet, String commentStr) {
        Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        Comment comment = drawingPatriarch.createCellComment(
                new XSSFClientAnchor(0, 0, 0, 0, cell.getColumnIndex(), cell.getRowIndex(),
                        cell.getColumnIndex() + 2, cell.getRowIndex() + 6));
        comment.setString(new XSSFRichTextString(commentStr));
        cell.setCellComment(comment);
    }

    private static Font getTitleCommonStyle(Workbook workbook) {
        Font headFont = workbook.createFont();
        headFont.setFontName("微软雅黑");
        headFont.setFontHeightInPoints((short)11);
        return headFont;
    }

    /**
     * 合并指定单元格
     * eg: {0, {[0, 3], [4, 5], [6, 10]}} 合并第1行第1列至第4列，第5列至第6列，第7列至第11列
     * @return
     */
    public static List<CellRangeAddress> getCellRangeAddresss(Map<Integer, List<Integer[]>> arr) {
        List<CellRangeAddress> list = new ArrayList<>();
        for (Integer row : arr.keySet()) {
            List<Integer[]> cols = arr.get(row);
            for (Integer[] col : cols) {
                CellRangeAddress rowCell = new CellRangeAddress(row, row, col[0], col[1]);
                list.add(rowCell);
            }
        }
        return list;
    }

    /**
     * 模板字段的微软雅黑，字体大小11号，颜色黑色（000000） 行高20
     * @param cell
     * @param sheet
     */
    public static void setRow4(Cell cell, Sheet sheet,CellStyle cellStyle) {
        // 模板字段的微软雅黑，字体大小11号，颜色黑色（000000） 行高20
        Workbook workbook = sheet.getWorkbook();
        //CellStyle cellStyle = workbook.createCellStyle();
        Font headFont = getTitleCommonStyle(workbook);
        cellStyle.setFont(headFont);
        cell.setCellStyle(cellStyle);

        // 除了说明，设置行高为20
        cell.getRow().setHeight(height);
    }
}

导出方式(正常导出,根据实体类) (实体类字段注解:@ExcelProperty(value = “客户姓名”)) 实体类上注解(设置列宽 @ColumnWidth(16)):

//设置样式,文件后缀
EasyExcelUtils.constResponse(response, "外场客户列表");
//导出((HttpServletResponse response),(类文件class))   (文件名)   (封装了实体类数据的list)
EasyExcel.write(response.getOutputStream(), PcOutCustomerVo.class).sheet("外场客户列表").doWrite(records);

导出方式(动态)

// 表头
List<List<String>> headList = new ArrayList<>();
List<String> head0 = Arrays.asList("按揭分类");
List<String> head1 = Arrays.asList("欠款分类");
List<String> head3 = Arrays.asList("欠款金额小计");
headList.add(head0);
headList.add(head1);
headList.add(head3);
// 内容
List<List<Object>> list = new ArrayList<>();
List<Object> list1 = Arrays.asList("{a}","{b}","{c}");
list.add(list1);
EasyExcelUtils.constResponse(response, "test");
(HttpServletResponse response) (头字段list) (表名) (数据list)
EasyExcel.write(response.getOutputStream()).head(headList).sheet("test").doWrite(list);

// 表头
List<List<String>> headList = new ArrayList<>();
List<String> head0 = Arrays.asList("按揭分类","欠款分类","欠款金额小计");

Map<String ,Object> map = new HashMap <>();
map.put("按揭分类","ces1");
map.put("欠款分类","ces2");
map.put("欠款金额小计","ces3");


List<Map<String ,Object>> mapList = new ArrayList <>();
mapList.add(map);
mapList.add(map);
mapList.add(map);
// 内容
List<List<Object>> list = new ArrayList<>();

List <String> lista = null;
//拼接表头
for (String s : head0) {
    lista = new ArrayList <>();
    lista.add(s);
    headList.add(lista);
}


List <Object> objectList = null;
//拼接数据
for (Map <String, Object> objectMap : mapList) {
    objectList =new ArrayList <>();
    for (String s : head0) {
        objectList.add(objectMap.get(s));
    }
    list.add(objectList);
}


//导出
EasyExcelUtils.constResponse(response, "test");

EasyExcel.write(response.getOutputStream()).head(headList).sheet("test").doWrite(list);

//表头样式工具类

package com.eaton.biz.modules.prj.excel;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
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.HashMap;
import java.util.List;
import java.util.Map;

public class Custemhandler extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;
    private  Map<Integer, Map <Integer, Integer>> CACHE = new HashMap(8);

    public Custemhandler() {
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List <CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData)cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch(type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

//设置动态导出表头样式

EasyExcelUtils.constResponse(response, "文件名字");
EasyExcel.write(response.getOutputStream()).needHead(true).registerWriteHandler(new Custemhandler()//上面的工具类).head(lists//表头).sheet("文件名字").doWrite(listList//内容);