easyExcel2.2.6版本导入工具类，导出工具类，监听

最新推荐文章于 2024-03-15 11:02:50 发布

qzz12345

最新推荐文章于 2024-03-15 11:02:50 发布

阅读量225

点赞数

文章标签： java

本文链接：https://blog.csdn.net/qzz12345/article/details/132333248

版权

使用easyexcel方式解析数据的工具类（EasyExcel）：

package org.jeecg.modules.mes.utils;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;

import javax.mail.Message;
import java.lang.reflect.Field;
import java.util.*;

/**
 * @Description 使用easyexcel方式解析数据的工具类
 * @author qingyun
 * @Date 2021年5月19日 上午10:35:58
 */
@Component
public class EasyExcelUtils extends AnalysisEventListener<Object> {

    List<Object> list = new ArrayList<Object>();


    Class clazz;



    public EasyExcelUtils() {
        super();
        // TODO Auto-generated constructor stub
    }

    public EasyExcelUtils(Class clazz) {
        super();
        this.clazz = clazz;
    }

    /**
     * @Description invoke方法为一行一行读取excel内容
     * @author qingyun
     * @Date 2021年5月19日 上午10:43:17
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        list.add(data);
    }

    /**
     * @Description invokeHeadMap读取excel表头，校验表头是否正确
     * @author qingyun
     * @Date 2021年5月19日 上午10:44:43
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        Map<Integer, String> head = new HashMap<>();
        try {
            head = getIndexNameMap(clazz);   //通过class获取到使用@ExcelProperty注解配置的字段
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        Set<Integer> keySet = head.keySet();  //解析到的excel表头和实体配置的进行比对
        for (Integer key : keySet) {
            if (StringUtils.isEmpty(headMap.get(key))) {

                throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
            }
            if (!headMap.get(key).equals(head.get(key))) {

                throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
            }
        }
    }

    /**
     * @Description 读取完成之后进行的处理
     * @author qingyun
     * @Date 2021年5月19日 上午10:45:13
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }

    /**
     * @Description 通过class获取类字段信息
     * @author qingyun
     * @Date 2021年5月19日 下午1:41:47
     */
    public Map<Integer, String> getIndexNameMap(Class clazz) throws NoSuchFieldException {
        Map<Integer, String> result = new HashMap<>();
        Field field;
        Field[] fields = clazz.getDeclaredFields();     //获取类中所有的属性
        for (int i = 0; i < fields.length; i++) {
            field = clazz.getDeclaredField(fields[i].getName());
            field.setAccessible(true);
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);//获取根据注解的方式获取ExcelProperty修饰的字段
            if (excelProperty != null) {
                int index = excelProperty.index();         //索引值
                String[] values = excelProperty.value();   //字段值
                StringBuilder value = new StringBuilder();
                for (String v : values) {
                    value.append(v);
                }
                result.put(index, value.toString());
            }
        }
        return result;
    }

    /**
     * @Description 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     * @author qingyun
     * @Date 2021年5月19日 下午3:02:49
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
        }
    }

    public List<Object> getList() {
        return list;
    }

    public void setList(List<Object> list) {
        this.list = list;
    }

    public Message getMessage() {
        return null;
    }

    public void setMessage(Message message) {

    }




}

导出工具类（ExcelExportUtils）：

package org.jeecg.modules.mes.utils;


import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelExportUtils {
    private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtils.class);

    private static Sheet initSheet;

    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("sheet");
        // 设置自适应宽度
        initSheet.setAutoWidth(Boolean.TRUE);
    }

    /**
     * 读取少于1000行数据
     *
     * @param filePath 文件绝对路径
     * @return
     */
    public static List<Object> readLessThan1000Row(String filePath) {
        return readLessThan1000RowBySheet(filePath, null);
    }

    /**
     * 读取少于1000行数据，带样式的
     *
     * @param filePath 文件绝对路径
     * @param sheet
     * @return
     */
    public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
//        if (!StringUtils.hasText(filePath)) {
//            return null;
//        }

        sheet = sheet != null ? sheet : initSheet;

        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            return EasyExcelFactory.read(inputStream, sheet);
        } catch (FileNotFoundException e) {
            logger.error("找不到文件或者文件路径错误", e);
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.error("excel文件读取失败，失败原因：{}", e);
            }
        }
        return null;
    }

    /**
     * 读取大于1000行数据
     * @param filePath
     * @param sheet
     * @return
     */
    public static List<Object> readMoreThan1000Row(String filePath, Sheet sheet) {
        return readMoreThan1000RowBySheet(filePath, null);
    }

    /**
     * 读取大于1000行数据
     * @param filePath
     * @param sheet
     * @return
     */
    public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
