Skip to content

10.使用Easy Excel导出聊天记录

Jump to bottom
JustCoding-Hai edited this page Nov 8, 2020 · 1 revision

使用Easy Excel导出聊天记录

1.数据转换

导入依赖：

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

EasyExcel提供的五种图片导出的方式：https://www.yuque.com/easyexcel/doc/write#cb1b271f

因为我想把聊天记录中的图片内容也导出到Excel中，而图片的保存的是URL地址。另外因为GroupMsgContent的content属性可能存储普通的文本和图片URL。

1.1 做法1

定义一个URL转换器的做法，新键一个类GroupMsgContentData来分开处理普通的文本和图片URL。

/**
 * Excel导入导出的数据类
 * @author Hai
 * @date 2020/10/7 - 23:52
 */
@ColumnWidth(25)
@ContentRowHeight(30)
public class GroupMsgContentData {

    @ExcelProperty("消息内容编号")
    private Integer id;

    @ExcelProperty("发送者的编号")
    private Integer fromId;

    @ExcelProperty("昵称")
    private String fromName;

    @ExcelIgnore
    private URL fromProfile;

    @ExcelProperty("发送时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date createTime;

    @ExcelProperty(value = {"内容","文本"})
    @ColumnWidth(50)
    private String textContent;

    @ExcelProperty(value = {"内容","图片"},converter = MyUrlImageConverter.class)
    @ColumnWidth(50)
    private URL imageContent;

    @ExcelIgnore
    private Integer messageTypeId;

}

对应的MyUrlImageConverter

/**
 * @author Hai
 * @program: subtlechat
 * @description: 将URL图片的格式转化器
 * @create 2020/10/8 - 12:45
 **/
public class MyUrlImageConverter implements Converter<URL> {
    @Override
    public Class supportJavaTypeKey() {
        return URL.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.IMAGE;
    }

    @Override
    public URL convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                 GlobalConfiguration globalConfiguration) {
        throw new UnsupportedOperationException("Cannot convert images to url.");
    }

    @Override
    public CellData convertToExcelData(URL value, ExcelContentProperty contentProperty,
                                       GlobalConfiguration globalConfiguration) throws IOException {
        InputStream inputStream = null;
        try {
            //开启连接
            URLConnection uc = value.openConnection();
            URL url  = null;
            //获取响应状态
            int statusCode = ((HttpURLConnection) uc).getResponseCode();
            switch (statusCode){
                case 200:
                    inputStream = value.openStream();
                    break;
                case 404:
                    //默认给一个图片
                    url = new URL("https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1598096095144&di=9a72ad26e83effb9341c711c9818b85f&imgtype=0&src=http%3A%2F%2Fpic.616pic.com%2Fys_bnew_img%2F00%2F11%2F69%2Fj2AjnHspwT.jpg");
                    inputStream = url.openStream();
                    break;
                default :
                    url = new URL("https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1598096095144&di=9a72ad26e83effb9341c711c9818b85f&imgtype=0&src=http%3A%2F%2Fpic.616pic.com%2Fys_bnew_img%2F00%2F11%2F69%2Fj2AjnHspwT.jpg");
                    inputStream = url.openStream();
                    break;
            }
            byte[] bytes = IoUtils.toByteArray(inputStream);
            byte[] compressBytes = ImgUtil.compressPicForScale(bytes,200, UUID.randomUUID().toString());
            return new CellData(compressBytes);
        }catch (ConnectException exception){
            //捕获下链接异常
            URL url = new URL("https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1598096095144&di=9a72ad26e83effb9341c711c9818b85f&imgtype=0&src=http%3A%2F%2Fpic.616pic.com%2Fys_bnew_img%2F00%2F11%2F69%2Fj2AjnHspwT.jpg");
            inputStream = url.openStream();
            byte[] bytes = IoUtils.toByteArray(inputStream);
            return new CellData(bytes);
        }catch (FileNotFoundException fileNotFoundException){
            URL url = new URL("https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1598096095144&di=9a72ad26e83effb9341c711c9818b85f&imgtype=0&src=http%3A%2F%2Fpic.616pic.com%2Fys_bnew_img%2F00%2F11%2F69%2Fj2AjnHspwT.jpg");
            inputStream = url.openStream();
            byte[] bytes = IoUtils.toByteArray(inputStream);
            return new CellData(bytes);
        }finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }
}

