none
Excel Database RRS feed

  • Question

  • Hi Everyone

    i've a form in vb.net that has a datagridview
    where the data coming from excel database sheet1 and sheet2
    (with sql command "SELECT a,b,c,d FROM [Sheet1$] UNION (SELECT a,b,c,d FROM [Sheet2$])).
    and want to insert all data in datagridview to sheet3 by clicking button1.
    How could i get it?

    Thnak you very much.
    Tuesday, September 12, 2017 7:49 AM

Answers

  • Hi Mike,

    According to your description, you want to combine 2 datagridview to 1 datagridview, you can refer to this link:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/0a3eba75-9fe1-4332-89f0-577bdadbe94c/using-multiple-datagridview?forum=vbgeneral#60de28a6-1096-419d-b3be-ed8b97a36fd0

    About exporting data from datagridview into excel, please refer to the following code.

      Dim app As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
            ' creating new WorkBook within Excel application  
            Dim workbook As Microsoft.Office.Interop.Excel._Workbook = app.Workbooks.Add(Type.Missing)
            ' creating new Excelsheet in workbook  
            Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
            ' see the excel sheet behind the program  
            app.Visible = True
            ' get the reference of first sheet. By default its name is Sheet1.  
            ' store its reference to worksheet  
            worksheet = workbook.Sheets("Sheet1")
            worksheet = workbook.ActiveSheet
            ' changing the name of active sheet  
            worksheet.Name = "Exported from gridview"
            ' storing header part in Excel  
            For i As Integer = 1 To DataGridView1.Columns.Count
                worksheet.Cells(1, i) = DataGridView1.Columns(i - 1).HeaderText
            Next
            ' storing Each row and column value to excel sheet  
            For i As Integer = 0 To DataGridView1.Rows.Count - 2
                For j As Integer = 0 To DataGridView1.Columns.Count - 1
                    worksheet.Cells(i + 2, j + 1) = DataGridView1.Rows(i).Cells(j).Value.ToString()
                Next
            Next
            ' save the application  
            workbook.SaveAs("D:\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            ' Exit from the application  
            app.Quit()
    

    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.

    • Marked as answer by Mike Zed Tuesday, September 12, 2017 2:30 PM
    Tuesday, September 12, 2017 10:14 AM
    Moderator

All replies

  • Hello everyone
    I'm making a simple app.
    on the form I added 3 datagridview.

    dgv = datagridview

    dgv1 data I get from excel database
    dgv2 data I get from textbox input
    dgv3 the data i want to get from dgv1 and dgv2
    where on dgv3 the data in remove duplicate.
    and dgv3 will be exported to excel.

    for dgv1 and dgv2 can already run.
    but dgv 3 I do not know the code yet

    each dgv has 4 columns.

    please input and the solution from the masters here.
    Thank you very much.
    :)

    Warm Regards,
    Mike Zed
    • Merged by KareninstructorMVP, Moderator Tuesday, September 12, 2017 11:03 AM Same topic so its considered a duplicate post which is frowned upon here
    Wednesday, September 6, 2017 12:29 PM
  • How much data, rows and columns?

    Any formatting needed?

    What version of Excel format e.g. .xls or .xlsx (or both)?

    Have you listed all requirements, anything else to add to your question?


    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

    Tuesday, September 12, 2017 10:08 AM
    Moderator
  • Hi Mike,

    According to your description, you want to combine 2 datagridview to 1 datagridview, you can refer to this link:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/0a3eba75-9fe1-4332-89f0-577bdadbe94c/using-multiple-datagridview?forum=vbgeneral#60de28a6-1096-419d-b3be-ed8b97a36fd0

    About exporting data from datagridview into excel, please refer to the following code.

      Dim app As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
            ' creating new WorkBook within Excel application  
            Dim workbook As Microsoft.Office.Interop.Excel._Workbook = app.Workbooks.Add(Type.Missing)
            ' creating new Excelsheet in workbook  
            Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
            ' see the excel sheet behind the program  
            app.Visible = True
            ' get the reference of first sheet. By default its name is Sheet1.  
            ' store its reference to worksheet  
            worksheet = workbook.Sheets("Sheet1")
            worksheet = workbook.ActiveSheet
            ' changing the name of active sheet  
            worksheet.Name = "Exported from gridview"
            ' storing header part in Excel  
            For i As Integer = 1 To DataGridView1.Columns.Count
                worksheet.Cells(1, i) = DataGridView1.Columns(i - 1).HeaderText
            Next
            ' storing Each row and column value to excel sheet  
            For i As Integer = 0 To DataGridView1.Rows.Count - 2
                For j As Integer = 0 To DataGridView1.Columns.Count - 1
                    worksheet.Cells(i + 2, j + 1) = DataGridView1.Rows(i).Cells(j).Value.ToString()
                Next
            Next
            ' save the application  
            workbook.SaveAs("D:\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            ' Exit from the application  
            app.Quit()
    

    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.

    • Marked as answer by Mike Zed Tuesday, September 12, 2017 2:30 PM
    Tuesday, September 12, 2017 10:14 AM
    Moderator
  • I have merged your two threads as they are on the same topic. Also, you didn't respond to my questions. Thanks for understanding.

    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

    Tuesday, September 12, 2017 11:05 AM
    Moderator
  • Dear Cherry,

    i get an error msg like thid;
    "Additional information: Object reference not set to an instance of an object."
    Wednesday, September 13, 2017 2:49 PM
  • Hi Mike,

    Can you tell me where you encounter this issue, I check my code and find that I don't detect DataGridView.value is valid or not, please try this.

     For i As Integer = 1 To DataGridView1.Columns.Count
                worksheet.Cells(1, i) = DataGridView1.Columns(i - 1).HeaderText
            Next
            ' storing Each row and column value to excel sheet  
            For i As Integer = 0 To DataGridView1.Rows.Count - 2
                For j As Integer = 0 To DataGridView1.Columns.Count - 1
                    If DataGridView1(i, j).Value IsNot Nothing Then
                        worksheet.Cells(i + 2, j + 1) = DataGridView1.Rows(i).Cells(j).Value.ToString()
                    End If
                Next
            Next

    If not this issue, please provide some code here and point out where the problem is encountered.

    Thanks for your understanding.

    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.

    Thursday, September 14, 2017 1:30 AM
    Moderator
  • For Datagridview to Excel, try this.

    Imports System.Data.SqlClient
    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            Dim connectionString As String = "Data Source=.;Initial Catalog=pubs;Integrated Security=True"
            Dim sql As String = "SELECT * FROM Authors"
            Dim connection As New SqlConnection(connectionString)
            Dim dataadapter As New SqlDataAdapter(sql, connection)
            Dim ds As New DataSet()
            connection.Open()
            dataadapter.Fill(ds, "Authors_table")
            connection.Close()
            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "Authors_table"
    
        End Sub
    
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
    
            Dim i As Int16, j As Int16
    
            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
    
    
            For i = 0 To DataGridView1.RowCount - 2
                For j = 0 To DataGridView1.ColumnCount - 1
                    xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value.ToString()
                Next
            Next
    
            xlWorkBook.SaveAs("c:\vb.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _
             Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
            xlWorkBook.Close(True, misValue, misValue)
            xlApp.Quit()
    
            releaseObject(xlWorkSheet)
            releaseObject(xlWorkBook)
            releaseObject(xlApp)
    
            MessageBox.Show("Over")
        End Sub
    
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString())
            Finally
                GC.Collect()
            End Try
        End Sub
    
    End Class

    You can find several related, and useful, topics from this link.

    http://vb.net-informations.com/datagridview/vb.net_datagridview_export.htm


    MY BOOK

    Friday, September 15, 2017 3:21 AM