none
Combine all Sheets in Excel RRS feed

  • Question

  • Hi All,

    I want combine all sheet in excel Sheet1 to Sheet30.How to do it.It possible if we have 1 to 3 sheet write manually.

     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim _filename As String = "C:\Users\Uset\Documents\Book3.xlsx"
    
            Dim _conn As String
            _conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _filename & ";Extended Properties='Excel 12.0;IMEX=1;'"
    
            Dim _connection As OleDbConnection = New OleDbConnection(_conn)
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim _command As OleDbCommand = New OleDbCommand()
    
            _command.Connection = _connection
            _command.CommandText = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union all SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"
            da.SelectCommand = _command
    
            Try
                da.Fill(ds1, "Sheet1" & "Sheet2")
                MessageBox.Show("The import is complete!")
                Me.DataGridView1.DataSource = ds1
                Me.DataGridView1.DataMember = "Sheet1" & "Sheet2"
    
            Catch e1 As Exception
                MessageBox.Show("Import Failed, correct Column name in the sheet!")
            End Try
        End Sub

    Thank

    Saturday, March 23, 2019 7:25 AM

Answers

  • Hi,
    you can use DataAdapter.Update with Sheet3 like this:

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form10
    
      Private WithEvents Button2 As New Button With {.Text = "Load Data", .Dock = DockStyle.Top}
      Private WithEvents Button3 As New Button With {.Text = "Save Data", .Dock = DockStyle.Top}
      Private DataGridView1 As New DataGridView With {.Dock = DockStyle.Fill}
      Private ds1 As New DataSet
      Private _filename As String = "Book1.xlsx"
    
      Private Sub Form10_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {DataGridView1, Button3, Button2})
      End Sub
    
      Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim p = Path.Combine(Environment.CurrentDirectory, _filename)
        Dim _conn = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={p};Extended Properties='Excel 12.0;IMEX=1;'"
        Dim SqlSelect = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union all SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"
        Try
          Using da As New OleDbDataAdapter(SqlSelect, _conn)
            da.AcceptChangesDuringFill = False
            da.Fill(ds1, "Sheet1" & "Sheet2")
            MessageBox.Show("The import is complete!")
            Me.DataGridView1.DataSource = ds1
            Me.DataGridView1.DataMember = "Sheet1" & "Sheet2"
          End Using
        Catch e1 As Exception
          MessageBox.Show("Import Failed, correct Column name in the sheet!")
        End Try
      End Sub
    
      Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim p = Path.Combine(Environment.CurrentDirectory, _filename)
        Dim _conn = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={p};Extended Properties='Excel 12.0;'"
        Dim SqlSelect = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet3$]"
        Try
          Using da As New OleDbDataAdapter(SqlSelect, _conn)
            Dim cb As New OleDbCommandBuilder(da)
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            da.Update(ds1, "Sheet1" & "Sheet2")
          End Using
          MessageBox.Show("The export is complete!")
        Catch e1 As Exception
          MessageBox.Show("Export Failed, correct Column name in the sheet!" & vbNewLine & e1.Message)
        End Try
      End Sub
    
    End Class


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    • Edited by Peter Fleischer Saturday, March 23, 2019 1:29 PM
    • Marked as answer by mipakteh Saturday, March 23, 2019 3:45 PM
    Saturday, March 23, 2019 1:27 PM

