Drawing directly to Excel cell canvas

Question

Is there a way to draw an image directly to the canvas that contains the cells of Excel using VSTO without having to insert a shape?

I have seen it done and I know that it is possible I just can't seem to be able to find a source of information.

    Range cell = sheet.Cells[1, 1];

    var border = cell.Borders[XlBordersIndex.xlDiagonalDown];
    border.Weight = XlBorderWeight.xlThin;
    border.LineStyle = XlLineStyle.xlContinuous;

This code snippet seems to produce a drawing inside the canvas of the cell. I need to access the layer that this draws onto.

Answer 1

The example in the video (which you had not posted at the time of my original answer) does not "draw to the canvas," instead the program generates OLE objects and embeds them inside the spreadsheet's cells. This can be seen when the secondary application is used to actually draw the molecules. If there was any doubt whether or not it uses OLE, the name should put them to rest -- it is called MarvinOLE. There is some documentation, including how to interact with other vendors' OLE objects, here. Since OLE is a COM technology, and Excel's interfaces are all COM-based, this works.

If you are 100% committed to learning how to "draw an image directly to the canvas", please ignore the rest of this answer. Since you want to use C# (a .NET language) to interact with Excel's COM interfaces, you'll need to familiarize yourself with the Primary Interop Assemblies. Their reference can be found here. If you are only targeting the newest Excel/Office versions, you may want to look at this SO answer, discussing the "improved" Interop interface. Here's a blogger with an example that might help you get started.

Search for "excel interop oleobjects" or something similar and you will find numerous examples, SO questions, documentation, etc.

Answer 2

Unfortunately, there is no way to draw on the canvas directly, at least through the public API, and you definitely can't use the GDI+ drawing engine to draw which you propose when you want to reuse code from drawing Windows Forms controls.

I am looking into the possibility of drawing using Windows directly, but that doesn't seem possible too, since Excel doesn't keep a handle for every cell, to it is hard to know which cell to draw. Also, this drawing wouldn't be persistent, which is a major drawback in my opinion.

---

You found this movie yourself, and I think there is a viable option there. If you could render your drawings to an image, you could insert that drawing inline through a formula (note that pictures are still shapes, which you didn't like, but I guess we can make a exception for those).

If OLE objects are used or necessary for rendering, I would advise not to use those. They are too 'old school' in my opinion and won't work with anything other than Office on your Windows Desktop.

Answer 3

This is a very basic VBA script that draws a rectangle and fills it with the desired image. Instead of being bound to a cell, its position and size is relative to the worksheet. You'll need to modify to fit your exact needs, but it seemed like your stumbling block was moving beyond a cell-based positioning scheme.

Sub PlaceImage()

Dim ws As Excel.Worksheet
Dim sh As Excel.Shape

   Set ws = ThisWorkbook.Worksheets(1) 'use an index number or string as needed
   Set sh = ws.Shapes.AddShape(msoShapeRectangle, 100, 60, 400, 300) 'x-pos, y-pos, width, height

   sh.Fill.Visible = msoTrue
   sh.Fill.UserPicture "C:\Users\Public\Pictures\Sample Pictures\Desert.jpg" 'filename of image
   sh.Line.Visible = msoFalse 'removes the outline

   Range("A1").Select

End Sub