BDC - Is there a way to Export data shown in BDC to Excel

Question

I know BDC is to get data from an external database, but is there an OOB funtionality that allows you to export BDC data filtered in SharePoint site to Excel...

 

If not I can do it as an Action Item (i guess!)

 

Any lead would be of great help

 

Thank you

Answer 1

Hi.

  You can use DCL(Data Connect Library) to export data to Excel through MOSS.

 

Answer 2

I'm also trying to figure out how to export Business Data Catalog data to excel.

 

Can you elaborate on how BDC can use Data Connect Library?

 

Thanks.

 

Answer 3

Hi.

  Maybe you mistake my point, For MOSS, BDC & DCL is 2 fuction, you can use BDC to get

data from DB or you can use DCL to run a queries. Which kind method you like according to

your request.

  For example, I want Client check not "on-time pickup", I use BDC to get data and show on MOSS. but My Boss need use Pivot table to analysis some data, I wrote a queries and upload to DCL, So my boss can access by himself. I don't need install the queries one by one.

Answer 4

Ok, DCL is not the solution for me then. I wanted add an "export to excel button" in my BDC list so that users can export what they see in the BDC list to excel.

 

Answer 5

 

We have the same request: an "export to Excel" button on a Business Data List webpart, which exports the list data. 

 

Is there any way to accomplish this with MOSS 2007 now?

 

Answer 6

I also need to do this....  display data in a Business Data List Web Part and allow users to filter list results then export the filtered list to excel.  I had thought that this could be accomplished using a custom action in the Business Data Actions Web Part but have been unable to find an example of what the code/xml should look like.  The only thing I can think of is to try to find the code/xml that currently exists in default SharePoint files that controls that action from other menus in lists and document libraries.  Haven't tried it yet though...

 

Has anyone found the code to do this?

 

Answer 7

.

 

I've recently talked to someone from Microsoft, and he said the export to excel feature is not available yet. He did say to try http://www.thesug.org/Pages/default.aspx and someone may be able to provide a work-around.

Answer 8

There is a sample Excel User Defined Function contained within the latest release of the MOSS 2007 SDK, once installed you can find it at:

C:\Program Files\2007 Office System Developer Resources\Samples\Business Data Catalog\ExcelServicesUdf

The idea of this though is to use it in a workbook published to Excel Services.

HTH

Nick

Answer 9

This will display the data in an Excel Web Access Web Part on the SharePoint site using Excel Services (data source is BDC) but would there be a way to save the data to a user's desktop as an .xls file?

 

Answer 10

Can you use the 'Take Snapshot' functionality to export the data to an excel workbook the same way you can do with normal excel services published spreadsheets?

 

I'm sorry I've not got time to try it myself at the moment so just thinking out loud.

 

Nick

Answer 11

Josh Madagan wrote a custom webpart to accomplish this.  You can see his blog at http://www.thesug.org/blogs/joshm/default.aspx

 

 

Answer 12

http://www.sharepointblogs.com/lovedjohnysmith/default.aspx - Here's a one way to do that....

 

Answer 13

Hi,

 

Can u plesae provide the contents of C:\Program Files\2007 Office System Developer Resources\Samples\Business Data Catalog\ExcelServicesUdf to me.Because i am unable to find this folder in my server.

 

Thanks;

 

dinesh

Answer 14

 

The files would not be in your server, they come from the Office SharePoint Server 2007 SDK -

 

http://www.microsoft.com/downloads/details.aspx?FamilyId=6D94E307-67D9-41AC-B2D6-0074D6286FA9

 

Kind Regards

 

Phill

Answer 15

I used a slight variation of the HttpHandler method outlined over a year ago. (I thought the MSDN article with video was helpful for creating this.) By default I use the Finder method as this is what the BDCList shows. However, via the extended BDCList web part I created, it can be configured to use any method and the Finder is the default. The session parameters pass the LOB instance, method and fields being shown in the view to be exported. I wasn't able to get the HttpHandler to format the data returned from BDC as suggested above. As such, that is done manually in the HttpHandler. This is OK, but I use a tab character to delimit fields which breaks down if fields contain that character. Does anyone know a more robust way to format the data for Excel?

The other issue I'm having with the export is that after calling the HttpHandler and exporting the data, the original web page is dead. I.e. if the export button is hit again or a field column to sort, nothing happens. The page needs to be reloaded for it to work again. I'd imagine I'm not setting something properly in the Response in the HttpHandler, but haven't been able to figure that out. Any thoughts?

