Using listbox to display an excel file?

Question

So after some changes, I have used this code:

Dim XlApp = New Microsoft.Office.Interop.Excel.Application
Dim oBook As Object = XlApp.Workbooks.Open("C:\file.xlsx")
Dim oSheet As Object = oBook.Worksheets(1)

Private Sub form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim StartedRow As Integer
    Dim TotalRows As Integer

    TotalRows = XlApp.ActiveWorkbook.Sheets(1).Range("a1").CurrentRegion.Rows.Count

    For StartedRow = 1 To TotalRows
        Me.ListBox1.Items.Add(oSheet.Cells(StartedRow, 1).text)
        Me.ListBox1.Items.Add(oSheet.Cells(StartedRow, 2).text)
    Next

    MessageBox.Show("Succesful")

This one works but only shows two rows which is not in proper order, I need to show the whole file. Sorry, since I'm new to Stack Overflow I find it kinda confusing for now :)

Answer 1

You need to declare your variables as specific types so you can use the properties and methods of that type. In general, don't let variables flap about as Object unless absolutely necessary.

I used .UsedRange to get row count. It seemed a bit simpler.

I used an interpolated string, indicated by the $, to get the range. In versions of Visual Studio prior to 2015 you will have to use

String.Format("A{0}", RowNum)

Excel is hard to get rid of. Therefore, put the Excel stuff in a separate method and add the two GC calls when the method returns.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    DoExcelThing()
    GC.Collect()
    GC.WaitForPendingFinalizers()
    'Even doing this will still leave a Zoombie Excel in Task Manager when debugging. (Hopefully not release)
    MessageBox.Show("Succesful")
End Sub

Private Sub DoExcelThing()
    Dim XlApp As New Microsoft.Office.Interop.Excel.Application
    Dim oBook As Workbook = XlApp.Workbooks.Open("C:\file.xlsx")
    Dim oSheet As Worksheet = CType(oBook.Worksheets(1), Worksheet)
    Dim TotalRows = oSheet.UsedRange.Rows.Count
    For RowNum = 1 To TotalRows
        ListBox1.Items.Add(oSheet.Range($"A{RowNum}").Value)
        ListBox1.Items.Add(oSheet.Range($"B{RowNum}").Value)
    Next
End Sub

Answer 2

@Wakashio1234 This Will loop through all the cells..

TotalRows = XlApp.ActiveWorkbook.Sheets(1).Range("a1").CurrentRegion.Rows.Count
Totalcolumns = XlApp.ActiveWorkbook.Sheets(1).Range("a1").CurrentRegion.columns.Count
For StartedRow = 1 To TotalRows
   For Startedcolumn = 1 To Totalcolumns
       Me.ListBox1.Items.Add(oSheet.Cells(StartedRow, Startedcolumn).text)
   Next
Next
MessageBox.Show("Successful")

Answer 3

The following code shows how to take out the data in Excel and put it into 'List(Of String())', then bind listbox control to the List and draw aligned columns of data in listbox.

Dim XlApp = New Microsoft.Office.Interop.Excel.Application
Dim oBook As Excel.Workbook = XlApp.Workbooks.Open("your file path")
Dim oSheet As Excel.Worksheet = oBook.Worksheets(1)
Dim lst As List(Of String()) = New List(Of String())()
Private RowHeight, RowWidth As Single
Private ColWidths As Single() = Nothing
Private Const RowMargin As Single = 10
Private Const ColumnMargin As Single = 10

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ListBox1.DrawMode = DrawMode.OwnerDrawVariable
    ListBox1.DataSource = GetList()
End Sub

Private Function GetList() As List(Of String())
    Dim rows As Integer = oSheet.UsedRange.Rows.Count
    'If you want to display only two columns, set the value of 'cols' to 2
    Dim cols As Integer = oSheet.UsedRange.Columns.Count

    For r As Integer = 1 To rows
        Dim Value As String() = New String(cols - 1) {}
        For c As Integer = 1 To cols
            Value(c - 1) = oSheet.Cells(r, c).Text
        Next
        lst.Add(Value)
    Next
    oBook.Close()
    XlApp.Quit()
    Return lst
End Function

Private Sub ListBox1_MeasureItem(sender As Object, e As MeasureItemEventArgs) Handles ListBox1.MeasureItem
    If ColWidths Is Nothing Then
        GetRowColumnSizes(e.Graphics, ListBox1.Font, lst, RowHeight, ColWidths)
        For i As Integer = 0 To ColWidths.Length - 1
            ColWidths(i) += ColumnMargin
        Next

        RowHeight += RowMargin
        RowWidth = ColWidths.Sum()
    End If

    e.ItemHeight = CInt(RowHeight)
    e.ItemWidth = CInt(RowWidth)
End Sub

Private Sub ListBox1_DrawItem(sender As Object, e As DrawItemEventArgs) Handles ListBox1.DrawItem
    Dim values As String() = CType(ListBox1.Items(e.Index), String())
    e.DrawBackground()

    If (e.State And DrawItemState.Selected) = DrawItemState.Selected Then
        DrawRow(e.Graphics, ListBox1.Font, SystemBrushes.HighlightText, Nothing, e.Bounds.X, e.Bounds.Y, RowHeight, ColWidths, values, False)
    Else
        DrawRow(e.Graphics, ListBox1.Font, Brushes.Black, Nothing, e.Bounds.X, e.Bounds.Y, RowHeight, ColWidths, values, False)
    End If
End Sub

Private Sub GetRowColumnSizes(ByVal gr As Graphics, ByVal font As Font, ByVal values As List(Of String()), ByRef max_height As Single, ByRef col_widths As Single())
    Dim num_cols As Integer = values(0).Length
    col_widths = New Single(num_cols - 1) {}
    max_height = 0

    For Each row As String() In values
        For col_num As Integer = 0 To num_cols - 1
            Dim col_size As SizeF = gr.MeasureString(row(col_num), font)
            If col_widths(col_num) < col_size.Width Then col_widths(col_num) = col_size.Width
            If max_height < col_size.Height Then max_height = col_size.Height
        Next
    Next
End Sub

Private Sub DrawRow(ByVal gr As Graphics, ByVal font As Font, ByVal brush As Brush, ByVal box_pen As Pen, ByVal x0 As Single, ByVal y0 As Single, ByVal row_height As Single, ByVal col_widths As Single(), ByVal values As String(), ByVal draw_box As Boolean)
    Dim rect As RectangleF = New RectangleF()
    rect.Height = row_height
    Using sf As StringFormat = New StringFormat()
        Dim x As Single = x0
        For col_num As Integer = 0 To values.Length - 1
            sf.Alignment = StringAlignment.Near
            sf.LineAlignment = StringAlignment.Center
            rect.X = x
            rect.Y = y0
            rect.Width = col_widths(col_num)
            gr.DrawString(values(col_num), font, brush, rect, sf)
            If draw_box Then gr.DrawRectangle(box_pen, rect.X, rect.Y, rect.Width, rect.Height)
            x += col_widths(col_num)
        Next
    End Using
End Sub

Result of my test:

For more details you can see: Make an owner-drawn ListBox that justifies columns in C#