Export dataset to excel running forever.

Public Class ExportExcel
Export dataset to excel running forever.

I know there are more than 65000 rows.  I need to fix this function. Any help would be great.


        Public Function DataSetToExcel(ByVal dtSource As System.Data.DataSet, ByVal sFileName As String)

            Dim iRowCount As Integer = dtSource.Tables(0).Rows.Count

            Dim iColCount As Integer = dtSource.Tables(0).Columns.Count

            Dim oData(iRowCount, iColCount) As Object

            Dim iRow As Integer, iCol As Integer

            For iRow = 0 To iRowCount - 1

                For iCol = 0 To iColCount - 1

                    oData(iRow, iCol) = dtSource.Tables(0).Rows(iRow).Item(iCol)

                Next

            Next

            ' Start Excel and get Application object
            Dim oExcel As Excel.Application = New Excel.Application()
            oExcel.Visible = True  ' Make visible

            ' Get a new workbook
            Dim oBook As Excel._Workbook = CType(oExcel.Workbooks.Add(Missing.Value), Excel._Workbook)
            Dim oSheet As Excel._Worksheet = CType(oBook.ActiveSheet, Excel._Worksheet)

            Dim oRange As Excel.Range = oSheet.Range("A1")
            oRange = oRange.Resize(iRowCount, iColCount)
            oRange.Value = oData

            oSheet.SaveAs(sFileName)
            oExcel.Workbooks.Close()
            oExcel.Quit()

            oBook = Nothing
            oSheet = Nothing
            oExcel = Nothing
        End Function



    End Class
mathieu_cuprykAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iboutchkineCommented:
Your problem is that Excel does not deal well with such quantity of rows.  Just imagine that the user got Excel with 65000 rows. What is he going to do with it? I think that you have to redesign the whole app. Such quantity of records calls for the database - not Excel
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mathieu_cuprykAuthor Commented:
Can't I have multiple worksheets?
0
iboutchkineCommented:
yes you can. But what is wrong with database?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

mathieu_cuprykAuthor Commented:
Just the client would like to look at a few years of results.
0
iboutchkineCommented:
How do you thinkthe client will browse through 65000 rows. Is it more reasonable to create a front end with search utility to find the reasonable amount of records?
If you still want to use Excel, you can do it. But with such quantity of records it will be slow.
I think that this is not the right tool for it
0
mathieu_cuprykAuthor Commented:
With excel the user can do whatever he wants.
0
iboutchkineCommented:
Then go for it
0
mathieu_cuprykAuthor Commented:
User can open and edit /exfile------------------------------------------------------------------------------------------
0
iboutchkineCommented:
Before user can open it you have to populate Excel. Do you think user will wait half an hour to get it?
0
mathieu_cuprykAuthor Commented:
Yes for sure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.