EaxyExcel高亮单元格和填充颜色

龙叔的技术笔记

已于 2023-04-12 18:20:51 修改

阅读量2.5k

点赞数

分类专栏： EasyExcel 文章标签： java

于 2021-01-24 22:47:45 首次发布

本文链接：https://blog.csdn.net/qq_42747210/article/details/113099280

版权

EasyExcel 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

前言

excel导出有高亮单元格的需求，本文介绍EaxyExcel高亮单元格的方法

效果

在这里插入图片描述

高亮的单元格，字体也设为红色

实现

先引入EasyExcel和Guava工具类，还有lombok

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

		<dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>30.1-jre</version>
        </dependency>

		<!-- springboot test启动器 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

对单元格的样式进行指定，要实现CellWriteHandler接口，并在afterCellDispose进行相关操作。灵活使用EasyExcel的WriteHandler接口，可以实现下拉框，高亮等需求。


import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.*;

import java.util.HashMap;
import java.util.List;

/**
 * https://blog.csdn.net/scholartang/article/details/108024953
 *
 * @date 01/22/2021 15:46
 */
public class CellColorCellWriteHandler implements CellWriteHandler {

    /**
     * map
     * key：第i行
     * value：第i行中单元格索引集合
     */
    private HashMap<Integer, List<Integer>> map;

    /**
     * 颜色
     */
    private Short colorIndex;

    /**
     * 无参构造
     */
    public CellColorCellWriteHandler() {

    }

    /**
     * 有参构造
     */
    public CellColorCellWriteHandler(HashMap<Integer, List<Integer>> map, Short colorIndex) {
        this.map = map;
        this.colorIndex = colorIndex;
    }


    /**
     * 在创建单元格之前调用
     */
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    /**
     * 在单元格创建后调用
     */
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    }

    /**
     * 在单元上的所有操作完成后调用
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        /*
         * 考虑到导出数据量过大的情况，不对每一行的每一个单元格进行样式设置，只设置必要行中的某个单元格的样式
         */
        //当前行的第i列
        int i = cell.getColumnIndex();
        //不处理第一行
        List<Integer> integers = map.get(cell.getRowIndex());
        if (integers != null && integers.size() > 0) {
            if (integers.contains(i)) {
                // 根据单元格获取workbook
                Workbook workbook = cell.getSheet().getWorkbook();
                //设置行高
                writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) (1.4 * 256));
                // 单元格策略
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                // 设置背景颜色白色
                contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                // 设置垂直居中为居中对齐
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                // 设置左右对齐为靠左对齐
                contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
                // 设置单元格上下左右边框为细边框
                contentWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
                contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
                contentWriteCellStyle.setBorderRight(BorderStyle.MEDIUM);
                contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);
                // 创建字体实例
                WriteFont cellWriteFont = new WriteFont();
                // 设置字体大小
                cellWriteFont.setFontName("宋体");
                cellWriteFont.setFontHeightInPoints((short) 14);
                //设置字体颜色，此处用红色
                cellWriteFont.setColor(IndexedColors.RED.getIndex());
                //单元格颜色，被高亮的单元格颜色
                contentWriteCellStyle.setFillForegroundColor(colorIndex);
                contentWriteCellStyle.setWriteFont(cellWriteFont);
                CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
                //设置当前行第i列的样式
                cell.getRow().getCell(i).setCellStyle(cellStyle);
            }
        }
    }
}

单元测试

定义Person类

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @date 01/24/2021 22:16
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Person {

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("性别")
    private String gender;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("爱好")
    private String hobby;

    @ExcelProperty("城市")
    private String city;

    @ExcelIgnore
    private String status;
}

测试


/**
 * EasyExcel设置行中单个单元格的样式，如高亮单元格
 * <p>
 * https://blog.csdn.net/scholartang/article/details/108024953
 *
 * @date 01/22/2021 15:46
 */
public class Demo4ForCSDN {

    @Test
    public void test() throws FileNotFoundException {
        // 文件输出位置
        OutputStream out = new FileOutputStream("/Users/quanlinglong/Downloads/mergeDemo/demo" + System.currentTimeMillis() + ".xlsx");

        // 指定要高亮的单元格
        //用来记录需要为第`key`行中的第`value.get(i)`列设置样式
        HashMap<Integer, List<Integer>> map = new HashMap<>();
        // 第1行，第2，5列的格子高亮
        map.put(1, Arrays.asList(2, 5));
        map.put(2, Arrays.asList(1, 2, 4));
        map.put(4, Arrays.asList(1, 2, 4));

        //指定单元格样式,通过有残构造，指定填充的颜色
        CellColorCellWriteHandler writeHandler = new CellColorCellWriteHandler(map, IndexedColors.YELLOW1.getIndex());
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel
                    .write(out, Person.class)  // Person指定了表头
                    .registerWriteHandler(defaultStyles())
                    .registerWriteHandler(writeHandler) // 注意优先级，后面的writeHandler会覆盖前面的样式
                    .build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            // 第一个参数传List，泛型可以是person实例或者一个List
//            excelWriter.write(personData(), writeSheet);
            // 如果泛型是List，就失去隐藏表头的功能，所有字段都写到excel
            excelWriter.write(listData(), writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }


    }

    private List<List<Object>> listData() {
        List<List<Object>> lists = new ArrayList<>();
        lists.add(Lists.newArrayList("张三","男",20,"游泳","北京","单身"));
        lists.add(Lists.newArrayList("李四","男",20,"游泳","北京","单身"));
        lists.add(Lists.newArrayList("王五","男",20,"游泳","北京","单身"));
        lists.add(Lists.newArrayList("赵薇","女",25,"游泳","北京","单身","明星"));
        return lists;
    }

    private List<Person> personData() {
        ArrayList<Person> people = new ArrayList<>();
        people.add(new Person("张三","男",20,"游泳","北京","单身"));
        people.add(new Person("李四","男",20,"游泳","北京","单身"));
        people.add(new Person("王五","男",20,"游泳","北京","单身"));
        people.add(new Person("赵薇","女",25,"游泳","北京","单身"));
        return people;
    }

    /**
     * 默认样式
     */
    public static HorizontalCellStyleStrategy defaultStyles() {
        //默认样式
        //表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景设置淡蓝色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容样式策略策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色白色
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置垂直居中为居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置左右对齐为靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 设置单元格上下左右边框为细边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderRight(BorderStyle.MEDIUM);
        contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);
        //创建字体对象
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 14);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 初始化表格样式
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}