How to Create Source File

Excel Ignite is an Excel add-on that allows users to retrieve data from online APIs, as User Defined Functions (UDF). To make your API accessible through Excel Ignite, all you need to do is to create an XML-based Source file, which defines

  • Available functions, API end points and their inputs
  • How the Excel Ignite would connects to the corresponding APIs, with the inputs provided
  • Detailed mapping between API response and available outputs for the Excel function

The APIs can be publically accessible or local to a specific environment. The following is instruction of how to create a source file.

Prerequisite

  1. Download the Excel Ignite here.
  2. Unzip the installation package and install it.
  3. Once installed, open Excel and enter =Ignite_Browse() in any cell.
  4. The =Ignite_Browse() function will open Windows Explorer in the folder where sources files are kept.
    This is also where your source file should be added.

Source File Examples

Here is a portion of the EnClout source file (enclout.xml).

Excel Ignite uses source files to define Excel UDFs. The file name should be the same as the source defined here, with .xml extensions. You can define multiple functions within a single source file. Here is a high level structure of the source file:

<ignite version="1.0" source="enclout">
   <func name="content" endpoint="http://www.enclout.com/content/show.json?" method="GET" format="JSON">
      ...
   </func>

   <func name="dns" endpoint="http://www.enclout.com/dns/show.json?" method="GET" format="JSON" jsonRootForXML="dns">
   ...
   </func>

   <func name="urlparser" endpoint="http://www.enclout.com/urlparser/show.xml?" method="GET" format="XML">
   ...
   </func>
   ...
</ignite>
       

Each function is defined in the "func" block which describes how to connect to the API with inputs and how to parse output to get the value for Excel UDF function. For the func block, we must specify:

  • API signature, including its name, end point, output format and method.
  • Optional description section
  • Detailed mapping from the Excel UDF function to API inputs. Excel Ignite supports two types of inputs.
    • Parameters with "config" type are configuration data Excel users do not need to know
    • Parameters with "input" type prefix are data provided by Excel users. The integer after the prefix determines the order in the Excel UDF call.
    • Each parameter can be specified with a method, which determines how the value is passed to the underlying API. Supported methods are GET, POST or HTTP_HEADER. Default is GET.
  • Detailed mapping from API output to UDF outputs. Excel Ignite support APIs with JSON or XML outputs.
  • Optional status section, which tells Excel Ignite how to extract API status
<func name="Name" endpoint="API URL" method="GET" format="JSON or XML"jsonRootForXML="root name" > 
    <description>
          ...
    </description>
    <inputs>
         <input name="header" type="config" value="XXXXXXX" method="HTTP_HEADER"/>
         <input name="auth_token" type="config" value="XXXXXXX"/>
         <input name="url" type="input1"/>
         <input name="data" type="input2" method="POST" />
         ...
    </inputs>
    <outputs>
       <output name="Something" path="XPath or Json Path"/>
        ...
    </outputs>
    <status ERROR_path="" OK_path=""/>
</func>
      

The example below is an API with JSON output. We use Newtonsoft.Json.Linq library to parse the API output to UDF output.

<func name="content" endpoint="http://www.enclout.com/content/show.json?" method="GET" format="JSON">
   <description>
         This function returns content meta information related to a domain
	 Usage: "=ignite("enclout","content","category", "http://www.enclout.com")"
   </description>
   <inputs>
       <input name="url" type="input1"/>
       <input name="auth_token" type="config" value="JPbbCaVFYdnpm2xY9aq1"/>
   </inputs>
   <outputs>
      <output name="redirect_url" path="redirect_url"/>
      <output name="category" path="category"/>
      <output name="meta_description" path="meta_description"/>
      <output name="meta_keywords" path="meta_keywords"/>
      <output name="language" path="language"/>
      <output name="domain_words" path="domain_words"/>
      <output name="ALL" path=""/>
   </outputs>
</func>
      

Sometimes, even though the API returns JSON object, but we want to use XPath to retrieve specific data. In such cases, we can use jsonRootForXML to help convert JSON to XML object.

<func name="dns" endpoint="http://www.enclout.com/dns/show.json?" method="GET" format="JSON" jsonRootForXML="dns">
      <description>
            This function returns DNS data for a given domain.
	    Usage: "=ignite("enclout","dns","AAAA", "facebook.com")"
      </description>
      <inputs>
            <input name="url" type="input1"/>
            <input name="auth_token" type="config" value="JPbbCaVFYdnpm2xY9aq1"/>
      </inputs>
      <outputs>
            <output name="SOA" path="//dns/dns_entries[Type='SOA']/RData"/>
            <output name="A" path="//dns/dns_entries[Type='A']/RData"/>
            <output name="AAAA" path="//dns/dns_entries[Type='AAAA']/RData"/>
            <output name="NS" path="//dns/dns_entries[Type='NS']/RData"/>
            <output name="MX" path="//dns/dns_entries[Type='MX']/RData"/>
      </outputs>
      <status ERROR_path="" OK_path=""/>
</func>
        

APIs with XML outputs are supported. When the API returns XML objects, we can use XPath to specify how to retrieve data.

<func name="urlparser" endpoint="http://www.enclout.com/urlparser/show.xml?" method="GET" format="XML">
       <description>
             This function parses a given URL and returns back any valid part.
	     Usage: "=ignite("enclout","urlparser","root_domain-ascii", "http://www.facebook.com/hello")"
       </description>
       <inputs>
             <input name="url" type="input1"/>
             <input name="auth_token" type="config" value="JPbbCaVFYdnpm2xY9aq1"/>
       </inputs>
       <outputs>
             <output name="root-domain-ascii" path="//urlparser/root-domain-ascii"/>
             <output name="root-domain-unicode" path="//urlparser/root-domain-unicode"/>
             <output name="host-ascii" path="//urlparser/host-ascii"/>
             <output name="host-unicode" path="//urlparser/host-unicode"/>
             <output name="scheme" path="//urlparser/scheme"/>
             <output name="path" path="//urlparser/path"/>
             <output name="query" path="//urlparser/query"/>
             <output name="fragment" path="//urlparser/fragment"/>
             <output name="userinfo" path="//urlparser/userinfo"/>
             <output name="port" path="//urlparser/port"/>
       </outputs>
       <status ERROR_path="//status[. != 'OK']" OK_path="//status[. = 'OK']"/>
</func>
      

Deployment

Once the source file is created, in order to use it, Excel users just need to drop the file to the Ignite source file folder to deploy it. =Ignite_Browse() function will open Windows Explorer in the folder where sources files are kept.

Complete EnClout files

EnClout Source file
EnClout Excel file