I have this question.

I have already imported all the excel data in the datatable,
but now I want them stored in a 2 dimensional array, meaning I want to have this kind of thing in the array, something like array[x][y], where x is the row and y is the column.
how can I create this kind of array?


Recommended Answers

All 6 Replies

You could use two ArrayLists.
For each ArrayList as an item in another ArrayList.
The "outer" ArrayList contains the rows, and the "inner" ArrayList contains the columns.

Dim arrRows As New ArrayList

For Each row As DataRow In <datatable>.Rows
   Dim arrCols As New ArrayList
   arrCols.Items.Add(row("first column"))
   arrCols.Items.Add(row("second column"))
   arrCols.Items.Add(row("third column"))
   arrCols.Items.Add(row("fourth column"))
   'and so on


To access those rows and columns in the ArrayLists, you can do this.

For Each item As ArrayList in arrRows
   variable1 = item(0)
   variable2 = item(1)
   variable3 = item(2)
   variable4 = item(3)

'Or this

someVariable1 = arrRows(0)(0) 'Retrieve the first "cell" in the first row
someVariable2 = arrRows(2)(3) 'Retrieve the fourth "cell" in the third row
dim TwoDArray(10,10) as Int16

Both would work for what you intend to do.

A 2 dimension array means that all the items, in both dimensions, must be of the same type. If the columns in the datatable are not of the same type, this can not be implemented.

While the solution from Oxiegen uses ArrayLists, and is a very good solution for unknown types of items, the solution from ChrisPadgham uses less overhead but needs to know in advance the types and the number of elements.

Also there is an intermediate solution: use a undefined length 2 dimensions array of a known type, then enlarge the array as needed.

To define the array (lets say of integers) you can use:

Dim TwoDimensionsArray(,) as Intgeger

Then on the loop to fill the array from the datatable you can write something like:

Dim R as Integer = -1
For Each row As DataRow In <datatable>.Rows
    R += 1;
    If TwoDimensionsArray Is Null Then
        ReDim TwoDimensionsArray(0,1)
        ReDim Preserve TwoDimensionsArray(R,1)
    End If
    TwoDimensionsArray(R,0) = row("first column")
    TwoDimensionsArray(R,1) = row("second column")

The ReDim Preserve sentence copies the current content in a new memory address and allocates as many space as requested. Depending on the size, this solution can have more (or less) overhead than the one of ArrayLists depending on the number of elements. Allways will have more overhead than the solution of knowing the right size.

Hope this helps

if you have more than one type you may be better with a user defined type

Public Type UserDetails
    MyName As String
    BackupName As String
    CurrId as Integer
End Type

dim Userlist(99) as UserDetails

All of these examples are perfectly good. When it comes down to it, it's a matter of preference for which way to go.
There really is no right or wrong method.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.