Excel reports builder for Creatio - devlabs

Hi Gareth,

The application does not include such functionality out of the box.

Could you please tell us more about the way you implemented this functionality?

Specifically, please provide the source code of the functionality, describe how it works, and specify the steps where you receive an error.

Yevhen Vorobiov,

Thank you for your reply.



I am able to retrieve an authentication cookie.  I am also able to retrieve a filter key.  However when I attempt to download the spreadsheet the WebRequest `GetResponse` call results in "The remote server returned an error: (401) Unauthorized."  It works when I query the API using Postman, but not from within a Creatio script task for some reason.



The C# code I am using to download the excel spreadsheet is as follows:

void TryForData() {
	var url = _authServiceUrl3 + "/" + _excelReportName + "/" + _key;
    // This would be for example, "https://130417-crm-bundle.creatio.com/0/rest/IntExcelReportService/GetExportFilteredData//Test_Report/ExportFilterKey_7e4a40f3-2e89-495d-9a66-91bd4a206dc4"
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
    request.Method = "GET";
    request.CookieContainer = new CookieContainer();
    string[] cookiesAry = new string[_authCookie.Count];
    int i = 0;
    foreach (Cookie cook in _authCookie) {
	    	cookiesAry[i++] = (cook.Name + "=" + cook.Value);
    }
    _cookieHeader = string.Join("; ", cookiesAry);
    request.Headers.Add("Cookie", _cookieHeader);
    request.Accept="*/*";
    request.AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate;
    request.KeepAlive  = true;
    using (var response = (HttpWebResponse)request.GetResponse())
    {
        if (response.StatusCode == HttpStatusCode.OK)
        {
        }
        else
        {
        	_output = _output + response.StatusDescription + "\n\n";
        }
    }
}

The full C# code for the script task is as follows:

string _output = "";
string _userName = "XXXXXXXXX@XXXXXXXXXXXXXXX.com";
string _userPassword = "XXXXXXXX";
string _authServiceUrl = "https://130417-crm-bundle.creatio.com/ServiceModel/AuthService.svc/Login";
var _authCookie = new CookieCollection();
string _authServiceUrl2 = "https://130417-crm-bundle.creatio.com/0/rest/IntExcelReportService/GetExportFiltersKey";
string _cookieHeader = "";
string _bpmcsrfHeader = "";
string _key = "";
string _authServiceUrl3 = "https://130417-crm-bundle.creatio.com/0/rest/IntExcelReportService/GetExportFilteredData/";
string _excelReportName = "Test_Report";
string _data = "";
byte[] _file;
 
TryLogin();
TryForKey();
TryForData();
 
Set<string>("ProcessSchemarequestResult", _output);
return true;
 
void TryLogin() {
    var authData = @"{
        ""UserName"":""" + _userName + @""",
        ""UserPassword"":""" + _userPassword + @"""
    }";
    var request = CreateRequest(_authServiceUrl, authData);
    request.CookieContainer = new CookieContainer();
    using (var response = (HttpWebResponse)request.GetResponse())
    {
        if (response.StatusCode == HttpStatusCode.OK)
        {
            using (var reader = new StreamReader(response.GetResponseStream()))
            {
                var responseMessage = reader.ReadToEnd();
                _output = _output + responseMessage + "\n\n";
                if (responseMessage.Contains("\"Code\":1"))
                {
                    throw new UnauthorizedAccessException($"Unauthorized {_userName} for {_authServiceUrl}");
                }
            }
			_authCookie.Add(response.Cookies);
			_output = _output + "_authCookie.Count : " + _authCookie.Count.ToString() + "\n\n";
        }
        else
        {
        	_output = _output + response.StatusDescription + "\n\n";
        }
    }
}
 
