void IRtdServer.DisconnectData(int topicId)
{
try
{
Topic topic;
if (!_activeTopics.TryGetValue(topicId, out topic))
{
return;
}
_activeTopics.Remove(topicId);
using (XlCall.Suspend())
{
DisconnectData(topic);
topic.OnDisconnected();
}
}
catch (Exception e)
{
Logger.RtdServer.Error("Error in RTD server {0} DisconnectData: {1}", GetType().Name, e.ToString());
}
}
object IRtdServer.ConnectData(int topicId, ref Array strings, ref bool newValues)
{
try
{
List <string> topicInfo = new List <string>(strings.Length);
for (int i = 0; i < strings.Length; i++)
{
topicInfo.Add((string)strings.GetValue(i));
}
Topic topic = new Topic(this, topicId);
_activeTopics[topicId] = topic;
using (XlCall.Suspend())
{
return(ConnectData(topic, topicInfo, ref newValues));
}
}
catch (Exception e)
{
Logging.LogDisplay.WriteLine("Error in RTD server {0} ConnectData: {1}", GetType().Name, e.ToString());
return(null);
}
}
// Update cache
private static void UpdateCache(Workbook book)
{
for (int i = 0; i < book.Worksheets.Count; i++)
{
Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
if (sheet != null)
{
ExcelReference xref = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, sheet.Name);
ExcelReference xused = new ExcelReference(
sheet.UsedRange.Row,
sheet.UsedRange.Row + sheet.UsedRange.Rows.Count,
sheet.UsedRange.Column,
sheet.UsedRange.Column + sheet.UsedRange.Columns.Count,
xref.SheetId);
if (_usedRanges.ContainsKey(xref.SheetId))
{
_usedRanges.Remove(xref.SheetId);
}
_usedRanges.Add(xref.SheetId, xused);
}
}
}
//[ExcelFunction(Name = "Markdown", Description = "About xlMdDna")]
public static string Markdown(dynamic[,] args)
{
initEnd = init();
caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
wb = (Workbook)xl.ActiveWorkbook;
ws = (Worksheet)xl.ActiveSheet;
rng = (Range)ws.Cells[caller.RowFirst + 1, caller.ColumnFirst + 1];
shapName = $"{wb.Name}_{ws.Name}_{rng.Address[false, false]}";
var buf = getArgsString(args);
md = string.Join("\n", buf).Replace("\u00A0", " ");
try {
getPreviewWindow(md.Trim(), $"{shapName}.html");
}
catch (Exception ex) {
Clipboard.SetText($"Err: mermaidFail\n{ex.Message}");
return("NG");
}
return("OK");
}
public static object s2tcache(
[ExcelArgument(Name = "QueryKey", Description = "tiingo query key in s2cfg!C")] string qkey,
[ExcelArgument(Name = "XOffset", Description = "column offset to cache position. 0 default")] int xoffset,
[ExcelArgument(Name = "YOffset", Description = "row offset to cache position. 0 default")] int yoffset,
[ExcelArgument(Name = "Trigger", Description = "dummy to trigger recalc")] object trigger)
{
if (!s_Cache.ContainsTiingoKey(qkey))
{
return(ExcelMissing.Value);
}
// Figure out our caller's posn in the sheet; that's the cell we'll pull from the cache.
// If offsets are supplied use them to calc cell posn too. xoffset & yoffset will default
// to 0 if not supplied in the sheet.
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
string val = s_Cache.GetTiingoCell(qkey, caller.RowFirst - yoffset, caller.ColumnFirst - xoffset);
if (val == null)
{
return(ExcelError.ExcelErrorNA); // ExcelMissing.Value;
}
return(val);
}
private static void MarkUdfNonVolatile()
{
// Async functions should be non-volatile only.
#region #8.6.5 in Financial Applications Using Excel Add-in Development in C/C++
/* Functions registered as macro-sheet equivalents, type #, and as taking xloper or
* xloper12 arguments, type R and U, rather than the value-only types P and Q, are by
* default volatile. This echoes the behaviour of XLM macro sheets when the ARGUMENT()
* function was used with the parameter 8 to specify that a given argument should be left
* as a reference. The logic behind Excel treating these functions as volatile is that if you
* want to calculate something based on the reference, i.e. the location of a cell, then you
* must recalculate every time in case the location has changed but the value has stayed the
* same.
* It is possible to alter the volatile status of an XLL function with a call to the C
* API function xlfVolatile, passing a Boolean false xloper/xloper12 argument.
* However, there are reports that this can confuse Excel’s order-of-recalculation logic, so
* the advice would be to decide at the outset whether your functions need to be volatile or
* not, and stick with that. */
#endregion
XlCall.Excel(XlCall.xlfVolatile, false);
}
public HRESULT CreateInstance([In] IntPtr pUnkOuter, [In] ref IID riid, [Out] out IntPtr ppvObject)
{
// Suspend the C API (helps to prevent some Excel-crashing scenarios)
using (XlCall.Suspend())
{
ppvObject = IntPtr.Zero;
object instance = Activator.CreateInstance(_comClass.Type);
// If not an ExcelRtdServer, create safe wrapper that also maps types.
if (_comClass.IsRtdServer && !instance.GetType().IsSubclassOf(typeof(ExcelRtdServer)))
{
// wrap instance in RtdWrapper
RtdServerWrapper rtdServerWrapper = new RtdServerWrapper(instance, _comClass.ProgId);
instance = rtdServerWrapper;
}
if (pUnkOuter != IntPtr.Zero)
{
// For now no aggregation support - could do Marshal.CreateAggregatedObject?
return(ComAPI.CLASS_E_NOAGGREGATION);
}
if (riid == ComAPI.guidIUnknown)
{
ppvObject = Marshal.GetIUnknownForObject(instance);
}
else
{
ppvObject = Marshal.GetIUnknownForObject(instance);
HRESULT hrQI = Marshal.QueryInterface(ppvObject, ref riid, out ppvObject);
Marshal.Release(ppvObject);
if (hrQI != ComAPI.S_OK)
{
return(ComAPI.E_NOINTERFACE);
}
}
return(ComAPI.S_OK);
}
}
void IRtdServer.ServerTerminate()
{
try
{
// The Unregister call here just tells the reg-free loading that we are gone,
// to ensure a fresh load with new 'fake progid' next time.
// Also safe to call (basically a no-op) if we are not loaded via reg-free, but via real COM Server.
RtdRegistration.UnregisterRTDServer(RegisteredProgId);
if (_updateSync != null)
{
_updateSync.DeregisterUpdateNotify(_callbackObject);
}
using (XlCall.Suspend())
{
ServerTerminate();
}
}
catch (Exception e)
{
Logging.LogDisplay.WriteLine("Error in RTD server {0} ServerTerminate: {1}", GetType().Name, e.ToString());
}
}
// We could do something like this:
public static bool SetResults(object[] asyncHandles, object[] results)
{
object unusedResult;
XlCall.XlReturn callReturn = XlCall.TryExcel(XlCall.xlAsyncReturn, out unusedResult, asyncHandles, results);
if (callReturn == XlCall.XlReturn.XlReturnSuccess)
{
// The normal case - value has been accepted
return(true);
}
if (callReturn == XlCall.XlReturn.XlReturnInvAsynchronousContext)
{
// This is expected sometimes (e.g. calculation was cancelled)
// Excel will show #VALUE
Debug.WriteLine("Warning: InvalidAsyncContext returned from xlAsyncReturn []");
return(false);
}
// This is never unexpected
Debug.WriteLine("Error: Unexpected error from xlAsyncReturn []");
return(false);
}
private void LogState()
{
string msg;
if (IsEvaluatingBatchSeemsToBeFinished)
{
msg = "";
}
else
{
int calculating = evalStates_.Values.Count(_ => _ == EvalState.ObservableCreated);
int calculated = evalStates_.Values.Count(_ => _ == EvalState.ObservableFinished);
int scheduled =
evalStates_.Values.Count(_ => _ == EvalState.Scheduled || _ == EvalState.ForcingFormulaUpdate);
msg = String.Format("{0}: Evaluating async funcs ({1} calculating, {2} calculated, {3} scheduled)...",
batchId_,
calculating,
calculated,
scheduled);
}
ExcelAsyncUtil.QueueAsMacro(() => XlCall.Excel(XlCall.xlcMessage, true, msg));
}
// Returns true if the Excel call succeeded.
private static bool TryCallRTD(out object result, string progId, string server, params string[] topics)
{
object[] args = new object[topics.Length + 2];
args[0] = progId;
args[1] = null;
topics.CopyTo(args, 2);
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlfRtd, out result, args);
if (retval == XlCall.XlReturn.XlReturnSuccess)
{
// All is good
return(true);
}
if (retval == XlCall.XlReturn.XlReturnUncalced)
{
// An expected error - the first call in an array-group seems to always return this,
// to be followed by one call for each element in the array (where xlfRtd succceeds).
Debug.Print("### RTD Call failed. Excel returned {0}", retval);
result = null;
return(false);
}
// Unexpected error - throw for the user to deal with
throw new XlCallException(retval);
}
private static object GetApplicationFromNewWorkbook(bool allowProtected, out bool isProtected)
{
// Create new workbook with the right stuff
// Echo calls removed for Excel 2013 - this caused trouble in the Excel 2013 'browse' scenario.
object application;
bool isExcelPre15 = SafeIsExcelVersionPre15;
if (isExcelPre15)
{
XlCall.Excel(XlCall.xlcEcho, false);
}
try
{
XlCall.Excel(XlCall.xlcNew, 5);
XlCall.Excel(XlCall.xlcWorkbookInsert, 6);
// Try again
application = GetApplicationFromWindows(allowProtected, out isProtected);
XlCall.Excel(XlCall.xlcFileClose, false);
}
catch
{
// Not expecting this ever - but be consistent about Try vs. exceptions
application = null;
isProtected = false;
}
finally
{
if (isExcelPre15)
{
XlCall.Excel(XlCall.xlcEcho, true);
}
}
return(application); // Might be null in a bad case, but we have no further ideas
}
object IRtdServer.ConnectData(int topicId, ref Array strings, ref bool newValues)
{
try
{
// Check for an active topic with the same topicId
// - this is unexpected, but is reported as a bug in an earlier Excel version.
// (Thanks ngm)
// (Does not address the Excel 2010 bug documented here:
// http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/ba06ac78-7b64-449b-bce4-9a03ac91f0eb/
// fixed by hotfix: http://support.microsoft.com/kb/2405840
// and SP1 )
if (_activeTopics.ContainsKey(topicId))
{
((IRtdServer)this).DisconnectData(topicId);
}
List <string> topicInfo = new List <string>(strings.Length);
for (int i = 0; i < strings.Length; i++)
{
topicInfo.Add((string)strings.GetValue(i));
}
Topic topic = new Topic(this, topicId);
_activeTopics[topicId] = topic;
using (XlCall.Suspend())
{
return(ConnectData(topic, topicInfo, ref newValues));
}
}
catch (Exception e)
{
Logging.LogDisplay.WriteLine("Error in RTD server {0} ConnectData: {1}", GetType().Name, e.ToString());
return(null);
}
}
public static void ReturnToSelection(Action action)
{
bool updating = ScreenUpdating;
try {
if (updating)
{
ScreenUpdating = false;
}
//remember the current active cell
object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
action.Invoke();
//go back to old selection
XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
} finally {
if (updating)
{
XLApp.ScreenUpdating = true;
}
}
}
void LoadAddins()
{
var ais = Session.Contexts.SelectMany(f => f.Addins);
logger.Debug($"LoadGlobalsFirst is '{Session.LoadGlobalsFirst}'");
if (Session.LoadGlobalsFirst)
{
ais = Session.Addins.Concat(ais);
}
else
{
ais = ais.Concat(Session.Addins);
}
foreach (var ai in ais)
{
if (!File.Exists(ai.Path))
{
logger.Error($"Cannot find file '{ai.Path}'.");
continue;
}
XlCall.Excel(XlCall.xlcMessage, true, $"Loading {Path.GetFileName(ai.Path)}...");
if (Path.GetExtension(ai.Path).Trim().ToUpperInvariant() == ".XLL")
{
LoadXLL(ai.Path);
}
else
{
ComLoadAddin(ai.Path, ai.ReadOnly);
}
XlCall.Excel(XlCall.xlcMessage, false);
}
}
static string GetDefaultDateFormat()
{
var result = XlCall.Excel(XlCall.xlfGetWorkspace, 37) as object[, ];
int i = 16;
string date_seperator = (string)result[0, i++];
string time_seperator = (string)result[0, i++];
string year_symbol = (string)result[0, i++];
string month_symbol = (string)result[0, i++];
string day_symbol = (string)result[0, i++];
string hour_symbol = (string)result[0, i++];
string minute_symbol = (string)result[0, i++];
string second_symbol = (string)result[0, i++];
//32 Number indicating the date order
//0 = Month-Day-Year
//1 = Day-Month-Year
//2 = Year-Month-Day
double date_order = (double)result[0, 31];
day_symbol = day_symbol + day_symbol;
month_symbol = month_symbol + month_symbol;
year_symbol = string.Concat(year_symbol, year_symbol, year_symbol, year_symbol);
if (date_order == 0)
{
return(month_symbol + date_seperator + day_symbol + date_seperator + year_symbol);
}
else if (date_order == 1)
{
return(day_symbol + date_seperator + month_symbol + date_seperator + year_symbol);
}
else
{
return(year_symbol + date_seperator + month_symbol + date_seperator + day_symbol);
}
}
public static object[,] SplitIntoWords(string phrase, string delimiters = " ", bool removeBlanks = false)
{
ExcelReference callerRef = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
int cols = callerRef.ColumnLast - callerRef.ColumnFirst + 1;
//create arrays for values, bins and output
object[,] res = new object[1, cols];
StringSplitOptions opt = removeBlanks ? StringSplitOptions.RemoveEmptyEntries : StringSplitOptions.None;
string[] words = phrase.Split(delimiters.ToCharArray(), opt);
//transfer all values to value array
for (int j = 0; j < cols; j++)
{
res[0, j] = "";
if (j < words.Length)
{
res[0, j] = words[j];
}
}
return(res);
}
// This is the private implementation of the IRtdServer interface
int IRtdServer.ServerStart(IRTDUpdateEvent callbackObject)
{
try
{
_updateSync = SynchronizationManager.RtdUpdateSynchronization;
if (_updateSync == null)
{
// CONSIDER: A better message to alert user of problem here?
return(0);
}
_callbackObject = callbackObject;
_updateSync.RegisterUpdateNotify(_callbackObject);
using (XlCall.Suspend())
{
return(ServerStart() ? 1 : 0);
}
}
catch (Exception e)
{
Logger.RtdServer.Error("Error in RTD server {0} ServerStart: {1}", GetType().Name, e.ToString());
return(0);
}
}
public static void ExportXmlMappedListToHDF5()
{
String sFileName = "test.h5";
ExcelReference selection = null;
MOIE.Application ma = null;
MOIE.Range mr = null;
MOIE.XmlMap aXmlMap = null;
MOIE.ListObject aListObject = null;
RecordSetMD aRecordSetMD = null;
//using isContinuing instead of throwing on last steps
Boolean isContinuing = true;
try {
selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
ma = (ExcelDnaUtil.Application as MOIE.Application);
mr = ma.Evaluate(XlCall.Excel(XlCall.xlfReftext, selection, true)) as MOIE.Range;
try {
aListObject = mr.ListObject;
aXmlMap = aListObject.XmlMap;
}
catch (Exception) {
throw new com.WDataSci.WDS.WDSException("Error: could not pull XmlMap from selection");
}
aRecordSetMD = new RecordSetMD(RecordSetMDEnums.eMode.Internal)
.cAs(RecordSetMDEnums.eType.HDF5, RecordSetMDEnums.eSchemaType.XSD)
;
aRecordSetMD.SchemaMatter.InputSchema = new XmlDocument();
aRecordSetMD.SchemaMatter.InputSchema.LoadXml(aXmlMap.Schemas.Item[1].XML);
MessageBoxButtons msgboxbuttons = MessageBoxButtons.YesNoCancel;
DialogResult msgboxresponse = MessageBox.Show("Write HDF5 file from XmlMap'd ListObject of selection?", "Confirm", msgboxbuttons);
isContinuing = (isContinuing && msgboxresponse == System.Windows.Forms.DialogResult.Yes);
if (isContinuing)
{
using (SaveFileDialog aSaveFileDialog = new SaveFileDialog()) {
aSaveFileDialog.InitialDirectory = ma.ActiveWorkbook.Path;
aSaveFileDialog.Filter = "HDF5 Files (*.h5)|*.h5|All Files (*.*)|*.*";
aSaveFileDialog.FilterIndex = 1;
aSaveFileDialog.RestoreDirectory = true;
aSaveFileDialog.FileName = sFileName;
aSaveFileDialog.AddExtension = true;
//aSaveFileDialog.CheckFileExists = true;
aSaveFileDialog.CheckPathExists = true;
aSaveFileDialog.Title = "Export XmlMap'd ListObject to HDF5 (*.h5) File....";
if (aSaveFileDialog.ShowDialog() == DialogResult.OK)
{
sFileName = aSaveFileDialog.FileName;
if (!sFileName.ToLower().EndsWith(".h5"))
{
sFileName += ".h5";
}
}
else
{
isContinuing = false;
}
}
}
if (isContinuing)
{
aRecordSetMD
.cToFile(sFileName)
.cWithDataSetName("RecordSet")
.mReadMapFor(null, null, true);
int nColumns = aRecordSetMD.nColumns();
if (aListObject.ListColumns.Count != nColumns)
{
throw new com.WDataSci.WDS.WDSException("ListObject Column Count Does Not Match Schema Node List Count!");
}
aRecordSetMD.HDF5Matter.mWriteRecordSet(aRecordSetMD, aListObject);
}
}
catch (com.WDataSci.WDS.WDSException e) {
MessageBox.Show(e.getMessage());
}
catch (Exception e) {
com.WDataSci.WDS.WDSException we = new com.WDataSci.WDS.WDSException("Error in ExportXmlMappedListToHDF5 to " + sFileName, e);
MessageBox.Show(we.getMessage());
}
finally {
selection = null;
aListObject = null;
aXmlMap = null;
mr = null;
ma = null;
if (aRecordSetMD != null)
{
aRecordSetMD.Dispose();
}
aRecordSetMD = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return;
}
public static void ImportHDF5CompoundDS()
{
String sFileName = "test.h5";
ExcelReference selection = null;
MOIE.Application tapp = null;
MOIE.Workbook twb = null;
MOIE.Sheets twbSheets = null;
MOIE.Worksheet tws = null;
MOIE.Range tblr = null;
MOIE.Range trng = null;
MOIE.Range trng2 = null;
MOIE.ListObject tbl = null;
tapp = (ExcelDnaUtil.Application as MOIE.Application);
Boolean screenupdating_prior = tapp.ScreenUpdating;
MOIE.XlCalculation calculation_prior = tapp.Calculation;
RecordSetMD aRecordSetMD = null;
RecordSet aRecordSet = null;
//using isContinuing instead of throwing on last steps
Boolean isContinuing = true;
try {
//tapp.ScreenUpdating = false;
tapp.Calculation = MOIE.XlCalculation.xlCalculationManual;
int i, iP1, ii, iiP1, j, jP1, jj, jjP1;
using (OpenFileDialog aOpenFileDialog = new OpenFileDialog()) {
aOpenFileDialog.InitialDirectory = tapp.ActiveWorkbook.Path;
aOpenFileDialog.Filter = "HDF5 Files (*.h5)|*.h5|All Files (*.*)|*.*";
aOpenFileDialog.FilterIndex = 1;
aOpenFileDialog.RestoreDirectory = true;
aOpenFileDialog.CheckPathExists = true;
aOpenFileDialog.CheckFileExists = true;
aOpenFileDialog.FileName = sFileName;
aOpenFileDialog.AddExtension = true;
aOpenFileDialog.Title = "Import compound data from HDF5 (*.h5) File....";
if (aOpenFileDialog.ShowDialog() == DialogResult.OK)
{
sFileName = aOpenFileDialog.FileName;
}
else
{
isContinuing = false;
}
}
if (isContinuing)
{
String hDSPath = tapp.InputBox("Input path (with or without initial root /), if left as \"/\" and there is only 1 data set, that is used", "HDF5 path to dataset in file", "/");
aRecordSetMD = new RecordSetMD(RecordSetMDEnums.eMode.Input)
.cAs(RecordSetMDEnums.eType.HDF5)
.cFromFile(sFileName)
.cWithDataSetName(hDSPath)
.mReadMapFor(null, null, true)
;
aRecordSet = new RecordSet()
.cAsInput()
.mReadRecordSet(aRecordSetMD)
;
MessageBoxButtons msgboxbuttons = MessageBoxButtons.YesNoCancel;
DialogResult msgboxresponse;
msgboxresponse = MessageBox.Show("Write to a new sheet (Yes) or point to cell for the upper left corder (No)?", "Confirm", msgboxbuttons);
if (msgboxresponse == System.Windows.Forms.DialogResult.Cancel)
{
throw new com.WDataSci.WDS.WDSException("Cancel");
}
if (msgboxresponse == System.Windows.Forms.DialogResult.No)
{
try {
selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
trng = tapp.Evaluate(XlCall.Excel(XlCall.xlfReftext, selection, true)) as MOIE.Range;
trng2 = tapp.InputBox("Enter cell address (navigable)", "Output Location", trng.Address.ToString(), 100, 100, "", 0, 8) as MOIE.Range;
trng = null;
tws = trng2.Parent;
twb = tws.Parent;
}
catch {
throw new com.WDataSci.WDS.WDSException("Cancel");
}
}
else
{
twb = tapp.ActiveWorkbook;
twbSheets = twb.Sheets;
tws = twbSheets.Add();
twbSheets = null;
trng2 = tws.Cells[1, 1];
try {
tws.Name = aRecordSetMD.HDF5Matter.DSName;
}
catch (Exception e) {
String s = tapp.InputBox("Cannot name sheet to " + aRecordSetMD.HDF5Matter.DSName, "New Sheet Name", "Leave-As-Is", 100, 100, "");
if (!s.Equals("Leave-As-Is"))
{
try {
tws.Name = s;
}
catch (Exception) {
}
}
}
}
int nRows = aRecordSet.Records.Count;
int nColumns = aRecordSetMD.nColumns();
tapp.ScreenUpdating = false;
tapp.Calculation = MOIE.XlCalculation.xlCalculationManual;
for (jj = 0, jjP1 = 1; jj < nColumns; jj++, jjP1++)
{
trng2.Offset[0, jj].Value2 = aRecordSetMD.Column[jj].Name;
for (i = 0, iP1 = 1; i < nRows; i++, iP1++)
{
trng2.Offset[iP1, jj].Value2 = aRecordSet.Records_Orig[i][jj];
}
}
tblr = tws.Range[trng2, trng2.Offset[nRows, nColumns - 1]];
tbl = (MOIE.ListObject)tws.ListObjects.AddEx(MOIE.XlListObjectSourceType.xlSrcRange, tblr, null, MOIE.XlYesNoGuess.xlYes);
}
}
catch (com.WDataSci.WDS.WDSException e) {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
MessageBox.Show(e.getMessage());
}
catch (Exception e) {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
MessageBox.Show(e.Message);
}
finally {
if (tapp.ScreenUpdating != screenupdating_prior)
{
tapp.ScreenUpdating = screenupdating_prior;
}
if (tapp.Calculation != calculation_prior)
{
tapp.Calculation = calculation_prior;
}
//Queuing up for GC
selection = null;
if (aRecordSetMD != null)
{
aRecordSetMD.Dispose();
}
aRecordSetMD = null;
if (aRecordSet != null)
{
aRecordSet.Dispose();
}
aRecordSet = null;
tapp = null;
twb = null;
twbSheets = null;
tws = null;
trng = null;
trng2 = null;
tblr = null;
tbl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return;
}
static void RunMacro(object macroName)
{
XlCall.Excel(XlCall.xlcRun, macroName);
}
// Forwarded from XlCall
// Loads the RTD server with temporary ProgId.
// CAUTION: Might fail when called from array formula (the first call in every array-group fails).
// When it fails, the xlfRtd call returns xlReturnUncalced.
// In that case, this function returns null, and does not keep a reference to the created server object.
// The next call should then succeed (though a new server object will be created).
public static bool TryRTD(out object result, string progId, string server, params string[] topics)
{
Debug.Print("### RtdRegistration.RTD " + progId);
// Check if this is any of our business.
Type rtdServerType;
if (!string.IsNullOrEmpty(server) || !registeredRtdServerTypes.TryGetValue(progId, out rtdServerType))
{
// Just pass on to Excel.
return(TryCallRTD(out result, progId, null, topics));
}
// TODO: Check that ExcelRtdServer with stable ProgId case also works right here -
// might need to add to loadedRtdServers somehow
// Check if already loaded.
string loadedProgId;
if (loadedRtdServers.TryGetValue(progId, out loadedProgId))
{
if (ExcelRtd2010BugHelper.ExcelVersionHasRtdBug && rtdServerType.IsSubclassOf(typeof(ExcelRtdServer)))
{
ExcelRtd2010BugHelper.RecordRtdCall(progId, topics);
}
// Call Excel using the synthetic RtdSrv_xxx (or actual from attribute) ProgId
return(TryCallRTD(out result, loadedProgId, null, topics));
}
// Not loaded already - need to get the Rtd server loaded
// TODO: Need to reconsider registration here.....
// Sometimes need stable ProgIds.
object rtdServer;
if (ExcelRtd2010BugHelper.ExcelVersionHasRtdBug && rtdServerType.IsSubclassOf(typeof(ExcelRtdServer)))
{
Debug.Print("### Creating Wrapper " + progId);
rtdServer = new ExcelRtd2010BugHelper(progId, rtdServerType);
}
else
{
using (XlCall.Suspend())
{
rtdServer = Activator.CreateInstance(rtdServerType);
}
ExcelRtdServer excelRtdServer = rtdServer as ExcelRtdServer;
if (excelRtdServer != null)
{
// Set ProgId so that it can be 'unregistered' (removed from loadedRtdServers) when the RTD server terminates.
excelRtdServer.RegisteredProgId = progId;
}
else
{
// Make a wrapper if we are not an ExcelRtdServer
// (ExcelRtdServer implements exception-handling and XLCall supension itself)
rtdServer = new RtdServerWrapper(rtdServer, progId);
}
}
// We pick a new Guid as ClassId for this add-in...
CLSID clsId = Guid.NewGuid();
// ... (bad idea - this will cause Excel to try to load this RTD server while it is not registered.)
// Guid typeGuid = GuidUtilit.CreateGuid(..., DnaLibrary.XllPath + ":" + rtdServerType.FullName);
// or something based on ExcelDnaUtil.XllGuid
// string progIdRegistered = "RtdSrv_" + typeGuid.ToString("N");
// by making a fresh progId, we are sure Excel will try to load when we are ready.
// Change from RtdSrv.xxx to RtdSrv_xxx to avoid McAfee bug that blocks registry writes with a "." anywhere
string progIdRegistered = "RtdSrv_" + clsId.ToString("N");
Debug.Print("RTD - Using ProgId: {0} for type: {1}", progIdRegistered, rtdServerType.FullName);
try
{
using (new SingletonClassFactoryRegistration(rtdServer, clsId))
using (new ProgIdRegistration(progIdRegistered, clsId))
using (new ClsIdRegistration(clsId, progIdRegistered))
{
Debug.Print("### About to call TryCallRTD " + progId);
if (TryCallRTD(out result, progIdRegistered, null, topics))
{
// Mark as loaded - ServerTerminate in the wrapper will remove.
loadedRtdServers[progId] = progIdRegistered;
Debug.Print("### Added to loadedRtdServers " + progId);
return(true);
}
return(false);
}
}
catch (UnauthorizedAccessException secex)
{
Logger.RtdServer.Error("The RTD server of type {0} required by add-in {1} could not be registered.\r\nThis may be due to restricted permissions on the user's HKCU\\Software\\Classes key.\r\nError message: {2}", rtdServerType.FullName, DnaLibrary.CurrentLibrary.Name, secex.Message);
result = ExcelErrorUtil.ToComError(ExcelError.ExcelErrorValue);
// Return true to have the #VALUE stick, just as it was before the array-call refactoring
return(true);
}
catch (Exception ex)
{
Logger.RtdServer.Error("The RTD server of type {0} required by add-in {1} could not be registered.\r\nThis is an unexpected error.\r\nError message: {2}", rtdServerType.FullName, DnaLibrary.CurrentLibrary.Name, ex.Message);
Debug.Print("RtdRegistration.RTD exception: " + ex.ToString());
result = ExcelErrorUtil.ToComError(ExcelError.ExcelErrorValue);
// Return true to have the #VALUE stick, just as it was before the array-call refactoring
return(true);
}
}
public static long dnaConversionToInt64([ExcelArgument(AllowReference = true)] object arg)
{
double numResult = (double)XlCall.Excel(XlCall.xlCoerce, arg, (int)XlType12.XlTypeNumber);
return((long)Math.Round(numResult, MidpointRounding.AwayFromZero));
}
// Understanding newValues
// -----------------------
// As input: If Excel has a cached value to display, newValues passed in will be false.
// On return: if newValues is now false, Excel will use cached value if it has one, (else #N/A if passed in as true)
// if newValues is now true, Excel will use the value returned by ConnectData.
object IRtdServer.ConnectData(int topicId, ref Array strings, ref bool newValues)
{
try
{
// Check for an active topic with the same topicId
// - this is unexpected, but is reported as a bug in an earlier Excel version.
// (Thanks ngm)
// (Does not address the Excel 2010 bug documented here:
// http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/ba06ac78-7b64-449b-bce4-9a03ac91f0eb/
// fixed by hotfix: http://support.microsoft.com/kb/2405840 and SP1. This problem is fixed by the ExcelRtd2010BugHelper. )
if (_activeTopics.ContainsKey(topicId))
{
using (XlCall.Suspend())
{
((IRtdServer)this).DisconnectData(topicId);
}
}
List <string> topicInfo = new List <string>(strings.Length);
for (int i = 0; i < strings.Length; i++)
{
topicInfo.Add((string)strings.GetValue(i));
}
Topic topic;
using (XlCall.Suspend())
{
// We create the topic, but what if its value is set here...?
topic = CreateTopic(topicId, topicInfo);
}
if (topic == null)
{
Logger.RtdServer.Error("Error in RTD server {0} CreateTopic returned null.", GetType().Name);
// Not sure what to return here for error. We try the COM error version of #VALUE !?
return(ExcelErrorUtil.ToComError(ExcelError.ExcelErrorValue));
}
// NOTE: 2016-11-04
// Before v 0.34 the topic was added to _activeTopics before ConnectData was called
// The effect of moving it after (hence that topic is not in _activeTopics during the ConnectData call)
// is that a call to UpdateValue during the the ConnectData call will no longer cause an Update call to Excel
// (since SetDirty is ignored for topics not in _activeTopics)
object value;
using (XlCall.Suspend())
{
value = ConnectData(topic, topicInfo, ref newValues);
}
_activeTopics[topicId] = topic;
// Now we need to ensure that the topic value does indeed agree with the returned value
// Otherwise we are left with an inconsistent state for future updates.
// If there's a difference, we do force the update.
if (!object.Equals(value, topic.Value))
{
// 2020-03-03 v1.1
// Changing from topic.UpdateNotify, which no longer (in recent Excel) seems to work on its own.
// NOTE: In the unusual case that the FixValue inside Topic makes value == topic.Value, we won't get an update here
// E.g. for long strings that are truncated
//
topic.UpdateValue(value);
}
return(value);
}
catch (Exception e)
{
Logger.RtdServer.Error("Error in RTD server {0} ConnectData: {1}", GetType().Name, e.ToString());
// Not sure what to return here for error. We try the COM error version of #VALUE !?
return(ExcelErrorUtil.ToComError(ExcelError.ExcelErrorValue));
}
}
public static object WEBSOCKET(
[ExcelArgument(Description = "", Name = "銘柄コード")] string symbol,
[ExcelArgument(Description = "", Name = "市場コード")] string exchange,
[ExcelArgument(Description = "", Name = "項目名")] string itemName)
{
try
{
string ResultMessage = Validate.ValidateRtdBoard(_websocketStream, symbol, exchange, itemName);
if (!string.IsNullOrEmpty(ResultMessage))
{
return(ResultMessage);
}
if (!_websocketStream && CustomRibbon._updatePressed)
{
middleware.StartWebSocket();
}
object ret = null;
if (CustomRibbon._updatePressed)
{
ret = XlCall.RTD(RtdBoard.WebApiRequestServerProgId, null, "WEBSOCKET");
}
if (!CustomRibbon._env)
{
return(0);
}
Dictionary <string, Tuple <DateTime, BoardElement> > _Cache = _websocketCache;
object returnData = "";
if (_Cache.Count > 0)
{
var tplKey = symbol + "-" + exchange;
Tuple <DateTime, BoardElement> tpl;
if (_Cache.TryGetValue(tplKey, out tpl))
{
returnData = BoardResult.GetBoardItem(tpl.Item2, symbol, int.Parse(exchange), itemName, false);
}
// 銘柄コード、市場コードのキーでキャッシュに該当データが無い場合、指数としてキャッシュをチェック
if (string.IsNullOrEmpty(returnData.ToString()))
{
// Nullの場合、キャッシュには"0"で登録されるため、市場コ－ド"0"でキャッシュをチェック
tplKey = symbol + "-" + "0";
if (_Cache.TryGetValue(tplKey, out tpl))
{
returnData = BoardResult.GetBoardItem(tpl.Item2, symbol, int.Parse(exchange), itemName, false);
}
}
}
return(returnData);
}
catch (Exception exception)
{
if (exception.InnerException == null)
{
return(exception.Message);
}
else
{
return(exception.InnerException.Message);
}
}
}
public static double dnaConversionToDouble([ExcelArgument(AllowReference = true)] object arg)
{
return((double)XlCall.Excel(XlCall.xlCoerce, arg, (int)XlType12.XlTypeNumber));
}
public static string dnaConversionToString([ExcelArgument(AllowReference = true)] object arg)
{
return((string)XlCall.Excel(XlCall.xlCoerce, arg, (int)XlType12.XlTypeString));
}
public static bool dnaConversionToBoolean([ExcelArgument(AllowReference = true)] object arg)
{
// This is the full gamut we need to support
return((bool)XlCall.Excel(XlCall.xlCoerce, arg, (int)XlType12.XlTypeBoolean));
}
public static object[,] GetHistoricalDataFromYahoo(
[ExcelArgument("Yahoo Ticker")] string ticker,
[ExcelArgument("From Date")] DateTime fromDate,
[ExcelArgument("To Date")] DateTime toDate)
{
var begin = fromDate;
var end = toDate;
var yahooURL =
@"http://ichart.finance.yahoo.com/table.csv?s=" +
ticker + @"&a=" + (begin.Month - 1).ToString(CultureInfo.InvariantCulture) + @"&b=" + begin.Day.ToString(CultureInfo.InvariantCulture) +
@"&c=" + begin.Year.ToString(CultureInfo.InvariantCulture) + @"&d=" + (end.Month - 1).ToString(CultureInfo.InvariantCulture) + @"&e=" + end.Day.ToString(CultureInfo.InvariantCulture) + @"&f=" + end.Year.ToString(CultureInfo.InvariantCulture) +
@"&g=d&ignore=.csv";
// string historicalData;
// var webConnection = new WebClient();
// try
// {
// historicalData = webConnection.DownloadString(yahooURL);
// }
// catch (WebException ex)
// {
// throw new Exception("Unable to download the data! Check your Internet Connection!", ex);
// }
// finally
// {
// webConnection.Dispose();
// }
//
// historicalData = historicalData.Replace("\r", string.Empty);
// var rows = historicalData.Split('\n');
// var headings = rows[0].Split(',');
var rnd = new Random();
var excelData = new object[10, 5];
for (var i = 0; i < 5; ++i)
{
excelData[0, i] = i.ToWords();
}
for (var i = 1; i < 10; ++i)
{
for (var j = 0; j < 5; ++j)
{
excelData[i, j] = rnd.Next(1, 10000);
}
}
// for (var i = 1; i < rows.Length; ++i)
// {
// var thisRow = rows[i].Split(',');
// if (thisRow.Length == headings.Length)
// {
// excelData[i, 0] = DateTime.Parse(thisRow[0]);
// for (var j = 1; j < headings.Length; ++j)
// {
// excelData[i, j] = double.Parse(thisRow[j]);
// }
// }
// }
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
ExcelAsyncUtil.QueueAsMacro(() =>
{
ArrayResizer.Resize(excelData, caller);
});
return(excelData);
}
public static string QSERIES(
[ExcelArgument(Name = "quandlCode",
Description = "Single or multiple Quandl codes with optional columns references", AllowReference = true)
] object rawQuandlCodeColumns,
[ExcelArgument(Name = "dateRange", Description = "(optional) The date or range of dates to filter on", AllowReference = true)] object rawDates = null,
[ExcelArgument(Name = "frequency", Description = "(optional) Change the sampling frequency of the returned data", AllowReference = true
)] string rawCollapse = null,
[ExcelArgument(Name = "order", Description = "(optional) Order the data is returned in",
AllowReference = true)] string rawOrder = null,
[ExcelArgument(Name = "transformation", Description = "(optional) How the data is to be transformed",
AllowReference = true)] string rawTransformation = null,
[ExcelArgument(Name = "limit", Description = "(optional) Limit the number of rows returned",
AllowReference = true)] object rawLimit = null,
[ExcelArgument(Name = "headers",
Description = "(optional) Default: true - Whether the resulting data will include a header row",
AllowReference = true)] string rawHeader = null,
[ExcelArgument(Name = "dates",
Description = "(optional) Default: true - Whether the resulting data will include a dates column",
AllowReference = true)] string rawDateColumn = null,
[ExcelArgument(Name = "transpose",
Description = "(optional) Default: false - Transpose the resulting data matrix, dates will be displayed in one row rather than a column",
AllowReference = true)] string rawTranspose = null
)
{
try
{
// Need to reset cell volatility on each run-through
Tools.SetCellVolatile(false);
// Prevent the formula from running should it be turned off.
if (QuandlConfig.PreventCurrentExecution)
{
return(Locale.English.AutoDownloadTurnedOff);
}
// Parse out all the parameters specified in the UDF as well as the calling cell.
var reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
var quandlCodeColumns = Tools.GetArrayOfValues(rawQuandlCodeColumns).Select(s => s.ToString().ToUpper()).ToList();
var dates = Tools.GetArrayOfDates(rawDates);
var collapse = Tools.GetStringValue(rawCollapse);
var orderAsc = Tools.GetStringValue(rawOrder).ToLower() == "asc";
var transformation = Tools.GetStringValue(rawTransformation);
var limit = Tools.GetIntValue(rawLimit);
var includeHeader = string.IsNullOrEmpty(rawHeader) || Tools.GetBoolValue(rawHeader);
var includeDates = string.IsNullOrEmpty(rawDateColumn) || Tools.GetBoolValue(rawDateColumn);
var transpose = !string.IsNullOrEmpty(rawTranspose) || Tools.GetBoolValue(rawTranspose);
// Update status bar so the user knows which function is currently running.
Common.StatusBar.AddMessage($"{Locale.English.UdfRetrievingData} QSERIES({{{string.Join(", ", quandlCodeColumns)}}}, {{{string.Join(", ", dates)}}}, {collapse}, {orderAsc}, {transformation}, {limit}, {includeHeader}, {includeDates}, {transpose})");
// Pull the data from the server
ResultsData results = null;
try
{
results = RetrieveData(quandlCodeColumns, dates, collapse, transformation, limit, includeDates);
}
catch (DatasetParamError e)
{
return(e.Message);
}
Common.StatusBar.AddMessage(Locale.English.UdfDataRetrievalSuccess);
// Assume the first column is date column
string dateColumn = results.Headers.Select(s => s.ToUpper()).ToList()[0];
// Sort out the data and place it in the cells
var sortedResults = new ResultsData(results.SortedData(dateColumn, orderAsc), results.Headers);
var reorderColumns = sortedResults.ExpandAndReorderColumns(SanitizeColumnNames(quandlCodeColumns), dateColumn, includeDates);
// Enqueue the data to be written out to the sheet when excel is ready to run macro's
SheetHelper excelWriter = new SheetHelper(reorderColumns, includeHeader, true, false, transpose);
WriteData(excelWriter, reference);
// Return the first cell value
return(Utilities.ValidateEmptyData(excelWriter.firstCellValue()));
}
catch (Exception e)
{
string msg = null;
if (e.InnerException != null && e.InnerException is Shared.Errors.QuandlErrorBase)
{
msg = Common.HandlePotentialQuandlError(e, false);
}
else
{
msg = Common.HandlePotentialQuandlError(e, false, new Dictionary <string, string>()
{
{ "UDF", "QSERIES" },
{ "Columns", Utilities.ObjectToHumanString(rawQuandlCodeColumns) },
{ "Dates", Utilities.ObjectToHumanString(rawDates) },
{ "Collapse", Utilities.ObjectToHumanString(rawCollapse) },
{ "Order", Utilities.ObjectToHumanString(rawOrder) },
{ "Transformation", Utilities.ObjectToHumanString(rawTransformation) },
{ "Limit", Utilities.ObjectToHumanString(rawLimit) },
{ "Header", Utilities.ObjectToHumanString(rawHeader) },
{ "DateColumn", Utilities.ObjectToHumanString(rawDateColumn) }
});
}
if (msg == null)
{
msg = Locale.English.UdfCompleteError;
}
return(msg);
}
}
