Solved

Export dataset to excel running forever.

Posted on 2007-04-04
10
244 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 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
Industry Leaders: 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Title # Comments Views Activity
Footer for each row on Gridview 2 39
Expression Evaluater 3 45
Generate Unique ID in VB.NET 21 101
DataGridView / get bound table name? 8 36
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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