我們不造輪子，只是輪子的搬運工。（其實最好是造輪子摊册，造比別人好的輪子）

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

1、 在pom.xml中加入依賴

<!--excel操作-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>3.3.0</version>
</dependency>

或者引入下面的依賴：

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

2重斑、編寫實體類

此處注意必須要有空構(gòu)造函數(shù)兵睛，否則會報錯“對象創(chuàng)建錯誤”
關(guān)于注解@Excel，其他還有@ExcelCollection窥浪，@ExcelEntity 祖很，@ExcelIgnore，@ExcelTarget等漾脂，此處我們用不到突琳，可以去官方查看更多

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;

@Data
@EqualsAndHashCode(callSuper = false)
public class PersonExportVo implements Serializable {

    private static final long serialVersionUID = 1L;
    /**
     * 姓名
     */
    @Excel(name = "姓名", orderNum = "0", width = 15)
    private String name;
    
    /**
     * 登錄用戶名
     */
    @Excel(name = "用戶名", orderNum = "1", width = 15)
    private String username;

    @Excel(name = "手機號碼", orderNum = "2", width = 15)
    private String phoneNumber;

    /**
     * 人臉圖片
     */
    @Excel(name = "人臉圖片", orderNum = "3", width = 15, height = 30, type = 2)
    private String imageUrl;
}

3、導(dǎo)入導(dǎo)出公用工具類

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

public class ExcelUtils {
    /**
     * excel 導(dǎo)出
     *
     * @param list           數(shù)據(jù)
     * @param title          標題
     * @param sheetName      sheet名稱
     * @param pojoClass      pojo類型
     * @param fileName       文件名稱
     * @param isCreateHeader 是否創(chuàng)建表頭
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 導(dǎo)出
     *
     * @param list      數(shù)據(jù)
     * @param title     標題
     * @param sheetName sheet名稱
     * @param pojoClass pojo類型
     * @param fileName  文件名稱
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * excel 導(dǎo)出
     *
     * @param list         數(shù)據(jù)
     * @param pojoClass    pojo類型
     * @param fileName     文件名稱
     * @param response
     * @param exportParams 導(dǎo)出參數(shù)
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 導(dǎo)出
     *
     * @param list     數(shù)據(jù)
     * @param fileName 文件名稱
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 默認的 excel 導(dǎo)出
     *
     * @param list         數(shù)據(jù)
     * @param pojoClass    pojo類型
     * @param fileName     文件名稱
     * @param response
     * @param exportParams 導(dǎo)出參數(shù)
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 默認的 excel 導(dǎo)出
     *
     * @param list     數(shù)據(jù)
     * @param fileName 文件名稱
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下載
     *
     * @param fileName 文件名稱
     * @param response
     * @param workbook excel數(shù)據(jù)
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 導(dǎo)入
     *
     * @param filePath   excel文件路徑
     * @param titleRows  標題行
     * @param headerRows 表頭行
     * @param pojoClass  pojo類型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能為空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 導(dǎo)入
     *
     * @param file      excel文件
     * @param pojoClass pojo類型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 導(dǎo)入
     *
     * @param file       excel文件
     * @param titleRows  標題行
     * @param headerRows 表頭行
     * @param pojoClass  pojo類型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass);
    }

    /**
     * excel 導(dǎo)入
     *
     * @param file       上傳的文件
     * @param titleRows  標題行
     * @param headerRows 表頭行
     * @param needVerfiy 是否檢驗excel內(nèi)容
     * @param pojoClass  pojo類型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 導(dǎo)入
     *
     * @param inputStream 文件輸入流
     * @param titleRows   標題行
     * @param headerRows  表頭行
     * @param needVerfiy  是否檢驗excel內(nèi)容
     * @param pojoClass   pojo類型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        params.setNeedVerfiy(needVerfiy);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能為空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * Excel 類型枚舉
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }
}

4符相、測試

導(dǎo)出：

/**
 * 導(dǎo)出
 *
 * @param response
 */
@RequestMapping(value = "/export", method = RequestMethod.GET)
public void exportExcel(HttpServletResponse response) {
    long start = System.currentTimeMillis();
    List<MonitorPersonExportVo> personList = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        MonitorPersonExportVo personVo = new MonitorPersonExportVo();
        personVo.setName("張三" + i);
        personVo.setUsername("張三" + i);
        personVo.setPhoneNumber("18888888888");
        personVo.setImageUrl(Constant.DEFAULT_IMAGE);
        personList.add(personVo);
    }
    log.debug("導(dǎo)出excel所花時間：" + (System.currentTimeMillis() - start));
    ExcelUtils.exportExcel(personList, "員工信息表", "員工信息", MonitorPersonExportVo.class, "員工信息", response);
}

1.png

導(dǎo)入：

/**
 * 導(dǎo)入
 *
 * @param file
 */
@RequestMapping(value = "/import", method = RequestMethod.POST)
public ResultView importExcel(@RequestParam("file") MultipartFile file) {
    long start = System.currentTimeMillis();
    List<MonitorPersonImportVo> personVoList = ExcelUtils.importExcel(file, MonitorPersonImportVo.class);
    log.debug(personVoList.toString());
    log.debug("導(dǎo)入excel所花時間：" + (System.currentTimeMillis() - start));
    return getResponse(true, "導(dǎo)入成功");
}

