Re: Read selected Excel data into DataGridView using VB.NET2008 - Visual Basic Discussion Boards

Workaround: DrawItem for flagged listbox item?

Sonhospa12-Jul-09 2:34

12-Jul-09 2:34

Meanwhile I found a workaround, but it's not very elegant so I just want to inform others about a working solution - and maybe inspire the gurus here to throw some of their 'cheap tricks' on me Wink | ;)

What at least works is:

- I set the listbox's SelectionMode property to 'MultiExtended'
- After processing a file, I set its relevant listbox item to 'selected' in the same loop

Me.lstTargetFiles.SetSelected(Me.lstTargetFiles.Items.IndexOf(fileInfoItem), True)

- In the listbox's DrawItem event I (additionally) set the font for selected items to yellow

If e.State And DrawItemState.Selected Then myBrush = Brushes.Yellow

I'd really be interested in a more sophisticated approach, so please let me know if you have a better idea!

Thanks again
Michael

Re: DrawItem for flagged listbox item?

Henry Minute12-Jul-09 3:46

Henry Minute

12-Jul-09 3:46

One thing that would work for this is to create a separate List (call it processedFilesList) derived from List(Of String) and then file paths/names would be added to that list when they are successfully processed, and removed if necessary when they change. In this new list Override the Add, Insert, Remove etc methods so that you can trigger an event when the list changes. Handle that event in your Form and trigger a repaint. In your lstTargetFiles_DrawItem include something like:

If processedFilesList.Contains((CType(sender, ListBox)).Items(e.Index).ToString())
  myBrush = Brushes.Red
Else
  myBrush = Brushes.Green
EndIf

Hope that helps.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: DrawItem for flagged listbox item?

Sonhospa12-Jul-09 7:25

Sonhospa

12-Jul-09 7:25

WOW - this one, Henry, sounds like an extremely smart and cool idea Thumbs Up | :thumbsup:

how to trick the limitation I faced.

Thank you in advance (without having tested it yet)

Michael

Read selected Excel data into DataGridView using VB.NET2008

drexler_kk11-Jul-09 21:58

drexler_kk

11-Jul-09 21:58

Hello all,I need some help here on reading specified column and row of data from the Excel Sheet. I have manage to read the full sheet into my datagridview now,but what I need is to read specified column and row but not all.I'll try to explain it like below:

Its a table in my excel that the table start at location in the PO Detail spreadsheet of A4 until G4. And thats the Header of the table,the Header name of each column are Product Code, Description , Internal Product Code, Selling Price , Order Quantity, Amount and other.

But currently my code now read all the data into my datagridview which is not convenient for me to do further process with some unuseful details inside the PO Detail excel sheet. Can anyone teach me how could you retrieve only the column and row I need and put into the datagridview?

This is the code I have currently which read everything into my datagridview:

<br />
Imports System.Data<br />
Imports System.Data.OleDb<br />
<br />
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click<br />
        Dim MyConn As System.Data.OleDb.OleDbConnection<br />
        Dim DtSet As System.Data.DataSet<br />
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter<br />
<br />
        Try<br />
            MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\OrderingFormat.xls'; Extended Properties=Excel 8.0;")<br />
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [PO Detail$] ", MyConn)<br />
            MyCommand.TableMappings.Add("Table", "TestTable")<br />
            DtSet = New System.Data.DataSet<br />
            MyCommand.Fill(DtSet)<br />
            DataGridView1.DataSource = DtSet.Tables(0)<br />
            MyConn.Close()<br />
<br />
        Catch ex As Exception<br />
<br />
        End Try<br />
    End Sub<br />

Thank you for reading and hope someone can give me some guidance on this issue.
Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008

dan!sh 11-Jul-09 22:34

dan!sh

11-Jul-09 22:34

You can use the names of the columns. This way:

SELECT ColName1,ColName2,ColName3 FROM [SheetName$]

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk11-Jul-09 22:51

drexler_kk

11-Jul-09 22:51

I have tried it. It doesn't appeared anythings inside my Datagirdview with that. Shows that it return nothing,I'm using Excel 2003 format for my file. Do you have any idea to identify that the data table should retrive date from A4 to G4 as table header and A5 to G5 onwards are the data I need?

Thanks for your help.
Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008

dan!sh 11-Jul-09 23:47

dan!sh

11-Jul-09 23:47

Set HDR=YES in the connection string. Not sure though.

drexler_kk wrote:
Do you have any idea to identify that the data table should retrive date from A4 to G4 as table header and A5 to G5 onwards are the data I need?

Through OleDB, no, apart from using column names. This can very well be done using Interop through Range object.

Re: Read selected Excel data into DataGridView using VB.NET2008

helelark12311-Jul-09 23:52

helelark123

11-Jul-09 23:52

I tried it for you and it works well:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyConn As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Try
MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\1.xls'; Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Qty from [Sheet1$] ", MyConn)
MyCommand.TableMappings.Add("Table", "TestTable")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConn.Close()

Catch ex As Exception

End Try
End Sub

----------------------------------------------------------------------
For example
Excel FullFileName: C:\1.xls
In sheet1 add 3 columns (from A1 to A3):
A1=Qty A2=100 A3=250
B1=Prod B2=A B3=B
C1=ID C2=1 C2=2

I suggest you to do this simple example that works fine and after you get that this example works with no problem try to check where is your problem

