Read Excel Sheet

[thurst0n]

I just want to read an excel sheet, and change the sheet into a 2d array....I want it with an openfiledialog, but whatever i try just doesnt seem to work! Any guidance would be helpful! Please don't assume i know anything, assume i know nothing!! Thanks in advance for any help!

[thurst0n]

ANY DIRECTION or SUGGESTION will help!! Please i'm desperate...!!

[CyberHawke]

Not too difficult, just need to know where to look.

Try this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dtSchema As DataTable
        Dim drSchema As DataRow
        Dim strForTextBox As String
        Dim fDlg As New OpenFileDialog
        fDlg.Filter = "Excel Spreadsheets (*.xls)|*.xls"
        fDlg.ShowDialog()
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                              "Data Source=" & fDlg.FileName & _
                                              ";Extended Properties=Excel 8.0;")
 
        cn.Open()
        dtSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
 
        DataGrid1.DataSource = dtSchema
        'DataGrid1.SetDataBinding(dtSchema, "")
        For Each drSchema In dtSchema.Rows
            strForTextBox = strForTextBox & "Table name = " & drSchema("TABLE_NAME") & vbCrLf
        Next
 
        TextBox1.Text = strForTextBox
 
        cn.Close()
        cn.Dispose()
    End Sub

[techgnome]

Wow... does that really work? If so I jsut might have to *cough* borrow that for an app I'm working on.... certanly would make things easier since I wouldn't need to use the Excel model (thus making Office install manditory).

TG

[CyberHawke]

Yeah, it works great, I have done a lot of work with excel spreadsheets in some side projects I have worked on. Hold on a sec because this just returns the schema, I'm working on code that also selects an individual sheet in a file and uses that as a datasource.

[CyberHawke]

Updated code for opening a specific sheet in your .xls:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dtSchema As DataTable
        Dim drSchema As DataRow
        Dim strForTextBox As String
        Dim strTableName As String
        Dim strSelect As String = "SELECT * FROM [{0}]"
        Dim da As New OleDbDataAdapter
        Dim ds As New DataTable
        Dim fDlg As New OpenFileDialog
        fDlg.Filter = "Excel Spreadsheets (*.xls)|*.xls"
        fDlg.ShowDialog()
        If fDlg.FileName.Length > 0 Then
            Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                  "Data Source=" & fDlg.FileName & _
                                                  ";Extended Properties=Excel 8.0;")
 
            cn.Open()
            dtSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
 
            'DataGrid1.SetDataBinding(dtSchema, "")
            drSchema = dtSchema.Rows(0)
            strTableName = drSchema("TABLE_NAME")
            Dim cmd As New OleDbCommand(String.Format(strSelect, strTableName))
            cmd.Connection = cn
            da.SelectCommand = cmd
            da.Fill(ds)
            'For Each drSchema In dtSchema.Rows
            '    strForTextBox = strForTextBox & "Table name = " & drSchema("TABLE_NAME") & vbCrLf
            'Next
            DataGrid1.DataSource = ds
 
            'TextBox1.Text = strForTextBox
 
            cn.Close()
            cn.Dispose()
        End If
    End Sub

[thurst0n]

wow thanks a million man!! I'll go try it out!! however i don't feel comfortable using code that I don't understand, and I don't understand most of this code! I understand if you don't want to, but if you would be willing to kinda explain what is happening i would be MORE than grateful!! Also say what each line does, as i may want to change it to fit my own needs, and I can't do that if I don't know why it works, or what each line does!

But thanks a million!!

[thurst0n]

I get a build error, and a runtime error error...

An unhandled exception of type 'System.InvalidCastException' occurred in Excel.exe

Additional information: Specified cast is not valid.

I get that error with this line

 dtSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

My build error comes here

        Dim ds As New DataTable

"New cannot be used on an interface."


Any guidance again is helpful...:-( I really wish i understood this code, and btw, i had to add Imports System.data.oledb to make it work, things like that are nice to know b4 i see bunches of blue lines...

[CyberHawke]

Yep, sorry, I was in a hurry and just cut this code out of a project I am working on... well, I did add the file dialog code before I sent it.

I'll comment it now and send it out again with explanations on what is going on there.

[CyberHawke]

Here is the code with comments. I tested it again and it works fine here. If you are having a problem getting it to work, let me know which line it is breaking on and perhaps I can debug the problem.

'Imports Statements
Imports System
Imports System.Data
Imports System.Data.OleDb
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' The declaration lines should be self explanatory
        Dim daExcelData As New OleDbDataAdapter
        Dim dtFirstTable As New DataTable
        Dim drSchema As DataRow
        Dim dtSchema As DataTable
        Dim strForTextBox As String
        Dim strTableName As String
        Dim strSelect As String = "SELECT * FROM [{0}]"
        Dim fDlg As New OpenFileDialog
 
        ' Clear the current contents of the datagrid
        DataGrid1.DataSource = Nothing
        ' Set the filter for the file dialog to show only spreadsheet files,
        ' could be modified to include csv files also, but I don't need it.
        fDlg.Filter = "Excel Spreadsheets (*.xls)|*.xls"
        ' Show the file open dialog
        fDlg.ShowDialog()
        ' Make sure the user didn't click cancel...
        If fDlg.FileName.Length > 0 Then
            ' Declare the connection string, every section of this line is critical or the spreadsheet won't open
            Dim cnExcelData As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                   "Data Source=" & fDlg.FileName & _
                                                   ";Extended Properties=Excel 8.0;")
            ' I hope this is self explanatory
            cnExcelData.Open()
            ' Read the database schema into a DataTable
            dtSchema = cnExcelData.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
            ' Get the first row in the table which will equate to the first sheet in the workbook
            drSchema = dtSchema.Rows(0)
            ' Get the name of the first sheet
            strTableName = drSchema("TABLE_NAME")
            ' create a command object to select the data from the first sheet in the workbook
            Dim cmd As New OleDbCommand(String.Format(strSelect, strTableName))
            ' I hope the following lines are self explanatory
            cmd.Connection = cnExcelData
            daExcelData.SelectCommand = cmd
            daExcelData.Fill(dtFirstTable)
            DataGrid1.DataSource = dtFirstTable
            cnExcelData.Close()
            cnExcelData.Dispose()
        End If
    End Sub

