EasyExcel导入导出工具类

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel

1.导入Maven

        <!--EasyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.16</version>
        </dependency>

2.封装类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
* @Author: 海绵宝宝
* @Explain: easyExcel导入导出工具类
* @DateTime: 2022/6/2 12:58
* @Params:
* @Return
*/
@Slf4j
public class ExcelUtil {


    public ExcelUtil() {
    }

    /**
     * 导出execl
     *
     * @param response 响应体
     * @param list     数据
     * @param clazz    类
     * @param <T>
     */
    public static <T> void downExcelUtil(HttpServletResponse response, List<T> list, Class<T> clazz) {
        String fileName = System.currentTimeMillis() + "";
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        try {
            int i = 0;
            if (!list.isEmpty()) {
                EasyExcel.write(response.getOutputStream(), clazz)
                        .registerWriteHandler(createTableStyle())
                        .sheet("data" + (i++))
                        .doWrite(list);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 文件上传
     * <p>
     * 1. 创建excel对应的实体对象
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，
     * <p>
     * 3. 直接读即可
     */

    public static <T> List<T> uploadExcel(MultipartFile file) throws IOException {

        final List<T> rows = new ArrayList<T>();
        ReadListener readListener = new AnalysisEventListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext context) {
                rows.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("read {} rows", rows.size());
            }
        };
        EasyExcel.read(file.getInputStream(), null, readListener).sheet().doRead();
        return rows;

    }

    public static <T> List<T> uploadExcel(MultipartFile file, Class<T> tClass) throws IOException {
        final List<T> rowss = new ArrayList<T>();
        ReadListener readListener = new AnalysisEventListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext context) {
                rowss.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("read {} rows", rowss.size());
            }
        };
        EasyExcel.read(file.getInputStream(), tClass, readListener).sheet().doRead();
        return rowss;

    }

    private static WriteHandler createTableStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteFont.setBold(false);
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
}

3.实体类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @Author: 海绵宝宝
* @Explain: 菜单对应表
* @DateTime: 2022/6/1 9:53
* @Params:
* @Return
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Menus {
  @ExcelProperty(value = "菜单ID", index = 0,converter = TimestampConverter.class)
  private Integer id;
  @ExcelProperty(value = "菜单标题", index = 1,converter = TimestampConverter.class)
  private String menuTitle;
  @ExcelProperty(value = "菜单名", index = 2,converter = TimestampConverter.class)
  private String menuName;

}

4.调用示例

// 导出模版
    @GetMapping("/template")
    public void template(HttpServletResponse response) {
        Menus menus = new Menus();

        ArrayList<Menus> list = new ArrayList<>();

        for (int i=0;i<=3;i++){
            menus.setId(123);
            menus.setMenuTitle("123");
            menus.setMenuName("123");
            list.add(menus);
        }

        ExcelUtil.downExcelUtil(response, list, Menus.class);
    }
    // 上传文件
    @PostMapping("/upload")
    public void test02(@RequestParam("file") MultipartFile file) {

        try {
            List<Object> objects = ExcelUtil.uploadExcel(file);

            log.info("studnets:{}", objects);


        } catch (IOException e) {
            e.printStackTrace();
        }
    }

5.日期（Timestamp）转换器

package com.iet.ustb.project.system.util;


import java.sql.Timestamp;
import java.text.SimpleDateFormat;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* @Author: 海绵宝宝
* @Explain: Timestamp时间转换器（EasyExcel）
* @DateTime: 2022/7/12 16:04
* @Params:
* @Return
*/
public class TimestampConverter implements Converter<Timestamp> {

    @Override
    public Class<Timestamp> supportJavaTypeKey() {
        return Timestamp.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public WriteCellData<String> convertToExcelData(Timestamp timestamp, ExcelContentProperty contentProperty,
                                                    GlobalConfiguration globalConfiguration) throws Exception {
        return new WriteCellData<String>(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timestamp));
    }

}