java实现excel表格导出,带有下拉框

  org.apache.poi
     poi
     4.1.2
 
 
     org.apache.poi
     poi-ooxml
     4.1.2
 

    @PostMapping("/downloadAdsourceTemplate")
    public void downloadAdsourceTemplate(@RequestBody AdSourceVo vo,HttpServletResponse response) throws IOException {
        Workbook workbook = aggregateService.downloadAdsourceTemplate(vo);
        String excelName = "批量导入代码位模板.xlsx";
        response.setContentType("application/octet-stream");
        // 指定下载的文件名
        response.setHeader("content-Disposition", "attachment;filename=" + excelName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

@Override
public Workbook downloadAdsourceTemplate(AdSourceVo vo) {
    //声明一个工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    //表头行数据
    List header = new ArrayList<>();
    header.add("广告源");
    header.add("代码位");
    header.add("代码位名称");
    header.add("价格类型");
    header.add("价格");
    header.add("状态");

    //生成一个表格，设置表格名称
    XSSFSheet sheet = workbook.createSheet("批量导入代码位模板");
    //设置表格列宽度为30个字节
    sheet.setDefaultColumnWidth(30);
    //创建标题的显示样式
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.index);
    headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    headerStyle.setAlignment(HorizontalAlignment.CENTER);
    //创建第一行表头
    Row headrow = sheet.createRow(0);
    String[] heads = header.toArray(new String[0]);
    for (int i = 0; i < heads.length; i++) {
        Cell cell = headrow.createCell(i);
        cell.setCellValue(heads[i]);
        cell.setCellStyle(headerStyle);
    }

    
     Row row = sheet.createRow(1);
     Cell cell = row.createCell(0);
     cell.setCellValue("穿山甲");

     cell = row.createCell(1);
     cell.setCellValue("123456");

     cell = row.createCell(2);
     cell.setCellValue("测试代码位");

     cell = row.createCell(3);
     cell.setCellValue("固价");

     cell = row.createCell(4);
     cell.setCellValue("10");

     cell = row.createCell(5);
     cell.setCellValue("开启");

     cell = row.createCell(6);
     XSSFCellStyle cellStyle = workbook.createCellStyle();
     XSSFFont font = workbook.createFont();
     //设置字体颜色为红色
     font.setColor(IndexedColors.RED.getIndex());
     cellStyle.setFont(font);
     cell.setCellStyle(cellStyle);
     cell.setCellValue("示例数据,请勿操作!");

    
    Map platMap = logService.getPlatMap();
    String platName = platMap.get(String.valueOf(vo.getPlatId()));
    //下拉框数据
    List selecDatatArray = new ArrayList<>();

    String[] settleType = {"固价","竞价"}  ;
    String[] sts = {"开启","关闭"}  ;

    selecDatatArray.add(new String[]{platName});
    selecDatatArray.add(settleType);
    selecDatatArray.add(sts);
    //下拉框列位置
    List colNums = new ArrayList<>();
    colNums.add(0);
    colNums.add(3);
    colNums.add(5);


    
    for (int i = 0; i < selecDatatArray.size() ; i++){
        String[] selectData = selecDatatArray.get(i);
        Integer colNum = colNums.get(i);
        //设置下拉框位置  开始行 结束行  开始列 结束列

        DataValidationHelper helper=sheet.getDataValidationHelper();
        //获取需要生成下拉框的格   开始行 结束行  开始列 结束列
        CellRangeAddressList addressList = new CellRangeAddressList(1, 100, colNum, colNum);
        //设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(selectData);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        sheet.addValidationData(dataValidation);
    }

    return workbook;
}