Solved

Populating a ListView from an Excel Sheet

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now