EXCEL导入导出

1、导入依赖

        <!-- 阿里开源EXCEL -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>1.1.2-beta5</version>
		</dependency>

2、Excel帮助类

package com.cmkj.plug.excel.util;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author wj
 * @date 2021/3/29
 * @describe 阿里开源EXCEL工具类
 */
public class ExcelUtil {

    /**
     * logger
     */
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.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行数据, 带样式
     * filePath 文件绝对路径
     * initSheet ：
     *      sheetNo: sheet页码，默认为1
     *      headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取
     *      clazz: 返回数据List<Object> 中Object的类名
     */
    public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet){
        if(!StringUtils.hasText(filePath)){
            return null;
        }

        sheet = sheet != null ? sheet : initSheet;

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

    /**
     * 读大于1000行数据
     * @param filePath 文件觉得路径
     * @return
     */
    public static List<Object> readMoreThan1000Row(String filePath){
        return readMoreThan1000RowBySheet(filePath,null);
    }

    /**
     * 读大于1000行数据, 带样式
     * @param filePath 文件觉得路径
     * @return
     */
    public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet){
        if(!StringUtils.hasText(filePath)){
            return null;
        }

        sheet = sheet != null ? sheet : initSheet;

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

    /**
     * 生成excle
     * @param filePath  绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx
     * @param data 数据源
     * @param head 表头
     */
    public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head){
        writeSimpleBySheet(filePath,data,head,null);
    }

    /**
     * 生成excle
     * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx
     * @param data 数据源
     * @param sheet excle页面样式
     * @param head 表头
     */
    public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet){
        sheet = (sheet != null) ? sheet : initSheet;

        if(head != null){
            List<List<String>> list = new ArrayList<>();
            head.forEach(h -> list.add(Collections.singletonList(h)));
            sheet.setHead(list);
        }

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write1(data,sheet);
        } catch (FileNotFoundException e) {
            log.error("找不到文件或文件路径错误, 文件：{}", filePath);
        }finally {
            try {
                if(writer != null){
                    writer.finish();
                }

                if(outputStream != null){
                    outputStream.close();
                }

            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因：{}", e);
            }
        }

    }

    /**
     * 生成excle
     * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx
     * @param data 数据源
     */
    public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data){
        writeWithTemplateAndSheet(filePath,data,null);
    }

    /**
     * 生成excle
     * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx
     * @param data 数据源
     * @param sheet excle页面样式
     */
    public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet){
        if(CollectionUtils.isEmpty(data)){
            return;
        }

        sheet = (sheet != null) ? sheet : initSheet;
        sheet.setClazz(data.get(0).getClass());

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write(data,sheet);
        } catch (FileNotFoundException e) {
            log.error("找不到文件或文件路径错误, 文件：{}", filePath);
        }finally {
            try {
                if(writer != null){
                    writer.finish();
                }

                if(outputStream != null){
                    outputStream.close();
                }
            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因：{}", e);
            }
        }

    }

    /**
     * 生成excle
     * @param outputStream 输出流
     * @param data 数据源
     */
    public static void writeWithTemplate(OutputStream outputStream, List<? extends BaseRowModel> data){
        writeWithTemplateAndSheet(outputStream,data,null);
    }

    /**
     * 生成excle
     * @param outputStreamOut 输出流
     * @param data 数据源
     * @param sheet excle页面样式
     */
    public static void writeWithTemplateAndSheet(OutputStream outputStreamOut, List<? extends BaseRowModel> data, Sheet sheet){
        if(CollectionUtils.isEmpty(data)){
            return;
        }

        sheet = (sheet != null) ? sheet : initSheet;
        sheet.setClazz(data.get(0).getClass());

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = outputStreamOut;
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write(data,sheet);
        } catch (Exception e) {
            log.error("excel文件导出失败, 失败原因：{}", e);
        }finally {
            try {
                if(writer != null){
                    writer.finish();
                }

                if(outputStream != null){
                    outputStream.close();
                }
            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因：{}", e);
            }
        }

    }

    /**
     * 生成多Sheet的excle
     * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx
     * @param multipleSheelPropetys
     */
    public static void writeWithMultipleSheel(String filePath,List<MultipleSheelPropety> multipleSheelPropetys){
        if(CollectionUtils.isEmpty(multipleSheelPropetys)){
            return;
        }

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {
                Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;
                if(!CollectionUtils.isEmpty(multipleSheelPropety.getData())){
                    sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());
                }
                writer.write(multipleSheelPropety.getData(), sheet);
            }

        } catch (FileNotFoundException e) {
            log.error("找不到文件或文件路径错误, 文件：{}", filePath);
        }finally {
            try {
                if(writer != null){
                    writer.finish();
                }

                if(outputStream != null){
                    outputStream.close();
                }
            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因：{}", e);
            }
        }

    }

    /*********************匿名内部类开始，可以提取出去******************************/

    @Data
    public static class MultipleSheelPropety{

        private List<? extends BaseRowModel> data;

        private Sheet sheet;
    }

    /**
     * 解析监听器，
     * 每解析一行会回调invoke()方法。
     * 整个excel解析结束会执行doAfterAllAnalysed()方法
     *
     * @author: chenmingjian
     * @date: 19-4-3 14:11
     */
    @Getter
    @Setter
    public static class ExcelListener extends AnalysisEventListener {

        private List<Object> datas = new ArrayList<>();

        /**
         * 逐行解析
         * object : 当前行的数据
         */
        @Override
        public void invoke(Object object, AnalysisContext context) {
            //当前行
            // context.getCurrentRowNum()
            if (object != null) {
                datas.add(object);
            }
        }

        /**
         * 解析完所有数据后会调用该方法
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            //解析结束销毁不用的资源
        }
    }

    /************************匿名内部类结束，可以提取出去***************************/

}

