EasyExcel数据导出功能的封装及上传到云服务器(OSS)

1. 工具类

1.1 EasyExcel封装类
package com.example.utils.easyexcel;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
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.AbstractColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;

/**
 * 描述:
 * EasyExcel的基础封装
 *
 * @author XueGuCheng
 * @create 2021-03-19 23:01
 */
@Slf4j
public class EasyExcel {



    /**
     * 设置响应头
     *
     * @param response 回应的请求数据
     * @param fileName 文件名字
     */
    public static void setHead(HttpServletResponse response, String fileName) {

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            log.error("编码异常");
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

    }

    /**
     * 设置Excel的格式
     *
     * @return 格式化后的Excel
     */
    public static HorizontalCellStyleStrategy formatExcel() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置自动换行
        contentWriteCellStyle.setWrapped(true);
        // 设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

    }

    /**
     * 设置头部单元格宽度
     */
    public static class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
            // 设置宽度
            Sheet sheet = writeSheetHolder.getSheet();
            sheet.setColumnWidth(cell.getColumnIndex(), 5000);
        }
    }



}

1.2 EasyExcelUtils封装
注意: 博主并非采用了浏览器访问url直接下载文件的方式实现导出功能,所以采用了ByteArrayOutputStream转换数据直接上传至OSS。如果需要浏览器访问url直接下载文件,使用HttpServletResponse参数的getOutputStream()方法获取输出流,传入ExcelWriter即可,见注释代码。
package com.example.utils.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
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.AbstractColumnWidthStyleStrategy;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;


import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

/**
 * @ClassName: EasyExcelUtils
 * @Description: EasyExcel工具类(替换com.example.utils.easyexcel)
 * @Author: jiangbeiping
 * @Data: 2021/3/18 16:46
 * @Version: 1.0
 */
@Slf4j
public class EasyExcelUtils {

    /**
     * 每个sheet的容量,即超过60000时就会把数据分sheet
     */
    private static final int PAGE_SIZE = 60000;


    /**
     * 导出报表(使用注解方式设置Excel头数据)
     *
     * @param response   响应请求
     * @param data       报表数据
     * @param fileName   文件名字
     * @param excelClass 报表实体类的Class(根据该Class的属性来设置Excel的头属性)
     */
    public static void exportByExcel(HttpServletResponse response, List<?> data, String fileName, Class<?> excelClass) throws IOException {
        @Cleanup ByteArrayOutputStream os = new ByteArrayOutputStream();
//        ServletOutputStream os = response.getOutputStream();
        long exportStartTime = System.currentTimeMillis();;
        log.info("报表导出Size: " + data.size() + "条。");

        // 把查询到的数据按设置的sheet的容量进行切割
        List<? extends List<?>> lists = SplitList.splitList(data, PAGE_SIZE);
        // 设置响应头
        com.example.utils.easyexcel.EasyExcel.setHead(response, fileName);
        // 格式化Excel数据
        // EasyExcelBasic.formatExcel():设置Excel的格式
        // EasyExcelBasic.ExcelWidthStyleStrategy():设置头部单元格宽度
        ExcelWriter excelWriter = EasyExcel.write(os, excelClass).registerWriteHandler(com.example.utils.easyexcel.EasyExcel.formatExcel()).registerWriteHandler(new com.example.utils.easyexcel.EasyExcel.ExcelWidthStyleStrategy()).build();
		
		// 浏览器访问url直接下载文件的方式
 		//ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), excelClass).registerWriteHandler(com.example.utils.easyexcel.EasyExcel.formatExcel()).registerWriteHandler(new com.example.utils.easyexcel.EasyExcel.ExcelWidthStyleStrategy()).build();

        ExcelWriterSheetBuilder excelWriterSheetBuilder;
        WriteSheet writeSheet;
        for (int i = 1; i <= lists.size(); ++i) {
            excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
            excelWriterSheetBuilder.sheetNo(i).sheetName("sheet" + i);
            writeSheet = excelWriterSheetBuilder.build();
            excelWriter.write(lists.get(i - 1), writeSheet);
        }
        // 必须要finish才会写入,不finish只会创建empty的文件
        excelWriter.finish();

        byte[] content = os.toByteArray();
        @Cleanup InputStream is = new ByteArrayInputStream(content);

        // 文件落地,用来测试文件的格式和数据的完整性
        // @Cleanup:lombok关流注解
        //FileOutputStream fileOutputStream = new FileOutputStream("/data/logs/" + fileName + ".xlsx");
        //@Cleanup BufferedInputStream bis = new BufferedInputStream(is);
        //@Cleanup BufferedOutputStream bos = new BufferedOutputStream(fileOutputStream);
        //byte[] buff = new byte[2048];
        //int bytesRead;
        //while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
        //    bos.write(buff, 0, bytesRead);
       // }
        //log.info("文件落地磁盘");


        // 文件上传到OSS
        FileUploadUtil_Test.upLocalFileToOss(is, fileName);


        System.out.println("报表导出结束时间:" + new Date() + ";导出耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");

    }


}

