System out of memory exception when loading an excel spreadsheet with 100000+ rows with 25 columns or more to a gridview

Question

1.00/5 (1 vote)

See more:

, +

Really need help on a project. i have to load a gridview with a huge amount of data 100k rows and about 27 columns. the datatable gives me a system out of memory exception error everytime i load it and then try to bind the gridview which is posted below.

What I have tried:

i have done research for about 2 weeks now and nothing has helped. can someone please give me a proper answer as to what i need to do?
The below is what i do to load the spreadsheet directly into a dataset which is giving me the system out of memory exception. But now i would like to use an access database.

VB

Public Function loadExcel()
        If GlobalVariable.outputSelectBackButtonFlag = True Then
            BindData()
        Else
            ''loadExcel Function to show the file to the gridView.
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As New System.Data.DataSet("TaskList")
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            Dim taskTable As New DataTable("TaskList")

            Dim filePath = Path.Combine(GlobalVariable.savedPath, GlobalVariable.excelFileName)
'Code to Use an Oledb Connection to get data from the Excel File
            If File.Exists(filePath) Then
                MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties='Excel 12.0 Xml;HDR=YES;';")
                MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
                DtSet = New System.Data.DataSet
                MyCommand.Fill(DtSet)
                GridView1.DataSource = DtSet.Tables(0)
                Session("TaskTable") = DtSet
                BindData()
                MyConnection.Close()
            Else
                Response.Redirect("UploadFile.aspx", True)
            End If
			Return Nothing
End Function

Posted 2-Mar-17 8:38am

JT1992

Updated 2-Mar-17 15:44pm

v5

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-03-02T09:41:00

The solution is simple: don't do it.
Think about what you are doing from a user point of view - what use is a table with 100,000 rows by 27 columns? What is the user supposed to do with that? How long do you think it is going to take him or her to find the info they need?

And it runs out of memory because every single cell is (at least) one separate window, with it's own window handle. So you are trying to create 2,700,000 new windows - and that is silly, slow, and too many for Windows to cope with.

Think about your application,and redesign to use paging, searching, filtering. Because if you don't, not only will it not work, but if it did your users will hate it with a passion.

Patrice T · Answer 2 · 2017-03-02T15:44:00

Solution 2

Quote:
i have to load a gridview with a huge amount of data 100k rows and about 27 columns.

Don't do it, think about it, how many clicks does it take to find a random row in this list ?
You should study about filters: The user give an hint about what he is looking for an you display a short list of useful rows.

Posted 2-Mar-17 15:44pm

Patrice T

Comments

JT1992 3-Mar-17 9:26am

but i have to display all 100000 rows and columns how is it possible to do this through my code above with paging. i am really confused

Graeme_Grant 3-Mar-17 9:33am

WinForms Datagrid Control, Windows Forms Grid Component, VirtualGrid |Telerik[^]

Patrice T 3-Mar-17 9:54am

Looks a good solution

JT1992 3-Mar-17 9:55am

but this requires a pricing? is there no other code way to be able to do this?

Graeme_Grant 3-Mar-17 10:03am

Syncfusion Windows Forms[^] - check out their Community License[^].