Page 1 of 1

Adding images to XLS

PostPosted: Tue Nov 01, 2011 12:33 am
by mjrobbins
Hi Jason

Can you give me any hints on how to add an image to an XLSX file?

Many thanks

Re: Adding images to XLS

PostPosted: Tue Nov 01, 2011 12:42 am
by mjrobbins
To elaborate a bit more...

I've created a Pic from a BinaryPart, as with PowerPoint, and now I need to add it to the WorksheetPart. With Powerpoint I called:

Code: Select all
slidePart.getJaxbElement().getCSld().getSpTree().getSpOrGrpSpOrGraphicFrame().add(pic);


But what's the equivalent in WorksheetPart?

Re: Adding images to XLS

PostPosted: Tue Nov 01, 2011 3:38 am
by mjrobbins
Okay, it now looks like I'm completely on the wrong track... the Pic class that I was using for PPTX is specific to that format from what I can make of the documentation. So I'm completely lost. Is it a Drawing I need to create? If so how, and where is it added?

Re: Adding images to XLS

PostPosted: Tue Nov 01, 2011 3:40 am
by mjrobbins
Right, investigating further I've found that BinaryPartAbstractImage has a bunch of undocumented methods (since the 2.7 javadoc isn't available on this site). Looking at those I've figured out that BinaryPartAbstractImage.createImagePart gets me the image part, but the method is completely undocumented, gives no clue what it actually does, and I've still no idea what to do with the image part once I've actually got it...

Re: Adding images to XLS

PostPosted: Tue Nov 01, 2011 5:06 am
by mjrobbins
Okay, so my code now is basically just:

Code: Select all
BinaryPartAbstractImage image = BinaryPartAbstractImage.createImagePart(package,worksheetPart,bytes);


That gives me an ImagePart, which is added to the package but doesn't display anywhere.

How do I now make the image appear on the worksheet?

Re: Adding images to XLS

PostPosted: Wed Nov 02, 2011 4:04 am
by mjrobbins
So, after a day of trial and error I've managed to get something working, but I still have a problem.

The code below works when I use a TwoCellAnchor and uncomment the CTMarker for 'to', but I need to use a OneCellAnchor in order to let the image I add display at it's full size. For some reason, switching to CTOneCellAnchor and removing the 'to' field causes Excel to throw a wobbly on loading. Any ideas?

Hopefully the code below can be of some use to someone...

Code: Select all
       
                BinaryPartAbstractImage image = null;
                try
                {
                    image = BinaryPartAbstractImage.createImagePart(pkg, worksheetPart, bytes);
                }
                catch (Exception e)
                {
                    e.printStackTrace();
                }
                PartName drawingPartName = new PartName(worksheetPart.getPartName().getName().replace("worksheets/sheet","drawings/drawing"));
                Drawing drawing = (Drawing)pkg.getParts().get(drawingPartName);
                List<Object> objects = drawing.getJaxbElement().getEGAnchor();
                String rid = "rId" + (objects.size() + 1);
                CTOneCellAnchor anchor = new CTOneCellAnchor();

                //anchor.setEditAs(STEditAs.ONE_CELL);
                CTMarker from = new CTMarker();
                //CTMarker to = new CTMarker();
                from.setCol(column);
                from.setRow(row);
                from.setColOff(0L);
                from.setRowOff(0L);
                //to.setCol(16);
                //to.setRow(16);
                anchor.setFrom(from);
                //anchor.setTo(to);
                CTPicture pic = new CTPicture();
                CTPictureNonVisual nvPicPr = new CTPictureNonVisual();
                CTNonVisualDrawingProps nvpr = new CTNonVisualDrawingProps();
                nvpr.setId(objects.size() + 2);
                String name = image.getPartName().getName();
                name = name.substring(name.lastIndexOf("/")+1);
                nvpr.setName(name);
                nvpr.setDescr(name);
                nvPicPr.setCNvPr(nvpr);
                CTPictureLocking ctPictureLocking = new CTPictureLocking();
                ctPictureLocking.setNoChangeAspect(true);
                CTNonVisualPictureProperties nvpp = new CTNonVisualPictureProperties();
                nvpp.setPicLocks(ctPictureLocking);
                nvPicPr.setCNvPicPr(nvpp);
                pic.setNvPicPr(nvPicPr);
                CTBlipFillProperties blipProps = new CTBlipFillProperties();
                CTStretchInfoProperties props = new CTStretchInfoProperties();
                CTRelativeRect rect = new CTRelativeRect();
                props.setFillRect(rect);
                blipProps.setStretch(props);
                CTBlip blip = new CTBlip();
                blip.setEmbed(rid);
                blip.setCstate(STBlipCompression.PRINT);
                blipProps.setBlip(blip);
                pic.setBlipFill(blipProps);
                CTShapeProperties sppr = new CTShapeProperties();
                ImageSize imageSize = image.getImageInfo().getSize();
                CTPoint2D off = new CTPoint2D();
                off.setX(0);
                off.setY(0);
                CTPositiveSize2D ext = new CTPositiveSize2D();
                ext.setCx(imageSize.getWidthMpt());
                ext.setCy(imageSize.getHeightMpt());
                CTTransform2D xfrm = new CTTransform2D();
                xfrm.setOff(off);
                xfrm.setExt(ext);
                sppr.setXfrm(xfrm);
                CTPresetGeometry2D prstGeom = new CTPresetGeometry2D();
                prstGeom.setPrst(STShapeType.RECT);
                prstGeom.setAvLst(new CTGeomGuideList());
                sppr.setPrstGeom(prstGeom);
                pic.setSpPr(sppr);
                anchor.setPic(pic);
                CTAnchorClientData data = new CTAnchorClientData();
                anchor.setClientData(data);
                drawing.getJaxbElement().getEGAnchor().add(anchor);
                Relationship rel = new Relationship();
                rel.setId(rid);
                rel.setType("http://schemas.openxmlformats.org/officeDocument/2006/relationships/image");
                rel.setTarget("../media/" + name);
                drawing.getRelationshipsPart().addRelationship(rel);
                RelationshipsPart relPart = drawing.getRelationshipsPart();
                pkg.getParts().remove(relPart.getPartName());
                pkg.getParts().put(relPart);
                pkg.getParts().remove(drawing.getPartName());
                pkg.getParts().put(drawing);

Re: Adding images to XLS

PostPosted: Wed Nov 02, 2011 8:43 pm
by jason
mjrobbins wrote:I've found that BinaryPartAbstractImage has a bunch of undocumented methods (since the 2.7 javadoc isn't available on this site). Looking at those I've figured out that BinaryPartAbstractImage.createImagePart gets me the image part, but the method is completely undocumented, gives no clue what it actually does


I'd encourage you to download the source code and look at it. That is the value of open source. Javadoc will always be a poor substitute for source code. Beyond the Getting Started guide, the source code is also the best way to understand how to use docx4j, and contains a lot of stuff you can adapt to your purposes.

You can checkout the source code from svn, or browse it online. Here is the relevant class (trunk tip):

http://www.docx4java.org/svn/docx4j/tru ... Image.java

If you really want javadoc, you can use the 2.6.0 javadoc, generate the 2.7.0 javadoc yourself (using ant or maven), or download the 2.7.1 Javadoc (and source code for that matter) from Maven Central.

cheers .. Jason

Re: Adding images to XLS

PostPosted: Mon May 27, 2013 10:13 pm
by jason