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>