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;/*** @Author: 海绵宝宝* @Explain: easyExcel导入导出工具类* @DateTime: 2022/6/2 12:58* @Params:* @Return*/@Slf4jpublic class ExcelUtil {    public ExcelUtil() {    }    /**     * 导出execl     *     * @param response 响应体     * @param list     数据     * @param clazz    类     * @param      */    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(); }    }    /**     * 文件上传     * 
     * 1. 创建excel对应的实体对象     * 
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，     * 
     * 3. 直接读即可     */    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;/*** @Author: 海绵宝宝* @Explain: 菜单对应表* @DateTime: 2022/6/1 9:53* @Params:* @Return*/@Data@NoArgsConstructor@AllArgsConstructor@Builderpublic 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 objects = ExcelUtil.uploadExcel(file);     log.info("studnets:{}", objects); } catch (IOException e) {     e.printStackTrace(); }    }

开发者涨薪指南 48位大咖的思考法则、工作方式、逻辑体系彭州一中