public static object rxCreateArrays()
{
ExcelReference caller = Excel(xlfCaller) as ExcelReference;
object result = RxExcel.Observe("rxCreateArrays", null,
() => Observable.Generate(
new List <object> {
1, 2, 3
},
lst => true,
lst => { lst.Add((int)lst[lst.Count - 1] + 1); return(lst); },
lst => Transpose(lst.ToArray()),
lst => TimeSpan.FromSeconds(1)));
if (result.Equals(ExcelError.ExcelErrorNA))
{
result = new object[, ] {
{ result }
};
}
return(ArrayResizer.Resize((object[, ])result, caller));
}
//[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");
}
// 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);
}
}
}
public static object Resize(object[,] array, ExcelReference caller)
{
if (caller == null)
{
Debug.Print("Resize - Abandoning - No Caller");
return(array);
}
int rows = array.GetLength(0);
int columns = array.GetLength(1);
if ((caller.RowLast - caller.RowFirst + 1 != rows) ||
(caller.ColumnLast - caller.ColumnFirst + 1 != columns))
{
// Size problem: enqueue job, call async update and return #N/A
EnqueueResize(caller, rows, columns);
ExcelAsyncUtil.QueueAsMacro(DoResizing);
}
// Size is already OK - just return result
return(array);
}
public static object GetCellCommentText(
[ExcelArgument(Description = "带批注的单元格", AllowReference = true)] object srcRange)
{
ExcelReference excelReference = srcRange as ExcelReference;
IExcel.Range excelRange = excelReference.ToPiaRange();
if (excelRange.Cells.Count > 1)
{
return(ExcelError.ExcelErrorNA);
}
else
{
if (excelRange.Comment != null)
{
return(excelRange.Comment.Text());
}
else
{
return(ExcelEmpty.Value);
}
}
}
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);
}
/// <summary>Initializes a new instance of the <see cref="ExcelTableQueryHeader"/> class.
/// </summary>
/// <param name="excelReference">The Excel Range ('header').</param>
/// <param name="excelDataQueryName">The name of the Excel data query.</param>
/// <param name="maxRowCount">The maximal number of rows to take into account.</param>
/// <exception cref="ArgumentException">Thrown, if the value of <paramref name="excelReference"/> is not represented by an two-dimensional array (for example a single row header) or a <see cref="System.String"/>.</exception>
public ExcelTableQueryHeader(ExcelReference excelReference, string excelDataQueryName, int maxRowCount)
{
if (excelReference == null)
{
throw new ArgumentNullException("excelRangeValue");
}
m_HeaderRange = excelReference;
m_BelowHeaderRange = new ExcelReference(m_HeaderRange.RowFirst + 1, maxRowCount - m_HeaderRange.RowLast - 1, m_HeaderRange.ColumnFirst, m_HeaderRange.ColumnLast, m_HeaderRange.SheetId);
var value = m_HeaderRange.GetValue();
if (value is object[, ])
{
m_HeaderData = (object[, ])value;
RowCount = m_HeaderData.GetLength(0);
ColumnCount = m_HeaderData.GetLength(1);
}
else if (value is String)
{
m_HeaderData = new object[1, 1];
m_HeaderData[0, 0] = value as String;
ColumnCount = 1;
}
else
{
throw new ArgumentException("excelRangeValue");
}
RowCount = (m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1) + (m_BelowHeaderRange.RowLast - m_BelowHeaderRange.RowFirst + 1);
m_BelowHeaderData = m_BelowHeaderRange.GetValue() as object[, ];
if (excelDataQueryName == null)
{
throw new ArgumentNullException("excelDataQueryName");
}
Name = LongName = new IdentifierString(excelDataQueryName);
m_GuidedExcelDataQuery = new GuidedExcelDataQuery(excelDataQueryName, rowCount: RowCount, columnCount: ColumnCount);
}
/// <summary>Creates a specific <see cref="IExcelDataQuery"/> object.
/// </summary>
/// <param name="propertyNames">The Excel Range (exactly one row or one column) which contains a list of property names.</param>
/// <param name="propertyValues">The Excel range (exactly one row or one column) which contains the values of the properties.</param>
/// <param name="excelDataQueryName">The name of the <see cref="IExcelDataQuery"/> object.</param>
/// <returns>A <see cref="IExcelDataQuery"/> object which represents the specific user input.</returns>
public static IExcelDataQuery Create(object propertyNames, object propertyValues, string excelDataQueryName = "General Properties")
{
if ((IsEmpty(propertyNames) == true) || (IsEmpty(propertyValues) == true))
{
return(new ExcelNullQuery(excelDataQueryName));
}
else if ((propertyNames is object[, ]) && (propertyValues is object[, ]))
{
throw new NotImplementedException("Use 'ExcelArgument' attribute and set 'AllowReference=true'.");
}
else if ((propertyNames is ExcelReference) && (propertyValues is ExcelReference))
{
ExcelReference xlPropertyNames = (ExcelReference)propertyNames;
ExcelReference xlPropertyValues = (ExcelReference)propertyValues;
if (xlPropertyNames.ColumnLast - xlPropertyNames.ColumnFirst == 0) // the properties (name/value) are given row-wise
{
if (xlPropertyNames.RowLast - xlPropertyNames.RowFirst == 0) // exact one property given
{
return(new ExcelPropertyQuerySingle(xlPropertyNames, xlPropertyValues, excelDataQueryName));
}
return(new ExcelPropertyQueryRowWise(xlPropertyNames, xlPropertyValues, excelDataQueryName));
}
else if (xlPropertyNames.RowLast - xlPropertyNames.RowFirst == 0) // the properties (name/values) are given column-wise
{
return(new ExcelPropertyQueryColumnWise(xlPropertyNames, xlPropertyValues, excelDataQueryName));
}
else
{
throw new ArgumentException("The Excel Range which represents properties must have exactly one row or one column.");
}
}
else
{
return(new ExcelPropertyQuerySingle(propertyNames, propertyValues, excelDataQueryName));
}
}
public static void UploadSelection(ExcelReference selection)
{
if (selection == null)
{
return;
}
// Get all the items currently "Waiting", update their status and send for processing
var waiting = UploadItems.Where(item => item.Status == UploadStatus.Waiting && IsInsideSelection(item.Caller)).ToList();
foreach (var item in waiting)
{
item.SetStatus(UploadStatus.InProgress);
}
PerformUploads(waiting);
// We just check the top left of the caller
bool IsInsideSelection(ExcelReference reference)
{
if (reference == null)
{
return(false);
}
if (reference.SheetId != selection.SheetId)
{
return(false);
}
return(reference.RowFirst >= selection.RowFirst &&
reference.RowFirst <= selection.RowLast &&
reference.ColumnFirst >= selection.ColumnFirst &&
reference.ColumnFirst <= selection.ColumnLast);
}
}
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);
}
public static object GetRangeAddress(
[ExcelArgument(Description = "输入需获取地址的单元格区域，获取本身地址可省略输入", AllowReference = true)] object srcRange,
[ExcelArgument(Description = "是否绝对引用返回引用的行部分，默认为否")] bool isRowAbsolute,
[ExcelArgument(Description = "是否绝对引用返回引用的列部分，默认为否")] bool isColumnAbsolute)
{
IExcel.Range excelRange = null;
if (srcRange is ExcelMissing)
{
excelRange = Common.ExcelApp.ActiveCell;
}
else
{
ExcelReference excelReference = srcRange as ExcelReference;
if (excelReference != null)
{
excelRange = excelReference.ToPiaRange();
}
else
{
return(ExcelError.ExcelErrorRef);
}
}
return(excelRange.Address[isRowAbsolute, isColumnAbsolute]);
}
private static void WriteData(SheetHelper excelWriter, ExcelReference reference)
{
try
{
if (excelWriter.ConfirmedOverwrite == false)
{
Common.StatusBar.AddMessage(Locale.English.WarningOverwriteNotAccepted);
}
else
{
var range = Tools.ReferenceToRange(reference);
ExcelAsyncUtil.QueueAsMacro(() =>
{
excelWriter.PopulateData(range);
Common.StatusBar.AddMessage(Locale.English.UdfDataWritingSuccess);
});
}
}
catch (Exception e)
{
Common.StatusBar.AddMessage(Locale.English.UdfCompleteError);
Common.HandlePotentialQuandlError(e, false);
}
}
/// <summary>
/// 单元格范围地址
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public static string Address(this ExcelReference range)
{
string sheetName = range.SheetNameLocal();
return($"{sheetName}!{range.AddressLocal()}");
}
/// <summary>
/// 返回给定<see cref="ExcelReference"/>是否为单一单元格
/// </summary>
/// <param name="reference"></param>
/// <returns></returns>
private static bool IsSingleCell(this ExcelReference reference)
{
return(reference.ColumnFirst == reference.ColumnLast && reference.RowFirst == reference.RowLast);
}
/// <summary>
/// 单元格所在 工作表 本地名称,不包括 Workbook 名称
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public static string SheetNameLocal(this ExcelReference range)
{
string sheetName = range.SheetName();
return(sheetName.Substring(sheetName.IndexOf(']') + 1));
}
public static string WorkbookName(this ExcelReference range)
{
return((string)XlCall.Excel(XlCall.xlfGetCell, 66, range));
}
public static object Resize(object[,] array)
{
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
return(Resize(array, caller));
}
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 ExcelPrecedent(ExcelReference reference, ExcelFormula parentFormula, string parentFormulaSrc, ExcelReference parentReference)
{
reference_ = reference;
parentFormula_ = parentFormula;
parentFormulaSrc_ = parentFormulaSrc;
parentReference_ = parentReference;
}
public MyDialog(ExcelReference sheetReference)
{
this.LoadViewFromUri("/MyApp;component/mynamespace/mydialog.xaml");
}
static void EnqueueResize(ExcelReference caller, int rows, int columns)
{
ExcelReference target = new ExcelReference(caller.RowFirst, caller.RowFirst + rows - 1, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId);
ResizeJobs.Enqueue(target);
}
/// <summary>
/// 获取指定单元格是否包含公式
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public static bool HasFormula(this ExcelReference range)
{
var formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, range);
return(!string.IsNullOrEmpty(formula));
}
public Precedents(ExcelReference src)
{
src_ = src;
}
public static T GetValue <T>(this ExcelReference range)
{
return(range.GetValue().ConvertTo <T>());
}
// TODO Handle INDIRECT, etc.
// TODO Static cache (string-ExcelFormula).
// TODO Fix for R1C1 mode.
// TODO Fix external references.
private IEnumerable <ExcelPrecedent> GetPrecedents(ExcelReference reference)
{
List <ExcelPrecedent> items;
if (cache_.TryGetValue(reference, out items))
{
foreach (var item in items)
{
yield return(item);
foreach (var p in GetPrecedents(item.Reference))
{
yield return(p);
}
}
}
else
{
bool isFormula = (bool)XlCall.Excel(XlCall.xlfGetCell, 48, reference);
if (isFormula)
{
string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 6, reference);
ExcelFormula excelFormula = new ExcelFormula(formula);
foreach (var token in excelFormula)
{
if (token.Type == ExcelFormulaTokenType.Operand &&
token.Subtype == ExcelFormulaTokenSubtype.Range)
{
//if (isRCmode)
//{
// var regex = new Regex(
// @"^=(?:(?<Sheet>[^!]+)!)?(?:R((?<RAbs>\d+)|(?<RRel>\[-?\d+\]))C((?<CAbs>\d+)|(?<CRel>\[-?\d+\]))){1,2}$",
// RegexOptions.Compiled | RegexOptions.IgnoreCase);
// if (regex.IsMatch(formula))
// {
// throw new NotSupportedException();
// }
//}
var range = token.Value;
Match rangeMatch = rangeRegex_.Match(range);
var col1 = ExcelAColumnToInt(rangeMatch.Groups["Col1"].Value) - 1;
var row1 = Int32.Parse(rangeMatch.Groups["Row1"].Value) - 1;
Group sheetGroup = rangeMatch.Groups["Sheet"];
var sheetName = sheetGroup.Success ? sheetGroup.Value : null;
int col2 = col1;
int row2 = row1;
if (rangeMatch.Groups["Col2"].Success)
{
col2 = ExcelAColumnToInt(rangeMatch.Groups["Col2"].Value) - 1;
row2 = Int32.Parse(rangeMatch.Groups["Row2"].Value) - 1;
}
for (int col = col1; col <= col2; col++)
{
for (int row = row1; row <= row2; row++)
{
ExcelReference precedantRef;
if (sheetName == null)
{
precedantRef = new ExcelReference(row, row, col, col, reference.SheetId);
}
else
{
precedantRef = new ExcelReference(row, row, col, col, sheetName);
}
ExcelPrecedent newPrecedent = new ExcelPrecedent(
precedantRef, excelFormula, formula, reference);
AddToCache(reference, newPrecedent);
yield return(newPrecedent);
foreach (var nestedPrecedant in GetPrecedents(precedantRef))
{
yield return(nestedPrecedant);
}
}
}
}
}
}
}
}
/// <summary>
/// 下方 下一个单元格 不包括当前单元格
/// 遇到空单元格结束
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public static IEnumerable <ExcelReference> NextRows(this ExcelReference range)
{
return(range.NextRows(1));
}
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;
}
/// <summary>
/// 获取 单元格所在 工作表的 名称
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public static string SheetRef(this ExcelReference range)
{
return((string)XlCall.Excel(XlCall.xlfGetCell, 62, range));
}
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);
}
/// <summary>
/// 单元格所在 工作表名称,包括 Workbook 名称
/// [BookName]SheetName
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public static string SheetName(this ExcelReference range)
{
return((string)XlCall.Excel(XlCall.xlSheetNm, range));
}
