spingboot实现导出excel文件功能

柔光的暖阳◎ 2024-04-03 10:19 218阅读 0赞

#### 1、引入pom依赖 ####

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-spring-boot-starter</artifactId>
                <version>4.4.0</version>
            </dependency>

#### 2、导出功能代码 ####

/**
         * @Author: ljh
         * @Description: 功能接口
         * @DateTime: 16:07 2022/9/6
         * @Params:
         * @Return
         */
    	@ApiOperation("导出excel")
        @GetMapping("/exportToExcel")
        public void exportToExcel(HttpServletResponse response){
        
            try {
        
                //生成数据和excel样式
                Workbook wb = getExcelData();
               
                String fileName = "excel文件名.xlsx";
                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");
                
               	OutputStream output = response.getOutputStream();
                wb.write(output);
                output.flush();
                output.close();
                wb.close();
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * @Author: ljh
         * @Description: 生成数据
         * @DateTime: 16:07 2022/9/6
         * @Params:
         * @Return
         */
        public Workbook getExcelData() {
        
        	//表头数据
            List<String> headerList = new ArrayList();
            headerList.add("序号");
            headerList.add("日期");
            headerList.add("机构名称（部门/分中心）");
            headerList.add("姓名");
            headerList.add("工号");
            String[] arr = headerList.toArray(new String[headerList.size()]);
    
            //2 具体数据 需要和表头一一对应
            List totalList = new ArrayList();
            totalList.add("1");
            totalList.add("2022-10-10");
            totalList.add("研发部");
            totalList.add("张三");
            totalList.add("12345678");
            dataList.add(totalList);
            
            //设置excel样式
            return writeToExcelByList(arr, dataList);
        }
    
    
        /**
        * @Author: ljh
        * @Description: 设置excel样式
        * @DateTime: 16:59 2022/9/27
        * @Params:
        * @Return
        */
        public Workbook writeToExcelByList(String[] array, List<List> list) {
        
    
            //1 创建工作薄
            Workbook wb = new XSSFWorkbook();
    
            //2 设置标题样式
            CellStyle titleStyle = wb.createCellStyle();
            titleStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
            titleStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());//设置背景色
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//必须设置 否则无效
            titleStyle.setBorderBottom(BorderStyle.THIN);//设置边框
            titleStyle.setBorderLeft(BorderStyle.THIN);//设置边框
            titleStyle.setBorderRight(BorderStyle.THIN);//设置边框
            titleStyle.setBorderTop(BorderStyle.THIN);//设置边框
            Font titleFont = wb.createFont();
            titleFont.setFontHeightInPoints((short) 15);//字体大小
            titleFont.setFontName("黑体");//字体样式
            titleStyle.setFont(titleFont);
    
            //3 设置表头样式 代码含义同标题样式
            CellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            Font headerFont = wb.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setFontName("黑体");
            headerStyle.setBorderBottom(BorderStyle.THIN);
            headerStyle.setBorderLeft(BorderStyle.THIN);
            headerStyle.setBorderRight(BorderStyle.THIN);
            headerStyle.setBorderTop(BorderStyle.THIN);
            headerStyle.setFont(headerFont);
    
            //4 创建sheet
            Sheet sheet = wb.createSheet("sheet名");
    
            //5 设置单元格宽度  参数1表示列（下标从0开始） 参数2表示宽度（必须乘256）
            sheet.setColumnWidth(1,15*256);
            sheet.setColumnWidth(2,25*256);
    
            //6 指定合并开始行、合并结束行 合并开始列、合并结束列
            CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, array.length-1);
            //添加要合并地址到表格
            sheet.addMergedRegion(rangeAddress);
    
            //7 添加标题和标题样式
            Row row0 = sheet.createRow((int) 0);
            row0.setHeightInPoints(40);
            Cell cell0 = row0.createCell(0);
            cell0.setCellValue("标题名");
            cell0.setCellStyle(titleStyle);
    
            //8 在sheet中添加表头   因为第0行是标题 所以表头行数从1开始
            Row row = sheet.createRow((int) 1);
            for (int i = 0; i < array.length; i++) {
        
                Cell cell = row.createCell(i);
                cell.setCellValue(array[i]);
                cell.setCellStyle(headerStyle);
            }
    
            //9 数据样式 因为标题和数据样式不同 需要分开设置 不然会覆盖
            CellStyle dataStyle = wb.createCellStyle();
            //设置居中样式，水平居中
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setBorderTop(BorderStyle.THIN);
    
            //10 数据从序号2开始
            try {
        
                int index = 2;
                for (int i = 0;  i < list.size(); i++) {
        
                    // 默认的行数从0开始，为了统一格式设置从2开始，就是从excel的第三行开始
                    row = sheet.createRow(index);
                    index++;
                    List data = list.get(i);
                       for (int j = 0; j < data.size(); j++) {
        
                            row.setHeightInPoints(30);
                            Cell cell = row.createCell(j);
                            // 为当前列赋值
                            cell.setCellValue(data.get(j).toString());
                            //设置数据的样式
                            cell.setCellStyle(finalStyle);
                       }
                }
            } catch (Exception e) {
        
                e.printStackTrace();
            }
            return wb;
        }

#### 3、效果 ####

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

[e49547319c1842819a379fe6475ed8bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/65dbac7c1ad84a39a08f20b65699345c.png