如何使用 C# 将 CSV 数据粘贴到 Windows 剪贴板
我想要完成的任务
- 我的应用程序生成一些表格数据
- 我希望用户能够启动 Excel 并单击“粘贴”将数据作为 Excel 中的单元格放置
- Windows 接受与其 API 一起使用的名为“CommaSeparatedValue”的格式所以这似乎是可能的
- 将原始文本放在剪贴板上可以工作,但尝试使用这种格式则不行
- 注意:我可以从剪贴板正确检索 CSV 数据,我的问题是将 CSV 数据粘贴到剪贴板。
我尝试过的方法不起作用
Clipboard.SetText()
System.Windows.Forms.Clipboard.SetText(
"1,2,3,4\n5,6,7,8",
System.Windows.Forms.TextDataFormat.CommaSeparatedValue
);
Clipboard.SetData()
System.Windows.Forms.Clipboard.SetData(
System.Windows.Forms.DataFormats.CommaSeparatedValue,
"1,2,3,4\n5,6,7,8",
);
在这两种情况下,某些内容都会放置在剪贴板上,但当粘贴到 Excel 中时,它会显示为一个垃圾文本单元格:“–§žý;pC yVk²ˆû"
更新 1:使用 SetText() 的解决方法
正如 BFree 的答案所示,SetText 和 TextDataFormat 是一种解决方法,
System.Windows.Forms.Clipboard.SetText(
"1\t2\t3\t4\n5\t6\t7\t8",
System.Windows.Forms.TextDataFormat.Text
);
我已经尝试过了,并确认现在粘贴到 Excel 和 Word 中可以工作正确。 在每种情况下,它都会粘贴为带有单元格而不是纯文本的表格。
仍然好奇为什么 CommaSeparatedValue 不起作用。
What I'm trying to accomplish
- My app generates some tabular data
- I want the user to be able to launch Excel and click "paste" to place the data as cells in Excel
- Windows accepts a format called "CommaSeparatedValue" that is used with it's APIs so this seems possible
- Putting raw text on the clipboard works, but trying to use this format does not
- NOTE: I can correctly retrieve CSV data from the clipboard, my problem is about pasting CSV data to the clipboard.
What I have tried that isn't working
Clipboard.SetText()
System.Windows.Forms.Clipboard.SetText(
"1,2,3,4\n5,6,7,8",
System.Windows.Forms.TextDataFormat.CommaSeparatedValue
);
Clipboard.SetData()
System.Windows.Forms.Clipboard.SetData(
System.Windows.Forms.DataFormats.CommaSeparatedValue,
"1,2,3,4\n5,6,7,8",
);
In both cases something is placed on the clipboard, but when pasted into Excel it shows up as one cell of garbarge text: "–§žý;pC¦yVk²ˆû"
Update 1: Workaround using SetText()
As BFree's answer shows SetText with TextDataFormat serves as a workaround
System.Windows.Forms.Clipboard.SetText(
"1\t2\t3\t4\n5\t6\t7\t8",
System.Windows.Forms.TextDataFormat.Text
);
I have tried this and confirm that now pasting into Excel and Word works correctly. In each case it pastes as a table with cells instead of plaintext.
Still curious why CommaSeparatedValue is not working.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
另一种解决方法使用 "Html 格式" 编写 html ; 并用特殊标头包围
Another workaround Use "Html Format" write html <table> and surround with special header
.NET Framework 将
DataFormats.CommaSeparatedValue
作为 Unicode 文本放置在剪贴板上。 但正如http://www.syncfusion.com/faq/windowsforms/faq_c98c中提到的.aspx#q899q,Excel 期望 CSV 数据是 UTF-8 内存流(很难说是 .NET 还是 Excel 导致了不兼容)。我在自己的应用程序中提出的解决方案是将两个版本的表格数据同时作为制表符分隔文本和 CSV 内存流放置在剪贴板上。 这允许目标应用程序以其首选格式获取数据。 记事本和 Excel 更喜欢使用制表符分隔的文本,但出于测试目的,您可以通过“选择性粘贴...”命令强制 Excel 抓取 CSV 数据。
下面是一些示例代码(请注意,此处使用了 WPF 命名空间中的 WinForms 等效项):
The .NET Framework places
DataFormats.CommaSeparatedValue
on the clipboard as Unicode text. But as mentioned at http://www.syncfusion.com/faq/windowsforms/faq_c98c.aspx#q899q, Excel expects CSV data to be a UTF-8 memory stream (it is difficult to say whether .NET or Excel is at fault for the incompatibility).The solution I've come up with in my own application is to place two versions of the tabular data on the clipboard simultaneously as tab-delimited text and as a CSV memory stream. This allows the destination application to acquire the data in its preferred format. Notepad and Excel prefer the tab-delimited text, but you can force Excel to grab the CSV data via the Paste Special... command for testing purposes.
Here is some example code (note that WinForms-equivalents from the WPF namespaces are used here):
使用制表符而不是逗号。 即:
我自己测试过,它对我有用。
Use tabs instead of commas. ie:
Just tested this myself, and it worked for me.
我已经成功使用 \t (参见 BFree 的答案)作为列分隔符和 \n 作为行分隔符粘贴到 Excel 中。
I have had success pasting into Excel using \t (see BFree's answer) as column separators and \n as row separators.
我通过使用 CSV 库 (KBCsv) 将数据写入临时文件夹中的 CSV 文件,然后使用
Process.Start()
在 Excel 中打开它。 一旦进入 Excel,格式化就很容易了,从那里复制粘贴即可。BYO 错误处理和 记录。
I got the most success defeating formatting issues by using a CSV library (KBCsv) to write the data into a CSV file in the temp folder then open it in Excel with
Process.Start()
. Once it is in Excel the formatting bit is easy(er), copy-paste from there.BYO Error handing & logging.