1

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.

Community
  • 1
  • 1
sergeidave
  • 662
  • 4
  • 11
  • 23
  • does it have to go via copy to clipboard? cause I could post an example of how I retrieve stuff from an excel file – bas Jan 25 '13 at 20:12
  • Oh, I have a couple other methods working now, one that goes cell by cell extracting values and another one that goes sheet by sheet and saves it as a text file. What I want to do is to compare the three methods, including this "Copy" method to see which one is faster/most efficient. – sergeidave Jan 25 '13 at 20:50
  • i'll drop an answer with what I got here. seems that what you are doing should work. – bas Jan 25 '13 at 21:10

1 Answers1

2

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"

bas
  • 13,550
  • 20
  • 69
  • 146
  • This is very strange. I just opened a new instance of C# to test your code and it works indeed. I even added the bit to write to the text file and it works too... I just don't know why it will not work in my code posted above. – sergeidave Jan 25 '13 at 22:29