Putting cell values from excel columns into multiple arrays

[bellaelysium]

Hi, I hope you can give me some guidance in this.

I am dealing with the data in excel using VS. Currently, I have problem trying to put my column data (starting from D column to x columns that user specified in textbox) into different arrays as I want to manipulate them individually.

Right now, my code output is that the values from D column is being displayed in rows instead of columns and the E column continue thereafter (in listview).

The reason for putting them in arrays because I am dealing with large amounts of data and copying to new excel workbook slow everything down. So I figure I'll put them in arrays. In addition, is it possible to apply excel formula (INDIRECT forumla) while they are in arrays before pasting the final calculated values in new excel workbook?


PS: I insert listviewbox just for checking purpose to see if values are displayed correctly.

 
   
       
'select column one by one using for loop (D to x column) to paste into different workbooks
        
Dim lastcell, firstcell, entireColumn, arrayrange As Excel.Range     
Dim colNum As Integer        
Dim i AsInteger

        colNum = TextBox1.Text + 3
         
For i = 4 To colNum
             firstcell = WSheet.Cells(2, i)
             lastcell = firstcell.End(Excel.XlDirection.xlDown)
             entireColumn = WSheet.Range(firstcell, lastcell)
             arrayrange = entireColumn

             'put the entire column values into array
Dim myArray AsObject(,) '<-- declared as 2D Array  
            myArray = arrayrange.Value    

'store the content of each cell 

For r AsInteger = 1 To myArray.GetUpperBound(0)
For c AsInteger = 1 To myArray.GetUpperBound(1)
                
Dim myValue AsObject = myArray(r, c)
                    ListView1.Items.Add(myValue.ToString)
                 
Next c
           
Next r
           
Continue For
Next

[jayinthe813]

I really dont like dealing with arrays when list is available.

        Dim OverallValueList As New List(Of List(Of String))

        Dim xlapp As New Excel.Application
        xlapp.Workbooks.Add()
        Dim Wsheet As Excel.Worksheet
        xlapp.Visible = True

        Wsheet = xlapp.ActiveSheet

        Dim colNum As Integer = TextBox1.Text + 3

        For i As Integer = 4 To colNum
            Dim LastRow = Wsheet.Cells(Wsheet.Rows.Count, i).End(Excel.XlDirection.xlUp).Row

            If LastRow >= 2 Then
                Dim ColValuesList As New List(Of String)
                For c = 2 To LastRow
                    ColValuesList.Add(Wsheet.Cells(c, i).value)
                Next c
                OverallValueList.Add(ColValuesList)
            End If
        Next i

        For Each columnValueList In OverallValueList
            For Each value In columnValueList
                MessageBox.Show("this is value item from column: " & value)
            Next value
            MessageBox.Show("move to next column value list")
        Next columnValueList

http://www.dotnetperls.com/list-vbnet

If you want to enable editing, perhaps you should add the values to a datagridview instead?

[bellaelysium]

I really dont like dealing with arrays when list is available.

        Dim OverallValueList As New List(Of List(Of String))

        Dim xlapp As New Excel.Application
        xlapp.Workbooks.Add()
        Dim Wsheet As Excel.Worksheet
        xlapp.Visible = True

        Wsheet = xlapp.ActiveSheet

        Dim colNum As Integer = TextBox1.Text + 3

        For i As Integer = 4 To colNum
            Dim LastRow = Wsheet.Cells(Wsheet.Rows.Count, i).End(Excel.XlDirection.xlUp).Row

            If LastRow >= 2 Then
                Dim ColValuesList As New List(Of String)
                For c = 2 To LastRow
                    ColValuesList.Add(Wsheet.Cells(c, i).value)
                Next c
                OverallValueList.Add(ColValuesList)
            End If
        Next i

        For Each columnValueList In OverallValueList
            For Each value In columnValueList
                MessageBox.Show("this is value item from column: " & value)
            Next value
            MessageBox.Show("move to next column value list")
        Next columnValueList

