Subscribe to andrewsw's Blog        RSS Feed
-----

Bung Excel Into ListView

Icon Leave Comment
I don't know why one would want Excel data in a ListView, but it seems a shame to do so cell-by-cell. In particular, I was hoping to take advantage of SubItems.AddRange to at least add one row of Excel data at a time.

UsedRange will reference the table of Excel data and (not so many people know) an entire Excel range can be fed to an Object(,). I've never truly worked out how to take advantage of this, but I'm always willing to give it another go.

        Dim theData As Object(,)
        theData = xlRange.Value

An important thing to note is that the array indexing starts at 1 rather than 0.

Unfortunately, the closest we can come to grabbing a single row from a two-dimensional array is this:

            lvItem.SubItems.AddRange(Enumerable.Range(2, theData.GetUpperBound(1) - 1) _
                                     .Select(Function(colNum) CType(theData(tempRow, colNum), String)).ToArray())

This looks like it is just grabbing a row, but it is still an iterable process.

Anyway, here is the result of my current labours:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        lvExcel.View = View.Details
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open("C:\Users\Andrew\Documents\Book2.xlsx")
        Dim xlSheet As Excel.Worksheet = CType(xlBook.Worksheets(1), Excel.Worksheet)   'or use worksheet-name

        Dim xlRange As Excel.Range = xlSheet.UsedRange

        Dim theData As Object(,)
        theData = xlRange.Value

        For c As Integer = 1 To theData.GetUpperBound(1)
            lvExcel.Columns.Add(theData(1, c).ToString())
        Next

        For r As Integer = 2 To theData.GetUpperBound(0)
            Dim lvItem As New ListViewItem(theData(r, 1).ToString())    'the StaffNo

            Dim tempRow = r     'advisable for use with lambda
            lvItem.SubItems.AddRange(Enumerable.Range(2, theData.GetUpperBound(1) - 1) _
                                     .Select(Function(colNum) CType(theData(tempRow, colNum), String)).ToArray())
            lvExcel.Items.Add(lvItem)
        Next

        xlBook.Close(False)
        xlApp.Quit()
        ReleaseObject(xlRange)
        ReleaseObject(xlSheet)
        ReleaseObject(xlBook)
        ReleaseObject(xlApp)
    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        'http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class


Posted Image

Well, I suppose the fact that switching to List view (rather than Details) will display all the StaffNo's offers a different perspective of the data.

0 Comments On This Entry

 

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

October 2017

S M T W T F S
1234567
891011121314
15161718192021
22 232425262728
293031    

Tags

    Recent Entries

    Recent Comments

    Search My Blog

    2 user(s) viewing

    2 Guests
    0 member(s)
    0 anonymous member(s)

    Categories