POI导出大量下拉解决方案

1. 处理思路

• 下拉个数较少时直接创建下拉
• 下拉个数超过一定数量时候，直接创建下拉会有bug，导致展示异常。此时可以创建一个隐藏sheet，并添加单元格数据校验指向刚才创建的隐藏sheet中的数据

2. 源码

import com.hz.model.CommonException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.util.*;

/**
 * excel导出工具
 *
 * @author pp_lan
 */
public class ExcelExportUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportUtils.class);

    public String outputFile;

    private List<String> headers;

    private List<List<String>> dataList;

    private Map<Integer, List<String>> dropdownMap;

    private ExcelExportUtils() {
        this.outputFile = null;
        this.headers = null;
        this.dataList = null;
        this.dropdownMap = new HashMap<>();
    }


    public static final ExcelExportUtils getInstance() {
        return new ExcelExportUtils();
    }

    public ExcelExportUtils buildOutput(String outputFile) {
        this.outputFile = outputFile;
        return this;
    }

    public ExcelExportUtils buildHeader(List<String> headers) {
        this.headers = headers;
        return this;
    }

    public ExcelExportUtils buildData(List<List<String>> dataList) {
        this.dataList = dataList;
        return this;
    }

    public ExcelExportUtils buildDropdown(int columnIndex, List<String> dropdownContent) {
        this.dropdownMap.put(columnIndex, dropdownContent);
        return this;
    }



    public void createExcel(String sheetName) {

        // 校验数据
        this.checkData();

        try (FileOutputStream out = new FileOutputStream(outputFile)) {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(sheetName);

            sheet.setDefaultColumnWidth(18);
            sheet.setDefaultRowHeightInPoints(20);

            XSSFCellStyle headerStyle = buildStyle(workbook, true);
            XSSFCellStyle dataStyle = buildStyle(workbook, false);

            int rowNumber = 0;
            XSSFRow headerRow = sheet.createRow(rowNumber++);
            headerRow.setHeightInPoints(23);
            for (int i = 0; i < headers.size(); i++) {
                XSSFCell cell = headerRow.createCell(i);
                cell.setCellValue(headers.get(i));
                cell.setCellStyle(headerStyle);
            }

            for (int i = 0; i < dataList.size(); i++) {
                XSSFRow dataRow = sheet.createRow(rowNumber++);
                dataRow.setHeightInPoints(20);
                List<String> rowData = dataList.get(i);

                for (int j = 0; j < rowData.size(); j++) {
                    XSSFCell cell = dataRow.createCell(j);
                    cell.setCellValue(rowData.get(j));
                    cell.setCellStyle(dataStyle);
                }

            }

            // 创建下拉
            for (Map.Entry<Integer, List<String>> entry : this.dropdownMap.entrySet()) {
                Integer columnIndex = entry.getKey();
                List<String> dropdownList = entry.getValue();
                addDropDown(workbook, sheetName, columnIndex, dropdownList, dataList.size());
            }

            workbook.write(out);
            out.flush();

        } catch (Exception e) {
            throw new CommonException("导出异常", e);
        }
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param row
     * @param firstCol
     * @param lastCol
     */
    private void addMergeCell(XSSFSheet sheet, int row, int firstCol, int lastCol) {
        CellRangeAddress cellAddresses = new CellRangeAddress(row, row, firstCol, lastCol);
        sheet.addMergedRegion(cellAddresses);
    }

    /**
     * 校验数据
     */
    private void checkData() {
        if (!StringUtils.hasText(outputFile)) {
            throw new CommonException("输出文件未设置");
        }

        if (CollectionUtils.isEmpty(headers)) {
            throw new CommonException("表头未设置");
        }
        if (CollectionUtils.isEmpty(dataList)) {
            throw new CommonException("数据未设置");
        }

        int headerSize = headers.size();
        for (List<String> data : dataList) {
            if (data == null) {
                continue;
            }

            int arrange = data.size();
            if (headerSize != arrange) {
                throw new CommonException("数据校验失败, 列和表头数量不一致");
            }
        }
    }

    /**
     * 添加下拉
     *
     * @param workbook
     * @param sheetName
     * @param column
     * @param dropdownList
     */
    private void addDropDown(XSSFWorkbook workbook, String sheetName,  int column, List<String> dropdownList, int dataSize) {

        if (CollectionUtils.isEmpty(dropdownMap)) {
            return;
        }

        if (dropdownList.size() <= 100) {
            XSSFSheet sheet = workbook.getSheet(sheetName);

            for (Map.Entry<Integer, List<String>> entry : dropdownMap.entrySet()) {
                Integer columnIndex = entry.getKey();
                List<String> dropDownList = entry.getValue();
                String[] options = dropDownList.toArray(new String[]{});

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
                DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(options);
                CellRangeAddressList addressList = new CellRangeAddressList(1, dataSize, columnIndex, columnIndex);
                DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
                sheet.addValidationData(validation);
            }

        } else {
            // 下拉项太多会有bug，采用此方案可以解决
            int sheetTotal = workbook.getNumberOfSheets();
            String hiddenSheetName = "hiddenSheet" + sheetTotal;
            XSSFSheet sheet = workbook.createSheet(hiddenSheetName);

            for (int i = 0; i < dropdownList.size(); i++) {
                XSSFRow row = sheet.createRow(i);
                XSSFCell cell = row.createCell(0);
                cell.setCellValue(dropdownList.get(i));
            }

            int dropdownSize = dropdownList.size();
            // 使用单元格A的数据进行单元格校验
            String strFormula = new StringBuilder().append(hiddenSheetName).append("!$A$1:$A$").append(dropdownSize).toString();

            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
            CellRangeAddressList cell = new CellRangeAddressList(1, dataSize, column, column);
            XSSFDataValidationHelper help = new XSSFDataValidationHelper(sheet);
            DataValidation validation = help.createValidation(constraint, cell);

            XSSFSheet dataSheet = workbook.getSheet(sheetName);
            if (dataSheet != null) {
                dataSheet.addValidationData(validation);
            }
            workbook.setSheetHidden(sheetTotal, true);
        }
    }


    /**
     * 创建单元格样式
     *
     * @param workbook
     * @param isHeader
     * @return
     */
    private static XSSFCellStyle buildStyle(XSSFWorkbook workbook, boolean isHeader) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 字体
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        if (isHeader) {
            font.setFontHeightInPoints((short) 12);
            font.setBold(true);
        } else {
            font.setFontHeightInPoints((short) 10);
            font.setBold(false);
        }
        cellStyle.setFont(font);

        // 边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        return cellStyle;
    }
}

