/// <summary>
/// Resets the color scheme of the parameter rows in the parameter sheet
/// </summary>
/// <param name="application">
/// The Excel application.
/// </param>
/// <param name="workbook">
/// The current <see cref="Workbook"/>.
/// </param>
public void ResetRows(Application application, Workbook workbook)
{
var sw = new Stopwatch();
sw.Start();
application.Cursor = XlMousePointer.xlWait;
application.StatusBar = "CDP4: Reset row color scheme in Parameter sheet";
this.parameterSheet = ParameterSheetUtilities.RetrieveParameterSheet(workbook);
try
{
var parameterRange = this.parameterSheet.Range(ParameterSheetConstants.ParameterRangeName);
parameterRange.Interior.Color = XlRgbColor.rgbWhite;
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, true);
}
catch (Exception ex)
{
logger.Error(ex);
}
finally
{
application.Cursor = XlMousePointer.xlDefault;
sw.Stop();
application.StatusBar = string.Format("CDP4: Reset row color scheme in {0} [ms]", sw.ElapsedMilliseconds);
}
}
/// <summary>
/// Highlight the rows of the <see cref="Thing"/>s in the <paramref name="things"/>
/// </summary>
/// <param name="application">
/// The Excel application.
/// </param>
/// <param name="workbook">
/// The current <see cref="Workbook"/>.
/// </param>
/// <param name="things">
/// The <see cref="Thing"/>s that need to be highlighted in the <see cref="Workbook"/>
/// </param>
public void HighlightRows(Application application, Workbook workbook, IReadOnlyDictionary <Guid, ProcessedValueSet> processedValueSets)
{
var sw = new Stopwatch();
sw.Start();
application.Cursor = XlMousePointer.xlWait;
application.StatusBar = "Highlighting rows in Parameter sheet";
this.parameterSheet = ParameterSheetUtilities.RetrieveParameterSheet(workbook);
try
{
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, false);
var parameterRange = this.parameterSheet.Range(ParameterSheetConstants.ParameterRangeName);
this.ResetDefaultColorScheme(parameterRange);
this.parameterContent = (object[, ])parameterRange.Value;
var currentRow = parameterRange.Row;
application.StatusBar = string.Format("Processing Parameter sheet - row: {0}", currentRow);
for (var i = 1; i < this.parameterContent.GetLength(0); i++)
{
var rowType = (string)this.parameterContent[i, ParameterSheetConstants.TypeColumn];
if (rowType != null)
{
var rowIid = Convert.ToString(this.parameterContent[i, ParameterSheetConstants.IdColumn]);
var rowIidChar = rowIid.Split(':');
var thingIid = rowIidChar[0];
Guid iid;
var isIid = Guid.TryParse(thingIid, out iid);
//TODO: fix for compites
if (isIid && processedValueSets.ContainsKey(iid))
{
var row = parameterRange.Rows[i];
row.Interior.Color = XlRgbColor.rgbYellow;
}
}
currentRow++;
}
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, true);
}
catch (Exception ex)
{
logger.Error(ex);
}
finally
{
application.Cursor = XlMousePointer.xlDefault;
sw.Stop();
application.StatusBar = string.Format("Highlighted rows in {0} [ms]", sw.ElapsedMilliseconds);
}
}
/// <summary>
/// Validate the values on the Parameter sheet and check for any changes that have been made by the user
/// </summary>
/// <param name="application">
/// The excel application object that contains the <see cref="Workbook"/> in which the parameter sheet is to be processed.
/// </param>
/// <param name="workbook">
/// The <see cref="Workbook"/> that contains the Parameter sheet that is being processed
/// </param>
/// <param name="processedValueSets">
/// A <see cref="List{ProcessedValueSet}"/> of clones that capture the updated <see cref="Thing"/>s with its value validation result
/// </param>
/// <remarks>
/// Changed cells will be highlighted, cells containing invalid or out-of-bounds data will be marked.
/// </remarks>
public void ValidateValuesAndCheckForChanges(Application application, Workbook workbook, out IReadOnlyDictionary <Guid, ProcessedValueSet> processedValueSets)
{
var sw = new Stopwatch();
sw.Start();
application.Cursor = XlMousePointer.xlWait;
application.StatusBar = "Processing Parameter sheet";
this.parameterSheet = ParameterSheetUtilities.RetrieveParameterSheet(workbook);
var temporaryProcessedValueSets = new Dictionary <Guid, ProcessedValueSet>();
var numberFormatInfo = this.QuerayNumberFormatInfo(application);
var rowType = string.Empty;
var rowIid = string.Empty;
try
{
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, false);
var parameterRange = this.parameterSheet.Range(ParameterSheetConstants.ParameterRangeName);
this.parameterContent = (object[, ])parameterRange.Value;
this.parameterFormula = (object[, ])parameterRange.Formula;
var currentRow = parameterRange.Row;
application.StatusBar = string.Format("Processing Parameter sheet - row: {0}", currentRow);
var parameterContentRows = this.parameterContent.GetLength(0) + 1;
for (var i = 1; i < parameterContentRows; i++)
{
try
{
rowType = (string)this.parameterContent[i, ParameterSheetConstants.TypeColumn];
}
catch (Exception ex)
{
logger.Error(ex);
}
if (rowType != string.Empty &&
(rowType == ParameterSheetConstants.PVS || rowType == ParameterSheetConstants.PVSCT ||
rowType == ParameterSheetConstants.POVS || rowType == ParameterSheetConstants.POVSCT ||
rowType == ParameterSheetConstants.PSVS || rowType == ParameterSheetConstants.PSVSCT))
{
rowIid = Convert.ToString(this.parameterContent[i, ParameterSheetConstants.IdColumn]);
var computedValue = this.QueryComputedValue(i);
var formulaValue = this.QueryFormulaValue(i);
var manualValue = this.QueryManualValue(i);
var referenceValue = this.QueryReferenceValue(i);
var switchValue = this.QuerySwitchValue(i);
var actualValue = this.QueryActualValue(i);
var rowIidChar = rowIid.Split(':');
var thingIid = rowIidChar[0];
var componentIndex = 0;
if (rowIidChar.Length > 1)
{
componentIndex = int.Parse(rowIidChar[1]);
}
var lazyThing = this.workbookSession.Assembler.Cache.Select(item => item.Value).SingleOrDefault(item => item.Value.Iid == Guid.Parse(thingIid));
if (lazyThing != null)
{
var thing = lazyThing.Value;
if (rowType == ParameterSheetConstants.PVS || rowType == ParameterSheetConstants.PVSCT)
{
var parameterValueSet = (ParameterValueSet)thing;
this.ProcessValueSet(parameterValueSet, componentIndex, currentRow, manualValue, computedValue, referenceValue, actualValue, switchValue, formulaValue, ref temporaryProcessedValueSets, numberFormatInfo);
}
if (rowType == ParameterSheetConstants.POVS || rowType == ParameterSheetConstants.POVSCT)
{
var parameterOverrideValueSet = (ParameterOverrideValueSet)thing;
this.ProcessValueSet(parameterOverrideValueSet, componentIndex, currentRow, manualValue, computedValue, referenceValue, actualValue, switchValue, formulaValue, ref temporaryProcessedValueSets, numberFormatInfo);
}
if (rowType == ParameterSheetConstants.PSVS || rowType == ParameterSheetConstants.PSVSCT)
{
var parameterSubscriptionValueSet = (ParameterSubscriptionValueSet)thing;
this.ProcessValueSet(parameterSubscriptionValueSet, componentIndex, currentRow, manualValue, switchValue, ref temporaryProcessedValueSets, numberFormatInfo);
}
}
else
{
logger.Warn("The Thing of RowType {0} with unique id {1} could not be found in the workbook cache", rowType, thingIid);
}
}
currentRow++;
}
ParameterSheetUtilities.ApplyLocking(this.parameterSheet, true);
}
catch (Exception ex)
{
if (rowType != string.Empty && rowIid != string.Empty)
{
this.errorMessage = $"{rowType}:{rowIid} - {ex.Message}";
}
else
{
this.errorMessage = ex.Message;
}
logger.Error(ex);
}
finally
{
application.Cursor = XlMousePointer.xlDefault;
sw.Stop();
if (string.IsNullOrEmpty(this.errorMessage))
{
application.StatusBar = string.Format("CDP4: Parameter sheet processed in {0} [ms]", sw.ElapsedMilliseconds);
}
else
{
application.StatusBar = string.Format("CDP4: The following error occured while processing the sheet: {0}", this.errorMessage);
}
processedValueSets = new Dictionary <Guid, ProcessedValueSet>(temporaryProcessedValueSets);
}
}