web request

[mzhu...@gmail.com]

Hi, Govert, 

I tried the simple example you provided in one of the post. It works in console application,  However, it gave me error when using exceldna. Here is part of the error msg

"!!! EXCEPTION: System.AggregateException: One or more errors occurred. --->
System.Net.Http.HttpRequestException: An error occurred while sending the request. --->
System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a send. --->
System.IO.IOException: Unable to read data from the transport connection:
An existing connection was forcibly closed by the remote host. --->
System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host

Here is the code

        public void AutoOpen()
        {
            ExcelIntegration.RegisterUnhandledExceptionHandler(
                ex => "!!! EXCEPTION: " + ex.ToString());
        }

        public void AutoClose()
        {
        }

        [ExcelFunction(Name = "test002.pmGet")]
        public static object pmGet()
        {
            using (var client = new HttpClient())
            {
                string uri = "https://postman-echo.com/get?foo1=bar1&foo2=bar2";
                var response = client.GetAsync(uri).Result;
                var res = response.Content.ReadAsStringAsync();
                return "ok";
            }
        }

I also tried client.DefaultRequestHeaders.ConnectionClose = true;, but did not work.

Could you please help take a look if I missed something?

Thanks,

Min

[Govert van Drimmelen]

HI Min,

 

You need to resolve the second async call too.

You can change it like this:

 

        [ExcelFunction(Name = "test002.pmGet")]
        public static object pmGet()
        {
            using (var client = new HttpClient())
            {
                string uri = "https://postman-echo.com/get?foo1=bar1&foo2=bar2";
                var response = client.GetAsync(uri).Result;

                var res = response.Content.ReadAsStringAsync().Result;
                return res;
            }
        }

 

That works, but it’s not good to make a new instance of HttpClient for every call.

 

So maybe

 

    static HttpClient _httpClient;

 

        [ExcelFunction(Name = "test002.pmGet")]
        public static object pmGet()
        {

            if (_httpClient == null)

                   _httpClient = new HttpClient();

            string uri = "https://postman-echo.com/get?foo1=bar1&foo2=bar2";

            var response = _httpClient.GetAsync(uri).Result;
            var res = response.Content.ReadAsStringAsync().Result;
            return res;
        }

 

This is a synchronous request – there are ways to make it async, but getting this to work is the simplest start.

 

Let me know if this works for you.

 

-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/99e83a81-d46c-4187-acd0-b3ec97d0ea54n%40googlegroups.com.

[mzhu...@gmail.com]

Hi, Govert,

Thank you so much for the help. However, I tried both suggestions, still get same error.

by the way I am using excel 2013 64 bit, vs 2019, framework 4.7.2. Do you mean it works on your end?

using ExcelDna.Integration;
namespace testAsync
{
    public class test002 : IExcelAddIn
    {
        static HttpClient _httpClient;

        public void AutoOpen()
        {
            ExcelIntegration.RegisterUnhandledExceptionHandler(
                ex => "!!! EXCEPTION: " + ex.ToString());
        }
        public void AutoClose(){}
        [ExcelFunction(Name = "test002.pmGet")]
        public static object pmGet()
        {

            if (_httpClient == null) _httpClient = new HttpClient();
            string uri = "https://postman-echo.com/get?foo1=bar1&foo2=bar2";
            var response = _httpClient.GetAsync(uri).Result;
            var res = response.Content.ReadAsStringAsync().Result;
            return res;
        }
    }
}

Thanks,

Min

[Govert van Drimmelen]

Hi Min,

 

I was testing under .NET 6, where it works OK.

 

For .NET Framework 4.x, the default TLS / SSL support is only at TLS v 1.0, and the https website you are talking to is probably serving a higher version.

 

So you need to make a call like this to set the SecurityProtocol, somewhere before you make the HttpClient.

 

    using System.Net;

 

    ….

 

 

        public static object pmGet()
        {
            if (_httpClient == null)

            {

                ServicePointManager.SecurityProtocol =

                      SecurityProtocolType.Tls |

                      SecurityProtocolType.Tls11 |

                      SecurityProtocolType.Tls12;

               _httpClient = new HttpClient();

            }

            ….. etc.

          }

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of mzhu...@gmail.com
Sent: Sunday, February 12, 2023 9:25 PM
To: Excel-DNA <exce...@googlegroups.com>
Subject: Re: [ExcelDna] web request

 

Hi, Govert,

 

Thank you so much for the help. However, I tried both suggestions, still get same error.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/a526015a-646c-42c2-8c07-d3ed85bc312dn%40googlegroups.com.

[mzhu...@gmail.com]

Hi, Govert,

Thanks a lot. It works now

so you suggest to use .Net 6 instead of framework? I think I tried compile exceldna under .Net Core, but did not work, I will try again. By the way, I thought if I use " GetAsync" it is already async request. I am new to the network related development. I mainly use exceldna to create xll for numerical calculation. Do you have instruction somewhere I can take a look?

Thanks,

Min

[mzhu...@gmail.com]

By the way, why it worked in console application? Is this related to excel?

Thanks,

Min

[Govert van Drimmelen]

Hi Min,

 

I’m glad it’s working now.

 

Between .NET Framework and .NET 6 is a tricky choice at the moment.

It’s much easier to distribute an add-in targeting .NET Framework, while with .NET 6 you have to also ensure the user has the runtime installed.

And some things worked better in .NET Framework than in .NET 6, like the AppDomain isolation between add-ins.

On the other hand, .NET 6+ is the only future direction that will be worked on, so more and more features and libraries are only available there.

For now, if everything you do works on .NET Framework, it’s probably easier to stay until you have a specific reason need to move.

 

A somewhat orthogonal issue is to change to “SDK-style” project files. These can support targeting .NET Framework or .NET 6 (or both) and are much better to work with. So if possible I recommend you change your projects to the new format, even if you stay on .NET Framework.

 

Regarding async functions – no the example discussed here will not make an async Excel function – the function call will only return to the sheet after the web request is complete and processed. There are ways to make functions that behave as async functions, but the various examples in the Excel-DNA documentation is rather scattered. You might look at this write-up as one example: Excel-DNA.github.io/asynchronous-functions.md at master · Excel-DNA/Excel-DNA.github.io

 

I have been trying to get some help on the documentation and examples side, but progress will depend on getting some more sponsors to sign up as GitHub Sponsors.

Maybe you can have a look?

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of mzhu...@gmail.com
Sent: Sunday, February 12, 2023 10:17 PM
To: Excel-DNA <exce...@googlegroups.com>
Subject: Re: [ExcelDna] web request

 

Hi, Govert,

 

Thanks a lot. It works now

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/1400861f-2f7c-4358-ab83-1ec6377362fen%40googlegroups.com.

[mzhu...@gmail.com]

Thanks a lot, Govert. Your suggestion is really helpful. I will definitely take a look.

Thanks,

Min