3、导入Excel

package com.cmkj.plug.excel.util;

import com.cmkj.plug.excel.config.ExcelImport;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wj
 * @date 2021/3/29
 * @describe 导入Excel文件
 */
public class ImportExcelTools {

    /**
     * 设置导入数据的最大行数
     */
    private static final int DEFAULT_COUNT = 20000;
    /**
     * 设置导入数据的起始行数
     */
    private static final int DEFAULT_START_LINE = 0;


    /**
     * 根据参数将导入excel数据转化为list
     *
     * @param in          excel 转换成的流
     * @param clazz       行对象
     * @param isExcel2003 是否2003版
     * @param startLine   从哪一行开始
     * @param maxCount    最大行数
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> convertSheetToList(InputStream in, Class<T> clazz, boolean isExcel2003,
                                                 int startLine, int maxCount) throws Exception {
        List<T> list = new ArrayList<T>();
        /** 根据版本选择创建Workbook的方式 */
        Workbook wb;
        if (isExcel2003) {
            wb = new HSSFWorkbook(in);
        } else {
            wb = new XSSFWorkbook(in);
        }
        if (null != wb) {
            /**获取第0个工作表格*/
            Sheet sheet = wb.getSheetAt(0);
            int count = sheet.getLastRowNum();
            if (maxCount == 0) {
                maxCount = DEFAULT_COUNT;
            }
            if (count > maxCount) {
                throw new Exception("导入失败，excel数据控制在" + maxCount + "条之内！");
            }
            /**遍历excel表格并将每一行中的数据转换成对象*/
            if (startLine < 0) {
                startLine = DEFAULT_START_LINE;
            }
            for (int i = startLine; i <= count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                T obj = convertLineToObj(clazz, row);
                if (obj == null) {
                    continue;
                }
                list.add(obj);
            }
        }
        return list;
    }


    /**
     * 将行数据转换成class（按注解转换）
     *
     * @param clazz
     * @param row
     * @param <T>
     * @return
     * @throws Exception
     */
    private static <T> T convertLineToObj(Class<T> clazz, Row row) throws Exception {
        T obj = clazz.newInstance();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            ExcelImport annotation = field.getAnnotation(ExcelImport.class);
            if (annotation != null && row.getLastCellNum() >= annotation.columnIndex()) {
                /**每行对应的单元格遍历*/
                Cell cell = row.getCell(annotation.columnIndex());
                if (cell == null)
                    throw new Exception("请使用正确的excel模板");
                field.setAccessible(true);
                field.set(obj, getCellValue(cell));
            }
        }
        return obj;
    }


    /**
     * 将cell 的数据格式化
     *
     * @param cell
     * @return
     */
    private static Object getCellValue(Cell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            Integer strs = (int) cell.getNumericCellValue();
            return strs.toString();
        }
        return String.valueOf(cell.getStringCellValue());
    }
}

4、Excel导出

package com.cmkj.plug.excel.util;

import com.cmkj.plug.excel.config.ExcelExport;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author wj
 * @date 2021/3/29
 * @describe Excel导出
 */
public class ExportExcelTools {

    /**
     * 定义输出流对象
     */
    private OutputStream outputStream;
    /**
     * 定义Workbook对象
     */
    private Workbook workbook;
    /**
     * 定义HSSFSheet
     */
    private HSSFSheet sheet;
    private int index;


    /**
     * 以流的形式初始化ExportExcelTools
     *
     * @param outputStream
     */
    public ExportExcelTools(OutputStream outputStream) {
        try {
            this.init(outputStream);
        } catch (Exception e) {
            System.out.println("导出excel出错" + e.getMessage());
        }
    }


    /**
     * 以流的形式初始化ExportExcelTools
     *
     * @param fileOutputStream
     */
    private void init(OutputStream fileOutputStream) {
        this.outputStream = fileOutputStream;
        this.workbook = new HSSFWorkbook();
        this.index = 0;
    }


