public static string Combine([ExcelArgument(AllowReference = true)] object range, string separator = "", bool skipBlanks = false)
{
ExcelReference theRef = (ExcelReference)range;
int rows = theRef.RowLast - theRef.RowFirst + 1;
int cols = theRef.ColumnLast - theRef.ColumnFirst + 1;
string res = "";
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < cols; j++)
{
ExcelReference cellRef = new ExcelReference(theRef.RowFirst + i, theRef.RowFirst + i, theRef.ColumnFirst + j, theRef.ColumnFirst + j, theRef.SheetId);
object val = cellRef.GetValue();
if (val is ExcelDna.Integration.ExcelEmpty)
{
if (!skipBlanks)
{
res = res + separator;
}
}
else
{
res = res + cellRef.GetValue().ToString() + separator;
}
}
}
return(res.Substring(0, res.Length - separator.Length));
}
/// <summary>
/// 获取单元格数值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="range"></param>
/// <returns></returns>
public static IEnumerable <T> GetValues <T>(this ExcelReference range)
{
if (range.CellsCount() == 1)
{
return(new T[] { range.GetValue().ConvertTo <T>() });
}
return(((object[, ])range.GetValue()).AsIEnumerable <T>());
}
public static List <object> GetValuesFromCellRange(ExcelReference excelReference)
{
if (IsSingleCell(excelReference))
{
var returnValue = new List <object>();
returnValue.Add(excelReference.GetValue());
return(returnValue);
}
return(GetValuesFromObjectArray((object[, ])excelReference.GetValue()));
}
public static string GetValueFromSingleCell(ExcelReference excelReference)
{
// This is a very basic way of detecting date formatted cell. Checking for the existence of a y as most date formats will contain at least one 'y' for the year.
if (XlCall.Excel(XlCall.xlfGetCell, 7, excelReference).ToString().Contains("y"))
{
return(GetDateValueFromDouble((double)excelReference.GetValue()).ToString(Utilities.DateFormat));
}
else
{
return(excelReference.GetValue().ToString());
}
}
public object GetCell(int row, int col)
{
// TODO: optimise to not create an ExcelReference on every visit.
ExcelReference xlref = new ExcelReference(row, row, col, col, "s2cfg");
return(xlref.GetValue( ));
}
/// <summary>Creates a specific <see cref="IExcelDataQuery"/> object.
/// </summary>
/// <param name="excelRange">The Excel Range.</param>
/// <param name="excelRangeName">The name of the Excel Range.</param>
/// <returns>A <see cref="IExcelDataQuery"/> object which represents the specific user input.</returns>
public static IExcelDataQuery Create(object excelRange, string excelRangeName)
{
if (IsEmpty(excelRange) == true)
{
return(new ExcelNullQuery(excelRangeName));
}
else if (excelRange is ExcelReference)
{
ExcelReference excelReference = excelRange as ExcelReference;
object[,] valueArray = excelReference.GetValue() as object[, ];
if (valueArray != null)
{
if (valueArray.GetLength(0) == 1) // just one row
{
return(new ExcelTableQueryRowVector(excelReference, excelRangeName));
}
return(new ExcelTableQueryRegular(excelReference, excelRangeName));
}
return(new ExcelCellQuery(excelReference, excelRangeName));
}
else if (excelRange is IExcelDataQuery)
{
return((IExcelDataQuery)excelRange);
}
else if (excelRange is object[, ])
{
return(new ExcelTableQueryPlain(excelRange as object[, ], excelRangeName));
}
return(new ExcelCellQueryPlain(excelRange, excelRangeName));
}
/// <summary>Initializes a new instance of the <see cref="ExcelPropertyQueryColumnWise"/> class.
/// </summary>
/// <param name="propertyNames">The Excel Range (exactly one column) which contains a list of property names.</param>
/// <param name="propertyValues">The Excel range (exactly one column) which contains the values of the properties.</param>
/// <param name="excelDataQueryName">The name of the Excel data query.</param>
internal ExcelPropertyQueryColumnWise(ExcelReference propertyNames, ExcelReference propertyValues, string excelDataQueryName)
{
if (propertyNames == null)
{
throw new ArgumentNullException("propertyNames");
}
m_PropertyNames = propertyNames;
if (propertyValues == null)
{
throw new ArgumentNullException("propertyValues");
}
m_PropertyValues = propertyValues;
m_PropertyNameArray = (object[, ])propertyNames.GetValue();
m_PropertyValueArray = (object[, ])propertyValues.GetValue();
m_RowCount = m_PropertyNameArray.GetLength(1);
m_ColumnCount = m_PropertyValueArray.GetLength(0) + 1;
if (excelDataQueryName == null)
{
throw new ArgumentNullException("excelDataQueryName");
}
m_Name = new IdentifierString(excelDataQueryName);
m_GuidedExcelDataQuery = new GuidedExcelDataQuery(excelDataQueryName, m_ColumnCount, m_RowCount);
}
public static object[,] PercentageOfTotal([ExcelArgument(AllowReference = true)] object range)
{
ExcelReference theRef = (ExcelReference)range;
int rows = theRef.RowLast - theRef.RowFirst + 1;
ExcelReference callerRef = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
//create arrays for values, bins and output
object[,] res = new object[rows, 1];
double[] vals = new double[rows];
//transfer all values to value array
for (int i = 0; i < rows; i++)
{
ExcelReference cellRef = new ExcelReference(theRef.RowFirst + i, theRef.RowFirst + i, theRef.ColumnFirst, theRef.ColumnFirst, theRef.SheetId);
vals[i] = (double)cellRef.GetValue();
}
//get sum
double sum = vals.Sum();
//create output array
for (int i = 0; i < rows; i++)
{
res[i, 0] = vals[i] / sum;
}
return(res);
}
public static List <T> ToList <T>(this ExcelReference range, Func <T> factory = null) where T : class
{
var items = new List <T>();
XLObjectMapper.AddRange(items, range.GetValue(), factory);
return(items);
}
/// <summary>Initializes a new instance of the <see cref="ExcelTableQueryRegular"/> class.
/// </summary>
/// <param name="excelReference">The Excel Range.</param>
/// <param name="excelDataQueryName">The name of the Excel data query.</param>
/// <exception cref="ArgumentException">Thrown, if the value of <paramref name="excelReference"/> is not
/// represented by an two-dimensional array.</exception>
public ExcelTableQueryRegular(ExcelReference excelReference, string excelDataQueryName)
{
if (excelReference == null)
{
throw new ArgumentNullException("excelRangeValue");
}
m_Range = excelReference;
object value = m_Range.GetValue();
if (value is object[, ])
{
m_Data = (object[, ])value;
m_RowCount = m_Data.GetLength(0);
m_ColumnCount = m_Data.GetLength(1);
}
else
{
throw new ArgumentException("excelRangeValue");
}
if (excelDataQueryName == null)
{
throw new ArgumentNullException("excelDataQueryName");
}
m_Name = new IdentifierString(excelDataQueryName);
m_GuidedExcelDataQuery = new GuidedExcelDataQuery(excelDataQueryName, rowCount: m_RowCount, columnCount: m_ColumnCount);
}
/// <summary>Gets the value of a specific Excel cell.
/// </summary>
/// <typeparam name="T">The type of the output.</typeparam>
/// <param name="inputCell">The input Excel cell.</param>
/// <param name="value">The value of the Excel cell (output).</param>
/// <param name="valueDropDownListAsString">The optional semicolon separated <see cref="System.String"/> representation of the
/// possible outcomes with respect to <paramref name="value"/>, i.e. if not <c>null</c> a drop down list will be added to the corresponding Excel cell.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
private ExcelCellValueState TryGetValue <T>(object inputCell, out T value, string valueDropDownListAsString)
{
object excelCellValue;
if (inputCell == null)
{
value = default(T);
return(ExcelCellValueState.NoValidValue);
}
else if (inputCell is ExcelReference)
{
ExcelReference excelRange = (ExcelReference)inputCell;
excelRange.CreateDropdownList(0, 0, valueDropDownListAsString);
excelCellValue = excelRange.GetValue();
}
else
{
excelCellValue = inputCell;
}
if (ExcelDataConverter.IsEmptyCell(excelCellValue) == true)
{
value = default(T);
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
return((ExcelDataConverter.TryGetCellValue <T>(excelCellValue, out value) == true) ? ExcelCellValueState.ProperValue : ExcelCellValueState.NoValidValue);
}
/// <summary>Gets the value of a specific Excel cell.
/// </summary>
/// <typeparam name="TEnum">The type of the output which is assumed to be a enumeration.</typeparam>
/// <param name="inputCell">The input Excel cell.</param>
/// <param name="value">The value of the Excel cell (output).</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a enumeration.</exception>
private ExcelCellValueState TryGetValue <TEnum>(object inputCell, out TEnum value, EnumStringRepresentationUsage enumStringRepresentationUsage)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
object excelCellValue;
if (inputCell == null)
{
value = default(TEnum);
return(ExcelCellValueState.NoValidValue);
}
else if (inputCell is ExcelReference)
{
ExcelReference excelRange = (ExcelReference)inputCell;
string valueDropDownListAsString = EnumString <TEnum> .GetValues(enumStringRepresentationUsage).AsExcelDropDownListString();
excelRange.CreateDropdownList(0, 0, valueDropDownListAsString);
excelCellValue = excelRange.GetValue();
}
else
{
excelCellValue = inputCell;
}
if (ExcelDataConverter.IsEmptyCell(excelCellValue) == true)
{
value = default(TEnum);
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
return((ExcelDataConverter.TryGetCellValue <TEnum>(excelCellValue, enumStringRepresentationUsage, out value) == true) ? ExcelCellValueState.ProperValue : ExcelCellValueState.NoValidValue);
}
public static String castParamString(
Object param, String paramName, bool isMandatory, String defaultVal = null)
{
String param_casted;
if (param is string)
{
param_casted = Convert.ToString(param);
}
else if (param is double)
{
param_casted = Convert.ToString(param);
}
else if (param is Boolean)
{
param_casted = Convert.ToString(param);
}
else if (param is ExcelReference)
{
ExcelReference reference = (ExcelReference)param;
List <ExcelReference> list = reference.InnerReferences;
if (reference.GetValue() is ExcelError && list != null && list.ToArray().Length > 0)
{
param_casted = castParamString(list[0], paramName, isMandatory, defaultVal);
}
else
{
param_casted = castParamString(reference.GetValue(), paramName, isMandatory, defaultVal);
}
}
else if (!isMandatory && (param == null || param is ExcelEmpty || param is ExcelMissing))
{
param_casted = defaultVal;
}
else if (isMandatory && (param == null || param is ExcelEmpty || param is ExcelMissing))
{
throw new ArgumentException("Mandatory Parameter missing: '" + paramName + "'.", paramName);
}
else
{
throw new ArgumentException("Invalid Parameter value '" + param.ToString() + "'.", paramName);
}
return(param_casted);
}
public static void SquareRange()
{
object[,] result;
// Get a reference to the current selection
ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
// Get the value of the selection
object selectionContent = selection.GetValue();
if (selectionContent is object[, ])
{
object[,] values = (object[, ])selectionContent;
int rows = values.GetLength(0);
int cols = values.GetLength(1);
result = new object[rows, cols];
// Process the values
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < cols; j++)
{
if (values[i, j] is double)
{
double val = (double)values[i, j];
result[i, j] = val * val;
}
else
{
result[i, j] = values[i, j];
}
}
}
}
else if (selectionContent is double)
{
double value = (double)selectionContent;
result = new object[, ] {
{ value *value }
};
}
else
{
result = new object[, ] {
{ "Selection was not a range or a number, but " + selectionContent.ToString() }
};
}
// Now create the target reference that will refer to Sheet 2, getting a reference that contains the SheetId first
ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "Sheet2"); // Throws exception if no Sheet2 exists
// ... then creating the reference with the right size as new ExcelReference(RowFirst, RowLast, ColFirst, ColLast, SheetId)
int resultRows = result.GetLength(0);
int resultCols = result.GetLength(1);
ExcelReference target = new ExcelReference(0, resultRows - 1, 0, resultCols - 1, sheet2.SheetId);
// Finally setting the result into the target range.
target.SetValue(result);
}
public static object[,] SubTotals([ExcelArgument(AllowReference = true)] object rangeKeys, [ExcelArgument(AllowReference = true)] object rangeVals, bool atTop = false)
{
ExcelReference theRef = (ExcelReference)rangeKeys;
ExcelReference valsRef = (ExcelReference)rangeVals;
int rows = theRef.RowLast - theRef.RowFirst + 1;
ExcelReference callerRef = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
//create arrays for values, bins and output
object[,] res = new object[rows, 1];
string[] keys = new string[rows];
double[] vals = new double[rows];
//transfer all values to value array
for (int i = 0; i < rows; i++)
{
ExcelReference cellRef = new ExcelReference(theRef.RowFirst + i, theRef.RowFirst + i, theRef.ColumnFirst, theRef.ColumnFirst, theRef.SheetId);
keys[i] = (string)cellRef.GetValue();
cellRef = new ExcelReference(valsRef.RowFirst + i, valsRef.RowFirst + i, valsRef.ColumnFirst, valsRef.ColumnFirst, valsRef.SheetId);
vals[i] = (double)cellRef.GetValue();
}
int top = 0;
int bottom = rows - 1;
int dir = 1;
double sum = 0;
if (atTop)
{
top = bottom;
bottom = 0;
dir = -1;
}
//create output array
for (int i = top; dir *i < dir *bottom; i += dir)
{
if (keys[i] == keys[i + dir])
{
res[i, 0] = "";
sum += vals[i];
}
else
{
res[i, 0] = sum + vals[i];
sum = 0;
}
}
res[bottom, 0] = sum + vals[bottom];
return(res);
}
/// <summary>
/// 单元格是否为空
/// </summary>
/// <param name="range"></param>
/// <returns></returns>
public static bool IsEmpty(this ExcelReference range)
{
if (range == null)
{
return(true);
}
object value = range.GetValue();
if (value is ExcelEmpty || value is ExcelError || value is ExcelMissing)
{
return(true);
}
return(string.IsNullOrEmpty(value.ToString()));
}
public static T GetValue <T>(string nameRef)
{
object result = XlCall.Excel(XlCall.xlfEvaluate, "=" + nameRef);
ExcelReference r = result as ExcelReference;
if (r != null)
{
return(r.GetValue().ConvertTo <T>());
}
else
{
return(result.ConvertTo <T>());
}
}
/// <summary>Initializes a new instance of the <see cref="ExcelCellQuery"/> class.
/// </summary>
/// <param name="excelReference">The Excel Range.</param>
/// <param name="excelDataQueryName">The name of the Excel data query.</param>
public ExcelCellQuery(ExcelReference excelReference, string excelDataQueryName)
{
if (excelReference == null)
{
throw new ArgumentNullException("excelRangeValue");
}
m_Range = excelReference;
m_ExcelCellValue = m_Range.GetValue();
if (excelDataQueryName == null)
{
throw new ArgumentNullException("excelDataQueryName");
}
m_Name = new IdentifierString(excelDataQueryName);
m_GuidedExcelDataQuery = new GuidedExcelDataQuery(excelDataQueryName, rowCount: 1, columnCount: 1);
}
internal static object[,] ReadFromRange()
{
object[,] result = null;
ExcelReference selection = new ExcelReference(0, 4, 0, 2);
object selectContent = selection.GetValue();
if (selectContent is object[, ])
{
result = selectContent as object[, ];
}
else if (selectContent is double)
{
result = new object[, ] {
{ selectContent }
};
}
return(result);
}
public static void ShowIntrospectionWindow()
{
ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfActiveCell);
object value = cell.GetValue();
if (value != null)
{
string handle = value.ToString();
object acq_object;
if (ACQ.Excel.Handles.GlobalCache.TryGetObject(handle, out acq_object))
{
ACQ.Excel.Introspection.IntrospectionDlg dlg = new ACQ.Excel.Introspection.IntrospectionDlg(acq_object);
dlg.Show();
}
}
}
/// <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);
}
public override object GetValue()
{
return(_excelReference.GetValue());
}
private static void parseParameters(Object param, string suffix, string value, ref List <string> parameters)
{
if (param is Object[])
{
Object[] param_casted = (Object[])param;
int param_d1 = param_casted.Length;
for (int i = 0; i < param_d1; i++)
{
if (param_casted[i] == null || param_casted[i] is ExcelEmpty || param_casted[i] is ExcelMissing)
{
continue;
}
parseParameters(param_casted[i], suffix, value, ref parameters);
}
}
else if (param is Object[, ])
{
Object[,] param_casted = (Object[, ])param;
int param_d1 = param_casted.GetLength(0);
int param_d2 = param_casted.GetLength(1);
for (int i = 0; i < param_d1; i++)
{
for (int j = 0; j < param_d2; j++)
{
if (param_casted[i, j] == null || param_casted[i, j] is ExcelEmpty || param_casted[i, j] is ExcelMissing)
{
continue;
}
parseParameters(param_casted[i, j], suffix, value, ref parameters);
}
}
}
else if (param is ExcelReference)
{
ExcelReference reference = (ExcelReference)param;
List <ExcelReference> list = reference.InnerReferences;
if (reference.GetValue() is ExcelError && list != null && list.ToArray().Length > 0)
{
foreach (ExcelReference refer in list)
{
Object val = refer.GetValue();
parseParameters(val, suffix, value, ref parameters);
}
}
else
{
parseParameters(reference.GetValue(), suffix, value, ref parameters);
}
}
else if (param is string)
{
string param_Key = Convert.ToString(param);
string errormsg = "Invalid Parameter value '" + param_Key + "'. Accepted format: 'prefix:Dimension'.";
Regex regex = new Regex("^[^:]+:[^:]+" + suffix + "$");
param_Key = param_Key + suffix;
if (!regex.IsMatch(param_Key))
{
throw new ArgumentException(errormsg, param_Key);
}
parameters.Add(param_Key + "=" + value);
}
else if (param == null || param is ExcelEmpty || param is ExcelMissing)
{
; // skip
}
else
{
throw new ArgumentException("Invalid Parameter value '" + Convert.ToString(param) + ".", Convert.ToString(param));
}
}
public static T GetValue <T>(this ExcelReference range)
{
return(range.GetValue().ConvertTo <T>());
}
public void parse(Object parameters)
{
if (parameters is Object[])
{
//Utils.log("Object[]");
Object[] param_casted = (Object[])parameters;
int param_d1 = param_casted.Length;
for (int i = 0; i < param_d1; i++)
{
if (param_casted[i] == null || param_casted[i] is ExcelEmpty || param_casted[i] is ExcelMissing)
{
continue;
}
parse(param_casted[i]);
}
}
else if (parameters is Object[, ])
{
//Utils.log("Object[,]");
Object[,] param_casted = (Object[, ])parameters;
int param_d1 = param_casted.GetLength(0);
int param_d2 = param_casted.GetLength(1);
for (int i = 0; i < param_d1; i++)
{
for (int j = 0; j < param_d2; j++)
{
if (param_casted[i, j] == null || param_casted[i, j] is ExcelEmpty || param_casted[i, j] is ExcelMissing)
{
continue;
}
parse(param_casted[i, j]);
}
}
}
else if (parameters is ExcelReference)
{
//Utils.log("ExcelReference");
ExcelReference reference = (ExcelReference)parameters;
List <ExcelReference> list = reference.InnerReferences;
if (reference.GetValue() is ExcelError && list != null && list.ToArray().Length > 0)
{
foreach (ExcelReference refer in list)
{
Object val = refer.GetValue();
parse(val);
}
}
else
{
parse(reference.GetValue());
}
}
else if (parameters is string)
{
string paramStr = Convert.ToString(parameters);
string[] paramTokenz = paramStr.Split('&');
if (paramTokenz.Length > 1)
{
foreach (string param in paramTokenz)
{
parse(param);
}
}
else
{
string[] tokenz = paramStr.Split('=');
string errormsg = "Invalid Parameter '" + paramStr + "'. Accepted format: 'parameter=value'.";
if (tokenz.Length != 2)
{
throw new ArgumentException(errormsg, "parameters");
}
string param_Key = Convert.ToString(tokenz[0]);
string param_Value = Convert.ToString(tokenz[1]);
Parameter param = getParameter(param_Key);
if (param != null)
{
param.addValue(param_Value);
}
else
{
param = new Parameter(param_Key, param_Value);
addParameter(param_Key, param);
}
}
}
else if (parameters == null || parameters is ExcelEmpty || parameters is ExcelMissing)
{
; // skip
}
else
{
throw new ArgumentException("Invalid Parameter value '" + Convert.ToString(parameters) + "' in 'parameters'.", "parameters");
}
}
public object GetCell(int row, int col)
{
ExcelReference xlref = new ExcelReference(row, row, col, col, "s2cfg");
return(xlref.GetValue( ));
}
private static void readFromObject(Object param, String paramName, String suffix, Dictionary <string, string> dict)
{
if (param is Object[])
{
//Utils.log("Object[]");
Object[] param_casted = (Object[])param;
int param_d1 = param_casted.Length;
for (int i = 0; i < param_d1; i++)
{
if (param_casted[i] == null || param_casted[i] is ExcelEmpty || param_casted[i] is ExcelMissing)
{
continue;
}
readFromObject(param_casted[i], paramName, suffix, dict);
}
}
else if (param is Object[, ])
{
//Utils.log("Object[,]");
Object[,] param_casted = (Object[, ])param;
int param_d1 = param_casted.GetLength(0);
int param_d2 = param_casted.GetLength(1);
for (int i = 0; i < param_d1; i++)
{
for (int j = 0; j < param_d2; j++)
{
if (param_casted[i, j] == null || param_casted[i, j] is ExcelEmpty || param_casted[i, j] is ExcelMissing)
{
continue;
}
readFromObject(param_casted[i, j], paramName, suffix, dict);
}
}
}
else if (param is ExcelReference)
{
//Utils.log("ExcelReference");
ExcelReference reference = (ExcelReference)param;
List <ExcelReference> list = reference.InnerReferences;
if (reference.GetValue() is ExcelError && list != null && list.ToArray().Length > 0)
{
foreach (ExcelReference refer in list)
{
Object val = refer.GetValue();
readFromObject(val, paramName, suffix, dict);
}
}
else
{
readFromObject(reference.GetValue(), paramName, suffix, dict);
}
}
else if (param is string)
{
string param_Val = Convert.ToString(param);
//Utils.log("val: " + param_Val);
string[] tokenz = param_Val.Split('=');
string errormsg = "Invalid Parameter value '" + param_Val + "' for parameter '" + paramName + "'. Accepted format: 'prefix:Dimension=value'.";
if (tokenz.Length != 2)
{
throw new ArgumentException(errormsg, paramName);
}
Regex regex = new Regex("^[^:]+:[^:]+" + suffix + "$");
string param_Key = tokenz[0] + suffix;
if (!regex.IsMatch(param_Key)) // the user might have used the suffix already
{
param_Key = tokenz[0] + "";
}
if (!regex.IsMatch(param_Key))
{
throw new ArgumentException(errormsg, paramName);
}
string param_Value = Convert.ToString(tokenz[1]);
//Utils.log(param_Key + "=" + param_Value);
dict.Add(param_Key, param_Value);
}
else if (param == null || param is ExcelEmpty || param is ExcelMissing)
{
; // skip
}
else
{
throw new ArgumentException("Invalid Parameter value '" + Convert.ToString(param) + "' for '" + paramName + "'.", paramName);
}
}
public static T[] ToVector <T>(this ExcelReference range)
{
return(range.GetValue().ToVector <T>());
}
public static object ConvertTo(this object vt, Type toType)
{
if (vt == null)
{
return(GetDefault(toType));
}
Type fromType = vt.GetType();
if (fromType == typeof(DBNull))
{
return(GetDefault(toType));
}
if (fromType == typeof(ExcelDna.Integration.ExcelEmpty) || fromType == typeof(ExcelDna.Integration.ExcelError) || fromType == typeof(ExcelDna.Integration.ExcelMissing))
{
return(GetDefault(toType));
}
if (fromType == typeof(ExcelReference))
{
ExcelReference r = (ExcelReference)vt;
object val = r.GetValue();
return(ConvertTo(val, toType));
}
//acount for nullable types
toType = Nullable.GetUnderlyingType(toType) ?? toType;
if (toType == typeof(DateTime))
{
DateTime dt = DateTime.FromOADate(0.0);
if (fromType == typeof(DateTime))
{
dt = (DateTime)vt;
}
else if (fromType == typeof(double))
{
dt = DateTime.FromOADate((double)vt);
}
else if (fromType == typeof(string))
{
DateTime result;
if (DateTime.TryParse((string)vt, out result))
{
dt = result;
}
}
return(Convert.ChangeType(dt, toType));
}
else if (toType == typeof(XLDate))
{
XLDate dt = 0.0;
if (fromType == typeof(DateTime))
{
dt = (DateTime)vt;
}
else if (fromType == typeof(double))
{
dt = (double)vt;
}
else if (fromType == typeof(string))
{
DateTime result;
if (DateTime.TryParse((string)vt, out result))
{
dt = result;
}
else
{
dt = 0.0;
}
}
else
{
dt = (double)Convert.ChangeType(vt, typeof(double));
}
return(Convert.ChangeType(dt, toType));
}
else if (toType == typeof(double))
{
double dt = 0.0;
if (fromType == typeof(double))
{
dt = (double)vt;
}
else if (fromType == typeof(DateTime))
{
dt = ((DateTime)vt).ToOADate();
}
else if (fromType == typeof(string))
{
double.TryParse((string)vt, out dt);
}
else
{
dt = (double)Convert.ChangeType(vt, typeof(double));
}
return(Convert.ChangeType(dt, toType));
}
else if (toType.IsEnum)
{
try
{
return(Enum.Parse(toType, vt.ToString(), true));
}
catch (Exception)
{
return(GetDefault(toType));
}
}
else
{
return(Convert.ChangeType(vt, toType));
}
}
public static T[,] ToMatrix <T>(this ExcelReference range)
{
return(range.GetValue().ToMatrix <T>());
}