Preserve empty Excel cells when reading data from clipboard into DataTable

Question

I am using the following code to read Excel data from the clipboard into a C# data table. The code is relatively unchanged as found from this answer to this question. I then add the data table as a data source to a DataGridView control for manipulation.

However, in my Excel data, I have blank/empty cells that I need to preserve, which this code does not do (blank cells are skipped over, effectively compressing each row leaving no empty space; the empty cells are missing from the Excel XML). How could I preserve empty cells when transferring to the data table?

Method:

private DataTable ParseClipboardData(bool blnFirstRowHasHeader)
    {
        var clipboard = Clipboard.GetDataObject();
        if (!clipboard.GetDataPresent("XML Spreadsheet")) return null;
        StreamReader streamReader = new StreamReader((MemoryStream)clipboard.GetData("XML Spreadsheet"));
        streamReader.BaseStream.SetLength(streamReader.BaseStream.Length - 1);

        XmlDocument xmlDocument = new XmlDocument();
        xmlDocument.LoadXml(streamReader.ReadToEnd());
        XNamespace ssNs = "urn:schemas-microsoft-com:office:spreadsheet";
        DataTable dt = 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++)
            dt.Columns.Add("Column " + x.ToString());

        int intCol = 0;
        DataRow currentRow;

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

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

            dt.Rows.RemoveAt(0);
        }

        return dt;
    }

Extension method:

public static XDocument fwToXDocument(this XmlDocument xmlDocument)
{
    using (XmlNodeReader xmlNodeReader = new XmlNodeReader(xmlDocument))
    {
        xmlNodeReader.MoveToContent();
        var doc = XDocument.Load(xmlNodeReader);
        return doc;
    }
}

Contrived example to illustrate: (Excel 2015)

Range in Excel, copied to clipboard

DataGridView on Winform, with data table as data source

score 1 · Accepted Answer · answered Jul 07 '17 at 19:59

The cell's xml will have an Index attribute if the previous cell was missing (had an empty value). You can update your code to check if the column index has changed before copying it to your data table row.

linqRows.ForEach(rowElement =>
{
    intCol = 0;
    currentRow = dt.Rows.Add();
    rowElement.Descendants(ssNs + "Cell")
        .ToList<XElement>()                    
        .ForEach(cell => 
        {
            int cellIndex = 0;
            XAttribute indexAttribute = cell.Attribute(ssNs + "Index");

            if (indexAttribute != null)
            {
                Int32.TryParse(indexAttribute.Value, out cellIndex);
                intCol = cellIndex - 1;
            }

            currentRow[intCol] = cell.Value;
            intCol++;
        });
});

Preserve empty Excel cells when reading data from clipboard into DataTable

1 Answers1