All replies

  • Hello,

    This can be done with Excel automation which would be a last resort for me or to use OleDb to either do a SQL INSERT for each row in the DataTable or to use a SELECT INTO via OleDb which is another choice but the simple method is to use SpreadSheetLight which is a free library for Excel, lightweight and absolutely free.

    To use SpreadSheetLight, right click on Solution Explorer, select manage NuGet packages, click on the browse tab, enter in the input SpreadSheetLight. Select the project to install and press the install button. Optionally install via the NuGet package manager console (see this page).

    On SpreadSheetLight home page, bottom left side of page you can download the help file.

    So how to merge? The following is a base pattern which if executed multiple times will append rather than overwrite existing content.

    Note that this operation is super fast as there is no automation involved. Bone up on the documentation and you can easily style and format cell data.

    Imports SpreadsheetLight
    Public Class ImportDataTable
        ''' <summary>
        ''' Import DataTable into pMergeSheetName with option to
        ''' include each DataColumn as header
        ''' </summary>
        ''' <param name="pFileName">Excel file name and path</param>
        ''' <param name="pMergeSheetName">WorkSheet to merge too</param>
        ''' <param name="pDataTable">DataTable to use for merge</param>
        ''' <param name="pIncludeHeader">True to include DataColumn names for header</param>
        Public Sub Merge(
            pFileName As String,
            pMergeSheetName As String,
            pDataTable As DataTable,
            pIncludeHeader As Boolean)
    
            ' Open the Excel file to the sheet we want to merge the DataTable into
            Using doc As New SLDocument(pFileName, pMergeSheetName)
                ' get the last row, increment by 1 which is used to set where we do the insert at
                Dim lastRow = doc.GetWorksheetStatistics().EndRowIndex
                lastRow = If(lastRow = -1, 1, lastRow + 1)
                ' perform the merge/import
                doc.ImportDataTable(lastRow, 1, pDataTable, pIncludeHeader)
                ' Save the Excel file
                doc.Save()
            End Using
    
        End Sub
    
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, March 23, 2019 11:40 AM
    Moderator
  • Thank Karen for your feedback.

    Try to do as you said but fail.

    1.Download file'.

    2.go t Solution Explore In VB.

    Add Reffrence.

    3.Search NuGet packages but not find.

    Saturday, March 23, 2019 1:07 PM
  • Hi,
    you can use DataAdapter.Update with Sheet3 like this:

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form10
    
      Private WithEvents Button2 As New Button With {.Text = "Load Data", .Dock = DockStyle.Top}
      Private WithEvents Button3 As New Button With {.Text = "Save Data", .Dock = DockStyle.Top}
      Private DataGridView1 As New DataGridView With {.Dock = DockStyle.Fill}
      Private ds1 As New DataSet
      Private _filename As String = "Book1.xlsx"
    
      Private Sub Form10_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {DataGridView1, Button3, Button2})
      End Sub
    
      Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim p = Path.Combine(Environment.CurrentDirectory, _filename)
        Dim _conn = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={p};Extended Properties='Excel 12.0;IMEX=1;'"
        Dim SqlSelect = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union all SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"
        Try
          Using da As New OleDbDataAdapter(SqlSelect, _conn)
            da.AcceptChangesDuringFill = False
            da.Fill(ds1, "Sheet1" & "Sheet2")
            MessageBox.Show("The import is complete!")
            Me.DataGridView1.DataSource = ds1
            Me.DataGridView1.DataMember = "Sheet1" & "Sheet2"
          End Using
        Catch e1 As Exception
          MessageBox.Show("Import Failed, correct Column name in the sheet!")
        End Try
      End Sub
    
      Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim p = Path.Combine(Environment.CurrentDirectory, _filename)
        Dim _conn = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={p};Extended Properties='Excel 12.0;'"
        Dim SqlSelect = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet3$]"
        Try
          Using da As New OleDbDataAdapter(SqlSelect, _conn)
            Dim cb As New OleDbCommandBuilder(da)
            cb.QuotePrefix = "["
            cb.QuoteSuffix = "]"
            da.Update(ds1, "Sheet1" & "Sheet2")
          End Using
          MessageBox.Show("The export is complete!")
        Catch e1 As Exception
          MessageBox.Show("Export Failed, correct Column name in the sheet!" & vbNewLine & e1.Message)
        End Try
      End Sub
    
    End Class


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    • Edited by Peter Fleischer Saturday, March 23, 2019 1:29 PM
    • Marked as answer by mipakteh Saturday, March 23, 2019 3:45 PM
    Saturday, March 23, 2019 1:27 PM
  • Hello,

    With NuGet packages you DO NOT use add reference, instead when installing a package such as SpreadSheetLight NuGet adds the reference for you.

    Here are the steps

    Once pressing install a dialog appears, press ok. After it's done installing you will see the reference for SpreadSheetLight in the project reference folder.

    Now you can use the library.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, March 23, 2019 2:20 PM
    Moderator
  • Thank you very much peter...It work fine.

    1.  If I want write a sql code like this;

    Dim SqlSelect = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union all SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"

     change to separate

    Dim SqlSelect = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union all & _

    SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"

    How to write the correct code.

    2.  If I want to join sheet1 to sheet30 using code above.how


    • Edited by mipakteh Saturday, March 23, 2019 3:45 PM add
    Saturday, March 23, 2019 3:42 PM
  • Hi,
    it ist easier to add every sheet in a separate Fill-step. In this case you can add many sheets without problems:

      Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim p = Path.Combine(Environment.CurrentDirectory, _filename)
        Dim _conn = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={p};Extended Properties='Excel 12.0;IMEX=1;'"
        Try
          Using da As New OleDbDataAdapter("SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$]", _conn)
            da.AcceptChangesDuringFill = False
            da.Fill(ds1, "Sheet1" & "Sheet2")
          End Using
          Using da As New OleDbDataAdapter("SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]", _conn)
            da.AcceptChangesDuringFill = False
            da.MissingSchemaAction = MissingSchemaAction.Add
            da.Fill(ds1, "Sheet1" & "Sheet2")
          End Using
          Me.DataGridView1.DataSource = ds1
          Me.DataGridView1.DataMember = "Sheet1" & "Sheet2"
          MessageBox.Show("The import is complete!")
        Catch e1 As Exception
          MessageBox.Show("Import Failed, correct Column name in the sheet!")
        End Try
      End Sub


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Saturday, March 23, 2019 5:46 PM