[RESOLVED] Load data into Datagridview

Dear all,
I'm trying to import data from an excel file into a windowsforms and specifically here into a datagridview.
I already set up the datagridview in my windowsform and I have a specific range in excel from where I should get the populated cells.

I've tried with two different codes but anyone of them is giving me any result:

Code 1:

Code:

---

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class Form14
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlAppP As Excel.Application
        Dim xlWBP As Excel.Workbook
        Dim xlWSP As Excel.Worksheet
        Dim fNameP As String

        If OpenFileDialog1.ShowDialog = DialogResult.OK Then
            TextBox1.Text = OpenFileDialog1.FileName
        End If
        fNameP = TextBox1.Text

        xlAppP = New Excel.Application
        Try
            xlWBP = xlAppP.Workbooks.Open(fNameP, [ReadOnly]:=True)
        Catch ex As Exception
            Stop
        End Try

        xlWSP = xlWBP.Worksheets(1)
        xlAppP.Visible = False

Dim loadarrayMarc(,) As Object = xlWSP.Range("C24:E29").Value
        For i As Integer = 0 To 1
                    For j As Integer = 0 To 1
                If i = 0 Then
                    DataGridView1.Rows.Add()
                End If
                DataGridView1.Rows(j).Cells(i).Value = loadarrayMarc(i, j)
            Next
        'Next

---

Code 2:

Code:

---

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class Form14
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlAppP As Excel.Application
        Dim xlWBP As Excel.Workbook
        Dim xlWSP As Excel.Worksheet
        Dim fNameP As String

        If OpenFileDialog1.ShowDialog = DialogResult.OK Then
            TextBox1.Text = OpenFileDialog1.FileName
        End If
        fNameP = TextBox1.Text

        xlAppP = New Excel.Application
        Try
            xlWBP = xlAppP.Workbooks.Open(fNameP, [ReadOnly]:=True)
        Catch ex As Exception
            Stop
        End Try

        xlWSP = xlWBP.Worksheets(1)
        xlAppP.Visible = False

Dim loadarrayMarc(,) As Object = xlWSP.Range("C24:E29").Value
 DataGridView1.DataSource = loadarrayMarc

---

Please note that not all the cells in the range C24:E29 could be populated, and also that in the excel source columns C and D are merged.

Could you please help me?

Thanks,
A.

Debug your code, i.e. set a breakpoint and step through it line by line, and explain EXACTLY where and how the behaviour of the code deviates from your expectation.

Dim loadarrayMarc(,) As Object = xlWSP.Range("C24:E29").Value

I'm fairly sure that doesn't work how you're expecting it to...

The first thing I would do is change this,

Code:

---

        Catch ex As Exception
            Stop
        End Try

---

If an exception is thrown then you need to know what the problem is,

Code:

---

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

---

Dear all,
thanks for all your suggestions.
So, I inserted Try, Catch as Exception statement in both my options and:

1. I set a breakpoint in the loop code at the statement Datagridview1.Rows(j).Cells(i).value etc. I checked the array variable and it is not initialized in a proper way as it has lenght of 18 (while it should be 12 - 6 rows x 2 columns) and the values from excel on indexes (1,1) and (1,3) while it should have been them on (1,1) and (1,2). Furthermore at the end of the execution I got the following error: "System.IndexOutOfRangeException: Index was outside the bounds of the array";

2. If I go with the option of datasource, I get the following error: "System.ArgumentException: Array was not a one-dimensional array. at System.Array.GetValue(Int32 index), at System.Array.System.Collections.IList.get.Item(Int32 index), at System.Windows.Forms.CurrencyManager.get_Item(Int32 index), at System.Windows.Forms.DataGridview.DataGridViewDataConnection.GetError(int32 rowIndex)".

Please let me know if you need any further detail to help me in understanding how I solve this.

A.

Loop through the WorkSheet.Cells at the correct indices instead of trying to grab a range that way...

That was a nice explanation but anytime you've modified your code, you should post the current version of the relevant code. It just makes it easier for us to see what's actually going on.

Dear all,
sure you're right.
Here's below the code I came up with. It works fine except for a system exception retrieved that I'm reporting here for your suggestion: "System.NullReferenceException: Object reference not set to an instance of an object". The line the message refers to is the if one.

What does it mean? How can I correct it? From what I understand it happens when the excel cell is empty. Am I right?

Code:

---

Dim loadarrayGMarc(,) As Object = xlWSP.Range("C24:C29").Value
        Dim loadarrayMMarc(,) As Object = xlWSP.Range("E24:E29").Value
        Try
            For i = 1 To 5
                If loadarrayGMarc(i, 1).ToString <> "" Then
                    DataGridView1.Rows.Add()
                    DataGridView1.Rows(i - 1).Cells(0).Value = loadarrayGMarc(i, 1)
                    DataGridView1.Rows(i - 1).Cells(1).Value = loadarrayMMarc(i, 1)
                End If
            Next
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

---

Instead of...

If loadarrayGMarc(i, 1).ToString <> "" Then

Use...

If StringIsNullOrEmpty... (not sure of the exact syntax at my phone atm)

Don't use the ToString, just use the array....

Dear .paul,
thanks a lot: it works! This is the code:

Code:

---

