/// <summary>
/// Function to generate a basic Excel function with one cell range as parameter and an optional post argument
/// </summary>
/// <param name="target">Target worksheet of the cell reference. Can be null if on the same worksheet</param>
/// <param name="range">Main argument as cell range. If applied on one cell, the start and end address are identical</param>
/// <param name="functionName">Internal Excel function name</param>
/// <param name="postArg">Optional argument</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
private static Cell GetBasicFormula(Worksheet target, Range range, string functionName, string postArg)
{
string arg1, arg2, prefix;
if (postArg == null)
{
arg2 = "";
}
else
{
arg2 = "," + postArg;
}
if (target != null)
{
prefix = target.SheetName + "!";
}
else
{
prefix = "";
}
if (range.StartAddress.Equals(range.EndAddress))
{
arg1 = prefix + range.StartAddress.ToString();
}
else
{
arg1 = prefix + range.ToString();
}
return(new Cell(functionName + "(" + arg1 + arg2 + ")", CellType.FORMULA));
}
/// <summary>
/// Function to generate a Vlookup as Excel function
/// </summary>
/// <param name="queryTarget">Target worksheet of the query argument. Can be null if on the same worksheet</param>
/// <param name="address">In case of a reference lookup, query address of a cell as string</param>
/// <param name="number">In case of a numeric lookup, number for the lookup</param>
/// <param name="rangeTarget">Target worksheet of the matrix. Can be null if on the same worksheet</param>
/// <param name="range">Matrix of the lookup</param>
/// <param name="columnIndex">Column index of the target column (1 based)</param>
/// <param name="exactMatch">If true, an exact match is applied to the lookup</param>
/// <param name="numericLookup">If true, the lookup is a numeric lookup, otherwise a reference lookup</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
private static Cell GetVLookup(Worksheet queryTarget, Address address, object number, Worksheet rangeTarget, Range range, int columnIndex, bool exactMatch, bool numericLookup)
{
CultureInfo culture = CultureInfo.InvariantCulture;
string arg1, arg2, arg3, arg4;
if (numericLookup == true)
{
Type t = number.GetType();
if (t == typeof(byte))
{
arg1 = ((byte)number).ToString("G", culture);
}
else if (t == typeof(sbyte))
{
arg1 = ((sbyte)number).ToString("G", culture);
}
else if (t == typeof(decimal))
{
arg1 = ((decimal)number).ToString("G", culture);
}
else if (t == typeof(double))
{
arg1 = ((double)number).ToString("G", culture);
}
else if (t == typeof(float))
{
arg1 = ((float)number).ToString("G", culture);
}
else if (t == typeof(int))
{
arg1 = ((int)number).ToString("G", culture);
}
else if (t == typeof(long))
{
arg1 = ((long)number).ToString("G", culture);
}
else if (t == typeof(ulong))
{
arg1 = ((ulong)number).ToString("G", culture);
}
else if (t == typeof(short))
{
arg1 = ((short)number).ToString("G", culture);
}
else if (t == typeof(ushort))
{
arg1 = ((ushort)number).ToString("G", culture);
}
else
{
throw new FormatException("InvalidLookupType", "The lookup variable can only be a cell address or a numeric value. The value '" + number + "' is invalid.");
}
}
else
{
if (queryTarget != null)
{
arg1 = queryTarget.SheetName + "!" + address.ToString();
}
else
{
arg1 = address.ToString();
}
}
if (rangeTarget != null)
{
arg2 = rangeTarget.SheetName + "!" + range.ToString();
}
else
{
arg2 = range.ToString();
}
arg3 = columnIndex.ToString("G", culture);
if (exactMatch == true)
{
arg4 = "TRUE";
}
else
{
arg4 = "FALSE";
}
return(new Cell("VLOOKUP(" + arg1 + "," + arg2 + "," + arg3 + "," + arg4 + ")", CellType.FORMULA));
}