Selecting Data From an Excel file

Z

ZeroEffect

Well-known member
Joined
Oct 24, 2004
Messages
180
Location
Detroit, MI
Thanks For stopping by, Here is what I am doing.

I want to take an excel file grab some data from it and then do some other things with that data then export it as a new excel sheet.

I am able to load the whole excel file into a dataset which is great but I dont want all the data that is in the excel file. But As of right now I have not found what expresion to use to find the data I want.

Here is the code I am using to load the file.

Code: 
        Dim DBSet As System.Data.DataSet
        Dim DBCommand As System.Data.OleDb.OleDbDataAdapter
        Dim DBCon As System.Data.OleDb.OleDbConnection
        Try
            DBCon = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=Analog Report.XLS;Extended Properties=Excel 8.0;")
            DBCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", DBCon)

            DBSet = New System.Data.DataSet
            DBCommand.Fill(DBSet)
            DataGrid1.DataSource = DBSet.Tables.Item(0) ///put the excel info in to a datagrid.
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            DBCon.Close() /// close the connection to excel.
            DBSet = Nothing
            DBCommand = Nothing
        End Try

Ideally I would like to take the column D from row 9 to 40 as new columns rows and column K rows 9 through the end of the file as a value. The name in column D repeat.

and example would be D9 = Tower 1 Ratio, K9 = 0.82

And in a table it would be

Tower 1 Ratio
0.82

And every time Tower 1 Ratio was found in column D it would ad the value to the new table under Tower 1 Ratio.

Is there a way to do this and how. As always Ill be hunting for more info.

Thanks for your help,

ZeroEffect
 
Update

Ok I have made some progress I figured out how to get the rows I wanted.

Code: 
DBCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$D7:K]", DBCon)

I load that into datagrid then I loop through that datagrid for the data I want then I load that into a new datagrid. So now I have a datagrid with two columns one with names and one with readings. My next step is to loop through the first column in the second datagrid and create a data tabe from it. The values in column one repete every so many rows. So durring the loop process Every new value will become a column in the new dataset. Once that is done Ill loop through the second column and place the value in the correct column.

Its progress :)

ZeroEffect
 
Back
Top