GitCode 开源社区

话不多说，上才艺。

下面代码粘贴即用

 /**
     *
     * 导出表格带下拉框
     */
    @GetMapping("exportBox")
    public void export(HttpServletResponse response)
        throws IOException
    {
        
        String fileName = "模板.xls";
        
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)14);
        // 字体加粗
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        
        // 内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置内容字体
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short)12);
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置单元格格式为 文本
        contentWriteCellStyle.setDataFormat((short)49);
        
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        
        // 假数据 实际开发中一般是从数据库中查询
        List<Employee> objects = new ArrayList<>();
        for (int i = 0; i < 3; i++)
        {
            Employee employee = new Employee();
            employee.setSchool(i + "大学");
            employee.setName(i + "RR");
            objects.add(employee);
        }
        
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        // 设置表名，引脚名，文件格式，list数据
        EasyExcel.write(response.getOutputStream(), Employee.class)
            .registerWriteHandler(horizontalCellStyleStrategy)
            .registerWriteHandler(new SpinnerWriteHandler())
            .sheet("模板")
            .doWrite(objects);
        
    }

package com.temporary.handle;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

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

/**
 * @author Han
 * @Description
 * @date 2022/3/21
 */
public class SpinnerWriteHandler implements SheetWriteHandler
{
    
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)
    {
        
    }
    
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)
    {
        String[] ageTypes = new String[] {"0 - 14", "15 - 25", "26 - 50", "51 - ~"};
        String[] schoolTypes = new String[] {"清华大学", "北京大学", "郑州大学", "南京大学"};
        Map<Integer, String[]> mapDropDown = new HashMap<>();
        // 这里的key值 对应导出列的顺序 从0开始
        mapDropDown.put(1, ageTypes);
        mapDropDown.put(2, schoolTypes);
        Sheet sheet = writeSheetHolder.getSheet();
        /// 开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();// 设置下拉框
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet())
        {
            /*** 起始行、终止行、起始列、终止列 **/
            CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey());
            /*** 设置下拉框数据 **/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            /*** 处理Excel兼容性问题 **/
            if (dataValidation instanceof XSSFDataValidation)
            {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            }
            else
            {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }
        
    }
}