根据数据源和地址生成EXCEL

Public Sub Load_Excel_Details(ByVal fileLPATH As String, ByVal dt As DataTable)

        'Extracting from database

        Dim col, row As Integer

 

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

        If Excel Is Nothing Then

            MsgBox("It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.Critical)

            Return

        End If

 

        'Export to Excel process

        Try

            With Excel

                .SheetsInNewWorkbook = 1

                .Workbooks.Add()

                .Worksheets(1).Select()

 

                Dim i As Integer = 1

                For col = 0 To dt.Columns.Count - 1

                    .cells(1, i).value = dt.Columns(col).ColumnName

                    .cells(1, i).EntireRow.Font.Bold = True

                    i += 1

                Next

                i = 2

                Dim k As Integer = 1

                For col = 0 To dt.Columns.Count - 1

                    i = 2

                    For row = 0 To dt.Rows.Count - 1

                        .Cells(i, k).Value = dt.Rows(row).ItemArray(col)

                        i += 1

                    Next

                    k += 1

                Next

                ' filename = "c:\File_Exported" & Format(Now(), "dd-MM-yyyy_hh-mm-ss") & ".xls"

                .ActiveCell.Worksheet.SaveAs(fileLPATH)

            End With

            Excel.Quit()

            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)

            GC.Collect()

            'Excel = Nothing

            'MsgBox("Data's are exported to Excel Succesfully in '" & fileLPATH & "'", MsgBoxStyle.Information)

 

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

 

        ' The excel is created and opened for insert value. We most close this excel using this system

        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")

        For Each i As Process In pro

            i.Kill()

        Next

 

    End Sub