Doesn’t it sound great that you can fetch data from an online web service in an Excel worksheet! Once fetched, you can organize it in Excel in any desired manner. There are so many day-to-day applications where this can be used like fetching stock prices, weather feed, or geographical data available online.
Do you know it is as simple to perform as executing an Excel function? There's no need to write long codes, macros, scripts, or using PowerQuery.
The WEBSERVICE function, introduced in Excel 2013, can directly fetch the data from web services on the internet in XML format. Use the FILTERXML function to parse the returned XML string. It uses XPath expressions to return specific data from the XML string.
The WEBSERVICE and FILTERXML functions are supported by GrapeCity Documents for Excel library, referred to as GcExcel. This quick tutorial explains how to Get Started with GcExcel.
In this article, we'll showcase how to use these functions in GcExcel to retrieve weather data information and save it to Excel using C#.
Use Case: Get Weather Data from a Web Service
Let's fetch the weather data from the US National Weather Service by using the WEBSERVICE function, in GcExcel. The fetched data (XML format) will be supplied to the FILTERXML function. It will take the XPath expressions as parameters to return the specified information from weather data in XML format.
See below for what the final output will look like in an Excel worksheet:
The next set of detailed steps will help you understand how to achieve this scenario.
Step 1: Setup a C# Project
We will create a basic GcExcel project by following the below steps:
- In VS, create a new .NET Core Console Application
- Install the ‘GrapeCity.Documents.Excel’ package (version 4.2.0) from the NuGet Package Manager
- Create a new workbook and fetch a worksheet by using the code below:
//Create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); //Fetch worksheet IWorksheet ws = workbook.Worksheets[0];
Step 2: Get Weather Data in XML
The weather data from the US National Weather Service can be fetched in XML format by passing its URL to the WEBSERVICE function as explained below:
WEBSERVICE: Returns the data from a web service on the Internet or Intranet.
Syntax: WEBSERVICE(URL)
Here, the URL belongs to the web service being called. We have applied the WEBSERVICE function in Cell B2 to fetch the data from the web service URL mentioned in cell B1.
Use this sample code snippet for the WEBSERVICE function in GcExcel:
ws.Range["A1"].Value = "URL"; //URL of Webservice ws.Range["B1"].Value = "https://w1.weather.gov/xml/current_obs/KAKR.xml"; ws.Range["A2"].Value = "Data Fetched from URL (WEBSERVICE Function)"; ws.Range["A2"].VerticalAlignment = VerticalAlignment.Center; //Use WEBSERVICE Function ws.Range["B2"].Formula = "=WEBSERVICE(B1)"; //Wrap the XML string returned by WEBSERVICE Function ws.Range["B2"].WrapText = true; ws.Range["B2"].RowHeight = 200;
See below for what the WEBSERVICE function and its output will look like below in Excel:
The detailed view of the fetched data in XML format is shown below:
"<?xml version=""1.0"" encoding=""ISO-8859-1""?>
<?xml-stylesheet href=""latest_ob.xsl"" type=""text/xsl""?>
<current_observation version=""1.0""
xmlns:xsd=""http://www.w3.org/2001/XMLSchema""
xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""
xsi:noNamespaceSchemaLocation=""http://www.weather.gov/view/current_observation.xsd"">
<credit>NOAA's National Weather Service</credit>
<credit_URL>http://weather.gov/</credit_URL>
<image>
<url>http://weather.gov/images/xml_logo.gif</url>
<title>NOAA's National Weather Service</title>
<link>http://weather.gov</link>
</image>
<suggested_pickup>15 minutes after the hour</suggested_pickup>
<suggested_pickup_period>60</suggested_pickup_period>
<location>Akron, Akron Fulton International Airport, OH</location>
<station_id>KAKR</station_id>
<latitude>41.0375</latitude>
<longitude>-81.46417</longitude>
<observation_time>Last Updated on Aug 24 2021, 5:54 am EDT</observation_time>
<observation_time_rfc822>Tue, 24 Aug 2021 05:54:00 -0400</observation_time_rfc822>
<weather>Fair</weather>
<temperature_string>68.0 F (20.0 C)</temperature_string>
<temp_f>68.0</temp_f>
<temp_c>20.0</temp_c>
<relative_humidity>84</relative_humidity>
<wind_string>Calm</wind_string>
<wind_dir>North</wind_dir>
<wind_degrees>0</wind_degrees>
<wind_mph>0.0</wind_mph>
<wind_kt>0</wind_kt>
<pressure_string>1016.2 mb</pressure_string>
<pressure_mb>1016.2</pressure_mb>
<pressure_in>30.04</pressure_in>
<dewpoint_string>63.0 F (17.2 C)</dewpoint_string>
<dewpoint_f>63.0</dewpoint_f>
<dewpoint_c>17.2</dewpoint_c>
<visibility_mi>9.00</visibility_mi>
<icon_url_base>http://forecast.weather.gov/images/wtf/small/</icon_url_base>
<two_day_history_url>http://www.weather.gov/data/obhistory/KAKR.html</two_day_history_url>
<icon_url_name>nskc.png</icon_url_name>
<ob_url>http://www.weather.gov/data/METAR/KAKR.1.txt</ob_url>
<disclaimer_url>http://weather.gov/disclaimer.html</disclaimer_url>
<copyright_url>http://weather.gov/disclaimer.html</copyright_url>
<privacy_policy_url>http://weather.gov/notice.html</privacy_policy_url>
</current_observation>"
Step 3: Extract Required Information from XML
Now that we have the data from the web services in XML format, we will extract the required information from it by using the FILTERXML function as explained below:
FILTERXML: Returns specific data from XML content by using the specified XPath.
Syntax: FILTERXML(xml, xpath)
XML is the string in the valid XML format and XPath is the string in standard XPath format. XPath (XML Path Language) is defined in a “path-like” syntax to match the specified path in XML and extract data from it. We used the FILTERXML function in cells B4 to B7 to return the specified information (mentioned in XPath expressions which are passed as parameters from the data present in XML format in cell B1.
See this sample code snippet for using the FILTERXML function in GcExcel:
//Add labels for resultant values from FILTERXML formula ws.Range["A4"].Value = "Location"; ws.Range["A5"].Value = "Observation Time"; ws.Range["A6"].Value = "Weather"; ws.Range["A7"].Value = "Temperature"; //Fetch Location using ‘//location' XPath Expression ws.Range["B4"].Formula = "=FILTERXML(B2,\"//location\")"; //Fetch Observation Time using ‘//observation_time' XPath Expression ws.Range["B5"].Formula = "=FILTERXML(B2,\"//observation_time\")"; //Fetch Weather using ‘//weather' XPath Expression ws.Range["B6"].Formula = "=FILTERXML(B2,\"//weather\")"; //Fetch Temperature using ‘//temperature_string ' XPath Expression ws.Range["B7"].Formula = "=FILTERXML(B2,\"//temperature_string\")";
The FILTERXML function and its output will look like below in Excel:
Lastly, apply simple formatting in the cells and use the ‘AutoFit’ and ‘Save’ methods to automatically fit the columns and save the workbook in Excel respectively, as shown below:
//Apply Formatting
ws.Range["A4:A7"].Interior.Color = Color.FromArgb(88, 132, 52);
ws.Range["A4:A7"].Font.Color = Color.White;
ws.Range["B4:B7"].Interior.Color = Color.FromArgb(226, 239, 218);
ws.Range["A4:B7"].Font.Bold = true;
ws.Range["A4:B7"].Font.Size = 12;
//Auto fit columns
ws.Range["A:B"].EntireColumn.AutoFit();
//Save to an Excel file
workbook.Save("webserviceandfilterxml.xlsx");
At the time of fetching, this is how the web service data appears:
Below you will find the fetched data, using GcExcel, in Excel:
You can also try this use-case example by downloading the sample, which includes the code for all the steps described above.
Note: The data in the Excel worksheet containing the WEBSERVICE function gets updated only when:
- A referenced cell is edited
- The entire workbook is refreshed (Keyboard shortcut: CTRL+ ALT + F9)
However, the data is updated automatically every time you run the GcExcel application.