Excel.Range.Copy（）与Clipboard.GetText（）粘贴 [英] Excel.Range.Copy() paste with Clipboard.GetText()

问题描述

我有这段C＃代码，试图在Excel文件中遍历工作表，将UsedRange 复制到剪贴板，然后粘贴到一个文本文件中。

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.

到目前为止，我所拥有的代码似乎可以正常工作引发任何错误，但由于某种原因，什么也没有写入我的文本文件。我想念什么吗？而且，如果我将 System.Reflection.Missing.Value 作为Copy方法的参数，仍然没有任何反应。

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.

这是我的代码：

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

编辑＃1：我怀疑我的小应用程序一定有某种引用，因为如果我创建一个新的C＃项目，Haxx建议的代码段就可以使用。但是，如果我使用Haxx的相同代码并将其作为新方法插入我的小应用程序中，即使它是完全相同的代码，并且调用了完全相同的使用库，它也无法正常工作。重做整个应用程序，复制/粘贴代码中最重要的部分，如果可以解决问题，请报告。

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.

编辑＃2：相信我知道问题出在哪里。我忘了提到我正在使用BackgroundWorker（ System.ComponentModel.BackgroundWorker ），以便在运行此小程序时可以显示进度栏。我刚刚读到，显然您只能从STAThread访问剪贴板（ Clipboard.GetText返回null （空字符串））。因此，我继续创建了一个单独的方法，该方法不使用BackgroundWorkers，因此可以在STAThread上运行，瞧！该代码是正确的并且可以运行，但是如果从BackgroundWorker线程异步调用剪贴板，则尝试访问剪贴板将不起作用。

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.

推荐答案

我制作了一个excel文件，其中第一张纸包含单元格A1 ='a'，B1 ='b'，A2 ='c'，B2 ='d'

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现在包含 a\tb\r\nc\td\r＼ ＼n

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

这篇关于Excel.Range.Copy（）与Clipboard.GetText（）粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！