Parallel.Foreach on Excel.Range c#

Question

I am writing a c# application that reads an Excel file and creates in-memory objects against each row found in the Excel worksheet. I have successfully done this using for-loop but would like to use Parallel.Foreach to improve the performance since the Excel file contains over 90,000+ records.

I have previously used Parallel.Foreach on DataTable objects, is there any way I can use it on Excel.Range object? I need to iterate on excel data row by row.

Any examples would help.

Are you sure parallel processing will help here? Have you profiled the time it takes to create your objects? The bottleneck may just be disk access. — Gusdor, Dec 23 '13 at 08:06
For Excel performance check [this](http://stackoverflow.com/a/17021537/1069200) out. I don't think Parallel.Foreach is going to do anything. Batch-read and then read from memory imo. — Johan Larsson, Dec 23 '13 at 08:09
All you need is an IEnumerable. Your Excel library can probably give you that. Study the docs. — usr, Dec 23 '13 at 09:32

score 0 · Answer 1 · answered Mar 10 '16 at 18:11

I defined this method which takes Excel.Range object and transform it into lists using PLINQ

private List<List<String>> ExcelRangeToLists(Excel.Range cells)
{
   return cells.Rows.Cast<Excel.Range>().AsParallel().Select(row =>
   {         
      return row.Cells.Cast<Excel.Range>().Select(cell =>
      {
         var cellContent = cell.Value2;
         return (cellContent == null) ? String.Empty : cellContent.ToString();                        
      }).Cast<string>().ToList();                
   }).ToList();
});

Then, just read some range from excel sheet and call this method

var cells = xlSheet.get_Range("A3", "BK1555");
var data = ExcelRangeToLists(cells);

Measuring the performance, it is better but not drastically - For example ~20% faster for 1400 rows range (should be even better for larger sets).

I didn't used the Parallel.Foreach since I needed the select to create the lists, but the basic casting idea is similar.

Parallel.ForEach(cells.Rows.Cast<Excel.Range>(), row => DoSomethingWithRow(row));

Enjoy

Parallel.Foreach on Excel.Range c#

1 Answers1