How to insert picture into excel using MemoryStream?

Question

Below are two methods, one uses MemoryStream and the other uses real file on the disk.

        public void InsertImage(long x, long y, long? width, long? height, string sImagePath, WorksheetPart wsp)
    {
        try
        {
            DrawingsPart dp;
            ImagePart imgp;
            WorksheetDrawing wsd;

            ImagePartType ipt;
            switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower())
            {
                case "png":
                    ipt = ImagePartType.Png;
                    break;
                case "jpg":
                case "jpeg":
                    ipt = ImagePartType.Jpeg;
                    break;
                case "gif":
                    ipt = ImagePartType.Gif;
                    break;
                default:
                    return;
            }

            if (wsp.DrawingsPart == null)
            {
                //----- no drawing part exists, add a new one

                dp = wsp.AddNewPart<DrawingsPart>();
                imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp));
                wsd = new WorksheetDrawing();
            }
            else
            {
                //----- use existing drawing part

                dp = wsp.DrawingsPart;
                imgp = dp.AddImagePart(ipt);
                dp.CreateRelationshipToPart(imgp);
                wsd = dp.WorksheetDrawing;
            }

            using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
            {
                imgp.FeedData(fs);
            }

            int imageNumber = dp.ImageParts.Count<ImagePart>();
            if (imageNumber == 1)
            {
                Drawing drawing = new Drawing();
                drawing.Id = dp.GetIdOfPart(imgp);
                wsp.Worksheet.Append(drawing);
            }

            NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
            nvdp.Id = new UInt32Value((uint)(1024 + imageNumber));
            nvdp.Name = "Picture " + imageNumber.ToString();
            nvdp.Description = "";
            DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
            picLocks.NoChangeAspect = true;
            picLocks.NoChangeArrowheads = true;
            NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
            nvpdp.PictureLocks = picLocks;
            NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
            nvpp.NonVisualDrawingProperties = nvdp;
            nvpp.NonVisualPictureDrawingProperties = nvpdp;

            DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
            stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

            BlipFill blipFill = new BlipFill();
            DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
            blip.Embed = dp.GetIdOfPart(imgp);
            blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
            blipFill.Blip = blip;
            blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
            blipFill.Append(stretch);

            DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
            DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
            offset.X = 0;
            offset.Y = 0;
            t2d.Offset = offset;
            Bitmap bm = new Bitmap(sImagePath);

            DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();

            if (width == null)
                extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
            else
                extents.Cx = width * (long)((float)914400 / bm.HorizontalResolution);

            if (height == null)
                extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
            else
                extents.Cy = height * (long)((float)914400 / bm.VerticalResolution);

            bm.Dispose();
            t2d.Extents = extents;
            ShapeProperties sp = new ShapeProperties();
            sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
            sp.Transform2D = t2d;
            DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
            prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
            prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
            sp.Append(prstGeom);
            sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

            DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
            picture.NonVisualPictureProperties = nvpp;
            picture.BlipFill = blipFill;
            picture.ShapeProperties = sp;

            Position pos = new Position();
            pos.X = x * 914400 / 72;
            pos.Y = y * 914400 / 72;
            Extent ext = new Extent();
            ext.Cx = extents.Cx;
            ext.Cy = extents.Cy;
            AbsoluteAnchor anchor = new AbsoluteAnchor();
            anchor.Position = pos;
            anchor.Extent = ext;
            anchor.Append(picture);
            anchor.Append(new ClientData());
            wsd.Append(anchor);
            wsd.Save(dp);
        }
        catch (Exception ex)
        {
            throw ex; // or do something more interesting if you want
        }
    }

    //use memorystream
