Java Excel导入导出功能实现

代码已经上传到我的Github，有兴趣的同学可以下载来看看：

地址：​​​https://github.com/ylw-github/EasyPoi-Demo​​​

我使用的是EasyPoi注解的功能实现的，实现了Excel导入导出的功能。当然如果想要详细了解EasyPoi的功能，可以查看​​ easypoi详细文档 ​​，先看看效果图：

下面直接贴上代码

1.所需要的依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.2.0</version>
</dependency>

2.构造实体类

public class Bill implements Serializable {

    @Excel(name = "序号",orderNum = "0")
    private int num;

    @Excel(name="工资",orderNum = "1")
    private double salary;

    @Excel(name="车费",orderNum = "2")
    private double carFare;

    @Excel(name="伙食费",orderNum = "3")
    private double eatFare;


    @Excel(name = "合计",groupName = "房租",orderNum = "4")
    private double total;
    @Excel(name = "水费",groupName = "房租",orderNum = "5")
    private double water;
    @Excel(name = "电费",groupName = "房租",orderNum = "6")
    private double eleCharge;
    @Excel(name = "物业费",groupName = "房租",orderNum = "7")
    private double manageFee;

    @Excel(name="备注",orderNum = "8")
    private String remark;

    public Bill() {
    }

    public Bill(int num, double salary, double carFare, double eatFare, double total, double water, double eleCharge, double manageFee, String remark) {
        this.num = num;
        this.salary = salary;
        this.carFare = carFare;
        this.eatFare = eatFare;
        this.total = total;
        this.water = water;
        this.eleCharge = eleCharge;
        this.manageFee = manageFee;
        this.remark = remark;
    }

    public int getNum() {
        return num;
    }

    public void setNum(int num) {
        this.num = num;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public double getCarFare() {
        return carFare;
    }

    public void setCarFare(double carFare) {
        this.carFare = carFare;
    }

    public double getEatFare() {
        return eatFare;
    }

    public void setEatFare(double eatFare) {
        this.eatFare = eatFare;
    }


    public double getTotal() {
        return total;
    }

    public void setTotal(double total) {
        this.total = total;
    }

    public double getWater() {
        return water;
    }

    public void setWater(double water) {
        this.water = water;
    }

    public double getEleCharge() {
        return eleCharge;
    }

    public void setEleCharge(double eleCharge) {
        this.eleCharge = eleCharge;
    }

    public double getManageFee() {
        return manageFee;
    }

    public void setManageFee(double manageFee) {
        this.manageFee = manageFee;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    @Override
    public String toString() {
        return "Bill{" +
                "num=" + num +
                ", salary=" + salary +
                ", carFare=" + carFare +
                ", eatFare=" + eatFare +
                ", total=" + total +
                ", water=" + water +
                ", eleCharge=" + eleCharge +
                ", manageFee=" + manageFee +
                ", remark='" + remark + '\'' +
                '}';
    }
}

3.Excel导出功能

@Test
public void exportExcel() throws Exception {
    List<Bill> billList = new ArrayList<>();
    billList.add(new Bill(1, 10000, 1000, 1500, 1200, 100, 200, 100, "余额充足"));
    billList.add(new Bill(2, 10000, 1200, 1000, 1200, 100, 200, 100, "余额不足"));
    billList.add(new Bill(3, 14000, 1300, 1200, 1200, 100, 200, 100, "无"));
    billList.add(new Bill(4, 14000, 1100, 1300, 1200, 100, 200, 100, "无"));
    billList.add(new Bill(5, 14000, 1200, 1400, 1200, 100, 200, 100, "无"));
    billList.add(new Bill(6, 14000, 1500, 1500, 1200, 100, 200, 100, "无"));
    billList.add(new Bill(7, 15000, 1800, 100, 1200, 100, 200, 100, "还贷"));
    billList.add(new Bill(8, 14000, 1200, 1400, 1200, 100, 200, 100, "无"));
    billList.add(new Bill(9, 14000, 1500, 1500, 1200, 100, 200, 100, "无"));
    billList.add(new Bill(10, 14000, 1200, 1400, 1200, 100, 200, 100, "无"));
    billList.add(new Bill(11, 14000, 1500, 1500, 1200, 100, 200, 100, "余额不足"));
    billList.add(new Bill(12, 14000, 1200, 1400, 1200, 100, 200, 100, "余额不足"));

    ExportParams params = new ExportParams();
    params.setTitle("年度账单");
    params.setSheetName("年度账单表");
    params.setType(ExcelType.XSSF);

    Workbook workbook = ExcelExportUtil.exportExcel(params, Bill.class, billList);
    FileOutputStream fileOutputStream = new FileOutputStream("Bill.xls");
    workbook.write(fileOutputStream);

}

4.Excel导入功能

@Test
public void importExcel() throws Exception {
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    params.setHeadRows(2);
    List<Bill> bills = ExcelImportUtil.importExcel(new File("Bill.xls"), Bill.class, params);
    for (Bill bill : bills) {
        System.out.println(bill.toString());
    }
}