Transform array of data to datatable rows
I am try to take a 1 dimensional array of data and transform it to display in X number of columns each with X number of rows for each page into a datatable to easily display as the sample below shows. I am not sure the best way to do this.
Just to test the example I want 3 columns per page, and each column will contain 3 rows with the sample data below there should be 2 pages with 3 columns and a 4 page will have only 1 col and 1 row of data remaining.
samplearray= "{"100","110","120","130",
Example Output:
[Page #1]
[Column #1] [Column #2] [Column #3]
100 130 160
110 140 170
120 150 180
[Page #2]
[Column #1] [Column #2] [Column #3]
190 220 250
200 230 260
210 240 270
[Page #3]
[Column #1] [Column #2] [Column #3]
280
Just to test the example I want 3 columns per page, and each column will contain 3 rows with the sample data below there should be 2 pages with 3 columns and a 4 page will have only 1 col and 1 row of data remaining.
samplearray= "{"100","110","120","130",
Example Output:
[Page #1]
[Column #1] [Column #2] [Column #3]
100 130 160
110 140 170
120 150 180
[Page #2]
[Column #1] [Column #2] [Column #3]
190 220 250
200 230 260
210 240 270
[Page #3]
[Column #1] [Column #2] [Column #3]
280
This seemed to work as a test here, should give you the general idea of an approach. Code below, followed by output from debug.
»bp
Sub EE29146093()
Dim a() As String
Dim dt As DataTable
Dim s As String
' Initialize array
a = {"100", "110", "120", "130", "140", "150", "160", "170", "180", "190", "200", "210", "220", "230", "240", "250", "260", "270", "280"}
' Convert to data table breaking into 3 columns
dt = Array2DataTable(a, 3)
' Display data table to console for testing
s = ""
For Each col As DataColumn In dt.Columns
If s <> "" Then
s = s & ","
End If
s = s & col.ColumnName
Next
Console.WriteLine(s)
For Each row As DataRow In dt.Rows
s = ""
For Each col As DataColumn In dt.Columns
If s <> "" Then
s = s & ","
End If
s = s & row(col.ColumnName)
Next
Console.WriteLine(s)
Next
End Sub
Function Array2DataTable(a() As String, w As Integer) As DataTable
Dim dt As New DataTable
Dim r As Integer
Dim c As Integer
' Define column headers for table
For c = 1 To w
dt.Columns.Add(New DataColumn("Col" & c))
Next
' Map input array to rows and columns in data table
For r = 0 To UBound(a) Step 3
' Create new row
Dim row As DataRow = dt.NewRow()
' Add desired number of columns to each row
For c = 0 To w - 1
' Handle case where array doesn't have an even multiple of columns in it (fill with blank values)
If r + c > UBound(a) Then
row(c) = ""
Else
row(c) = a(r + c)
End If
Next
' Add this row to table
dt.Rows.Add(row)
Next
' return table
Array2DataTable = dt
End FunctionCol1,Col2,Col3
100,110,120
130,140,150
160,170,180
190,200,210
220,230,240
250,260,270
280,,»bp
ASKER
Hi Bill,
The only problem is that I am actually trying to make the value increment per column row and not per row column. That is why I was having a hard time. So the output should be as follows.
Page #1
Col1,Col2,Col3
100,130,160
110,140,170
120,150,180
Page #2
Col1,Col2,Col3
190,220,250
200,230,260
210,240,270
Page #3
Col1,Col2,Col3
290,,
The only problem is that I am actually trying to make the value increment per column row and not per row column. That is why I was having a hard time. So the output should be as follows.
Page #1
Col1,Col2,Col3
100,130,160
110,140,170
120,150,180
Page #2
Col1,Col2,Col3
190,220,250
200,230,260
210,240,270
Page #3
Col1,Col2,Col3
290,,
Sorry, I noticed that when I originally read the Q, then just did the "obvious" mapping when I got to trying to code an example. Okay, we can adjust for that...
»bp
»bp
ASKER
No Problem. I was spinning my gears for awhile on that one and thought I should get a fresh mind on the problem. Thanks!
So it appears we would have to take the "rows per page" value into consideration to do that. Since you want the first 9 values on the first 3 rows of the data table, then the next 9, etc.
Let me ask you this, what do you want the data table itself to look like? Do you just want:
»bp
Let me ask you this, what do you want the data table itself to look like? Do you just want:
[Col1] [Col2] [Col3]
100 130 160
110 140 170
120 150 180
190 220 250
200 230 260
210 240 270
280»bp
Or...
»bp
[Col1] [Col2] [Col3]
100 170 230
110 180 240
120 190 250
130 200 260
140 210 270
150 220 280
160»bp
ASKER
Yes the first example is how I would want the data table populated and yes you would have to use a "rows per page" value. Thanks.
Okay, another boundary condition to consider now.
If we started with this array:
»bp
If we started with this array:
{"100", "110", "120", "130", "140", "150", "160", "170", "180", "190", "200", "210", "220", "230", "240", "250", "260", "270", "280", "290", "300"}[Col1] [Col2] [Col3]
100 130 160
110 140 170
120 150 180
190 220 250
200 230 260
210 240 270
280
290
300[Col1] [Col2] [Col3]
100 130 160
110 140 170
120 150 180
190 220 250
200 230 260
210 240 270
280 290 300»bp
ASKER
The first example would be correct. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Bill. This was exactly what I was looking for. Great Job!
Great, glad that was useful (and I had a little fun puzzling out the mapping).
»bp
»bp
If not then you would have to break pages based on row number in the presentation code?
If you just want a simple two dimensional table from a one dimensional one, then would the number of columns be the driver for the transformation? And then based on that as many rows as needed would be added to the array (not assigned to pages)?
»bp