Dim loadarrayGMarc(,) As Object = xlWSP.Range("C24:C29").Value
Dim loadarrayMMarc(,) As Object = xlWSP.Range("E24:E29").Value
        Try
            For i = 1 To 5
                If loadarrayGMarc(i, 1) IsNot Nothing Then
                    DataGridView1.Rows.Add()
                    DataGridView1.Rows(i - 1).Cells(0).Value = loadarrayGMarc(i, 1)
                    DataGridView1.Rows(i - 1).Cells(1).Value = loadarrayMMarc(i, 1)
                End If
            Next
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

---

Before setting the thread as Resolved, please let me know if there is anything else from a syntax point of view that I should add in the code.

Thanks again,
A.

another option is to use OLEDB

here a sample that will use a Listview to display

Code:

---

Option Strict On
Imports System.Data.OleDb

Public Class Form1
    Private objConnection As OleDbConnection
    Private objCommand As OleDbCommand
    Private objDataAdapter As OleDbDataAdapter
    Private objDataTable As System.Data.DataTable

    Private PathExcelFile As String = "E:\Book1.xlsx"
    Private strConnectionString As String = _
    "provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & PathExcelFile & ";" _
    & "Extended Properties=""Excel 12.0 XML;HDR=Yes"""



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        objConnection = New OleDbConnection(strConnectionString)

        'select sheet
        'objCommand = New OleDbCommand("SELECT * FROM [Sheet4$]", objConnection)

        'select Range
        objCommand = New OleDbCommand("SELECT * FROM [Sheet4$A1:B10]", objConnection)

        'select with Letter, AD1 and AD2 are Header names in the Excel Sheet
        'objCommand = New OleDbCommand("SELECT AD1, AD2 FROM [Sheet4$] Where AD2 Like 'Q%'", objConnection)

        objDataAdapter = New OleDbDataAdapter
        objDataAdapter.SelectCommand = objCommand
        objDataTable = New System.Data.DataTable
        objDataAdapter.Fill(objDataTable)
        ListViewFillFromDataTable(ListView1, objDataTable)

        'Clean(up)
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Dispose()
        objConnection = Nothing
    End Sub


    Public Sub ListViewFillFromDataTable(ByVal Lvw As ListView, ByVal Dt As System.Data.DataTable, _
                                          Optional ByVal UseItemStyleForSubItems As Boolean = False, _
                                          Optional ByVal ColumnsAutoSize As Boolean = True, _
                                          Optional ByVal FormatDecimal As String = "0.00")

        With Lvw
            .View = View.Details
            .GridLines = True
            .HideSelection = False
            .FullRowSelect = True
            .LabelEdit = False
            .Items.Clear()
            .Columns.Clear()
            'Header
            For i As Integer = 0 To Dt.Columns.Count - 1
                Dim Key As String = Dt.Columns(i).ColumnName
                .Columns.Add(Key)
                Select Case Dt.Columns(i).DataType.Name
                    Case "String", "DateTime", "Char", "Boolean"
                    Case Else
                        .Columns(i).TextAlign = HorizontalAlignment.Right
                End Select
            Next
            .BeginUpdate()
            .SuspendLayout()
            'fill Listview
            For i As Integer = 0 To Dt.Rows.Count - 1
                Dim Li As New ListViewItem
                Dim Row As DataRow = Dt.Rows(i)
                Li.Tag = Row
                Li.UseItemStyleForSubItems = UseItemStyleForSubItems
                For j As Integer = 0 To Row.ItemArray.GetUpperBound(0)
                    Dim s As String = Nothing
                    If Not Row.IsNull(j) Then
                        s = CStr(Row.Item(j))
                        If Row.Item(j).GetType.Name = "Decimal" Then
                            If Not String.IsNullOrEmpty(FormatDecimal) Then
                                s = Convert.ToDecimal(s).ToString(FormatDecimal)
                            End If
                        End If
                    End If
                    If j = 0 Then
                        Li.Text = s
                    Else
                        Li.SubItems.Add(s)
                    End If
                Next
                .Items.Add(Li)
            Next

            If ColumnsAutoSize Then
                .AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize)
            End If
            .ResumeLayout()
            .EndUpdate()
        End With
    End Sub

---

hth

Have you considered OleDb

Code:

---

Public Function GetRange(ByVal SheetName As String, ByVal Range As String) As DataTable
        Dim dt As New DataTable

        Using cn As New OleDbConnection With {.ConnectionString = Connection.NoHeaderConnectionString(FileName)}
                cn.Open()

                Dim cmd As OleDbCommand = New OleDbCommand(
                        <Text>
                                        SELECT * FROM [<%= SheetName %>$<%= Range %>]
                                </Text>.Value,
                        cn
                )

                dt.Load(cmd.ExecuteReader)


        End Using

        Return dt

End Function

---

Usage

Dim dt As DataTable = GetRange("Sheet1", "C24:E29")

Have you considered OleDb

Code:

---

Public Function GetRange(ByVal SheetName As String, ByVal Range As String) As DataTable
        Dim dt As New DataTable

        Using cn As New OleDbConnection With {.ConnectionString = Connection.NoHeaderConnectionString(FileName)}
                cn.Open()

                Dim cmd As OleDbCommand = New OleDbCommand(
                        <Text>
                                        SELECT * FROM [<%= SheetName %>$<%= Range %>]
                                </Text>.Value,
                        cn
                )

                dt.Load(cmd.ExecuteReader)


        End Using

        Return dt

End Function

---

Usage

Dim dt As DataTable = GetRange("Sheet1", "C24:E29")