Spreadsheetgear for WinForms paste from excel removes validation on target cell

Question

I have number only validation on a cell on spreadsheetgear where it gives an error on typing anything other then a number, but I am facing a problem wherein the user would just type anything he likes in excel and paste the cell itself onto the spreadsheetgear sheet causing it to lose the validation.

Now, I went through other threads and found out that you can disable all "Edit" operations on a cell but I want to give user the ability to be able to paste cells from excel.

So, my question is that is there is an event that gets hit on paste cell? I can just use that event to validate the data and generate custom messages. I don't care about the excel error messages as I plan to handle onError event anyway and generate the same custom error messages for other cases too.

Any other approach to accomplish the task would be helpful too. Thanks in advance.

Answer 1

An event does not get triggered when a Paste operation is invoked. At least, not one that would be of any use for your scenario. However, pasting does generate a CommandRange.Paste command. So you could attach your own custom CommandManager to the WorkbookView's workbook set and override the default behavior of the Paste Command to perform whatever special requirements you have.

For instance, you could effectively force an Edit -> Paste Special... -> Values command whenever a Paste Command is invoked, so that formats are not altered, which would potentially remove cell validation. You might look through the PasteType enum for other paste options. Example:

using SpreadsheetGear;
using SpreadsheetGear.Commands;

public class MyCommandManager : CommandManager
{
    internal MyCommandManager(IWorkbookSet workbookSet)
        : base(workbookSet)
    { }

    ...

    // Gets called anytime a Paste command is invoked (i.e., Ctrl+V, context 
    // menu item, WorkbookView.Paste(), etc)
    public override Command CreateCommandPaste(IRange range)
    {
        // Anytime a CommandRange.Paste command is invoked, we'll instead invoke a 
        // CommandRange.PasteSpecial with "Paste Values" (PasteType.Values) so that 
        // values are pasted but not cell formatting, which could remove cell 
        // validation.
        return new CommandRange.PasteSpecial(range, PasteType.Values, 
            PasteOperation.None, false, false);
    }
    ...
}

When you create an instance of MyCommandManager, it will automatically become the CommandManager of the specified IWorkbookSet, i.e.:

  new MyCommandManager(workbookView.ActiveWorkbookSet);

Obviously, this approach means that any formatting you want to pull over from Excel would get lost.