//        if (!StringUtils.hasText(filePath)) {
//            return null;
//        }
        sheet = sheet != null ? sheet : initSheet;

        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            ExcelListener excelListener = new ExcelListener();
            EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
            return excelListener.getDatas();
        } catch (FileNotFoundException e) {
            logger.error("找不到文件或者文件路径错误");
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.error("excel文件读取失败，失败原因：{}", e);
            }
        }
        return null;
    }

    /**
     * 导出单个sheet(自定义模板)
     * @param response
     * @param dataList
     * @param
     * @param fileName
     * @throws UnsupportedEncodingException
     */
//    public static void writeExcelOneSheet(HttpServletResponse response, List<? extends BaseRowModel> dataList, String fileName,String template) throws UnsupportedEncodingException {
//
//        try {
//            String target = "attachment; filename=" + new String(
//                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
//            response.setContentType("multipart/form-data");
//            response.setCharacterEncoding("utf-8");
//            response.setContentType("application/vnd.ms-excel;charset=utf-8");
//            response.setHeader("Content-disposition", target);
//
//            ServletOutputStream out = response.getOutputStream();
//            //工作薄对象
//            ExcelWriter excelWriter = EasyExcel.write(out,SubjectReport.class).withTemplate(template).build();
//            //工作区对象
//            WriteSheet writeSheet = EasyExcel.writerSheet().build();
//            excelWriter.fill(dataList,writeSheet);
//            excelWriter.finish();
//        } catch (UnsupportedEncodingException e) {
//            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
//    }

    /**
     * 导出单个sheet（自动成模板,自定义路径）
     */
    public static void writeExcelOneSheet1(HttpServletResponse response, List<? extends BaseRowModel> dataList, Sheet sheet, String fileName,String path) throws UnsupportedEncodingException {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        // 如果sheet为空，则使用默认的
        if (null == sheet) {
            sheet = initSheet;
        }
        try {

            String value = "attachment; filename=" + new String(
                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", value);
            FileOutputStream out = new FileOutputStream(path);
            //ServletOutputStream out = response.getOutputStream(path);
            ExcelWriter writer = EasyExcelFactory.getWriter(out, ExcelTypeEnum.XLSX, true);
            // 设置属性类
            sheet.setClazz(dataList.get(0).getClass());
            writer.write(dataList, sheet);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            logger.error("导出失败，失败原因：{}", e);
        }
    }

    /**
     * 导出单个sheet（自动成模板,自定义路径）
     */
    public static void writeExcelOneSheet2(HttpServletResponse response, List<? extends BaseRowModel> dataList, Sheet sheet, String fileName) throws UnsupportedEncodingException {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        // 如果sheet为空，则使用默认的
        if (null == sheet) {
            sheet = initSheet;
        }
        try {

            String value = "attachment; filename=" + new String(
                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", value);
            //FileOutputStream out = new FileOutputStream(path);
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = EasyExcelFactory.getWriter(out, ExcelTypeEnum.XLSX, true);
            // 设置属性类
            sheet.setClazz(dataList.get(0).getClass());
            writer.write(dataList, sheet);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            logger.error("导出失败，失败原因：{}", e);
        }
    }
    /**
     * @Author lockie
     * @Description 导出excel 支持一张表导出多个sheet
     * @Param OutputStream 输出流
     * Map<String, List>  sheetName和每个sheet的数据
     * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
     * @Date 上午12:16 2019/1/31
     */
//    public static void writeExcelMutilSheet(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> dataList, String fileName,String template) throws UnsupportedEncodingException {
//        if (CollectionUtils.isEmpty(dataList)) {
//            return;
//        }
//        try {
//            String value = "attachment; filename=" + new String(
//                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
//            response.setContentType("multipart/form-data");
//            response.setCharacterEncoding("utf-8");
//            response.setContentType("application/vnd.ms-excel;charset=utf-8");
//            response.setHeader("Content-disposition", value);
//            ServletOutputStream out = response.getOutputStream();
//            //ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
//            ExcelWriter writer = EasyExcel.write(out, BiFinanceFeeDetail.class).withTemplate(template).build();
//            // 设置多个sheet
//            setMutilSheet(dataList, writer);
//            writer.finish();
//            out.flush();
//        } catch (IOException e) {
//            logger.error("导出异常", e);
//        }
//    }

    /**
     * @Author lockie
     * @Description 导出excel 支持一张表导出多个sheet
     * @Param OutputStream 输出流
     * Map<String, List>  sheetName和每个sheet的数据
     * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
     * @Date 上午12:16 2019/1/31
     */
    public static void writeExcelMutilSheetNew(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> dataList, String fileName) throws UnsupportedEncodingException {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        try {
            String value = "attachment; filename=" + new String(
                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", value);
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            //ExcelWriter writer = EasyExcel.write(out, BiFinanceFeeDetail.class).withTemplate(template).build();
            // 设置多个sheet
            setMutilSheet(dataList, writer);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            logger.error("导出异常", e);
        }
    }


    /**
     * @Author lockie
     * @Description //setSheet数据
     * @Date 上午12:39 2019/1/31
     */
    private static void setMutilSheet(Map<String, List<? extends BaseRowModel>> dataList, ExcelWriter writer) {
        int sheetNum = 1;
        for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : dataList.entrySet()) {
            Map columnWidth = new HashMap();
            columnWidth.put(0, 8000);
            Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
            sheet.setColumnWidthMap(columnWidth);
            sheet.setSheetName(stringListEntry.getKey());

            writer.write(stringListEntry.getValue(), sheet);
            sheetNum++;
        }
    }


    /**
     * test
     *
     * @param args
     * @param response
     * @throws UnsupportedEncodingException
     */
    public static void main(String[] args, HttpServletResponse response) throws UnsupportedEncodingException {
        // 导出多个sheet
//        List<OrderExportDTO> orderExportDTOList = new ArrayList<>();
//        Map<String, List<? extends BaseRowModel>> map = new HashMap<>();
//        map.put("自营订单", orderExportDTOList);
//        map.put("互联互通", orderExportDTOList);
//        String fileName = new String(("测试导出2019").getBytes(), "UTF-8");
//        writeExcelMutilSheet(response, map, fileName);

        // 导出单个sheet
//        writeExcelOneSheet(response, orderExportDTOList, null, fileName);
    }

}

导入工具类（ExcelImportUtils）：

package org.jeecg.modules.mes.utils;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;


public class ExcelImportUtil {
    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws Exception {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            reader.read(sheet);
        }
        return excelListener.getDatas();
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws Exception {
        return readExcel(excel, rowModel, sheetNo, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo,
                                         int headLineNum) throws Exception {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDatas();
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                                  String fileName, String sheetName, BaseRowModel object) {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        //创建本地文件
        String filePath = fileName + ".xlsx";
        File dbfFile = new File(filePath);
        try {
            if (!dbfFile.exists() || dbfFile.isDirectory()) {
                dbfFile.createNewFile();
            }
            fileName = new String(filePath.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel,
                                         ExcelListener excelListener) throws Exception {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new Exception("文件格式错误！");
        }
        InputStream inputStream;
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(inputStream, null, excelListener, true);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

}

监听工具类（ExcelListener）

package org.jeecg.modules.mes.utils;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;


public class ExcelListener extends AnalysisEventListener {
    //自定义用于暂时存储data。
    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<>();

    /**
     * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(Object object, AnalysisContext context) {
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        datas.add(object);
        //根据业务自行 do something
        doSomething();

        /*
        如数据过大，可以进行定量分批处理
        if(datas.size()<=100){
            datas.add(object);
        }else {
            doSomething();
            datas = new ArrayList<Object>();
        }
         */

    }

    /**
     * 根据业务自行实现该方法
     */
    private void doSomething() {
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        /*
            datas.clear();
            解析结束销毁不用的资源
         */
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }
}

StringUtil

package org.jeecg.modules.mes.utils;

import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author alan.zhaowm
 */
public class StringUtil {

    /**
     * 模板解析
     *
     * @param template   模板字符串 如：My name is ${name}, i am ${age} years old! ${sayHai}
     * @param properties map接口的集合，如： {name: "azi", age: 18, sex: "man"}
     * @return: 解析后的字符串，如： My name is azi, i am 18 years old!
     * @date: 2021/8/19 3:03
     */
    public static String parseTemplate(String template, Map properties) {
        if (template == null || template.isEmpty() || properties == null) {
            return template;
        }
        String r = "\\$\\{([^\\}]+)\\}";
        Pattern pattern = Pattern.compile(r);
        Matcher matcher = pattern.matcher(template);
        while (matcher.find()) {
            String group = matcher.group();
            Object o = properties.get(group.replaceAll(r, "$1"));
            if (o != null) {
                template = template.replace(group, String.valueOf(o));
            } else {
                template = template.replace(group, "");
            }
        }
        return template;
    }
}