# How do I...

#### Range

See below links to various samples that demonstrate working with DataTables and DataSets and Excel workbooks.
// Create a workbook from an Excel file
IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("myfile.xls");

// Get a DataSet from an existing defined name
DataSet dataSet = workbook.GetDataSet("mydefinedname",

Visit the below sample for a demonstration on consolidating ranges with the IRange.Copy(…) method.
###### Razor Page Samples
Visit the below sample for a demonstration of setting up outline levels, grouping ,etc.
###### API Samples
// Create a workbook and get the first worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

// Add a hyperlink to cell A1.
null, "My Screen Tip", "My Hyperlink Text");

Note: Hyperlinks can be linked to web pages, email addresses, workbook files and references to ranges in the current workbook. Hyperlinks can also be created by using the HYPERLINK function.
###### API Samples
// Create a workbook.

// Get the first worksheet and name it
SpreadsheetGear.IWorksheet mySheet = workbook.Worksheets[0];
mySheet.Name = "MySheet";

// Create a defined name which references A1.
SpreadsheetGear.INames names = workbook.Names;
names.Add("MyName", "=MySheet!$A$1");

// Get the IRange for the defined name.
SpreadsheetGear.IRange cell = names["MyName"].RefersToRange;

###### API Samples
// Create a workbook and get the first worksheet
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

// Merge a range of cells
worksheet.Cells["A1:D2"].Merge();

###### API Samples
SpreadsheetGear does not have a direct way to accept an HTML hexadecimal color. However, .NET itself provides a System.Drawing.ColorTranslator.FromHtml(...) method that can convert such a color to System.Drawing.Color, and which can then be converted to a SpreadsheetGear Color. Example:
    /// <summary>
/// Converts an HTML hex-based color string to a SpreadsheetGear color.
/// NOTE: for an actual implementation you may want to check for exceptions due to invalid colors
/// passed into ColorTranslator.FromHtml(...).
/// </summary>
public SpreadsheetGear.Color ConvertColorHexToSG(string hexColor)
{
// Convert hex color string to .NET System.Drawing.Color.
System.Drawing.Color systemColor = System.Drawing.ColorTranslator.FromHtml(hexColor);

// Now convert the System color to a SpreadsheetGear color.

return sgColor;
}

...

worksheet.Cells["A1"].Interior.Color = ConvertColorHexToSG("#ff0000");


###### API Samples
// Create a workbook and get the first worksheet
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

// Set the bottom border of a range of cells
SpreadsheetGear.IRange range = worksheet.Cells["A1:D1"];

###### API Samples
// Create a workbook and get the first worksheet
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

// Create a 10 row by 2 column array of values
object[,] values = new object[10, 2];
for (int i = 0; i < 10; i++)
{
values[i, 0] = "Row=" + i + " Col=0";
values[i, 1] = "Row=" + i + " Col=1";
}

// Set the values in the worksheet
// Notice the range "A1:B10" has to match the size of the array
worksheet.Cells["A1:B10"].Value = values;

// Get the values from the worksheet
object[,] retVals = (object[,])worksheet.Cells["A1:B10"].Value;

###### API Samples

Under normal circumstances, SpreadsheetGear will attempt to parse cell input into the appropriate data type. For instance, "001234" will be treated as a number and so the leading zeros will be stripped. You can force input into a cell a couple of ways:

1. Set the destination cell's IRange.NumberFormat to "@" prior to setting its value. Example:

worksheet.Cells["A1"].NumberFormat = "@";
worksheet.Cells["A1"].Value = "00123";


2. Prefix the cell's value with a single quote character. Example:

worksheet.Cells["A1"].Value = "'" + "00123";


Column Widths are set using a relative unit of measurement called "Character Units", where 1 Character Unit is roughly the width of a "0" character when using the default font of the workbook (this is typically 11pt Calibri, but can be changed).

The below extension method can be used to set Column Widths in Points, which is an absolute unit of measurement. It uses the Slope and Slope Intercept formulas to convert the specified Point-based value to Character Units. Note that due to extra padding, the accuracy will diminish when setting very small column widths and so has a minimum of 1 Character Unit. Column widths also have a maximum with of 255 Character Units, so will be capped when this value is reached.

