CODEWITHSUNDEEP
c#multithreadingexcel
Robert
Robert

Reputation: 4406

How to Iterate an Excel document in a thread safe manor

I am completely out of my element here, and I admit I am a little confused on how this should work. I have an excel document that I am trying to parse in a thread safe way.

Here is what I have so far:

        public IEnumerable<DepartmentOfEnergyIndex> ParseJet(Workbook workbook)
    {
        var indecies = new ConcurrentQueue<DepartmentOfEnergyIndex>();
        var worksheet = (Worksheet)workbook.Worksheets.Item["Data 6"];
        Parallel.ForEach(worksheet.Rows, currentRow =>
        {
            indecies.Enqueue(
                new DepartmentOfEnergyIndex
                {
                    DOELastUpdated = currentRow[0],
                    DollarsPerGallon = currentRow[1],
                    RegionID = RegionEnum.USGC.ToInt(),
                });
        });
        return indecies.ToArray();
    }

The first problem I am having (besides lack of knowledge) is ForEach is throwing a compiler error:

    Error   1   The type arguments for method 
'System.Threading.Tasks.Parallel.ForEach<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Action<TSource>)' 
cannot be inferred from the usage. Try specifying the type arguments explicitly.

Based on this example. That should have worked, but fine, worksheet.Rows is a Microsoft.Office.Interop.Excel.Range: 

Parallel.ForEach<Range>(worksheet.Rows, currentRow =>
        {
            //same stuff
        });

Now:

Error   1   The best overloaded method match for 'System.Threading.Tasks.Parallel.ForEach<Microsoft.Office.Interop.Excel.Range>(
System.Collections.Generic.IEnumerable<Microsoft.Office.Interop.Excel.Range>, 
System.Action<Microsoft.Office.Interop.Excel.Range>)' has some invalid arguments

Okay Range inherits from IEnumerable but it is not in fact an IEnumerable.

So how do I iterate over the rows here in a thread safe manor?

Upvotes: 0

Views: 1375

Answers (1)

Dave Cousineau
Dave Cousineau

Reputation: 13198

Rows is a Range which implements IEnumerable, but not IEnumerable<T>. I think the underlying items are also of type Range, so you would need to cast them:

Parallel.ForEach(worksheet.Rows.Cast<Range>(), currentRow => {
   // do something
});

This should solve your compiler error, but not necessarily any threading issues you may or may not also have.

Also, I don't know if it's a good idea in the first place to iterate over every single row. 99.999% of the time most of the available worksheet space is not used, but this will iterate over the whole thing, which can be over a million rows depending on the version of Excel.

Upvotes: 1

Related Questions