在类GroupMsgContent中添加转换为GroupMsgContentData的方法

    /**
     * 将数据库实体转化为Excel的数据实体
     * @param groupMsgContent
     * @return
     */
    public static GroupMsgContentData convertEntityToData(GroupMsgContent groupMsgContent) throws MalformedURLException {
        GroupMsgContentData groupMsgContentData = new GroupMsgContentData();
        groupMsgContentData.setFromId(groupMsgContent.getFromId());
        groupMsgContentData.setId(groupMsgContent.getId());
        groupMsgContentData.setFromName(groupMsgContent.getFromName());
        groupMsgContentData.setCreateTime(groupMsgContent.getCreateTime());
        //转化为URL以Excel导出图片
        groupMsgContentData.setFromProfile(new URL(groupMsgContent.getFromProfile()));
        //根据消息类型设置内容
        if (groupMsgContent.getMessageTypeId()==1){
            groupMsgContentData.setTextContent(groupMsgContent.getContent());
        }
        if (groupMsgContent.getMessageTypeId()==2){
            groupMsgContentData.setImageContent(new URL(groupMsgContent.getContent()));
        }

        return  groupMsgContentData;
    }

在Excel操作前，获取所有的GroupMsgContent，然后通过Stream处理获取转换后的GroupMsgContentData

        RespPageBean allGroupMsgContentByPage = getAllGroupMsgContentByPage(null, null, null, null, null);
        List<GroupMsgContent> data = (List<GroupMsgContent>) allGroupMsgContentByPage.getData();
        //转化数据为用于Excel导出的格式
        List<GroupMsgContentData> convertedData = data.stream().map(item -> {
            try {
                return GroupMsgContent.convertEntityToData(item);
            } catch (MalformedURLException e) {
                e.printStackTrace();
            }
            return new GroupMsgContentData();
        }).collect(Collectors.toList());

这样将GroupMsgContentData作为导出数据的映射类。但该做法增加了一次遍历，当数据量大时，浪费的时间更加明显。

1.2 做法2

定义一个Converter同时处理图片和文本。如果图片处理出错，就当成是字符串处理。

/**
 * @author Hai
 * @program: subtlechat
 * @description: 处理聊天记录的内容的导出
 * @create 2020/10/8 - 23:59
 **/
public class MyContentConverter implements Converter<String> {
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.IMAGE;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                    GlobalConfiguration globalConfiguration) {
        throw new UnsupportedOperationException("Cannot convert images to string");
    }

    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
                                       GlobalConfiguration globalConfiguration) throws IOException {
        if (value.startsWith("http")){
            InputStream inputStream = null;
            URL imageUrl = new URL(value);
            try {
                //开启连接
                URLConnection uc = imageUrl.openConnection();
                URL url  = null;
                //获取响应状态
                int statusCode = ((HttpURLConnection) uc).getResponseCode();
                switch (statusCode){
                    case 200:
                        inputStream = imageUrl.openStream();
                        break;
                    default :
                        //直接当成String处理
                        return new CellData(value);
                }
                byte[] bytes = IoUtils.toByteArray(inputStream);
                //压缩图片
                byte[] compressBytes = ImgUtil.compressPicForScale(bytes,200, UUID.randomUUID().toString());
                return new CellData(compressBytes);
            }catch (Exception exception){
                return new CellData(value);
            }finally {
                if (inputStream != null) {
                    inputStream.close();
                }
            }

        }
        else{
            return new CellData(value);
        }
    }
}

直接在GroupMsgContent中指定转化器

/**
 * (GroupMsgContent)实体类
 *
 * @author makejava
 * @since 2020-06-17 10:46:50
 */
