spingboot实现导出excel文件功能

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、效果