快捷導(dǎo)航

easyexcel封裝工具類以及簡單使用方式

更新時間：2026年02月05日 14:43:41 作者：哀愁

文章介紹了如何使用封裝工具類、控制器和實(shí)體類進(jìn)行開發(fā),并通過Postman進(jìn)行測試,作者分享了個人經(jīng)驗(yàn),希望能對大家有所幫助

封裝工具類

package com.lk.util;

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 : lk
 * @date 2021/8/31 23:29
 **/
@Slf4j
public class ExcelUtil {


    public ExcelUtil() {
    }

    /**
     * 導(dǎo)出execl
     *
     * @param response 響應(yīng)體
     * @param list     數(shù)據(jù)
     * @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可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系
        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. 創(chuàng)建excel對應(yīng)的實(shí)體對象
     * <p>
     * 2. 由于默認(rèn)一行行的讀取excel，所以需要創(chuàng)建excel一行一行的回調(diào)監(jiān)聽器，
     * <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();
        // 背景設(shè)置為紅色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 設(shè)置字體
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteFont.setBold(false);
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 內(nèi)容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

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

        // 這個策略是 頭是頭的樣式 內(nèi)容是內(nèi)容的樣式 其他的策略可以自己實(shí)現(xiàn)
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
}

控制器

package com.lk.controller;

import com.lk.model.Student;
import com.lk.util.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * @author : lk
 * @date 2021/8/31 22:19
 **/
@RestController
@Slf4j
public class HelloController {
	// 導(dǎo)出模版
    @GetMapping("template")
    public void template(HttpServletResponse response) {

        ExcelUtil.downExcelUtil(response, new ArrayList<>(), Student.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();
        }
    }


}

實(shí)體類

package com.lk.model;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * @author : lk
 * @date 2021/8/31 22:19
 **/
@Data
public class Student {

    @ExcelProperty(value = "編號")
    private String id;

    @ExcelProperty(value = "學(xué)生姓名")
    private String name;

    @ExcelProperty(value = "年齡")
    private Integer age;
}