从剪贴板（从 Excel 粘贴）获取包含重音字符的 CSV 数据

发布于 2024-07-23 09:38:04 字数 1994 浏览 4 评论 0原文

场景

我的用户将从 Excel 复制单元格（从而将其放入剪贴板）
我的应用程序将从剪贴板检索这些单元格

问题

我的代码从剪贴板检索 CSV 格式
但是，如果原始 Excel 内容包含类似 ä ( a 带变音符号）然后检索到的 CSV 字符串没有正确的字符（ä 最终对我来说显示为“正方形”）
相比之下，如果我的代码从剪贴板检索 Unicode 文本格式，则一切正常： ä 被保留在从剪贴板检索到的字符串中

源代码 - 原始 - 问题

[STAThread]
static void Main(string[] args)
{
    var fmt_csv = System.Windows.Forms.DataFormats.CommaSeparatedValue;

    // read the CSV
    var dataobject = System.Windows.Forms.Clipboard.GetDataObject();
    var stream = (System.IO.Stream)dataobject.GetData(fmt_csv);
    var enc = new System.Text.UTF8Encoding();
    var reader = new System.IO.StreamReader(stream,enc);
    string data_csv = reader.ReadToEnd();

    // read the unicode string
    string data_string = System.Windows.Forms.Clipboard.GetText();



}

所在运行示例代码时的结果

重现步骤：在 Excel 中输入一些文本（我使用了单词“doppelgänger”加上一些数字），然后只需按 Ctrl-C 进行复制将其复制到剪贴板，然后运行上面的代码。
data_csv 设置为“doppelgänger,1\r\n2,3\r\n\0”
data_string 设置为“doppelgänger\t1\r\n2\t3\r\n”

问题

我该怎么做才能获得正确的字符？

是的，我知道我可以通过使用 Unicode 文本来解决这个问题。但我实际上想了解在
检索流时使用或不使用 UTF-8 编码的 CSV 发生了什么对结果没有影响

答案

在查看评论并密切关注 Excel 的内容之后在 CSV 的剪贴板中，Excel 可能会使用“旧版”编码而不是 UTF-8 来放置内容，这似乎是合理的。所以我尝试使用 Windows 1252 代码页作为编码，它起作用了。请参阅下面的代码

源代码 - 带答案

[STAThread]
static void Main(string[] args)
{
    var fmt_csv = System.Windows.Forms.DataFormats.CommaSeparatedValue;

    //read the CSV
    var dataobject = System.Windows.Forms.Clipboard.GetDataObject();
    var stream = (System.IO.Stream)dataobject.GetData(fmt_csv);
    var enc = System.Text.Encoding.GetEncoding(1252);
    var reader = new System.IO.StreamReader(stream,enc);
    string data_csv= reader.ReadToEnd();

    //read the Unicode String
    string data_string = System.Windows.Forms.Clipboard.GetText();
}

原文

SCENARIO

My users will copy cells from Excel (thus placing it into the clipboard)
And my application will retrieve those cells from the clipboard

THE PROBLEM

My code retrieves the CSV format from the clipboard
However, the if the original Excel content contains characters like ä (a with umlaut) then retrieved CSV string doesn't have the correct characters (ä ends up showing as a "square" for me)
In comparison, if my code retrieves the Unicode text format from the clipboard everything works fine: the ä is preserved in the string retrieved from the clipboard

SOURCE CODE - ORIGINAL - WITH THE PROBLEM

[STAThread]
static void Main(string[] args)
{
    var fmt_csv = System.Windows.Forms.DataFormats.CommaSeparatedValue;

    // read the CSV
    var dataobject = System.Windows.Forms.Clipboard.GetDataObject();
    var stream = (System.IO.Stream)dataobject.GetData(fmt_csv);
    var enc = new System.Text.UTF8Encoding();
    var reader = new System.IO.StreamReader(stream,enc);
    string data_csv = reader.ReadToEnd();

    // read the unicode string
    string data_string = System.Windows.Forms.Clipboard.GetText();



}

THE RESULTS WHEN RUNNING THE SAMPLE CODE

Repro steps: Enter some text in Excel (I used the word "doppelgänger" plus some numbers) and simply hit Ctrl-C to copy it to the clipboard and then run the code above.
data_csv is set to "doppelg�nger,1\r\n2,3\r\n\0"
data_string is set to "doppelgänger\t1\r\n2\t3\r\n"

QUESTION

What can I do to get the correct characters?

COMMENTS

Yes, I know I could workaround this problem by using the Unicode text. But I actually want to understand what is going on with the CSV
using or not using the UTF-8 encoding when retrieving the stream makes no difference in the results

THE ANSWER

After looking at the comments, and paying close attention to what Excel was putting on the clipboard for CSV, it seemed reasonable that Excel might be placing the contents using an "legacy" encoding instead of UTF-8. So I tried the using the Windows 1252 codepage as the encoding and it worked. See the code below

SOURCE CODE - WITH THE ANSWER

[STAThread]
static void Main(string[] args)
{
    var fmt_csv = System.Windows.Forms.DataFormats.CommaSeparatedValue;

    //read the CSV
    var dataobject = System.Windows.Forms.Clipboard.GetDataObject();
    var stream = (System.IO.Stream)dataobject.GetData(fmt_csv);
    var enc = System.Text.Encoding.GetEncoding(1252);
    var reader = new System.IO.StreamReader(stream,enc);
    string data_csv= reader.ReadToEnd();

    //read the Unicode String
    string data_string = System.Windows.Forms.Clipboard.GetText();
}

分享到QQ

分享到微博