6

Is it possible to use Excel's Camera Tool with EPPlus? that is: programatically copy a range of cells from one sheet and paste them as a drawing object into another sheet?

Update:

I noticed that Excel's Camera Tool simply creates a picture with a formula. The formula is the range of cells to be watched/observed by Excel. If any of these cells change the picture is updated by Excel.

But with EPPlus is not possible to inject a formula to a Picture object, eg:

var picture = worksheet.Drawings.AddPicture("picture", (FileInfo)null);
picture.SetPosition(1, 0, 1, 0);
picture.Formula = "A1:D9"; // ...there is no "Formula" property for ExcelPicture object

Any workaround?

sports
  • 7,851
  • 14
  • 72
  • 129
  • My opinion is that it's not possible with EPPlus (without modifying its source code), because it just doesn't know about camera tool and does not emit required xml when saving file. – Evk Dec 08 '17 at 12:37

1 Answers1

1

Bad news when I record a VBA Macro and replay it, it doesn't work. This is the syntax thats generated:

Range("A2").Select
Selection.Copy
ActiveSheet.Shapes.AddShape(, 355.5, 32.25, 72#, 72#).Select
ActiveSheet.Shapes.Range(Array("Picture 3")).Select
Application.CutCopyMode = False

Working with Images in Excel via automation is limited. You are pretty much limited to Shapes (or shudder - clipboard):

Set shp = ws.Shapes.AddPicture("C:\You.png", msoFalse, msoTrue, l, t, w, h)
shp.Name = strPic
shp.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
shp.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue

What I am suggesting is create a screenshot of the selected cell and workaround it that way. 

'Select the cells you want to copy to image
Range("A2").Select
'Copy selected cells contents to clipboard as image
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
'Choose destination
Range("B3").Select
'Paste
ActiveSheet.Paste

'Restore previous clipboard to memory

Hopefully the above will be enough to help you get it working in EPPPlus.

ps Converting VBA to C# is really easy, and it should be trivial converting the above to EPPPlus: https://stackoverflow.com/a/34055947/495455

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321