/// <summary>
/// This demo shows the usage of cell and worksheet selection, auto-sanitizing of worksheet names
/// </summary>
private static void Demo8()
{
Workbook workbook = new Workbook("test8.xlsx", "Sheet*1", true); // Create new workbook with invalid sheet name (*); Auto-Sanitizing will replace * with _
workbook.CurrentWorksheet.AddNextCell("Test"); // Add cell A1
workbook.CurrentWorksheet.SetSelectedCells("A5:B10"); // Set the selection to the range A5:B10
workbook.AddWorksheet("Sheet2"); // Create new worksheet
workbook.CurrentWorksheet.AddNextCell("Test2"); // Add cell A1
var range = new NanoXLSX.Range(new Address(1, 1), new Address(3, 3)); // Create a cell range for the selection B2:D4
workbook.CurrentWorksheet.SetSelectedCells(range); // Set the selection to the range
workbook.AddWorksheet("Sheet2", true); // Create new worksheet with already existing name; The name will be changed to Sheet21 due to auto-sanitizing (appending of 1)
workbook.CurrentWorksheet.AddNextCell("Test3"); // Add cell A1
workbook.CurrentWorksheet.SetSelectedCells(new Address(2, 2), new Address(4, 4)); // Set the selection to the range C3:E5
workbook.SetSelectedWorksheet(1); // Set the second Tab as selected (zero-based: 1)
workbook.Save(); // Save the workbook
}
/// <summary>
/// Returns a cell with a max formula
/// </summary>
/// <param name="range">Cell range to apply the max operation to</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell Max(Range range)
{
return(Max(null, range));
}
/// <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)
{
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;
}
else
{
arg1 = address.ToString();
}
}
if (rangeTarget != null)
{
arg2 = rangeTarget.SheetName + "!" + range;
}
else
{
arg2 = range.ToString();
}
arg3 = columnIndex.ToString("G", culture);
if (exactMatch)
{
arg4 = "TRUE";
}
else
{
arg4 = "FALSE";
}
return(new Cell("VLOOKUP(" + arg1 + "," + arg2 + "," + arg3 + "," + arg4 + ")", Cell.CellType.FORMULA));
}
/// <summary>
/// Returns a cell with a average formula
/// </summary>
/// <param name="target">Target worksheet of the average operation. Can be null if on the same worksheet</param>
/// <param name="range">Cell range to apply the average operation to</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell Average(Worksheet target, Range range)
{
return(GetBasicFormula(target, range, "AVERAGE", null));
}
/// <summary>
/// Returns a cell with a average formula
/// </summary>
/// <param name="range">Cell range to apply the average operation to</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell Average(Range range)
{
return(Average(null, range));
}
/// <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">Query address of a cell as string as source of 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>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell VLookup(Worksheet queryTarget, Address address, Worksheet rangeTarget, Range range, int columnIndex, bool exactMatch)
{
return(GetVLookup(queryTarget, address, 0, rangeTarget, range, columnIndex, exactMatch, false));
}
/// <summary>
/// Function to generate a Vlookup as Excel function
/// </summary>
/// <param name="number">Numeric value for the lookup. Valid types are int, long, float and double</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>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell VLookup(object number, Worksheet rangeTarget, Range range, int columnIndex, bool exactMatch)
{
return(GetVLookup(null, new Address(), number, rangeTarget, range, columnIndex, exactMatch, true));
}
/// <summary>
/// Function to generate a Vlookup as Excel function
/// </summary>
/// <param name="address">Query address of a cell as string as source of the lookup</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>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell VLookup(Address address, Range range, int columnIndex, bool exactMatch)
{
return(VLookup(null, address, null, range, columnIndex, exactMatch));
}
/// <summary>
/// Returns a cell with a sum formula
/// </summary>
/// <param name="target">Target worksheet of the sum operation. Can be null if on the same worksheet</param>
/// <param name="range">Cell range to get a sum of</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell Sum(Worksheet target, Range range)
{
return(GetBasicFormula(target, range, "SUM", null));
}
/// <summary>
/// Function to generate a Vlookup as Excel function
/// </summary>
/// <param name="number">Numeric value for the lookup. Valid types are int, long, float and double</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>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell VLookup(object number, Range range, int columnIndex, bool exactMatch)
{
return(VLookup(number, null, range, columnIndex, exactMatch));
}
/// <summary>
/// Returns a cell with a sum formula
/// </summary>
/// <param name="range">Cell range to get a sum of</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell Sum(Range range)
{
return(Sum(null, range));
}
/// <summary>
/// Returns a cell with a min formula
/// </summary>
/// <param name="target">Target worksheet of the min operation. Can be null if on the same worksheet</param>
/// <param name="range">Cell range to apply the median operation to</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell Min(Worksheet target, Range range)
{
return(GetBasicFormula(target, range, "MIN", null));
}
/// <summary>
/// Returns a cell with a min formula
/// </summary>
/// <param name="range">Cell range to apply the min operation to</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell Min(Range range)
{
return(Min(null, range));
}
/// <summary>
/// Returns a cell with a median formula
/// </summary>
/// <param name="range">Cell range to apply the median operation to</param>
/// <returns>Prepared Cell object, ready to be added to a worksheet</returns>
public static Cell Median(Range range)
{
return(Median(null, range));
}
/// <summary>
/// Gets a list of cell addresses from a cell range (format A1:B3 or AAD556:AAD1000)
/// </summary>
/// <param name="range">Range to process</param>
/// <returns>List of cell addresses</returns>
/// <exception cref="Exceptions.FormatException">Throws a FormatException if a part of the passed range is malformed</exception>
/// <exception cref="RangeException">Throws an RangeException if the range is out of range (A-XFD and 1 to 1048576) </exception>
public static List <Address> GetCellRange(string range)
{
Range range2 = ResolveCellRange(range);
return(GetCellRange(range2.StartAddress, range2.EndAddress));
}