The three tables of interest in the xmlSpreadsheet DataSet schema are "Data" which holds the data values, "Row" which holds the row indexes, and "Cell" which hold the column indexes. Select New XMLSpreadsheetCellData With not recognized.", cell.DataType))Įnd If Return Nothing End If End If Return Nothing End Function On cellRecord( " Cell_Id") Equals dataRecord( " Cell_Id") On cellRecord( " Row_Id") Equals rowRecord( " Row_Id") Join dataRecord In spreadsheet.Tables( " Data") Rows(i)( " Index") + 1 End If Next End With Dim cells = (From cellRecord In spreadsheet.Tables( " Cell") Join rowRecord In spreadsheet.Tables( " Row") Rows(i)( " Row_Id") rowIndex Then columnIndex = 1ĬolumnIndex =. Rows(i)( " Index") + 1 End If Next End With ' Iterate through the cell table and set the column indexesĭim columnIndex = 0 With spreadsheet.Tables( " Cell")įor i = 0 To. Rows.Count - 1 If IsDBNull(.Rows(i)( " Index")) Then ' Iterate through the Row table and set the row indexes Dim rowIndex = 1 With spreadsheet.Tables( " Row")įor i = 0 To. If Not spreadsheet.Tables( " Row").Columns.Contains( " Index") Then spreadsheet.Tables( " Row").Columns.Add( New DataColumn( " Index", GetType( Int32))) If rowCount > 0 AndAlso columnCount > 0 Then Dim result(rowCount - 1, columnCount - 1) As Object If ( " Data") Then ' if there is no "Data" table then all cells are empty and all array elements will be nothing If Not spreadsheet.Tables( " Cell").Columns.Contains( " Index") Then spreadsheet.Tables( " Cell").Columns.Add( New DataColumn( " Index", GetType( Int32))) Spreadsheet.ReadXml(clipboardData.GetData( " XML Spreadsheet"))ĭim rowCount = spreadsheet.Tables( " Table").Rows( 0)( " ExpandedRowCount")ĭim columnCount = spreadsheet.Tables( " Table").Rows( 0)( " ExpandedColumnCount") If clipboardData IsNot Nothing Then If ( " XML Spreadsheet") Then Dim spreadsheet = New DataSet Public Property CellId As Int32 Public Property RowIndex As Int32 Public Property ColumnIndex As Int32 Public Property DataType As String Public Property DataValue As String End Class Private Function ParseClipboard() As Object(,)ĭim clipboardData = Clipboard.GetDataObject
#Excel import text clipboard code
The code is mostly self explanatory, and I would appreciate any comments or suggestions for improvement.Ĭopy Code Private Class XMLSpreadsheetCellData This is a function that will inspect the clipboard for a DataObject containing a "XML Spreadsheet" object and if found return an Array(,) of Object, or Nothing if not found. You can inspect the schema for this by setting a debugging breakpoint after the call to ReadXml and then click the magnifying glass in the watch window. The "XML Spreadsheet" object seems to be a self contained XML spreadsheet with a single sheet containing only the cells that were copied to the clipboard. Although the array which is returned is an array of objects, the underlying object type will be either String, Decimal, or DateTime. The main benefits of this are that it ensures empty cells are not skipped, and it will always transfer the raw unformatted value. The solution I finally arrived at was to use the "XML Spreadsheet" format to transfer the data. The other issue was that both of these methods transferred the data as it was "displayed" in Microsoft Excel which meant that according to the Microsoft Excel user's Excel formatting, the data could be transformed in arbitrary formats. I didn't not find any other standardized parser and wasn't confident to roll my own due to issues such as embedded deliminators or line breaks, etc. My next try was to grab the text from the clipboard as a "UnicodeText" object and then use a TextFieldParser (with VbTab deliminator), but that didn't work because the TextFieldParser would ignore blank cells (Microsoft says this is not a bug, but I think it is since the TextFieldParser actually performs a transform which deletes empty rows as it parses). My first try was copying the text from the clipboard and then parsing it, but that didn't work because my text has Asian characters and the text is copied as ASCII (I think). Copying data from Excel to my application turned out to be a tricky beast.
Copying from my app to Excel is easy (it is already baked into the DevExpress GridView I am using). I am developing an application and need to be able to copy and paste between my application and Microsoft Excel using the clipboard.
NET Array of Objects (which the user can then use as needed).
The function will inspect the clipboard DataObject for an "XML Spreadsheet" object, and convert that to a. This tip presents a function to transfer data cut or copied from Microsoft Excel into VB.NET using the clipboard.
0 kommentar(er)
