java下载Excel文件并设置表头内容与下拉框

干货干货直接上代码

Maven依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

    /**
     * 下载班级信息Excel模板
     *
     * @return
     * @GetMapping
     */
    @GetMapping("/download")
    public R downloadClassExcel(String companyId, HttpServletRequest request, HttpServletResponse response) throws IOException {
        try {
            String[] title = {"届别", "学部", "年级", "班级名称", "班级代码", "班级排序"};
            // 1.创建Excel工作薄对象
            HSSFWorkbook wb = new HSSFWorkbook();
            // 2.创建Excel工作表对象
            HSSFSheet sheet = wb.createSheet("班级信息导入范本");
            // 3.创建Excel工作表的行
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < title.length; i++) {
                //6.设置Excel工作表的值
                row.createCell(i).setCellValue(title[i]);
            }
            List<String> periodList = new ArrayList<>();
            List<String> deptList = new ArrayList<>();
            List<String> gradeList = new ArrayList<>();
            List<SchoolPeriod> schoolPeriodList = schoolPeriodService.list(Wrappers.<SchoolPeriod>lambdaQuery().eq(SchoolPeriod::getCompanyId, companyId));
            List<DepartmentManage> departmentManageList = departmentManageService.list(Wrappers.<DepartmentManage>lambdaQuery().eq(DepartmentManage::getCompanyId, companyId));
            List<GradeManage> gradeManageList = gradeManageService.list(Wrappers.<GradeManage>lambdaQuery().eq(GradeManage::getCompanyId, companyId));

            // 【届别】
            if (null != schoolPeriodList && schoolPeriodList.size() > 0) {
                for (int i = 0; i < schoolPeriodList.size(); i++) {
                    periodList.add(schoolPeriodList.get(i).getName());
                }
            }
            // 【学部】
            if (null != departmentManageList && departmentManageList.size() > 0) {
                for (int i = 0; i < departmentManageList.size(); i++) {
                    deptList.add(departmentManageList.get(i).getDeptName());
                }
            }
            // 【年级】
            if (null != gradeManageList && gradeManageList.size() > 0) {
                for (int i = 0; i < gradeManageList.size(); i++) {
                    gradeList.add(gradeManageList.get(i).getGradeName());
                }
            }
            // 表格下拉【届别】
            if (periodList.size() > 0) {
                String[] periodArray = periodList.toArray(new String[periodList.size()]);
                AddComboBox("届别", wb, sheet, 1, 0, periodArray);
            }
            // 表格下拉【学部】
            if (deptList.size() > 0) {
                String[] deptArray = deptList.toArray(new String[deptList.size()]);
                AddComboBox("学部", wb, sheet, 1, 1, deptArray);
            }
            // 表格下拉【年级】
            if (gradeList.size() > 0) {
                String[] gradeArray = gradeList.toArray(new String[gradeList.size()]);
                AddComboBox("年级", wb, sheet, 1, 2, gradeArray);
            }
            // 设置sheet名称和单元格内容
            wb.setSheetName(0, "班级信息导入范本");
            try {
                //一个流 两个头
                //文件名称
                String filename = "班级信息导入范本.xls";
                response.setContentType("application/ms-excel");
                response.setCharacterEncoding("UTF-8");
                String encodedFileName = null;
                // 如果是IE,通过URLEncoder对filename进行UTF8编码。而其他的浏览器（firefox、chrome、safari、opera），则要通过字节转换成ISO8859-1。
                if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
                    encodedFileName = URLEncoder.encode(filename, "UTF-8");
                } else {
                    encodedFileName = new String(filename.getBytes("UTF-8"), "ISO8859-1");
                }
                response.setHeader("Content-Disposition", "attachment; filename=" + encodedFileName);//设置文件头编码方式和文件名
                OutputStream out = response.getOutputStream();
                wb.write(out);
                wb.close();
            } catch (Exception e) {
                R.failed("导出报错误" + e);
                e.printStackTrace();
            }
        } catch (Exception e) {
            R.failed("导出报错误" + e);
            e.printStackTrace();
        }
        return null;
    }



   /**
     * 下载的Excel表格中
     * 列表下拉选择框
     *
     * @param sheetName
     * @param workbook
     * @param sheet
     * @param rowIndex
     * @param colIndex
     * @param list
     */
    private void AddComboBox(String sheetName, HSSFWorkbook workbook, HSSFSheet sheet, int rowIndex, int colIndex, String[] list) {
        if (list.length > 10) {
            //数据源sheet页不显示
            HSSFSheet hidden = workbook.createSheet(sheetName);
            HSSFRow row = null;
            HSSFCell cell = null;
            for (int i = 0, length = list.length; i < length; i++) {
                row = hidden.createRow(i);
                cell = row.createCell(0);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(list[i]);
            }
            Name namedCell = workbook.createName();
            namedCell.setNameName(sheetName);
            String exp = sheetName + "!$A$1:$A$" + String.valueOf(list.length);
            namedCell.setRefersToFormula(exp);
            DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(sheetName);
            CellRangeAddressList addressList = new CellRangeAddressList(1, 100000, colIndex, colIndex);
            HSSFDataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
            sheet.addValidationData(validation);
        } else {
            CellRangeAddressList regions = new CellRangeAddressList(1, 100000, colIndex, colIndex);
            //生成下拉框内容
            DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);
            //绑定下拉框和作用区域
            HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint);
            //对sheet页生效
            sheet.addValidationData(data_validation);
        }
    }