Problems about Paste using Clipboard

Seems that posts on forum get reply a little easily.

Hi,
I encouter problems about paste,  make me frustrated, and turn for help. Here is the story.

I use Entity Framework to bulid my database, and then use Flexcel to generate Excel report. After the report is generated, I open the report file and put the data on the Clipboard according to the "40.Copy And Paste" example in the sample folder. Here is the code:
  public static void GetDataObjectOnClipBoardFromFlexcel(string filename)
        {
            List<MemoryStream> dataStreams = new List<MemoryStream>();
            try
            {
                
                XlsFile file = new XlsFile(true);
                file.Open(filename);
                DataObject data = new DataObject();
                Clipboard.Clear();
                //we will use this list to dispose the memorystreams after they have been used.
                foreach (FlexCelClipboardFormat cf in Enum.GetValues(typeof (FlexCelClipboardFormat)))
                {

                    MemoryStream dataStream = new MemoryStream();
                    dataStreams.Add(dataStream);
                    file.CopyToClipboard(cf, dataStream);
                    dataStream.Position = 0;
                    data.SetData(FlexCelDataFormats.GetString(cf), dataStream);
                }
                Clipboard.SetDataObject(data, true);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message + "\r\n\r\n" + exception.StackTrace);
            }
            finally
            {
                dataStreams.ForEach(d =>
                {
                    d.Dispose();
                });
            }
        }
And then, I paste the data back to the Excel Instance using the "Microsoft.Office.Interop.Excel" Assembly.  The code is as followed:
     private void GetExcelReportFromClipBoard()
        {
            var currentSheet = (Excel.Worksheet) this.currentWorkSheets.Add();
            currentSheet.PasteSpecial(Format:"Unicode 文本");
            var usedRange = currentSheet.UsedRange;
            var borders = usedRange.Borders;
            borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            var rows = currentSheet.Rows;
            var columns = currentSheet.Columns;
            rows.AutoFit();
            columns.AutoFit();
            currentSheet = null;
            borders = null;
            rows = null;
            columns = null;
            usedRange = null;
        }
The problems come to me: 
1. When debuging, After  I test  two more times, the code fires an Accessvalidationexception, Excel shuts down automatically and it reboots. I don't know why.
2.  I also try to test "Ctrl+V" in Excel, the problem remains the same.
3. I test 'currentSheet.PasteSpecial(Format="HTML")' or currentSheet.Range["A1"].PasteSpecial, and after pasting into Excel it seems that the Datetime Data turns into "########". I also don't konw why.
Thank you for help.

I revise the line of code:

currentSheet.PasteSpecial(Format:"Unicode 文本");
I use this:
currentSheet.PasteSpecial();



They don't. We keep a strict first-in / first out priority list to answer, and we don't really care the medium from where the question came from.

This being said, I do prefer that people post the questions in the forums, because of 2 reasons:
1)What I write here can help other people with similar problems, while what I answer via email will only benefit the guy I answered to.
2)Posts in the newsgroups don't get lost in spam filters, which is something that happens a lot via email. It is really frustrating to spend 30 minutes writing an answer and have that answer marked as spam and the user never noticing he got an answer.

But no matter what I prefer, we don't give any special treatment to posts in the forums, that would be unfair to people who prefer to use other ways. We respect everyone's preferences and I won't try to force my own into our users.

In your case, you wrote yesterday in a saturday evening here, and I didn't got enough time to finish a test app so I was finishing it today (sunday). I can understand you are frustrated, but please understand we are human too.

Now, going back to your problem. I tried it here and I couldn't find any issues. Worked fine and didn't crash no matter how many times I pressed the button. So I would ask you to follow the steps below to see if we can figure out what is going on.

1)Download the test app I used from here:
http://www.tmssoftware.biz/flexcel/samples/TestCopyAndPaste.zip

2)Run it in your machine. Does it crash?

3)If it crashes in your machine, can you tell me the exact version of Excel you are using so I can try to reproduce it with the same version here? (I used Excel 2016 in my tests). Also, do you have anything installed that could be causing issues: A clipboard manager, an antivirus, etc?

4)If this test app worked fine with your machine, then it is probably a problem of the file itself. Can you try the test app with your actual file instead of the test file I used? Does it crash with your actual file?

5)If it crashes with your actual file, can you send it to me so I can see what is going on?

6)If the app doesn't crash with your file either, is there anything else that you think you can do to modify the test app to show the problem? Anything that is different in your actual app?

Now a personal note: While of course I don't know the exact problem you are solving, copying to the clipboard and then using OLE Automation to paste into a sheet seems a very fragile process. When you join a shared thing like the clipboard with another shared (and very flaky) think like OLEAutomation you are likely to see issues like this or worse (Imagine the user replaces the clipboard after the moment you copied the data, but before you have time to paste it). Or the user is busy writing in Excel when you paste your data on it.

It also isn't very user friendly to have his clipboard replaced so you can paste your data. The user might have something already in his clipboard before running your app, and he will expect to still have the same data in the clipboard once your app runs. FlexCel's copy to clipboard feature works best with a button "Copy to clipboard" where the user explicitly decides he wants to put your data into the clipboard.
So I am wondering if there wouldn't be a simpler way to achieve  what you want to achieve. (Maybe using FlexCel's InsertAndCopyRange from one file to another?) I don't know, and it might be the case that copying and pasting is the only viable solution, but if you can explain me a little more what is what you are trying to do in a higher level, I might be able to think in a more solid solution.

Thank you for your advice. 

And sorry for that, I don't mean it.
My os version is Win10, and the Excel Version is 2016.
I download the sample code, and test it. At first it crashes again. After I stop Windows Defender, it works every time I test.
Thank you again.

This is strange, as it is the same configuration I tested it here (win10, Excel 2016, windows defender enabled). I will try to play a little with the settings of defender to see if I can reproduce it.

But the larger point stands: As shown here (with 2 machines with the same configuration one crashes, the other doesn't), the whole thing is a little unstable. Starting with OLE Automation itself which is the reason FlexCel exists at all: If OLE Automation was reliable, we would have used it instead of developing FlexCel. But reality is that OLE crashes a lot, and might work well in one machine, and another, and then crash in a third for unknown reasons.

Isn't there a way you can avoid using OLE Automation? FlexCel can copy the cells or sheets from one file to another if that is your need.

Yeah, I find an alternative way. My goal is to get the excel report back to Excel Instance. So at first I use flexcel to generatee the report, and then use the Interop.Excel Api to copy the report and paste data on the target sheet. Both the sheets share the same Excel.Application class.

Here is my ExcelHelper Class:

By the way, it is stable and quicker than I think. If the api creates a new Excel.Application class, the operation is slower than this.