列表导出Excel表格文件

斧头虽小，但多劈几次，就能将坚硬的树木伐倒

记录列表数据导出：

1、Controller层：

package com.common.excel.demo;

import java.io.IOException;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

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.multipart.MultipartFile;

import com.common.excel.ExcelUtils;

/**
 * {@link ExcelUtils} demo
 *
 * @author loveLetter 
 * Created on 2023-05-16
 */
public class ExcelUtilsDemoController {

    /**
     * 用户基础数据下载
     *
     * @param response
     * @throws IOException
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        // 模拟获取用户数据
        ExcelUtilsDemoUserInfo user1 = ExcelUtilsDemoUserInfo.builder()
                .id(1)
                .name("张三")
                .birthday(new Date())
                .account("zhangsan01")
                .password("abcd1234")
                .build();
        ExcelUtilsDemoUserInfo user2 = ExcelUtilsDemoUserInfo.builder()
                .id(2)
                .name("李四")
                .birthday(new Date())
                .account("lisi08")
                .password("1234abcd")
                .build();
        List<ExcelUtilsDemoUserInfo> data = Arrays.asList(user1, user2);

        ExcelUtils.download(response, "用户基础数据", ExcelUtilsDemoUserInfo.class, data);
    }

    /**
     * 只下载表头数据，例如用作后续上传数据的模板
     *
     * @param response
     * @throws IOException
     */
    @GetMapping("/header/download")
    public void headerDownload(HttpServletResponse response) throws IOException {
        ExcelUtils.headerDownload(response, "用户基础数据", ExcelUtilsDemoUserInfo.class);
    }

    /**
     * excel上传
     *
     * @param file
     * @throws IOException
     */
    @PostMapping("/upload")
    public void upload(@RequestParam("file") MultipartFile file) throws IOException {
        List<ExcelUtilsDemoUserInfo> data = ExcelUtils.read(file, ExcelUtilsDemoUserInfo.class);
        // 数据业务逻辑处理 ...
    }

}

2、Entity实体类

package com.common.excel.demo;

import java.util.Date;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author loveLetter 
 * Created on 2023-05-16
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelUtilsDemoUserInfo {

    @ExcelProperty("id", index = 0)
    @ColumnWidth(value = 30)
    private int id;

    @ExcelProperty("姓名", index = 1)
    @ColumnWidth(value = 30)
    private String name;

    /**
     * 生日，并按照特定的样式转换
     */
    @ExcelProperty("生日")
    @ColumnWidth(value = 30, index = 2)
    @DateTimeFormat("yyyy年MM月dd日")
    private Date birthday;

    @ExcelProperty("账号", index = 3)
    @ColumnWidth(value = 30)
    private String account;

    /**
     * 操作excel时忽略当前字段
     */
    @ExcelIgnore
    @ExcelProperty("密码")
    private String password;

}

3、ExcelUtils

package com.common.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.common.excel.demo.ExcelUtilsDemoController;

import lombok.AllArgsConstructor;
import lombok.Getter;

/**
 * Excel工具类，基础使用示例，见 {@link ExcelUtilsDemoController}
 *
 * 在本工具类中，使用{@link ExcelProperty}注解标注Excel中字段名称到Java对象字段属性的对照
 * 如若想忽略某个对象属性，使用{@link ExcelIgnore}注解标记字段
 * 使用{@link DateTimeFormat}转换日期格式
 * 使用{@link ContentRowHeight}和{@link ColumnWidth}设置单元格长宽属性
 * 使用{@link java.io.File}、{@link java.io.InputStream}、byte[]字段属性可实现对图片资源的Excel导出处理
 * 使用{@link URL}字段属性将远程图片资源进行Excel导出处理
 *
 * @author loveLetter 
 *
 * Created on 2023-05-16
 */
public class ExcelUtils {

    private static final String DEFAULT_SHEET_NAME = "Sheet1";

    /**
     * 从流中读取excel数据到list
     */
    public static <T> List<T> read(MultipartFile file, Class<T> clazz) throws IOException {
        return read(file.getInputStream(), clazz);
    }

    /**
     * 从流中读取excel数据到list
     */
    public static <T> List<T> read(InputStream inputStream, Class<T> clazz) {
        return read(inputStream, clazz, 0, 1).data;
    }

