EasyExcelUtil 工具类

duanhaiyang1

已于 2023-10-07 15:22:27 修改

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： java工具类文章标签： windows

于 2023-07-13 10:56:17 首次发布

本文链接：https://blog.csdn.net/duanhaiyang1/article/details/131697699

java工具类专栏收录该内容

9 篇文章

订阅专栏

该代码示例展示了如何使用EasyExcel库在Java中进行Excel文件的读取和写入，包括从文件和输入流读取，以及向文件和输出流写入。同时，定义了实体类来映射Excel表格的数据，并提供了下载Excel到浏览器的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package com.chne.chne_implement.privateUtils;

import com.alibaba.excel.EasyExcel;
//import com.chne.chne_base.entity.DataListener;
import com.chne.chne_exception.exception.NJYException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
/**
 * @author duanhaiyang
 * @date 2023/7/12 19:57
 */
public class EasyExcelUtil {

        private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtil.class);

        //public static <T> List<T> read(String filePath, final Class<?> clazz) {
        //    File f = new File(filePath);
        //    try (FileInputStream fis = new FileInputStream(f)) {
        //        return read(fis, clazz);
        //    } catch (FileNotFoundException e) {
        //        LOGGER.error("文件{}不存在", filePath, e);
        //    } catch (IOException e) {
        //        LOGGER.error("文件读取出错", e);
        //    }
        //    return null;
        //}

        //public static <T> List<T> read(InputStream inputStream, final Class<?> clazz) {
        //    if (inputStream == null) {
        //        throw new NJYException("解析出错了，文件流是null");
        //    }
        //
        //    // 有个很重要的点 DataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        //    DataListener<T> listener = new DataListener<>();
        //
        //    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        //    EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
        //    return listener.getRows();
        //}

        public static void write(String outFile, List<?> list) {
            Class<?> clazz = list.get(0).getClass();
            // 新版本会自动关闭流，不需要自己操作
            EasyExcel.write(outFile, clazz).sheet().doWrite(list);
        }

        public static void write(String outFile, List<?> list, String sheetName) {
            Class<?> clazz = list.get(0).getClass();
            // 新版本会自动关闭流，不需要自己操作
            EasyExcel.write(outFile, clazz).sheet(sheetName).doWrite(list);
        }

        public static void write(OutputStream outputStream, List<?> list, String sheetName) {
            Class<?> clazz = list.get(0).getClass();
            // 新版本会自动关闭流，不需要自己操作
            // sheetName为sheet的名字，默认写第一个sheet
            EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(list);
        }

        /**
         * 文件下载（失败了会返回一个有部分数据的Excel），用于直接把excel返回到浏览器下载
         */
        public static void download(HttpServletResponse response, List<?> list, String sheetName) throws IOException {
            Class<?> clazz = list.get(0).getClass();

            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(list);
        }
}

实体类：

package com.chne.chne_base.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.chne.chne_base.model.BaseModel;
import io.swagger.annotations.ApiModelProperty;

import java.io.Serializable;

/**
 * 系统重点操作日志表
 */
@ContentRowHeight(15) // 文本行高度
@HeadRowHeight(20) // 标题高度
@ColumnWidth(20) // 默认列宽度
public class BSysOperation extends BaseModel implements Serializable {

    @ExcelProperty(value = "项目编码", index = 0)
    @ApiModelProperty("项目编码")
    private String projectCode;

    @ExcelProperty(value = "用户名称", index = 1)
    @ApiModelProperty("用户名称")
    private String userName;

    @ExcelProperty(value = "用户编码", index = 2)
    @ApiModelProperty("用户编码 ")
    private String userCode;

    @ExcelProperty(value = "操作类型", index = 3)
    @ApiModelProperty("操作类型")
    private String operation;

    @ExcelProperty(value = "操作描述", index = 4)
    @ApiModelProperty("操作描述")
    private String operationType;

    public String getProjectCode() {
        return projectCode;
    }

    public void setProjectCode(String projectCode) {
        this.projectCode = projectCode;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserCode() {
        return userCode;
    }

    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }

    public String getOperation() {
        return operation;
    }

    public void setOperation(String operation) {
        this.operation = operation;
    }

