Excel reports builder for Creatio

Devlabs refer us to the Community for support for their Excel reports builder for Creatio app, hence my post.

 

Is it possible to retrieve an excel spreadsheet in a business process (to be emailed as an attachment)?  I have tried a web service approach (see here and here), I am able to authenticate with the `AuthService.svc` service, but get an authentication error when trying to access the `GetExportFiltersKey` endpoint.

 

Creatio trial, 7.18 configuration.

 

Thanks,

Like 0

Like

4 comments

The code I'm using to access the web service (based on this) is as follows, the cookie is definitely retrieved (though I'm not 100% sure what the `_appUrl` should be):

string _output = "";
string _userName = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
string _userPassword = "XXXXXXXXXXXX";
string _authServiceUrl = "https://XXXXXX-crm-bundle.creatio.com/ServiceModel/AuthService.svc/Login";
string _authServiceUrl2 = "https://XXXXXX-crm-bundle.creatio.com/0/rest/IntExcelReportService/GetExportFiltersKey";
string _appUrl = "https://XXXXXX-crm-bundle.creatio.com/";
var _authCookie = new CookieContainer();
 
TryLogin();
TryForKey();
Set<string>("ProcessSchemarequestResult", _output);
 
// Sends a request to the authentication service and processes the response.
void TryLogin() {
    var authData = @"{
        ""UserName"":""" + _userName + @""",
        ""UserPassword"":""" + _userPassword + @"""
    }";
    var request = CreateRequest(_authServiceUrl, authData);
    //_authCookie = new CookieContainer();
    request.CookieContainer = _authCookie;
    // Upon successful authentication, we save authentication cookies for
    // further use in requests to Creatio. In case of failure
    // authentication application console displays a message about the reason
    // of the mistake.
    using (var response = (HttpWebResponse)request.GetResponse())
    {
        if (response.StatusCode == HttpStatusCode.OK)
        {
            using (var reader = new StreamReader(response.GetResponseStream()))
            {
                var responseMessage = reader.ReadToEnd();
                //Console.WriteLine(responseMessage);
                _output = _output + responseMessage + "\n\n";
                if (responseMessage.Contains("\"Code\":1"))
                {
                    throw new UnauthorizedAccessException($"Unauthorized {_userName} for {_appUrl}");
                }
            }
            string authName = ".ASPXAUTH";
            string authCookeValue = response.Cookies[authName].Value;
            _authCookie.Add(new Uri(_appUrl), new Cookie(authName, authCookeValue));
        }
        else
        {
        	_output = _output + response.StatusDescription + "\n\n";
        }
    }
}
 
void TryForKey() {
	string esqStr = "{\"rootSchemaName\":\"Contact\",\"operationType\":0,\"includeProcessExecutionData\":true,\"filters\":{\"items\":{\"a99615bf-66bc-4ad4-b6da-e2d8949e090b\":{\"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 : \"ebf37bad-134c-423e-af16-aa0897522de6\"";
    var request = CreateRequest(_authServiceUrl2, callData);
    request.CookieContainer = _authCookie;
    using (var response = (HttpWebResponse)request.GetResponse())
    {
        if (response.StatusCode == HttpStatusCode.OK)
        {
            using (var reader = new StreamReader(response.GetResponseStream()))
            {
                var responseMessage = reader.ReadToEnd();
                //Console.WriteLine(responseMessage);
                _output = _output + responseMessage + "\n\n";
                if (responseMessage.Contains("\"Code\":1"))
                {
                    throw new UnauthorizedAccessException($"Unauthorized {_userName} for {_appUrl}");
                }
            }
        }
        else
        {
        	_output = _output + response.StatusDescription + "\n\n";
        }
    }
}
 
// Create request to the authentication service.
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;
        }
 
return true;

 

The error that results:

System.Net.WebException: The remote server returned an error: (401) Unauthorized.
   at System.Net.HttpWebRequest.GetResponse()
   at Terrasoft.Core.Process.UsrProcess_3608a7dMethodsWrapper.<ScriptTask1Execute>g__TryForKey|1_1(<>c__DisplayClass1_0& )
   at Terrasoft.Core.Process.UsrProcess_3608a7dMethodsWrapper.ScriptTask1Execute(ProcessExecutingContext context)
   at Terrasoft.Core.Process.ProcessScriptTask.InternalExecute(ProcessExecutingContext context)
   at Terrasoft.Core.Process.ProcessFlowElement.ExecuteItem(ProcessExecutingContext context)
   at Terrasoft.Core.Process.ProcessFlowElement.Execute(ProcessExecutingContext context)

 

Gareth Osler,

I was able to implement this in the end, if anyone happens this way and would like to know the solution by all means DM me.

Gareth Osler,

hello Gareth i want to do the same to export a forecast in business process can you please share with me the solution 

Show all comments