locked
How to get Data from Excel Range values. RRS feed

  • Question

  • Hi,

    Our Requirement:

    We have to Select specific Range in Excel and display that table in Outlook Email Body using programmatically.

    we have tried Excel.Range.Copy() method

    But when we Try to Read from Clipboard we are getting null but manually we are able to paste anywhere.

    Attaching Sample project

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Windows.Forms;
    using System.Windows.Media;
    using System.Windows.Media.Imaging;
    using System.IO;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                Excel.Workbook excelWorkbook = null;
                Excel.Application excel = new Excel.Application();
    
                excelWorkbook = excel.Workbooks.Open("C:\\Temp1\\SGHCardsInputSheet.xlsx");
                Excel.Worksheet ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Worksheets[1];
    
                Excel.Range ShtRange = null;
                ShtRange = ExcelWorkSheet.UsedRange.Rows;           
                
                ShtRange.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap);
                if (Clipboard.GetDataObject() != null)
                {
                    FileStream img = new FileStream(Environment.CurrentDirectory + "\\" + "RCPD.jpg", FileMode.Create);
                    BitmapSource image = (BitmapSource)Clipboard.GetDataObject().GetData(DataFormats.Bitmap, true);
                    JpegBitmapEncoder jpg = new JpegBitmapEncoder();
                    jpg.Frames.Add(BitmapFrame.Create(image));
                    jpg.Save(img);
                    img.Close();
                }
            }
        }
    }
    

     

    • Moved by CoolDadTx Thursday, September 21, 2017 1:55 PM Office related
    Thursday, September 21, 2017 11:48 AM

All replies

  • Have you attempted to see if the data in the range is an image ?

    if (Clipboard.ContainsImage())


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, September 21, 2017 12:36 PM
  • Hi Sravanthi chepuri,

    I would suggest you paste the picture/range to the mail body directly. We could get the inspector of a mail and then get the word editor. We could paste the picture/range to the mail body.

    Here is the example.

    Excel.Workbook excelWorkbook = null;
    
                Excel.Application excel = new Excel.Application();
    
                excelWorkbook = excel.Workbooks.Open(@"C:\Users\v-guaxu\Desktop\Test.xlsx");
    
                Excel.Worksheet ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Worksheets[1];
    
                Excel.Range ShtRange = null;
    
                ShtRange = ExcelWorkSheet.UsedRange.Rows;
    
                //ShtRange.Copy();
    
                ShtRange.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap);
    
                Outlook.Application olApp = new Outlook.Application();
    
                Outlook.MailItem mailItem = olApp.CreateItem(Outlook.OlItemType.olMailItem);
    
               
    
                Outlook.Inspector ins = mailItem.GetInspector;
    
                Word.Document doc;
    
                doc = ins.WordEditor;
    
                if (doc.Application.Selection ==null) {
    
                    doc.Select();
    
                }
    
                doc.Application.Selection.Paste();
    
                mailItem.Display();

    Best Regards,

    Terry

    Friday, September 22, 2017 5:05 AM
  • Hi,

    Thanks for the reply....

    when we try this approarch we are getting below exception.

    <style type="text/css">body { margin: 0 0 0 0; padding:0 0 0 0 } td,div { font-family:Segoe UI;font-size:9pt;vertical-align:top } /* Copyright IBM Corp. 2015 All Rights Reserved. */ body { margin: 0 0 0 0; padding:0 0 0 0; overflow:hidden; } .transcript { background-color:#d2d2d2; } .messageBlock { padding-left:10px; padding-right:10px; margin-bottom:3px } .message { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre; white-space:pre-wrap;} .messageCont { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap;} .other { font-size:11px;color:#39577a;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .myself { font-size:11px;color:#da8103;font-style:normal;font-weight:bold;font-style:normal;float:left; width:95px; white-space:nowrap; } .otherCont { font-size:8px;text-align:right; color:#39577a;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .myselfCont { font-size:8px;text-align:right; color:#da8103;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .system { font-size:11px; word-wrap:break-word;color:#da8103;font-style:normal;font-weight:normal; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap; } .showTimestamp { padding-left:20px; font-size:11px; float:right; color:#999999;font-style:normal;font-weight:normal; } .other1 { font-size:11px; color:#ac2000;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont1 { font-size:8px;text-align:right; color:#ac2000;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other2 { font-size:11px; color:#3c9fa8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont2 { font-size:8px;text-align:right; color:#3c9fa8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other3 { font-size:11px; color:#e25614;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont3 { font-size:8px;text-align:right; color:#e25614;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other4 { font-size:11px; color:#0b6ac8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont4 { font-size:8px;text-align:right; color:#0b6ac8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other5 { font-size:11px; color:#b23290;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont5 { font-size:8px;text-align:right; color:#b23290;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other6 { font-size:11px; color:#02e7c7;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont6 { font-size:8px;text-align:right; color:#02e7c7;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .other7 { font-size:11px; color:#5b3284;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; } .otherCont7 { font-size:8px;text-align:right; color:#5b3284;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; } .highlight { background-color:#bed6f8; } .datestamp { padding-right:0px; font-size:11px; cursor:default; margin-bottom:1px; background-color:#c0c0c0; width:100%; float:left; text-align:right; color:#ffffff; font-weight:bold; font-style:italic; } #chatAlert { float:left; border-bottom:1px solid #E8D091; padding:6px; width:100%; color:#A5754C; } #chatAlertImage { float:left; } #chatAlertText { float:left; margin-left:6px; margin-right:10px;} #chatAlertClose { float:right; margin-right:10px; padding-right:6px; margin-top:0px; } #chatAlertText a { color:#A5754C; } #chatAlertText a:hover { color:#A5754C; text-decoration:none; } .tsDisplay { display:block }.dsDisplay { display:none }</style>
    Operation aborted (Exception from HRESULT: 0x80004004 (E_ABORT)) at Line ins.WordEditor.

    Please suggest how to resolve this..

    Thanks in advance...


    Monday, September 25, 2017 11:09 AM
  • Take a look at your last reply, it's unreadable with all the formatting codes exposed. Please edit your post so it's in a readable format.

    This is how I see it currently.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, September 25, 2017 11:32 AM
  • Hi,

    I just copied Exception but when I submit all that message came.

    we are getting below exception at line   doc =ins.WordEditor;

    Operation aborted (Exception from HRESULT: 0x80004004 (E_ABORT)) 

    thanks

    Tuesday, September 26, 2017 7:26 AM
  • Hi Sravanthi chepuri

    The WordEditor property is only valid if the IsWordMail method returns True and the EditorType property is olEditorWord . I would suggest you check these properties before getting the WordEditor.

    Here is the code example.

    if (ins.IsWordMail() && ins.EditorType ==Outlook.OlEditorType.olEditorWord)
    
                {
    
                    doc = ins.WordEditor;
    
                    if (doc.Application.Selection == null)
    
                    {
    
                        doc.Select();
    
                    }
    
                    doc.Application.Selection.Paste();
    
                }
    
                else {
    
                    MessageBox.Show("Failed to get the WordEditor");
    
                    MessageBox.Show("The inspector's IsWordMail is "+ins.IsWordMail().ToString());
    
                    MessageBox.Show("The inspector's IsWordMail is "+ins.EditorType.ToString());
    
                }

    Besides, what's the version of your Office? Have you tried to updated your office to latest version?

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 27, 2017 8:58 AM