EasyExcel导入导出工具类

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

1.导入Maven

        
        
            com.alibaba
            easyexcel
            2.2.3
        

        
            cn.hutool
            hutool-all
            5.7.16
        

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;


@Slf4j
public class ExcelUtil {


    public ExcelUtil() {
    }

    
    public static  void downExcelUtil(HttpServletResponse response, List list, Class 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();
        }
    }

    

    public static  List uploadExcel(MultipartFile file) throws IOException {

        final List rows = new ArrayList();
        ReadListener readListener = new AnalysisEventListener() {
            @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  List uploadExcel(MultipartFile file, Class tClass) throws IOException {
        final List rowss = new ArrayList();
        ReadListener readListener = new AnalysisEventListener() {
            @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;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Menus {
  @ExcelProperty(value = "菜单ID", index = 0)
  private Integer id;
  @ExcelProperty(value = "菜单标题", index = 1)
  private String menuTitle;
  @ExcelProperty(value = "菜单名", index = 2)
  private String menuName;

}

4.调用示例

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

        ArrayList 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