-
Jun 30th, 2019, 03:42 PM
#1
Thread Starter
Lively Member
[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.
-
Jun 30th, 2019, 09:21 PM
#2
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.
-
Jun 30th, 2019, 09:24 PM
#3
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...
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 30th, 2019, 09:34 PM
#4
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
-
Jul 1st, 2019, 03:07 PM
#5
Thread Starter
Lively Member
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.
-
Jul 1st, 2019, 03:16 PM
#6
Re: Load data into Datagridview
Loop through the WorkSheet.Cells at the correct indices instead of trying to grab a range that way...
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jul 1st, 2019, 03:26 PM
#7
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.
-
Jul 1st, 2019, 04:55 PM
#8
Thread Starter
Lively Member
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
-
Jul 1st, 2019, 05:19 PM
#9
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)
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jul 1st, 2019, 05:20 PM
#10
Re: Load data into Datagridview
Don't use the ToString, just use the array....
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jul 2nd, 2019, 12:52 AM
#11
Thread Starter
Lively Member
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.
-
Jul 2nd, 2019, 01:19 AM
#12
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.
-
Jul 2nd, 2019, 07:40 AM
#13
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")
-
Jul 2nd, 2019, 07:40 AM
#14
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|