Export BDC(Business Data Catalog) Data to Excel/PDF

• Download results - 1.35 KB

Introduction

One of the coolest features in MOSS 2007 is Business Data Catalog which provides an easy way to integrate multiple business data from back-end server applications, such as SAP or Siebel, with your corporate portal to provide rich solutions for end users without writing any code. It is like an inter-operable solution where you can integrate any other data sources into MOSS 2007 environment. You register business data exposed in databases or through Web services in the Business Data Catalog by creating metadata that describes the database or Web service. The Business Data Catalog then uses this metadata to make the right calls into the data source to retrieve the relevant data. 

Background

One of the hottest requirements from customers is that they would like to export their search results from BDC. The business data entities are available for use by any of the following business data features: Business Data WebParts, Business Data List, Business Data Search, and Business Data in User Profiles. So customers want to export these business data entities to Excel or PDF. But unfortunately, Microsoft doesn't provide any facility to do export to any format. So here I'm proposing a way to export the business data entities to Excel/PDF. 

Using the Code

There are two ways to export the business entities to Excel:

1. using HTTPHandler
2. using Generic Invoker (normally this option would be helpful when you do custom webpart development)

The first option would be useful when you would like to export the enterprise search results (which were developed using Business Data) and User Profile Information from some other systems (i.e., SAP R/3, PeopleSoft). 

public void ProcessRequest(HttpContext context)
{
    context.Response.ContentType = "application/vnd.ms-excel";
    NamedLobSystemInstanceDictionary ObjInstances = 
		ApplicationRegistry.GetLobSystemInstances();
    LobSystemInstance ObjInstance = ObjInstances["Give your Instance Name"];
    Entity ObjEntity = ObjInstance.GetEntities()["Give your Entity Name"];
    MethodInstance ObjMethodInst = ObjEntity.GetFinderMethodInstance();
    IEntityInstanceEnumerator ObjEntityInstanceEnumerator = 
	(IEntityInstanceEnumerator)prodEntity.Execute(ObjMethodInst, ObjInstance);
    while (ObjEntityInstanceEnumerator.MoveNext())
    {
        IEntityInstance IE = prodEntityInstanceEnumerator.Current;
        foreach (Field f in prodEntity.GetFinderView().Fields)
        {
            context.Response.Write(IE[f]);
            context.Response.Write('\t');
        }
        context.Response.Write('\n');
    }
}

The second option is invoking GenericInvoker to execute the MethodInstance and then export return results to Excel as follows: 

//Application Definition

        <Method Name="ExportExcel">
          <Properties>
            </Parameter>
          </Parameters>
          <MethodInstances>
            <MethodInstance Name="ExportToExcel" 
		Type="GenericInvoker" ReturnParameterName="ExportPlantsExcel"/>
          </MethodInstances>
        </Method> 

//WebPart
        protected override void CreateChildControls()
        {
            lbExcel = new HyperLink();
            lbExcel.Text = "Export To Excel";
            lbExcel.NavigateUrl = SPContext.Current.Web.Url + 
			@"/Export.ashx?format=excel&instance=ExcelInstance";
            lbExcel.Load += new EventHandler(lbExcel_Load);
            lbExcel.ImageUrl = "/_layouts/images/ICXLS.GIF";
 
            lbPdf = new HyperLink();
            lbPdf.Text = "Export To PDF";
            lbPdf.Load += new EventHandler(lbPdf_Load);
            lbExcel.NavigateUrl = SPContext.Current.Web.Url + 
			@"/Export.ashx?format=pdf&instance=PDFInstance";
            lbExcel.ImageUrl = "/_layouts/images/pdf.gif";
           
                ….
        }

The session object has to hold the object result from GenericInvoker MethodInstance:

    /// <summary>
    /// Holds PDF Instance
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void lbPdf_Load(object sender, EventArgs e)
    {
        System.Web.HttpContext.Current.Session["PDFInstance"] =
    BdcHelpers.ExecuteGenericInvoker(lobSystemInstance,
    entityName, "ExportToExcel");
    }

    /// <summary>
    /// Holds Excel Instance
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void lbExcel_Load(object sender, EventArgs e)
    {
       System.Web.HttpContext.Current.Session["ExcelInstance"] =
    Helpers.ExecuteGenericInvoker(lobSystemInstance,
    entityName, "ExportToExcel");
    }
    /// <summary>
    /// Overloads Execute Method for MethodInstance of specified LOBSystem
    /// </summary>
    /// <param name="lobSystemInstance">LOB System</param>
    /// <param name="entityName">Name Of an Enity</param>
    /// <param name="methodInstance">GenericInvoker Method Instance Name</param>
    /// <returns>Object</returns>
    public static Object ExecuteGenericInvoker
(string lobSystemInstance, string entityName, stringmethodInstance)
    {
        NamedLobSystemInstanceDictionary instances =
        ApplicationRegistry.GetLobSystemInstances();
        LobSystemInstance instance = instances[lobSystemInstance];
        Entity entity = instance.GetEntities()[entityName];

        MethodInstance methInst = entity.GetMethodInstances()[methodInstance];

       return entity.Execute(methInst, instance);
    }

Finally, you have to register your handler as follows:

<httpHandlers>
    <remove verb="GET,HEAD,POST" path="*" />
    <add verb="*" path="Export.ashx" type="BDCWebParts.ExportHandler, BDCWebParts" />
</httpHandlers> 

For more details, please have a look at my blog http://sharepointblogs.com/lovedjohnysmith.

History

• 24^th July, 2009: Initial post