从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

单元格格式为Percentage，并设置为显示两位小数。所以他们在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 .49％'和'31 .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中将相同的单元格计算并复制/粘贴到Calc中。 Calc保留原始数据的完整精度。

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 Calc源代码，并且将会有一个窍门，看看我能否找到他们如何从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-To-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数据读取器，但是生成的DataSet总是空的...

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屋！