EasyExcel使用

该代码示例展示了如何使用EasyExcel在Excel中添加下拉选择框,并对标题进行批注和字体填充红色。通过`TitleHandler`类处理单元格样式,包括设置标题列的颜色和批注。同时,提供了`EasyExcelUtil`工具类用于简化导出过程。
摘要由CSDN通过智能技术生成

EasyExcel增加下拉选择框

@Slf4j
public class TitleHandler implements SheetWriteHandler {

    /**
     * 下拉框值
     */
    private Map<Integer,String[]> dropDownMap;

    /**
     * 多少行有下拉
     */
    private final static Integer rowSize = 200;

    public TitleHandler(Map<Integer,String[]> dropDownMap) {
        this.dropDownMap = dropDownMap;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();

        dropDownMap.forEach((celIndex, strings) -> {
            // 区间设置
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, rowSize, celIndex, celIndex);
            // 下拉内容
            DataValidationConstraint constraint = helper.createExplicitListConstraint(strings);
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
            sheet.addValidationData(dataValidation);
        });
    }
}
public class EasyExcelUtil {
    public static <T> void writeExcelWithModel(OutputStream outputStream, Class<T> clazz, Map<Integer,String[]> dropDownMap) throws IOException {
        EasyExcel.write(outputStream, clazz).registerWriteHandler(new TitleHandler(dropDownMap)).sheet("模板").doWrite(ListUtil.empty());
    }
}
public class Test{
    @Data
    @ColumnWidth(20) // 定义列宽
    public static class TestVO {
        @ExcelProperty(value = "*姓名", index = 0)
        private String name;
        @ExcelProperty(value = "*年龄", index = 1)
        private int age;
        @ExcelProperty(value = "学校", index = 2)
        private String school;
    }
    /**
     * 测试导出下拉款
     * @throws IOException
     */
    @Test
    public void testDropDown() throws IOException {
        // 输出流
        OutputStream outputStream = new FileOutputStream(new File("D:\\1.xlsx"));
        HashMap<Integer, String[]> dropDownMap = new HashMap<>();
        // 指定下拉框
        String[] school = {"一中","二中","三中"};
        dropDownMap.put(2,school);
        EasyExcelUtil.writeExcelWithModel(outputStream, TestVO.class, dropDownMap);
    }
}

EasyExcel标题加批注和标题字体填充红色

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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

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

@Slf4j
public class TitleHandler implements CellWriteHandler {

    //操作列
    private List<Integer> columnIndexs;
    //颜色
    private Short colorIndex;
    // 批注<列的下标,批注内容>
    private HashMap<Integer,String> annotationsMap;

    public TitleHandler(List<Integer> columnIndexs, Short colorIndex, HashMap<Integer, String> annotationsMap) {
        this.columnIndexs = columnIndexs;
        this.colorIndex = colorIndex;
        this.annotationsMap = annotationsMap;
    }

