使用easyexcel实现Excel的导入导出

maven

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta4</version>
        </dependency>

excel实体

public class ExcelPropertyUserModel extends BaseRowModel {
    @ExcelProperty(value = "姓名" ,index = 0)
    private String name;

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

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

    @ExcelProperty(value = "备注" ,index = 3)
    private String remarks;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getRemarks() {
        return remarks;
    }

    public void setRemarks(String remarks) {
        this.remarks = remarks;
    }
}

读取监听

public class ExcelListener extends AnalysisEventListener {

    public void invoke(Object o, AnalysisContext analysisContext) {
        ExcelPropertyUserModel userModel = (ExcelPropertyUserModel) o;
        System.out.println(userModel.getName());
    }

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

读取导出演示

public class ExcelDemoApplication {

    /**导入Excel*/
    @Test
    public void testRead() throws Exception{
        InputStream inputStream = new FileInputStream(new File("D:\\test.xlsx"));
        try {
            // 解析每行结果在listener中处理
            AnalysisEventListener listener = new ExcelListener();

            ExcelReader excelReader = new ExcelReader(inputStream, null, listener);

            /**
             *
             * Sheet 构造函数参数
             * sheetNo  是第几个sheet  默认从1开始
             * headLineMun 表头占几行
             * 导入excel的实体地址
             */
            excelReader.read(new Sheet(1, 1, ExcelPropertyUserModel.class));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    private List<ExcelPropertyUserModel> getData(){
        List<ExcelPropertyUserModel> data = new ArrayList<ExcelPropertyUserModel>();
        ExcelPropertyUserModel userModel = new ExcelPropertyUserModel();
        userModel.setName("张大伟");
        userModel.setAge(27);
        userModel.setRemarks("不错哦");
        userModel.setSex("男");
        data.add(userModel);

        userModel = new ExcelPropertyUserModel();
        userModel.setName("小熊猫");
        userModel.setAge(29);
        userModel.setRemarks("国宝啊");
        userModel.setSex("女");

        data.add(userModel);

        return data;
    }

    /**
     * 导出 excel
     * @throws FileNotFoundException
     */
    @Test
    public void testWrite() throws FileNotFoundException {
        OutputStream out = new FileOutputStream("C:\\Users\\zhangqiushi\\Desktop\\test.xlsx");
        try {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
            Sheet sheet1 = new Sheet(1, 0,ExcelPropertyUserModel.class);
            writer.write(getData(), sheet1);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}

浏览器下载

 //添加响应头信息
response.setHeader("Content-disposition", "attachment; filename=" + "catagory.xls");
 response.setContentType("application/msexcel;charset=UTF-8");//设置类型
response.setHeader("Pragma", "No-cache");//设置头
response.setHeader("Cache-Control", "no-cache");//设置头
response.setDateHeader("Expires", 0);//设置日期