QueryTables.Add method

GAURAV GUPTA

4y1.3kC#

I am using Excel Library in c# and trying to fill a Excel from

Querytables.Add Method with a Url

This Url is already implemented with the security of single sign on. So neither my code giving any error nor it is filling the Excel Sheet with the data. Issue is of authentication. I am not sure how i can allow this windows forms application to authenticate or Query tables. Fill method to Authenticate so that Excel can be filled from the Url Data.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

GAURAV GUPTA4y ago
Hi sachin,

I have posted the code by editing in my previous post. Can you please suggest where i am wrong
Sachin Singh4y ago
I am guessing windows credentials are enough to validate the user on the API side, however, if windows credentials are different then you will have to pass the username and password explicitly.
var wi = (System.Security.Principal.WindowsIdentity)HttpContext.Current.User.Identity;

var wic = wi.Impersonate();
try
{
    var data = JsonConvert.SerializeObject(new
    {
        Property1 = 1,
        Property2 = "something"
    });

    using (var client = new WebClient { UseDefaultCredentials = true })
    {
        client.Headers.Add(HttpRequestHeader.ContentType, "application/json; charset=utf-8");
        client.UploadData("your url", "POST", Encoding.UTF8.GetBytes(data));
    }
}
catch (Exception exc)
{
    // handle exception
}
finally
{
    wic.Undo();
}
if windows credentials are different from API credentials then
try
{
    var data = JsonConvert.SerializeObject(new
    {
        Property1 = 1,
        Property2 = "something"
    });

    using (var client = new WebClient () )
    {
        client.Headers.Add(HttpRequestHeader.ContentType, "application/json; charset=utf-8");
client.Credentials = new NetworkCredential("username", "password");
        client.UploadData("your url", "POST", Encoding.UTF8.GetBytes(data));
    }
}
catch (Exception exc)
{
    // handle exception
}
finally
{
    wic.Undo();
}
GAURAV GUPTA4y ago
Thanks Sachin. First method was giving an error while second method is giving no error.

I have a doubt like i have following sample code written:

var data=JSonConvert.SerializeObject(new {

Property1=1;

Property2="Something"});

using (var client=new webclient())

{

ServicePointManager.SecurityProtocol=SecurityProtocol.Tls12;

Client.Headers.Add(HttpRequestHeader.ContentType,"application/json;charset=utf-8");

client.Credentials=new NetworkCredential("username","pwd");

client.UploadData("https://mylink","POST",Encoding.UTF8.GetBytes(data));

//here i will add the code to create a excel file like

myexcelapp=new Excel.Application{visible=true};

........

mysheet=ExcelHandler.Workbook.Worksheets.Add();

mysheet.QueryTables.Add(Connection:"MyURL;",Destination:mysheet.Cells[1,1];

var qt=mysheet.QueryTables[1];

qt.Refresh();

}

Is this right way of writing the code. I am new to c# . so asking this. Might be i am doing some silly mistake. Plz suggest if code after client.UploadData(); is correct place to add the code to bring the data table from link to excel file.

As of now i have tried running the code after writing this. But data is not getting populated in excel although no error is coming.

If i am trying running same url in browser then after doing SSOAuthentication data is getting displayed in browser in the form of a data table.

Plz suggest where i am making the mistake

Hi Sachin, plz suggest how can v pass the request to get token and then send the excel request along with token to authenticate.

Thanks in advance for Your efforts
Sachin Singh4y ago
You first need to issue a post request with username and password from your windows app's method using httpclient or anything to fetch the token. Then issue the get request to fill data to excel along with the token as request header.