Shay Noy

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk12-Jul-09 0:17

drexler_kk

12-Jul-09 0:17

Well,I have try with this code as you suggest below:
<code> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyConn As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Try
MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\TestPharmaExcel.xls'; Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Qty from [Sheet1$] ", MyConn)
MyCommand.TableMappings.Add("Table", "TestTable")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)

End Try

And it promped me an error: No value given for one or more required parameter. What should I do to this?
Anyone can give me some idea?

Thanks for reading.

Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008

Paramu197312-Jul-09 0:29

Paramu1973

12-Jul-09 0:29

Iam using like the following Thumbs Up | :thumbsup:

ExcelFile="C:\temp\My_test.xls"

Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ExcelFile & "; Extended Properties=""Excel 8.0; HDR=Yes; IMEX=1"""

Dim excelsheet As String = "[Sheet1$]"
Dim sql As String = "SELECT * FROM " & excelsheet

Try
Using cn As New OleDb.OleDbConnection
cn.ConnectionString = strConn
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, cn)
cn.Open()
da.Fill(items)
cn.Close()
End Using

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk12-Jul-09 0:38

drexler_kk

12-Jul-09 0:38

Sorry,I'm not going to read all the Sheet1 information into my datagirdview,because I doesn't need the additional data inside the sheet1. I would like to just retrieve the table data into my DataGridView. My 1st row header column for the table in my Excel sheet are A4:G4, the details of each row of data are from A5:G5. Can I know how to do this retrieving of data into my DataGridView?

Hope someone can guide me better. Thank you~!

Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008 [modified]

helelark12312-Jul-09 0:49

helelark123

12-Jul-09 0:49

At which line of code it gave you the error?

Shay Noy

modified on Sunday, July 12, 2009 6:55 AM

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk12-Jul-09 0:54

drexler_kk

12-Jul-09 0:54

Well..The Error appeared at this line>> MyCommand.Fill(DtSet)

What might be the problem?
Thanks ~!

Re: Read selected Excel data into DataGridView using VB.NET2008

helelark12312-Jul-09 0:56

helelark123

12-Jul-09 0:56

I made a simulation by changing the name of the Qty column in my excel file and I got the same error message as you got.

The problem is that you do not have a Qty column in your excel file.
You have to change the "Select Qty" in the query to "Select YourNameField"

Shay Noy

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk12-Jul-09 1:21

drexler_kk

12-Jul-09 1:21

I do have a Qty column located at A3 cell here at Sheet1 actually. But I don't know why can cause this problem. My data is start at A4 column,I cannot put my Qty column to A1 because there is other data in A1 field in my Excel. Do you have any idea how should solve this problem?

Thanks

Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008

helelark12312-Jul-09 1:24

helelark123

12-Jul-09 1:24

Have you got data in A1, A2 lines?
If yes you must leave those lines blank.
Try it and reply

Shay Noy

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk12-Jul-09 1:42

drexler_kk

12-Jul-09 1:42

No,it was blank field for A1 and A2 field here actually. My table start with A3 actually. Do you have any other way beside this? Is that Interop can help to solve this problem? Do you have sample for using interops?

Thanks~!
Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008

helelark12312-Jul-09 1:45

helelark123

12-Jul-09 1:45

I have another way, if you want, by linking the excel table to MS Access and then using access table to continue you project.
But, let's try to understand what you problem is.
If you want send me your excel file by email (helelark@gmail.com) and the button1 code too, I will check and re-send it to you.

Just tell me if you send it too me by email so I will check my email

Shay Noy

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk12-Jul-09 2:00

drexler_kk

12-Jul-09 2:00

I just email you my Excel file. Hope you can check it for me. Thank you~!

Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008

helelark12312-Jul-09 2:10

helelark123

12-Jul-09 2:10

Just remove the 2 first rows, A1 and A2 and it will works fine

Shay Noy

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk12-Jul-09 2:15

drexler_kk

12-Jul-09 2:15

Yes,but the problem is I must get it work with A4 as the row header. Because I need to grab the data from the OrderingFormat.xls file I have send to you. If I start with A1 as the row header,it won't works in my project. Do you have any other ideas?

Thank you.

Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008

helelark12312-Jul-09 2:17

helelark123

12-Jul-09 2:17

Sorry, I saw the second file you sent me only after sending you this reply. I have checked in google and the solution is as following:

select Qty from [Sheet1$A3:C6]...
A3:first column,first row
C6:last column,last row

Good luck

Shay Noy

Re: Read selected Excel data into DataGridView using VB.NET2008

drexler_kk12-Jul-09 2:25

drexler_kk

12-Jul-09 2:25

Yes,I got the solution now. Thank you so much for your help on this issue Mr. Eric Hilel. I will solve the problem here. Thanks alot for your kind effort.

Regards
Drex

Re: Read selected Excel data into DataGridView using VB.NET2008

helelark12312-Jul-09 2:28

helelark123

12-Jul-09 2:28

You are welcome

Shay Noy

Network Messages?

Paramu197311-Jul-09 21:34

Paramu1973

11-Jul-09 21:34

I would like to learn sending the messages inside our LAN like a chat...or Just sending the messages enough.

I came to know Tcpclient & Tcplistener can do that... vb.net 2008

Any helps will be really appreciated...

Thanks Thumbs Up | :thumbsup:

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.