Below is the handler code in case anyone is interested. It would be trivial to change it to take the parameters from the URL to make it easy to create arbitrary buttons or links to pull data from any BDC method and export it to Excel. It would still suffer the second issue above. Also, if anyone is interested in the actual web part, I'd be happy to post that code. It is based on the code from Josh Madagan already referenced in this thread and simply creates an "Export" button and setts the session parameters the HttpHandler is expecting. Basically just one additional method from what Josh has shown.

HttpHandler:

<%@ Assembly Name="mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" %>

<%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Assembly Name="System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" %>

<%@ Assembly Name="Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ WebHandler Language="C#" Class="ExcelDumper" %>

using System;

using System.Web;

using System.Web.UI;

using System.Web.SessionState;

using System.Collections;

using System.Collections.Generic;

using Microsoft.SharePoint;

using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;

using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;

using Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db;

/// <summary>

/// A generic HttpHandler that calls a BDC Method and exports the

/// returned data to Excel. The LOB Method to be called is passed into

/// this HttpHandler via session parameters as follows:

///

///      "Entity"        The LOB Entity from which to dump the data

///      "Instance"      The Instance in the LOB Entity

///      "Method"        The Method in the LOB Instance

///      "FilterFields"  If true, only dump fields that are passed in via Fields

///      "Fields"        The fields which are exported when filtering is on

///

/// The "\t" character is used to delimit fields during the export. If the data

/// contains tabs, this will break down. There is probably a more foolproof way

/// to do this.

/// </summary>

public class ExcelDumper : IHttpHandler, IReadOnlySessionState

{

public bool IsReusable {

get { return false; }

}

public void ProcessRequest(HttpContext context) {

SPSite siteColl = SPContext.Current.Site;

SPWeb site = SPContext.Current.Web;

Boolean first = true;

context.Response.ContentType = "application/vnd.ms-excel";

try

{

NamedLobSystemInstanceDictionary instances = ApplicationRegistry.GetLobSystemInstances();

LobSystemInstance instance = instances[(string)context.Session["Entity"]];

Entity entity = instance.GetEntities()[(string)context.Session["Instance"]];

MethodInstance methodInstance = entity.GetMethodInstances()[(string)context.Session["Method"]];

bool filterFields = (bool)context.Session["FilterFields"];

DbEntityInstanceEnumerator ie = (DbEntityInstanceEnumerator)entity.Execute(methodInstance, instance);

List<string> displayedFields = (List<string>)context.Session["Fields"];

if (displayedFields == null)

{

// If we don't have any fields to display, don't bother filtering.

filterFields = false;

}

while (ie.MoveNext())

{

DbEntityInstance dbInstance = (DbEntityInstance)ie.Current;

string s = dbInstance.ToString();

Microsoft.Office.Server.ApplicationRegistry.MetadataModel.View v = dbInstance.ViewDefinition;

if (first)

{

//

// The first row are the titles. If a localized display name is set, use that.

// If not, just use the default display name.

//

FieldCollection titles = v.Fields;

foreach (Field title in titles)

{

// If filtering is on, only display the title if it's in the exportedFields list

if (filterFields)

{

if (displayedFields.Contains(title.Name))

{

string titleName;

if (title.ContainsLocalizedDisplayName)

{

titletitleName = title.LocalizedDisplayName;

}

else

{

titletitleName = title.DefaultDisplayName;

}

context.Response.Write(titleName);

context.Response.Write('\t');

}

}

else

{

// Filtering is not on, export everything.

string titleName;

if (title.ContainsLocalizedDisplayName)

{

titletitleName = title.LocalizedDisplayName;

}

else

{

titletitleName = title.DefaultDisplayName;

}

context.Response.Write(titleName);

context.Response.Write('\t');

}

}

context.Response.Write("\n");

first = false;

}

//

// Now export all the data, row by row.

//

FieldCollection c = v.Fields;

foreach (Field f in c)

{

if (filterFields)

{

// Only display the title if the field is visible

if (displayedFields.Contains(f.Name))

{

TypeDescriptor td = f.TypeDescriptor;

Object vName = dbInstance.GetFormatted(f.Name);

context.Response.Write(vName);

context.Response.Write('\t');

}

}

else

{

// Filtering is off, export everything.

TypeDescriptor td = f.TypeDescriptor;

Object vName = dbInstance.GetFormatted(f.Name);

context.Response.Write(vName);

context.Response.Write('\t');

}

}

context.Response.Write("\n");

}

ie.Dispose();

}

catch (Exception exception)

{

context.Response.Write("Error reading:\tBDC");

context.Response.Write("\n");

context.Response.Write("Entity:\t" + context.Session["Entity"]);

context.Response.Write("\n");

context.Response.Write("Instance:\t" + context.Session["Instance"]);

context.Response.Write("\n");

context.Response.Write("Method:\t" + context.Session["Method"]);

}

}

}

