Fastest way to export from gridView to c#

Question

0.00/5 (No votes)

See more:

Hello everyone

I'm searching for an efficient way to export data from a datagridview to an excel sheet. This is the current algorithm i'm working with:

for (int i = 0; i < dgvResult.RowCount; i++)
{
    for (int j = 0; j < dgvResult.ColumnCount; j++)
    {
        DataGridViewCell cell = dgvResult[j, i];
        sheet.Cells[i + 2, j + 1] = cell.Value;
   }
}

This method(nested loops) is very slow as my program will be dealing with a huge database. I need an alternative method that won't keep me waiting too much while the excel sheet is saving.

Any suggestions?

Posted 12-Jul-10 3:20am

deadwood88

Add a Solution

4 solutions

Solution 2

C#

for (int i = 0; i < table.Rows.Count; i++) {
    for (int j = 0; j < table.Columns.Count; j++) {
        arr[i, j] = table.Rows[j].ToString();
        MessageBox.Show(arr[i, j].ToString());
    }
}

The box is printing "System.Data.DataRow" instead of the expected value.

Posted 12-Jul-10 21:05pm

deadwood88

Comments

Nuri Ismail 13-Jul-10 4:27am

See my answer below, because comments are not suitable for posting code fragments and links.
Also it is better to update your original question (by using "Improve Question" link) instead of posting fake answers for such clarifications. :)

Solution 3

You have to change your code like this:

C#

for (int i = 0; i < table.Rows.Count; i++) {
    for (int j = 0; j < table.Columns.Count; j++) {
        arr[i, j] = table.Rows[i][j].ToString(); // Change here!!!
        MessageBox.Show(arr[i, j].ToString());
    }
}

Give it a try. Also this is not the fastest way. If the performance is still not satisfactory you can improve performance by caching the used data columns: see this[^] post for an idea. :)

Posted 12-Jul-10 22:00pm

Nuri Ismail

Comments

deadwood88 13-Jul-10 4:49am

It worked, thank you again. The performance is satisfactory :)

Nuri Ismail 13-Jul-10 5:11am

You are welcome! :)

Solution 4

Hello again :)

I tried the last algorithm u offered me in the attempt to export faster.

This is what I wrote:

C#

//Storing the columns of my table in an array of Columns
for (int i = 0; i < table.Columns.Count; i++) {
    col = table.Columns[i];
    obj[i] = col;
}

C#

int j = 0;
foreach (DataRow dr in table.Rows) {
    for (int i = 0; i < table.Columns.Count; i++) {
        arr[j, i] = dr[obj[i]];
    }
    j++;
}

The difference between this algorithm and the one written in the previous post is minimal. Am I using it wrong?

Thank you.

Posted 26-Jul-10 22:49pm

deadwood88

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)

Nuri Ismail · Accepted Answer · 2010-07-12T03:32:00

Solution 1

You can collect your values in array and push this array to Excel at once. See this[^] thread for details. The second answer (from Lesmo) contains a C# implementation of this idea. :)

Posted 12-Jul-10 3:32am

Nuri Ismail

Updated 12-Jul-10 3:55am

v2

Comments

deadwood88 12-Jul-10 10:07am

The application is working with significant improvement. Thank You!!
Just one error, the cells in the excel sheet are showing "System.String[,]" instead of the expected values. Any clue why?

Nuri Ismail 12-Jul-10 10:38am

Hmmm...
Try using a 2-dimensional array of objects like: object[,] arr = new object[Rows.Count, Columns.Count];
Also have a look at this blog entry: http://blogs.msdn.com/b/eric_carter/archive/2004/05/04/126190.aspx
If the result remains the same, please edit your original question and add the relevant parts from your new code.
:)