CODEWITHSUNDEEP
arraysvb.net
Sajid
Sajid

Reputation: 23

Array to Excel using vb.net

This code takes a text file as input and stores each line in a string array, loops through each line and checks a condition and eventually fills the array.

When I try to transfer this one dimensional array to an excel column through the .range method of excel object library it fills all the cells of range with the first value of the array (i.e array(0)).

 sr = openFileDialog1.FileName()
            If (sr IsNot Nothing) Then

            Dim alltextlines() As String = IO.File.ReadAllLines(sr)
            Dim name As String
            Dim isvalid As Boolean
            Dim tempstr() As String
            Dim count As Integer = 0


            For Each myLine In alltextlines
                ReDim Preserve tempstr(count)
                If myLine.Contains(">") And myLine.Contains(" sds dsd") Then

                    isvalid = Integer.TryParse((Microsoft.VisualBasic.Mid(Microsoft.VisualBasic.LTrim(myLine), 3, 1)), 0)

                    If isvalid Then

                        name = Microsoft.VisualBasic.Left(Microsoft.VisualBasic.LTrim(myLine), 5)

                    Else
                        name = Microsoft.VisualBasic.Left(Microsoft.VisualBasic.LTrim(myLine), 7)

                    End If

                    tempstr(count) = name
                    count = count + 1
                End If

            Next
            Dim message As String = String.Join("..", tempstr)
            MsgBox(message)

            Dim oExcel As Object
            Dim oBook As Object
            Dim oSheet As Object

            'Start a new workbook in Excel.
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add
            oSheet = oBook.Worksheets(1)
            oSheet.Range("A1").Value = "SiteNames"
            oSheet.Range("A1:B1").Font.Bold = True
            oSheet.Range("A2").Resize(tempstr.Length).Value = tempstr




            oSheet = Nothing
            oBook = Nothing
            oExcel.Quit()
            oExcel = Nothing
            GC.Collect()


        Else
            MsgBox("TEXT FILE IS EMPTY", MsgBoxStyle.Critical, "Error")
        End If
    End If
End Sub

Upvotes: 2

Views: 6307

Answers (1)

Steve
Steve

Reputation: 216363

You should pass a bidimensional array to set the range values. This complicates your loop to extract only the rows that respect a predefined condition and you are forced to a second loop to fill the bidimensional array

As an example

Dim values(5) as string
values(0) = "Test0"
values(1) = "Test1"
values(2) = "Test2"
values(3) = "Test3"
values(4) = "Test4"

Dim tempstr(,) As String = new String(values.Length,1) {}
for x as Integer = 0 to values.Length - 1
    tempstr(x, 0) = values(x)
next


Dim oExcel as Object = CreateObject("Excel.Application")    
Dim oBook as Object = oExcel.Workbooks.Add 
Dim oSheet as Object = oBook.Worksheets(1) 
oSheet.Range("A1").Value = "SiteNames" 
oSheet.Range("A1").Font.Bold = True 
Dim r As Range = oSheet.Range("A2").Resize(tempStr.GetLength(0))
r.Value2 =  tempstr
oExcel.Visible = true

Upvotes: 2

Related Questions