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.



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



Step 3: Add a Service reference:

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.




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

Add a Ribbon control to the project:





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>