从Excel粘贴到C＃应用程序，保留充分的精度 [英] Paste from Excel into C# app, retaining full precision

问题描述

我有一个Excel电子表格的数据，像这样的值：

I have data in an Excel spreadsheet with values like this:

• 0.69491375


• 0.31220394

该单元格的格式为百分比，并设置为显示两位小数。因此，他们出现在Excel中的：

The cells are formatted as Percentage, and set to display two decimal places. So they appear in Excel as:

• 69.49％


• 31.22％

我有解析这个数据从剪贴板 C＃程序。

I have a C# program that parses this data off the Clipboard.

var dataObj = Clipboard.GetDataObject();
var format = DataFormats.CommaSeparatedValue;

if (dataObj != null && dataObj.GetDataPresent(format))
{
    var csvData = dataObj.GetData(format);
    // do something
}

问题是， csvData 包含来自Excel中显示值，即69年0.49％'和'31 0.22％。它不包含额外的小数位的全精度。

The problem is that csvData contains the display values from Excel, i.e. '69.49%' and '31.22%'. It does not contain the full precision of the extra decimal places.

我已经使用各种不同的 DataFormats 值试图，但数据永远只包含来自Excel中显示值，如：

I have tried using the various different DataFormats values, but the data only ever contains the display value from Excel, e.g.:

• DataFormats.Dif


• DataFormats.Rtf


• DataFormats.UnicodeText


• 等

• DataFormats.Dif
• DataFormats.Rtf
• DataFormats.UnicodeText
• etc.

作为测试，我安装的LibreOffice钙和复制/粘贴从Excel相同的细胞进入计算器。钙保留了原始数据的完整精度。

As a test, I installed LibreOffice Calc and copy/pasted the same cells from Excel into Calc. Calc retains the full precision of the raw data.

所以很明显的Excel把这个数据的地方，其他程序可以访问。 ？我怎样才能从我的C＃应用​​程序访问它。

So clearly Excel puts this data somewhere that other programs can access. How can I access it from my C# application?

修改 - 下一步

我已经下载了LibreOffice的计算器源代码，将有一个闲逛，看看我能找出他们如何从Excel中复制数据的完整的上下文。

I've downloaded the LibreOffice Calc source code and will have a poke around to see if I can find out how they get the full context of the copied data from Excel.

我也做了一个 GetFormats（）呼叫数据对象从剪贴板返回，得到了24个不同的数据格式的列表，其中一些是不是在 DataFormats 枚举。这些措施包括格式，如 BIFF12 ， BIFF8 ， BIFF5 ， Format129 ，所以我会调查这些和应对，如果我做任何的发现...

I also did a GetFormats() call on the data object returned from the clipboard and got a list of 24 different data formats, some of which are not in the DataFormats enum. These include formats like Biff12, Biff8, Biff5, Format129 among other formats that are unfamiliar to me, so I'll investigate these and respond if I make any discoveries...

推荐答案

也是不完整的答案，但有的进一步分析上市公司的问题：

Also not a complete answer either, but some further insights into the problem:

当你复制一个Excel单元格那么什么将剪贴板中的最后是一个包含一个电子表格，其中又包含一个单细胞的完整的Excel工作簿：

When you copy a single Excel cell then what will end up in the clipboard is a complete Excel workbook which contains a single spreadsheet which in turn contains a single cell:

var dataObject = Clipboard.GetDataObject();
var mstream = (MemoryStream)dataObject.GetData("XML Spreadsheet");

// Note: For some reason we need to ignore the last byte otherwise
// an exception will occur...
mstream.SetLength(mstream.Length - 1);

var xml = XElement.Load(mstream);

现在，当您转储的XElement到控制台的内容，你可以看到你确实得到完成Excel工作簿。也是XML表格格式包含存储在单元中的数字的内部表示。所以，我想你可以使用LINQ到XML或类似的获取你需要的数据：

Now, when you dump the content of the XElement to the console you can see that you indeed get a complete Excel Workbook. Also the "XML Spreadsheet" format contains the internal representation of the numbers stored in the cell. So I guess you could use Linq-To-Xml or similar to fetch the data you need:

XNamespace ssNs = "urn:schemas-microsoft-com:office:spreadsheet";

var numbers = xml.Descendants(ssNs + "Data").
              Where(e => (string)e.Attribute(ssNs + "Type") == "Number").
              Select(e => (double)e);

我也尝试使用的 Excel数据阅读器但是结果数据集总是出来的空...

I've also tried to read the Biff formats using the Excel Data Reader however the resulting DataSets always came out empty...

这篇关于从Excel粘贴到C＃应用程序，保留充分的精度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！