How to transfer data from Excel to sfDataGrid?

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>





3 Replies 1 reply marked as answer

VS Vijayarasan Sivanandham Syncfusion Team November 22, 2021 02:09 PM UTC

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; 
} 

Regards, 
Vijayarasan S 


Marked as answer

ÖZ Özgür November 24, 2021 05:31 AM UTC

Thank yo for amswer. Code worked for me.



VS Vijayarasan Sivanandham Syncfusion Team November 24, 2021 05:45 AM UTC

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


Loader.
Up arrow icon