AnsweredAssumed Answered

'Calc Failed' with interpolated values

Question asked by eradwan on Apr 20, 2016
Latest reply on Apr 28, 2016 by gregor

Hi There,

 

I'm using the following code to get the hourly values of some calculated tags. the strange behavior is, I get 'Calc Failed' as a value for the more recent dates, while same tags and time frame works fine from Data Link! In the beginning, I thought its a date format issue as I'm using UK culture but that didn't help. Your help here is appreciated. Screenshot attached.

 

 

 

try

            {

                IWorkbook workBook = SpreadsheetExtension.GetCurrentDocument("Spreadsheet");

 

 

 

 

                Worksheet dataSheet = workBook.Worksheets[0];

 

 

                AFDatabase eMDatabase = (AFDatabase) System.Web.HttpContext.Current.Application["eMDatabase"];

 

 

                AFCategory nOxWeeklyEmissions = eMDatabase.AttributeCategories["NOxWeeklyEmissions"];

 

 

                AFAttributeList nOxAttribList = AFAttribute.FindElementAttributes(eMDatabase,null, "*",null, null, AFElementType.Any, "*", nOxWeeklyEmissions, TypeCode.Empty, true, AFSortField.Name, AFSortOrder.Ascending, 100);

 

 

 

 

                CultureInfo culture = CultureInfo.CreateSpecificCulture("en-GB");

                var strDate = fromDate.Substring(0, 24);

                DateTime startSearchDate = DateTime.ParseExact(strDate, "ddd MMM d yyyy HH:mm:ss", culture);

                string sDate = startSearchDate.ToString("dd/MMM/yyyy HH:mm:ss",culture);

                string eDate = (startSearchDate.AddDays(7)).ToString("dd/MMM/yyyy HH:mm:ss", culture);

 

 

                string[] dates = new string[169];

                for (int i = 0; i < dates.Length; i++)

                {

                    dates[i] = startSearchDate.ToString("dd/MM/yyyy HH:mm");

                    startSearchDate = startSearchDate.AddHours(1);

                }

                WorksheetExtensions.Import(dataSheet, dates, 5, 0, true);

 

 

                timeRange = new AFTimeRange(new AFTime(sDate), new AFTime(eDate));

 

 

                PIPagingConfiguration config = new PIPagingConfiguration(PIPageType.TagCount,100);

 

 

 

 

                IEnumerable<AFValues> interpolatedValues = nOxAttribList.Data.InterpolatedValues(timeRange, AFTimeSpan.Parse("1h"), null,false, config);

 

 

 

 

                foreach (var afValues in interpolatedValues)

                {

                    string[] readingsConverted = new string[afValues.Count];

 

 

                    for (int i = 0; i < afValues.Count; i++)

                    {

                        readingsConverted[i] = afValues[i].ToString();

                    }

 

 

                    AFValue firstRowIndexValue = afValues.Attribute.Attributes["firstRowIndex"].GetValue();

                    int firstRowIndex = Convert.ToInt16(firstRowIndexValue.Value);

 

 

                    AFValue firstColumnIndexValue = afValues.Attribute.Attributes["firstColumnIndex"].GetValue();

                    int firstColumnIndex = Convert.ToInt16(firstColumnIndexValue.Value);

 

 

                   

 

 

                    WorksheetExtensions.Import(dataSheet, readingsConverted, firstRowIndex, firstColumnIndex, true);

 

 

                    Cell cell = dataSheet.Cells[firstRowIndex, firstColumnIndex];

                    Range startCell = cell.GetRangeWithRelativeReference();

 

 

                    for (int i = 1; i < 170; i++)

                    {

                        decimal n;

                        bool isDecimal = decimal.TryParse(startCell.Value.ToString(), out n);

 

 

                        if (isDecimal)

                        {

                            if (n > 55)

                            {

                                startCell.FillColor = System.Drawing.Color.Yellow;

                            }

                            else

                            {

                                startCell.FillColor = System.Drawing.Color.White;

                            }

                            startCell.Value = Math.Round(n, 3).ToString();

                            startCell.NumberFormat = "0.###";

                        }

                        else

                        {

                            startCell.FillColor = System.Drawing.Color.OrangeRed;

                        }

 

 

                        startCell = startCell.Offset(1, 0);

                       

                    }

                   

                }

 

 

            }

 

 

            catch (Exception ex)

            {

                Logger.Error(ex);

            }

Attachments

Outcomes