Question what i can do to enhance that code

[BrOkEn_iCe]

hi all i need some help here


i built this code to export some data from sql server to excel

Imports System.Data


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










Public Class Main


    Dim con As SqlConnection
    Dim sql As String
    Dim ds As New DataSet
    Dim maxrow As Integer






    Private Sub Main_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        OpenCon()


        If con Is Nothing OrElse con.State = ConnectionState.Closed Then
            MsgBox("closed")
            OpenCon()
        End If
    End Sub


    Private Sub OpenCon()
        con = New SqlConnection
        con.ConnectionString = "Data Source=7oda-pc;Initial Catalog=inv;Integrated Security=true;"
        'con.ConnectionString = "Data Source = ALLAH-88CB53937\Admin;Database Name=MedicalStore;Userid=Admin;password=maverick;"
        con.Open()


    End Sub


    Public Sub ExportEx_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportEx.Click


        sql = "select * from read_ex where group_code = " & Me.group_code.Text & "ORDER BY location, building_no,route_seq,gio_code,old_no "


        Dim cmd As New SqlCommand(sql, con)
        Dim da As New SqlDataAdapter(cmd)
        ds.Clear()
        da.Fill(ds, "loc")
        maxrow = ds.Tables("loc").Rows.Count
        MsgBox(maxrow)


        Load_Excel_Details()


    End Sub


    Private Sub Load_Excel_Details()
        'Extracting from database
        Dim filename As String
        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 ds.Tables(0).Columns.Count - 1
                    .cells(1, i).value = ds.Tables(0).Columns(col).ColumnName
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To ds.Tables(0).Columns.Count - 1
                    i = 2
                    For row = 0 To ds.Tables(0).Rows.Count - 1
                        .Cells(i, k).Value = ds.Tables(0).Rows(row).ItemArray(col)
                        i += 1
                    Next
                    k += 1
                Next
                filename = "d:\hadi_app\billing\" & Me.group_code.Text & "-" & Format(Now(), "dd-MM-yyyy") & ".xls"
                .ActiveCell.Worksheet.SaveAs(filename)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Data's are exported to Excel Succesfully in '" & filename & "'", 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


End Class

is there any better way to enhance this code to make it work faster caz it's 2 slow


and by the way i want to add a loop for the group_code that need in the sql command to get the value from table call groups and export all groups to excel


sorry my English is bad


and thx

 

[ss7thirty]

The main hold up here is probably Excel, I have run 100's of programs like this one. But one main difference in mine is that I always used DataReader to iterate through the data you may see some sort of speed increase from doing things that way. Also I have done a neat trick where you place the query in the Excel sheet (if you are doing no calculations in code as is the case here) and just refresh, wait, and save the sheet with the most current data, then e-mail or whatever you want to do with the sheet. Also, you may want to make your application a console app if there is no real need for a UI.

 

[cjard]

may be faster to write an xlsx xml file than create excel and fill it