The above code has served me well for the past few years, I have a client that insists on using spreadsheets for storage containers for certain data types because he knows how to manipulate the data in the sheets. I attempted to explain that he could just pull data from the database and insert it into the sheet when he needed, but alas....

I hope this serves you as well as it has me

[thurst0n]

'New' cannot be used on an interface.

I still get this error with this line of code...

Dim dtFirstTable As New DataTable

I am also getting the -- An unhandled exception of type 'System.InvalidCastException' occurred in Excel.exe

Additional information: Specified cast is not valid.

from

dtSchema = cnExcelData.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

I changed the first error to DataSet, and it didnt give me any build errors..I think i may be missing something key, and i'll be like oh duh, but i just dont see it

Thanks for documentation


Also if you wouldn't mind telling me if i need anythign else on my form besides the datagrid, and the OpenFileDialog(not actually on form)

Thanks for your help so far!!


I feel like a leeching n00b...

[CyberHawke]

'New' cannot be used on an interface.

I still get this error with this line of code...

Dim dtFirstTable As New DataTable

I don't receive this error with this code and it is directly from a working project I use. What version of VB.NET are you using?

[CyberHawke]

Here is an example project that I put that code and and tested, as my tagline says, it works on my machine

[thurst0n]

Well yours works! thanks, i wonder why mine doesnt...i did it exactly wht you siad, i coppied and pasted...I sitll dont' understand what's going on though...i wish i did, i dont really feel comfortable using it...

you've done quite enough so far but theres 1 more step, how do i USE this information, how do i get it when i need it...just an example of the assignment of one of the values would be great

Thanks again!!

[CyberHawke]

What parts don't you understand, are you not familiar with ADO.NET, or is it SQL in general. This code couldn't be simpler. It simply establishes a connection to an Excel spreadsheet using the Jet OLEDB driver and then reads the schema from the workbook. For each sheet that is contained in the workbook, you can treat it as a database IF, it is formatted in a way that can be interpreted as an actual database. If you have five levels of headers, eg. World, Country, State, Locality, Address, it wouldn't know how to parse the data, you could end up with almost anything.

you've done quite enough so far but theres 1 more step, how do i USE this information, how do i get it when i need it...just an example of the assignment of one of the values would be great

What do you mean by this? Are you asking what you can do with the data in the table? If so, you would treat it as any other datasource, you can read it, update it, delete records, populate controls on a form, the possibilities are as endless as your imagination, what is it that you want to do with it?

[thurst0n]

no no, i need to inturpret the data..This data will be from a questionaire so for example

If Question1=2 then
DO THIS
End if

etc etc, of course it will be much much larger
lets say the answer for quetsion 2 is in the slot B1, how would i assign the value of B1 to the string Question1?? or how would i get the single data entry of B1..etc etc. i still feel like i'm not explaining myself verywell


and as to the other thing, I'm not familiar with ADO or SQL i think i get the code, just not as comfortable with it

[CyberHawke]

It would be helpful if I could see an example of the data you have to read

[thurst0n]

sure thing i see your online right now i have both YAhoo and AIM, and MSN can you get on any of those? I knwo i can do all the petty stuff, but i don't know how to get hte program to know what is in a certain spot...

Here is a sampletest, It's a "survey" filled out by 3 people, and they answered 4 questions...So how do i know what Person 1, answered to question 2?

[thurst0n]

or can you put it into an array, i can work with that...but this datagrid is all new to me perhaps if you could point me into the direction of a simple tutorial, nothing deep, just the outskirts

[CyberHawke]

Since you are reading the data into a table, you don't really need the grid, I was just using that to show that you had in fact opened your data and had it in memory. You can think of a table as a two dimentional array with a "LOT" of extras, but baiscally it has columns and rows of data.

[CyberHawke]

Here is an updated version that does not use the grid but uses the values in the table one at a time.