Poi 使用其他页做为下拉数据源

最新推荐文章于 2023-09-26 10:00:14 发布

原创最新推荐文章于 2023-09-26 10:00:14 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个用于下载学生课程安排模板的Java方法实现。该方法创建了一个Excel文件，并设置了教室名称、课程名称及学生学号等字段。此外，还实现了课程名称和教室名称的下拉列表功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

@RequestMapping("downLoadFile.do")
@ControllerLog(module="cdb-stuCourseInfo",methods="下载模版",description="下载模版")
public ResponseEntity<byte[]> downLoadFile() throws  Exception{
   try{
      HSSFWorkbook workbook = new HSSFWorkbook();
      HSSFSheet sheet = workbook.createSheet("sheet1");
      sheet.setColumnWidth(0,5000);
      sheet.setColumnWidth(1,4000);
      sheet.setColumnWidth(2,4000);

      HSSFRow hssfRow = sheet.createRow(0);

      HSSFCell cell0 = hssfRow.createCell(0);
      cell0.setCellValue("教室名称");

      HSSFCell cell1 = hssfRow.createCell(1);
      cell1.setCellValue("课程名称");

      HSSFCell cell2 = hssfRow.createCell(2);
      cell2.setCellValue("学生学号");

      Map<String, String> param = new HashMap<String, String>();
      param.put("dtCode", "subjectDict");
      List<SysDict> sysDictList = cdbStuCourseInfoService.findByNamedQuery("getSysDictList", param, SysDict.class);
      //设置下拉列表
      String[] subject = new String[sysDictList.size()];
      for(int i = 0; i< sysDictList.size();i++){
         subject[i] = sysDictList.get(i).getDictName();
      }

      CellRangeAddressList subjectRegions = new CellRangeAddressList(1, 2055, 1, 1);
      DVConstraint subjectConstraint = DVConstraint.createExplicitListConstraint(subject);
      HSSFDataValidation subjectDataValidation = new HSSFDataValidation(subjectRegions, subjectConstraint);
      sheet.addValidationData(subjectDataValidation);
      subjectDataValidation.setShowErrorBox(false);// 取消弹出错误框*/

      List<BdFieldRoom> fieldRoomList = bdSchoolInfoCommonService.getBdFieldRoomList("2");  //场室信息列表
      String[] fieldRooms = new String[fieldRoomList.size()];
      for(int i = 0; i< fieldRoomList.size();i++){
         fieldRooms[i] = fieldRoomList.get(i).getName();
      }

      HSSFSheet hidden = workbook.createSheet("hidden");
      HSSFCell cell = null;
      for (int i = 0;i < fieldRooms.length; i++) {
         HSSFRow row = hidden.createRow(i);
         cell = row.createCell(0);
         cell.setCellValue(fieldRooms[i]);
      }

      Name namedCell = workbook.createName();
      namedCell.setNameName("hidden");
      // namedCell.setRefersToFormula("hidden!A1:A" + countryName.length); 导致下拉选项越选越少
      //Offset（参照单元格，行偏移量，列偏移量，返回几行，返回几列）
      namedCell.setRefersToFormula("OFFSET('hidden'!$A$1,0,0,COUNTA('hidden'!$A:$A)- 1,1)");
      //加载数据,将名称为Sites的做为数据源
      DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

      // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
      CellRangeAddressList addressList = new CellRangeAddressList(1, 2550, 0, 0);
      HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);

      //将第二个sheet设置为隐藏
      workbook.setSheetHidden(1, true);
      sheet.addValidationData(validation);

      ByteArrayOutputStream os = new ByteArrayOutputStream();
      workbook.write(os);
      byte[] bytes = os.toByteArray();
      return this.getResponseEntity("学生课程安排.xls",bytes);
   }catch (Exception e){
      e.printStackTrace();
   }
   return null;
}
public ResponseEntity<byte[]> getResponseEntity(String title, byte[] content) throws UnsupportedEncodingException {
    if (IgBlankUtil.isNotBlank(title)) {
        HttpHeaders headers = new HttpHeaders();
        String fileName = new String(title.getBytes("GBK"), "iso-8859-1");//为了解决中文名称乱码问题
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<byte[]>(content, headers, HttpStatus.OK);
    }
    return null;
}