One of the down parts in Business Data Catalog is lack of export the data. Also, most of the folks expecting this functionality as following thread. http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2201460&SiteID=17.
The solution is writing an HttpHandler to do the job. The nice thing about using the HttpHandler approach is that it pretty much did one thing for me: it took care of transforming the data into a format that Excel can open.How I actually gathered my data was a task that could be offloaded to some other data access component in the application, and the differentiating query values could be retrieved by using QueryString property variables. Using QueryString property variables makes reusing the solution quite easy. I've create an GenericInvoker MethodInstance to fetch the records as follows
<Method Name="ExportPlants">
<Properties>
……..
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="ExportToExcel" Type="GenericInvoker" ReturnParameterName="ExportPlantsExcel"/>
</MethodInstances>
</Method>
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";
….
}
Session object has 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, string methodInstance)
{
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>
Hope this helps!