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.
If they are flat data you could do it like this.
Using the XML method is the reason for that internal structure of multiple datatables. Try this method instead:
Ref: http://www.seesharpdot.net/?p=221
Edit:
I've done some tests and even using the "XML Spreadsheet" Clipboard format the data can get stored in exponential notation:
You could detect and convert these numbers: Parse a Number from Exponential Notation
I found a clean and bullet-proof solution. Here the code:
First, a extension to convert a XmlDocument to XElement:
The complete function:
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.