AbstractXlsxView导出带有下拉框列的Excel

摘要：在导出数据的时候，遇到了某一列要设置为下拉框选择，前辈们在网上都是简单的例子，没有下拉框的设置，索性自己折腾一下。

AbstractXlsxView 在导出Excel数据的时候，遇到了某一列要设置为下拉框选择，前辈们在网上都是简单的例子，没有下拉框的设置，索性自己折腾一下。

Controller:

...
    @Autowired
    private ActivityMonitorService monitorService;
...
    @GetMapping("/downloadExcel")
    public ModelAndView downloadExcel() {
        ExcelView excelView = new ExcelView();
        // 该map组装excelView用到的数据及表头
        Map map = monitorService.uploadExcelParam();
        return new ModelAndView(excelView, map);
    }
... 

AbstractXlsxView实现类：

import com.jason.dto.ActivityMonitorDto;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * 导出xlsx数据
 */
@Service
public class ExcelView extends AbstractXlsxView {

    private final int MAX_ROW = 65535;

    @Override
    protected void buildExcelDocument(Map map, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
        String name = (String) map.get("fileName");
        if (StringUtils.isEmpty(name)) {
            name = "Template";
        }
        String fileName =  name + ".xlsx";
        // 火狐浏览器乱码解决
        String agent = request.getHeader("User-Agent");
        if (agent != null) {
            if ("firefox".contains(agent.toLowerCase())) {
                response.setHeader("content-disposition", String.format("attachment;filename*=utf-8"zh_cn"%s", URLEncoder.encode(fileName,"utf-8")));
            } else {
                response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            }
        }
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/ms-excel;charset=utf-8");

        Sheet sheet = workbook.createSheet();

        String[] headerTitle = (String[]) map.get("headers");
        Row header = sheet.createRow(0);
        for (int i = 0; i < headerTitle.length; i++) {
            header.createCell(i).setCellValue(headerTitle[i]);
        }

        // 需要导出的数据
        List list = (List) map.get("content");
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(list.get(i).getActName());
            row.createCell(1).setCellValue(list.get(i).getActId());
            // 第三列填写默认值
            row.createCell(2).setCellValue(list.get(i).getReleaseTime());
            row.createCell(3).setCellValue(list.get(i).getPointPositionName());
            // 第五列填写默认值
            row.createCell(4).setCellValue(list.get(i).getAttributeName());
            row.createCell(5).setCellValue(list.get(i).getUseChannelName());
            row.createCell(6).setCellValue(list.get(i).getRemark());
        }

        // 普通格式组装完毕，开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();

        // 下拉框的option数据
        String[] pointPositionArray = (String[]) map.get("pointPositionArray");
        String[] channelArray = (String[]) map.get("channelArray");

        // 第三和第五列设置为下拉框
        DataValidationConstraint constraintPosition = helper.createExplicitListConstraint(pointPositionArray);
        CellRangeAddressList regionsPosotion = new CellRangeAddressList(1, MAX_ROW, 3, 3);
        DataValidation positionData = helper.createValidation(constraintPosition, regionsPosotion);
        
        DataValidationConstraint constraintChannel = helper.createExplicitListConstraint(channelArray);
        CellRangeAddressList regionChannel = new CellRangeAddressList(1, MAX_ROW, 5, 5);
        DataValidation channelData = helper.createValidation(constraintChannel, regionChannel);

        //处理Excel兼容性问题
        if(positionData instanceof XSSFDataValidation) {
            positionData.setSuppressDropDownArrow(true);
            positionData.setShowErrorBox(true);
        }else {
            positionData.setSuppressDropDownArrow(false);
        }
        if(channelData instanceof XSSFDataValidation) {
            channelData.setSuppressDropDownArrow(true);
            channelData.setShowErrorBox(true);
        }else {
            channelData.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(positionData);
        sheet.addValidationData(channelData);
    }
}