spring boot整合Easy Excel（导入导出数据）

1、在pom.xml文件里，添加依赖

        <!--alibaba-easyexcel的使用-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.3</version>
        </dependency>

        <!-- 引入 HttpServletResponse 等  -->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-core</artifactId>
            <version>9.0.63</version>
            <scope>compile</scope>
            <optional>true</optional>
        </dependency>

2、创建工具类

package com.ynkbny.config.easyExcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Set;

public class EasyExcelUtils {
    private static final Log log = LogFactory.getLog(EasyExcelUtils.class);

    /**
     * 单sheet版本Excel读取
     * 从Excel中读取文件，读取的文件是一个DTO类
     *
     * @param inputStream 文件流
     * @param clazz       行数据类型
     */
    public static <T> List<T> readExcelOneSheet(InputStream inputStream, final Class<?> clazz) {
        // 1.创建监听类
        ExcelListener<T> listener = new ExcelListener<>();
        // 2.构建工作簿对象的输入流
        ExcelReader excelReader = EasyExcel.read(inputStream, clazz, listener).build();
        // 3.构建工作表对象的输入流，默认是第一张工作表
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        // 4.读取信息，每读取一行都会调用监听类的 invoke 方法
        excelReader.read(readSheet);
        // 5.关闭流，如果不关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
        return listener.getDataList();
    }

    /**
     * 多sheet版本Excel读取
     *
     * @param <T>      行数据的类型
     * @param filePath 文件路径
     * @param clazz    行数据的类型
     * @return 所有信息
     */
    public static <T> List<T> readExcelAllSheet(String filePath, final Class<?> clazz) {
        ExcelListener<T> listener = new ExcelListener<>();
        // 读取全部sheet
        // 这里需要注意 ExcelListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
        EasyExcel.read(filePath, clazz, listener).doReadAll();
        return listener.getDataList();
    }


    /**
     * 网页上的下载导出，只有一个工作表
     *
     * @param fileName  文件名
     * @param clazz     类的字节码文件，行数据的类型
     * @param dataList  导出的数据
     * @param sheetName 工作表名
     * @param response  响应体
     * @throws IOException 异常对象
     */
    public static void writeWeb(String fileName, final Class<?> clazz, List<?> dataList
            , String sheetName, HttpServletResponse response) throws IOException {
        // 1.指定响应体内容类型
        response.setContentType("application/vnd.ms-excel");
        // 2.指定编码方式
        response.setCharacterEncoding("utf-8");
        // 3.URLEncoder.encode可以防止中文乱码：import java.net.URLEncoder
        fileName = URLEncoder.encode(fileName, "UTF-8");
        // 4.指定响应标头
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        // 5.获取工作簿对象的输出流
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        // 6.设置工作表的名称
        if (!StringUtils.hasText(sheetName)) {
            sheetName = "sheet1";
        }
        // 7.指定写用哪个class去写
        WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).head(clazz).build();
        // 8.将 dataList 中的数据逐行写入工作表中
        excelWriter.write(dataList, writeSheet);
        // 9.finish关闭流
        excelWriter.finish();
        // 10.关闭流
        response.getOutputStream().close();
    }

    /**
     * 网页上的下载导出，只有一个工作表
     *
     * @param fileName 文件名
     * @param dataList 导出的数据
     * @param response 响应体
     * @throws IOException 异常对象
     */
    public static void writeWeb(String fileName, List<?> dataList, HttpServletResponse response) throws IOException {

        // 1.指定响应体内容类型
        response.setContentType("application/vnd.ms-excel");

        // 2.指定编码方式
        response.setCharacterEncoding("utf-8");

        // 3.URLEncoder.encode可以防止中文乱码：import java.net.URLEncoder
        fileName = URLEncoder.encode(fileName, "UTF-8");

        // 4.指定响应标头
        response.setHeader("Strict-Transport-Security", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
//        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "public");

        // 5.获取工作簿对象的输出流
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

        // 6.设置工作表的名称
        // 7.指定写用哪个class去写
        WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet1").head(dataList.get(0).getClass()).build();

        // 8.将 dataList 中的数据逐行写入工作表中
        excelWriter.write(dataList, writeSheet);

        // 9.finish关闭流
        excelWriter.finish();

        // 10.关闭流
        response.getOutputStream().close();
    }

    /**
     * 写出数据到文件
     *
     * @param response
     * @param data
     * @param fileName
     * @param clazz
     * @param sheetName
     * @param <T>
     * @throws Exception
     */
    public static <T> void writeExcelList(HttpServletResponse response, List<List<T>> data, String fileName, Class<?> clazz, String sheetName) throws Exception {
        OutputStream out = getOutputStream(fileName, response);
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
        ExcelWriter excelWriter = excelWriterBuilder.build();
        ExcelWriterSheetBuilder excelWriterSheetBuilder;
        WriteSheet writeSheet;
        for (int i = 1; i <= data.size(); i++) {
            excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
            excelWriterSheetBuilder.sheetNo(i);
            excelWriterSheetBuilder.sheetName(sheetName + i);
            writeSheet = excelWriterSheetBuilder.build();
            excelWriter.write(data.get(i - 1), writeSheet);
        }
        excelWriter.finish();
        out.close();
    }

    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        //  response.setContentType("application/vnd.ms-excel"); // .xls

        // .xlsx
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        response.setCharacterEncoding("utf8");

        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        return response.getOutputStream();
    }

    /**
     * 获取默认表头内容的样式
     *
     * @return
     */
    private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy() {
        /** 表头样式 **/
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色（浅灰色）
        // 可以参考：https://www.cnblogs.com/vofill/p/11230387.html
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体大小
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        /** 内容样式 **/
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 内容字体样式（名称、大小）
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
//		//设置内容垂直居中对齐
//		contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//		//设置内容水平居中对齐
//		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 头样式与内容样式合并
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 导出 Excel到指定目录 ：单个 sheet，带表头,
     *
     * @param tableData
     * @param fileName  导出的路径+文件名  例如：   file/test.xlsx
     * @param sheetName 导入文件的 sheet 名
     * @throws Exception
     */
    public static void writeExcelAutoColumnWidth(String fileName, List<?> tableData, String sheetName, Class<?> clazz) throws Exception {
        // 根据用户传入字段 假设我们要忽略 date
        EasyExcel.write(fileName, clazz)
                .sheet(sheetName)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .doWrite(tableData);
    }


    /**
     * 导出 Excel到指定目录 ：单个 sheet，带表头,
     *
     * @param fileName  导出的路径+文件名  例如：   file/test.xlsx
     * @param tableData
     */
    public static void writeExcelWithOneSheet1(String fileName, List<?> tableData
            , String sheetName, Class<?> clazz, Set<String> excludeColumnFiledNames) {
        // 根据用户传入字段 假设我们要忽略 date
        EasyExcel.write(fileName, clazz)
                .excludeColumnFiledNames(excludeColumnFiledNames)
                .sheet(sheetName)
                .registerWriteHandler(styleWrite(false))
                .doWrite(tableData);
    }

    /**
     * 导出 Excel到指定目录 ：单个 sheet，带表头,
     *
     * @param fileName  导出的路径+文件名  例如：   file/test.xlsx
     * @param tableData
     */
    public static void writeExcelWithOneSheet1(String fileName, List<?> tableData) {

        // 根据用户传入字段 假设我们要忽略 date
        EasyExcel.write(fileName, tableData.get(0).getClass())
                .sheet("Sheet1")
                .registerWriteHandler(styleWrite(false))
                .doWrite(tableData);
    }

    /**
     * 设置Excel样式
     *
     * @param isWrapped 设置 自动换行
     * @return
     */
    public static HorizontalCellStyleStrategy styleWrite(boolean isWrapped) {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        // headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 18);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        //contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        //contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 11);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(isWrapped);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        //EasyExcel.write(fileName, DemoData.class).registerWriteHandler(horizontalCellStyleStrategy).sheet("模板")
        //    .doWrite(data());
    }

}