    public TitleHandler(List<Integer> columnIndexs, Short colorIndex) {
        this.columnIndexs = columnIndexs;
        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 afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if(isHead){
            // 设置列宽
            Sheet sheet = writeSheetHolder.getSheet();
            sheet.setColumnWidth(cell.getColumnIndex(), 14 * 256);
            writeSheetHolder.getSheet().getRow(0).setHeight((short)(1.8*256));
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            Drawing<?> drawing = sheet.createDrawingPatriarch();

            // 设置标题字体样式
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontName("宋体");
            headWriteFont.setFontHeightInPoints((short)14);
            headWriteFont.setBold(true);
            if (CollectionUtils.isNotEmpty(columnIndexs) &&
                    colorIndex != null &&
                    columnIndexs.contains(cell.getColumnIndex())) {
                // 设置字体颜色
                headWriteFont.setColor(colorIndex);
            }
            headWriteCellStyle.setWriteFont(headWriteFont);
            headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
            cell.setCellStyle(cellStyle);

            if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())) {
                // 批注内容
                String context = annotationsMap.get(cell.getColumnIndex());
                // 创建绘图对象
                Comment comment=drawing.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) cell.getColumnIndex(), 0, (short) 5, 5));
                comment.setString(new XSSFRichTextString(context));
                cell.setCellComment(comment);
            }
        }
    }
}
public class EasyExcelUtil {
     /**
     * 导出excel
     * @param outputStream 输出流
     * @param dataList     导出的数据
     * @param classT        模板类
     * @param sheetName     sheetName
     * @param cellWriteHandlers    样式处理类
     */
    public static void writeExcelWithModel(OutputStream outputStream, List<? extends Object> dataList, Class<? extends Object> classT, String sheetName, CellWriteHandler... cellWriteHandlers) {

        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 单元格策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 初始化表格样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, classT).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy);
        if (null != cellWriteHandlers && cellWriteHandlers.length > 0) {
            for (int i = 0; i < cellWriteHandlers.length; i++) {
                excelWriterSheetBuilder.registerWriteHandler(cellWriteHandlers[i]);
            }
        }
        // 开始导出
        excelWriterSheetBuilder.doWrite(dataList);
    }
}
public class TestEasyExcel {
    @Data
    @ColumnWidth(20)
    public static class TestVO {
        @ExcelProperty(value = "*姓名", index = 0)
        private String name;
        @ExcelProperty(value = "*年龄", index = 1)
        private int age;
        @ExcelProperty(value = "学校", index = 2)
        private String school;
    }

    /**
     * 测试导出模板
     * 1. 标题指定某列标红色字段
     * 2. 标题指定某列加批注
     */
    @Test
    public void testExport1() throws FileNotFoundException {
        // 输出流
        OutputStream outputStream = new FileOutputStream(new File("D:\\1.xlsx"));
        // 导出的数据
        List<TestVO> dataList = new ArrayList<>();
        // 指定标红色的列
        List<Integer> columns = Arrays.asList(0, 1);
        // 指定批注
        HashMap<Integer, String> annotationsMap = new HashMap<>();
        annotationsMap.put(0,"第一列标题批注");
        annotationsMap.put(1,"第二列标题批注");
        TitleHandler titleHandler = new TitleHandler(columns, IndexedColors.RED.index,annotationsMap);
        EasyExcelUtil.writeExcelWithModel(outputStream, dataList, TestVO.class, "sheetName", titleHandler);
    }
}

在这里插入图片描述

easyExcel同一单元格部分文字颜色、样式修改

public class WriteHandlerStrategy implements CellWriteHandler {

    @Override
    public void beforeCellCreate(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
                                 final Row row, final Head head, final Integer columnIndex,
                                 final Integer relativeRowIndex, final Boolean isHead) {

    }

    @Override
    public void afterCellCreate(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
                                final Cell cell, final Head head, final Integer relativeRowIndex, final Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
                                       final CellData cellData, final Cell cell, final Head head,
                                       final Integer relativeRowIndex, final Boolean isHead) {

    }

    @Override
    public void afterCellDispose(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
                                 final List<CellData> list, final Cell cell, final Head head, final Integer integer,
                                 final Boolean aBoolean) {

    }
}

@Override
    public void afterCellDispose(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
                                 final List<CellData> list, final Cell cell, final Head head, final Integer integer,
                                 final Boolean aBoolean) {
        Sheet sheet = writeSheetHolder.getSheet();
        Workbook workbook = sheet.getWorkbook();
        // xlsx格式,如果是老版本格式的话就用 HSSFRichTextString
        XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        // 从哪到哪,你想设置成什么样的字体都行startIndex,endIndex
        richString.applyFont(0, 2, font);
        // 再设置回每个单元格里
        cell.setCellValue(richString);
    }

最后最最最重要的就是,富文本得把inMemory设置为true,不然没有效果

EasyExcel.write(response.getOutputStream(), ReportVO.class)
            .inMemory(true) // 富文本
            .registerWriteHandler(new WriteHandlerStrategy()).build();

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值