springboot中开发导出excel文件功能，实现写入标题、写入表头、写入数据、写入下拉框功能

向右看齐 2024-04-01 12:06 64阅读 0赞

#### 效果 ####

![在这里插入图片描述][c90a8aa4734143b390a3e8d01d9b3d69.png]

#### 引入pom依赖 ####

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-spring-boot-starter</artifactId>
                <version>4.4.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.0</version>
            </dependency>

#### Controller层接口代码 ####

@ApiOperation("下载模板")
        @GetMapping("/downloadTemplate")
        public void downloadTemplate(HttpServletResponse response) {
        
            try {
        
                //1 在这里定义下拉框中的数据(不需要下拉框的话这块代码可以去掉，下面调用setExcelData工具类时不传这个shiftMarks 参数就可以了)  
                //手动定义下拉框中数据
                List<String> selects= new ArrayList<>();
                //selects.add("选项1");
                //selects.add("选项2");
                //selects.add("选项3");
                
                //2 在这里定义写入表中的数据(不需要写入数据的话这块代码可以去掉，下面调用setExcelData工具类时不传这个datas参数就可以了)  
                List<List<String>> datas = new ArrayList<>();
               	//List<String> list = new ArrayList<>();
                //list.add("数据1");
                //list.add("数据2");
                //list.add("数据3");
                //datas.add(list);
                
                //调用下面工具类中的方法
                HSSFWorkbook wb = checkDownloadExcel.setExcelData("2022-10",selects,datas);
                
                OutputStream output = null;
                String fileName = "文件名.xls";
                fileName = URLEncoder.encode(fileName, "UTF-8");
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName);
                // 服务端要在header设置Access-Control-Expose-Headers, 前端才能正常获取到
                response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
                output = response.getOutputStream();
                wb.write(output);
                output.flush();
                output.close();
                wb.close();
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }

#### checkDownloadExcel工具类 ####

