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.
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:
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:

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.
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

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 ]
← January 2021 →
S | M | T | W | T | F | S |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
31 |
Tags
My Blog Links
Recent Entries
Recent Comments
Search My Blog
1 user(s) viewing
1 Guests
0 member(s)
0 anonymous member(s)
0 member(s)
0 anonymous member(s)