Solved

Populating a ListView from an Excel Sheet

Posted on 2006-07-03
5
1,038 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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