apachePoi Excel 导出图片（支持多个图片到一个单元格）_dx1:图片左边界距离单元格左边框像素值,

基于SpringBoot Web项目

maven 依赖：

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

1
2
3
4
5
6
7
8
9
10
11

demo

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;

public class Test {
    public static void main(String[] args) {
        HSSFWorkbook book=new HSSFWorkbook();
        HSSFSheet sheet=book.createSheet();
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        BufferedImage bufferedImage = null;
        try {
            String[] imgs = {"D:\\photos\\792b6604637e40de2f0c800db6648122.jpeg",
            				 "D:\\photos\\d701e5d7f2f32d27ff3cb8e919e56b61.jpeg",
            				 "D:\\photos\\e1885b439f188ef9ed1ab209af35b3ee.jpeg" };
            // 计算边距
            int mar = 10 + 10 + (imgs.length - 1) * 10;
            // 大致平均值,每个图片宽度(1023为每个单元格总比，）
            int ave = (1023 - mar) / imgs.length;
            //设置图片位于表格的单元格下标
            int hang=0;
            int lie=0;
            HSSFRow row = sheet.createRow(hang);
            //设置单元个宽高，单元格宽高限制了图片的宽高
            row.setHeight((short)(100*10));
            sheet.setColumnWidth(lie,4800*imgs.length);
            for (int i = 0; i < imgs.length; i++) {
                File file = new File(imgs[i]);
                bufferedImage = ImageIO.read(file);
                ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
                ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
                HSSFClientAnchor hSSFClientAnchor=null;
                //dx1:图片左上角x坐标, dy1:图片左上角y坐标范, dx2:图片右下角x坐标, dy2:图片右下角y坐标。坐标x轴总比1023，坐标y轴总比255,图片排列样式根据左上角与右下角坐标比例排列
                hSSFClientAnchor = new HSSFClientAnchor(10 * (i + 1) + ave * i, 10,(10 + ave) * (i + 1), 245, (short) lie, hang, (short) lie, hang);
                patriarch.createPicture(hSSFClientAnchor,book.addPicture(byteArrayOutputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
            }
            FileOutputStream outputStream = new FileOutputStream("C:\\Users\\LENOVO\\Desktop\\photo\\template\\test.xls");
            book.write(outputStream);
            outputStream.close();
        } catch (Exception io) {
            io.printStackTrace();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

项目实战：

先抽取方法

/**
     *
     * @param workbook
     * @param patriarch
     * @param fileMetadataList 文件元数据列表
     * @param hang 第几行
     * @throws IOException
     */
    private void generatePicture(HSSFWorkbook workbook,HSSFPatriarch patriarch,List<SzzcFileMetadata> fileMetadataList,int hang) throws IOException{
        //设置图片位于表格的单元格下标
        int lie=2;
        BufferedImage bufferedImage = null;
        if (CollectionUtils.isEmpty(fileMetadataList)){
            return;
        }
        for (int i = 0; i < fileMetadataList.size(); i++) {
            // 计算边距
            int mar = 10 + 10 + (fileMetadataList.size() - 1) * 10;
            // 大致平均值,每个图片宽度(1023为每个单元格总比，）
            int ave = (1023 - mar) / fileMetadataList.size();
            SzzcFileMetadata szzcFileMetadata = fileMetadataList.get(i);
            String path = szzcFileMetadata.getBaseDir()+ szzcFileMetadata.getFileUrl();
            File file = new File(path);
            try {
                bufferedImage = ImageIO.read(file);
                if(ObjectUtils.isEmpty(bufferedImage)){
                    log.warn("读取文件异常：bufferedImage为空：{}, fileUrl->{}",path);
                    continue;
                }
            } catch (IOException e) {
                log.error("读取文件异常：异常信息是：{}, fileUrl->{}",e.getMessage(),path);
                continue;
            }
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

            try {
                ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
            } catch (IOException e) {
                log.error("===ImageIO.write===$$$$$$$$$$$$$$$$$$$===文件路径： {},异常信息：{}",path,e.getMessage());
                continue;
            }
            /* dx1:图片左边界距离单元格左边框像素值,
             * dy1:图片上边界距离单元格上边框像素值,
             * dx2:图片右边界距离单元格右边框像素值（负数）,
             * dy2:图片下边界距离单元格下边框像素值（负数）,
             * col1:列下标（0开始），
             * row1:行下标（0开始），
             * col2:列下标（1开始），
             * row2:行下标（1开始）。*/
            HSSFClientAnchor  anchor = new HSSFClientAnchor(10 * (i + 1) + ave * i, 10,(10 + ave) * (i + 1), 245, (short) lie, hang, (short) lie, hang);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOutputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            byteArrayOutputStream.close();
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

具体调用：

    /**
     * 导出数据
     * @param response
     * @param data 从数据库查询，封装的数据列表
     * @throws IOException
     */
    public  void exportData(HttpServletResponse response, List<SzzcFeedBackExportVO> data) throws IOException{
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 650);

        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setBorderBottom(BorderStyle.THIN);//下边框
        headStyle.setBorderLeft(BorderStyle.THIN);//左边框
        headStyle.setBorderTop(BorderStyle.THIN);//上边框
        headStyle.setBorderRight(BorderStyle.THIN);//右边框

        //声明列对象
        HSSFCell cell;
        //创建标题
        List<String> titleList = Arrays.asList("标识号", "反馈内容","图片","反馈人","反馈时间");
        for (int i = 0; i < titleList.size(); i++) {
            sheet.setColumnWidth(i, 10000);
            sheet.setDefaultRowHeight((short) 255);
            cell = row.createCell(i);
            cell.setCellValue(titleList.get(i));
            HSSFFont font = workbook.createFont();
            //设置excel数据字体颜色
            font.setColor(Font.COLOR_NORMAL);
            //设置excel数据字体大小
            font.setFontHeightInPoints((short) 15);
            headStyle.setFont(font);
            cell.setCellStyle(headStyle);
        }

        HSSFCellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setWrapText(true);//自动换行
        HSSFFont fontContext = workbook.createFont();
        fontContext.setColor(Font.COLOR_NORMAL);
        fontContext.setFontHeightInPoints((short) 15);
        dataStyle.setFont(fontContext);
        for (int j = 0; j < data.size(); j++) {
            SzzcFeedBackExportVO szzcFeedBackExportVO = data.get(j);
            int hang  = j+1;
            //填充数据
            row = sheet.createRow(hang);
            row.setHeight((short) 2000);
            cell = row.createCell(0);
            cell.setCellValue(szzcFeedBackExportVO.getId());
            cell.setCellStyle(dataStyle);
            cell = row.createCell(1);
            cell.setCellValue(szzcFeedBackExportVO.getContext());
            cell.setCellStyle(dataStyle);
            cell = row.createCell(3);
            cell.setCellValue(szzcFeedBackExportVO.getCreateUserName());
            cell.setCellStyle(dataStyle);
            cell = row.createCell(4);
            cell.setCellValue(szzcFeedBackExportVO.getCreateTime());
            cell.setCellStyle(dataStyle);
            //处理图片
            this.generatePicture(workbook,patriarch,szzcFeedBackExportVO.getFileMetadataList(),hang);
        }

        //第四步，将.xsl文件导出到respones的输出流，下载到浏览器
        //(1)设置响应类型
//        response.setContentType("application/octet-stream;charset=UTF-8");
//        response.addHeader("Content-Disposition", "attachment;filename=反馈记录表.xls");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("反馈记录" + ".csv", "UTF-8"));
        //(2)获取输出流
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.close();
        //关闭资源
        out.flush();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

导出效果：

参考文章：
https://blog.csdn.net/fan___/article/details/127669050