http://www.dotnetperls.com/list-vbnet

If you want to enable editing, perhaps you should add the values to a datagridview instead?

Whoa whoa thanks! The list array looks good. Is it possible to use the list array to display the whole column instead of cell by cell and put in in a new workbook?

I am beginner to programming actually so is there a link to doing datagridview so that I can take a look? I have google about it but could not understand what it do.

[jayinthe813]

http://www.dotnetperls.com/datagridview-vbnet for some brief info on datagridview. It basically contains columns and rows kind of like a spreadsheet.

If you wanted to view the items all at one time, say through a messagebox, you could use whats called a stringbuilder. In your instance however, you do not need to view all the data at once. Instead, you can write each value in the list to a row using a for each loop. Increment the row number and column numbers as needed. Below is an example to get you started.

        Dim colIndex As Integer = 1
        Dim rowIndex As Integer = 1

        For Each columnValueList In overallValueList
            For Each value In columnValueList
                wSheet.Cells(rowIndex, colIndex).value = value
                rowIndex += 1
            Next value
            ColIndex += 1

        Next columnValueList

[bellaelysium]

http://www.dotnetperls.com/datagridview-vbnet for some brief info on datagridview. It basically contains columns and rows kind of like a spreadsheet.

If you wanted to view the items all at one time, say through a messagebox, you could use whats called a stringbuilder. In your instance however, you do not need to view all the data at once. Instead, you can write each value in the list to a row using a for each loop. Increment the row number and column numbers as needed. Below is an example to get you started.

Finally I am getting started somewhere. I have read through the datagridview and it seems like its for displaying purposes. Can you actually manipulate the data?
I am trying to actually manipulate the values of each column with this code (excel macro) in vb.net format:

ActiveCell.FormulaR1C1 = "=RC1/INDIRECT(""$A""&(COLUMN()))"

So initially, my idea is to put each column of values into new workbook and then insert this INDIRECT formula before combining the same column from all workbooks into one master workbook and filtered out the rows that I don't want. Does the datagridview able to do that? I have huge datasets so it is a performance killer and cause my computer to lag so I thought of putting them into arrays and manipulate straight from there but I do not know if its possible to that for generic list...and have no idea how to proceed.


From what I read, it seems the datagridview is more for displaying purposes?


Thanks for your guidance so far

[jayinthe813]

Finally I am getting started somewhere. I have read through the datagridview and it seems like its for displaying purposes. Can you actually manipulate the data?
I am trying to actually manipulate the values of each column with this code (excel macro) in vb.net format:

ActiveCell.FormulaR1C1 = "=RC1/INDIRECT(""$A""&(COLUMN()))"

So initially, my idea is to put each column of values into new workbook and then insert this INDIRECT formula before combining the same column from all workbooks into one master workbook and filtered out the rows that I don't want. Does the datagridview able to do that? I have huge datasets so it is a performance killer and cause my computer to lag so I thought of putting them into arrays and manipulate straight from there but I do not know if its possible to that for generic list...and have no idea how to proceed.


From what I read, it seems the datagridview is more for displaying purposes?


Thanks for your guidance so far

Yes the datagridview is for displaying, but it seems this is not the functionality you want. you can manipulate anything in a list just like an array. List is essentially the .NET replacement for arrays so you should use them where possible.

Resizing becomes way easier. Instead of needing to do:

        ReDim Preserve ExcelArray(i)

You call the .add method of the list:

        Dim ExcelStrings As New List(Of String)
        ExcelStrings.Add("Value")
        MsgBox(ExcelStrings(0)) 'access index of list item

        For Each stringItem In ExcelStrings 'or as a loop
            MsgBox(stringItem)
        Next stringItem

You can still access each item by index or loop through the collection. You will loop to pull the values out of excel, and then loop again to place them in a new workbook. Why are you using a formula? You can run a test on each item as you pull it out of the current sheet or place it in a new one to determine whether or not you want to keep it.