package com.ynkbny.config.easyExcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * excel通用读取监听类
 *
 */
@Slf4j
@Getter
@NoArgsConstructor
public class ExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * 自定义用于暂时存储data 可以通过实例获取该值
     */
    private final List<T> dataList = new ArrayList<>();

    /**
     * 每解析一行都会回调invoke()方法
     *
     * @param data 每一行的数据
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        dataList.add(data);
        log.info("读取的一条信息：{}", data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("{}条数据，解析完成", dataList.size());
    }
}

3、有了上面的工具类，基本上就可以自己根据需求去使用了，后面是我自己使用的场景，导出实体

package com.ynkbny.user.model.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@TableName(value ="user_energy_day")
@Data
@HeadRowHeight(100)
public class UserEnergyDay {

    /**
     * 企业名称
     */
    @ColumnWidth(40)
    @ExcelProperty("企业名称")
    private String name;

    /**
     * 电能耗数值
     */
    @ColumnWidth(20)
    @ExcelProperty("电能耗数值(kw·h)")
    private BigDecimal energyConsumption;

    /**
     * 创建时间
     */
    @ColumnWidth(15)
    @ExcelProperty("时间")
    private String createTime;

}

4、Controller

package com.ynkbny.web.user.controller;

import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.ynkbny.common.base.BaseResponse;
import com.ynkbny.common.utils.ResultUtils;
import com.ynkbny.config.easyExcel.EasyExcelUtils;
import com.ynkbny.user.manager.UserEnergyManager;
import com.ynkbny.user.model.excel.UserEnergyDay;
import com.ynkbny.user.model.excel.UserEnergyMonth;
import com.ynkbny.user.model.vo.PieVo;
import com.ynkbny.user.model.vo.UserProductEnergyConsumptionVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.List;
import java.util.Map;

@Api(tags = "用户端电能耗")
@RestController
@RequestMapping("/user/energy")
@Validated
public class UserEnergyController {
    @Resource
    UserEnergyManager userEnergyManager;


    @ApiOperation("根据条件 导出 全厂电能耗信息")
    @GetMapping("/exportUserEnergy")
    public void exportUserEnergy(HttpServletResponse response,
                                 @ApiParam("开始时间") @NotNull String startTime,
                                            @ApiParam("结束时间") String endTime,
                                              @ApiParam("社会信用代码") @NotNull String enterpriseId,
                                              @ApiParam("日、月、年标识") @NotNull String range) throws IOException, ParseException {
        List list = userEnergyManager.exportUserEnergy(startTime, endTime, enterpriseId, range);
        if("1".equals(range) && ObjectUtils.isEmpty(list)) {
            list.add(new UserEnergyDay());
        }
        if("2".equals(range) && ObjectUtils.isEmpty(list)) {
            list.add(new UserEnergyDay());
        }
        if("3".equals(range) && ObjectUtils.isEmpty(list)) {
            list.add(new UserEnergyMonth());
        }
        EasyExcelUtils.writeWeb("模板_"+enterpriseId, list, response);
    }

}