#13: Reading MS Excel the OLEDB Way + WPF

Sorry folks for a bit delay of this blog. I've been so busy for the past few days. Lots of projects are coming and you know the feeling of pushing yourself just to bet the deadline. Anyway, I'm back to the blogging world and hopefully this one could help you also on your projects.

 Reading Excel file can also be done using the Interop.Excel. Its basically the most common way of reading an excel data. However, I just notice that it takes so much time reading the file specially if you have lots of rows and columns. But still using Interop is fine as long as you don't require faster reading.

This time we will talk about how to read the Excel file using the OLEDB. Well, if you compare it, OLEDB is faster in reading Excel Data. Also we will build this as WPF Application.

In your Visual Studio, create new project and select Visual C# WPF Application.

 

 

Then in your  Xaml, type below;

<Grid>
                <DataGrid FrozenColumnCount="1" AutoGenerateColumns="True" Margin="6" Name="dgList" SelectionMode="Single" SelectionUnit="CellOrRowHeader" CanUserAddRows="False" CanUserReorderColumns="False" CanUserResizeColumns="True" CanUserResizeRows="True" CanUserSortColumns="True" ItemsSource="{Binding Path=.}" LoadingRow="dgList_LoadingRow"></DataGrid>

</Grid>

You can also drag and drop a Datagrid from the toolbox then setup properties. Just follow the above Xaml datagrid settings.

In Code behind type the following;

 

private void Window_Loaded(object sender, RoutedEventArgs e)              

           {

                System.Data.OleDb.OleDbConnection MyConnection;             
                System.Data.OleDb.OleDbDataAdapter MyCommand;

                sPath = "E:/Test.xls"; //excel file path
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data     

               Source=" + sPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\";");
                MyConnection.Open();
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from WorksheetName$A1:J30", 

               MyConnection);
               MyCommand.TableMappings.Add("Table", "TestTable");
               DtSet = new System.Data.DataSet();
               MyCommand.Fill(DtSet);

           dgList.ItemsSource = DtSet.Tables[0].DefaultView; 

           }

Take note that in our Oledb connection we set HDR=Yes which means that the first row will be considered as the column names. IMEX=0 also means the excel data will read as it is, but if you set it IMEX=1, this will read excel data intermixed such as number, date and strings. If you're not sure what data type under excel, you can leave it as IMEX=0.

When you run the code above, you should have the excel data loaded in the datagrid. Quite easy but that's all you need to do.

Now we just completed a WPF excel file reader. For questions and inquiries just hit the comments.