如何使用剪贴板将数据从Excel Sheet复制到DataTable？ [英] How to use clipboard to copy data from Excel Sheet to DataTable?

问题描述

我有一个Winform项目，在Microsoft Framework 3.5上创建。
用户可能已经安装了Windows 7或Windows XP，Office 2007或更高版本。

我正在开发一个程序来获取剪贴板数据，放在C＃DataTable上。
我已经创建了一种从剪贴板获取原始数据并将其上传到DataTable中的方法。

但在某些情况下，Excel数据显示值，但内部却有另一个：

基本上，我想将这些结构转换成一个简单的DataTable，只有原始数据。
有人可以帮助我一个提示如何实现这一点？
...我不想在这个实现中使用第三方库。

解决方案

我发现一个清洁和防弹解决方案。这里的代码：

首先，将XmlDocument转换为XElement的扩展名：

  ///< summary>将XML文档转换为XDocument< / summary> 
 ///< param name =xmlDocument>附加的XML文档< / param> 
 public static XDocument fwToXDocument（this XmlDocument xmlDocument）
 {
 using（XmlNodeReader xmlNodeReader = new XmlNodeReader（xmlDocument））
 {
 xmlNodeReader.MoveToContent（）; 
 return XDocument.Load（xmlNodeReader）; 
} 
} 
  

完整的功能：

  private DataTable clipboardExcelToDataTable（bool blnFirstRowHasHeader = false）
 {
 string strTime =S+ DateTime.Now.ToString（mm ：SS：FFF）; 
 var clipboard = Clipboard.GetDataObject（）; 
 if（！clipboard.GetDataPresent（XML Spreadsheet））返回null; 
 
 strTime + =\r\\\
Read+ DateTime.Now.ToString（mm：ss：fff）; 
 StreamReader streamReader = new StreamReader（（MemoryStream）clipboard.GetData（XML Spreadsheet））; 
 strTime + =\r\\\
Finish read+ DateTime.Now.ToString（mm：ss：fff）; 
 streamReader.BaseStream.SetLength（streamReader.BaseStream.Length  -  1）; 
 
 XmlDocument xmlDocument = new XmlDocument（）; 
 xmlDocument.LoadXml（streamReader.ReadToEnd（））; 
 strTime + =\r\\\
Read XML Document+ DateTime.Now.ToString（mm：ss：fff）; 
 
 XNamespace ssNs =urn：schemas-microsoft-com：office：spreadsheet; 
 DataTable dtData = new DataTable（）; 
 
 var linqRows = xmlDocument.fwToXDocument（）。Descendants（ssNs +Row）ToList< XElement>（）; （int x = 0; x  dtData 
 
 Columns.Add（Column {0}。fwFormat（x + 1））; 
 
 int intCol = 0; 
 
 DataRow drCurrent; 
 
 linqRows.ForEach（rowElement => 
 {
 intCol = 0; 
 drCurrent = dtData.Rows.Add（）; 
 rowElement.Descendants （ssNs +Cell）
 .ToList< XElement>（）
 .ForEach（cell => drCurrent [intCol ++] = cell.Value）; 
}）; 
 
 if（blnFirstRowHasHeader）
 {
 int x = 0; 
 foreach（DataColumn dcCurrent in dtData.Columns）
 dcCurrent.ColumnName = dtData.Rows [0] [x ++] ToString（）; 
 
 dtData.Rows.RemoveAt（0）; 
} 
 
 strTime + =\r\\\
F+ DateTime.Now.ToString（mm：ss：fff）; 
 
 return dtData; 
} 
  

该过程需要〜15秒钟读取〜25,000行。

适用于任何类型的数据。
基本上，该方法创建一个具有相同结构的Excel工作表的网格。
行或列的合并将填满第一个单元格。
默认情况下，所有列都将为字符串DataType。

I have a Winform project, created on Microsoft Framework 3.5. The users may have installed Windows 7 or Windows XP, and Office 2007 or above.

I'm working on in a procedure to get the clipboard data and put in on a C# DataTable. I already created a method to get the raw data from the clipboard and upload it in a DataTable.

