Resolved Iterating through and writing to excel speed issue

ConsKa

Well-known member
Joined
Dec 11, 2020
Messages
140
Programming Experience
Beginner
This is using Microsoft Office Interop.

I have a Dictionary with two data types, one data type matches an entry in a master excel. I am currently using a For loop to loop over the entries, and where there is a match, write the Dictionary data in the two cells to the left. I understand that I am essentially reading over 300+ entries each time but I do not know a better way of identify the connected cell.

The code I have works, but it is quite slow when it comes to writing the data - and I feel there is probably a better way of doing this?

Any tips for speeding this up?

Here is the code I have currently.

C#:
            _Excel.Application excelApp = new _Excel.Application();
            _Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath);
            _Excel.Worksheet sheet = excelWorkbook.Sheets[1];
            _Excel.Range xlRange = sheet.UsedRange;
            int colCount = xlRange.Columns.Count;
            int rowCount = xlRange.Rows.Count;
            sheet.Columns["A"].Insert();
            sheet.Columns["B"].Insert();
            sheet.Cells[1, 1] = "File Name";
            sheet.Cells[1, 2] = "MetaData Verba ID";

            var len = dict.Count;
            double bar = 100 / len;

            foreach (var data in dict.Keys)
            {
                for (int k = 1; k <= rowCount; k++)
                {
                    if (xlRange.Cells[k, "A"].Value.ToString() == data)
                    {
                        sheet.Cells[k, "B"] = data;
                        sheet.Cells[k, "A"] = dict[data];
                    }
                }
                (sender as BackgroundWorker).ReportProgress((int)bar);
                bar = bar + 100 / len;

            }
 
Solution
Also, you are going about things the wrong way with this:
C#:
foreach (var data in dict.Keys)
{
    for (int k = 1; k <= rowCount; k++)
    {
        if (xlRange.Cells[k, "A"].Value.ToString() == data)
        {
            sheet.Cells[k, "B"] = data;
            sheet.Cells[k, "A"] = dict[data];
        }
    }
}
specially given that you already have a dictionary available.

The approach you have above is that you grab a word from the dictionary, and then you go through the entire book looking for that just one word so that you can replace it with something else. Then you pick up the next word from the dictionary, and then go through the entire book again. And then you do it again with the next word. Wouldn't it be better if you just...

JohnH

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
1,197
Location
Norway
Programming Experience
10+

JohnH

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
1,197
Location
Norway
Programming Experience
10+
Thread moved to Third Party Products forum.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
3,388
Location
Chesapeake, VA
Programming Experience
10+
Also, you are going about things the wrong way with this:
C#:
foreach (var data in dict.Keys)
{
    for (int k = 1; k <= rowCount; k++)
    {
        if (xlRange.Cells[k, "A"].Value.ToString() == data)
        {
            sheet.Cells[k, "B"] = data;
            sheet.Cells[k, "A"] = dict[data];
        }
    }
}
specially given that you already have a dictionary available.

The approach you have above is that you grab a word from the dictionary, and then you go through the entire book looking for that just one word so that you can replace it with something else. Then you pick up the next word from the dictionary, and then go through the entire book again. And then you do it again with the next word. Wouldn't it be better if you just went through the book once and let the dictionary look up what the replacement should be? Something like:

C#:
for (int k = 1; k <= rowCount; k++)
{
    var key = xlRange.Cells[k, "A"].Value.ToString();
    if (dict.ContainsKey(key))
    {
        sheet.Cells[k, "B"] = key;
        sheet.Cells[k, "A"] = dict[key];
    }
}
 
Solution

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
3,388
Location
Chesapeake, VA
Programming Experience
10+
As a quick aside, there is something that your old code handles, that my new code does not. In your old code if you had a dictionary that looks like:
C#:
["Foo"] = "Bar"
["Bar"] = "Boo"

and the Excel Sheet looked like:
C#:
     A   B
1 Foo

Your code would result in:
C#:
     A   B
1 Boo Bar

but my code would result in:
C#:
     A   B
1 Bar Foo
 

ConsKa

Well-known member
Joined
Dec 11, 2020
Messages
140
Programming Experience
Beginner
Wow that went from a 3 min job to an instantaneous output, thank you.

And no, the Key in the Dictionary is a Unique Identifier and the Value is a FileName that will never be the same as the UID....so thanks for highlighting but shouldn't impact on this task, appreciate the heads up, will commit ContainsKey to memory banks!
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
3,388
Location
Chesapeake, VA
Programming Experience
10+

ConsKa

Well-known member
Joined
Dec 11, 2020
Messages
140
Programming Experience
Beginner
You should check out TryGetValue() as well.

Will add it to my toolbox, in this case, the UID in the dictionary is in the Master spreadsheet there are no extras and none missing (or shouldn't be). The only data that is different are the values that I create that I then want to bring into the master.

Essentially the Master is a merge of all of the smaller files in a previous state.

I thought about creating two lists, and just ordering them by the UID, then writing them together - I think the result would be exactly the same....but I wanted to have the check to make sure that nothing had gone wrong...and this way, if I have a blank space....something has gone wrong and it is easy to see if something has gone wrong.

Otherwise I was left with the prospect of reading very long UID numbers (like MD5 hash numbers) and trying to see if they were different.
 
Top Bottom