How to use clipboard to copy data from Excel Sheet to DataTable?

Question

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.

Answer 1

If they are flat data you could do it like this.

private class Field
{
        public string Valor { get; set; }
}

private class Row
{
        public List<Field> Fields { get; set; }

        public Row(string value)
        {
            Fields = new List<Field>();
            var fieldsString = value.Split(new char[] {'\t'});
            foreach (string f in fieldsString)
            {
                Fields.Add(new Field {Valor = f});
            }
    }
}

public Parse()
{
    var data = Clipboard.GetDataObject();
    var datos = (string)data.GetData(DataFormats.Text);
    var stringRows = datos.Split(new Char[] {'\r', '\n'}, StringSplitOptions.RemoveEmptyEntries);
    var table = new List<Row>(stringRows.Length) ;
    foreach (string stringRow in stringRows)
    {
        table.Add( new Row(stringRow) );
    }
}