    /**
     * 从流中读取excel数据到list
     *
     * @param inputStream
     * @param clazz 读取的对象的类型
     * @param sheetNo 被读取的Excel文件的Sheet序号，从0开始
     * @param headRowNumber 被读取的Excel文件的表头的行数，
     *                      0：该表没有表头，首行开始即为数据
     *                      1：该表有1行表头数据
     *                      1+：该表实际的表头行数，从headRowNumber+1开始为数据
     */
    public static <T> ExcelReadResult<T> read(InputStream inputStream, Class<T> clazz, int sheetNo,
            int headRowNumber) {
        ListReadListener<T> readListener = new ListReadListener(headRowNumber);
        EasyExcel.read(inputStream, clazz, readListener).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
        return new ExcelReadResult(readListener.data, readListener.headers);
    }

    /**
     * web环境的Excel文件下载：下载表头文件
     *
     * @param response
     * @param fileName 下载的文件名，不用加扩展名
     * @throws IOException
     */
    public static void headerDownload(HttpServletResponse response, String fileName, Class clazz) throws IOException {
        download(response,fileName, DEFAULT_SHEET_NAME, clazz, null);
    }

    /**
     * web环境的Excel文件下载
     *
     * @param response
     * @param fileName 下载的文件名，不用加扩展名
     * @param data Excel数据来源对象
     * @throws IOException
     */
    public static void download(HttpServletResponse response, String fileName, Class clazz, List data) throws IOException {
        download(response,fileName, DEFAULT_SHEET_NAME, clazz, data);
    }

    /**
     * web环境的Excel文件下载
     *
     * @param response
     * @param fileName 下载的文件名，不用加扩展名
     * @param sheetName 下载的Excel文件sheetName
     * @param clazz
     * @param data Excel数据来源对象集
     * @throws IOException
     */
    public static void download(HttpServletResponse response, String fileName, String sheetName,
            Class clazz, List data) throws IOException {
        if (response == null || clazz == null || StringUtils.isEmpty(fileName)) {
            throw new IllegalArgumentException("Response,clazz,fileName cannot be empty.");
        }

        fileName = new String(fileName.replace(" ", "_").getBytes("UTF-8"), "ISO-8859-1");
        // fileName = URLEncoder.encode(fileName, "UTF-8");
        sheetName = StringUtils.isEmpty(sheetName) ? DEFAULT_SHEET_NAME : sheetName;
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 将数据写入到Excel文件中
     *
     * @param file
     * @param clazz
     * @param data Excel数据来源对象集
     * @param sheetName Excel文件sheetName
     */
    public static void write(File file, Class clazz, List data, String sheetName) throws FileNotFoundException {
        write(new FileOutputStream(file), clazz, data, sheetName);
    }

    /**
     * 将数据写入到Excel文件中
     *
     * @param outputStream 输出流
     * @param clazz
     * @param data Excel数据来源对象集
     * @param sheetName Excel文件sheetName
     */
    public static void write(OutputStream outputStream, Class clazz, List data, String sheetName) {
        EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 读取Excel的结果集对象
     */
    @Getter
    @AllArgsConstructor
    public static class ExcelReadResult<T> {

        /**
         * Excel中读取的对象的数据集
         */
        private List<T> data;

        /**
         * Excel中读取的表头数据集合，从第一行开始，顺序记录
         * list中的元素map，key为当前表头字段index，value为表头值
         */
        private List<Map<Integer, String>> headers;

    }

    private static class ListReadListener<T> extends AnalysisEventListener<T> {

        private List<T> data;
        private List<Map<Integer, String>> headers;
        private int headRowNumber;

        ListReadListener() {
            this(1);
        }

        ListReadListener(int headRowNumber) {
            if (headRowNumber < 0) {
                headRowNumber = 0;
            }

            this.headRowNumber = headRowNumber;
            data = new ArrayList<>();
            headers = new ArrayList<>(headRowNumber);
        }

        @Override
        public void invoke(T rowData, AnalysisContext context) {
            this.data.add(rowData);
        }

        @Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
            headers.add(headMap);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
        }

    }
}