2 Replies - 8432 Views - Last Post: 25 July 2011 - 10:54 PM Rate Topic: -----

#1 everyone  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 22-July 11

Save to excel file

Posted 22 July 2011 - 10:12 PM

Hello,

I could use some help. I have a program where I put data into a list view in VB.net. I then use the code below in a button click to export the content into a excel file where I can then save it. however I plan to add alot more data to the list view ad would like to auto save the data in the list view to an excel file (where it saves it to disk not an open excel window) and then clears the list view then will repopulate the list view and repet the loop of saving the data to a new excel file on disk.

could anyone help me with the process on how to change this code to make it save it right to disk rather the how it is currently? as it is now it opens a excel document and enters the contents of the listview into that excel document but you have to save it manually.

I normally do C# an I am editing a VB.net program for a client and could use the help.




        Try


            Dim xla As New Microsoft.Office.Interop.Excel.Application()

            xla.Visible = True

            Dim wb As Microsoft.Office.Interop.Excel.Workbook = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)

            Dim ws As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(xla.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

            Dim i As Integer = 1

            Dim j As Integer = 1
            Dim jj As Integer = ListView1.Columns.Count

            For rr = 0 To ListView1.Columns.Count - 1
                ws.Cells(i, j) = ListView1.Columns(rr).Text
                j = j + 1
            Next

            i = 2

            j = 1
            For Each comp As ListViewItem In ListView1.Items


                ws.Cells(i, j) = comp.Text.ToString()

                'MessageBox.Show(comp.Text.ToString());

                For Each drv As ListViewItem.ListViewSubItem In comp.SubItems


                    ws.Cells(i, j) = drv.Text.ToString()


                    j += 1
                Next

                j = 1


                i += 1
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try



Is This A Good Question/Topic? 0
  • +

Replies To: Save to excel file

#2 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: Save to excel file

Posted 23 July 2011 - 12:56 AM

See how it will works for you
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



        Try


            Dim xla As New Microsoft.Office.Interop.Excel.Application()

            xla.Visible = True

            Dim wb As Microsoft.Office.Interop.Excel.Workbook = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)

            Dim ws As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(xla.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

            Dim i As Integer = 1

            Dim j As Integer = 1
            Dim jj As Integer = ListView1.Columns.Count

            For rr = 0 To ListView1.Columns.Count - 1
                ws.Cells(i, j) = ListView1.Columns(rr).Text
                j = j + 1
            Next

            i = 2

            j = 1
            For Each comp As ListViewItem In ListView1.Items


                ws.Cells(i, j) = comp.Text.ToString()

                'MessageBox.Show(comp.Text.ToString());

                For Each drv As ListViewItem.ListViewSubItem In comp.SubItems


                    ws.Cells(i, j) = drv.Text.ToString()


                    j += 1
                Next

                j = 1


                i += 1
            Next
            ws.SaveAs("C:\Test\blabla.xls")
            wb.Close()
            xla.Quit()

            releaseObject(xla)
            releaseObject(wb)
            releaseObject(ws)
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub


Was This Post Helpful? 0
  • +
  • -

#3 everyone  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 22-July 11

Re: Save to excel file

Posted 25 July 2011 - 10:54 PM

It gives a error when trying to auto save and I couldnt figure it out so i just took it out, figure I can pass it off to the client as a feature since the program pauses when it loads to the excel file til the usesr saves it. imma try to pass it as a way to make sure thier workers are working or somthing like that... thanks alot for the help.

there is another issue though. it is eating ALOT of memory and I think it's how the orig programmer made the string edits.

editing strings take alot of memory and in C# you can use string builder to do it and cut down the memory use by alot. can you do the same thing in VB.net anyway? the code for the string edits is below.

a simple example on how to use the string builder in VB.net is really all i need and i'll try to edit the code myself. or any other way to help fix the memory eating issue with the code. thanks again for your help.







        Try
            If TextBox1.Text = Nothing Then
                MessageBox.Show("please upload list of textfile")
                Return
            End If

            i = linbyline.Length - 1



            Dim eachurl As String = Nothing


            For kkk = 0 To i

                eachurl = linbyline(kkk).ToString

                CurrentURLTextBox.Text = eachurl



                Button1.Enabled = False
                WebBrowser1.Navigate(eachurl)

                Do Until WebBrowser1.ReadyState = WebBrowserReadyState.Complete
                    System.Windows.Forms.Application.DoEvents()
                Loop
                Dim allelements As HtmlElementCollection = WebBrowser1.document.GetElementsByTagName("p")
                Dim k As Integer = 1

                Dim totalstring As String = Nothing

                For Each webpagelement As HtmlElement In allelements
                    If webpagelement.GetAttribute("className") = "nav" Then
                        totalstring = webpagelement.InnerText
                    End If
                Next


                If totalstring = Nothing Then
                    GoTo skip 'If the url is empty the program will skip the url and go to the next one
                End If


                totalstring = totalstring.Replace("Next", "")
                totalstring = totalstring.Replace("|", "")
                totalstring = totalstring.Replace("of", "")
                totalstring = totalstring.Substring(totalstring.Length - 5)
                totalstring = totalstring.Replace(" ", "")
                'totalstring = totalstring.LastIndexOf(4)
                Dim l As Integer = Convert.ToInt16(totalstring)
                r = l / 10
                r = r + 1
                Dim n As Integer = 0
                Dim number As Integer = 1

                For n = 0 To r
                    If CheckBox1.Checked = True Then



                        Dim urlst As String = "http://v4.jiwire.com/search-wifi-hotspots.htm?city_id=8026&&startnum=" & number

                        WebBrowser1.Navigate(urlst)
                        number = number + 10
                        Do Until WebBrowser1.ReadyState = WebBrowserReadyState.Complete
                            System.Windows.Forms.Application.DoEvents()
                        Loop
                        Dim allelements1 As HtmlElementCollection = WebBrowser1.document.GetElementsByTagName("td")

                        Dim locationname As String = Nothing
                        Dim completestring As String = Nothing
                        Dim linebyline As String() = Nothing
                        Dim street As String = Nothing
                        Dim city As String = Nothing
                        Dim state As String = Nothing
                        Dim country As String = Nothing
                        Dim pincode As String = Nothing

                        For Each webpagelement1 As HtmlElement In allelements1
                            If CheckBox1.Checked = True Then

                                Dim ServiceProviderName0 As String = Nothing
                                Dim ServiceProviderNetworkName0 As String = Nothing
                                Dim ServiceProviderServicePlanPrice0 As String = Nothing
                                Dim ServiceProviderName1 As String = Nothing
                                Dim ServiceProviderNetworkName1 As String = Nothing
                                Dim ServiceProviderServicePlanPrice1 As String = Nothing
                                Dim ServiceProviderName2 As String = Nothing
                                Dim ServiceProviderNetworkName2 As String = Nothing
                                Dim ServiceProviderServicePlanPrice2 As String = Nothing
                                If webpagelement1.GetAttribute("className") = "desc" Then
                                    Dim allelements2 As HtmlElementCollection = webpagelement1.GetElementsByTagName("H3")
                                    Dim kk As Integer = 0
                                    For Each webpagelement2 As HtmlElement In allelements2
                                        If kk = 0 Then
                                            Dim allelements3 As HtmlElementCollection = webpagelement2.GetElementsByTagName("A")
                                            For Each webpagelement3 As HtmlElement In allelements3
                                                Dim href As String = Nothing

                                                href = webpagelement3.GetAttribute("href")
                                                locationname = Nothing
                                                locationname = webpagelement3.InnerText
                                                Dim urltwo As WebBrowser = Nothing
                                                urltwo = New WebBrowser()
                                                urltwo.ScriptErrorsSuppressed = True
                                                urltwo.Navigate(href)
                                                Do Until urltwo.ReadyState = WebBrowserReadyState.Complete
                                                    System.Windows.Forms.Application.DoEvents()
                                                Loop
                                                Dim allelements4 As HtmlElementCollection = urltwo.document.GetElementsByTagName("title")


                                                For Each webpagelement4 As HtmlElement In allelements4
                                                    completestring = Nothing
                                                    completestring = webpagelement4.InnerHtml
                                                    linebyline = Nothing
                                                    linebyline = completestring.Split(New Char() {","c})
                                                    street = Nothing
                                                    street = linebyline(1).ToString()
                                                    city = Nothing
                                                    city = linebyline(2).ToString()
                                                    state = Nothing
                                                    state = linebyline(3).ToString()
                                                    pincode = Nothing
                                                    pincode = linebyline(4).ToString()
                                                    country = Nothing
                                                    country = linebyline(5).ToString()
                                                    country = country.Replace(" - JiWire Global Wi-Fi Finder", "")
                                                Next
                                                Dim allelements5 As HtmlElementCollection = urltwo.document.GetElementsByTagName("tbody")

                                                Dim connectioninfo As String = Nothing
                                                Dim eachconectioninfo As String() = Nothing
                                                Dim value1 As Integer = 0

                                                For Each webpagelement5 As HtmlElement In allelements5

                                                    Dim allelements6 As HtmlElementCollection = webpagelement5.GetElementsByTagName("tr")
                                                    For Each webpagelement6 As HtmlElement In allelements6
                                                        Dim allelements7 As HtmlElementCollection = webpagelement6.GetElementsByTagName("td")
                                                        connectioninfo = Nothing
                                                        For Each webpagelement7 As HtmlElement In allelements7
                                                            connectioninfo += webpagelement7.InnerText & ","

                                                        Next
                                                        eachconectioninfo = connectioninfo.Split(New Char() {","c})
                                                        If value1 = 2 Then
                                                            ServiceProviderName2 = Nothing
                                                            ServiceProviderName2 = eachconectioninfo(0).ToString
                                                            ServiceProviderNetworkName2 = Nothing
                                                            ServiceProviderNetworkName2 = eachconectioninfo(1).ToString
                                                            ServiceProviderServicePlanPrice2 = Nothing
                                                            ServiceProviderServicePlanPrice2 = eachconectioninfo(2).ToString
                                                            value1 = value1 + 1

                                                        End If
                                                        If value1 = 1 Then
                                                            ServiceProviderName1 = Nothing
                                                            ServiceProviderName1 = eachconectioninfo(0).ToString
                                                            ServiceProviderNetworkName1 = Nothing
                                                            ServiceProviderNetworkName1 = eachconectioninfo(1).ToString
                                                            ServiceProviderServicePlanPrice1 = Nothing
                                                            ServiceProviderServicePlanPrice1 = eachconectioninfo(2).ToString
                                                            value1 = value1 + 1

                                                        End If
                                                        If value1 = 0 Then
                                                            ServiceProviderName0 = Nothing
                                                            ServiceProviderName0 = eachconectioninfo(0).ToString
                                                            ServiceProviderNetworkName0 = Nothing
                                                            ServiceProviderNetworkName0 = eachconectioninfo(1).ToString
                                                            ServiceProviderServicePlanPrice0 = Nothing
                                                            ServiceProviderServicePlanPrice0 = eachconectioninfo(2).ToString
                                                            value1 = value1 + 1

                                                        End If


                                                    Next
                                                Next
                                            Next

                                        End If

                                    Next
                                    Dim totaldata(14) As String

                                    Dim itm As ListViewItem
                                    totaldata(0) = locationname
                                    totaldata(1) = street
                                    totaldata(2) = city
                                    totaldata(3) = state
                                    totaldata(4) = pincode
                                    totaldata(5) = country
                                    totaldata(6) = ServiceProviderName0
                                    totaldata(7) = ServiceProviderNetworkName0
                                    totaldata(8) = ServiceProviderServicePlanPrice0
                                    totaldata(9) = ServiceProviderName1
                                    totaldata(10) = ServiceProviderNetworkName1
                                    totaldata(11) = ServiceProviderServicePlanPrice1
                                    totaldata(12) = ServiceProviderName2
                                    totaldata(13) = ServiceProviderNetworkName2
                                    totaldata(14) = ServiceProviderServicePlanPrice2
                                    itm = New ListViewItem(totaldata)
                                    ListView1.Items.Add(itm)

                                    If ListView1.Items.Count() = maxReturnTextBox.Text Then
                                        save()
                                        ListView1.Items.Clear()
                                        GC.Collect()




                                    End If

                                End If
                            End If
                        Next
                        WebBrowser1 = Nothing
                        WebBrowser1 = New WebBrowser()
                        WebBrowser1.ScriptErrorsSuppressed = True
                    End If
                Next
                WebBrowser1 = Nothing
                WebBrowser1 = New WebBrowser()
                WebBrowser1.ScriptErrorsSuppressed = True
skip:
            Next
            Button1.Enabled = True
            MessageBox.Show("sucessfully scrape all information")
        Catch ex As Exception



        End Try



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1