Solved

Populating a ListView from an Excel Sheet

Posted on 2006-07-03
5
1,040 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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