6

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.

Christo S. Christov
  • 2,268
  • 3
  • 32
  • 57

3 Answers3

3

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.

Community
  • 1
  • 1
ExactaBox
  • 3,235
  • 16
  • 27
  • Okay,that sounds good enough for me. You'll recieve the bounty, the answer to the question tho will go to Patrick since he also proposed the OLE objects as a solution. Thank you :) – Christo S. Christov Jan 29 '15 at 12:01
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.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
  • @Chris: It seems he is embedding OLE object. Please, don't to there. They are too 'old school' in my opinion and won't work with anything other than Office on your Windows Desktop. – Patrick Hofman Jan 28 '15 at 08:58
  • @Chris: Updated the section under the line. – Patrick Hofman Jan 28 '15 at 09:13
  • I don't think the structures in this example aren't shapes.They are not interactable and seem to be "embedded" inside the cell. I have taken a look onto a few projects that use this approach.(dissasembly *whoops*) . They seem to use some kind of low level interaction with Excel.That's what I'm after.Unfortunetely I cannot see what it is since it is obfuscated. – Christo S. Christov Jan 28 '15 at 09:26
  • Are they using OLE perhaps? Do you have a location of the project? – Patrick Hofman Jan 28 '15 at 09:27
  • I'm not really allowed to provide a project to you, to dissemble but if you are curious I'm sure you'll think of something :D . I don't know if theyare OLE objects, I'm not very familiar with it. – Christo S. Christov Jan 28 '15 at 10:56
  • @Chris: I meant the one you found. I thought you had the project mentioned there. I will try to find something for you. – Patrick Hofman Jan 28 '15 at 11:12
  • Thanks Patrick I really appreciate your thought on the problem! :) – Christo S. Christov Jan 28 '15 at 12:36
  • @Chris: It is not very nice of you giving away the bounty already. I was working on a solution for you and now the incentive is away already... – Patrick Hofman Jan 29 '15 at 12:12
  • If you come up with a solution I'll be happy to give you a bounty. – Christo S. Christov Jan 29 '15 at 12:13
  • @Chris: That is not the point. I don't need the rep. It is just to let you know how others could feel about it. – Patrick Hofman Jan 29 '15 at 12:14
  • I did award it because you said you would try to find something and I haven't heard from you, thus I assumed you haven't found a good example for the situation and I assumed that the best it can get is the answer I recieved from ExactaBox. Anyway as I said, if you provide an example you deserve an award which I will be happy to provide. Having your example here will also provide a further reference for anybody who is wondering about this problem. – Christo S. Christov Jan 29 '15 at 12:17
  • @Chris: Okay, no problem. I am just quite busy on my work, so didn't have a lot of time to make it working. If I find the time to complete it I will post it. – Patrick Hofman Jan 29 '15 at 12:18
  • Okay,take it easy there brother ;) . I'm currently developing my solution based on shapes but I'm planning to leave a window there for me to replace the functionality whenever I get a bit more experience with OLE objects(alas,I wish I didn't have to get familiar with that). – Christo S. Christov Jan 29 '15 at 12:23
  • Hey, if you cannot provide an example I would be happy to have a book about it or something else, so if you know any good titles please do share. Thanks – Christo S. Christov Feb 02 '15 at 21:16
-2

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
ExactaBox
  • 3,235
  • 16
  • 27
  • I'm looking for something that allows me to draw directly on the canvas of the cell. What you are pointing out is a solution with shapes that I already have and don't need.But thanks for the effort. – Christo S. Christov Jan 26 '15 at 19:58
  • can I ask what exactly you're trying to implement that specifically requires the canvas? – ExactaBox Jan 26 '15 at 20:13
  • OP asks "without having to insert a shape". You propose: using a shape. Also, VBA isn't C#. – Patrick Hofman Jan 27 '15 at 09:23
  • @ExactaBox I'm doing custom drawing on a custom .NET control using Drawing Visuals. I load the control and draw the stuff that I need to draw, and then I want to insert the that inside a cell canvas. – Christo S. Christov Jan 27 '15 at 12:21
  • There is no possibility in Excel cells for drawing something with the default API. Excel cells can contain values or formulas, nothing else. The cell interior can contain a background color or predefined pattern, but not a picture. Your code snipped also do not draw something. It only switches a predefined cell border line to on. – Axel Richter Jan 28 '15 at 06:45