Results 1 to 14 of 14

Thread: [RESOLVED] Load data into Datagridview

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    90

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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Load data into Datagridview

    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.

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Load data into Datagridview

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

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

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Load data into Datagridview

    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

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    90

    Re: Load data into Datagridview

    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.

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Load data into Datagridview

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

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Load data into Datagridview

    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.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    90

    Re: Load data into Datagridview

    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

  9. #9
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Load data into Datagridview

    Instead of...

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

    Use...

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

  10. #10
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Load data into Datagridview

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

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Sep 2018
    Posts
    90

    Re: Load data into Datagridview

    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.

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Load data into Datagridview

    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Load data into Datagridview

    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")

  14. #14
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Load data into Datagridview

    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")

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width