6

How do I insert an image (of type Image) into a specific cell in a Excel sheet

taperSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item("Taper");

Microsoft.Office.Interop.Excel.Range cell = GetMyPictureCELL(taperSheet);

Image myImage = new Image();
RenderTargetBitmap bmp;

bmp = new RenderTargetBitmap((int)this.Width, (int)this.Height, 96, 96, PixelFormats.Pbgra32);
bmp.Render(myViewPort);

myImage.Source = bmp;
myImage.Stretch = Stretch.Uniform;

and now ? I was hoping for 

cell.Add(myImage)

But I assume it is not that easy.

/Stefan

Thanks for your input doitgood

The following code works for me

In my case my Image source is a viewport (myViewPort) The placement of the image is determinated by cell

try
{
    Image myImage = new Image();
    RenderTargetBitmap bmp;
    PngBitmapEncoder encoder;
    string fileName;
    System.IO.Stream stream;
    object missing = System.Reflection.Missing.Value; 
    Microsoft.Office.Interop.Excel.Picture pic = null;
    Microsoft.Office.Interop.Excel.Pictures p = null;

    bmp = new RenderTargetBitmap((int)this.Width, (int)this.Height, 96, 96, PixelFormats.Pbgra32);
    bmp.Render(myViewPort);

    myImage.Source = bmp;
    myImage.Stretch = Stretch.Uniform;

    fileName = System.IO.Path.GetTempFileName();
    stream = System.IO.File.OpenWrite(fileName);

    encoder = new PngBitmapEncoder();
    encoder.Frames.Add(BitmapFrame.Create(bmp));
    encoder.Save(stream);
    stream.Close();

    p = taperSheet.Pictures(missing) as Microsoft.Office.Interop.Excel.Pictures; 
    pic = p.Insert(fileName, missing); 
    pic.Left = cell.Left;
    pic.Top = cell.Top;

}
catch { }
H.B.
  • 166,899
  • 29
  • 327
  • 400
Stefan Olsson
  • 617
  • 3
  • 16
  • 32

2 Answers2

15

Try this:

object missing = System.Reflection.Missing.Value;
Excel.Range picPosition = GetPicturePosition(); // retrieve the range for picture insert
Excel.Pictures p = yourWorksheet.Pictures(missing) as Excel.Pictures;
Excel.Picture pic = null;
pic = p.Insert(yourImageFilePath, missing);
pic.Left = Convert.ToDouble(picPosition .Left);
pic.Top = Convert.ToDouble(picPosition .Top);
pic.Placement = // Can be any of Excel.XlPlacement.XYZ value

And don't forget to release all that stuff!

Cengiz Araz
  • 680
  • 9
  • 17
Benoit Drapeau
  • 624
  • 1
  • 6
  • 13
  • 2
    Is it not possible to "send" the picure directly to Excel, does it have to go "over" the file system? – Stefan Olsson Sep 14 '11 at 08:34
  • I updated my answer to allow placement of the image in an Excel.Range. For inserting a picture not coming from the file system, I'll make some tests and provide results if relevant. Note : that code has not been tested under every scenario... You can also take a look at this post that suggest using Worksheet.Shapes.AddPicture instead of WorkSheet.Pictures http://stackoverflow.com/questions/521979/is-is-possible-to-insert-an-image-into-an-excel-cell-via-com – Benoit Drapeau Sep 14 '11 at 08:39
  • 1
    From what I can find, it is impossible to insert a picture other than from the file system but if you want to buy third party components, Aspose and Synfusion are both doing what you need without the need of Excel being installed. – Benoit Drapeau Sep 14 '11 at 08:48
  • I'd give you 100 points if I could for this answer. It lead me to resolving a standing over a month old problem using a .COM version of putting images in Excel using the Interop. It worked great on development side but would bomb on live side since server didn't have Office installed. With this work around, everything worked! – Lukas Jul 02 '12 at 21:39
2

Or Try this:

private void PlacePicture(Image picture, Range destination)
{
    Worksheet ws = destination.Worksheet;
    Clipboard.SetImage(picture);
    ws.Paste(destination, false);
    Pictures p = ws.Pictures(System.Reflection.Missing.Value) as Pictures;
    Picture pic = p.Item(p.Count) as Picture;
    ScalePicture(pic, (double)destination.Width, (double)destination.Height);
}

private void ScalePicture(Picture pic, double width, double height)
{
    double fX = width / pic.Width;
    double fY = height / pic.Height;
    double oldH = pic.Height;
    if (fX < fY)
    {
        pic.Width *= fX;
        if (pic.Height == oldH) // no change if aspect ratio is locked
            pic.Height *= fX;
        pic.Top += (height - pic.Height) / 2;
    }
    else
    {
        pic.Width *= fY;
        if (pic.Height == oldH) // no change if aspect ratio is locked
            pic.Height *= fY;
        pic.Left += (width - pic.Width) / 2;
    }
}
Jürgen Tschandl
  • 21
  • 4