Spreadsheetgear error when uses clipboard

Question

In my application I use spreadsheetgear to work with Excel files. I use the following code:

//move incorrect to aux                         
incorrectLine.Select(); //select the line to be moved
wbkView.Cut();

auxLine.Select();
wbkView.Paste();

//move correct to incorrect
correctLine.Select(); //select the line to be moved
wbkView.Cut();

incorrectLine.Select();
wbkView.Paste();

//move aux to correct 
auxLine.Select(); //select the line to be moved
wbkView.Cut();

correctLine.Select();
wbkView.Paste();

I receive from time to time the following error:

Requested Clipboard operation did not succeed. 

with the StrackTrace:

at System.Windows.Forms.Clipboard.ThrowIfFailed(Int32 hr)

and ErrorCode = -2147221040

From what I understand there is a problem if the clipboard is used by other process so I want to know if exists another mode to switch two lines from an Excel file without using the clipboard.

Answer 1

You might be able to use the IRange.Copy(...) method instead of WorkbookView.Cut/Paste. This method performs a copy routine internally within SpreadsheetGear, without the use of the Windows Clipboard, so would avoid any problems like the one you are seeing here. Plus, you wouldn’t need all those Select method calls to navigate around the worksheet.

The obvious problem is that this is a copy routine and not a cut routine, which behave different from one another in a variety of ways:

• Copy obviously leaves the source range untouched; cut removes the value and formatting from the source range. You could work around this by clearing out the source range via IRange.Clear() after calling IRange.Copy().
• More subtly, if your source range contain formulas or named ranges, references within these formulas and also named ranges will potentially be fixed up in different manner when using copy instead of cut. Not much can be done about this as this is simply the way Excel works, and SpreadsheetGear follows Excel’s lead on these things. Of course, if your source range is just simple values with no named ranges, this isn’t a problem.

Putting it all together, your code might look like the following:

// move incorrect to aux
incorrectLine.Copy(auxLine);
incorrectLine.Clear();

// move correct to incorrect
correctLine.Copy(incorrectLine);
correctLine.Clear();

// move aux to correct
auxLine.Copy(correctLine);
auxLine.Clear();