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

问题描述

我在 Excel 工作表中的图像下方有一个图像和一些文本.当我将 autoSizeColumn() 应用于显示文本的列时，图像也被拉伸.我还将锚点类型设置为 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屋！