Consuming SAP Data from MS-Excel

What is this about?

Everybody knows the importance of MS-Excel, How will it be if we can populate SAP data into the Excel client automatically. Sometimes we feel good if we are able to view a list of data from SAP and update the data from Excel itself. Yes I have taken this case to demonstrate as of how we can populate the data from SAP system into MS Excel.

How are we going to achieve this?

With the Duet Enterprise and Office 2010 suite, now it is possible to build solution that would let you interact with your SAP backend directly from your MS Office products. This solution is build using VSTO packages via Visual Studio. Since we are developing on top of Duet Enterprise, the developer/partner need not worry about security, localization etc., because all these are taken care by Duet Enterprise.

What are the tools required to build this solution?

1. Visual Studio 2010
2. BCS Artifact Generator
3. BCS Solution Packaging Tool

Use case to explain this article:

Say, Martin, who is using Duet Enterprise is responsible for some CRM accounts. He wants to see the list of CRM activities that are associated with the accounts and responsible contacts. After getting the list of activities, Martin can group those activities as per the status and send that data to his higher official with a chart in excel.

I am demoing this scenario using CRM Activity that is delivered as part of Feature Pack 1 for Duet Enterprise 1.0.

Pre-Requisite for this demo is Feature Pack 1 for DuetEnterprise 1.0.

To acheive this scenario follow the steps mentioned below:

1. Create a Base solution.
2. Download the sample code from MSDN.
3. Edit the code as shown below.

Creation and deployment of base BCS Solution:

1. Assign the required permissions to the BCS models (CRM Activity) from your central administration as shown below. Please select
        the sub-set of user as per your requirement. Here I would allow all the authenticated users to have access to this solution.

Goto Central admin -> ManageService Application->BusinessData Connectivity Service.

2.  Export the models with all the permission and proxy information as shown below:

3. We’ll use the BCS Artifact tool to generate the artifacts for the downloaded model.

a.      Import the model in the artifact tool and click next:

         

b.     Click on Generate Artifacts:

         

4. Once the artifacts are generated open the BCS Packaging tool:
                   

1. Provide a Solution Name and select the solution type as given in the screenshot above.
2. Provide the path of the files which are generated in the previous tool(BCS artifact tool)
3. Provide an output folder for this BCS Packing tool.
4. Now click on Package which will generate VSTO package.
5. Once the VSTO package is created successfully, click on Deploy which will deploy the base solution in the current machine.

5. Now you may download the code from this MSDN article:
6. After downloading the code extract it to a folder and open the SalesOrderAddIn visual studio solution.

7. Open the SalesDataManager Class and make the changes as shown below as per the CRM activity BCS Model:

Existing code in the Solution:

private const string SalesOrderHeader = “SalesOrderHeader”;

private const string SalesOrderLine = “SalesOrderLine”;

private const string SalesOrderHeaderFinderName = “SalesOrderHeaderReadList”;

private const string SalesOrderSpecificFinderName = “SalesOrderHeaderReadItem”;

private const string SalesOrderLineSpecificFinderName = “SalesOrderDetailReadItem”;

private const string SalesOrderLobSystemName = “AdventureWorks”;

private const string SalesOrderLobSystemInstanceName = “AdventureWorks”;

private const string SalesOrderAssociationName = “SalesOrderDetailNavigateAssociation”;

private const string SalesOrderHeaderNamespace = “http://intranet.contoso.com“;

private const string DependentDataSolutionID = “SalesOrderSolution”;

private const string DependentDataSolutionVersion = “1.0.0.0”;

Changed Code will look like this:

private const string SalesOrderHeader = “Activity”;

private const string SalesOrderLine = “empty”;

private const string SalesOrderHeaderFinderName = “FindActivityByElements”;

private const string SalesOrderSpecificFinderName = “ReadActivityById”;

//private const string SalesOrderLineSpecificFinderName = “SalesOrderDetailReadItem”;

private const string SalesOrderLobSystemName = “Activity”;

private const string SalesOrderLobSystemInstanceName = “Activity”;

private const string SalesOrderHeaderNamespace = “SAP.Office.DuetEnterprise.CRMAccount”;

private const string DependentDataSolutionID = ” Demo”;

private const string DependentDataSolutionVersion = “1.0.0.0”;

private const string UpdateMethodName = “UpdateActivity”;

Add a button on the ribbon and on the button click event add a code like this:

private void btnGetData_Click(object sender, RibbonControlEventArgs e)

{

     if (lobData != null)

            {   
               salesOrderTable = lobData.GetSalesOrderHeaderItems();              
               if (salesOrderTable != null)              
               {      
                BindSalesOrderLines(salesOrderTable);
               }

            }

}

public DataTable GetSalesOrderHeaderItems()

{

            if ((entitySalesOrderHeader != null)&& (lobInstance != null))

               {

               // Get the default filters

               IFilterCollection filters = entitySalesOrderHeader.GetMethodInstance(

                                             CRMAccountHeaderFinderName,MethodInstanceType.Finder).GetFilters();

              // Execute the FindFiltered method online.              
               IEntityInstanceEnumerator enumerator = entitySalesOrderHeader.FindFiltered(

                                                       filters,SalesOrderHeaderFinderName,lobInstance,OperationMode.Online);

               SalesOrderTable = null;

               SalesOrderTable = catalog.Helper.CreateDataTable(enumerator);

               // Clear the dirty rows, if any

               changedSalesLineRows.Clear();

               //SalesOrderLineTable = catalog.Helper.CreateDataTable(enumerator,true);              

               if (SalesOrderTable != null)

               {

               SalesOrderTable.RowChanged += new DataRowChangeEventHandler(SalesOrderTable_RowChanged);

                               }

               changedSalesLineRows = SalesOrderTable.Clone();

          }

     return SalesOrderTable;

}

You may comment out/delete the code where ever it is not required. Also rename the Class Vriables/members as required.

If you want to update the item from Excel:

1.  Listen to the datatable rowchanged event and add the changed rows in a new datatable.

///<summary

/// Track the row which has been edited by end user

/// </summary>

/// <param name=”sender”></param>

/// <param name=”e”></param>

void SalesOrderTable_RowChanged(object sender, DataRowChangeEventArgs e)

{
         changedSalesLineRows.ImportRow(e.Row);

}

  2. Add a button on the ribbon and on the click even on the button add this code:

foreach (DataRow changedrow in changedSalesLineRows.Rows)

{

try

    { 

    itemIdentity = new Identity(changedrow[EntityIdentifier].ToString());

     salesOrderinstance = entitySalesOrderHeader.FindSpecific(itemIdentity,
     CRMAccountSpecificFinderName, lobInstance, OperationMode.Online);

         foreach (DataColumn col in changedSalesLineRows.Columns)

           {

try 

    {

     salesOrderinstance[col.ColumnName] = changedrow[col.ColumnName];

           }

                catch (System.Exception ex)

          {

           }

}

           salesOrderinstance.Update();

     System.Windows.Forms.MessageBox.Show(“Updated Successfully!!”);

     }catch(System.Exception ex)

     {  

     }

}

 

So now the user can get SAP data in Excel and update the same. This sample code can be enhanced further with custom action pane as per the customer requirement.A good starting point to refer could be the MSDNarticle.  Most of the code pieces used in this demo have been taken from this MSDN article which talk about Excel add-ins. Also it explains about each method as to what it does.