Place .net control on the sheet

Question

I am using spreadsheetgear, and I want to place the combobox (ComponentOne) into 1 cell. I want that when the user go to this cell, this combobox will activate and show the list to user. After user chose item on the list, it will place this item into the cell value and hide the combobox. How to do it in Spreadsheetgear.

Thanks,

Doit

On SO, you are expected to try to **write the code yourself**. After **[doing more research](//meta.stackoverflow.com/questions/261592)** if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a **[Minimal, Complete, and Verifiable example](//stackoverflow.com/help/mcve)**. — Rob, Nov 13 '17 at 14:55

score 1 · Answer 1 · answered Nov 13 '17 at 14:53

I am not familiar with ComponentOne controls, so cannot really speak for that portion of your question. However, regarding a more general approach to embedding custom controls onto a SpreadsheetGear WorkbookView UI control, this is possible by sub-classing the UIManager class, which would allow you to intercept the creation of existing shapes on a worksheet and replace them with your own custom controls.

Below is a simple example that demonstrates this with the Windows Forms WorkbookView control and a sub-class of SpreadsheetGear.Windows.Forms.UIManager. This example just replaces a rectangle AutoShape with a button. You could modify it to show a ComponentOne CheckBox instead.

Note that the UIManager.CreateCustomControl(...) method gets called anytime a shape is scrolled into view / made visible on the WorkbookView. Also note that that your custom control will be disposed of every time it is scrolled out of view or otherwise made invisible. Please see the documentation for more details on this API.

Another important point about shapes and worksheets in general--shapes are not embedded inside a cell. Instead they hover over cells. So there will be no explicit "link" between a given shape and a given cell. The closest you could come to making such an association is with the IShape.TopLeftCell or BottomRightCell properties, which will provide the ranges for which this shape's respective edges reside over. The IShape interface contains a number of other API that you might find useful in your use-case. For instance, you can hide a shape by setting the IShape.Visible property to false.

using System;
using System.Windows.Forms;
using SpreadsheetGear;
using SpreadsheetGear.Shapes;

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();

        // Create the UIManager replacement.
        new MyUIManager(workbookView1.ActiveWorkbookSet);
    }

    private void buttonRunSample_Click(object sender, EventArgs e)
    {
        // NOTE: Must acquire a workbook set lock.
        workbookView1.GetLock();
        try
        {
            // Get a reference to the active worksheet and window information.
            IWorksheetWindowInfo windowInfo = workbookView1.ActiveWorksheetWindowInfo;
            IWorksheet worksheet = workbookView1.ActiveWorksheet;

            // Get a reference to a cell.
            IRange cell = workbookView1.ActiveWorksheet.Cells["B2"];

            // Add a placeholder shape to the worksheet's shape collection.
            // This shape will be replaced with a custom control.
            double left = windowInfo.ColumnToPoints(cell.Column) + 5;
            double top = windowInfo.RowToPoints(cell.Row) + 5;
            double width = 100;
            double height = 30;
            IShape shape = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, left, top, width, height);

            // Set the name of the shape for identification purposes.
            shape.Name = "MyCustomControl";
        }
        finally
        {
            // NOTE: Must release the workbook set lock.
            workbookView1.ReleaseLock();
        }
        buttonRunSample.Enabled = false;
    }

    // UIManager replacement class.
    private class MyUIManager : SpreadsheetGear.Windows.Forms.UIManager
    {
        private Button _customControl;

        public MyUIManager(IWorkbookSet workbookSet)
            : base(workbookSet)
        {
            _customControl = null;
        }

        // Override to substitute a custom control for any existing shape in the worksheet.  
        // This method is called when a control is first displayed within the WorkbookView.
        public override System.Windows.Forms.Control CreateCustomControl(IShape shape)
        {
            // If the shape name matches...
            if (String.Equals(shape.Name, "MyCustomControl"))
            {
                // Verify that a control does not already exist.
                System.Diagnostics.Debug.Assert(_customControl == null);

                // Create a custom control and set various properties.
                _customControl = new Button();
                _customControl.Text = "My Custom Button";

                // Add a Click event handler.
                _customControl.Click += new EventHandler(CustomControl_Click);

                // Add an event handler so that we know when the control 
                // has been disposed.  The control will be disposed when
                // it is no longer in the viewable area of the WorkbookView.
                _customControl.Disposed += new EventHandler(CustomControl_Disposed);

                return _customControl;
            }
            return base.CreateCustomControl(shape);
        }

        private void CustomControl_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Custom Control was Clicked!");
        }

        private void CustomControl_Disposed(object sender, EventArgs e)
        {
            // Add any cleanup code here...

            // Set the custom control reference to null.
            _customControl = null;
        }
    }
}

Place .net control on the sheet

1 Answers1