在使用autosizeColumn的Apache POI 3.9中，同一列上的图像被拉伸 [英] In Apache POI 3.9 using autosizeColumn the image present on the same column getting stretched

问题描述

我在Excel图片下面有一张图片和一些文字。当我将autoSizeColumn（）应用于文本呈现的列时，图像也被拉伸。
我也将anchortype设置为2，但这并不是保护图像调整大小。
我在这里发布一些示例代码。

I have an image and some text below the image in an excel sheet. when i am applying autoSizeColumn() to the column where text present the image is also getting streched . i am also setting the anchortype to 2 but this is not protecting the image to resize. I am posting some sample code here.

public static void main(String[] args) {
    try{
    XSSFWorkbook book = new XSSFWorkbook();
    XSSFSheet sheet = book.createSheet("Test Sheet");
     InputStream is = new          FileInputStream("D:\\RPM_Eclipse_Workspaces\\B6.9\\00POI\\Chrysanthemum.jpg");
        byte[] bytes = IOUtils.toByteArray(is);
        int pictureIdx = book.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, 2,2,10,10);

        //Image should not get Resized while doing Autosize
        anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
        Picture pict = drawing.createPicture(anchor, pictureIdx);

        XSSFRow row = sheet.createRow(12);
        for(int i = 2 ; i < 11 ; i++){
            XSSFCell cell = row.createCell(i);
            cell.setCellValue("oval (although anchor's type is set to MOVE_DONT_RESIZE ). ... But the one way to ");

        }
       sheet.autoSizeColumn(2);
    book.write(new FileOutputStream(new File("D:\\auto.xlsx")));
    System.out.println("=== DONE ===");
    }catch (Exception e){

    }
}

推荐答案

POI使用 TwoCellAnchors 用于添加图片...所以有一些讨厌的反射，你可以添加一个带有 OneCellAnchor

POI uses TwoCellAnchors for adding pictures ... so with a bit of nasty reflections, you can add a Picture with a OneCellAnchor

在示例中， drawing.createAnchor（10,10,110,110,2,2,0,0）用于定位图像10x10从单元格的左上角（2,2），并将图片缩放到100x100像素，即110-10。

In the example the drawing.createAnchor(10, 10, 110, 110, 2, 2, 0, 0) is used to position the image 10x10 from the top left corner of the cell(2,2) and scale the picture to 100x100 pixels, i.e. 110-10.

（使用Libre Office 4.0测试， Excel Viewer 2010）

(tested with Libre Office 4.0, Excel Viewer 2010)

import java.io.*;
import java.lang.reflect.*;

import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveSize2D;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.*;

public class Automation {

    public static void main(String[] args) throws Exception {
        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet("Test Sheet");
        InputStream is = new FileInputStream("src/test/resources/smiley.jpg");
        byte[] bytes = IOUtils.toByteArray(is);
        int pictureIdx = book.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        XSSFDrawing drawing = sheet.createDrawingPatriarch();

        XSSFClientAnchor anchor = drawing.createAnchor(10, 10, 110, 110, 2, 2, 0, 0);

        createPicture(anchor, pictureIdx, drawing);

        XSSFRow row = sheet.createRow(12);
        for (int i = 2; i < 11; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue("oval (although anchor's type is set to MOVE_DONT_RESIZE ). ... But the one way to ");

        }
        sheet.autoSizeColumn(2);
        book.write(new FileOutputStream(new File("auto.xlsx")));
    }

    public static XSSFPicture createPicture(XSSFClientAnchor anchor, int pictureIndex, XSSFDrawing drawing)
    throws Exception
    {
        Method m = XSSFDrawing.class.getDeclaredMethod("addPictureReference", int.class);
        m.setAccessible(true);
        PackageRelationship rel = (PackageRelationship)m.invoke(drawing, (Integer)pictureIndex);

        long shapeId = 1000+drawing.getCTDrawing().sizeOfOneCellAnchorArray();
        CTOneCellAnchor ctAnchor = createOneCellAnchor(drawing, anchor);
        CTPicture ctShape = ctAnchor.addNewPic();

        m = XSSFPicture.class.getDeclaredMethod("prototype");
        m.setAccessible(true);
        CTPicture ctp = (CTPicture)m.invoke(null);
        ctShape.set(ctp);
        ctShape.getNvPicPr().getCNvPr().setId(shapeId);

        Constructor<XSSFPicture> picCon = XSSFPicture.class
            .getDeclaredConstructor(XSSFDrawing.class, CTPicture.class);
        picCon.setAccessible(true);

        XSSFPicture shape = picCon.newInstance(drawing, ctShape);
        Field f = XSSFShape.class.getDeclaredField("anchor");
        f.setAccessible(true);
        f.set(shape, anchor);

        m = XSSFPicture.class.getDeclaredMethod("setPictureReference", PackageRelationship.class);
        m.setAccessible(true);
        m.invoke(shape, rel);
        return shape;
    }

    public static CTOneCellAnchor createOneCellAnchor(XSSFDrawing drawing, XSSFClientAnchor anchor) {
        final int pixel2emu = 12700;
        CTOneCellAnchor ctAnchor = drawing.getCTDrawing().addNewOneCellAnchor();

        long cx = (anchor.getTo().getRowOff()-anchor.getFrom().getRowOff())*pixel2emu;
        long cy = (anchor.getTo().getColOff()-anchor.getFrom().getColOff())*pixel2emu;
        CTPositiveSize2D size = CTPositiveSize2D.Factory.newInstance();
        size.setCx(cx);
        size.setCy(cy);
        ctAnchor.setExt(size);

        ctAnchor.setFrom(anchor.getFrom());
        CTMarker m = ctAnchor.getFrom();
        m.setColOff(m.getColOff()*pixel2emu);
        m.setRowOff(m.getRowOff()*pixel2emu);
        ctAnchor.addNewClientData();
        try {
            Method mt = XSSFClientAnchor.class.getDeclaredMethod("setFrom", CTMarker.class);
            mt.setAccessible(true);
            mt.invoke(anchor, ctAnchor.getFrom());
        } catch (Exception e) {
            throw new RuntimeException("handle me", e);
        }

        return ctAnchor;
    }
}

这篇关于在使用autosizeColumn的Apache POI 3.9中，同一列上的图像被拉伸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！