Springboot整合EasyExcel做Excel的导出

Springboot整合EasyExcel做Excel的导出

easyexcel 简介

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
-简化了开发量，能够用更少的代码实现更多的功能。
-使用简单。
-能够使用更少的内存占用

java代码实现

POM依赖

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

实体类格式

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

import java.io.Serializable;

@Data
public class User extends BaseRowModel implements Serializable {
    //通过 @ExcelProperty 注解与 index 变量可以标注成员变量所映射的列
    //通过@ColumnWidth设置Excel中每一列的默认列宽
    @ColumnWidth(30)
    @ExcelProperty(value = "序号", index = 0)
    private int id;
    @ExcelProperty(value = "姓名", index = 1)
    private String name;
    @ExcelProperty(value = "性别", index = 2)
    private String sex;
    @ExcelProperty(value = "年龄", index = 3)
    private int age;
    @ExcelProperty(value = "地址", index = 4)
    private String address;
    @ExcelProperty(value = "联系方式", index = 5)
    private String phone;

    public User(int id, String name, String sex, int age, String address, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.address = address;
        this.phone = phone;
    }
}

具体的使用

//返回void  导包自己导一下即可
public void export(HttpServletResponse response){
	try {
		ExcelWriter writer = EasyExcelFactory.getWriter(response.getOutputStream());
		// 写仅有一个 Sheet 的 Excel 文件
		//User是要导出的类型的实体类
		Sheet sheet1 = new Sheet(1, 0, User.class);
		sheet1.setSheetName("Sheet1");
		// 写数据到 Writer 上下文中
		//userList是要导出的list数据---可去自己库里查
		/*
		//测试使用
		List userList = new ArrayList();
        for(int i = 0;i<5;i++) {
            User user = new User(i,"name"+i,"sex"+i,i,"address"+i,"phone"+i);
            userList.add(user);
        }*/
		writer.write(userList, sheet1);

		// 将上下文中的最终 outputStream 写入到指定文件中
		response.setContentType("application/octet-stream");
		String fileName = user.getName() + ".xls";
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
		response.flushBuffer();
		writer.finish();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

如果报错EasyExcel与poi冲突 则可使用

<exclusion.>来忽略自带的pom版本，重新导入自己需要的pom依赖的版本

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
            <exclusions>
                <exclusion>
                    <groupId>javax.servlet</groupId>
                    <artifactId>servlet-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.0</version>
        </dependency>