Solved

Populating a ListView from an Excel Sheet

Posted on 2006-07-03
5
1,039 Views
Last Modified: 2008-05-01
I'm having loads of trouble trying to sort this.

I am trying to get my application to read an entire Worksheet from an Excel file and copy that information into a ListView. I have tried using a DataAdapter but that doesnt work properly if the columns in my Excel sheet have both numeric and alphanumeric values.

Can someone give me a code example of how to perform the operation I need. A few people have suggested a DataReader instead but I'm not clear on how to set them up or use them.

Thanks lots.
0
Comment
Question by:kiranboi
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17031201
So what is wrong with the code at

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21902125.html#17030138

Did you try it?  If so did it produce an error?  If so, what was it?  If it didn't produce an error, in what way did if fail to meet your needs?

We'd really like to help you but it's very difficult to do so if don't give us the feedback when we offer suggestions that look as though they should work.

Roger
0
 

Author Comment

by:kiranboi
ID: 17031615
Thanks Roger,

Ive had another go with the code from your previous post. Im not sure how to set up the table with the right columns and datatypes etc. Bearing in mind the source sheet will be changing so the app needs to do this dynamically
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17031837
Here's a revised version of it

    Private Function FillTextTableFromExcel(ByVal sourceFile As String, ByVal sourceSheet As String) As DataTable

        Dim resulttable As New DataTable
        Dim tablemade As Boolean = False

        If Not sourceSheet.EndsWith("$") Then
            sourceSheet &= "$"
        End If

        Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Dim sourceSQL As String = "SELECT * FROM [" & sourceSheet & "]"
        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        Dim sourceReader As OleDbDataReader

        sourceCon.Open()
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read()
            'This only runs once - taking the necessary information from the first record read
            If Not tablemade Then
                For i As Integer = 0 To sourceReader.FieldCount - 1
                    Dim dc As New DataColumn
                    dc.ColumnName = sourceReader.GetName(i) 'This line should go if the excel sheet does not have headers.
                    dc.DataType = GetType(String)
                    resulttable.Columns.Add(dc)
                Next
                tablemade = True
            End If
            Dim dr As DataRow = resulttable.NewRow
            For i As Integer = 0 To sourceReader.FieldCount - 1
                dr(i) = CStr(sourceReader(i))
            Next
            resulttable.Rows.Add(dr)
        End While
        sourceReader.Close()
        sourceCon.Close()
        Return resulttable

    End Function

It's now a function, rather than a sub, and it returns a datatable of the right structure (but with all columns of datatype String).  You would call it like this

    Dim myDataTable As DataTable = FillTextTableFromExcel(<yoursourcefile>, <yoursourcesheet>)

I should stress that there are lots of other (and more elegant) ways to build the table on the fly, but I've adopted the above because it is most consistent with the code/approach already adopted.

The same comments as before about modifying the connection string and about nulls are still applicable.  If those are giving you problems, then don't hesitate to ask for further clarification.

Roger
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17033160
It occurred to me that we (or at least I) may have been sidetracked into datatables.  It may be that you actually want a datatable.  But if it is only a step towards your real aim, of filling a ListView, and you have no other use for it as a datatable, then the right way to go might be to fill the ListView directly from Excel with a datareader, dispensing with the intermediate step of a datatable.  Here's a further revision of the sub with that in mind.

    Private Sub FillListViewFromExcel(ByVal sourceFile As String, ByVal sourceSheet As String, ByVal lv As ListView)

        Dim columnsmade As Boolean = False

        If Not sourceSheet.EndsWith("$") Then
            sourceSheet &= "$"
        End If

        Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Dim sourceSQL As String = "SELECT * FROM [" & sourceSheet & "]"
        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        Dim sourceReader As OleDbDataReader

        sourceCon.Open()
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read()
            If Not columnsmade Then
                For i As Integer = 0 To sourceReader.FieldCount - 1
                    lv.Columns.Add(sourceReader.GetName(i))
                Next
                columnsmade = True
            End If
            Dim lvi As New ListViewItem
            If TypeOf sourceReader(0) Is DBNull Then
                lvi.Text = "[Null]"
            Else
                lvi.Text = CStr(sourceReader(0))
            End If
            For i As Integer = 1 To sourceReader.FieldCount - 1
                If TypeOf sourceReader(i) Is DBNull Then
                    lvi.SubItems.Add("[Null]")
                Else
                    lvi.SubItems.Add(CStr(sourceReader(i)))
                End If
            Next
            lv.Items.Add(lvi)
        End While
        sourceReader.Close()
        sourceCon.Close()
    End Sub

You would call it like this

   FillListViewFromExcel(<yoursourcefile>, <yoursourcesheet>, <yourlistview>)

leaving the listview's columns collection blank - as the sub creates those from the Excel column names.  And I've added some checks for Nulls.

Roger
0
 

Author Comment

by:kiranboi
ID: 17035486
Roger,

That's absolutely fantastic !! That's exactly what I needed.  The only change I had to make was swapping
lv.Add(sourceReader.GetName(i))
with
lv.Add(sourceReader.GetName(i), 80, HorizontalAlignment.Left)
otherwise I got an error saying 'String' couldn't be converted to type 'System.ColumnHeader'

Thanks again !!
Kiran
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question