C# How to improve loop populating Excel cells

Question

I'm mainly parsing large amounts of text from a text file and then populating it into an excel.

//populate into worksheet
for (int x = 0; x < rawLine.Length; x++)
{
    string[] tempLine = rawLine[x].Split(';');

    for (int y = 0; y < tempLine.Length; y++)
    {
        DateTime hour = Convert.ToDateTime(tempLine[6]);                            
        xlWorkSheet.Cells[y + 2, 1] = tempLine[0];
        xlWorkSheet.Cells[y + 2, 2] = tempLine[1];
        xlWorkSheet.Cells[y + 2, 3] = tempLine[2];
        xlWorkSheet.Cells[y + 2, 4] = tempLine[3];
        xlWorkSheet.Cells[y + 2, 5] = tempLine[4];
        xlWorkSheet.Cells[y + 2, 6] = tempLine[5];
        xlWorkSheet.Cells[y + 2, 7] = tempLine[6];
        xlWorkSheet.Cells[y + 2, 8] = tempLine[7];
        xlWorkSheet.Cells[y + 2, 9] = tempLine[8];
        xlWorkSheet.Cells[y + 2, 10] = tempLine[9];
        xlWorkSheet.Cells[y + 2, 11] = tempLine[10];
        xlWorkSheet.Cells[y + 2, 12] = hour.Hour;
        xlWorkSheet.Cells[y + 2, 13] = tempLine[8] == "0" ? "SAME" : tempLine[9];
    }

    Console.WriteLine("Current line = " + x + "\n");
}

Currently this code works, but it's just taking way too long. Is there anyway to speed it up? I have done some searching but found nothing much specific.

Thanks in advance.

Instead of setting each cell's value one by one, get a Range and set it's value to an array, take a look here: http://stackoverflow.com/questions/536636/write-array-to-excel-range — Gusman, Jan 05 '16 at 01:52
Yes, Gusman is right here. That's a common technique used in VBA to sort this issue... — PaulG, Jan 05 '16 at 08:57

score 1 · Answer 1 · answered Jan 05 '16 at 03:30

It will probably be a very small improvement, but this line:

DateTime hour = Convert.ToDateTime(tempLine[6]);

Should be moved outside the y loop because it doesn't depend on it.

Other than that, you should probably look into some way to set multiple cells at the same time--most of the time is probably spent doing round trips to Excel. (It looks like this is what @Gusman suggests in the comments).

@Mohit's answer is good too because it is much shorter and simpler.

score 0 · Answer 2 · answered Jan 05 '16 at 12:38

You can try:

//populate into worksheet
DateTime hour;
string[] tempLine;
StringBuilder output = new StringBuilder();
for (int x = 0; x < rawLine.Length; x++)
{
    tempLine = rawLine[x].Split(';');    
    for (int y = 0; y < tempLine.Length; y++)
    {
        hour = Convert.ToDateTime(tempLine[6]);                            
        xlWorkSheet.Cells[y + 2, 1] = tempLine[0];
        xlWorkSheet.Cells[y + 2, 2] = tempLine[1];
        xlWorkSheet.Cells[y + 2, 3] = tempLine[2];
        xlWorkSheet.Cells[y + 2, 4] = tempLine[3];
        xlWorkSheet.Cells[y + 2, 5] = tempLine[4];
        xlWorkSheet.Cells[y + 2, 6] = tempLine[5];
        xlWorkSheet.Cells[y + 2, 7] = tempLine[6];
        xlWorkSheet.Cells[y + 2, 8] = tempLine[7];
        xlWorkSheet.Cells[y + 2, 9] = tempLine[8];
        xlWorkSheet.Cells[y + 2, 10] = tempLine[9];
        xlWorkSheet.Cells[y + 2, 11] = tempLine[10];
        xlWorkSheet.Cells[y + 2, 12] = hour.Hour;
        xlWorkSheet.Cells[y + 2, 13] = tempLine[8] == "0" ? "SAME" : tempLine[9];
    }    
    output.AppendLine("Current line = " + x);
}
Console.WriteLine(output.ToString());

score -1 · Answer 3 · answered Jan 05 '16 at 01:58

-1

May be just to improve the loop you can write like this. This does not improve the performance but would look cleaner.

for (int x = 0; x < rawLine.Length; x++)
{
    string[] tempLine = rawLine[x].Split(';');
    for (int y = 0; y < tempLine.Length; y++)
    {
        DateTime hour = Convert.ToDateTime(tempLine[6]);
        for(int z=0; z<11; z++)
        {
            xlWorkSheet.Cells[y + 2, (z+1)] = tempLine[z];
        }
        xlWorkSheet.Cells[y + 2, 12] = hour.Hour;
        xlWorkSheet.Cells[y + 2, 13] = tempLine[8] == "0" ? "SAME" : tempLine[9];
    }
    Console.WriteLine("Current line = " + x + "\n");
}

answered Jan 05 '16 at 01:58

Mohit S

13,723
6
34
69

Because you have an inner for loop, a loop always is slower than direct code. – Gusman Jan 05 '16 at 12:19
@Gusman FYI. Your info is wrong about loops. It is as equivalent as writing the direct code. The code "inside" the loop is obeyed a specified number of times, or once for each of a collection of items, or until some condition is met. – Mohit S Jan 06 '16 at 01:44
FYI: you have no idea of programming, a for loop has a minimum of two instructions more per iteration than unrolled code, the per-iteration action (in this case y ++) and the per-iteration check (int this case z < 11) and also the branching is more expensive. – Gusman Jan 06 '16 at 16:29

C# How to improve loop populating Excel cells

3 Answers3