SpringBoot利用SXSSFWorkbook导出带网络图片的Excel表格，并设置标题和正文的样式

项目场景：

提示：这里简述项目相关背景：

最近接到一个项目需求，需求离谱，但是咱也得做。具体内容就是需要在小程序的提交作业处加一个导出作业完成情况的excel表格，学校想通过这个方法收集学生的健康码。因为是后台在云上，作业里上传的图片也用了华为云的obs服务，存储在云服务器上，所以访问图片的地址是一个链接，本来想贪图方便，用easypoi提供的模板导出的案例实现快速导出的，结果导出图片用ImageEntity对象，根本加载不了url链接的图片，前后用了各种方法，还转化为inputstream流，还转化为base64编码，都不行，没办法，那就回到最简单的，直接用poi自己写吧，唯一的缺点是，代码多了点，但是耐不住好处多多啊，简单易懂。

---

问题描述

提示：这里描述项目中遇到的问题：

需要导出作业完成情况的Excel表格，困难是如何导出网络链接的图片？

---

原因分析：

提示：这里填写问题的分析：

经过各种尝试，本来想用别人提供的excel框架实现快速导出的方案都被我pass了，直接用最原始的poi开始干吧。

---

解决方案：

提示：这里填写该问题的具体解决方案：

导出excel的实体类，接收图片的字段用byte数组类型就可以

import lombok.Data;

/**
 * @author: fangchuhao
 * @description:
 * @create: 2022-11-14 15:42
 **/
@Data
public class HomeWorkExcelDto {
    private int id;
    private String userId;
    private String userName;
    private String submitTime;
    private byte[] picture1;
    private byte[] picture2;
}

FileUtils代码

package com.lancoo.assistant.util;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;

/**
 * @author: fangchuhao
 * @description:
 * @create: 2022-11-15 09:49
 **/