/**
         * @Author: ljh
         * @Description:
         * @DateTime: 14:17 2022/10/29
         * @Params: dateTime  时间yyyy-MM
         * @Params: selectList 下拉框中的数据
         * @Params: dataList 写入表的数据
         * @Return
         */
        public HSSFWorkbook setExcelData(String dateTime, List<String> selectList, List<List<String>> dataList) {
        
            //1 在这里定义表头数据headerList，我这里是根据时间dateTime动态生成的表头信息
            List<String> headerList = new ArrayList();
            headerList.add("序号");
            headerList.add("姓名");
            headerList.add("手机号");
            headerList.add("部门");
            //1.1 获取当月日期
            List<String> months = DateTimeUtils.getDayByMonth(Integer.valueOf(dateTime.substring(0, dateTime.indexOf("-"))), Integer.valueOf(dateTime.substring(dateTime.indexOf("-") + 1, dateTime.length())));
            //1.2 获取当月星期
            for (String day : months) {
        
                headerList.add(day.substring(day.lastIndexOf("-") + 1, day.length()) + "日/" + DateTimeUtils.dateToWeek(day));
            }
            String[] headers = headerList.toArray(new String[headerList.size()]);
    
    
            //2 下拉框数据
            String[] selects = selectList.toArray(new String[selectList.size()]);
    
            return setExcelStyle(headers, selects, dataList, dateTime);
    
        }
    
    
        /**
         * @Author: ljh
         * @Description: 设置样式和写入数据
         * @DateTime: 14:17 2022/10/29
         * @Params: headers表头数据  selects下拉框数据  datas写入的真实数据  date时间
         * @Return
         */
        public HSSFWorkbook setExcelStyle(String[] headers, String[] selects, List<List<String>> datas, String date) {
        
            //1 创建工作薄
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("排版设置");
    
            //2 设置标题样式
            HSSFCellStyle titleStyle = wb.createCellStyle();
            titleStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
            Font titleFont = wb.createFont();
            titleFont.setFontHeightInPoints((short) 15);//字体大小
            titleFont.setFontName("黑体");//字体样式
            titleStyle.setFont(titleFont);
    
            //3 设置表头样式
            HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setBorderBottom(BorderStyle.THIN);
            headerStyle.setBorderLeft(BorderStyle.THIN);
            headerStyle.setBorderRight(BorderStyle.THIN);
            headerStyle.setBorderTop(BorderStyle.THIN);
            Font headerFont = wb.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setFontName("黑体");
            headerStyle.setFont(headerFont);
    
            //6 指定合并开始行、合并结束行 合并开始列、合并结束列
            CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, headers.length - 1);
            //添加要合并地址到表格
            sheet.addMergedRegion(rangeAddress);
    
            //7 添加标题和标题样式
            HSSFRow row0 = sheet.createRow((int) 0);
            row0.setHeightInPoints(40);
            HSSFCell cell0 = row0.createCell(0);
            String year = date.substring(0, date.indexOf("-"));
            String month = date.substring(date.indexOf("-") + 1);
            cell0.setCellValue("出勤情况统计表（" + year + "年" + month + "月）");
            cell0.setCellStyle(titleStyle);
    
            //在excel表中添加表头
            HSSFRow row = sheet.createRow((int) 1);
            for (int j = 0; j < headers.length; j++) {
        
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(headers[j]);
                cell.setCellStyle(headerStyle);
            }
    
            //9 数据样式  如不需要写入数据这块代码可以去掉
            HSSFCellStyle dataStyle = wb.createCellStyle();
            //设置居中样式，水平居中
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
    
            //写入数据  如不需要写入数据这块代码可以去掉
            int index = 2;
            for (int i = 0; i < datas.size(); i++) {
        
                // 默认的行数从0开始，为了统一格式设置从2开始，就是从excel的第三行开始
                row = sheet.createRow(index);
                index++;
                List<String> data = datas.get(i);
                for (int j = 0; j < data.size(); j++) {
        
                    Cell cell = row.createCell(j);
                    // 为当前列赋值
                    cell.setCellValue(data.get(j));
                    //设置数据的样式
                    cell.setCellStyle(dataStyle);
                }
            }
    
            //设置下拉框 如不需要设置下拉框这块代码可以去掉
            if(datas.size() == 0){
        
                setHSSFValidation(sheet, selects, 2, 100, 4, headers.length - 1);
            }else{
        
                setHSSFValidation(sheet, selects, 2, datas.size()+1, 4, headers.length - 1);
            }
    
    
            return wb;
        }
    
    
        /**
         * 设置某些列的值只能输入预制的数据,显示下拉框
         *
         * @param sheet    HSSFSheet sheetlist = wb.createSheet("XXXX");工作表对象
         * @param textlist 下拉框内容
         * @param firstRow 添加下拉框对应开始行
         * @param endRow   添加下拉框对应结束行
         * @param firstCol 添加下拉框对应开始列
         * @param endCol   添加下拉框对应结束列
         * @return
         */
        private HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        
            //这里将下拉框进行拆分存储在每一个单元格  并进行隐藏  防止下拉框数据过多存储在一个单元格的字符数大于255而报错
            HSSFWorkbook workbook = sheet.getWorkbook();
            Sheet hidden = workbook.createSheet("hidden");
            Cell cell = null;
            for (int i = 0, length = textlist.length; i < length; i++) {
        
                String name = textlist[i];
                Row roww = hidden.createRow(i);
                cell = roww.createCell(0);
                cell.setCellValue(name);
            }
    
            Name namedCell = workbook.createName();
            namedCell.setNameName("hidden");
            namedCell.setRefersToFormula("hidden!$A$1:$A$" + textlist.length);
            //将第二个sheet页设置为隐藏
            workbook.setSheetHidden(1, true);
            //将名称为hidden的数据进行加载
            DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
            //设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
            sheet.addValidationData(data_validation_list);
            return sheet;
        }

[c90a8aa4734143b390a3e8d01d9b3d69.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/868344daa7684fbaae762b7be336c560.png