How to transfer data from Excel to sfDataGrid?

[Özgür]

Hello,

I want to export data from excel to sfDataGrid. I reached the example below but without success.

In the example below, it assumes that the data it created has been imported from Excel. How can I open OpenFileDialog and select an excel table and transfer the data in it to sfDataGrid?

Export Example

My sfDataGrid Code

<Syncfusion:SfDataGrid ItemsSource="{Binding Can1CollectionView}"

                                       Name="can1DataGrid"

                                       AllowFiltering="True"

                                       SelectionMode="Single"

                                       EnableDataVirtualization="True"

                                       ShowGroupDropArea="True"

                                       LiveDataUpdateMode ="AllowDataShaping"

                                       FontSize="{Binding ElementName=FontSizeValue, Path=Text}"

                                       Height="540">

                    <Syncfusion:SfDataGrid.Columns>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding ID}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding RTR}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding IDE}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding DLC}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding Byte0}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding Byte1}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding Byte2}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding Byte3}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding Byte4}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding Byte5}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding Byte6}" Width="90"/>

                        <Syncfusion:GridTextColumn DisplayBinding="{Binding Byte7}" Width="90"/>

                        <Syncfusion:GridDateTimeColumn DisplayBinding="{Binding Time}"

                                                       Pattern="CustomPattern"

                                                       CustomPattern="dd-m-yyyy hh:mm:ss"

                                                       Width="180"/>

                    </Syncfusion:SfDataGrid.Columns>

                </Syncfusion:SfDataGrid>

[Vijayarasan Sivanandham]

Hi Özgür,

Thank you for contacting Syncfusion Support.

Your requirement can be achieved by using OpenFileDialog to get the Excel file and read the data from Excel using IWorkSheet.ExportDataTable method and convert the data to a DataTable collection in SfDataGrid. Please refer the below code snippet, 

private void OnImportToExcelClicked(object sender, RoutedEventArgs e) 

{ 

            ExcelEngine excelEngine = new ExcelEngine(); 

 

            IApplication application = excelEngine.Excel; 

            IWorkbook workbook = null; 

 

            //open the OpenFileDialog and select an excel table 

            OpenFileDialog sfd = new OpenFileDialog 

            { 

                FilterIndex = 2, 

                Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx" 

            }; 

            if (sfd.ShowDialog() == true) 

            { 

                workbook = application.Workbooks.Open(sfd.FileName); 

            } 

            IWorksheet worksheet = workbook.Worksheets[0]; 

 

            //Read data from the worksheet and Export to the DataTable 

            DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); 

 

            //Binding exported DataTable to data grid, likewise it can binded to any  

            //user interface control which supports binding            

            sfDataGrid.ItemsSource = customersTable; 

} 

Sample Link: https://www.syncfusion.com/downloads/support/forum/170554/ze/Sample983741064

UG Link: https://help.syncfusion.com/wpf/datagrid/export-to-excel

Please let us know if you have any concerns in this. 

Regards, 

Vijayarasan S 

[Özgür]

Thank yo for amswer. Code worked for me.

[Vijayarasan Sivanandham]

Hi Özgür,

Thanks for the update.

We are glad to know that the reported problem has been resolved at your end. Please let us know if you have any further queries on this. We are happy to help you😊.

Regards,
Vijayarasan S