public static class IRangeExtensionMethods
{
// Slope Formula: (y2 - y1) / (x2 - x1)
//   x values == Character Units
//   y values == Points
// Slope Intercept formula: y = mx + b
//   x == x coordinate
//   y == y coordinate
//   m == slope
//   b == y intercept
public static void SetColumnWidthInPoints(this IRange cell, double widthInPoints)
{
// Setup x and y variables to determine the slope of a line based off two initial
// Points-based x values.
double x1 = 10, x2 = 1;
double y1, y2;

// Determine y1, y2 (Points) from x1, x2 (Character Units) inputs
cell.ColumnWidth = x1;
y1 = cell.Width;
cell.ColumnWidth = x2;
y2 = cell.Width;

// Calculate the slope of the line (m).
double m = (y2 - y1) / (x2 - x1);

// Calculate the intersect (b):
//   (y = mx + b) => (b = y - mx)
double b = y1 - m * x1;

// Now that we have m and b, we can calculate any other "x" (Character Units) from
// any new "y" (Points) input.  So use "widthInPoints" param as our new y:
//   (y = mx + b) => (x = (y - b) / m)
double y3 = widthInPoints;
double x3 = (y3 - b) / m;

// Set boundaries on the min / max ColumnWidth
x3 = Math.Min(255.0, Math.Max(1.0, x3));

// Set cell width to calculated Character Units from Points-based input.
cell.ColumnWidth = x3;

// Verify expected values if desired.
//Console.WriteLine($"Inputted Points: {widthInPoints}, Actual Points: {cell.Width}"); } }  The IWorksheet.UsedRange property will return the used area of a worksheet. "Used" includes cells that do not have values but are formatted in some way. For instance, if you have data only in cells A1:D10 but the empty cell Z100 has a custom NumberFormat, the worksheet's UsedRange will be A1:Z100 instead of A1:D1. The below extension method for IWorksheet demonstrates how you can only include cells with values when determining the UsedRange: public static class IWorksheetExtensionMethods { public static IRange GetUsedRange(this IWorksheet worksheet, bool ignoreEmptyCells) { IRange usedRange = worksheet.UsedRange; if (!ignoreEmptyCells) return usedRange; // Find last row in used range with a cell containing data. IRange foundCell = usedRange.Find("*", usedRange[0, 0], FindLookIn.Formulas, LookAt.Part, SearchOrder.ByRows, SearchDirection.Previous, false); int lastRow = foundCell?.Row ?? 0; // Find last column in used range with a cell containing data. foundCell = usedRange.Find("*", usedRange[0, 0], FindLookIn.Formulas, LookAt.Part, SearchOrder.ByColumns, SearchDirection.Previous, false); int lastCol = foundCell?.Column ?? 0; // Return a new used range that clips of any empty rows/cols. return worksheet.Cells[worksheet.UsedRange.Row, worksheet.UsedRange.Column, lastRow, lastCol]; } }  Note one edge-case where this approach may not work. In most cases this routine will take into account hidden rows / columns that contain cell data. One exception to this is if AutoFilters is enabled on the worksheet, which puts the worksheet in a special "mode" that excludes hidden rows when using IRange.Find(...). If AutoFilters is enabled (in such cases IWorksheet.AutoFilterMode will be true), and the bottom-most rows on a worksheet are filtered out, this approach will not consider these hidden rows and so report an incorrect UsedRange. The IRange.Find(…) method will only return the immediately next or previous cell that matches your search criteria. Use the below routine to return a list of all cells in a specified IRange of cells: public List FindAll(string searchString, IRange searchRange) { // Collection used to accumulate found cells and some local variables to assist the FindAll routine. List ranges = new List(); IRange firstRange = null; IRange nextRange = null; // Find first instance of searchString, if it exists nextRange = searchRange.Find(searchString, null, FindLookIn.Values, LookAt.Part, SearchOrder.ByRows, SearchDirection.Next, false); if (nextRange != null) { // Keep track of the starting point before we find all other instances firstRange = nextRange; while (true) { // Add range to list of found cells ranges.Add(nextRange); // Try to find another instance of searchString nextRange = searchRange.Find(searchString, nextRange, FindLookIn.Values, LookAt.Part, SearchOrder.ByRows, SearchDirection.Next, false); // Once the new instance loops back to the first instance, out of the loop. if (nextRange.Address == firstRange.Address) break; } } return ranges; }  #### Workbook public static IWorkbook OpenWorkbook(System.IO.Stream stream) { // Read a workbook from a stream and return it. return SpreadsheetGear.Factory.GetWorkbookSet() .Workbooks.OpenFromStream(stream); } public static void SaveWorkbook(System.IO.Stream stream) { // Create a workbook and write it to the stream. IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(); workbook.SaveToStream( stream, SpreadsheetGear.FileFormat.Excel8); }  ###### API Samples // Create a workbook. SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(); // Get the first default worksheet, name it and add a formula. SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Name = "MyFirstSheet"; worksheet.Cells["A1"].Value = 123.456; // Add a new 2nd worksheet, name it and add a formula. worksheet = workbook.Worksheets.Add(); worksheet.Name = "MySecondSheet"; worksheet.Cells["A1"].Formula = "=MyFirstSheet!A1 * 2";  ###### API Samples // Create a workbook and get the first worksheet PageSetup SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(); SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"]; SpreadsheetGear.IPageSetup pageSetup = worksheet.PageSetup; // Set the Print Area pageSetup.PrintArea = "Sheet1!$B$2:$F$20"; // Set the Print Titles pageSetup.PrintTitleRows = "Sheet1!$1:$1"; pageSetup.PrintTitleColumns = "Sheet1!$A:\$A";


#### Formulas and Calculations

See below links to various samples that demonstrate using the SpreadsheetGear calculation engine in a web app.
###### Razor Page Samples
Visit the below sample for a demonstration of creating your own Custom Function in SpreadsheetGear.
###### Razor Page Samples
// Create a workbook and get the first worksheet
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

// Get an IRange and set up random values
SpreadsheetGear.IRange rangeValues = worksheet.Range["A1:A10"];
rangeValues.Formula = "=RAND() * 10000";

// Get an IRange and add a formula to sum the values
SpreadsheetGear.IRange rangeFormula = worksheet.Range["A11"];
rangeFormula.Formula = "=SUM(" + rangeValues.Address + ")";

// Output the calculated value
System.Console.WriteLine("Sum = " + rangeFormula.Text);


Cross-workbook references will automatically resolve themselves once you have loaded all the workbooks into the same instance of an IWorkbookSet object. Example:

// Create a new workbook set
IWorkbookSet wbs = Factory.GetWorkbookSet();

// Open all codependent workbooks in the same workbook set.
IWorkbook workbook1 = wbs.Workbooks.Open(@"C:\path\to\workbook1.xlsx");
IWorkbook workbook2 = wbs.Workbooks.Open(@"C:\path\to\workbook2.xlsx");

// Any cross-workbook references will be found, and formulas and calculated values will be updated accordingly.


#### Graphics and UI

Samples for Windows Forms and WPF are available from the SpreadsheetGearExplorerSamples GitHub repository at: https://github.com/SpreadsheetGear/SpreadsheetGearExplorerSamples. If you are using SpreadsheetGear for .NET Framework, the installer comes with the SpreadsheetGear Explorer, a helpful Windows Forms Application sample similar to the GitHub samples. See SpreadsheetGear Explorer Sample Solutions for instructions on running the SpreadsheetGear Explorer.
For the Windows Forms WorkbookView you can replace set the ContextMenuStrip property to null or another ContextMenuStrip object. Example:
// Disable the context menu

// Replace the context menu.


For WPF, you would use the ContextMenu property. Example:
// Disable the context menu

// Replace the context menu.


The various Explorer dialogs were designed to work in a "modeless" fashion--where a user can jump between selecting worksheets, cells, etc., on a WorkbookView and making changes in the dialog. Using a dialog in this fashion is as simple as instantiating the Explorer and calling the Show() method. Example:

// Launch modeless dialog.
var rangeExplorer = new SpreadsheetGear.Windows.Forms.RangeExplorer(workbookView.ActiveWorkbookSet);
rangeExplorer.Show(this);


"Modal" dialogs, which prevent interacting with the parent window until the dialog is dismissed, is also supported. However, care will need to be taken to ensure any locks on the WorkbookView are first "unwound" with WorkbookView.UnwindLock() prior to launching the dialog. Example:

// Unwind GetLocks that might have previously been called.  Store this count
// in a variable, as we'll need to "rewind" the locks after the dialog has
// been dismissed.
int rewindCount = workbookView.UnwindLock();
try
{
// Launch modal dialog.
var rangeExplorer = new SpreadsheetGear.Windows.Forms.RangeExplorer(workbookView.ActiveWorkbookSet);
rangeExplorer.Show(this);
}
finally
{
// Re-apply the GetLocks that were previously unwound above.
workbookView.RewindLock(rewindCount);
}


#### Charting and Shapes

Visit the below sample for a demonstration of setting the source data range for a chart.
###### API Samples
See below links to samples that demonstrate adding picture shapes to a worksheet.