public void InsertImage(long x, long y, long? width, long? height, MemoryStream ms, WorksheetPart wsp)
        {
            try
            {
                DrawingsPart dp;
                ImagePart imgp;
            WorksheetDrawing wsd;

            ImagePartType ipt = ImagePartType.Jpeg;

            if (wsp.DrawingsPart == null)
            {
                //----- no drawing part exists, add a new one

                dp = wsp.AddNewPart<DrawingsPart>();
                imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp));
                wsd = new WorksheetDrawing();
            }
            else
            {
                //----- use existing drawing part

                dp = wsp.DrawingsPart;
                imgp = dp.AddImagePart(ipt);
                dp.CreateRelationshipToPart(imgp);
                wsd = dp.WorksheetDrawing;
            }
            Bitmap bitmap = new Bitmap(ms);
            imgp.FeedData(ms);

            int imageNumber = dp.ImageParts.Count<ImagePart>();
            if (imageNumber == 1)
            {
                Drawing drawing = new Drawing();
                drawing.Id = dp.GetIdOfPart(imgp);
                wsp.Worksheet.Append(drawing);
            }

            NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
            nvdp.Id = new UInt32Value((uint)(1024 + imageNumber));
            nvdp.Name = "Picture " + imageNumber.ToString();
            nvdp.Description = "";
            DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
            picLocks.NoChangeAspect = true;
            picLocks.NoChangeArrowheads = true;
            NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
            nvpdp.PictureLocks = picLocks;
            NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
            nvpp.NonVisualDrawingProperties = nvdp;
            nvpp.NonVisualPictureDrawingProperties = nvpdp;

            DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
            stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

            BlipFill blipFill = new BlipFill();
            DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
            blip.Embed = dp.GetIdOfPart(imgp);
            blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
            blipFill.Blip = blip;
            blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
            blipFill.Append(stretch);

            DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
            DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
            offset.X = 0;
            offset.Y = 0;
            t2d.Offset = offset;


            DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
            //Bitmap bitmap = new Bitmap(ms);
            if (width == null)
                extents.Cx = (long)bitmap.Width * (long)((float)914400 / bitmap.HorizontalResolution);
            else
                extents.Cx = width * (long)((float)914400 / bitmap.HorizontalResolution);

            if (height == null)
                extents.Cy = (long)bitmap.Height * (long)((float)914400 / bitmap.VerticalResolution);
            else
                extents.Cy = height * (long)((float)914400 / bitmap.VerticalResolution);

            bitmap.Dispose();

            t2d.Extents = extents;
            ShapeProperties sp = new ShapeProperties();
            sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
            sp.Transform2D = t2d;
            DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
            prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
            prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
            sp.Append(prstGeom);
            sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

            DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
            picture.NonVisualPictureProperties = nvpp;
            picture.BlipFill = blipFill;
            picture.ShapeProperties = sp;

            Position pos = new Position();
            pos.X = x * 914400 / 72;
            pos.Y = y * 914400 / 72;
            Extent ext = new Extent();
            ext.Cx = extents.Cx;
            ext.Cy = extents.Cy;
            AbsoluteAnchor anchor = new AbsoluteAnchor();
            anchor.Position = pos;
            anchor.Extent = ext;
            anchor.Append(picture);
            anchor.Append(new ClientData());
            wsd.Append(anchor);
            wsd.Save(dp);
        }
        catch (Exception ex)
        {
            throw ex; // or do something more interesting if you want
        }
    }

If I invoke the first method(use real file on the disk), it's ok, I can insert my picture into excel file. But if I read the file into memorystream and invoke method2, I can see the picture rectangle with error message.

So my question is how can I insert picture into excel via memorystream? Because I won't create too many files on disk.

Use a library like EPPplus to simplify this. [Check this similar question](http://stackoverflow.com/questions/11588704/adding-images-into-excel-using-epplus). The code required is 2 lines: `var picture = ws.Drawings.AddPicture(index.ToString(), logo); picture.SetPosition(index * 5, 0, 2, 0);` — Panagiotis Kanavos, Feb 09 '17 at 12:44
I'm sorry I think I can't use EPPlus, because I create my excel file by openxml in memory, if I use EPPlus, I should modify too many codes — user2155362, Feb 09 '17 at 12:57
The Bitmap(ms) constructor call reads data from the stream. So the Stream.Position is no longer at the position 0. Inevitably the FeedData(ms) call is doomed to read garbage. Adding `ms.Position = 0;` is a workaround but that can in turn make the bitmap misbehave, the codec used by the Bitmap class sometimes reads the pixel data lazily. Consider not creating the bitmap object. — Hans Passant, Feb 09 '17 at 14:11
@user2155362 which is exactly what EPPlus does, only better. You won't have to modify anything, just delete what is already built-in - almost everything. For example, you can fill a sheet from a collection with a single `sheet.LoadFromCollection(myCollection)`. Same with `LoadFromDataTable`. — Panagiotis Kanavos, Feb 09 '17 at 14:39

score 0 · Answer 1 · edited Feb 09 '17 at 13:56

I believe you need to create bitmap image data from stream first

There's a solution already for that here on Stack Overflow: Byte Array to Bitmap Image I copy-paste the code from the solution:

int w= 100;
int h = 200;
int ch = 3; //number of channels (ie. assuming 24 bit RGB in this case)

byte[] imageData    = new byte[w*h*ch]; //you image data here
Bitmap bitmap       = new Bitmap(w,h,PixelFormat.Format24bppRgb);
BitmapData bmData   = bitmap.LockBits(new System.Drawing.Rectangle(0, 0, bitmap.Width, bitmap.Height), ImageLockMode.ReadWrite, bitmap.PixelFormat);
IntPtr pNative      = bmData.Scan0;
Marshal.Copy(imageData,0,pNative,w*h*ch);
bitmap.UnlockBits(bmData);

You may want to add a link to the SO answer this came from – Metoniem Feb 09 '17 at 14:02 — Metoniem, Feb 09 '17 at 14:02

score 0 · Accepted Answer · answered Feb 10 '17 at 02:47

OK, I think I have found solution.

I change memorystream parameter to string, and convert it to memorystream like below:

MemoryStream ms = new System.IO.MemoryStream(System.Convert.FromBase64String(str))

Now, it works.

I learn it from open xml sdk 2.5 productivity tool.

How to insert picture into excel using MemoryStream?

2 Answers2