IMPORTAR / EXPORTAR DATOS EXCEL A DATASET / DATASET A EXCEL

Private Shared Function Import(ByRef initialIndex As Integer) As DataSet

        'Dim connectionString As String = String.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;;HDR=YES;", _ExcelFile_Source)

        Dim connectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", _ExcelFile_Source)

        For Each sheetName In GetExcelSheetNames(connectionString)

            Using con As New OleDbConnection(connectionString)

                Dim dataTable = New DataTable()

                Dim query As String = String.Format("SELECT * FROM [{0}]", sheetName)

                con.Open()

                Dim adapter As New OleDbDataAdapter(query, con)

                adapter.Fill(dataTable)

               

                dataTable.AcceptChanges()

                _ExcelDataset.Tables.Add(dataTable)

            End Using

        Next

        Return _ExcelDataset

    End Function

Public Sub Export()

        Dim Excel As Object = CreateObject("Excel.Application")

        Dim wBook As Microsoft.Office.Interop.Excel.Workbook

        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

        wBook = Excel.Workbooks.Add()

        wSheet = wBook.ActiveSheet()

        Dim _sheetname As String = ""

        Try

            Dim dt As System.Data.DataTable = _ExcelDataset.Tables(0)

            Dim dc As System.Data.DataColumn

            Dim dr As System.Data.DataRow

            Dim colIndex As Integer = 0

            Dim rowIndex As Integer = 0

            For Each dc In dt.Columns

                colIndex = colIndex + 1

                Excel.Cells(1, colIndex) = dc.ColumnName

            Next

            For Each dr In dt.Rows

                rowIndex = rowIndex + 1

                colIndex = 0

                For Each dc In dt.Columns

                    colIndex = colIndex + 1

                    Excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

                Next

            Next

        Catch ex As Exception

            MsgBox("Error de conversión.", MsgBoxStyle.Critical, "Error")

        End Try

        Try

            wSheet.Name = "Resultado"

            _sheetname = wSheet.Name

        Catch ex As Exception

            MsgBox("Error de formato.", MsgBoxStyle.Critical, "Error")

        End Try

        Try

            'Check if file exists if not, save file

            Dim button As DialogResult

            If System.IO.File.Exists(_ExcelFile_Result) Then

                button = MessageBox.Show _

                   ("Ya existe el archivo " & _ExcelFile_Result & ", ¿Quieres reemplazarlo?",

                     "¡Ojo!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning,

                    MessageBoxDefaultButton.Button1)

                If button = System.Windows.Forms.DialogResult.Yes Then

                    Excel.DisplayAlerts = False

                    wBook.SaveAs(_ExcelFile_Result, True)

                    MsgBox("El archivo se ha guardado correctamente.")

                Else

                    Dim i As String

                    _sheetname = ""

                    _sheetname = "Result.xls"

                    Dim _path As String = System.IO.Path.GetFullPath(_ExcelFile_Result)

                    i = InputBox("Informa el nombre de archivo", "", _sheetname, 200, 200)

                    _ExcelFile_Result = _path & "\" & i

                    wBook.SaveAs(_ExcelFile_Result, True)

                    MsgBox("El archivo se ha guardado correctamente en " & _path)

                End If

            End If

        Catch ex As Exception

            MsgBox("Error a guardar el archivo. Revise la direccion", MsgBoxStyle.Exclamation, "Error")

            wSheet = Nothing

            wBook = Nothing

            Excel.Quit()

            Excel = Nothing

            GC.Collect()

        End Try

        Excel.Visible = True

        GC.Collect()

    End Sub

Private Shared Function GetExcelSheetNames(connectionString As String) As String()

        Dim con As OleDbConnection = Nothing

        Dim dt As DataTable = Nothing

        con = New OleDbConnection(connectionString)

        con.Open()

        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

        If dt Is Nothing Then

            Return Nothing

        End If

        Dim excelSheetNames As [String]() = New [String](dt.Rows.Count - 1) {}

        Dim i As Integer = 0

        For Each row As DataRow In dt.Rows

            excelSheetNames(i) = row("TABLE_NAME").ToString()

            i += 1

        Next

        Return excelSheetNames

    End Function