public void RecalculateAvqCallLimitReachedError(IRibbonControl control)
{
Avq.CallLimitReachedError = false;
FfeExcel.FindCells((string)Avq.AvqExcelErrorCallLimitReached(), new ExcelFindOptions()
{
LookIn = XlFindLookIn.xlValues,
LookAt = XlLookAt.xlWhole,
MatchCase = true
}).Refresh("QAV");
}
public static object QCBF([ExcelArgument(Name = "ISIN/WKN", Description = "International Securities Identification Number or Wertpapierkennnummer (Germany)")]
string isin_wkn,
[ExcelArgument(Name = "Stock Exchange", Description = "Stock exchange.\nExample: GAT (=Tradegate)")]
string exchange = "",
[ExcelArgument(Name = "Info", Description = "Info to query.\nValid values: Price\nDefault: Price")]
string info = "price")
{
var value = QueryConsorsbank(isin_wkn, exchange, info);
try
{
// Price
if (value.CbqInfo == CbqInfo.price)
{
string xPath = CbqSetting.Default.QCB_Price_XPath_Currency;
string currency = value.WebParser.SelectByXPath(xPath);
if (!String.IsNullOrWhiteSpace(currency))
{
//TODO: Test if method is invoked by Excel formula.
ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
Microsoft.Office.Interop.Excel.Range refRange = FfeExcel.ReferenceToRange(reference);
refRange.NumberFormat = CbqSetting.Default.QCB_Price_NumberFormat.Replace("{CURRENCY}", currency);
}
}
}
catch (Exception ex)
{
if (ex.Source.Contains("Excel") &&
ex.Message.Contains("NumberFormat"))
{
// Ignore exception "Unable to set the NumberFormat property of the Range class".
}
else
{
log.Error(ex.Message);
return(CbqExcelError("FORMAT_ERROR"));
}
}
return(value.Value);
}
private void Refresh(Worksheet worksheet = null, Range range = null, IEnumerable <string> functionNames = null)
{
if (excelApp.Workbooks.Count == 0)
{
return;
}
if (FfeExcel.IsInEditingMode())
{
return;
}
// Preserve the current selected range.
Range currentRange = excelApp.Selection;
// Save the current state of Excel settings.
var screenUpdateState = excelApp.ScreenUpdating;
//var statusBarState = excelApp.DisplayStatusBar;
var displayAlertsState = excelApp.DisplayAlerts;
//var eventsState = excelApp.EnableEvents;
// TODO: Sheet-level setting: var displayPageBreakState = excelApp.ActiveSheet.DisplayPageBreaks;
//var interactiveState = excelApp.Interactive;
//var calcState = excelApp.Calculation;
try
{
excelApp.ScreenUpdating = false;
//excelApp.DisplayStatusBar = false;
excelApp.DisplayAlerts = false;
//excelApp.EnableEvents = false;
// TODO: Sheet-level setting: excelApp.ActiveSheet.DisplayPageBreaks = false;
//excelApp.Interactive = false;
//excelApp.Calculation = XlCalculation.xlCalculationManual;
this.functionNames = functionNames ?? this.functionNames ?? GetFfeFunctionNames();
foreach (string functionName in this.functionNames)
{
if (functionName.StartsWith("QAV") &&
AvqSetting.Default.StopRefreshAtFirstCallLimitReachedError &&
Avq.CallLimitReachedError)
{
log.Debug("(Re-)Calculation of AVQ functions were stopped due to Stop Refresh At First #AV_CALL_LIMIT_REACHED error setting.");
// Stop recalulation for AVQ functions other than the first one found.
continue;
}
List <Range> cells;
string formula = functionName + "(";
if (range == null)
{
cells = FfeExcel.FindCellsByFormula(formula, worksheet);
}
else
{
cells = FfeExcel.FindCellsInRangeByFormula(formula, range);
}
if (cells.Count > 0)
{
//excelApp.StatusBar = $"Recalculate {functionName}...";
FfeExcel.Refresh(cells, functionName);
}
}
}
finally
{
//excelApp.Calculation = calcState;
//excelApp.Interactive = interactiveState;
// TODO: Sheet-level setting: excelApp.ActiveSheet.DisplayPageBreaks = displayPageBreakState;
//excelApp.EnableEvents = eventsState;
excelApp.DisplayAlerts = displayAlertsState;
//excelApp.DisplayStatusBar = statusBarState;
excelApp.ScreenUpdating = screenUpdateState;
//excelApp.StatusBar = null;
// Go back to the preserved selected range.
currentRange.Worksheet.Select();
currentRange.Select();
Avq.CallLimitReachedError = false;
}
}