    public String getOperationType() {
        return operationType;
    }

    public void setOperationType(String operationType) {
        this.operationType = operationType;
    }

    @Override
    public String toString() {
        return "BOperation{" +
                "projectCode='" + projectCode + '\'' +
                ", userName='" + userName + '\'' +
                ", userCode='" + userCode + '\'' +
                ", operation='" + operation + '\'' +
                ", operationType='" + operationType + '\'' +
                '}';
    }
}

package com.chne.chne_base.model;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import org.springframework.format.annotation.DateTimeFormat;

import java.io.Serializable;
import java.util.Date;

/**
 * BaseModel
 */
@ExcelIgnoreUnannotated
public class BaseModel implements Serializable {

    private static final long serialVersionUID = 1L;
    @ApiModelProperty("物理主键ID")
    private Long id;
    private String createBy;//创建人编码
    private String createByName;//创建人名称
    @ExcelProperty(value = "创建时间", index = 5)
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;//创建时间
    private String updateBy;//更新人编码
    private String updateByName;//更新人名称
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date updateTime;//更新时间
    private int pageIndex;
    private int pageSize;
    private String messages;
    private Integer isDeleted; //是否删除

    private String mapString1;
    private String mapString2;

    private String startTime;
    private String endTime;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCreateBy() {
        return createBy;
    }

    public void setCreateBy(String createBy) {
        this.createBy = createBy;
    }

    public String getCreateByName() {
        return createByName;
    }

    public void setCreateByName(String createByName) {
        this.createByName = createByName;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getUpdateBy() {
        return updateBy;
    }

    public void setUpdateBy(String updateBy) {
        this.updateBy = updateBy;
    }

    public String getUpdateByName() {
        return updateByName;
    }

    public void setUpdateByName(String updateByName) {
        this.updateByName = updateByName;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public String getMessages() {
        return messages;
    }

    public void setMessages(String messages) {
        this.messages = messages;
    }

    public Integer getIsDeleted() {
        return isDeleted;
    }

    public void setIsDeleted(Integer isDeleted) {
        this.isDeleted = isDeleted;
    }

    public int getPageIndex() {
        return this.pageIndex;
    }

    public void setPageIndex(int pageIndex) {
        this.pageIndex = pageIndex;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public String getMapString1() {
        return mapString1;
    }

    public void setMapString1(String mapString1) {
        this.mapString1 = mapString1;
    }

    public String getMapString2() {
        return mapString2;
    }

    public void setMapString2(String mapString2) {
        this.mapString2 = mapString2;
    }

    public String getStartTime() {
        return startTime;
    }

    public void setStartTime(String startTime) {
        this.startTime = startTime;
    }

    public String getEndTime() {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }
}

工具类

//package com.chne.chne_base.entity;
//
//import com.alibaba.excel.context.AnalysisContext;
//import com.alibaba.excel.event.AnalysisEventListener;
//import org.slf4j.Logger;
//import org.slf4j.LoggerFactory;
//
//import java.util.ArrayList;
//import java.util.List;
///**
// * @author duanhaiyang
// * @date 2023/7/12 19:59
// */
//public class DataListener<T> extends AnalysisEventListener<T> {
//
//    private static final Logger LOGGER = LoggerFactory.getLogger(DataListener.class);
//
//    private final List<T> rows = new ArrayList<>();
//
//    @Override
//    public void invoke(T t, AnalysisContext analysisContext) {
//        rows.add(t);
//    }
//
//    @Override
//    public void doAfterAllAnalysed(AnalysisContext context) {
//        LOGGER.info("解析完成！读取{}行", rows.size());
//    }
//
//    public List<T> getRows() {
//        return rows;
//    }
//
//}

<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.1.7</version>
		</dependency>

调用：

 @Override
    public void dowloadExcel(HttpServletResponse response, BSysOperation bSysOperation){
        List<BSysOperation> bSysOperations = bSysOperationMapper.queryList(null);
        String sheetName = "操作记录表";
        try {
            EasyExcelUtil.download(response,bSysOperations,sheetName);
        } catch (IOException e) {
            e.printStackTrace();
        }