Exporting SQL code to Excel

[M8KWR]

Hi,

I hope someone can offer me some assistance in this query.

I am trying to export to excel from SQL Code.

I have got some code at the moment, but it takes about 50 minutes to export 24,000 rows. using this code

    Public Overloads Shared Function fncExcelExport(ByVal datasource As String, ByVal database As String, ByVal userid As String, ByVal password As String, ByVal strSql As String, ByVal Filename As String)
        Dim returnvalue As String = String.Empty
        Dim returnex As Exception

        Static Excel As New Excel.Application
        Try
            Dim dsExcelExport As New System.Data.DataSet
            Dim daExcelExport As System.Data.SqlClient.SqlDataAdapter
            Dim intColumn, intRow, intColumnValue As Integer
            Dim strExcelFile As String
            Dim conCurrent As New SqlClient.SqlConnection
            conCurrent.ConnectionString = "data source=" & datasource & ";initial catalog=" & database & ";User ID=" & userid & ";Password=" & password
            daExcelExport = New System.Data.SqlClient.SqlDataAdapter(strSql, conCurrent)
            daExcelExport.Fill(dsExcelExport)

            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()


                'For displaying the column name in the the excel file.            
                For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
                    .Cells(1, intColumn + 1).Value = dsExcelExport.Tables(0).Columns(intColumn).ColumnName.ToString

                Next


                'For displaying the column value row-by-row in the the excel file.            
                For intRow = 1 To dsExcelExport.Tables(0).Rows.Count
                    For intColumnValue = 0 To dsExcelExport.Tables(0).Columns.Count - 1
                        .Cells(intRow + 1, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows(intRow - 1).ItemArray(intColumnValue).ToString
                    Next

                Next
                'strFileName = InputBox("Please enter the file name.", "Swapnil")            

                strExcelFile = Filename
                .ActiveWorkbook().SaveAs(strExcelFile)
                .ActiveWorkbook.Close()

            End With
            GC.Collect()
            Excel.Quit()
            returnvalue = "File exported sucessfully."

        Catch ex As System.Runtime.InteropServices.COMException
            returnex = ex
            Excel.Quit()
            Excel.Application.Quit()
        Catch ex As SqlClient.SqlException
            returnex = ex
            Excel.Quit()
            Excel.Application.Quit()
        Catch ex As IndexOutOfRangeException
            returnex = ex
            Excel.Quit()
            Excel.Application.Quit()
        Catch ex As InvalidCastException
            returnex = ex
            Excel.Quit()
            Excel.Application.Quit()
        Catch ex As Exception
            returnex = ex
            Excel.Quit()
            Excel.Application.Quit()



        End Try
        If returnex IsNot Nothing Then
            Excel.Quit()
            Return returnex
        Else
            Excel.Quit()
            Return (returnvalue)
        End If
    End Function

If i do a dts straight from SQL this takes about 7 minutes.

I think i need to use the Microsoft.Jet.OLEDB.4.0 drivers to allow me to do this.

Is this the best way or is there an easier way of doing this.

many thanks for any help in advance.

 

[bboffin]

Try xPort Tools from

http://xport.mvps.org.

There's a fully functioning free trial available.

It'll probably take only a few seconds to run as it doesn't use Excel.

I had a similar job that came down from around an hour to less than 2 minutes using this component.

Usual disclaimers.



Bob Boffin

 

[dglienna]

Before you start adding rows, turn off worksheet updating.
Update the cells, and then update it. It will be a lot faster.

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[techsmith]

There is a much faster way to get data into an Excel file using the Excel range and an array.

You can build an array easily from a datatable

Dim _r As Integer = _tbl.Rows.Count
Dim _c As Integer = _tbl.Columns.Count
Dim _arr(_r, _c) As String

'add header
For i As Integer = 0 To _c - 1
 _arr(0, i) = _tbl.Columns(i).ColumnName
Next

'add cells
For ir As Integer = 1 To _r
 For ic As Integer = 0 To _c - 1
  _arr(ir, ic) = _tbl.Rows(ir - 1)(ic).ToString
 Next
Next

'fill range
_ws.Range("A1", _ws.Cells(_r + 1, _c)).Value2 = _arr

 

[AlexCuse]

You can also use ADO.net to insert rows to an excel spreadsheet. I have some code to do this at home (you do have to insert it one row at a time).

If the XML spreadsheet format is acceptable, I've found building one of these is the way to go. There is a bit of a learning curve involved, but its not too hard, its' not dependent on Excel being installed on the machine, and it is very fast.

Here is a good tool I use for this:

http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx

Hope it helps,

Alex





[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page