Calling web requests from ExcelDna function

[Joseph Roche]

Hello Govert,

I have developed an ExcelDna add-in as a wrapper around several web APIs, everything works fine for lightweight queries, I get the response from the server and can display the results back in Excel.

But for I have some queries which take about 10 seconds (when running them directly in curl/Postman) and those are causing issues in Excel:

1. F2, Enter to recalculate the formula calling the service 
2.  Excel hangs for about 1min - 1min 30sec
3. Then this popup appears: "Microsoft Excel is waiting for another application to complete an ole action"
4. I press OK and results are finally displayed right after

My ExcelDna function (non-volatile, non-thread- safe) just calls this method to make the web requests:

        public static string Post(string uri, string json)

        {

            StringContent content = new StringContent(json, Encoding.UTF8, "application/json");

            Task<HttpResponseMessage> task = Task.Run(() => Client.PostAsync(uri, content));

            task.Wait();

            HttpResponseMessage response = task.Result;

            if (!response.IsSuccessStatusCode)

            {

                throw new HttpRequestException($"Response {response.StatusCode} from {uri}");

            }

            return response.Content.ReadAsStringAsync().Result;

        }

The exact same code in a standalone console app runs just fine and I get the response within 10 seconds.

Is there anything I'm doing wrong while running the async task? The function has to be synchronous which is why I wait for the results. 

Thank you

p.s.

Govert, I have been using ExcelDna for the last 13 years and I can't express how great this whole project is. I've been building large frameworks built on top of ExcelDna for many clients in different companies and it has always been proved to be robust, scalable and very easy to develop. Huge respect for your work (and those who contributed)

Joseph

[Govert van Drimmelen]

Hi Joseph,

 

Thank you for the kind words about Excel-DNA.

 

Can you try a slightly simpler version of your function:

 

        public static string Post(string uri, string json)

        {

            StringContent content = new StringContent(json, Encoding.UTF8, "application/json");

 

            HttpResponseMessage response = Client.PostAsync(uri, content).Result;

 

            if (!response.IsSuccessStatusCode)

            {

                throw new HttpRequestException($"Response {response.StatusCode} from {uri}");

            }

 

            return response.Content.ReadAsStringAsync().Result;

        }

 

 

This eliminates the extra Task and Wait() calls, which should not make a difference, but there might be some surprise.

 

Then I would suggest checking some ‘Get’ calls against a public site or service, so that you can make something I can try out too.

For the simplest case it seems to work fine for me:

 

    public static class Functions

    {

        static HttpClient _client = new HttpClient();

 

        public static string CallHttp(string url)

        {

            var response = _client.GetAsync(url).Result;

            return response.Content.ReadAsStringAsync().Result;

        }

 

    }

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/4f97a470-92ee-4da7-9ea9-60ee6ead96fan%40googlegroups.com.

[Joseph Roche]

Hi Govert,

Can you try a slightly simpler version of your function 

This is how I had the code initially and I expanded it to test another variation but it does not make any difference

Then I would suggest checking some ‘Get’ calls against a public site or service

Making GET calls against my other services seemed to be working fine but they were quick so it didn't help me much to find out what the issue was.

But following your suggestion I've modified the web service to accept GET requests and ran it locally (still 10-12 sec when testing with Postman) then I called the ExcelDna functions and it works!

So somehow (and it's very weird) it looks like POST requests are taking much longer and causing Excel to hang, even if the equivalent GET request is taking the same time and returning the same volume of data...

Would you have any insight about why Excel/C API would behave like this?

Otherwise I think I'll look at modifying the services to use the GET verb.

Thank you

Joseph

[Govert van Drimmelen]

This is a simple PSOT against a public server:

 

        public static string CallHttpPost(string info)

        {

            var url = https://httpbin.org/post;

            var content = new StringContent(info);

            var response = _client.PostAsync(url, content).Result;

            return response.Content.ReadAsStringAsync().Result;

        }

 

 

It seems to work OK for me.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/e77f5caf-6795-4dea-8993-2ef5a47772dfn%40googlegroups.com.

[mzhu...@gmail.com]

Hi, Govert, 

I tried your example. It works in console application. However, when I put in exceldna function, it returns #Value. Do i need to add something in Auto Open function?

{

                string uri = "http to get token";
                var byteArray = Encoding.ASCII.GetBytes("user:pswd");
                var header = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteAttay));
                client.DefaultRequestHeaders.Authorization = header;

                 ExcelDna.Log....WriteLine("here1"); // can get here

                var res = client.GetStringAsync(uri);

                ExcelDna.Log....WriteLine("here2"); // never get here

                JsonToken jstk = JsonConvert.DeserializeObject<JsonToken>(res.Result);

                return jstk.token;

}

Could you please help with some suggestion?

Thanks,

Min