@ColumnWidth(25)
@ContentRowHeight(40)
public class GroupMsgContent implements Serializable {
    private static final long serialVersionUID = 980328865610261046L;
    /**
    * 消息内容编号
    */
    @ExcelProperty("消息内容编号")
    private Integer id;
    /**
    * 发送者的编号
    */
    @ExcelProperty("发送者的编号")
    private Integer fromId;
    /**
    * 发送者的昵称
    */
    @ExcelProperty("昵称")
    private String fromName;
    /**
    * 发送者的头像
    */
    @ExcelIgnore
    private String fromProfile;
    /**
    * 消息发送时间
    */
    @ExcelProperty("发送时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date createTime;
    /**
    * 消息内容
    */
    @ExcelProperty(value = "内容",converter = MyContentConverter.class)
    @ColumnWidth(50)
    private String content;
    /**
    * 消息类型编号
    */
    @ExcelIgnore
    private Integer messageTypeId;
}

2.图片压缩

上面使用到的图片压缩使用的是Thumbnails插件

/**
 * @author Hai
 * @program: subtlechat
 * @description: 调用Thumbnails压缩图片
 * @create 2020/10/8 - 13:50
 **/
public class ImgUtil {

    private static Logger logger = LoggerFactory.getLogger(ImgUtil.class);

    /**
     * 根据指定大小压缩图片
     *
     * @param imageBytes  源图片字节数组
     * @param desFileSize 指定图片大小，单位kb
     * @param imageId     影像编号
     * @return 压缩质量后的图片字节数组
     */
    public static byte[] compressPicForScale(byte[] imageBytes, long desFileSize, String imageId) {
        if (imageBytes == null || imageBytes.length <= 0 || imageBytes.length < desFileSize * 1024) {
            return imageBytes;
        }
        long srcSize = imageBytes.length;
        double accuracy=0.4;
        try {
            while (imageBytes.length > desFileSize * 1024) {
                ByteArrayInputStream inputStream = new ByteArrayInputStream(imageBytes);
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream(imageBytes.length);
                Thumbnails.of(inputStream)
                        .scale(accuracy)
                        .outputQuality(accuracy)
                        .toOutputStream(outputStream);
                imageBytes = outputStream.toByteArray();
            }
            logger.info("【图片压缩】imageId={} | 图片原大小={}kb | 压缩后大小={}kb",
                    imageId, srcSize / 1024, imageBytes.length / 1024);
        } catch (Exception e) {
            logger.error("【图片压缩】msg=图片压缩失败!", e);
        }
        return imageBytes;
    }
}

3.文件处理

依照官方文档处理：https://www.yuque.com/easyexcel/doc/write#afb7324a

将文件写入到

/**
 * 处理群聊记录的导出，提供
 * @param response
 * @throws IOException
 */
@Override
public void handleDownload(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    //设置文件信息 这里URLEncoder.encode可以防止中文乱码
    String fileName = URLEncoder.encode("群聊记录", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    RespPageBean allGroupMsgContentByPage = getAllGroupMsgContentByPage(null, null, null, null, null);
    List<GroupMsgContent> data = (List<GroupMsgContent>) allGroupMsgContentByPage.getData();
    //转化数据为用于Excel导出的格式
    List<GroupMsgContentData> convertedData = data.stream().map(item -> {
        try {
            return GroupMsgContent.convertEntityToData(item);
        } catch (MalformedURLException e) {
            e.printStackTrace();
        }
        return new GroupMsgContentData();
    }).collect(Collectors.toList());

    //写出数据到HttpServletResponse中
    EasyExcel.write(response.getOutputStream(),GroupMsgContentData.class).sheet("sheet1").doWrite(convertedData);
}

4.前端处理

使用JS的内置方法window.open方法处理。

      //导出数据到Excel文件中
      exportData(){
        window.open("/groupMsgContent/download","_parent");
      },

5.待改善

​ 问题：因为导出图片到Excel中导致写Excel的速度太慢了。

​ 解决：

1.前端显示优化，增加下载的进度条

2.选择其他POI技术

3.增加JVM内存

  1. 多线程处理

  2. 导出不处理图片了，只显示url

Clone this wiki locally