我們不造輪子张惹，只是輪子的搬運(yùn)工息裸。（其實(shí)最好是造輪子蝇更，造比別人好的輪子）

開發(fā)中經(jīng)常會遇到excel的處理沪编，導(dǎo)入導(dǎo)出解析等等，java中比較流行的用poi簿寂，但是每次都要寫大段工具類來搞定這事兒漾抬，此處推薦一個別人造好的輪子【easypoi】，下面介紹下“輪子”的使用常遂。

pom引入

不再需要其他jar

       <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>

編寫實(shí)體類

此處注意必須要有空構(gòu)造函數(shù)纳令，否則會報錯“對象創(chuàng)建錯誤”
關(guān)于注解@Excel，其他還有@ExcelCollection克胳，@ExcelEntity 平绩，@ExcelIgnore，@ExcelTarget等漠另，此處我們用不到捏雌，可以去官方查看更多

import cn.afterturn.easypoi.excel.annotation.Excel;

import java.util.Date;

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 String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

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

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

導(dǎo)入導(dǎo)出公用方法

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;
    }

對的，沒看錯笆搓，這就可以導(dǎo)出導(dǎo)入了,看起來代碼挺多性湿，其實(shí)是提供了多個導(dǎo)入導(dǎo)出方法而已

測試

  @RequestMapping("export")
    public void export(HttpServletResponse response){

        //模擬從數(shù)據(jù)庫獲取需要導(dǎo)出的數(shù)據(jù)
        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);

        //導(dǎo)出操作
        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)導(dǎo)入
        System.out.println("導(dǎo)入數(shù)據(jù)一共【"+personList.size()+"】行");

        //TODO 保存數(shù)據(jù)庫
    }

導(dǎo)出結(jié)果

導(dǎo)出結(jié)果

測試導(dǎo)入

導(dǎo)出結(jié)果再添加一行满败，執(zhí)行肤频，輸出導(dǎo)入數(shù)據(jù)行數(shù)

導(dǎo)入結(jié)果

• 更多使用請參考官方網(wǎng)站