public class FileUtils {
    /**
     * 得到文件流
     * @param url
     * @return
     */
    public static byte[] getFileStream(String url){
        try {
            URL httpUrl = new URL(url);
            HttpURLConnection conn = (HttpURLConnection)httpUrl.openConnection();
            conn.setRequestMethod("GET");
            conn.setConnectTimeout(5 * 1000);
            InputStream inStream = conn.getInputStream();//通过输入流获取图片数据
            byte[] btImg = readInputStream(inStream);//得到图片的二进制数据
            return btImg;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从输入流中获取数据
     * @param inStream 输入流
     * @return
     * @throws Exception
     */
    public static byte[] readInputStream(InputStream inStream) throws Exception{
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int len = 0;
        while( (len=inStream.read(buffer)) != -1 ){
            outStream.write(buffer, 0, len);
        }
        inStream.close();
        return outStream.toByteArray();
    }
}

service代码需要导入的包

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import java.io.OutputStream;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import com.lancoo.assistant.util.FileUtils;

service导出excel的方法代码

public void homeWorkExcelDownloads(HttpServletResponse response, Integer homeworkId) {
        OutputStream outputStream = null;
        try {
            Workbook workbook = new SXSSFWorkbook();
            Sheet sheet = workbook.createSheet();
            String[] titles = {"序号", "学生ID", "学生姓名", "提交时间", "图片一", "图片二"};
            DateTimeFormatter dfDate = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
            Row titleRow = sheet.createRow(0);
            XSSFCellStyle titleCellStyle = (XSSFCellStyle)workbook.createCellStyle();
            titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
            titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font titleFont = workbook.createFont();
            titleFont.setFontHeight((short) 240);
            titleFont.setBold(true);
            titleCellStyle.setFont(titleFont);

            XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font font = workbook.createFont();
            font.setFontHeight((short) 240);
            cellStyle.setFont(font);

            for (int i = 0; i < titles.length; i++) {
                Cell cell = titleRow.createCell(i);
                cell.setCellStyle(titleCellStyle);
                cell.setCellValue(titles[i]);
            }
            byte[] imgByte;
            List<HomeWorkExcelDto> list = new ArrayList<>();
            Homework homework = homeworkMapper.selectById(homeworkId);
            List<HomeworkUser> homeworkUsers = homeworkUserMapper.selectByHomeworkId(homeworkId);
            int a = 1;
            for (HomeworkUser homeworkUser : homeworkUsers) {
                HomeWorkExcelDto homeWorkExcelDto = new HomeWorkExcelDto();
                List<AnnexVo> annexVos = annexService.getByRelationIdAndAnnexType(homeworkUser.getId(), Constants.AnnexRelationType.SUBMIT, Constants.AnnexType.IMAGE);
                homeWorkExcelDto.setUserId(homeworkUser.getUserId());
                homeWorkExcelDto.setUserName(homeworkUser.getUsername());
                homeWorkExcelDto.setSubmitTime(dfDate.format(homeworkUser.getUpdateTime()));
                homeWorkExcelDto.setId(a++);
                for (int i = 0; i < annexVos.size(); i++) {
                    if (i == 0) {
                        imgByte = FileUtils.getFileStream(annexVos.get(i).getAnnexUrl());
                        homeWorkExcelDto.setPicture1(imgByte);
                    }else if(i == 1) {
                        imgByte = FileUtils.getFileStream(annexVos.get(i).getAnnexUrl());
                        homeWorkExcelDto.setPicture2(imgByte);
                    }
                }
                list.add(homeWorkExcelDto);
            }
            int rowNum = 1;
            font.setBold(false);
            for (HomeWorkExcelDto data: list) {
                Row row = sheet.createRow(rowNum);
                row.setHeightInPoints((short) 160);
                Cell cell0 = row.createCell(0);
                sheet.setColumnWidth(0, 256*10);
                cell0.setCellStyle(cellStyle);
                cell0.setCellValue(data.getId());

                Cell cell1 = row.createCell(1);
                sheet.setColumnWidth(1, 256*20);
                cell1.setCellStyle(cellStyle);
                cell1.setCellValue(data.getUserId());

                Cell cell2 = row.createCell(2);
                sheet.setColumnWidth(2, 256*20);
                cell2.setCellStyle(cellStyle);
                cell2.setCellValue(data.getUserName());

                Cell cell3 = row.createCell(3);
                sheet.setColumnWidth(3, 256*20);
                cell3.setCellStyle(cellStyle);
                cell3.setCellValue(data.getSubmitTime());

                Cell cell4 = row.createCell(4);
                sheet.setColumnWidth(4, 256*20);
                byte[] picture1 = data.getPicture1();
                if (picture1 != null) {
                    int addPicture = workbook.addPicture(picture1, Workbook.PICTURE_TYPE_PICT);
                    Drawing drawing = sheet.createDrawingPatriarch();
                    //图片位置 j是列，rowNum是行
                    ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 4,rowNum, 4+1,rowNum +1);
                    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);//设置图片随单元移动调整大小
                    drawing.createPicture(anchor, addPicture);
                }else {
                    cell4.setCellValue("");
                }

                Cell cell5 = row.createCell(5);
                sheet.setColumnWidth(5, 256*20);
                byte[] picture2 = data.getPicture2();
                if (picture2 != null) {
                    int addPicture = workbook.addPicture(picture2, Workbook.PICTURE_TYPE_PICT);
                    Drawing drawing = sheet.createDrawingPatriarch();
                    //图片位置 j是列，rowNum是行
                    ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5,rowNum, 5+1,rowNum +1);
                    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);//设置图片随单元移动调整大小
                    drawing.createPicture(anchor, addPicture);
                }else {
                    cell5.setCellValue("");
                }

                rowNum++;
            }
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    new String(homework.getTitle().getBytes("gbk"), "iso8859-1") + ".xlsx");
            outputStream = response.getOutputStream();
            //输出excel
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

controller代码：

   @ApiOperation("导出作业完成情况表")
   @GetMapping("/exportHomeworkCompletion")
   public void exportHomeworkCompletion(HttpServletResponse response, @RequestParam Integer homeworkId){
      homeworkService.homeWorkExcelDownloads(response, homeworkId);
   }

导出图片的关键代码：

// 导出excel表格实体类的图片字段，用byte数组即可
private byte[] picture1;

byte[] imgByte;
...
imgByte = FileUtils.getFileStream(annexVos.get(i).getAnnexUrl());
...
Cell cell4 = row.createCell(4);
sheet.setColumnWidth(4, 256*20);
byte[] picture1 = data.getPicture1();
if (picture1 != null) {
    int addPicture = workbook.addPicture(picture1, Workbook.PICTURE_TYPE_PICT);
    Drawing drawing = sheet.createDrawingPatriarch();
    //图片位置 j是列，rowNum是行
    ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 4,rowNum, 4+1,rowNum +1);
    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);//设置图片随单元移动调整大小
    drawing.createPicture(anchor, addPicture);
}else {
    cell4.setCellValue("");
}