none
work with listbox and excel sheet RRS feed

  • Question

  • i have a lisbox with unknown items count and new excel worksheet

    i want to write item 1 value in column 1

    and i want my program to automatic write item value in column item number

    how to do that

    Thursday, March 1, 2018 9:45 PM

Answers

  • no display first in listbox1 and then i click button1 to start write them into excel sheet

    OK, this will perform the export directly. You will need to change paths for the files:

            Dim ConnectionString As String
    
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=C:\Users\...\Documents\My Database\Excel\EmailAddress.xlsx;Extended Properties=""Excel 12.0 Xml"""
    
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            ExcelConnection.Open()
    
            Dim SQLString As String = "SELECT * INTO [EmailAddressSheet] FROM [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[EmailAddress.txt]"
    
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLString, ExcelConnection)
            ExcelCommand.ExecuteNonQuery()
    
            ExcelConnection.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 2, 2018 1:51 PM

All replies

  • Lots of question first

    What does the list box have to do with the task?

    "and new excel worksheet", where does the sheet name come from.

    "i want to write 1 value in column 1" which row, the last row, the first row, in between ???

    "and i want my program to automatic write item value in column item number"

    Nothing is automatic

    Finally, what have you tried yourself? Are you working with .xls or .xlsx or both?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 1, 2018 11:10 PM
    Moderator
  • first of all i want thank you for your reply

    now let me answer you 

    i have a text file containing e-mails addresses i can say its about 1 million e-mails 

    every line has just one e-mail

    and i successfully make a listbox of them with vb.net

    now i want make an excel sheet for this list

    can you help me

    Thursday, March 1, 2018 11:22 PM
  • Is the Excel file .xls or .xlsx ?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 1, 2018 11:48 PM
    Moderator
  •  the Excel file is .xlsx
    Thursday, March 1, 2018 11:52 PM
  • First off, one million rows of a single column will take a good deal of time.

    Next up, there are several avenues to take, Excel automation which is inherently slow outside of copying the data to the Windows clipboard and then using a paste command in the excel objects to insert data but that option should be off the table for this much data. There is OleDb, a tad faster than excel automation yet there can be issues here too.

    The best method is using OpenXML which is not easy to work with but there is a library SpreadSheetLight which is easy to use if you bypass the ListBox. Setting up SpreadSheetLight is described in the following code sample under "Build the sample"

    Going with bypassing the ListBox (you could still load the ListBox but not export from it) e.g.

    Private Sub EmailForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ListBox1.Items.AddRange(IO.File.ReadAllLines(
            IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "emailaddresses.txt")))
    End Sub

    Okay, here is a class to write a text file filled with an email on each line.

    Imports SpreadsheetLight
    
    Public Class ExportEmail
        Public Function ImportTextFile(
            pTextFileName As String,
            pExcelFileName As String,
            pSheetName As String) As Boolean
    
            Try
                Using sl As New SLDocument(pExcelFileName)
    
                    Dim sheets = sl.GetSheetNames(False)
    
                    If sheets.Any(Function(sheetName) sheetName.ToLower = pSheetName.ToLower) Then
                        sl.SelectWorksheet(pSheetName)
                        sl.ClearCellContent()
                    Else
                        sl.AddWorksheet(pSheetName)
                    End If
    
                    Dim tio As SLTextImportOptions = New SLTextImportOptions
    
                    sl.ImportText(pTextFileName, "A1", tio)
    
                    sl.AutoFitColumn("A")
                    sl.SetColumnWidth(2, 5)
    
    
                    If sheets.FirstOrDefault(Function(sheetName) sheetName.ToLower = "sheet1") IsNot Nothing Then
                        If Not pSheetName.ToLower = "sheet1" Then
                            sl.DeleteWorksheet("Sheet1")
                        End If
                    End If
    
                    sl.Save()
    
                    Return True
                End Using
            Catch ex As Exception
                '
                ' Keeping it simple, you need to decide how to 
                ' handle exceptions if any.
                '
                Return False
            End Try
            Return True
        End Function
    End Class
    

    Form code

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim textFileName As String =
                IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "emailaddresses.txt")
    
        Dim excelFileName As String =
                IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "email.xlsx")
    
        Dim sheetName As String = "EmailAddresses"
    
        Dim ops As New ExportEmail
        ops.ImportTextFile(textFileName, excelFileName, sheetName)
        MessageBox.Show("Done")
    
    End Sub

    Results (in this case 1,000 rows)

    The above example needs an existing Excel file while the example below create it for you.

    Imports SpreadsheetLight
    
    Public Class ExportEmail
        Public Function ImportTextFile(
            pTextFileName As String,
            pExcelFileName As String,
            pSheetName As String) As Boolean
    
            Try
                Using sl As New SLDocument()
    
                    Dim sheets = sl.GetSheetNames(False)
    
                    If sheets.Any(Function(sheetName) sheetName.ToLower = pSheetName.ToLower) Then
                        sl.SelectWorksheet(pSheetName)
                        sl.ClearCellContent()
                    Else
                        sl.AddWorksheet(pSheetName)
                    End If
    
                    Dim tio As SLTextImportOptions = New SLTextImportOptions
    
                    sl.ImportText(pTextFileName, "A1", tio)
    
                    sl.AutoFitColumn("A")
                    sl.SetColumnWidth(2, 5)
    
    
                    If sheets.FirstOrDefault(Function(sheetName) sheetName.ToLower = "sheet1") IsNot Nothing Then
                        If Not pSheetName.ToLower = "sheet1" Then
                            sl.DeleteWorksheet("Sheet1")
                        End If
                    End If
    
                    sl.SaveAs(pExcelFileName)
    
                    Return True
                End Using
            Catch ex As Exception
                '
                ' Keeping it simple, you need to decide how to 
                ' handle exceptions if any.
                '
                Return False
            End Try
            Return True
        End Function
    End Class
    

    FINALLY

    I can't stress enough that using SpreadSheetLight or another way as mentioned above will take time. You might even consider using a BackgroundWorker component to keep the app responsive.

    If I were doing this I would use even a different method of writing to a xml file with the structure of Excel 2003 format which will take 1/3 of the time but that takes time to learn as explained here.

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, March 2, 2018 12:52 AM
    Moderator
  • first of all i want thank you for your reply

    now let me answer you 

    i have a text file containing e-mails addresses i can say its about 1 million e-mails 

    every line has just one e-mail

    and i successfully make a listbox of them with vb.net

    now i want make an excel sheet for this list

    can you help me

    Hi HossamVSVB.NET,

    Now you just want to export listbox item into Excel, am I right? If yes, you can take a look the following code:

     Dim oItem As Object
        Dim OffS As Integer
        Dim MsExcel As Excel.Application
        Dim Wb As Excel.Workbook
        Private Sub Frmlistbox_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim lines() As String = IO.File.ReadAllLines("D:\TestField\Emails.txt")
            ListBox1.Items.AddRange(lines)
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            MsExcel = CreateObject("Excel.Application")
    
            Wb = MsExcel.Workbooks.Open("D:\TestField\Test2.xlsx")
            OffS = 0
            For Each oItem In ListBox1.Items
                Wb.Sheets(1).Range("A1").Offset(OffS, 0).Value = oItem
                OffS = OffS + 1
            Next oItem
    
            Wb.SaveAs()
            Wb.Close()
            MsExcel.Visible = False
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 2, 2018 4:43 AM
    Moderator
  • not work 

    the file test2.xlsx is empty and has no data 

    Friday, March 2, 2018 12:36 PM
  • Do you need to display the e-mail addresses in a ListBox or can they be copied directly from the text file to the Excel Workbook?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 2, 2018 1:00 PM
  • no display first in listbox1 and then i click button1 to start write them into excel sheet

    Friday, March 2, 2018 1:33 PM
  • no display first in listbox1 and then i click button1 to start write them into excel sheet

    Friday, March 2, 2018 1:40 PM
  • no display first in listbox1 and then i click button1 to start write them into excel sheet

    OK, this will perform the export directly. You will need to change paths for the files:

            Dim ConnectionString As String
    
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=C:\Users\...\Documents\My Database\Excel\EmailAddress.xlsx;Extended Properties=""Excel 12.0 Xml"""
    
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            ExcelConnection.Open()
    
            Dim SQLString As String = "SELECT * INTO [EmailAddressSheet] FROM [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[EmailAddress.txt]"
    
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLString, ExcelConnection)
            ExcelCommand.ExecuteNonQuery()
    
            ExcelConnection.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 2, 2018 1:51 PM
  • thank you so so so much this is the perfect code ever

    connecting two files and transfer data is perfect idea

    i thank you so much Paul P Clement

    but can i ask for something very difficult or that's enough for me


    Friday, March 2, 2018 4:42 PM
  • thank you so so so much this is the perfect code ever

    connecting two files and transfer data is perfect idea

    i thank you so much Paul P Clement

    but can i ask for something very difficult or that's enough for me



    If the question is related go ahead and ask. Otherwise, I would post a new question in the forum.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 2, 2018 8:11 PM