0

hi..

i just made two function related to excel .

1. Merging all sheet of excel as tables in single Dataset
2. Exporting a dataset to Excel File .(all the tables of dataset as sheets)

i just wanted to share.. i hope it becomes helpful to you.


ps: Dont forget to add references to

Imports Microsoft.Office.Interop.Excel
Imports System.Data.OleDb

Edited by sandeepparekh9: n/a

'The Merging Function
    Public Sub MergeExcelInDataSet(ByVal strExcelFile As String, ByRef ds As DataSet)
        Dim objExcel As Microsoft.Office.Interop.Excel.Application
        Dim objWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim tmpWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open(strExcelFile)

        Dim strSheetName(objWorkBook.Worksheets.Count - 1) As String
        Dim i As Integer = 0
        For Each tmpWorkSheet In objWorkBook.Worksheets
            strSheetName(i) = tmpWorkSheet.Name
            i += 1
        Next

        objExcel.Quit()
        releaseObject(objExcel)
        releaseObject(objWorkBook)
        releaseObject(tmpWorkSheet)


        Dim conn As New System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", strExcelFile))
        For Each str As String In strSheetName
            Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [" & str & "$]", conn)
            da.Fill(ds, str)
        Next

    End Sub


'The Exporting Function
    Public Sub ExportDatasetToExcel(ByVal ds As DataSet, ByVal strExcelFile As String)

        Dim conn As New System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", strExcelFile))
        conn.Open()

        Dim strTableQ(ds.Tables.Count) As String

        Dim i As Integer = 0

        'making table query
        For i = 0 To ds.Tables.Count - 1

            strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("

            Dim j As Integer = 0
            For j = 0 To ds.Tables(i).Columns.Count - 1
                Dim dCol As DataColumn
                dCol = ds.Tables(i).Columns(j)
                strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255) , "
            Next
            strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
            strTableQ(i) &= ")"

            Dim cmd As New OleDbCommand(strTableQ(i), conn)
            cmd.ExecuteNonQuery()

        Next

        'making insert query
        Dim strInsertQ(ds.Tables.Count - 1) As String
        For i = 0 To ds.Tables.Count - 1
            strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
            For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & " , "
            Next
            strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
            strInsertQ(i) &= ")"
        Next

        'Now inserting data
        For i = 0 To ds.Tables.Count - 1
            For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
                Dim cmd As New OleDbCommand(strInsertQ(i), conn)
                For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                    cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(), ds.Tables(i).Rows(j)(k).ToString())
                Next
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()

            Next
        Next
    End Sub

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


Example:
        Dim dsFinal As New DataSet
        MergeExcelInDataSet("d:\Book1.xls", dsFinal)
        MergeExcelInDataSet("d:\Book2.xls", dsFinal)
        MergeExcelInDataSet("d:\Book3.xls", dsFinal)
        MergeExcelInDataSet("d:\Book4.xls", dsFinal)
        MergeExcelInDataSet("d:\Book5.xls", dsFinal)
        'all the excels are in dsFinal dataset now
        'we will export this dataset to excel
        ExportDatasetToExcel(dsFinal, "d:\my.xls")
2
Contributors
2
Replies
8
Views
5 Years
Discussion Span
Last Post by sandeepparekh9
0

Hi,

I want to merge data from multiple excel files in to a single file, and in the final file. if the data is common between any of the work book, then only one will be pasted in the final workbook.(Eg, if name-a is present in more than one workbook, then in the final sheet name-a will apear only once)


can you help me with this???????????

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.