3. 使用示例

public static void main(String[] args) {
        List<String> headers = new ArrayList<>(Arrays.asList("银行名称", "列B", "列C"));

        // 下拉数量相对较少
        List<List<String>> dataList = new ArrayList<>();
        dataList.add(new ArrayList<>(Arrays.asList("中国银行", "2111", "3111")));
        dataList.add(new ArrayList<>(Arrays.asList("招商银行", "b111", "c111")));
        List<String> dropdownList = new ArrayList<>();
        for (int i = 0; i < 80; i++) {
            dropdownList.add("选项" + i);
        }

        // 下拉数量大于100
        ArrayList<String> dropdownList2 = new ArrayList<>();
        for (int i = 0; i < 300; i++) {
            dropdownList2.add("新选项" + i);
        }

        // 下拉数量较少
        List<String> bankList = new ArrayList<>();
        try (BufferedReader br = new BufferedReader(new FileReader("/home/xk/workspace/gitee/javarecord/doc/bankScrapy.txt"));) {
            String bank;
            while ((bank = br.readLine()) != null) {
                bankList.add(bank);
            }

        } catch (Exception e) {
            LOGGER.error("读取银行数据失败", e);
        }


        ExcelExportUtils excelExportUtils = ExcelExportUtils.getInstance();
        excelExportUtils.buildOutput("/home/xk/workspace/gitee/javarecord/doc/导出示例new.xlsx")
                .buildHeader(headers)
                .buildData(dataList)
                .buildDropdown(0, bankList)
                .buildDropdown(1, dropdownList)
                .buildDropdown(2, dropdownList2);

        excelExportUtils.createExcel("数据");
    }

4. 导出样例