-
May 2nd, 2018, 09:17 AM
#1
Thread Starter
New Member
-
May 2nd, 2018, 12:15 PM
#2
Re: Read Excel Sheet Data into DataGridView
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\Users\u618863\Documents\TestFile.xlsx; Extended Properties=Excel 8.0;")
I don't have direct experience with what you are doing, but I do know that Excel 8.0 refers to Excel 97, which would just be a plain old .xls file, not an .xlsx file. So, one thing to try would be to save your spreadsheet as the older .xls format of a file and change your connection to point to the .xls version of the file and see if that works. Another option is to change the Extended Properties value to a version that supported .xlsx files, possibly Excel 12.0.
Good luck.
-
May 2nd, 2018, 05:35 PM
#3
Re: Read Excel Sheet Data into DataGridView
Try the following
Code:
Imports System.Data.OleDb
Public Module ExcelSampleReadRange
Public Function GetSheetData() As DataTable
Dim fileName As String = "C:\Users\u618863\Documents\TestFile.xlsx"
Dim dt As New DataTable
Using cn As New OleDbConnection With
{
.ConnectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={fileName};Extended Properties=""Excel 12.0;IMEX=1;HDR=No;"""
}
Dim cmd = New OleDbCommand("SELECT * FROM [Sheet1$A1:D1]", cn)
cn.Open()
dt.Load(cmd.ExecuteReader)
End Using
Return dt
End Function
End Module
-
May 2nd, 2018, 07:58 PM
#4
Re: Read Excel Sheet Data into DataGridView
Your code is a little confused, but it'll probably work if you remove the TableMappings line:
Code:
MyCommand.TableMappings.Add("Table", "TestTable")
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
May 4th, 2018, 03:14 AM
#5
Thread Starter
New Member
Re: Read Excel Sheet Data into DataGridView
Thank you all for your replies. Unfortunately none of them have worked so far.
I) Change Excel 8.0 -> 12.0 or the file extension from .xlsx to .xls got me a "Could not find installable ISAM" error.
II) For Karen's code I got an error about "unrecognizable characters" with the dollar sign. I replaced the dollar sign with a " and then "" but to no avail.
III) Remove TableMapping -> Got me an "External table not in expected format" error.
Any ideas?
-
May 4th, 2018, 08:19 AM
#6
Re: Read Excel Sheet Data into DataGridView
Originally Posted by DoubleN
I) Change Excel 8.0 -> 12.0 or the file extension from .xlsx to .xls got me a "Could not find installable ISAM" error.
If all you did was change the file extension of your existing file from .xlsx to .xls and try that, it will 100% not work. It's not about the file extension per-se, it is about the format of the file.
You need to open the .xlsx file in Excel and go through the steps to save it as a different file type and select the type of file that is tied to previous versions of Excel. I don't have a version of Office new enough to give you the exact steps, but you would want to save it as a file that is described something like "Excel 97-2003 (.xls)" or something like that.
-
May 4th, 2018, 09:08 AM
#7
Thread Starter
New Member
Re: Read Excel Sheet Data into DataGridView
Thanks Option! I re-saved it as .xls and it worked; however, I only used this .xlsx as an simple example, in reality I need to use an .xlsm file. So the .xls won't do the trick I'm afraid.
The following code does seem to work BUT it puts my values from .xlsx workbook,sheet1, as columns next to the current ones. What I want it to do is to retain the same location as in the excel file, i.e. "1" was in A1 so I need it to be under column 1 in my datatable..
I have attached a pic below.
The code I used for this to work is [removing the TableMappings line did not change anything]:
Code:
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\Users\u618863\Documents\TestFile.xlsx;Extended Properties=Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter _
("select * from [Sheet1$]", MyConnection)
MyCommand.TableMappings.Add("Table", "TestTable")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
-
May 4th, 2018, 09:15 AM
#8
Re: Read Excel Sheet Data into DataGridView
The columns are autogenerated. You can either remove your design time columns and it'll add your columns in the correct order, or set your dgv's autogeneratedcolumns property to false, and set the columns from the datatable to the columns in the dgv in your code. I'll find an example for you...
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
May 4th, 2018, 09:21 AM
#9
Re: Read Excel Sheet Data into DataGridView
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
May 4th, 2018, 09:26 AM
#10
Re: Read Excel Sheet Data into DataGridView
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|