搜索
简帛阁>技术文章>EasyExcel数据导出功能的封装及上传到云服务器(OSS)

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包版本冲突问题,报某些方法或者类找不到(升级版本即可)
1工具类11EasyExcel封装类packagecomexampleutilseasyexcel;importcomalibabaexcelmetadataCellData;importcomal
整理下做笔记1、Controller层@ApiOperation(导出)@RequestMapping(value/exportAnswerAll,methodRequestMethodPOST)@R
准备七牛云/阿里OSShttps://githubcom/pfinal/storage2composer安装包安装composerrequirepfinal/storage本地存储Local阿里
OSS不支持通过一个网络地址来上传图片,所以若想将网络图片传到OSS需要走点弯路。1、通过链接将图片下载到本地一个文件夹下面2、用OSS上传该文件夹下文件3、上传完成后删除本地文件具体
参考:http://wwwthinkphpcn/extend/789html1、前往阿里github下载SDK包:https://githubcom/aliyun/aliyunossphpsdk/
需求背景由于现有后台管理系统,上传视频越来越大,加上上传视频较慢,后端小哥提出直接从前端上传视频或者其他文件到阿里OSS存储。阿里OSS阿里OSS文档介绍,这里不做过多赘述安装原本在最开始
Java下载https文件传到阿里oss服务器今天做了一个从Https链接中下载音频并且传到OSS服务器,记录一下希望大家也少走弯路。一共两个类:1、实现自己证书信任管理器类/***@auth
easyExcel导出此篇仅记录导出功能实现!1、导入maven依赖<dependency><groupId>comalibaba</groupId><a
easyExcel导出excel简单使用Java解析、生成Excel比较有名框架有Apachepoi、jxl。但他们都存在一个严重问题就是非常耗内存,poi有一套SAX模式API可以一定程
目录一、导出数据字典到Excel1创建导出实体类2后台接口代码Controller层Service层3页面导出按钮4测试数据导出到Excel二、导入数据字典到网页1后台接口代码Controller层S