Fastest way to get an Excel Range of Rows

Question

In a VSTO C# project I want to get a range of rows from a set of row indexes.

The row indexes can be for example like "7,8,9,12,14".

Then I want the range "7:9,12,14" rows.

I now do this:

Range rng1 = sheet.get_Range("A7:A9,A12,A14", Type.Missing);
rng1  = rng1.EntireRow;

But it's a bit inefficient due to string handling in range specification.

sheet.Rows["7:9"]

works but I can't give this

sheet.Rows["7:9,12,14"] // Fails

Solution 1

Try this:

Sheet.Range("7:9,12:12,14:14")

EDIT: Sorry if using VSTO in C# it should have been:

sheet.get_Range("7:9,12:12,14:14", Type.Missing)

Solution 2

Here is the code you are looking for:

int startRow, endRow, startCol, endCol, row,col;
var singleData = new object[col];
var data = new object[row,col];
//For populating only a single row with 'n' no. of columns.
var startCell = (Range)worksheet.Cells[startRow, startCol];
startCell.Value2 = singleData;
//For 2d data, with 'n' no. of rows and columns.
var endCell = (Range)worksheet.Cells[endRow, endCol];
var writeRange = worksheet.Range[startCell, endCell];
writeRange.Value2 = data;

You can have entire range, be it 1 dimensional or 2 dimensional array of cells.

This method is particularly helpful while looping through the entire excel sheet and populating data where and when required.

Solution 3

I am not an expert in C# but AFAIK you have to use the EntireRow as you have done above. The string that you are looking for can be achieved from the .Address property. For example

    private void button1_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        Microsoft.Office.Interop.Excel.Range xlRange;

        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();

        xlWorkBook = xlexcel.Workbooks.Add();

        // Set Sheet 1 as the sheet you want to work with
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlRange = xlWorkSheet.get_Range("A7:A9,A12,A14", misValue);

        MessageBox.Show(xlRange.EntireRow.Address);

        xlRange = xlWorkSheet.get_Range(xlRange.EntireRow.Address, misValue);

        MessageBox.Show(xlRange.Address);
    }

So you can write the above as

    private void button1_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        Microsoft.Office.Interop.Excel.Range xlRange;

        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();

        xlWorkBook = xlexcel.Workbooks.Add();

        // Set Sheet 1 as the sheet you want to work with
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlRange = xlWorkSheet.get_Range("$7:$9,$12:$12,$14:$14", misValue);

        MessageBox.Show(xlRange.Address);
    }

See the part

    xlRange = xlWorkSheet.get_Range("$7:$9,$12:$12,$14:$14", misValue);

Solution 4

Reafidy's edited answer is a great start, but I wanted to expand on it more than I could do in a comment. sheet.get_Range(rangeselect) is much faster than going row by row, but one thing I haven't seen mentioned yet is that the get_Range parameter has a 255 character limit.

To get around that limitation, construct a set of ranges like "8:8,10:13,14:55" as normal then use a variant of this code:

string rangeSelectPart;
while (rangeSelect.Length >= 255)
{
    rangeSelectPart = rangeSelect.Substring(0, rangeSelect.Substring(0,255).LastIndexOf(','));
    Range multiRangePart = sheet.get_Range(rangeSelectPart, Type.Missing);

    //do something with the range here using multiRangePart 

    rangeSelect= rangeSelect.Substring(rangeSelectPart.Length + 1);
}
Range multiRange = sheet.get_Range(rangeSelect, Type.Missing);
// do the same something with the last part of the range using multiRange 
// now that the remaining rows are described in less than 255 characters

This will be significantly faster than doing operations on individual rows, but also won't fail when presented with large non-contiguous row sets.

---

Note that SutharMonil's answer is way faster IFF setting values in contiguous rectangular ranges. The bottleneck going from C# to excel is usually the repeated calls through the COM objects which block while being created and updated, and his answer nicely consolidates calls.

Unfortunately in my testing so far, trying to use it to work with non-string properties that aren't of type string has resulted in a type error. For example:

object[,] colors;
//use C# to set appropriate colors to each location in array...
for(int i = 0; i < colors.get_Length(0); i++){
    for(int j = 0; j < colors.get_Length(1); j++){
        colors[i,j] = XlThemeColor.xlThemeColorAccent6;
    }
}

//below causes a type error
formatRange.Interior.ThemeColor = color;

I'll try to remember to update if I get it to work.

---

Lastly for repeated operations set Globals.ThisAddIn.Application.ScreenUpdating = false; and then set it to true when you're done. Without this, Excel stops to update the screen after each set of range properties is updated and that can add a lot of time to the operation.

View more solutions

Comments

[Tim Williams]

You can union the individual ranges, but that may not be any more efficient than using the single call with the concatenated address.

[Reafidy]

The important part I was trying to show was that you can use row numbers in the range method and you don't need to use .entirerow. The OP already had .get_range so I guess hopefully he knew what I meant. Keyboard in gear before brain. ;)

[Gayan Dasanayake]

Yes this looks like my answer, but I will give it a try now.

[jreichert]

Sorry, this seems to work. But for some kinds of Range.Properties you seem to need the EntireRow-Range nevertheless. For example "OutlineLevel", "Hidden", etc.

[TheAtomicOption]

Note that .get_Range() has a 255 character limit on its parameter. See my answer to this question for some copy/paste code to get around that.