【springboot+easypoi】一行代码搞定excel导入导出

easypoi 官方api

 

pom引入

<dependency>
　　<groupid>cn.afterturn</groupid>
　　<artifactid>easypoi-base</artifactid>
　　<version>3.0.3</version>
</dependency>
<dependency>
　　<groupid>cn.afterturn</groupid>
　　<artifactid>easypoi-web</artifactid>
　　<version>3.0.3</version>
</dependency>
<dependency>
　　<groupid>cn.afterturn</groupid>
　　<artifactid>easypoi-annotation</artifactid>
　　<version>3.0.3</version>
</dependency>

 

 

实体类

@data
    public class person {

        @excel(name = "姓名", ordernum = "0")
        private string name;
    
        @excel(name = "性别", replace = {"男_1", "女_2"}, ordernum = "1")
        private string sex;
    
        @excel(name = "生日", exportformat = "yyyy-mm-dd", ordernum = "2")
        private date birthday;
    
        public person(string name, string sex, date birthday) {
            this.name = name;
            this.sex = sex;
            this.birthday = birthday;
        }
    }

 

 

导入导出公共方法

public static void exportexcel(list<?> list, string title, string sheetname, class<?> pojoclass,string filename,boolean iscreateheader, httpservletresponse response){
        exportparams exportparams = new exportparams(title, sheetname);
        exportparams.setcreateheadrows(iscreateheader);
        defaultexport(list, pojoclass, filename, response, exportparams);

    }
    public static void exportexcel(list<?> list, string title, string sheetname, class<?> pojoclass,string filename, httpservletresponse response){
        defaultexport(list, pojoclass, filename, response, new exportparams(title, sheetname));
    }
    public static void exportexcel(list<map<string, object>> list, string filename, httpservletresponse response){
        defaultexport(list, filename, response);
    }

    private static void defaultexport(list<?> list, class<?> pojoclass, string filename, httpservletresponse response, exportparams exportparams) {
        workbook workbook = excelexportutil.exportexcel(exportparams,pojoclass,list);
        if (workbook != null);
        downloadexcel(filename, response, workbook);
    }

    private static void downloadexcel(string filename, httpservletresponse response, workbook workbook) {
        try {
            response.setcharacterencoding("utf-8");
            response.setheader("content-type", "application/vnd.ms-excel");
            response.setheader("content-disposition",
            "attachment;filename=" + urlencoder.encode(filename, "utf-8"));
            workbook.write(response.getoutputstream());
        } catch (ioexception e) {
            throw new normalexception(e.getmessage());
        }
    }
    private static void defaultexport(list<map<string, object>> list, string filename, httpservletresponse response) {
        workbook workbook = excelexportutil.exportexcel(list, exceltype.hssf);
        if (workbook != null);
        downloadexcel(filename, response, workbook);
    }

    public static <t> list<t> importexcel(string filepath,integer titlerows,integer headerrows, class<t> pojoclass){
        if (stringutils.isblank(filepath)){
            return null;
        }
        importparams params = new importparams();
        params.settitlerows(titlerows);
        params.setheadrows(headerrows);
        list<t> list = null;
        try {
            list = excelimportutil.importexcel(new file(filepath), pojoclass, params);
        }catch (nosuchelementexception e){
            throw new normalexception("模板不能为空");
        } catch (exception e) {
            e.printstacktrace();
        throw new normalexception(e.getmessage());
        }
        return list;
    }
    
    public static <t> list<t> importexcel(multipartfile file, integer titlerows, integer headerrows, class<t> pojoclass){
        if (file == null){
            return null;
        }
        importparams params = new importparams();
        
        params.settitlerows(titlerows);
        params.setheadrows(headerrows);
        list<t> list = null;
        try {
            list = excelimportutil.importexcel(file.getinputstream(), pojoclass, params);
        }catch (nosuchelementexception e){
            throw new normalexception("excel文件不能为空");
        } catch (exception e) {
            throw new normalexception(e.getmessage());
        }
        return list;
    }

 

测试

@requestmapping("export")
    public void export(httpservletresponse response){

        //模拟从数据库获取需要导出的数据
        list<person> personlist = new arraylist<>();
        person person1 = new person("路飞","1",new date());
        person person2 = new person("娜美","2", dateutils.adddate(new date(),3));
        person person3 = new person("索隆","1", dateutils.adddate(new date(),10));
        person person4 = new person("小狸猫","1", dateutils.adddate(new date(),-10));
        personlist.add(person1);
        personlist.add(person2);
        personlist.add(person3);
        personlist.add(person4);
    
        //导出操作
        fileutil.exportexcel(personlist,"花名册","草帽一伙",person.class,"海贼王.xls",response);
    }

    @requestmapping("importexcel")
    public void importexcel(){
        string filepath = "f:\\海贼王.xls";
        //解析excel，
        list<person> personlist = fileutil.importexcel(filepath,1,1,person.class);
        //也可以使用multipartfile,使用 fileutil.importexcel(multipartfile file, integer titlerows, integer headerrows, class<t> pojoclass)导入
        system.out.println("导入数据一共【"+personlist.size()+"】行");
        //todo 保存数据库
    }

 

————————————————

原文链接：https://blog.csdn.net/zhefudexiaojiahuo/article/details/83586498