SpringBoot导出Excel

1、数据库连接

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/demo?serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456

2、实体信息

@Data
@Table(name = "tb_user")
public class User {

    @Id
    private String id;

    private String name;

    private String password;

    private String phone;

    private String address;

    private String remark;

    private String salt;

}

3、Excel实体

@Data
public class Excel {

    @ExcelProperty(value = {"id"}, index = 0)
    private String id;

    @ExcelProperty(value = {"用户名"}, index = 1)
    private String name;

    @ExcelProperty(value = {"密码"}, index = 2)
    private String password;

    @ExcelProperty(value = {"手机号"}, index = 3)
    private String phone;

    @ExcelProperty(value = {"地址"}, index = 4)
    private String address;

    @ExcelProperty(value = {"备注"}, index = 5)
    private String remark;

    @ExcelProperty(value = {"密码盐值"}, index = 6)
    private String salt;
}

4、mapper

public interface UserMapper extends Mapper<User> {
}

5、service

1、从数据库查询数据

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;


    @Override
    public List<User> getAll() {
        List<User> users = userMapper.selectAll();
        return users;
    }
}

2、excel导出service

@Service
public class ExcelService {

    @Autowired
    private UserService userService;

    public void export(HttpServletResponse response) {
        List<User> all = userService.getAll();

        // 定义excel的页签
        List<Excel> excels = new ArrayList<>();

        // 字节数组输出流
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        // 构建写入对象
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

        // 这里可以多个WriteSheet对象
        WriteSheet user = EasyExcel.writerSheet(0, "user").head(Excel.class).build();

        // 这里可以写多个write
        excelWriter.write(all, user);

        // 写入完成
        excelWriter.finish();

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String format = simpleDateFormat.format(new Date());
        String fileName = format + "user.xlsx";

        try {
            outputStream.writeTo(response.getOutputStream());
            response.setContentType("application/octet-stream");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s\"; filename*=utf-8''%s", fileName, fileName));
            response.setContentLengthLong(outputStream.size());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}

6、controller

@RestController
@RequestMapping("export")
public class ExportController {
    @Autowired
    private ExcelService excelService;

    @PostMapping(value = "/excel")
    public String export(HttpServletResponse response) {
        excelService.export(response);
        return "success";
    }
}

7、pom.xml

1、主要配置文件

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.0.4</version>
        </dependency>

上图是导出结果