Spring Boot中使用Easypoi

pom文件引入以下依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.1.0</version>
</dependency>

或者引入下面的依赖

<!-- easy-poi -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.1.0</version>
</dependency>

四种导出excel的用法示例

直接将List<Map<String, Object>>数据导出为excel示例(无需模板)

/**
 * 直接导出(无需模板) 注:此方式存在一些不足之处,在对性能、excel要求比较严格时不推荐使用
 * 
 * @throws IOException
 */
@Test
public void directExportExcel() throws IOException {
    // Map作为每一行的数据容器,List作为行的容器
    List<Map<String, Object>> rowDataList = new ArrayList<>();
    // 每个ExcelExportEntity存放Map行数据的key
    List<ExcelExportEntity> keyList = new ArrayList<>();
    Map<String, Object> aRowMap;
    final int COMMON_KEY_INDEX = 10;
    for (int i = 0; i < 5; i++) {
        // 一个Map对应一行数据(如果需要导出多行数据,那么需要多个Map)
        aRowMap = new HashMap<>(16);
        for (int j = 0; j < COMMON_KEY_INDEX; j++) {
            String key = j + "";
            aRowMap.put(key, "坐标(" + i + "," + j + ")");
        }
        rowDataList.add(aRowMap);
        // 同一列对应的cell,在从Map里面取值时,会共用同一个key
        // 因此ExcelExportEntity的个数要保持和列数做多的行 的map.size()大小一致
        if (i == 0) {
            ExcelExportEntity excelExportEntity;
            for (int j = 0; j < COMMON_KEY_INDEX; j++) {
                excelExportEntity = new ExcelExportEntity();
                excelExportEntity.setKey(j + "");
                // 设置cell宽
                excelExportEntity.setWidth(15D);
                // 设置cell是否自动换行
                excelExportEntity.setWrap(true);
                keyList.add(excelExportEntity);
            }
        }
    }
    // excel总体设置
    ExportParams exportParams = new ExportParams();
    // 不需要标题
    exportParams.setCreateHeadRows(false);
    // 指定sheet名字
    exportParams.setSheetName("直接导出数据测试");
    // 生成workbook 并导出
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, keyList, rowDataList);
    File savefile = new File("E:\\temp\\easypoi");
    if (!savefile.exists()) {
        boolean result = savefile.mkdirs();
        System.out.println("目录不存在,创建" + result);
    }
    FileOutputStream fos = new FileOutputStream("E:\\temp\\easypoi\\坐标.xls");
    workbook.write(fos);
    fos.close();
}

通过注解,直接将Object(集合)数据导出为excel示例(无需模板)

/**
 * 对象---直接导出(无需模板) 注:如果模型 的父类的属性也有@Excel注解,那么导出excel时,会连该模型的父类的属性也一会儿导出
 * 
 * @throws IOException
 */
@Test
public void directExportExcelByObject() throws IOException {
    List<Student> list = new ArrayList<>(16);
    Student student;
    Random random = new Random();
    for (int i = 0; i < 10; i++) {
        student = new Student(i + "", "name" + i, random.nextInt(2), random.nextInt(100), new Date(),
                "className" + i);
        student.setSchoolName("学校名称" + i);
        student.setSchoolAddress("学校地址" + i);
        list.add(student);
    }
    ExportParams exportParams = new ExportParams();
    exportParams.setSheetName("我是sheet名字");
    // 生成workbook 并导出
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);
    File savefile = new File("E:/temp/easypoi");
    if (!savefile.exists()) {
        boolean result = savefile.mkdirs();
        System.out.println("目录不存在,创建" + result);
    }
    FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/学生.xls");
    workbook.write(fos);
    fos.close();
}

使用模板将Map<String, Object>数据导出为excel示例(需要模板)

// 模板导出---Map组装数据
// 注:.xls的模板可以导出.xls文件,也可以导出xlsx的文件;同样的, .xlsx的模板可以导出.xls文件,也可以导出xlsx的文件;
@Test
public void templateExportExcelByMap() throws IOException {
    // 加载模板
    TemplateExportParams params = new TemplateExportParams("templates/templateMap.xls");
    Map<String, Object> map = new HashMap<>(16);
    map.put("title", "全亚洲,最帅气人员名单");
    map.put("date", "2018-12-05");
    map.put("interviewer", "JustryDeng");
    List<Map<String, Object>> list = new ArrayList<>(16);
    Map<String, Object> tempMap;
    for (int i = 0; i < 5; i++) {
        tempMap = new HashMap<>();
        tempMap.put("name", "邓沙利文");
        tempMap.put("gender", new Random().nextInt(2) == 0 ? "男" : "女");
        tempMap.put("age", new Random().nextInt(90) + 11);
        tempMap.put("hobby", "活的,女的!!!");
        tempMap.put("handsomeValue", "100分(满分100分)");
        tempMap.put("motto", "之所以只帅到了全亚洲,是因为其他地方审美不同!");
        list.add(tempMap);
    }
    map.put("dataList", list);
    // 生成workbook 并导出
    Workbook workbook = ExcelExportUtil.exportExcel(params, map);
    File savefile = new File("E:/temp/easypoi");
    if (!savefile.exists()) {
        boolean result = savefile.mkdirs();
        System.out.println("目录不存在,进行创建,创建" + (result ? "成功!" : "失败!"));
    }
    FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/采访结果.xls");
    workbook.write(fos);
    fos.close();
}

