easyexcel自定义样式、选定列下拉数据

仓央乀

已于 2024-09-09 14:42:51 修改

阅读量2.3k

点赞数 2

CC 4.0 BY-SA版权

文章标签： java 开发语言

于 2023-07-07 15:41:49 首次发布

本文链接：https://blog.csdn.net/qq_44796093/article/details/131598283

该代码示例展示了如何使用EasyExcel库配合自定义拦截器OrgSheetWriteHandler来导出Excel文件，包括设置不需要表头、内容和标题格式，以及特定列的数据格式。拦截器在创建sheet后将某些列设置为文本格式，同时提供了风格策略来定制头部和内容的样式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、导入接口

导入工具类中
.registerWriteHandler(new OrgSheetWriteHandler()) 自定义拦截器进行设置

.registerWriteHandler(OrgSheetWriteHandler.getStyleStrategy()) 自定义内容、标题格式

.needHead(false) 不需要表头

.registerWriteHandler(new ExcelDropdownHandler()) 自定义下拉样式

	public static void exportOrgModelExcel(List<?> list, String sheetName, Class<?> pojoClass, String fileName,
								   HttpServletResponse response) {
		OutputStream out = null;
		try {
			if (fileName.indexOf(".") < 0) {
				throw new BizException("文件名必须带后缀");
			}
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setContentType("charset=UTF-8;application/form-data");
			response.setHeader("Content-Disposition",
					"attachment; filename=" + new String(fileName.getBytes(), "ISO8859-1"));
			response.setHeader("Set-Cookie", "fileDownload=true; path=/");
			out = response.getOutputStream();
			EasyExcel.write(out, pojoClass).sheet(StringUtils.isEmpty(sheetName) ? "sheet0" : sheetName)
					// 宽度自适应
					.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
					// 第9列设置为文本格式
					.registerWriteHandler(new OrgSheetWriteHandler())
					// 自定义全表的样式
					.registerWriteHandler(OrgSheetWriteHandler.getStyleStrategy())
					// 自定义下拉样式
//					.registerWriteHandler(new ExcelDropdownHandler())
					// 不需要表头
					.needHead(false)
					.doWrite(list);
		} catch (IOException e) {
			throw new BizException(e);
		} finally {
			if (out != null) {
				try {
					out.flush();
					out.close();
				} catch (IOException e) {
					logger.error("输出流关闭失败!", e);
				}
			}
		}
	}

2、拦截器代码

public class OrgSheetWriteHandler implements SheetWriteHandler  {

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 设置为文本格式
        SXSSFSheet sxssfSheet = (SXSSFSheet) writeSheetHolder.getSheet();
        CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();

        // 49为文本格式
        cellStyle.setDataFormat((short) 49);
        // 8为列，一整列设置为文本格式（从0开始）
        sxssfSheet.setDefaultColumnStyle(1, cellStyle);
        sxssfSheet.setDefaultColumnStyle(8, cellStyle);
        sxssfSheet.setDefaultColumnStyle(13, cellStyle);
        sxssfSheet.setDefaultColumnStyle(14, cellStyle);
    }

    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置对齐
        //headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 背景色, 设置为绿色，也是默认颜色
        //headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        // 字体
        //WriteFont headWriteFont = new WriteFont();
        //headWriteFont.setFontHeightInPoints((short) 12);
        //headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
//
//        // 字体策略
//        WriteFont contentWriteFont = new WriteFont();
//        //contentWriteFont.setFontHeightInPoints((short) 12);
//        contentWriteCellStyle.setWriteFont(contentWriteFont);
//        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
//        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//        //设置 水平居中
//        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//        //设置边框样式
//        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
}

4、自定义下拉数据

package com.boot.bean.handel;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.HashMap;
import java.util.Map;

/**
 * 自定义下拉样式
 *
 * @Author yujianfei
 * @Date 2024/8/7 9:23
 * @Version 1.0
 */
@Slf4j
public class ExcelDropdownHandler implements SheetWriteHandler {

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

    /**
     * 设置下拉数据的内容
     *
     * @return
     */
    public Map<Integer, String[]> getDropDownMap() {
        HashMap<Integer, String[]> result = new HashMap<>();
        // 指定下拉框
        String[] school = {"张三", "李四", "王五", "王二麻子"};
        // 从0开始，表示第二列是下拉
        result.put(1, school);
        return result;
    }

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 下拉框值
        Map<Integer, String[]> dropDownMap = this.getDropDownMap();
        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);
        });
    }

}

3、调用工具类

List<OrgStructureExportVO> restResult = orgStructureService.downloadOrg(uuid);
EasyExcelUtils.exportOrgModelExcel(restResult, "组织机构数据", OrgStructureExportVO.class, "组织机构数据.xlsx", response);