How to call web Service in Excel Using VSTO (CSExcelCallWebService)

Introduction

The sample demonstrates how to call web service in excel. The excel client call web service to get the returned string about weather and parse the returned string to XElement collection. Then query the collection and bind the value of XElement to the NameRange control.

Video


Building the Sample

Before you build the sample, you must install Microsoft Office 2013 on your Operation System and be sure that Excel process is not running.

Running the Sample

Step1. Open "CSExcelCallWebService.sln" and click F5 to run the solution

Step2. Choose the "Call WebService Ribbon" ribbon and input correct city name and country name. Then click "Get Weather" button to display the data about weather in worksheet. You will see the following form:

 

Step3. If you input incorrect city name or country name, you will get error message "Input City or Country is error, Please check them again" in cell A1.

Step4. Close Excel 2013 and In the Solution Explorer, right click CSExcelCallWebService and click Clean.

Using the Code

Step1. Create Excel 2013 Workbook project in Visual Studio 2012

Step2. Add a service reference:

 

Step3. Open sheet1.cs to write the code:

C#
Edit|Remove
/// <summary>
///  Call Web service and display the results to the NameRange control
/// </summary>
/// <param name="city">Search City</param>
/// <param name="country">Search Country</param>
public void DisplayWebServiceResult(string city, string country)
{
    // Get Name Range and Clear current display
    NamedRange range = (NamedRange)this.Controls["Data"];
    range.Clear();


    // Initialize the value of x 
    int x = 0;


    try
    {
        // Initialize a new instance of Service Client 
        using (GlobalWeatherSoapClient weatherclien = new GlobalWeatherSoapClient())
        {
            // Call Web service method to Get Weather Data
            string xmlweatherresult = weatherclien.GetWeather(city, country);


            // Load an XElement from a string that contains XML data
            var xmldata = XElement.Parse(xmlweatherresult);


            // Query the Name and value of Weather
            var query = from weather in xmldata.Elements()
                        select new
                        {
                            weather.Name,
                            weather.Value
                        };


            if (query.Count() > 0)
            {
                foreach (var item in query)
                {
                    // Use RefersToR1C1 property to change the range that a NameRange control refers to
                    range.RefersToR1C1 = String.Format("=R1C1:R{0}C2", query.Count());


                    // Update data  in range.
                    // Excel uses 1 as the base for index.
                    ((Excel.Range)range.Cells[x + 1, 1]).Value2 = item.Name.ToString();
                    ((Excel.Range)range.Cells[x + 1, 2]).Value2 = item.Value.ToString();
                    x++;
                    if (x == query.Count() - 1)
                    {
                        break;
                    }
                }
            }
        }
    }
    catch
    {
        this.Range["A1"].Value2 = "Input City or Country is error, Please check them again";


        // -16776961 is represent for red
        this.Range["A1"].Font.Color = -16776961;
    }
}

 

 

Step4. Create a Ribbon control to the project and design the UI, then you can input the city name and country name to get data of weather.

C#
Edit|Remove
/// <summary>
       /// Get Weather method
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       private void btnGetWeather_Click(object sender, RibbonControlEventArgs e)
       {
           if (citybox.Text.Trim().Equals(string.Empty) || countrybox.Text.Trim().Equals(string.Empty))
           {
               MessageBox.Show("Please input the city or country name firstly.");
               return;
           }


           // Call web service to get Weather
           Globals.Sheet1.DisplayWebServiceResult(citybox.Text.Trim(),countrybox.Text.Trim());
       }

 

 

More Information

NamedRange Interface

XElement.Parse Method

Linq to Xml