Solved

C# Excel Shapes.AddPicture

Posted on 2010-08-31
22
3,496 Views
Last Modified: 2012-05-10
Visual Studeio 2008
I am creating an Excel file and exporting it to PDF.  In the beginning of my code, I add a picture to the top left of the worksheet.  I want to add the picture when I pagebreak.  I need to know how to figure out the "top" value (offset from the top of the page to the current location).  Can anyone assist with this?

Thank you in advance for your help.
Alex
Excel.Application myExcelApplication = new Excel.Application();

                Excel._Workbook myWorkbook;

                Excel._Worksheet mySheet;

                Excel.Range myRange;

                Excel.Range firstRange;

                int currentRow = 0;

mySheet.Shapes.AddPicture(Server.MapPath(imageFile),

                    Office.Core.MsoTriState.msoFalse,

                    Office.Core.MsoTriState.msoTrue,

                    left,

                    top,

                    width,

                    height);

Open in new window

0
Comment
Question by:alexvdb
  • 11
  • 10
22 Comments
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33571079
can you try something like this?
Excel.Range oRange = (Excel.Range)mySheet.Cells[yourSheetRow,yourSheetCol];

Image oImage = Image.FromFile(Server.MapPath(imageFile));

oRange.set_Item(1,1,oImage);

Open in new window

0
 

Author Comment

by:alexvdb
ID: 33576933
This is close.  I get the string below in the cell.

System.Drawing.Bitmap

Thank you for your help.
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33577100
if the comment helped you, you can rate the comment. Or you want to wait some more time?
0
 

Author Comment

by:alexvdb
ID: 33577168
Well, it hasn't solved my issue yet.  I am not getting the image.
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33577393
are you getting the image in the object "oImage" (in my previous code). Can you do a null check on that? if image is getting, i can check in the function set_Item.
0
 

Author Comment

by:alexvdb
ID: 33577462
I tested for not null and it is not.  Is there perhaps something I could do with Range.AutoFormat to format the object as an image?
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33577608
can you try this?
Excel.Range oRange = (Excel.Range)mySheet.Cells[yourSheetRow,yourSheetCol];

Image oImage = Image.FromFile(Server.MapPath(imageFile));

oRange.set_Item(1,1,oImage);

System.Windows.Forms.Clipboard.SetDataObject(oImage,true);

mySheet.Paste(oRange,imageFile);

Open in new window

0
 

Author Comment

by:alexvdb
ID: 33577711
I got the error below on the SetDataObject.
at System.Windows.Forms.Clipboard.SetDataObject(Object data, Boolean copy, Int32 retryTimes, Int32 retryDelay) at System.Windows.Forms.Clipboard.SetDataObject(Object data, Boolean copy) at InstructorDB.ACICSExcel.MakeExcelFile() in c:\TFS\Westwood Faculty\InstructorDB\ACICSExcel.aspx.cs:line 931
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33577905
oh, Web-forms cannot use the Clipboard I think. let me check any other options
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33578190
can you try this?
Excel.Application myExcelApplication = new Excel.Application();

                Excel._Workbook myWorkbook;

                Excel._Worksheet mySheet;

                Excel.Range myRange;

                Excel.Range firstRange;

                int currentRow = 0;

Excel.Range oRange = (Excel.Range)mySheet.Cells[yourSheetRow,yourSheetCol];

mySheet.Shapes.AddPicture(Server.MapPath(imageFile),

                    Office.Core.MsoTriState.msoFalse,

                    Office.Core.MsoTriState.msoTrue,

                  (float)oRange.Left,

		  (float)oRange.Top,

                    width,

                    height);

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:alexvdb
ID: 33578434
I'm getting a rather generic error message on the new code.

at InstructorDB.ACICSExcel.MakeExcelFile() in c:\TFS\Westwood Faculty\InstructorDB\ACICSExcel.aspx.cs:line 946

0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33578858
have you filled the values of row, column in yourSheetRow,yourSheetCol?
can you show me the latest function of yours?
0
 

Author Comment

by:alexvdb
ID: 33579003
Here it is.  I think the cast to float of the range.top and left is what is failing.

Excel.Range oRange = (Excel.Range)mySheet.Cells[currentRow, 1];
mySheet.Shapes.AddPicture(Server.MapPath(imageFile),
                    Office.Core.MsoTriState.msoFalse,
                    Office.Core.MsoTriState.msoTrue,
                    (float)oRange.Left,
                    (float)oRange.Top,
                    width,
                    height);
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33579071
can you cast it to double?
0
 
LVL 23

Accepted Solution

by:
Roshan Davis earned 500 total points
ID: 33579101
can you try this?
				ThisSheet.Shapes.AddPicture(imageFile,

					Microsoft.Office.Core.MsoTriState.msoFalse,

					Microsoft.Office.Core.MsoTriState.msoTrue,

					System.Convert.ToSingle(oRange.Left.ToString()),

					System.Convert.ToSingle(oRange.Top.ToString()),

					100,

					100);

Open in new window

0
 

Author Comment

by:alexvdb
ID: 33579246
Well, I got an error on thet too.  But, I checked the actual value of top.  It was 540.75.  WHen I tried to set the value of my float (top) I got a message indicateing I need to add a "F" suffix.  Itried this line of code and it worked.
top = 540.75F;
So I added "F" to the convert lines of code like below and it now works.
mySheet.Shapes.AddPicture(imageFile,
                    Microsoft.Office.Core.MsoTriState.msoFalse,
                    Microsoft.Office.Core.MsoTriState.msoTrue,
                    System.Convert.ToSingle(oRange.Left.ToString() + "F"),
                    System.Convert.ToSingle(oRange.Top.ToString() + "F"),
                    100,
                    100);

Thank you so muc for sticking with me on this.  I really appreciate it.
Alex
0
 

Author Comment

by:alexvdb
ID: 33579268
Check that I ran the wrong file.  I need to figure out how to get the "F" suffix in there somehow.
0
 

Author Comment

by:alexvdb
ID: 33579429
OK.  Here is the code that workds...
Single left = (Single)(1.5);
Single top = (Single)(1.5);
top = System.Convert.ToSingle(oRange.Top.ToString());
mySheet.Shapes.AddPicture(Server.MapPath(imageFile),
                    Office.Core.MsoTriState.msoFalse,
                    Office.Core.MsoTriState.msoTrue,
                    left,
                    top,
                    width,
                    height);

Thanks again.
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 33580946
I think you have used some of the code that is mentioned in my comment, that deserves some points :)
0
 

Author Comment

by:alexvdb
ID: 33581746
roshmon,  
You deserve all of the credit or points or what ever.  It was not my intention to claim anythin.  My only intention was to mark the last comment as the one that work by setting the top variable with System.Convert before using it in the AddShapes.  You get all of the kudos.  Can you tell me how I can correct this?

Thank you.
0
 

Author Closing Comment

by:alexvdb
ID: 33586445
roshmon, you were most helpful and I appreciate the fact that you stuck with me on this.

Thank you very much.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now