java导出数据到excel表中

环境说明

jdk1.8，springboot2.5.3

项目结构

1.controller层

package com.example.pdf.controller;

import com.example.pdf.service.ExportDataToExcelService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;


@RestController
@RequestMapping(value = "export")
public class ExportDataToExcelController {

    @Resource
    private ExportDataToExcelService exportDataToExcelService;

    @GetMapping("dataToExcel")
    public void dataToExcel(HttpServletResponse response) {
        exportDataToExcelService.dataToExcel(response);

    }
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.service层

package com.example.pdf.service;

import javax.servlet.http.HttpServletResponse;

public interface ExportDataToExcelService {
    /**
     * 导出数据到excel表中
     *
     * @param response
     */
    void dataToExcel(HttpServletResponse response);
}

1
2
3
4
5
6
7
8
9
10
11
12
13

3.实现层

package com.example.pdf.service.impl;

import com.example.pdf.model.ProductModel;
import com.example.pdf.service.ExportDataToExcelService;
import com.example.utils.ExcelUtil;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExportDataToExcelServiceImpl implements ExportDataToExcelService {
    /**
     * 导出数据到excel表中
     *
     * @param response
     */
    @Override
    public void dataToExcel(HttpServletResponse response) {

        List<ProductModel> models = new ArrayList<>();

        // 模拟5条数据
        ProductModel p1 = new ProductModel("矿泉水", 2, "瓶");
        ProductModel p2 = new ProductModel("凤梨", 4, "个");
        ProductModel p3 = new ProductModel("笔记本", 1, "台");
        ProductModel p4 = new ProductModel("球鞋", 1, "双");
        ProductModel p5 = new ProductModel("超跑", 10, "辆");

        // 将模拟数据存入集合
        models.add(p1);
        models.add(p2);
        models.add(p3);
        models.add(p4);
        models.add(p5);

        try {
            // 导出数据
            ExcelUtil.export(response, ProductModel.class, models, "商品列表", ExcelUtil.getStyleStrategy());
        } catch (Exception e) {
            System.out.println("商品列表导出异常！");
        }
    }
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

4.工具类：ExcelUtil.java

package com.example.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@Component
public class ExcelUtil {

    /**
     * 导出excel
     *
     * @param response
     * @param head
     * @param data
     * @param sheetName
     * @param horizontalCellStyleStrategy
     */
    public static void export(HttpServletResponse response, Class head, List data, String sheetName, HorizontalCellStyleStrategy horizontalCellStyleStrategy) throws IOException {
        //给定导出实体类
        EasyExcel.write(response.getOutputStream(), head)
                //给定工作表名称
                .sheet(sheetName)
                //给定样式
                .registerWriteHandler(horizontalCellStyleStrategy)
                //给定导出数据
                .doWrite(data);
    }

    /**
     * 设置生成excel样式 去除默认表头样式及设置内容居中，如有必要可重载该方法给定参数配置不同样式
     *
     * @return HorizontalCellStyleStrategy
     */
    public static HorizontalCellStyleStrategy getStyleStrategy() {
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置单元格边框
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(false);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //头策略使用默认
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

5.ProductModel.java类

package com.example.pdf.model;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

@Data
public class ProductModel {

    public ProductModel(String productName, Integer number, String unit) {
        this.productName = productName;
        this.number = number;
        this.unit = unit;
    }

    @ColumnWidth(15)
    @ExcelProperty("商品名称")
    private String productName;

    @ColumnWidth(15)
    @ExcelProperty("商品数量")
    private Integer number;

    @ColumnWidth(15)
    @ExcelProperty("商品单位")
    private String unit;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

使用的Maven依赖

<!-- lombok插件 -->
        <dependency>
            <artifactId>lombok</artifactId>
            <groupId>org.projectlombok</groupId>
            <scope>provided</scope>
            <version>1.18.10</version>
        </dependency>
        
<!-- alibaba Excel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

postman请求展示，返回内容需要前端接收

浏览器接收说明（如果下载下来的为zip类型，记得将后缀改为：xlsx，再打开）

更改后再下载

查看文件