Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET

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

Rate me:
Please Sign up or sign in to vote.
4.99/5 (112 votes)
23 Jul 2009CPOL2 min read 83.9K   573   38   8
Way to export search results(BDC datalist/Enterprise Search) to Excel/PDF

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). 

C#
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: 

XML
//Application Definition

        <Method Name="ExportExcel">
          <Properties>
            </Parameter>
          </Parameters>
          <MethodInstances>
            <MethodInstance Name="ExportToExcel" 
		Type="GenericInvoker" ReturnParameterName="ExportPlantsExcel"/>
          </MethodInstances>
        </Method> 
C#
//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:

C#
    /// <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:

XML
<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

  • 24th July, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
United States United States
Johnson Manuel Devadoss ("Johnson Smith") is the Technical Lead/Administrator/Architect, and is responsible for Solution Architecture at global energy clients. Working exclusively with SharePoint Products and Technologies for 10+ years, he has had the pleasure to provide SharePoint training, mentoring, and consulting to dozens of to Fortune 50 clients.With over 10+ years experience in Microsoft Products and Technologies, he has a proven record of helping clients deliver global "Leading-Edge" technology integration solutions.

He has participating in the architectural design, development, customization and integration efforts of enterprise solutions involving Collaboration, Portals, Enterprise Content Management, Business Process and Forms, and Business Intelligence. Also, he is responsible for ensuring that the solutions are implemented in an efficient manner and ensures the satisfaction of the users by providing exceptional application technical support, by researching issues, and by problem solving and interaction with business users and vendors throughout all project phases.

Specialties:

Related areas of expertise are:

Technology Adoption & POC Management
Enterprise Portal Integration Solutions (SAP & MOSS)
Human Workflow & Systems Orchestration Solutions
Service Oriented Architectures (SOA) Implementations

Comments and Discussions

 
Suggestionsuggestion Pin
peter floyd 325-Mar-24 5:02
peter floyd 325-Mar-24 5:02 
Generalexporting search core webpart results to excel in moss 2007 Pin
Member 342211317-May-10 20:07
Member 342211317-May-10 20:07 
QuestionHow to use this solution Pin
sandeeprrao1-Sep-09 11:04
sandeeprrao1-Sep-09 11:04 
Generalgood Pin
zzx_12345610-Aug-09 19:42
zzx_12345610-Aug-09 19:42 
GeneralArkeos GENDOC Pin
Member 75483730-Jul-09 3:53
Member 75483730-Jul-09 3:53 
GeneralSource Code Pin
Deepak Ancil24-Jul-09 4:49
Deepak Ancil24-Jul-09 4:49 
Nice article, can u please share the code - Ohh, yeah i got it from your blog - Would appreciate if you can upload the code which you provided in your blog
GeneralRe: Source Code Pin
zzx_12345610-Aug-09 19:43
zzx_12345610-Aug-09 19:43 
GeneralNice Article Pin
Maverick Team24-Jul-09 4:45
Maverick Team24-Jul-09 4:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.