ArcDesktop 10.5: Adding Excel object to mxd with arcobjects

735
6
01-03-2018 04:57 AM
JanaMartin
New Contributor III

Background:

I'm using ArcdDesktop 10.5 with Arcobjects (C#) for development.

Problem:

I'm working on automated Chart creation for a client. They need a table of data to be added to the chart, and the excel sheet gives the most flexibility in styling.

I have created the excel with C#, and am trying to add this object to a MXD that is loaded in memory. I believe that I'm close but need some help to get it completed.

I'm able to load the excel sheet into memory but am not able to 'add' or 'update' the OLEFrame with the content.

I have tried adding a new OLEFrame with a given Envelope for placement and I've tried to find the OLEFrame from the IGraphicsContainer of the MXD and update the content. I keep getting the following error:

"Error HRESULT E_FAIL has been returned from a call to a COM component. at ESRI.ArcGIS.ArcMapUI.OleFrameClass.Load(IStream pstm)"

Here is the code that I'm using.

var outputFilePath = @"...";

IMemoryBlobStream MemStream = new MemoryBlobStreamClass();
IPageLayout pageLayout = TheMXD.PageLayout;
IGraphicsContainer graphicsContainer = pageLayout as IGraphicsContainer;
graphicsContainer.Reset();


IElement pElement = graphicsContainer.Next();

while (pElement != null)
{
IElementProperties ElementProps = pElement as IElementProperties;
if (ElementProps.Name.Contains("Excel"))
{
      MemStream.LoadFromFile(outputFilePath);
      IPersistStream oleSteam = pElement as IPersistStream;

      oleSteam.Load(MemStream);
      break;
}

pElement = graphicsContainer.Next();
}

((IActiveView)TheMXD.PageLayout).PartialRefresh(esriViewDrawPhase.esriViewGraphics, null, null);

6 Replies
DuncanHornby
MVP Frequent Contributor

Just to get some clarification, are you trying to load the data into arcmap? If so you want to add the table as a standalone table. Your code suggests you are trying to embed the whole worksheet as some sort of graphic element to page layout. Is that really your intention? 

0 Kudos
JanaMartin
New Contributor III

I'm trying to mirror the same functionality as the  "Insert -> Object" that is built into Arcmap. The basic insert feature class as table, does not give enough style functionality. Adding the Excel as an object will give a nice formatted 'table'.

DuncanHornby
MVP Frequent Contributor

Got interested in this problem but failed to get anywhere with it. I developed the following VBA code but it fails in one of two places:

Public Sub test()
Dim pMXD As IMxDocument
Set pMXD = ThisDocument
Dim pPageLayout As IPageLayout
Set pPageLayout = pMXD.PageLayout
Dim pGraphicsContainer As IGraphicsContainer
Set pGraphicsContainer = pPageLayout
pGraphicsContainer.Reset
Dim pElement As IElement
Dim sPath As String
Let sPath = "C:\Temp\Test1\SCT_OUTPUT\Excel\tblPolygonFeaturesMasterPolygonSingle__ee.xls"
Dim pBlobStream As IBlobStream
Set pBlobStream = New MemoryBlobStream
pBlobStream.LoadFromFile sPath
Dim pOleFrame As IOleFrame
Set pOleFrame = New OleFrame
Dim pPersistStream As IPersistStream
Set pPersistStream = pOleFrame
pPersistStream.Load pBlobStream
pOleFrame.Open ' This line throws an error, if you comment out it gets to the AddElement and bombs out
Set pElement = pOleFrame
Dim pElementProp As IElementProperties3
Set pElementProp = pElement
Debug.Print pElementProp.Type ' Returns "OLE Frame"
pGraphicsContainer.AddElement pElement, 0 ' This line throws an error
pMXD.ActiveView.Refresh
End Sub‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

As a side thought looking at your code you are replacing an existing OLE Frame in your page layout. If you use the GUI then you can only ever CREATE an OLE object, I could see no way of changing the data source. So even if you got the code working I wonder if you can only create rather than replace?

I did a load of searching on the internet and could find zero examples of how one would programatically insert an OLE Frame onto the page layout!  Would be good if someone from ESRI produced an example? The code snippets I found were about accessing an existing OLE object on the page layout, not creating or updating.

Sorry this is the best I could do! 

0 Kudos
JanaMartin
New Contributor III

Thank for taking the time to look at this.

I originally started by adding in a new OLEFrame, setting the envelope etc, but it would be null everytime that I added content to the Frame.

I'm going to open a support ticket with ESRI on this one. Given the API information this should be possible, but like you said there are zero examples of this.

My guess is that there is other non arcobjects operations going on in ArcMap. Whether or not esri will give that information to me is the other question.

I'll post any findings here.Hornbydd

GintautasKmieliauskas
Regular Contributor

Hi Jana,

I have found that some functionality is available only using MFC environment:

https://desktop.arcgis.com/en/arcobjects/latest/net/webframe.htm#IOleFrame_CreateOleClientItem.htm

So you need to create VC++ library which can create you IElement of IOLEFrame type. How to do that you could find on GitHub sample:

https://github.com/Microsoft/VCSamples/tree/master/VC2010Samples/MFC/ole/MFCBind

It uses the same Insert object dialog as ArcMap does.

0 Kudos
JanaMartin
New Contributor III

Thank you for finding this information. I never really thought to look in the other apis. I'll take a look into this and post my final solution.

0 Kudos