POI 导出excel ，xlsx 公式多个下拉框 XSSF

业务需求需要导出Excel，然而xls部分公式无法使用，且导出效率和行数有限制，因此更换导出文件为xlsx，下拉框个数根据参数传入。代码写得有点乱，如下代码为导出excel xlsx格式的代码

/**
*导出Excel  xlsx
* @param excelHeader 导出excel的头部 格式UserNo#userNo(输出excel的列的首行名字#所在列对应的实体bean的属性名字)，多列以','隔开  参考UserController里面的导出用户功能;
* @param dataList    需要导出数据的集合。
* @param indexs 下拉框索引
* @param slist 下拉框数组，支持多个下拉框
*/
public static <T> HSSFWorkbook downloadExcel(HttpServletResponse response, String fileName, String excelHeader, Integer[] indexs, List<String[]> slist) throws Exception {
    String[] excelHeaderArray = null;
    if (excelHeader != null) {
        excelHeaderArray = excelHeader.split(",");
    }
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle styleb = wb.createCellStyle();
    styleb.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
    XSSFSheet data = wb.createSheet("Sheet1");
    XSSFSheet select = wb.createSheet("select");
    wb.setSheetHidden(1, true);
    Row rowFirst = data.createRow(0);//第一个sheet的第一行为标题
    for (int i = 0; i < excelHeaderArray.length; i++) {
        Cell cell = rowFirst.createCell(i); //获取第一行的每个单元格
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //标头字体样式
        Font fontStyle = wb.createFont();
        fontStyle.setFontName(Const.EXPORT_EXCEL_HEADER_FONT);
        fontStyle.setFontHeightInPoints(Const.EXPORT_EXCEL_HEADER_FONT_SIZE);
        fontStyle.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(fontStyle);
        String excelHeaderstr = excelHeaderArray[i];
        if (StringUtils.isNotEmpty(excelHeaderstr) && excelHeaderstr.contains("*")) {
            fontStyle.setColor(Font.COLOR_RED);
        } else {
            //fontStyle.setColor(Font.COLOR_NORMAL);
        }

        data.setColumnWidth(i, excelHeaderArray[i].length() * 2 * 256); //设置每列的列宽
        cell.setCellStyle(style); //加样式
        cell.setCellValue(excelHeaderArray[i]); //往单元格里写数据
        cell.setCellType(Cell.CELL_TYPE_STRING);
    }

    //设置单元格的字体
    XSSFCellStyle style2 = wb.createCellStyle();
    XSSFDataFormat format = wb.createDataFormat();
    style2.setDataFormat(format.getFormat("@"));
    style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    style2.setBorderTop(XSSFCellStyle.BORDER_THIN);
    style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    style2.setBorderRight(XSSFCellStyle.BORDER_THIN);
    style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    Font dataFont = wb.createFont();
    dataFont.setFontHeightInPoints(Const.EXPORT_EXCEL_BODY_FONT_SIZE);
    dataFont.setFontName(Const.EXPORT_EXCEL_BODY_FONT);
    style2.setFont(dataFont);

    //默认创建100行数据
    for (int i = 1; i < 100; i++) {
        Row row = data.createRow(i);

        for (int j = 0; j < excelHeaderArray.length; j++) {
            Cell cell = row.createCell(j); //获取第一行的每个单元格
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(style2);
        }
    }
    //列 默认只支持26列，需要增加在数组中增加
    String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
    int index = 0;
    Row row = null;
    //循环输出slist  下拉框数组集合。
    for (int i = 0; i < slist.size(); i++) {
        String[] selectData = slist.get(i);
        if (selectData == null) {
            selectData = new String[0];
        }
        下拉框的索引位置
        int rowNum = indexs[i];
        //下拉框公式
        String strFormula = "'select'!$" + arr[index] + "$2:$" + arr[index] + "$" + (selectData.length + 1); //select第A1到A5000作为下拉列表来源数据
        select.setColumnWidth(i, 4000); //设置每列的列宽
        //设置下拉框数据校验
        data.addValidationData(ExcelUtil.setXSSFDataValidation(select, strFormula, 1, 50000, rowNum, rowNum)); 
//下拉列表元素很多的情况，循环下入到隐藏的sheet中
        for (int j = 0; j < selectData.length; j++) {
            if (index == 0) {
                row = select.createRow(j + 1); //创建数据行
                select.setColumnWidth(j + 1, 4000); //设置每列的列宽
                row.createCell(0).setCellValue(selectData[j]); //设置对应单元格的值

            } else {
                int rowCount = select.getLastRowNum();
                //判断最后一行数据的索引是否大于当前的行数，大于则需要创建一行，小于只需要创建单元格，赋值就好了。
                if (j < rowCount) {
                    select.getRow(j + 1).createCell(index).setCellValue(selectData[j]); //设置对应单元格的值
                } else {
                    select.setColumnWidth(j + 1, 4000); //设置每列的列宽
                    //创建行、创建列
                    select.createRow(j + 1).createCell(index).setCellValue(selectData[j]); //设置对应单元格的值
                }
            }
        }
        index++;
    }
    try {
        OutputStream outputStream = response.getOutputStream();// 打开流
        wb.write(outputStream);// HSSFWorkbook写入流
        //wb.close();// HSSFWorkbook关闭
        outputStream.flush();// 刷新流
        outputStream.close();// 关闭流
    } catch (Exception e) {
        e.printStackTrace();
    }

    return null;
}

 

 

ExcelUtil:setXSSFDataValidation

public static DataValidation setXSSFDataValidation(XSSFSheet sheet,String formula,
                                          int firstRow, int endRow, int firstCol, int endCol){
   CellRangeAddressList addressList = new CellRangeAddressList(
         firstRow, endRow, firstCol, endCol);
   DataValidationHelper dvHelper = sheet.getDataValidationHelper();
   XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
         dvHelper.createFormulaListConstraint(formula);
   XSSFDataValidation validation =(XSSFDataValidation)dvHelper.createValidation(
         dvConstraint, addressList);
   //sheet.addValidationData(validation);
   return validation;
}