使用模板将Object数据导出为excel示例(需要模板)

// 模板导出---对象组装数据
// 注:实际上仍然是"模板导出---Map组装数据",不过这里借助了工具类,将对象先转换为了Map<String, Object>
// 注:.xls的模板可以导出.xls文件,也可以导出xlsx的文件;同样的, .xlsx的模板可以导出.xls文件,也可以导出xlsx的文件;
@Test
public void templateExportExcelByObject() throws IOException, IllegalAccessException {
    // 加载模板
    TemplateExportParams params = new TemplateExportParams("templates/templateObject.xlsx");
    // 组装数据
    InterviewResult interviewResult = new InterviewResult();
    interviewResult.setTitle("全亚洲最帅人员名单");
    interviewResult.setInterviewer("邓沙利文");
    interviewResult.setDate("2018-12-05");
    List<HandsomeBoy> list = new ArrayList<>(8);
    interviewResult.setList(list);
    HandsomeBoy handsomeBoy;
    for (int i = 0; i < 5; i++) {
        handsomeBoy = new HandsomeBoy();
        handsomeBoy.setAge(20 + i);
        handsomeBoy.setGender(i % 2 == 0 ? "女" : "男");
        handsomeBoy.setHandsomeValue(95 + i + "(满分100分)");
        handsomeBoy.setHobby("女。。。。");
        handsomeBoy.setMotto("我是一只小小小小鸟~");
        handsomeBoy.setName("JustryDeng");
        list.add(handsomeBoy);
    }
    // 生成workbook 并导出
    Workbook workbook = ExcelExportUtil.exportExcel(params, objectToMap(interviewResult));
    File savefile = new File("E:/temp/easypoi");
    if (!savefile.exists()) {
        boolean result = savefile.mkdirs();
        System.out.println("目录不存在,进行创建,创建" + (result ? "成功!" : "失败!"));
    }
    FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/采访结果.xlsx");
    workbook.write(fos);
    fos.close();
}
/**
 * 对象转换为Map<String, Object>的工具类
 * 
 * @param obj 要转换的对象
 * @return
 * @throws IllegalAccessException
 */
private static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
    Map<String, Object> map = new HashMap<>(16);
    Class<?> clazz = obj.getClass();
    for (Field field : clazz.getDeclaredFields()) {
        field.setAccessible(true);
        String fieldName = field.getName();
        Object value = field.get(obj);
        map.put(fieldName, value);
    }
    return map;
}

模板标签语法

ExcelUtils导入导出excel

package cn.com.javakf.easypoi.utils;

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;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

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;

public class ExcelUtils {

    /**
     * excel 导出
     *
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     * @param isCreateHeader 是否创建表头
     * @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 导出
     *
     * @param list      数据
     * @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 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams,
            HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response)
            throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    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 导出
     *
     * @param list     数据
     * @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数据
     */
    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 导入
     *
     * @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 导入
     *
     * @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 导入
     *
     * @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 导入
     *
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerfiy 是否检验excel内容
     * @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 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerfiy  是否检验excel内容
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows,
            boolean needVerify, 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.setNeedVerify(needVerify);
        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;
        }
    }

}
package cn.com.javakf.easypoi.controller;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import cn.com.javakf.easypoi.model.Person;
import cn.com.javakf.easypoi.utils.ExcelUtils;

@RestController
@RequestMapping("excel")
public class ExcelController {
    /**
     * 导出
     * 
     * @param response
     * @throws IOException
     */
    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public void exportExcel(HttpServletResponse response) throws IOException {
        List<Person> personList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            Person person = new Person();
            person.setName("张三" + i);
            person.setUsername("张三" + i);
            person.setPhoneNumber("18888888888");
            person.setImageUrl("/static/person.jpg");
            personList.add(person);
        }
        ExcelUtils.exportExcel(personList, "员工信息", "员工信息sheet", Person.class, "员工信息表", response);
    }

    /**
     * 导入
     * 
     * @param file
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "/import", method = RequestMethod.POST)
    public Object importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        long start = System.currentTimeMillis();
        List<Person> personList = ExcelUtils.importExcel(file, Person.class);
        System.out.println("导入excel所花时间:" + (System.currentTimeMillis() - start) + "'ms");
        return personList;
    }
}
package cn.com.javakf.easypoi.model;

import java.io.Serializable;

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

@Data
@EqualsAndHashCode(callSuper = false)
public class Person 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;

}

处理导出时图片路径问题

package cn.com.javakf.easypoi.listener;

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

import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.afterturn.easypoi.cache.manager.IFileLoader;

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 {

            // 判断是否是网络地址
            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;
    }

}
package cn.com.javakf.easypoi.listener;

import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.stereotype.Component;

import cn.afterturn.easypoi.cache.manager.POICacheManager;

@Component
public class ExcelListener implements ApplicationListener<ApplicationReadyEvent> {

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

}

注:
通过模板导只能以xls为结尾,xlsx为结尾时不能正常打开。
不用模板xls,xlsx都能正常打开,通过ExcelType设置,ExcelType.HSSF:xls ExcelType.XSSF:xlsx。


原文:https://www.jianshu.com/p/47e7f137bafe
作者: 问题_解决_分享_讨论_最优