Resolved Iterating through and writing to excel speed issue

[ConsKa]

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.

            _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;

            }

 

[JohnH]

Reading and writing single cells through interop is quite slow, instead you can read/write large ranges of cells in one go. The speed improvement is enormous. See Automate Excel by using Visual C# to fill or to obtain data by using arrays - Office

 

[JohnH]

Thread moved to Third Party Products forum.

 

[Skydiver]

Also, you are going about things the wrong way with this:

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:

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];
    }
}

 

[ConsKa]

ContainsKey

Thank you, I think that is exactly what I needed.

 

[Skydiver]

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:

["Foo"] = "Bar"
["Bar"] = "Boo"

and the Excel Sheet looked like:

     A   B
1 Foo

Your code would result in:

     A   B
1 Boo Bar

but my code would result in:

     A   B
1 Bar Foo

 

[ConsKa]

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]

You should check out TryGetValue() as well.

 

[ConsKa]

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.