EasyExecl 简单导出使用整合

1. 导入依赖jar包

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

2. 创建映射类对象

  import com.alibaba.excel.annotation.ExcelIgnore;
  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.definesys.mpaas.query.annotation.*;
  import com.definesys.mpaas.query.json.MpaasDateDeserializer;
  import com.definesys.mpaas.query.json.MpaasDateSerializer;
  import com.definesys.mpaas.query.model.MpaasBasePojo;
  import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
  import com.fasterxml.jackson.databind.annotation.JsonSerialize;
  import lombok.Data;
  
  import java.util.Date;
  
  /**
   * @author: mingan.xie
   * @since: 2020-08-21
   */
  
  @ContentRowHeight(12)
  @HeadRowHeight(20)
  @Table(value = "t_blog")
  public class Blog extends MpaasBasePojo {
  
      @ExcelProperty(index = 0, value = "主键")
      @ExcelIgnore
      private Long id;
  
      @ExcelIgnore
      private String title;
  
      @ExcelProperty(index = 2, value = "是否欣赏")
      private Boolean appreciation;
  
      @ExcelProperty(index = 3, value = "是否评论")
      private Boolean commentabled;
  
      @ExcelProperty(index = 5, value = "内容")
      @ColumnWidth(20)
      private String content;
  
      @ColumnWidth(20)
      @ExcelProperty(index = 6, value = "创建时间", format = "yyyy-MM-dd HH:mm:ss")
      private Date createTime;
  
  }

注解说明：
    修饰类的注解：（建议直接复制实例的高度，格式已经调整过了）
    	@HeadRowHeight：设置 表头 高度
    	@ContentRowHeight：设置 row 高度，不包含表头（内容）
    修饰属性的注解：
    	@ExcelProperty：设置表头信息
    		value：表头名称
    		index：排序
    		format：日期格式化
    	@ColumnWidth：设置导出的宽度
    	@ExcelIgnore：被标记的属性不参与导出

3. 调用工具类

  import com.alibaba.excel.EasyExcel;
  import com.alibaba.excel.write.metadata.style.WriteCellStyle;
  import com.alibaba.excel.write.metadata.style.WriteFont;
  import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
  import org.apache.poi.ss.usermodel.IndexedColors;
  import org.apache.poi.ss.usermodel.VerticalAlignment;
  
  import javax.servlet.http.HttpServletResponse;
  import java.io.ByteArrayOutputStream;
  import java.net.URLEncoder;
  import java.util.List;
  
  /**
   * @author xiemingan
   * @date 2022-11-07 11:18
   */
  public class ExportExcelUtil {
  
  
      /**
       * response web excel下载
       *
       * @param fileName 文件名称
       * @param dataList 数据集合
       * @param response 响应对象
       * @param tClass   映射类对象
       * @param <T>      泛型
       */
      public static <T> void responseExportExcel(String fileName,
                                                 List<?> dataList,
                                                 HttpServletResponse response,
                                                 Class<T> tClass) {
          try {
              response.setContentType("application/vnd.ms-excel");
              response.setCharacterEncoding("utf-8");
              // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
              response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
  
              // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
              EasyExcel.write(response.getOutputStream(), tClass)
                      .autoCloseStream(Boolean.FALSE)
                      .sheet()
                      .registerWriteHandler(getCommonStrategy())
                      .doWrite(dataList);
          } catch (Exception e) {
              e.getMessage();
          }
      }
  
  
      /**
       * 本地excel下载
       *
       * @param fileName 本地绝对路径
       * @param dataList 数据集合
       * @param tClass   映射类对象
       * @param <T>      泛型
       */
      public static <T> void LocalExportExcel(String fileName,
                                              List<?> dataList,
                                              Class<T> tClass) {
          try {
              // 本地文件操作
              EasyExcel.write(fileName, tClass)
                      .sheet(0)
                      .registerWriteHandler(getCommonStrategy())
                      .doWrite(dataList);
          } catch (Exception e) {
              e.getMessage();
          }
      }
  
  
      /**
       * aliyun oss 服务上传
       * @param url 上传连接
       * @param dataList 数据集合
       * @param tClass 映射类对象
       * @param <T> 泛型
       */
      public static <T> void exportExcelResponse(String url,
                                                 List<?> dataList,
                                                 Class<T> tClass) {
          try {
              ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
              EasyExcel.write(byteArrayOutputStream, tClass)
                      .sheet(0)
                      .registerWriteHandler(getCommonStrategy())
                      .doWrite(dataList);
              OssUtil.ossUploadMediaByteArray(url, byteArrayOutputStream.toByteArray());
              byteArrayOutputStream.close();
          } catch (Exception e) {
              e.getMessage();
          }
      }
  
  
      /**
       * 设置通用头策略
       * @return
       */
      public static HorizontalCellStyleStrategy getCommonStrategy() {
          // 设置头的字体
          WriteFont headWriteFont = new WriteFont();
          // 字号
          headWriteFont.setFontHeightInPoints((short) 11);
          // 字体加粗
          headWriteFont.setBold(true);
          // 字体名称
          headWriteFont.setFontName("等线");
  
  
          // 头的策略
          WriteCellStyle headWriteCellStyle = new WriteCellStyle();
          // 垂直对齐方式
          headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
          // 背景色
          headWriteCellStyle.setFillBackgroundColor(IndexedColors.TURQUOISE.getIndex());
          headWriteCellStyle.setWriteFont(headWriteFont);
  
          return new HorizontalCellStyleStrategy(headWriteCellStyle, new WriteCellStyle());
      }
  
  }

aliyun oss 上传伪代码

public static void ossUploadMediaByteArray(String objectName, byte[] content) {
    //检查客户端是否初始化
    OSS ossClient = new OSSClientBuilder().build("url", "xxxxxxxxx", "xxxxxxxxx");
    try {
        ossClient.putObject(OssConfig.getBucketName(), objectName, new ByteArrayInputStream(content));
    } catch (OSSException oe) {
        //           Caught an OSSException, which means your request made it to OSS,but was rejected with an error response for some reason.;
        log.error("Error Message: {},Error Code:{},Request ID{},Host ID{}", oe.getErrorMessage(), oe.getErrorCode(), oe.getRequestId(), oe.getHostId());
    } catch (ClientException ce) {
        //           Caught an ClientException, which means the client encountered a serious internal problem while trying to communicate with OSS,such as not being able to access the network.
        log.error("Error Message:{}", ce.getMessage());
        ossClient.shutdown();
    }
}