java实现excel导入导出

ExcelKit

简单、好用且轻量级的海量Excel文件导入导出解决方案

1. 引入依赖

<dependency>
    <groupId>com.wuwenze</groupId>
    <artifactId>ExcelKit</artifactId>
    <version>2.0.72</version>
</dependency>

2. 配置Excel与实体之间的映射关系

@Data
@Excel("user")
@Table(name = "tb_user")
public class User {
    @Id
    @ExcelField(value = "编号", width = 50)
    private Long id;

    @ExcelField(value = "用户名", width = 100)
    private String username;

    @ExcelField(value = "昵称", width = 100)
    private String nickname;

    @ExcelField(value = "邮箱", width = 150)
    private String email;

    @ExcelField(value = "电话号码", width = 100)
    private String phoneNumber;

    private Integer status;

    @ExcelField(value = "创建时间", dateFormat = "yyyy年MM月dd日 HH:mm:ss", width = 180)
    private Date createTime;

    @ExcelField(value = "修改时间", dateFormat = "yyyy年MM月dd日 HH:mm:ss",width = 180)
    private Date modifiedTime;

    @ExcelField(//
            value = "性别",
            readConverterExp = "男=1,女=0",
            writeConverterExp = "1=男,0=女"
            ,width = 50
    )
    private Integer sex;

    @ExcelField(value = "密码盐值", width = 100)
    private String salt;

    @ExcelField(//
            value = "用户类型",
            readConverterExp = "超级管理员=0,普通用户=1",
            writeConverterExp = "0=超级管理员,1=普通用户"
            ,width = 80
    )
    private Integer type;

    @ExcelField(value = "用户密码", width = 100)
    private String password;

    @ExcelField(value = "出生日期", dateFormat = "yyyy/MM/dd",width = 100)
    private Date birth;

    private Long departmentId;

    @ExcelField(value = "头像url", width = 200)
    private String avatar;

3. 一行代码构建 Excel 导入模板

    /**
     * 导出excel
     * @param response
     */
    @ApiOperation(value = "导出excel", notes = "导出所有用户的excel表格")
    @PostMapping("/excel")
    @RequiresPermissions("user:export")
    @ControllerEndpoint(exceptionMessage = "导出Excel失败",operation = "导出用户excel")
    public void export(HttpServletResponse response) {
        //查询所有用户
        List<User> users = this.userService.findAll();
        ExcelKit.$Export(User.class, response).downXlsx(users, false);
    }

4. 测试页面

5. 点击导出按钮，生成xls文件

EasyExcel

alibaba开源的excle工具类也非常好用： https://github.com/alibaba/easyexcel

1. 引入依赖：

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

2. 实体类的创建：

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

3. 测试类：我是将xls文件生成在项目下面的，路径随便放~

public class EasyTest {

    String PATH = "E:\\code\\javaSE\\";

    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

    //写入
    public void indexWrite() {
        String fileName = PATH + "EasyTest.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }

    //读取
    public void indexRead() {
        String fileName = PATH + "EasyTest.xlsx";
        // 这里默认读取第一个sheet
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

    public static void main(String[] args) {
        EasyTest easyTest = new EasyTest();
        easyTest.indexWrite();
    }
}

4. 打开文件：