insert image into excel with Apache-poi

Question

well,I modified my code to eliminate other factors:

package com.shangzhu.drt;

import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;

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

/**
 * Created by lixiaoming on 2017/6/26.
 */
public class ImageTest2 {

    private static void insertImageWithPOI() throws Exception {
        XSSFWorkbook wwb = new XSSFWorkbook();
        XSSFSheet ws = wwb.createSheet("sheet0");

        BufferedImage image = ImageIO.read(new File("D:/poi.png"));
        ByteArrayOutputStream baps = new ByteArrayOutputStream();
        ImageIO.write(image,"png",baps);

        int pictureIdx = wwb.addPicture(baps.toByteArray(), Workbook.PICTURE_TYPE_PNG);

        XSSFDrawing drawing = ws.createDrawingPatriarch();
        XSSFCreationHelper helper = wwb.getCreationHelper();
        XSSFClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(1);
        anchor.setRow1(1);

        Picture picture = drawing.createPicture(anchor, pictureIdx);
        picture.resize();

        File excelFile = new File("D:/POI.xlsx");
        OutputStream ops = new FileOutputStream(excelFile);
        wwb.write(ops);
    }

    public static void main(String[] args) {
        try {
            insertImageWithPOI();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

below is the picture("D:/poi.png") in the code: D:/poi.png

I don't think the source code which is dealing image has problems,But I don't know what I missed

Try only setting top left cell in anchor and then resizing the picture relative to its native size using `pict.resize();` as described in https://poi.apache.org/spreadsheet/quick-guide.html#Images. — Axel Richter, Jun 26 '17 at 09:09
@AxelRichter thanks for your advice,in fact,I have tried this method before but still get a not ideal result. — lbsky, Jun 26 '17 at 09:22
Well as said `pic.resize();` will resize the picture to its native size. If this is not "ideal", then the native picture is not ideal. What is `DataChart` in your code? — Axel Richter, Jun 26 '17 at 09:44
@AxelRichter Thank you for your reply,DataChart contains the byte data for the image, and I can also get the number of rows and columns that the image occupies — lbsky, Jun 26 '17 at 09:59
"can also get the number of rows and columns that the image occupies": Well that's funny since in `Excel` sheet rows may have different height and columns may have different width. So I suspect the `chart.getHeight()` and `chart.getWidth()` will not fit exactly into the rows and columns of the resulting `Excel` sheet. So to answer we need code of your `DataChart` class. — Axel Richter, Jun 26 '17 at 10:06
@AxelRichter Well, I changed my code to eliminate other factors,we can have a test with following code:`code`hello`code` — lbsky, Jun 26 '17 at 10:29
Will test this ca. 4 hours after now, I'm busy now. But question: Why getting the bytes from `BufferedImage` why not getting the bytes directly from `File`? — Axel Richter, Jun 26 '17 at 11:09

score 2 · Answer 1 · answered Jun 26 '17 at 17:32

2

I confirm that there is a problem when default column size is used. XSSFPicture.resize needs calculating the column widths in pixels to get the XSSFClientAnchor Col2 and the Dx2. As long as default column size is used, then this calculation seems to be wrong.

A workaround could be defining explicit column sizes before using XSSFPicture.resize. Then the calculation of the column widths in pixels seems to be correct.

In your code:

...
        Picture picture = drawing.createPicture(anchor, pictureIdx);

        for (int c=0; c<20; c++) ws.setColumnWidth(c, 11*256);
        picture.resize();
...

answered Jun 26 '17 at 17:32

Axel Richter

56,077
6
60
87

I debugged the code `picture.resize()`,then I found somewhere may be the key.It uses the method `setPreferredSize()`,Here are two important ways to calculate pixels->`sheet.getColumnWidthInPixels(col2)` and `getRowHeightInPixels(sheet,row2)` – lbsky Jun 27 '17 at 06:19
for example,`public float getColumnWidthInPixels(int columnIndex) { float widthIn256 = (float)this.getColumnWidth(columnIndex); return (float)((double)widthIn256 / 256.0D * 7.001699924468994D); }`,I don't know why there is going to use 7.0,but when I changed it to 8d,I get an ideal result.of couse I changed the coefficient of height too – lbsky Jun 27 '17 at 06:26
@lbsky: I don't believe that this is the screw to turn. If you do that, then the calculation of explicit setted column widths will be wrong. And the height calculation is correct, so why changing that? I believe `XSSFSheet` - `public int getDefaultColumnWidth()` is wrong because it confounds `CTSheetFormatPr.getBaseColWidth` with `CTSheetFormatPr.getDefaultColWidth`. But I'm not part of the `apache poi` developer team and everytime I look at the code, then I don't want to be. – Axel Richter Jun 27 '17 at 07:03

insert image into excel with Apache-poi

1 Answers1

Linked