Load DataGrid from Excel File
-
Hi. Last week I posted a question regarding reading Excel file and loading it into the DataGrid. I didn't get any useful response back so please forgive me for posting this second time. I'm sure someone had the same problem I have. I have 3 columns in Excel. One of the columns (User Id) has text data (like: User 1) or a number data (like: 12345). My code works and doesn't throw any error but for some reason it only extracts text data. The numbers are missing and I'm not sure why. I've tried to foramt that columnt to TEXT only but it doesn't help. Just to be sure I looped through DataSet to make sure the values are there and of course they weren't. Here's the code that reads Excel and loads data into DataGrid. Please help. Thank you.
Try 'Create DataSet and DataAdapter ds = New System.Data.DataSet da = New System.Data.OleDb.OleDbDataAdapter 'Establish connection con = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=" & Session("ExcelFilePath").ToString() & "; " & _ "Extended Properties=Excel 8.0") 'Create command cmdSelectExcel = New System.Data.OleDb.OleDbCommand("SELECT * FROM [Sheet1$] ORDER BY Company", con) 'Open connection con.Open() 'Exectue command through adapter da.SelectCommand = cmdSelectExcel da.Fill(ds, "[Sheet1$]") 'Assign DataSourse of the DataGrid and bind it dg.DataSource = ds.Tables(0).DefaultView dg.DataBind() 'Close Connection con.Close() Catch ex As Exception 'Display error message lblError.Text = "Error: " & ex.ToString() lblError.Visible = True End Try -
Hi. Last week I posted a question regarding reading Excel file and loading it into the DataGrid. I didn't get any useful response back so please forgive me for posting this second time. I'm sure someone had the same problem I have. I have 3 columns in Excel. One of the columns (User Id) has text data (like: User 1) or a number data (like: 12345). My code works and doesn't throw any error but for some reason it only extracts text data. The numbers are missing and I'm not sure why. I've tried to foramt that columnt to TEXT only but it doesn't help. Just to be sure I looped through DataSet to make sure the values are there and of course they weren't. Here's the code that reads Excel and loads data into DataGrid. Please help. Thank you.
Try 'Create DataSet and DataAdapter ds = New System.Data.DataSet da = New System.Data.OleDb.OleDbDataAdapter 'Establish connection con = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=" & Session("ExcelFilePath").ToString() & "; " & _ "Extended Properties=Excel 8.0") 'Create command cmdSelectExcel = New System.Data.OleDb.OleDbCommand("SELECT * FROM [Sheet1$] ORDER BY Company", con) 'Open connection con.Open() 'Exectue command through adapter da.SelectCommand = cmdSelectExcel da.Fill(ds, "[Sheet1$]") 'Assign DataSourse of the DataGrid and bind it dg.DataSource = ds.Tables(0).DefaultView dg.DataBind() 'Close Connection con.Close() Catch ex As Exception 'Display error message lblError.Text = "Error: " & ex.ToString() lblError.Visible = True End TryHi Alexfromto. Do you want that the oledb driver extracts all data as Text, correctly? Then you need add the follow Extended Properties: 1st: IMEX=1; 2nd: MaxScanRows=0 The first property force the OleDb Driver to read any cell of excel for determine the column type. The second establish the number of cell for every column to scan for determine de column type, the 0 value force to scan all cells.
SINCERELY. ANTHONY ACUÑA PREFERED PHRASE: SOMEBODY TELL ME WHY IS MORE REAL WHEN I DREAM THAT I AM WAKE?
-
Hi Alexfromto. Do you want that the oledb driver extracts all data as Text, correctly? Then you need add the follow Extended Properties: 1st: IMEX=1; 2nd: MaxScanRows=0 The first property force the OleDb Driver to read any cell of excel for determine the column type. The second establish the number of cell for every column to scan for determine de column type, the 0 value force to scan all cells.
SINCERELY. ANTHONY ACUÑA PREFERED PHRASE: SOMEBODY TELL ME WHY IS MORE REAL WHEN I DREAM THAT I AM WAKE?
Thank you very much Anthony. It worked like a charm. You da man. Alex.