Solved

Export dataset to excel running forever.

Posted on 2007-04-04
10
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 28

Accepted Solution

by:
iboutchkine earned 500 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
Technology Partners: 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!

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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