Get Range and Range Area | Document Solutions for Excel, .NET Edition

Get Range and Range Area

In This Topic

Range or a range area refers to an array of cells that have been defined in a spreadsheet. While working with worksheets in a workbook, users can define multiple ranges and then further access those range areas separately to perform certain tasks like formatting of cells, merging of cells, insertion or deletion of cells within a range and other useful operations.

Refer to the following example code to see how you can define a range.

C#	Copy Code
//Use index to access cell A1. worksheet.Range[0, 0].Interior.Color = Color.LightGreen; //Use index to access range A1:B2 worksheet.Range[0, 0, 2, 2].Value = 5; //Use string to access range. worksheet.Range["A2"].Interior.Color = Color.LightYellow; worksheet.Range["C3:D4"].Interior.Color = Color.Tomato; worksheet.Range["A5:B7, C3, H5:N6"].Value = 2; //Use index to access rows worksheet.Rows[2].Interior.Color = Color.LightSalmon; //Use string to access rows worksheet.Range["4:4"].Interior.Color = Color.LightSkyBlue; //Use index to access columns worksheet.Columns[2].Interior.Color = Color.LightSalmon; //Use string to access columns worksheet.Range["D:D"].Interior.Color = Color.LightSkyBlue; //Use Cells to access range. worksheet.Cells[5].Interior.Color = Color.LightBlue; worksheet.Cells[5, 5].Interior.Color = Color.LightYellow; //Access all rows in worksheet var allRows = worksheet.Rows.ToString(); //Access all columns in worksheet var allColumns = worksheet.Columns.ToString(); //Access the entire sheet range var entireSheet = worksheet.Cells.ToString();

Copy Code

//Use index to access cell A1.
worksheet.Range[0, 0].Interior.Color = Color.LightGreen;

//Use index to access range A1:B2
worksheet.Range[0, 0, 2, 2].Value = 5;

//Use string to access range.
worksheet.Range["A2"].Interior.Color = Color.LightYellow;
worksheet.Range["C3:D4"].Interior.Color = Color.Tomato;
worksheet.Range["A5:B7, C3, H5:N6"].Value = 2;

//Use index to access rows
worksheet.Rows[2].Interior.Color = Color.LightSalmon;

//Use string to access rows
worksheet.Range["4:4"].Interior.Color = Color.LightSkyBlue;

//Use index to access columns
worksheet.Columns[2].Interior.Color = Color.LightSalmon;

//Use string to access columns
worksheet.Range["D:D"].Interior.Color = Color.LightSkyBlue;

//Use Cells to access range.
worksheet.Cells[5].Interior.Color = Color.LightBlue;
worksheet.Cells[5, 5].Interior.Color = Color.LightYellow;

//Access all rows in worksheet
var allRows = worksheet.Rows.ToString();

//Access all columns in worksheet
var allColumns = worksheet.Columns.ToString();

//Access the entire sheet range
var entireSheet = worksheet.Cells.ToString();

Refer to the following example code to see how you can access the range area with a range.

C#	Copy Code
//area1 is A5:B7. var area1 = worksheet.Range["A5:B7,C3,H5:N6"].Areas[0]; //set interior color for area1 area1.Interior.Color = Color.Pink; //area2 is C3. var area2 = worksheet.Range["A5:B7,C3,H5:N6"].Areas[1]; //set interior color for area2 area2.Interior.Color = Color.LightGreen; //area3 is H5:N6. var area3 = worksheet.Range["A5:B7,C3,H5:N6"].Areas[2]; //set interior color for area3 area3.Interior.Color = Color.LightBlue;

Copy Code

//area1 is A5:B7.
var area1 = worksheet.Range["A5:B7,C3,H5:N6"].Areas[0];

//set interior color for area1
area1.Interior.Color = Color.Pink;

//area2 is C3.
var area2 = worksheet.Range["A5:B7,C3,H5:N6"].Areas[1];

//set interior color for area2
area2.Interior.Color = Color.LightGreen;

//area3 is H5:N6.
var area3 = worksheet.Range["A5:B7,C3,H5:N6"].Areas[2];

//set interior color for area3
area3.Interior.Color = Color.LightBlue;