Excel.Range.Copy() paste with Clipboard.GetText()

问题:

I have this little piece of C# code in which I'm trying to loop through Worksheets inside an Excel file, copy the UsedRange to the Clipboard and paste it into a text file.

The code I have so far appears to work without throwing any errors, but for some reason nothing at all gets written into my text file. Am I missing something? And if I include the System.Reflection.Missing.Value as a parameter for the Copy method, still nothing happens.

Here's my code:

using (StreamWriter sw = File.CreateText("ExtractedText.txt"))
{
     foreach (Excel.Worksheet sheet in thisWkBook.Worksheets)
     {
         sheet.UsedRange.Copy();
         sw.Write(Clipboard.GetText());
     }                    
}

EDIT #1: I suspect that some kind of reference must've broke with my little app, since the piece of code suggested by Haxx works if I create a new C# project. But if I use the same code from Haxx and inserted in my little app as a new method, it just doesn't work, even though it's the exact same code with the exact same "using" libraries being called... I will just redo the whole app, copy/pasting the most important parts of code and report back if this fixes the problem.

EDIT #2: I believe I figured what the problem is. I forgot to mention that I'm using a BackgroundWorker (System.ComponentModel.BackgroundWorker) so that I can show a Progress Bar when I run this little process. I just read that apparently you can only access the Clipboard from the STAThread (Clipboard.GetText returns null (empty string)). So I went ahead and created a separate method that doesn't make any use of BackgroundWorkers, thus it runs on the STAThread, and voila! The code is correct and functional but trying to access the Clipboard won't work if calling it async from a BackgroundWorker thread.

回答1:

I made an excel file where the first sheet contains values in the cells A1 = 'a', B1 = 'b', A2 = 'c', B2 = 'd'

The code is simplified to just the following:

        Application appExl = new Application();
        Workbook workbook;

        //Opening Excel file(myData.xlsx)
        workbook = appExl.Workbooks.Open(
            @"c:\apps\book1.xlsx",
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value,
            Missing.Value);


        Worksheet sheet = workbook.Sheets.get_Item(1);
        sheet.UsedRange.Copy();
        var a = Clipboard.GetText();

a now contains "a\tb\r\nc\td\r\n"