1
2
3
4
5
6
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

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
package com.exceltest.demo.domain;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;

import java.util.Date;

@Data
public class Person {

    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    @ExcelProperty(value = "年龄", index = 1)
    private Integer age;

    @ExcelProperty(value = "性别", index = 2)
    private String sex;

    @DateTimeFormat("yyyy-MM-dd")
    @ExcelProperty(value = "生日", index = 3)
    private Date birthday;

    // 忽略此字段
    @ExcelIgnore
    private String address;

}

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
package com.exceltest.demo.utils;

import com.alibaba.excel.EasyExcel;
import com.exceltest.demo.domain.Person;

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

public class ExcelUtil {


    /**2.*版本更新代码
     * excel的 pojo 实体类取消了 继承BaseRowModel.
     *
     */
    public static void writeExcel(HttpServletResponse response, List<?> list, String fileName, String sheetName)throws Exception  {
        EasyExcel.write(getOutputStream(fileName,response), Person.class)
                .sheet(sheetName)
                .doWrite(list);
    }
    /**
     * 导出文件时为Writer生成OutputStream
     * @param fileName
     * @param response
     * @return
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }
}

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
package com.exceltest.demo.controller;

import com.exceltest.demo.domain.Person;
import com.exceltest.demo.utils.ExcelUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

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

@Controller
@RequestMapping("/person")
public class PersonController {

    @RequestMapping("/hello")
    @ResponseBody
    public String hello(){
        return "hello person";
    }

    @RequestMapping("/exportTest")
    public void exportEnterpriseBillV2(HttpServletResponse response) throws Exception {
        //模模拟sql查出的数据
        Person person = new Person();
        person.setName("Micheal");
        person.setAge(20);
        person.setSex("男");
        person.setBirthday(new Date());
        person.setAddress("AAA");
        Person person2 = new Person();
        person2.setName("Bruce");
        person2.setAge(24);
        person2.setSex("男");
        person2.setBirthday(new Date());
        person2.setAddress("BBB");
        List<Person> list = new ArrayList<>();
        list.add(person);
        list.add(person2);
        ExcelUtil.writeExcel(response, list, "导出测试", "sheet单1");
    }
}