    /**
     * 写文件头
     *
     * @param tClass
     * @return
     */
    public ExportExcelTools writeHead(Class<?> tClass) {
        if (tClass == null) {
            return this;
        }
        List<String> list = new ArrayList<String>();
        for (Field filed : getFields(tClass)) {
            ExcelExport excelExport = filed.getAnnotation(ExcelExport.class);
            list.add(excelExport.columnName());
        }
        createData(list);
        return this;
    }


    /**
     * 获取带有ExcelExport注解的属性
     *
     * @param tClass
     * @return
     */
    private List<Field> getFields(Class<?> tClass) {
        Field[] fields = tClass.getDeclaredFields();
        List<Field> list = new ArrayList<Field>(fields.length);
        /** 遍历tClass中的属性 */
        for (Field f : fields) {
            /** 有ExcelExport注解的信息属性保留 */
            if (f.isAnnotationPresent(ExcelExport.class)) {
                list.add(f);
            }
        }
        return list;
    }


    /**
     * 将数据写入到excel的sheet 中
     *
     * @param list
     */
    private void createData(List<String> list) {
        if (sheet == null) {
            sheet = (HSSFSheet) workbook.createSheet();
        }
        HSSFRow row = sheet.createRow(index++);
        HSSFCell[] cells = new HSSFCell[list.size()];
        for (int i = 0; i < cells.length; i++) {
            cells[i] = row.createCell(i);
            cells[i].setCellValue(list.get(i));
        }
    }


    /**
     * 写入动态表头到指定位置
     *
     * @param tClass
     * @param dynamicList
     * @param index
     * @return
     */
    public ExportExcelTools writeDyanmicHead(Class<?> tClass, List<String> dynamicList, int index) {
        if (tClass == null) {
            return this;
        }
        List<String> list = new ArrayList<String>();
        for (Field filed : getFields(tClass)) {
            ExcelExport excelExport = filed.getAnnotation(ExcelExport.class);
            list.add(excelExport.columnName());
        }
        list.addAll(list.size() - index, dynamicList);
        createData(list);
        return this;
    }


    /**
     * 写入列表数据
     *
     * @param list
     * @param <T>
     * @return
     */
    public <T> ExportExcelTools writeList(List<T> list) {
        if (list != null && list.size() > 0) {
            for (Object obj : list) {
                writeObject(obj);
            }
        }
        return this;
    }


    /**
     * 按行写入数据
     * 根据对象的属性类型做格式化
     * 将一行数据写入到excel 表格
     *
     * @param obj
     */
    private void writeObject(Object obj) {
        Class clazz = obj.getClass();
        List<String> list = new ArrayList<String>();
        for (Field f : getFields(clazz)) {
            f.setAccessible(true);
            try {
                Object o = f.get(obj);
                if (o instanceof Date) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    list.add(sdf.format(o));
                } else if (o instanceof BigDecimal) {
                    list.add(String.valueOf(((BigDecimal) o).setScale(2, BigDecimal.ROUND_CEILING)));
                } else {
                    if (null == o) {
                        list.add("");
                    } else {
                        list.add(String.valueOf(o));
                    }
                }
            } catch (IllegalAccessException e) {
                // e.printStackTrace();
                System.out.println("格式化obj失败" + e.getMessage());
            }
        }
        if (!list.isEmpty()) {
            createData(list);
        }
    }


    /**
     * 写入动态数据到excel指定位置
     *
     * @param list
     * @param dynamicList
     * @param flag
     * @param <T>
     * @return
     */
    public <T> ExportExcelTools writeDynamicList(List<T> list, List<List<String>> dynamicList, int flag) {
        int index = 0;
        if (list != null && list.size() > 0) {
            for (Object obj : list) {
                writeDynamicObject(obj, dynamicList.get(index), flag);
                index++;
            }
        }
        return this;
    }


    /**
     * 写入动态行数据
     *
     * @param obj
     * @param dynamicList
     * @param flag
     */
    private void writeDynamicObject(Object obj, List<String> dynamicList, int flag) {
        Class clazz = obj.getClass();
        List<String> list = new ArrayList<String>();
        for (Field f : getFields(clazz)) {
            f.setAccessible(true);
            try {
                Object o = f.get(obj);
                if (o instanceof Date) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    list.add(sdf.format(o));
                } else if (o instanceof BigDecimal) {
                    list.add(String.valueOf(((BigDecimal) o).setScale(2, BigDecimal.ROUND_CEILING)));
                } else {
                    if (null == o) {
                        list.add("");
                    } else {
                        list.add(String.valueOf(o));
                    }
                }
            } catch (IllegalAccessException e) {
                // e.printStackTrace();
                System.out.println("格式化obj失败" + e.getMessage());
            }
        }
        if (!list.isEmpty()) {
            list.addAll(list.size() - flag, dynamicList);
            createData(list);
        }
    }


    /**
     * 将内存的中信息输出到文件
     */
    public void exportData() {
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            // e.printStackTrace();
            System.out.println("创建excel失败" + e.getMessage());
        }
    }
}