1.3 SplitList封装类
package com.example.utils.easyexcel;

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

/**
 * @ClassName: SplitList
 * @Description: EasyExcelUtils类的辅助类(切割查询的数据)
 * @Author: jiangbeiping
 * @Data: 2021/3/19 17:38
 * @Version: 1.0
 */
public class SplitList {

    /**
     * 切割查询的数据
     * @param list 需要切割的数据
     * @param len 按照什么长度切割
     * @param <T>
     * @return
     */
    public static <T> List<List<T>> splitList(List<T> list, int len) {
        if (list == null || list.size() == 0 || len < 1) {
            return null;
        }
        List<List<T>> result = new ArrayList<List<T>>();
        int size = list.size();
        int count = (size + len - 1) / len;
        for (int i = 0; i < count; i++) {
            List<T> subList = list.subList(i * len, (Math.min((i + 1) * len, size)));
            result.add(subList);
        }
        return result;
    }

    /**
     * 集合平均分组
     * @param source 源集合
     * @param n 分成n个集合
     * @param <T> 集合类型
     * @return 平均分组后的集合
     */
    public static <T> List<List<T>> groupList(List<T> source, int n) {
        if (source == null || source.size() == 0 || n < 1) {
            return null;
        }
        if (source.size() < n) {
            return Collections.singletonList(source);
        }
        List<List<T>> result = new ArrayList<List<T>>();
        int number = source.size() / n;
        int remaider = source.size() % n;
        // 偏移量,每有一个余数分配,就要往右偏移一位
        int offset = 0;
        for (int i = 0; i < n;i++) {
            List<T> list1 = null;
            if (remaider > 0){
                list1 = source.subList(i * number + offset,(i + 1) * number + offset + 1);
                remaider--;
                offset++;
            }else {
                list1 = source.subList(i * number + offset, (i+1) * number + offset);
            }
            result.add(list1);
        }
        return result;
    }

}

2. 测试类

2.1 实体类
package com.example.vo;


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

@Data
public class User {

  @ExcelProperty(value = "ID",index = 2)
  private long id;

  @ExcelProperty(value = "用户名",index = 3)
  private String name;

  @ExcelProperty(value = "居住城市",index = 4)
  private String city;

  @ExcelProperty(value = "性别",index = 5)
  private String sex;

  @ExcelProperty(value = "序号",index = 1)
  private Integer num;

  @ExcelProperty(value = "时间",index = 6)
  @DateTimeFormat("yyyy-MM-dd hh:mm:ss")
  private String time;

}

2.2 Controller
package com.example.controller;

import com.example.service.UserService;
import com.example.utils.easyexcel.EasyExcel;
import com.example.utils.easyexcel.EasyExcelUtils;
import com.example.vo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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

/**
 * 描述:
 * Excel测试
 *
 * @author XueGuCheng
 * @create 2021-03-20 22:27
 */
@RestController
@RequestMapping("Excel")
public class ExcelController {

    @Autowired
    private UserService userService;

    // http://localhost:8081/Excel/EasyExce
    @GetMapping("/EasyExce")
    public void doExport(HttpServletResponse response) throws IOException {
        List<User> users = userService.ListUser();
        EasyExcelUtils.exportByExcel(response,users,"EasyExcel测试",User.class);
    }


}

3. 效果图

第一列是空,因为@ExcelProperty注解的index是从o开始的

部分数据没有,是因为数据库就没有
在这里插入图片描述

4. 总结

  1. 实体类需要标明注释
  2. 时间类字段转换(可以自定义转换器)
  3. ExcelWriter必须要finish才会写入,不finish只会创建empty的文件
  4. jar包版本冲突问题,报某些方法或者类找不到(升级版本即可)
  • 18
    点赞
  • 48
    收藏
    觉得还不错? 一键收藏
  • 15
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 15
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值