Live SpreadsheetGear API Samples

Demonstrates adding a ScrollBar control to a worksheet, linking its value to a cell, then setting its various options such as the amount to change the value when the up/down arrows are clicked or when the main scroll area is clicked. The linked cell is also linked to a chart series whose line will change in reaction to changes in the ScrollBar value.

// Open workbook and create some local variables.
SpreadsheetGear.IWorkbook workbook = 
    SpreadsheetGear.Factory.GetWorkbook(@"Files/ScrollBarSampleFile.xlsx");
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
SpreadsheetGear.IRange cells = worksheet.Cells;

// We want to position the ScrollBar control over this specific range of cells.
SpreadsheetGear.IRange shapeOverRange = cells["C4:C29"];

// Use the RowToPoints(...) and ColumnToPoints(...) methods to get to top-left
// coordinates (relative to the top-left edge of the worksheet) of the beginning
// of the range (i.e., the top-left edge of cell C4).
double left = windowInfo.ColumnToPoints(shapeOverRange.Column);
double top = windowInfo.RowToPoints(shapeOverRange.Row);

// Dimensions of shape will match the range
double width = shapeOverRange.Width;
double height = shapeOverRange.Height;

// Create the ScrollBar shape with the above-calculated position and dimensions.
SpreadsheetGear.Shapes.IShape scrollBarShape = worksheet.Shapes.AddFormControl(
    SpreadsheetGear.Shapes.FormControlType.ScrollBar, left, top, width, height);

// Get the IShape's IControlFormat object to modify various aspects of the ScrollBar.
SpreadsheetGear.Shapes.IControlFormat scrollBarControl = scrollBarShape.ControlFormat;

// Link the ScrollBar's value to a cell.
scrollBarControl.LinkedCell = "H32";

// Set min / max values ScrollBar can use.
scrollBarControl.Min = 0;
scrollBarControl.Max = 1000;

// Amount applied to value when the up or down button of the ScrollBar is clicked.
scrollBarControl.SmallChange = 10;

// Amount applied when the scroll area of the ScrollBar is clicked.
scrollBarControl.LargeChange = 250;