Any thoughts would be greatly appreciated.

Answer 16

Here is the HTTP Handler version ExcelExp.ashx to be copied to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS directory.

All you need to do to export is to append _layouts/ExcelExp.ashx to any site eg. http://kopperla:7777/_layouts/ExcelExp.ashx.

Use this as the base version. The code sample is derived from http://msdn.microsoft.com/en-us/library/ms559510.aspx

<%@ Assembly Name="mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" %> 

<%@ Assembly Name="System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" %> 

<%@ Assembly Name="Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 

<%@ Assembly Name="Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 

<%@ WebHandler Language="C#" Class="ExcelDumper" %> 

 

using System;

using System.Web;

using System.Web.UI;

using System.Web.SessionState;

using System.Collections;

using System.Collections.Generic;

using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;

using Microsoft.Office.Server.ApplicationRegistry.Runtime;

using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;

public class ExcelDumper : IHttpHandler, IReadOnlySessionState

{

    public bool IsReusable

    {

        get { return false; }

    }

    public void ProcessRequest(HttpContext context)

    {

        context.Response.ContentType = "application/vnd.ms-excel";

        NamedLobSystemInstanceDictionary sysInstances = ApplicationRegistry.GetLobSystemInstances();

        LobSystemInstance AdvWorksIns = sysInstances["Give your Instance Name"];

        Entity prodEntity = AdvWorksIns.GetEntities()["Give your Entity Name"];

        MethodInstance methInst = prodEntity.GetFinderMethodInstance();

        IEntityInstanceEnumerator prodEntityInstanceEnumerator = (IEntityInstanceEnumerator)prodEntity.Execute(methInst, AdvWorksIns);

        while (prodEntityInstanceEnumerator.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');

        }

    }

} 

Answer 17

Hi mfoley40...i am doing the same work that is exporting data from BDC list to excel...but unable to . can you please send me the entire code. I really appriciate that. its already more than 2 weeks i spent time on this but unable to ...so do pls send the code and exact procedure to do.
waiting eagerly for your reply
thanks
snp.

Answer 18

mfoley40,
If you have that webpart to hand it would be greatly appreciated... :-)

- Nekatu

Answer 19

Hi SJaya,

I am thinking you can use custom action to accomplish this task. Randy and I have co-authored a 8-part series of articles on BDC, and the last part is Custom Action, which is what you are looking for. It's to be published very soon on SharePointMagazine.net.  If you Bing “Everything you need to know about BDC”, you will find the first 7 installments.  Please let me know if you still need this solution and I’ll post more details or entire solution here.

Hope this helps.  Mahalo!  Wen

Wen He (http://sharepointhawaii.com/WenHe)

---

Wen

Answer 20

Wen,

I've seen your articles- they are fantastic.

Could we get a look at your solution to this problem?
I've been scratching my head about this for days now!!!!

- Nekatu

Answer 21

@Kopperla,

Your solution does work, if you want headers included add thise code above his while statement.

 

        foreach (Field f in prodEntity.GetFinderView().Fields)

        {

            context.Response.Write(f.Name);

            context.Response.Write('\t');

        }

        context.Response.Write('\n');

Answer 22

Recently, I researched the methods on how to export data to excel. And I found one of my methods may be helpful for you.
I have submitted my article about this method to dotnetspider.com
You can read my article on
http://www.dotnetspider.com/resources/42388-Export-Customized-Format-Data-Excel-without.aspx

Answer 23

> And I found one of my methods may be helpful for you.

You should give a brief precis of this method in the thread so that the thread also includes this basic information. The link to the article is then provided for more details.

Moderator pre-2010 forums

(locking the thread which has been going on long enough)