Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EPPlus and Excel's Camera Tool

Tags:

c#

epplus

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?

like image 349
sports Avatar asked Oct 01 '17 18:10

sports


People also ask

What is the camera tool in Excel?

You can use Excel's Camera tool to add images of Excel tables, charts, ranges, and single cells to your dashboard. The tool allows you to place images anywhere on the dashboard and size them however you like. Even better, any time a change is made to the source data, the images are updated in Excel.


1 Answers

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

like image 66
Jeremy Thompson Avatar answered Oct 26 '22 12:10

Jeremy Thompson