Consuming Web Service from Excel

Previous versions of Microsoft Office Applications were able to access web services using Microsoft Office Web Service Toolkit. But the Web Services Toolkit is not a Microsoft supported product.

Microsoft Visual Studio 2008 / 2010 provides support for creating Office 2007 / 2010 application.

And easily make calls to Web Services.

Using this article you can learn how to consume a web service and create an add-on for Excel.

Step 1: Creating an Excel 2010 workbook solution in Visual Studio 2010.

CnsWbSrvc1.jpg

Step 2: Open Sheet1.cs for viewing the code:

CnsWbSrvc2.jpg

Step 3: Add a Service reference:

CnsWbSrvc3.jpg

CnsWbSrvc4.jpg

public static void BindWorkSheet()

{

    List<ItemList> itemlist = new List<ItemList>();

    ItemList_Filter[] filters = null;

    ItemList_Service service = new ItemList_Service();

    ICredentials credntial = new System.Net.NetworkCredential(ConfigurationSettings.AppSettings["username"].ToString(), ConfigurationSettings.AppSettings["password"].ToString(), ConfigurationSettings.AppSettings["domain"].ToString());

    service.Credentials = credntial;

    itemlist = service.ReadMultiple(filters, string.Empty, 0).ToList();

    if (itemlist != null)

    {

        if (itemlist.Count() > 0)

        {                  

            Excel.Worksheet sheet = Globals.ThisWorkbook.Worksheets[1];                                

            sheet.ClearArrows();

            General general = new General();

            DataTable dt = general.ToDataTable(itemlist);

            if (dt != null)

            {

                if (dt.Rows.Count > 0)

                {

                    for (int j = 1; j <= dt.Columns.Count; j++)

                    {

                        if (dt.Columns[j - 1].ColumnName != null)

                        {

                            ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, j]).Value2 =
                                dt.Columns[j - 1].ColumnName.ToString();

                            ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, j]).Font.Bold
                                 = "true";

                            ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, j]).Font.Color
                                 = System.Drawing.Color.Black;

                        }

                    }

                    for (int i = 1; i <= dt.Rows.Count; i++)

                    {

                        for (int j = 1; j <= dt.Columns.Count; j++)

                        {

                            if (dt.Rows[i - 1][j - 1] != null)

                            {

                                ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[i+1, j]).Value2 = dt.Rows[i - 1][j - 1].ToString();                                       

                                ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[i+1, j]).Font.Color = System.Drawing.Color.Black;

                            } 

                        }

                    }

                }

            } 

        }

    }

}


Step 4: Build and run the application.

CnsWbSrvc5.jpg


Step 5: Create the Refresh Add-On so the user can get fresh data every time.

Add a Ribbon control to the project:

CnsWbSrvc6.jpg

CnsWbSrvc7.jpg

On the refresh button click, rebind the Excel file:

private void btnrefresh_Click(object sender, RibbonControlEventArgs e)
{
    ExcelDynamicNAV.
Sheet1.BindWorkSheet();
}


Step 6: Your app.setting file is like:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
        </sectionGroup>
    </configSections>
    <system.serviceModel>
        <bindings />
        <client />
    </system.serviceModel>
    <applicationSettings>
        <ExcelDynamicNAV.Properties.Settings>
            <setting name="ExcelDynamicNAV_ItemListRef_ItemList_Service"
                serializeAs="String">
                <value>Web service URL</value>
            </setting>
        </ExcelDynamicNAV.Properties.Settings>     
    </applicationSettings>
  <appSettings>
    <!-- Web service Credentials -->
    <add key="username" value=""/>
    <add key="password" value=" "/>
    <add key="domain" value=" "/>
  </appSettings>
</configuration> 

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now