Hi!
I want to use the excel data source selection ui in order to populate a datatable from an excel file.
I found from the documentation that I can use
Visual BasicExcelDataSourceUIHelper.EditDataSource
but I don't know what to do with the returned value…
How can I handle this?
Hi
While our ExcelDataSource component does not provide a built-in method to export data to a DataTable from a worksheet, you can implement the required functionality manually. Here is a sample code snippet that demonstrates how to retrieve data to a DataTable:
C#public static class ExcelDataSourceExtension {
public static DataTable ToDataTable(this ExcelDataSource excelDataSource) {
IList list = ((IListSource)excelDataSource).GetList();
DevExpress.DataAccess.Native.Excel.DataView dataView = (DevExpress.DataAccess.Native.Excel.DataView)list;
List<PropertyDescriptor> props = dataView.Columns.ToList<PropertyDescriptor>();
DataTable table = new DataTable();
for(int i = 0; i < props.Count; i++) {
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[props.Count];
foreach(DevExpress.DataAccess.Native.Excel.ViewRow item in list) {
for(int i = 0; i < values.Length; i++) {
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
}
In general, it's possible to accomplish the task using our Spreadsheet Document Server. This non-visual component allows operating with Excel-like files. You can export a worksheet's data to a DataTable by using the approach described in the How to: Export a Worksheet Range to a DataTable topic and the How to export cell range to a DataTable example. However, note that classes used in this scenario are defined in the DevExpress.Docs.v16.2.dll assembly. The Universal Subscription or an additional Document Server Subscription is required to distribute this assembly.
Hi Rita,
I have converted the ExcelDataSourceExtension class to VB, and it operates without any issues on my side. Here is the class implementation in VB that I tested:
Visual BasicPublic Module ExcelDataSourceExtension
<System.Runtime.CompilerServices.Extension>
Public Function ToDataTable(ByVal excelDataSource As ExcelDataSource) As DataTable
Dim list As IList = (CType(excelDataSource, IListSource)).GetList()
Dim dataView As DevExpress.DataAccess.Native.Excel.DataView = CType(list, DevExpress.DataAccess.Native.Excel.DataView)
Dim props As List(Of DevExpress.DataAccess.Native.Excel.ViewColumn) = dataView.Columns
Dim table As New DataTable()
For i As Integer = 0 To props.Count - 1
Dim prop As PropertyDescriptor = props(i)
table.Columns.Add(prop.Name, prop.PropertyType)
Next i
Dim values(props.Count - 1) As Object
For Each item As DevExpress.DataAccess.Native.Excel.ViewRow In list
For i As Integer = 0 To values.Length - 1
values(i) = props(i).GetValue(item)
Next i
table.Rows.Add(values)
Next item
Return table
End Function
End Module
Would you please send us a small sample that shows the issue? We will research it on our side.
Hello,
I've created a separate ticket on your behalf (T892350: How to convert ExcelDataSource to DataTable?). It has been placed in our processing queue and will be answered shortly.
Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.
Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.