Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Populating a ListView from an Excel Sheet

Posted on 2006-07-03
5
Medium Priority
?
1,046 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 2000 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

Industry Leaders: 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

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…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

596 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