5、注意：

SpringBoot中蠢琳，如果excel圖片文件路徑指向了SpringBoot的資源文件啊终，那么當(dāng)項目打成jar包后，easypoi將無法讀取到該資源文件傲须。

解決辦法：

重寫easypoi的IFileLoader接口的實現(xiàn)類：

原代碼實現(xiàn)方式：

/**
 * Copyright 2013-2015 JueYue (qrb.jueyue@gmail.com)
 * <p>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.afterturn.easypoi.cache.manager;

import cn.afterturn.easypoi.util.PoiPublicUtil;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.net.URL;
import java.net.URLConnection;

/**
 * 文件加載類,根據(jù)路徑加載指定文件
 *
 * @author JueYue
 *         2014年2月10日
 * @version 1.0
 */
public class FileLoaderImpl implements IFileLoader {

    private static final Logger LOGGER = LoggerFactory.getLogger(FileLoaderImpl.class);

    @Override
    public byte[] getFile(String url) {
        InputStream fileis = null;
        ByteArrayOutputStream baos = null;
        try {

            //判斷是否是網(wǎng)絡(luò)地址
            if (url.startsWith("http")) {
                URL urlObj = new URL(url);
                URLConnection urlConnection = urlObj.openConnection();
                urlConnection.setConnectTimeout(30);
                urlConnection.setReadTimeout(60);
                urlConnection.setDoInput(true);
                fileis = urlConnection.getInputStream();
            } else {
                //先用絕對路徑查詢,再查詢相對路徑
                try {
                    fileis = new FileInputStream(url);
                } catch (FileNotFoundException e) {
                    //獲取項目文件
                    fileis = FileLoaderImpl.class.getClassLoader().getResourceAsStream(url);
                }
            }

            baos = new ByteArrayOutputStream();
            byte[] buffer = new byte[1024];
            int len;
            while ((len = fileis.read(buffer)) > -1) {
                baos.write(buffer, 0, len);
            }
            baos.flush();
            return baos.toByteArray();
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(fileis);
            IOUtils.closeQuietly(baos);
        }
        LOGGER.error(fileis + "這個路徑文件沒有找到,請查詢");
        return null;
    }
}

修改后的代碼：

import cn.afterturn.easypoi.cache.manager.IFileLoader;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;

/**
 * 文件加載類,根據(jù)路徑加載指定文件
 *
 * @author user
 * @date 2018/12/18
 */
public class FileLoaderImpl implements IFileLoader {

    private static final Logger LOGGER = LoggerFactory.getLogger(cn.afterturn.easypoi.cache.manager.FileLoaderImpl.class);

    @Override
    public byte[] getFile(String url) {
        InputStream fileis = null;
        ByteArrayOutputStream baos = null;
        try {

            //判斷是否是網(wǎng)絡(luò)地址
            if (url.startsWith("http")) {
                URL urlObj = new URL(url);
                URLConnection urlConnection = urlObj.openConnection();
                urlConnection.setConnectTimeout(30);
                urlConnection.setReadTimeout(60);
                urlConnection.setDoInput(true);
                fileis = urlConnection.getInputStream();
            } else {
                //先用絕對路徑查詢,再查詢相對路徑
                try {
                    fileis = new FileInputStream(url);
                } catch (FileNotFoundException e) {
                    //獲取項目文件
                    fileis = FileLoaderImpl.class.getClassLoader().getResourceAsStream(url);
                    if (fileis == null) {
                        fileis = FileLoaderImpl.class.getResourceAsStream(url);
                    }
                }
            }
            baos = new ByteArrayOutputStream();
            byte[] buffer = new byte[1024];
            int len;
            while ((len = fileis.read(buffer)) > -1) {
                baos.write(buffer, 0, len);
            }
            baos.flush();
            return baos.toByteArray();
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(fileis);
            IOUtils.closeQuietly(baos);
        }
        LOGGER.error(fileis + "這個路徑文件沒有找到,請查詢");
        return null;
    }
}

在原代碼中加入了一下代碼蓝牲，就能讀取到文件了：

if (fileis == null) {
    fileis = FileLoaderImpl.class.getResourceAsStream(url);
}

最后通過作者提供的POICacheManager的靜態(tài)方法進行設(shè)置：

public static void setFileLoder(IFileLoader fileLoder) {
    POICacheManager.fileLoder = fileLoder;
}

/**
 * 一次線程有效
 * @param fileLoder
 */
public static void setFileLoderOnce(IFileLoader fileLoder) {
    if (fileLoder != null) {
        LOCAL_FILELOADER.set(fileLoder);
    }
}

寫一個監(jiān)聽器，監(jiān)聽項目啟動時泰讽，對POICacheManager進行全局替換：

import cn.afterturn.easypoi.cache.manager.POICacheManager;
import com.***.**.framework.common.util.FileLoaderImpl;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.stereotype.Component;

@Component
public class ExcelListener implements ApplicationListener<ApplicationReadyEvent> {

    @Override
    public void onApplicationEvent(ApplicationReadyEvent event) {
        POICacheManager.setFileLoader(new FileLoaderImpl());
    }
}

源碼地址：https://gitee.com/cnovel/demo