Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export dataset to excel running forever.

Posted on 2007-04-04
10
Medium Priority
?
249 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 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
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

604 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