CODEWITHSUNDEEP
c#exceliteration
TimB
TimB

Reputation: 1000

C# Efficient way to iterate over excel worksheet

I have the following code:

string result = "";
for(int i=2; i<=excelRange.Rows.Count; i++)
{
    result += excelRange.Cells[i, 2].Value2;
}

For an excel file with a couple hundred entries this takes 5 seconds. Is there a more efficient way, perhaps? I only want the values from B2 to Bn.

Upvotes: 3

Views: 3570

Answers (2)

Codo
Codo

Reputation: 78795

Yes, there is a more efficient way.

  1. Create a range that exactly matches the cells that you really need.

  2. Get the Value2 property of this range. The result will be an array type.

  3. Iterate through the array

The problem with your approach is the large number of inter-process requests between your application and Excel. Your approach requires two or three requests per cell. The proposed approach is much faster because it requires a few requests up-front but not additional requests per cell.

Note that this works up to about 4000 cells. If you need to process more cells, you will need to split it into several ranges, each one containing less than 4000 cells.

Update

Assuming Excel is already running, it would look something like this (the correct number of rows in the B column is automatically selected):

var excelApp =  (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
var range = (Excel.Range)workSheet.Range[workSheet.Range["B2"],
        workSheet.Range["B2"].End[Excel.XlDirection.xlDown]];
var cellData = (Object[,])range.Value2;

string result = "";
foreach (var cell in cellData) {
    result += cell.ToString();
}

Upvotes: 6

JohnyL
JohnyL

Reputation: 7122

The basic skeleton is the following:

var xlApp = new Excel.Application { Visible = true };
var xlBook = xlApp.Workbooks.Open(@"C:\Temp\Results.xlsx");
var xlSheet = xlBook.Sheets[1] as Excel.Worksheet;
var arr = (object[,])xlSheet.Range["B2:B100000"].Value;
var sb = new StringBuilder();
for (int x = 1; x <= arr.GetUpperBound(0); ++x)
{
    sb.Append(arr[x, 1]);
}
var final_string = sb.ToString();

// Close workbook, close Excel...

Upvotes: 1

Related Questions