dcsimg
Results 1 to 3 of 3

Thread: Having trouble copying and pasting grid with data from excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2012
    Posts
    4

    Having trouble copying and pasting grid with data from excel

    How can I get VB.net to copy (from a loaded excel file) a range of cells that have matching IDs in Column B, then paste into outlook body in grid form. Basically, what is happening here is I am sending emails out to every ID that is in this excel file. The matching lines could have only 1 row of Information (10 columns wide) or it may have 300.

    I seem to be having trouble with the grid portion. I can get some information transferred in a string.

    Thanks guys!

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,514

    Re: Having trouble copying and pasting grid with data from excel

    The attached VS2010 VB.NET project has all of the logic to do what you want for forming html from data in an Excel sheet from an Excel 2007 file.
    Note how the SQL select statement is done, since we have indicated no header each column is Fn but for column F2 I alias the name.

    I have a sample section for doing SMTP not Outlook email.

    The query to get Excel data does an entire sheet not a range.

    Code:
    Imports System.Net.Mail
    Imports System.ComponentModel
    Imports System.Data.OleDb
    Public Class Form1
        Private ConnectionNoHeader As String = "provider= Microsoft.ACE.OLEDB.12.0; data source='{0}';Extended Properties=""Excel 12.0; HDR=No;"""
        Private ExcelFileName As String = IO.Path.Combine(Application.StartupPath, "Excel1.xlsx")
        Private HTMLFileName As String = IO.Path.Combine(Application.StartupPath, "File1.html")
        WithEvents bsExcel As New BindingSource
        ''' <summary>
        ''' Load Excel data 
        ''' Show in DataGridView for demoing only.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim SampleSearchItem As String = "AA"
            Using cn As New OleDbConnection With {.ConnectionString = String.Format(ConnectionNoHeader, ExcelFileName)}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = <SQL>SELECT F1, F2 As Identifier, F3 FROM [Sheet1$] WHERE F2='<%= SampleSearchItem %>'</SQL>.Value
                    cn.Open()
                    Dim dt As New DataTable
                    dt.Load(cmd.ExecuteReader)
                    bsExcel.DataSource = dt
                End Using
            End Using
            DataGridView1.DataSource = bsExcel
        End Sub
        ''' <summary>
        ''' Create an HTML structure suitable for emailing. Please
        ''' note that the structure was taken from another demo I did
        ''' so some tweaks are in order for CSS.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks>
        ''' Utilizes XML literals and embedded expressions to form html.
        ''' </remarks>
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim dt As DataTable = CType(bsExcel.DataSource, DataTable)
            Dim OutputToBrowser = _
            <html>
                <style type="text/css">
                    TD {background-color: green;color: #F0F8FF;padding-right:15px;}
                    .THeader {background-color: Yellow;color: Black;}
    	        </style>
                <body>
                    <p style='font-weight:bold;margin-bottom:0px'>Sample of using <span style="color:red">LINQ</span>
                        to create HTML output"
                    </p>
                    <table border="0">
                        <tr>
                            <td class='THeader'>F1</td>
                            <td class='THeader'>Identifier</td>
                            <td class='THeader'>F3</td>
                        </tr>
                        <%= From T In dt.AsEnumerable _
                            Select _
                            <tr>
                                <td width="80px">
                                    <%= T.Field(Of String)("F1") %>
                                </td>
                                <td width="45px">
                                    <%= T.Field(Of String)("Identifier") %>
                                </td>
                                <td width="30px">
                                    <%= T.Field(Of String)("F3") %>
                                </td>
                            </tr> %>
                    </table>
                </body>
            </html>
    
            IO.File.WriteAllText(HTMLFileName, OutputToBrowser.ToString)
            If CheckBox1.Checked Then
                Process.Start(HTMLFileName)
            End If
            '
            ' This part is strictly demoing and not tested.
            ' DO NOT uncomment until you have set things up i.e. host, to and from
            ' DemoSmtpEmail(OutputToBrowser.ToString)
    
        End Sub
        ''' <summary>
        ''' Constructed from
        ''' http://msdn.microsoft.com/en-us/library/system.net.mail.smtpclient.aspx
        ''' </summary>
        ''' <param name="Body"></param>
        ''' <remarks></remarks>
        Private Sub DemoSmtpEmail(ByVal Body As String)
            Dim client As New SmtpClient("Your host")
            Dim [from] As New MailAddress("jane@contoso.com", "Jane " & ChrW(&HD8) & " Clayton", System.Text.Encoding.UTF8)
            ' Set destinations for the e-mail message. 
            Dim [to] As New MailAddress("ben@contoso.com")
            ' Specify the message content. 
            Dim message As New MailMessage([from], [to])
            Dim someArrows As New String(New Char() {ChrW(&H2190), ChrW(&H2191), ChrW(&H2192), ChrW(&H2193)})
    
            message.IsBodyHtml = True
            message.Body = Body
            message.Subject = "test message 1" & someArrows
            message.SubjectEncoding = System.Text.Encoding.UTF8
            AddHandler client.SendCompleted, AddressOf SendCompletedCallback
            ' The userState can be any object that allows your callback  
            ' method to identify this send operation. 
            ' For this example, the userToken is a string constant. 
            Dim userState As String = "test message1"
            client.SendAsync(message, userState)
            Console.WriteLine("Sending message... press c to cancel mail. Press any other key to exit.")
            Dim answer As String = Console.ReadLine()
            ' If the user canceled the send, and mail hasn't been sent yet, 
            ' then cancel the pending operation. 
            If answer.StartsWith("c") AndAlso mailSent = False Then
                client.SendAsyncCancel()
            End If
            ' Clean up.
            message.Dispose()
            MessageBox.Show("Goodbye.")
    
        End Sub
        Private Shared mailSent As Boolean = False
        Private Shared Sub SendCompletedCallback(ByVal sender As Object, ByVal e As AsyncCompletedEventArgs)
            ' Get the unique identifier for this asynchronous operation. 
            Dim token As String = CStr(e.UserState)
    
            If e.Cancelled Then
                Console.WriteLine("[{0}] Send canceled.", token)
            End If
            If e.Error IsNot Nothing Then
                Console.WriteLine("[{0}] {1}", token, e.Error.ToString())
            Else
                Console.WriteLine("Message sent.")
            End If
            mailSent = True
        End Sub
    
    End Class
    The following shows how to read a range of data using OleDb not included in the attached project.
    Code:
    Imports System.Data.OleDb
    
    Module OleDbDemos
    
        Private ConnectionNoHeader As String = "provider= Microsoft.ACE.OLEDB.12.0; data source='{0}';Extended Properties=""Excel 12.0; HDR=No;"""
    
        Public Sub OpenSheetInDataGridView(ByVal FileName As String, ByVal SheetName As String)
            Dim f As New frmViewSheetData
    
            Try
                Dim dt As New DataTable
    
                Using cn As New OleDbConnection With {.ConnectionString = String.Format(ConnectionNoHeader, FileName)}
                    cn.Open()
    
                    Dim cmd As OleDbCommand = New OleDbCommand(
                                              <Text>
                                                  SELECT F1 As TheMonth, F2 as Spent FROM [<%= SheetName %>$A2:B5]
                                              </Text>.Value,
                                            cn
                    )
    
                    dt.Load(cmd.ExecuteReader)
                End Using
    
                f.Text = "Sheet name: " & SheetName
    
                f.DataGridView1.DataSource = dt
                f.DataGridView1.Columns("TheMonth").HeaderText = "Month"
    
                f.ShowDialog()
            Finally
                f.Dispose()
            End Try
    
        End Sub
    End Module
    Attached Files Attached Files

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2012
    Posts
    4

    Re: Having trouble copying and pasting grid with data from excel

    Thank you! This will get me on the right track

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width