Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Export dataset to excel running forever.

Posted on 2007-04-04
10
Medium Priority
?
251 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:mathieu_cupryk
  • 5
  • 5
10 Comments
 
LVL 28

Accepted Solution

by:
iboutchkine earned 1500 total points
ID: 18857092
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
 

Author Comment

by:mathieu_cupryk
ID: 18857253
Can't I have multiple worksheets?
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 18857269
yes you can. But what is wrong with database?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mathieu_cupryk
ID: 18857317
Just the client would like to look at a few years of results.
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 18857423
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
 

Author Comment

by:mathieu_cupryk
ID: 18857458
With excel the user can do whatever he wants.
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 18857505
Then go for it
0
 

Author Comment

by:mathieu_cupryk
ID: 18857547
User can open and edit /exfile------------------------------------------------------------------------------------------
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 18857567
Before user can open it you have to populate Excel. Do you think user will wait half an hour to get it?
0
 

Author Comment

by:mathieu_cupryk
ID: 18858364
Yes for sure.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question