void TryForKey() {
	string esqStr = "\"{\\\"rootSchemaName\\\":\\\"Contact\\\",\\\"operationType\\\":0,\\\"includeProcessExecutionData\\\":true,\\\"filters\\\":{\\\"items\\\":{\\\"7cf7bca6-a231-4b19-bde3-44e46b7547cb\\\":{\\\"filterType\\\":1,\\\"comparisonType\\\":3,\\\"isEnabled\\\":true,\\\"trimDateTimeParameterToDate\\\":false,\\\"leftExpression\\\":{\\\"expressionType\\\":0,\\\"columnPath\\\":\\\"Id\\\"},\\\"rightExpression\\\":{\\\"expressionType\\\":2,\\\"parameter\\\":{\\\"dataValueType\\\":1,\\\"value\\\":\\\"98dae6f4-70ae-4f4b-9db5-e4fcb659ef19\\\"}}}},\\\"logicalOperation\\\":0,\\\"isEnabled\\\":true,\\\"filterType\\\":6},\\\"columns\\\":{\\\"items\\\":{\\\"Full name\\\":{\\\"caption\\\":\\\"Full name\\\",\\\"orderDirection\\\":0,\\\"orderPosition\\\":-1,\\\"isVisible\\\":true,\\\"expression\\\":{\\\"expressionType\\\":0,\\\"columnPath\\\":\\\"Name\\\"}}}},\\\"isDistinct\\\":false,\\\"rowCount\\\":-1,\\\"rowsOffset\\\":-1,\\\"isPageable\\\":false,\\\"allColumns\\\":false,\\\"useLocalization\\\":true,\\\"useRecordDeactivation\\\":false,\\\"serverESQCacheParameters\\\":{\\\"cacheLevel\\\":0,\\\"cacheGroup\\\":\\\"\\\",\\\"cacheItemName\\\":\\\"\\\"},\\\"queryOptimize\\\":false,\\\"useMetrics\\\":false,\\\"adminUnitRoleSources\\\":0,\\\"querySource\\\":0,\\\"ignoreDisplayValues\\\":false,\\\"isHierarchical\\\":false}\"";
	string callData = "{\"EsqString\":" + esqStr + ",\"RecordCollection\":[\"98dae6f4-70ae-4f4b-9db5-e4fcb659ef19\"],\"ReportId\":\"e01008d5-8b70-4c84-a7a2-aa8b20126030\"}";
    var request = CreateRequest(_authServiceUrl2, callData);
    request.CookieContainer = new CookieContainer();
    string[] cookiesAry = new string[_authCookie.Count];
    int i = 0;
    foreach (Cookie cook in _authCookie) {
	    	cookiesAry[i++] = (cook.Name + "=" + cook.Value);
    }
    _cookieHeader = string.Join("; ", cookiesAry);
    request.Headers.Add("Cookie", _cookieHeader);
    _bpmcsrfHeader = _authCookie["BPMCSRF"].Value;
	request.Headers.Add("BPMCSRF", _bpmcsrfHeader);
    foreach (string key in request.Headers.AllKeys) {
    	_output = _output + key + " : " + request.Headers.Get(key) + "\n";
    }
    _output = _output + "\n";
    using (var response = (HttpWebResponse)request.GetResponse())
    {
        if (response.StatusCode == HttpStatusCode.OK)
        {
            using (var reader = new StreamReader(response.GetResponseStream()))
            {
                var responseMessage = reader.ReadToEnd();
                _output = _output + responseMessage + "\n\n";
                if (responseMessage.Contains("\"success\":false"))
                {
                    throw new UnauthorizedAccessException($"Unauthorized {_userName} for {_authServiceUrl2}");
                }
                Regex re = new Regex(@".*""key"":""([^""]*)""");
                Match m = re.Match(responseMessage);
                Group g = m.Groups[1];
                _key = g.Value;
                _output = _output + _key + "\n\n";
            }
			_authCookie.Add(response.Cookies);  // There is only one cookie returned
			_output = _output + "_authCookie.Count : " + _authCookie.Count.ToString() + "\n\n";
        }
        else
        {
        	_output = _output + response.StatusDescription + "\n\n";
        }
    }
}
 
void TryForData() {
	var url = _authServiceUrl3 + "/" + _excelReportName + "/" + _key;
	_output = _output + url + "\n\n";
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
    request.Method = "GET";
    request.CookieContainer = new CookieContainer();
    string[] cookiesAry = new string[_authCookie.Count];
    int i = 0;
    foreach (Cookie cook in _authCookie) {
	    	cookiesAry[i++] = (cook.Name + "=" + cook.Value);
    }
    _cookieHeader = string.Join("; ", cookiesAry);
    request.Headers.Add("Cookie", _cookieHeader);
    foreach (string key in request.Headers.AllKeys) {
    	_output = _output + key + " : " + request.Headers.Get(key) + "\n";
    }
    _output = _output + "\n";
    request.Accept="*/*";
    request.AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate;
    request.KeepAlive  = true;
    _output = _output + "request.RequestUri : " + request.RequestUri + "\n\n";
    using (var response = (HttpWebResponse)request.GetResponse())
    {
        if (response.StatusCode == HttpStatusCode.OK)
        {
            //_authCookie = new CookieCollection();
			//_authCookie.Add(response.Cookies);
			//_output = _output + "_authCookie.Count : " + _authCookie.Count.ToString() + "\n\n";
        }
        else
        {
        	_output = _output + response.StatusDescription + "\n\n";
        }
    }
}
 
HttpWebRequest CreateRequest(string url, string requestData = null)
{
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
    request.ContentType = "application/json";
    request.Method = "POST";
    request.KeepAlive = true;
    if (!string.IsNullOrEmpty(requestData))
    {
        using (var requestStream = request.GetRequestStream())
        {
            using (var writer = new StreamWriter(requestStream))
            {
                writer.Write(requestData);
            }
        }
    }
    return request;
}

I've been able to get this working using the higher level `WebClient` API,

        void TryForData()
        {
            var url = _authServiceUrl3 + "/" + _excelReportName + "/" + _key;
            using (WebClient client = new WebClient())
            {
                string[] cookiesAry = new string[_authCookie.Count];
                int i = 0;
                foreach (Cookie cook in _authCookie)
                {
                    cookiesAry[i++] = (cook.Name + "=" + cook.Value);
                }
                _cookieHeader = string.Join("; ", cookiesAry);
                client.Headers.Add(HttpRequestHeader.Cookie, _cookieHeader);
                _data = client.DownloadData(url);
                _output = _output + "_data.Length : " + _data.Length + "\n\n";
                //client.DownloadFile(url, _excelReportName + ".xlsx");
            }
        }

I think the ticket can be closed now.  I'll post the full code at some point.  Thanks for the reply Yevhen.