But in some cases, the Excel data shows a value, but internally have another:

I'm investigating a method to get the raw data from Excel:

string XmlFmt = "XML Spreadsheet";
var clipboard = Clipboard.GetDataObject();

if (clipboard.GetDataPresent(XmlFmt))
{
    var clipData = clipboard.GetData(XmlFmt);
    StreamReader streamReader = new StreamReader((MemoryStream)clipData);
    streamReader.BaseStream.SetLength(streamReader.BaseStream.Length - 1);

    string xmlText = streamReader.ReadToEnd();
    var stream = new StringReader(xmlText);

    XmlDocument xmlDocument = new XmlDocument();
    xmlDocument.LoadXml(xmlText);

    DataSet dsExcelData = new DataSet();
    dsExcelData.ReadXml(new XmlNodeReader(xmlDocument));
}

But, this method retrieves me a DataSet with multiples tables with the configuration of each part of the Excel Data:

Basically, I want to convert these structures to a simple DataTable with only the raw data. Someone could help me with a hint how achieve this? ...I don't want to use a third party library in this implementation.

解决方案

I found a clean and bullet-proof solution. Here the code:

First, a extension to convert a XmlDocument to XElement:

/// <summary> Convert XML Document to XDocument </summary>
/// <param name="xmlDocument">Attached XML Document</param>
public static XDocument fwToXDocument(this XmlDocument xmlDocument)
{
    using (XmlNodeReader xmlNodeReader = new XmlNodeReader(xmlDocument))
    {
        xmlNodeReader.MoveToContent();
        return XDocument.Load(xmlNodeReader);
    }
}

The complete function:

private DataTable clipboardExcelToDataTable(bool blnFirstRowHasHeader = false)
{
    string strTime = "S " + DateTime.Now.ToString("mm:ss:fff");
    var clipboard = Clipboard.GetDataObject();
    if (!clipboard.GetDataPresent("XML Spreadsheet")) return null;

    strTime += "\r\nRead " + DateTime.Now.ToString("mm:ss:fff");
    StreamReader streamReader = new StreamReader((MemoryStream)clipboard.GetData("XML Spreadsheet"));
    strTime += "\r\nFinish read " + DateTime.Now.ToString("mm:ss:fff");
    streamReader.BaseStream.SetLength(streamReader.BaseStream.Length - 1);

    XmlDocument xmlDocument = new XmlDocument();
    xmlDocument.LoadXml(streamReader.ReadToEnd());
    strTime += "\r\nRead XML Document " + DateTime.Now.ToString("mm:ss:fff");

    XNamespace ssNs = "urn:schemas-microsoft-com:office:spreadsheet";
    DataTable dtData = new DataTable();

    var linqRows = xmlDocument.fwToXDocument().Descendants(ssNs + "Row").ToList<XElement>();

    for (int x = 0; x < linqRows.Max(a => a.Descendants(ssNs + "Cell").Count()); x++)
        dtData.Columns.Add("Column {0}".fwFormat(x + 1));

    int intCol = 0;

    DataRow drCurrent;

    linqRows.ForEach(rowElement =>
        {
            intCol = 0;
            drCurrent = dtData.Rows.Add();
            rowElement.Descendants(ssNs + "Cell")
                .ToList<XElement>()
                .ForEach(cell => drCurrent[intCol++] = cell.Value);
        });

    if (blnFirstRowHasHeader)
    {
        int x = 0;
        foreach (DataColumn dcCurrent in dtData.Columns)
            dcCurrent.ColumnName = dtData.Rows[0][x++].ToString();

        dtData.Rows.RemoveAt(0);
    }

    strTime += "\r\nF " + DateTime.Now.ToString("mm:ss:fff");

    return dtData;
}

The process takes ~15 seconds to read ~25,000 rows.

Works perfectly for any kind of data. Basically, the method creates a grid with the same structure of the Excel WorkSheet. Merge of rows or columns will fill up the first cell able. All columns will be string DataType by default.

这篇关于如何使用剪贴板将数据从Excel Sheet复制到DataTable？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！