How do I...

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", 
    SpreadsheetGear.Data.GetDataFlags.FormattedText);

Visit the below sample for a demonstration on consolidating ranges with the IRange.Copy(…) method.

Visit the below sample for a demonstration of setting up outline levels, grouping ,etc.

// Create a workbook and get the first worksheet.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

// Add a hyperlink to cell A1.
worksheet.Hyperlinks.Add(worksheet.Cells["A1"], 
    @"https://www.spreadsheetgear.com", 
    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.

// Create a workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

// 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;

// Create a workbook and get the first worksheet
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

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

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.
        SpreadsheetGear.Color sgColor = SpreadsheetGear.Drawing.Color.GetSpreadsheetGearColor(systemColor);
 
        return sgColor;
    }

    ...

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

// Create a workbook and get the first worksheet
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
 
// Set the bottom border of a range of cells
SpreadsheetGear.IRange range = worksheet.Cells["A1:D1"];
SpreadsheetGear.IBorder border = range.Borders[SpreadsheetGear.BordersIndex.EdgeBottom];
border.LineStyle = SpreadsheetGear.LineStyle.Continous;
border.Color = SpreadsheetGear.Colors.Blue;
border.Weight = SpreadsheetGear.BorderWeight.Thick;

// Create a workbook and get the first worksheet
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
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;

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";
Debug.Assert(worksheet.Cells["A1"].ValueType == SpreadsheetGear.ValueType.Text);

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

worksheet.Cells["A1"].Value = "'" + "00123";
Debug.Assert(worksheet.Cells["A1"].ValueType == SpreadsheetGear.ValueType.Text);

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;
}

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);
}

// 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";

// 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";

See below links to various samples that demonstrate using the SpreadsheetGear calculation engine in a web app.

Visit the below sample for a demonstration of creating your own Custom Function in SpreadsheetGear.

// Create a workbook and get the first worksheet
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
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.

Samples for the SpreadsheetGear for Windows product and 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 WinForms Explorer 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
workbookView1.ContextMenuStrip = null;

// Replace the context menu.
workbookView1.ContextMenuStrip = myContextMenuStrip;

For WPF, you would use the ContextMenu property. Example:

// Disable the context menu
workbookView1.ContextMenu = null;

// Replace the context menu.
workbookView1.ContextMenu = myContextMenu;

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);
}

See below links to various samples that demonstrate adding charts to a worksheet.

Visit the below sample for a demonstration of setting the source data range for a chart.

See below links to samples that demonstrate adding picture shapes to a worksheet.