Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 23 Jul 2009
Rate this:
Please Sign up or sign in to vote.
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). 

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

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

Share

About the Author

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

 
Generalexporting search core webpart results to excel in moss 2007 PinmemberMember 342211317-May-10 20:07 
Hi,
is there any way to export search core results to Excel without writing custom cide(using object model).
apu

QuestionHow to use this solution PinmemberMember 27434001-Sep-09 11:04 
Generalgood Pinmemberzzx_12345610-Aug-09 19:42 
GeneralArkeos GENDOC PinmemberMember 75483730-Jul-09 3:53 
GeneralSource Code PinmemberDeepak Ancil24-Jul-09 4:49 
GeneralRe: Source Code Pinmemberzzx_12345610-Aug-09 19:43 
GeneralNice Article PinmemberMaverick Team24-Jul-09 4:45 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140921.1 | Last Updated 24 Jul 2009
Article Copyright 2009 by Johnson Manuel Devadoss ("Johnson Smith")
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid