 | Normally, you use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. But you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. The word Name may refer to a string of characters that represents a cell, range of cells, formula, or constant value. For example, Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30 to represent a cell, range of cells, formula, or constant value. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, you may use a name. Named Ranges are among the most powerful features of Microsoft Excel, especially when used as the source range for List Controls, Pivot Tables or Charts etc. Users may assign a name to a named range so that this range of cells can be referred with its name. |
Create Named Range of Cells
Using MS Excel
The following steps describe how to name a cell or range of cells using MS Excel. This method applies to Microsoft Office Excel 2003, Microsoft Excel 97, 2000, and 2002.
1. Select the cell, range of cells that you want to name.
2. Click the Name Box at the left end of the formula bar.
3. Type the name for the cells.
4. Press ENTER.
You cannot name a cell while you are changing the contents of the cell.
The following steps describe how to name a cell or range of cells using MS Excel. This method applies to Microsoft Office Excel 2003, Microsoft Excel 97, 2000, and 2002.
1. Select the cell, range of cells that you want to name.
2. Click the Name Box at the left end of the formula bar.
3. Type the name for the cells.
4. Press ENTER.
| You cannot name a cell while you are changing the contents of the cell. |
Using Aspose.Cells
Here, we use Aspose.Cells API to do the task.
Aspose.Cells provides a class, Workbook ** that represents an Excel file. Workbook class contains a Worksheets collection that allows to access each worksheet in the Excel file. A worksheet is represented by the Worksheet class. Worksheet class provides a Cells collection.
Developers can create a named range by calling the overloaded CreateRa n ge method of the Cell collection. A typical version of CreateRange method takes the following parameters, you may choose others for your need:
- Name of the Upper Left Cell , represents the name of the upper left cell in the named range
- Name of the Lower Right Cell , represents the name of the lower right cell in the named range
When CreateRange method is called, it returns the newly created named range as an instance of Range class. Developers may use this Range object to configure the named range. For example, developers can set any desired name of the named range using the Name property of the Range object.
Example:
The following example shows how to create a named range of cells that extends over B4:G14.
[C#]
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream("C:\\book1.xls", FileMode.Open);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Creating a named range
Range range = worksheet.Cells.CreateRange("B4", "G14");
//Setting the name of the named range
range.Name = "TestRange";
//Saving the modified Excel file
workbook.Save("C:\\output.xls");
//Closing the file stream to free all resources
fstream.Close();
[VB.NET]
'Creating a file stream containing the Excel file to be opened
Dim fstream As FileStream = New FileStream("C:\\book1.xls", FileMode.Open)
'Instantiating a Workbook object
'Opening the Excel file through the file stream
Dim workbook As Workbook = New Workbook(fstream)
'Accessing the first worksheet in the Excel file
Dim worksheet As Worksheet = workbook.Worksheets(0)
'Creating a named range
Dim range As Range = worksheet.Cells.CreateRange("B4", "G14")
'Setting the name of the named range
range.Name = "TestRange"
'Saving the modified Excel file
workbook.Save("C:\\output.xls")
'Closing the file stream to free all resources
fstream.Close()
Here, we use Aspose.Cells API to do the task.
Aspose.Cells provides a class, Workbook ** that represents an Excel file. Workbook class contains a Worksheets collection that allows to access each worksheet in the Excel file. A worksheet is represented by the Worksheet class. Worksheet class provides a Cells collection.
Developers can create a named range by calling the overloaded CreateRa n ge method of the Cell collection. A typical version of CreateRange method takes the following parameters, you may choose others for your need:
- Name of the Upper Left Cell , represents the name of the upper left cell in the named range
- Name of the Lower Right Cell , represents the name of the lower right cell in the named range
When CreateRange method is called, it returns the newly created named range as an instance of Range class. Developers may use this Range object to configure the named range. For example, developers can set any desired name of the named range using the Name property of the Range object.
Example:
The following example shows how to create a named range of cells that extends over B4:G14.
[C#]
//Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream("C:\\book1.xls", FileMode.Open); //Instantiating a Workbook object //Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); //Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; //Creating a named range Range range = worksheet.Cells.CreateRange("B4", "G14"); //Setting the name of the named range range.Name = "TestRange"; //Saving the modified Excel file workbook.Save("C:\\output.xls"); //Closing the file stream to free all resources fstream.Close();
[VB.NET]
'Creating a file stream containing the Excel file to be opened Dim fstream As FileStream = New FileStream("C:\\book1.xls", FileMode.Open) 'Instantiating a Workbook object 'Opening the Excel file through the file stream Dim workbook As Workbook = New Workbook(fstream) 'Accessing the first worksheet in the Excel file Dim worksheet As Worksheet = workbook.Worksheets(0) 'Creating a named range Dim range As Range = worksheet.Cells.CreateRange("B4", "G14") 'Setting the name of the named range range.Name = "TestRange" 'Saving the modified Excel file workbook.Save("C:\\output.xls") 'Closing the file stream to free all resources fstream.Close()
Access All the Named Ranges in the Worksheets
Developers can call GetNamed R anges method of the Worksheets collection to get all named ranges in a spreadsheet. GetNamedRanges method returns an array of all names ranges in the Worksheets collection.
Example:
The following example shows how to access all the named ranges in a workbook.
[C#]
//Getting all named ranges
Range[] range = workbook.Worksheets.GetNamedRanges();
[VB.NET]
'Getting all named ranges
Dim ranges() As Range = workbook.Worksheets.GetNamedRanges()
Developers can call GetNamed R anges method of the Worksheets collection to get all named ranges in a spreadsheet. GetNamedRanges method returns an array of all names ranges in the Worksheets collection.
Example:
The following example shows how to access all the named ranges in a workbook.
[C#]
//Getting all named ranges
Range[] range = workbook.Worksheets.GetNamedRanges();
[VB.NET]
'Getting all named ranges Dim ranges() As Range = workbook.Worksheets.GetNamedRanges()
Access a Specific Named Range
Developers can call GetRangeB y Name method of the Worksheets collection to get a range by the specified name. A typical GetRangeByName method takes the name of the named range and returns the specified named range as an instance of Range class.
Example:
The following example shows how to access a specified range by its name.
[C#]
//Getting the specified named range
Range range = workbook.Worksheets.GetRangeByName("TestRange");
[VB.NET]
'Getting the specified named range
Dim range As Range = workbook.Worksheets.GetRangeByName("TestRange")
Developers can call GetRangeB y Name method of the Worksheets collection to get a range by the specified name. A typical GetRangeByName method takes the name of the named range and returns the specified named range as an instance of Range class.
Example:
The following example shows how to access a specified range by its name.
[C#]
//Getting the specified named range Range range = workbook.Worksheets.GetRangeByName("TestRange");
[VB.NET]
'Getting the specified named range
Dim range As Range = workbook.Worksheets.GetRangeByName("TestRange")
Identify Cells in the Named Range
You can insert data into the individual cells of a range following the pattern. i.e., Range[row,column] (C#) / Range(row,column)(VB). Suppose, you have a named range of cells.i.e., A1:C4. So the matrix would make 4 * 3 = 12 cells and the individual range cells are arranged sequentially i.e., Range[0,0],Range[0,1],Range[0,2],Range[1,0],Range[1,1],Range[1,2],Range[2,0],Range[2,1],Range[2,2],Range[3,0],Range[3,1],Range[3,2]. Aspose.Cells provides you some useful Properties of Range class to access the individual cells in the range. You may use the following properties to identify the cells in the ranage:
- FirstRow returns the index of the first row in the named range.
- FirstColumn returns the index of the first column in the named range.
- RowCount returns the total number of rows in the named range.
- ColumnCount returns the toal number of columns in the named range.
Example:
The following example shows how to input some values into the cells of a specified range.
[C#]
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get the first worksheet in the workbook.
Worksheet worksheet1 = workbook.Worksheets[0];
//Create a range of cells based on H1:J4.
Range range = worksheet1.Cells.CreateRange("H1", "J4");
//Name the range.
range.Name = "MyRange";
//Input some data into cells in the range.
range[0,0].PutValue("USA");
range[0,1].PutValue("SA");
range[0,2].PutValue("Israel");
range[1,0].PutValue("UK");
range[1,1].PutValue("AUS");
range[1,2].PutValue("Canada");
range[2,0].PutValue("France");
range[2,1].PutValue("India");
range[2,2].PutValue("Egypt");
range[3,0].PutValue("China");
range[3,1].PutValue("Philipine");
range[3,2].PutValue("Brazil");
//Identify range cells.
int firstrow = range.FirstRow;
int firstcol = range.FirstColumn;
int trows = range.RowCount;
int tcols = range.ColumnCount;
//Save the excel file.
workbook.Save("d:\\test\\rangecells.xls");
[VB.NET]
'Instantiate a new Workbook.
Dim workbook As Workbook = New Workbook()
'Get the first worksheet in the workbook.
Dim worksheet1 As Worksheet = workbook.Worksheets(0)
'Create a range of cells based on H1:J4.
Dim range As Range = worksheet1.Cells.CreateRange("H1", "J4")
'Name the range.
range.Name = "MyRange"
'Input some data into cells in the range.
range(0, 0).PutValue("USA")
range(0, 1).PutValue("SA")
range(0, 2).PutValue("Israel")
range(1, 0).PutValue("UK")
range(1, 1).PutValue("AUS")
range(1, 2).PutValue("Canada")
range(2, 0).PutValue("France")
range(2, 1).PutValue("India")
range(2, 2).PutValue("Egypt")
range(3, 0).PutValue("China")
range(3, 1).PutValue("Philipine")
range(3, 2).PutValue("Brazil")
'Identify range cells.
Dim firstrow As Integer = range.FirstRow
Dim firstcol As Integer = range.FirstColumn
Dim trows As Integer = range.RowCount
Dim tcols As Integer = range.ColumnCount
'Save the excel file.
workbook.Save("d:\test\rangecells.xls")
You can insert data into the individual cells of a range following the pattern. i.e., Range[row,column] (C#) / Range(row,column)(VB). Suppose, you have a named range of cells.i.e., A1:C4. So the matrix would make 4 * 3 = 12 cells and the individual range cells are arranged sequentially i.e., Range[0,0],Range[0,1],Range[0,2],Range[1,0],Range[1,1],Range[1,2],Range[2,0],Range[2,1],Range[2,2],Range[3,0],Range[3,1],Range[3,2]. Aspose.Cells provides you some useful Properties of Range class to access the individual cells in the range. You may use the following properties to identify the cells in the ranage:
- FirstRow returns the index of the first row in the named range.
- FirstColumn returns the index of the first column in the named range.
- RowCount returns the total number of rows in the named range.
- ColumnCount returns the toal number of columns in the named range.
Example:
The following example shows how to input some values into the cells of a specified range.
[C#]
//Instantiate a new Workbook. Workbook workbook = new Workbook(); //Get the first worksheet in the workbook. Worksheet worksheet1 = workbook.Worksheets[0]; //Create a range of cells based on H1:J4. Range range = worksheet1.Cells.CreateRange("H1", "J4"); //Name the range. range.Name = "MyRange"; //Input some data into cells in the range. range[0,0].PutValue("USA"); range[0,1].PutValue("SA"); range[0,2].PutValue("Israel"); range[1,0].PutValue("UK"); range[1,1].PutValue("AUS"); range[1,2].PutValue("Canada"); range[2,0].PutValue("France"); range[2,1].PutValue("India"); range[2,2].PutValue("Egypt"); range[3,0].PutValue("China"); range[3,1].PutValue("Philipine"); range[3,2].PutValue("Brazil"); //Identify range cells. int firstrow = range.FirstRow; int firstcol = range.FirstColumn; int trows = range.RowCount; int tcols = range.ColumnCount; //Save the excel file. workbook.Save("d:\\test\\rangecells.xls");
[VB.NET]
'Instantiate a new Workbook. Dim workbook As Workbook = New Workbook() 'Get the first worksheet in the workbook. Dim worksheet1 As Worksheet = workbook.Worksheets(0) 'Create a range of cells based on H1:J4. Dim range As Range = worksheet1.Cells.CreateRange("H1", "J4") 'Name the range. range.Name = "MyRange" 'Input some data into cells in the range. range(0, 0).PutValue("USA") range(0, 1).PutValue("SA") range(0, 2).PutValue("Israel") range(1, 0).PutValue("UK") range(1, 1).PutValue("AUS") range(1, 2).PutValue("Canada") range(2, 0).PutValue("France") range(2, 1).PutValue("India") range(2, 2).PutValue("Egypt") range(3, 0).PutValue("China") range(3, 1).PutValue("Philipine") range(3, 2).PutValue("Brazil") 'Identify range cells. Dim firstrow As Integer = range.FirstRow Dim firstcol As Integer = range.FirstColumn Dim trows As Integer = range.RowCount Dim tcols As Integer = range.ColumnCount 'Save the excel file. workbook.Save("d:\test\rangecells.xls")
Input Data into the Cells in the Named Range
You can insert data into the individual cells of a range following the pattern. i.e., Range[row,column] (C#) / Range(row,column)(VB). Suppose, you have a named range of cells.i.e., A1:C4. So the matrix would make 4 * 3 = 12 cells and the individual range cells are arranged sequentially i.e., Range[0,0],Range[0,1],Range[0,2],Range[1,0],Range[1,1],Range[1,2],Range[2,0],Range[2,1],Range[2,2],Range[3,0],Range[3,1],Range[3,2]. Aspose.Cells provides you some useful Properties of Range class to access the individual cells in the range. You may use the following properties to identify the cells in the ranage:
- FirstRow returns the index of the first row in the named range.
- FirstColumn returns the index of the first column in the named range.
- RowCount returns the total number of rows in the named range.
- ColumnCount returns the toal number of columns in the named range.
Example:
The following example shows how to input some values into the cells of a specified range.
[C#]
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get the first worksheet in the workbook.
Worksheet worksheet1 = workbook.Worksheets[0];
//Create a range of cells based on H1:J4.
Range range = worksheet1.Cells.CreateRange("H1", "J4");
//Name the range.
range.Name = "MyRange";
//Input some data into cells in the range.
range[0,0].PutValue("USA");
range[0,1].PutValue("SA");
range[0,2].PutValue("Israel");
range[1,0].PutValue("UK");
range[1,1].PutValue("AUS");
range[1,2].PutValue("Canada");
range[2,0].PutValue("France");
range[2,1].PutValue("India");
range[2,2].PutValue("Egypt");
range[3,0].PutValue("China");
range[3,1].PutValue("Philipine");
range[3,2].PutValue("Brazil");
//Identify range cells.
int firstrow = range.FirstRow;
int firstcol = range.FirstColumn;
int trows = range.RowCount;
int tcols = range.ColumnCount;
//Save the excel file.
workbook.Save("d:\\test\\rangecells.xls");
[VB.NET]
'Instantiate a new Workbook.
Dim workbook As Workbook = New Workbook()
'Get the first worksheet in the workbook.
Dim worksheet1 As Worksheet = workbook.Worksheets(0)
'Create a range of cells based on H1:J4.
Dim range As Range = worksheet1.Cells.CreateRange("H1", "J4")
'Name the range.
range.Name = "MyRange"
'Input some data into cells in the range.
range(0, 0).PutValue("USA")
range(0, 1).PutValue("SA")
range(0, 2).PutValue("Israel")
range(1, 0).PutValue("UK")
range(1, 1).PutValue("AUS")
range(1, 2).PutValue("Canada")
range(2, 0).PutValue("France")
range(2, 1).PutValue("India")
range(2, 2).PutValue("Egypt")
range(3, 0).PutValue("China")
range(3, 1).PutValue("Philipine")
range(3, 2).PutValue("Brazil")
'Identify range cells.
Dim firstrow As Integer = range.FirstRow
Dim firstcol As Integer = range.FirstColumn
Dim trows As Integer = range.RowCount
Dim tcols As Integer = range.ColumnCount
'Save the excel file.
workbook.Save("d:\test\rangecells.xls")
You can insert data into the individual cells of a range following the pattern. i.e., Range[row,column] (C#) / Range(row,column)(VB). Suppose, you have a named range of cells.i.e., A1:C4. So the matrix would make 4 * 3 = 12 cells and the individual range cells are arranged sequentially i.e., Range[0,0],Range[0,1],Range[0,2],Range[1,0],Range[1,1],Range[1,2],Range[2,0],Range[2,1],Range[2,2],Range[3,0],Range[3,1],Range[3,2]. Aspose.Cells provides you some useful Properties of Range class to access the individual cells in the range. You may use the following properties to identify the cells in the ranage:
- FirstRow returns the index of the first row in the named range.
- FirstColumn returns the index of the first column in the named range.
- RowCount returns the total number of rows in the named range.
- ColumnCount returns the toal number of columns in the named range.
Example:
The following example shows how to input some values into the cells of a specified range.
[C#]
//Instantiate a new Workbook. Workbook workbook = new Workbook(); //Get the first worksheet in the workbook. Worksheet worksheet1 = workbook.Worksheets[0]; //Create a range of cells based on H1:J4. Range range = worksheet1.Cells.CreateRange("H1", "J4"); //Name the range. range.Name = "MyRange"; //Input some data into cells in the range. range[0,0].PutValue("USA"); range[0,1].PutValue("SA"); range[0,2].PutValue("Israel"); range[1,0].PutValue("UK"); range[1,1].PutValue("AUS"); range[1,2].PutValue("Canada"); range[2,0].PutValue("France"); range[2,1].PutValue("India"); range[2,2].PutValue("Egypt"); range[3,0].PutValue("China"); range[3,1].PutValue("Philipine"); range[3,2].PutValue("Brazil"); //Identify range cells. int firstrow = range.FirstRow; int firstcol = range.FirstColumn; int trows = range.RowCount; int tcols = range.ColumnCount; //Save the excel file. workbook.Save("d:\\test\\rangecells.xls");
[VB.NET]
'Instantiate a new Workbook. Dim workbook As Workbook = New Workbook() 'Get the first worksheet in the workbook. Dim worksheet1 As Worksheet = workbook.Worksheets(0) 'Create a range of cells based on H1:J4. Dim range As Range = worksheet1.Cells.CreateRange("H1", "J4") 'Name the range. range.Name = "MyRange" 'Input some data into cells in the range. range(0, 0).PutValue("USA") range(0, 1).PutValue("SA") range(0, 2).PutValue("Israel") range(1, 0).PutValue("UK") range(1, 1).PutValue("AUS") range(1, 2).PutValue("Canada") range(2, 0).PutValue("France") range(2, 1).PutValue("India") range(2, 2).PutValue("Egypt") range(3, 0).PutValue("China") range(3, 1).PutValue("Philipine") range(3, 2).PutValue("Brazil") 'Identify range cells. Dim firstrow As Integer = range.FirstRow Dim firstcol As Integer = range.FirstColumn Dim trows As Integer = range.RowCount Dim tcols As Integer = range.ColumnCount 'Save the excel file. workbook.Save("d:\test\rangecells.xls")
Format Ranges...Setting Background Color and Font Attributes to a Named Range
If you want to apply some formattings, you'd better define a Style object to specify the style settings and apply it to the Range object.
Example:
The following example shows how to set solid fill color (shading color) with font settings to a range.
[C#]
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get the first worksheet in the book.
Worksheet WS = workbook.Worksheets[0];
//Create a range of cells.
Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 1, 18);
//Name the range.
range.Name = "MyRange";
//Declare a style object.
Style stl;
//Create/add the style object.
stl = workbook.Styles[workbook.Styles.Add()];
//Specify some Font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
//Set the font text color
stl.Font.Color = Color.Red;
//To Set the fill color of the range, you may use ForegroundColor with
//solid Pattern setting.
stl.ForegroundColor = Color.Yellow;
stl.Pattern = BackgroundType.Solid;
//Create a StyleFlag object.
StyleFlag flg = new StyleFlag();
//Make the corresponding attributes ON.
flg.Font = true;
flg.CellShading = true;
//Apply the style to the range.
range.ApplyStyle(stl,flg);
//Save the excel file.
workbook.Save("d:\\test\\rangestyles.xls");
[VB.NET]
'Instantiate a new Workbook.
Dim workbook As New Workbook()
'Get the first worksheet in the book.
Dim WS As Worksheet = workbook.Worksheets(0)
'Create a range of cells.
Dim range As Aspose.Cells.Range = WS.Cells.CreateRange(1, 1, 1, 18)
'Name the range.
range.Name = "MyRange"
'Declare a style object.
Dim stl As Style
'Create/add the style object.
stl = workbook.Styles(workbook.Styles.Add())
'Specify some Font settings.
stl.Font.Name = "Arial"
stl.Font.IsBold = True
'Set the font text color
stl.Font.Color = Color.Red
'To Set the fill color of the range, you may use ForegroundColor with
'solid Pattern setting.
stl.ForegroundColor = Color.Yellow
stl.Pattern = BackgroundType.Solid
'Create a StyleFlag object.
Dim flg As New StyleFlag()
'Make the corresponding attributes ON.
flg.Font = True
flg.CellShading = True
'Apply the style to the range.
range.ApplyStyle(stl,flg)
'Save the excel file.
workbook.Save("d:\test\rangestyles.xls")
If you want to apply some formattings, you'd better define a Style object to specify the style settings and apply it to the Range object.
Example:
The following example shows how to set solid fill color (shading color) with font settings to a range.
[C#]
//Instantiate a new Workbook. Workbook workbook = new Workbook(); //Get the first worksheet in the book. Worksheet WS = workbook.Worksheets[0]; //Create a range of cells. Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 1, 18); //Name the range. range.Name = "MyRange"; //Declare a style object. Style stl; //Create/add the style object. stl = workbook.Styles[workbook.Styles.Add()]; //Specify some Font settings. stl.Font.Name = "Arial"; stl.Font.IsBold = true; //Set the font text color stl.Font.Color = Color.Red; //To Set the fill color of the range, you may use ForegroundColor with //solid Pattern setting. stl.ForegroundColor = Color.Yellow; stl.Pattern = BackgroundType.Solid; //Create a StyleFlag object. StyleFlag flg = new StyleFlag(); //Make the corresponding attributes ON. flg.Font = true; flg.CellShading = true; //Apply the style to the range. range.ApplyStyle(stl,flg); //Save the excel file. workbook.Save("d:\\test\\rangestyles.xls");
[VB.NET]
'Instantiate a new Workbook. Dim workbook As New Workbook() 'Get the first worksheet in the book. Dim WS As Worksheet = workbook.Worksheets(0) 'Create a range of cells. Dim range As Aspose.Cells.Range = WS.Cells.CreateRange(1, 1, 1, 18) 'Name the range. range.Name = "MyRange" 'Declare a style object. Dim stl As Style 'Create/add the style object. stl = workbook.Styles(workbook.Styles.Add()) 'Specify some Font settings. stl.Font.Name = "Arial" stl.Font.IsBold = True 'Set the font text color stl.Font.Color = Color.Red 'To Set the fill color of the range, you may use ForegroundColor with 'solid Pattern setting. stl.ForegroundColor = Color.Yellow stl.Pattern = BackgroundType.Solid 'Create a StyleFlag object. Dim flg As New StyleFlag() 'Make the corresponding attributes ON. flg.Font = True flg.CellShading = True 'Apply the style to the range. range.ApplyStyle(stl,flg) 'Save the excel file. workbook.Save("d:\test\rangestyles.xls")
Format Ranges...Adding Borders to a Named Range
Sometimes, you may need to add borders to a range of cells rather than just a single cell. Range object provides a SetOutlineBorder method that takes the following parameters to add a border to the range of cells:
- Border Type , represents the type of border, which can be selected using BorderType enumeration
- Line Style , represents the line style of border, which can be selected using CellBorderType enumeration
- Color , represents the line color of border, which can be selected using Color enumeration
Example1:
The following example shows how to set an outline border to a range.
[C#]
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Clears the worksheets
workbook.Worksheets.Clear();
//Adding a new worksheet to the Workbook object
workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];
//Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];
//Adding some value to the "A1" cell
cell.PutValue("Hello World From Aspose");
//Creating a range of cells starting from "A1" cell to 3rd column in a row
Range range = worksheet.Cells.CreateRange(0, 0, 1, 3);
//Adding a thick top border with blue line
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue);
//Adding a thick bottom border with blue line
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue);
//Adding a thick left border with blue line
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue);
//Adding a thick right border with blue line
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Blue);
//Saving the Excel file
workbook.Save("C:\\book1.xls");
[VB.NET]
'Instantiating a Workbook object
Dim workbook As Workbook = New Workbook()
'Clears the worksheets
workbook.Worksheets.Clear()
'Adding a new worksheet to the Excel object
workbook.Worksheets.Add()
'Obtaining the reference of the newly added worksheet by passing its sheet index
Dim worksheet As Worksheet = workbook.Worksheets(0)
'Accessing the "A1" cell from the worksheet
Dim cell As Cell = worksheet.Cells("A1")
'Adding some value to the "A1" cell
cell.PutValue("Hello World From Aspose")
'Creating a range of cells starting from "A1" cell to 3rd column in a row
Dim range As Range = worksheet.Cells.CreateRange(0, 0, 1, 3)
'Adding a thick top border with blue line
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue)
'Adding a thick bottom border with blue line
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue)
'Adding a thick left border with blue line
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue)
'Adding a thick right border with blue line
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Blue)
'Saving the Excel file
workbook.Save("c:\book1.xls")
The following output would be generated after executing the above code
Figure: Borders applied on a range of cells
Example2:
The following example shows how to set borders around each cell in the range.
[C#]
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
//Create a range of cells.
Range range = cells.CreateRange("A6", "P216");
//Declare style.
Style stl;
//Create the style adding to the style collection.
stl = workbook.Styles[workbook.Styles.Add()];
//Specify the font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
stl.Font.Color = Color.Blue;
//Set the borders
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
//Create StyleFlag object.
StyleFlag flg = new StyleFlag();
//Make the corresponding formatting attributes ON.
flg.Font = true;
flg.Borders = true;
//Apply the style with format setttings to the range.
range.ApplyStyle(stl,flg);
//Save the excel file.
workbook.Save(@"d:\test\testrangeborders.xls");
[VB.NET]
'Instantiate a new Workbook.
Dim workbook As New Workbook()
'Access the cells in the first worksheet.
Dim cells As Cells = workbook.Worksheets(0).Cells
'Create a range of cells.
Dim range As Range = cells.CreateRange("A6", "P216")
'Declare style.
Dim stl As Style
'Create the style adding to the style collection.
stl = workbook.Styles(workbook.Styles.Add())
'Specify the font settings.
stl.Font.Name = "Arial"
stl.Font.IsBold = True
stl.Font.Color = Color.Blue
'Set the borders
stl.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin
stl.Borders(BorderType.TopBorder).Color = Color.Blue
stl.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
stl.Borders(BorderType.LeftBorder).Color = Color.Blue
stl.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
stl.Borders(BorderType.BottomBorder).Color = Color.Blue
stl.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin
stl.Borders(BorderType.RightBorder).Color = Color.Blue
'Create StyleFlag object.
Dim flg As New StyleFlag()
'Make the corresponding formatting attributes ON.
flg.Font = True
flg.Borders = True
'Apply the style with format setttings to the range.
range.ApplyStyle(stl,flg)
'Save the excel file.
workbook.Save("d:\test\testrangeborders.xls")
Sometimes, you may need to add borders to a range of cells rather than just a single cell. Range object provides a SetOutlineBorder method that takes the following parameters to add a border to the range of cells:
- Border Type , represents the type of border, which can be selected using BorderType enumeration
- Line Style , represents the line style of border, which can be selected using CellBorderType enumeration
- Color , represents the line color of border, which can be selected using Color enumeration
Example1:
The following example shows how to set an outline border to a range.
[C#]
//Instantiating a Workbook object Workbook workbook = new Workbook(); //Clears the worksheets workbook.Worksheets.Clear(); //Adding a new worksheet to the Workbook object workbook.Worksheets.Add(); //Obtaining the reference of the newly added worksheet by passing its sheet index Worksheet worksheet = workbook.Worksheets[0]; //Accessing the "A1" cell from the worksheet Cell cell = worksheet.Cells["A1"]; //Adding some value to the "A1" cell cell.PutValue("Hello World From Aspose"); //Creating a range of cells starting from "A1" cell to 3rd column in a row Range range = worksheet.Cells.CreateRange(0, 0, 1, 3); //Adding a thick top border with blue line range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue); //Adding a thick bottom border with blue line range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue); //Adding a thick left border with blue line range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue); //Adding a thick right border with blue line range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Blue); //Saving the Excel file workbook.Save("C:\\book1.xls");
[VB.NET]
'Instantiating a Workbook object Dim workbook As Workbook = New Workbook() 'Clears the worksheets workbook.Worksheets.Clear() 'Adding a new worksheet to the Excel object workbook.Worksheets.Add() 'Obtaining the reference of the newly added worksheet by passing its sheet index Dim worksheet As Worksheet = workbook.Worksheets(0) 'Accessing the "A1" cell from the worksheet Dim cell As Cell = worksheet.Cells("A1") 'Adding some value to the "A1" cell cell.PutValue("Hello World From Aspose") 'Creating a range of cells starting from "A1" cell to 3rd column in a row Dim range As Range = worksheet.Cells.CreateRange(0, 0, 1, 3) 'Adding a thick top border with blue line range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue) 'Adding a thick bottom border with blue line range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue) 'Adding a thick left border with blue line range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue) 'Adding a thick right border with blue line range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Blue) 'Saving the Excel file workbook.Save("c:\book1.xls")
The following output would be generated after executing the above code
Figure: Borders applied on a range of cells
Example2:
The following example shows how to set borders around each cell in the range.
[C#]
//Instantiate a new Workbook. Workbook workbook = new Workbook(); //Access the cells in the first worksheet. Cells cells = workbook.Worksheets[0].Cells; //Create a range of cells. Range range = cells.CreateRange("A6", "P216"); //Declare style. Style stl; //Create the style adding to the style collection. stl = workbook.Styles[workbook.Styles.Add()]; //Specify the font settings. stl.Font.Name = "Arial"; stl.Font.IsBold = true; stl.Font.Color = Color.Blue; //Set the borders stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; stl.Borders[BorderType.TopBorder].Color = Color.Blue; stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; stl.Borders[BorderType.LeftBorder].Color = Color.Blue; stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; stl.Borders[BorderType.BottomBorder].Color = Color.Blue; stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; stl.Borders[BorderType.RightBorder].Color = Color.Blue; //Create StyleFlag object. StyleFlag flg = new StyleFlag(); //Make the corresponding formatting attributes ON. flg.Font = true; flg.Borders = true; //Apply the style with format setttings to the range. range.ApplyStyle(stl,flg); //Save the excel file. workbook.Save(@"d:\test\testrangeborders.xls");
[VB.NET]
'Instantiate a new Workbook. Dim workbook As New Workbook() 'Access the cells in the first worksheet. Dim cells As Cells = workbook.Worksheets(0).Cells 'Create a range of cells. Dim range As Range = cells.CreateRange("A6", "P216") 'Declare style. Dim stl As Style 'Create the style adding to the style collection. stl = workbook.Styles(workbook.Styles.Add()) 'Specify the font settings. stl.Font.Name = "Arial" stl.Font.IsBold = True stl.Font.Color = Color.Blue 'Set the borders stl.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Thin stl.Borders(BorderType.TopBorder).Color = Color.Blue stl.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Thin stl.Borders(BorderType.LeftBorder).Color = Color.Blue stl.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Thin stl.Borders(BorderType.BottomBorder).Color = Color.Blue stl.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Thin stl.Borders(BorderType.RightBorder).Color = Color.Blue 'Create StyleFlag object. Dim flg As New StyleFlag() 'Make the corresponding formatting attributes ON. flg.Font = True flg.Borders = True 'Apply the style with format setttings to the range. range.ApplyStyle(stl,flg) 'Save the excel file. workbook.Save("d:\test\testrangeborders.xls")
Copy Named Ranges
Aspose.Cells provides Range.Copy() method to copy a range of cells with formattings into another range.
Example:
The following example shows how to copy a source range of cells to another named range.
[C#]
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get all the worksheets in the book.
WorksheetCollection worksheets = workbook.Worksheets;
//Get the first worksheet in the worksheets collection.
Worksheet worksheet = workbook.Worksheets[0];
//Create a range of cells.
Range range1 = worksheet.Cells.CreateRange("E12", "I12");
//Name the range.
range1.Name = "MyRange";
//Set the outline border to the range.
range1.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128));
range1.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128));
range1.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128));
range1.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128));
//Input some data with some formattings into
//a few cells in the range.
range1[0, 0].PutValue("Test");
range1[0, 0].Style.Font.Color = Color.Red;
range1[0, 0].Style.Font.IsBold = true;
range1[0, 4].PutValue("123");
range1[0, 4].Style.Font.Color = Color.Blue;
range1[0, 4].Style.Font.IsBold = true;
//Create another range of cells.
Range range2 = worksheet.Cells.CreateRange("B3", "F3");
//Name the range.
range2.Name = "testrange";
//Copy the first range into second range.
range2.Copy(range1);
//Save the excel file.
workbook.Save("d:\\test\\copyranges.xls");
[VB.NET]
'Instantiate a new Workbook.
Dim workbook As Workbook = New Workbook()
'Get all the worksheets in the book.
Dim worksheets As WorksheetCollection = workbook.Worksheets
'Get the first worksheet in the worksheets collection.
Dim worksheet As Worksheet = workbook.Worksheets(0)
'Create a range of cells.
Dim range1 As Range = worksheet.Cells.CreateRange("E12", "I12")
'Name the range.
range1.Name = "MyRange"
'Set the outline border to the range.
range1.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
range1.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
range1.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
range1.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
'Input some data with some formattings into
'a few cells in the range.
range1(0, 0).PutValue("Test")
range1(0, 0).Style.Font.Color = Color.Red
range1(0, 0).Style.Font.IsBold = True
range1(0, 4).PutValue("123")
range1(0, 4).Style.Font.Color = Color.Blue
range1(0, 4).Style.Font.IsBold = True
'Create another range of cells.
Dim range2 As Range = worksheet.Cells.CreateRange("B3", "F3")
'Name the range.
range2.Name = "testrange"
'Copy the first range into second range.
range2.Copy(range1)
'Save the excel file.
workbook.Save("d:\test\copyranges.xls")
The following output would be generated after executing the above code
Figure: A Range is copied to another range.
Aspose.Cells provides Range.Copy() method to copy a range of cells with formattings into another range.
Example:
The following example shows how to copy a source range of cells to another named range.
[C#]
//Instantiate a new Workbook. Workbook workbook = new Workbook(); //Get all the worksheets in the book. WorksheetCollection worksheets = workbook.Worksheets; //Get the first worksheet in the worksheets collection. Worksheet worksheet = workbook.Worksheets[0]; //Create a range of cells. Range range1 = worksheet.Cells.CreateRange("E12", "I12"); //Name the range. range1.Name = "MyRange"; //Set the outline border to the range. range1.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)); range1.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)); range1.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)); range1.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)); //Input some data with some formattings into //a few cells in the range. range1[0, 0].PutValue("Test"); range1[0, 0].Style.Font.Color = Color.Red; range1[0, 0].Style.Font.IsBold = true; range1[0, 4].PutValue("123"); range1[0, 4].Style.Font.Color = Color.Blue; range1[0, 4].Style.Font.IsBold = true; //Create another range of cells. Range range2 = worksheet.Cells.CreateRange("B3", "F3"); //Name the range. range2.Name = "testrange"; //Copy the first range into second range. range2.Copy(range1); //Save the excel file. workbook.Save("d:\\test\\copyranges.xls");
[VB.NET]
'Instantiate a new Workbook. Dim workbook As Workbook = New Workbook() 'Get all the worksheets in the book. Dim worksheets As WorksheetCollection = workbook.Worksheets 'Get the first worksheet in the worksheets collection. Dim worksheet As Worksheet = workbook.Worksheets(0) 'Create a range of cells. Dim range1 As Range = worksheet.Cells.CreateRange("E12", "I12") 'Name the range. range1.Name = "MyRange" 'Set the outline border to the range. range1.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)) range1.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)) range1.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)) range1.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)) 'Input some data with some formattings into 'a few cells in the range. range1(0, 0).PutValue("Test") range1(0, 0).Style.Font.Color = Color.Red range1(0, 0).Style.Font.IsBold = True range1(0, 4).PutValue("123") range1(0, 4).Style.Font.Color = Color.Blue range1(0, 4).Style.Font.IsBold = True 'Create another range of cells. Dim range2 As Range = worksheet.Cells.CreateRange("B3", "F3") 'Name the range. range2.Name = "testrange" 'Copy the first range into second range. range2.Copy(range1) 'Save the excel file. workbook.Save("d:\test\copyranges.xls")
The following output would be generated after executing the above code
Figure: A Range is copied to another range.
Merge Cells in the Named Range
Aspose.Cells provides Range.Merge() method to merge the cells in the range.
Example:
The following example shows how to merge the individual cells of a named range.
[C#]
//Instantiate a new Workbook.
Workbook wb1 = new Workbook();
//Get the first worksheet in the workbook.
Worksheet worksheet1 = wb1.Worksheets[0];
//Create a range.
Range mrange = worksheet1.Cells.CreateRange("A18", "J18");
//Name the range.
mrange.Name = "Details";
//Merge the cells of the range.
mrange.Merge();
//Get the range.
Range range1 = wb1.Worksheets.GetRangeByName("Details");
//Add a style object to the collection.
int i = wb1.Styles.Add();
//Define a style object.
Style style = wb1.Styles[i];
//Set the alignment.
style.HorizontalAlignment = TextAlignmentType.Center;
//Create a StyleFlag object.
StyleFlag flag = new StyleFlag();
//Make the relative style attribute ON.
flag.HorizontalAlignment = true;
//Apply the style to the range.
range1.ApplyStyle(style,flag);
//Input data into range.
range1[0, 0].PutValue("Aspose");
//Save the excel file.
wb1.Save("d:\\test\\mergingrange.xls");
[VB.NET]
'Instantiate a new Workbook.
Dim wb1 As Workbook = New Workbook()
'Get the first worksheet in the workbook.
Dim worksheet1 As Worksheet = wb1.Worksheets(0)
'Create a range.
Dim mrange As Range = worksheet1.Cells.CreateRange("A18", "J18")
'Name the range.
mrange.Name = "Details"
'Merge the cells of the range.
mrange.Merge()
'Get the range.
Dim range1 As Range = wb1.Worksheets.GetRangeByName("Details")
'Add a style object to the collection.
Dim i As Integer = wb1.Styles.Add()
'Define a style object.
Dim style As Style = wb1.Styles(i)
'Set the alignment.
style.HorizontalAlignment = TextAlignmentType.Center
'Create a StyleFlag object.
Dim flag As New StyleFlag()
'Make the relative style attribute ON.
flag.HorizontalAlignment = True
'Apply the style to the range.
range1.ApplyStyle(style,flag)
'Input data into range.
range1(0, 0).PutValue("Aspose")
'Save the excel file.
wb1.Save("d:\test\mergingrange.xls")
Aspose.Cells provides Range.Merge() method to merge the cells in the range.
Example:
The following example shows how to merge the individual cells of a named range.
[C#]
//Instantiate a new Workbook. Workbook wb1 = new Workbook(); //Get the first worksheet in the workbook. Worksheet worksheet1 = wb1.Worksheets[0]; //Create a range. Range mrange = worksheet1.Cells.CreateRange("A18", "J18"); //Name the range. mrange.Name = "Details"; //Merge the cells of the range. mrange.Merge(); //Get the range. Range range1 = wb1.Worksheets.GetRangeByName("Details"); //Add a style object to the collection. int i = wb1.Styles.Add(); //Define a style object. Style style = wb1.Styles[i]; //Set the alignment. style.HorizontalAlignment = TextAlignmentType.Center; //Create a StyleFlag object. StyleFlag flag = new StyleFlag(); //Make the relative style attribute ON. flag.HorizontalAlignment = true; //Apply the style to the range. range1.ApplyStyle(style,flag); //Input data into range. range1[0, 0].PutValue("Aspose"); //Save the excel file. wb1.Save("d:\\test\\mergingrange.xls");
[VB.NET]
'Instantiate a new Workbook. Dim wb1 As Workbook = New Workbook() 'Get the first worksheet in the workbook. Dim worksheet1 As Worksheet = wb1.Worksheets(0) 'Create a range. Dim mrange As Range = worksheet1.Cells.CreateRange("A18", "J18") 'Name the range. mrange.Name = "Details" 'Merge the cells of the range. mrange.Merge() 'Get the range. Dim range1 As Range = wb1.Worksheets.GetRangeByName("Details") 'Add a style object to the collection. Dim i As Integer = wb1.Styles.Add() 'Define a style object. Dim style As Style = wb1.Styles(i) 'Set the alignment. style.HorizontalAlignment = TextAlignmentType.Center 'Create a StyleFlag object. Dim flag As New StyleFlag() 'Make the relative style attribute ON. flag.HorizontalAlignment = True 'Apply the style to the range. range1.ApplyStyle(style,flag) 'Input data into range. range1(0, 0).PutValue("Aspose") 'Save the excel file. wb1.Save("d:\test\mergingrange.xls")
Remove a Named Range
Aspose.Cells provides NameCollection.RemoveAt() method to erase the name of the range. To clear the contents of the range you may utilize Cells.ClearRange() method.
Example:
The following example shows how to remove a named range with its contents.
[C#]
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get all the worksheets in the book.
WorksheetCollection worksheets = workbook.Worksheets;
//Get the first worksheet in the worksheets collection.
Worksheet worksheet = workbook.Worksheets[0];
//Create a range of cells.
Range range1 = worksheet.Cells.CreateRange("E12", "I12");
//Name the range.
range1.Name = "MyRange";
//Set the outline border to the range.
range1.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128));
range1.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128));
range1.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128));
range1.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128));
//Input some data with some formattings into
//a few cells in the range.
range1[0, 0].PutValue("Test");
range1[0, 0].Style.Font.Color = Color.Red;
range1[0, 0].Style.Font.IsBold = true;
range1[0, 4].PutValue("123");
range1[0, 4].Style.Font.Color = Color.Blue;
range1[0, 4].Style.Font.IsBold = true;
//Create another range of cells.
Range range2 = worksheet.Cells.CreateRange("B3", "F3");
//Name the range.
range2.Name = "testrange";
//Copy the first range into second range.
range2.Copy(range1);
//Remove the previous named range (range1) with its contents.
worksheet.Cells.ClearRange(11, 4, 11, 8);
worksheets.Names.RemoveAt(0);
//Save the excel file.
workbook.Save("d:\\test\\copyranges.xls");
[VB.NET]
'Instantiate a new Workbook.
Dim workbook As Workbook = New Workbook()
'Get all the worksheets in the book.
Dim worksheets As WorksheetCollection = workbook.Worksheets
'Get the first worksheet in the worksheets collection.
Dim worksheet As Worksheet = workbook.Worksheets(0)
'Create a range of cells.
Dim range1 As Range = worksheet.Cells.CreateRange("E12","I12")
'Name the range.
range1.Name = "MyRange"
'Set the outline border to the range.
range1.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium,
Color.FromArgb(0, 0, 128))
range1.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
range1.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
range1.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128))
'Input some data with some formattings into
'a few cells in the range.
range1(0,0).PutValue("Test")
range1(0,0).Style.Font.Color = Color.Red
range1(0,0).Style.Font.IsBold = True
range1(0,4).PutValue("123")
range1(0,4).Style.Font.Color = Color.Blue
range1(0,4).Style.Font.IsBold = True
'Create another range of cells.
Dim range2 As Range = worksheet.Cells.CreateRange("B3","F3")
'Name the range.
range2.Name = "testrange"
'Copy the first range into second range.
range2.Copy(range1)
'Remove the previous named range (range1) with its contents.
worksheet.Cells.ClearRange(11,4,11,8)
worksheets.Names.RemoveAt(0)
'Save the excel file.
workbook.Save("d:\test\copyranges.xls")
Aspose.Cells provides NameCollection.RemoveAt() method to erase the name of the range. To clear the contents of the range you may utilize Cells.ClearRange() method.
Example:
The following example shows how to remove a named range with its contents.
[C#]
//Instantiate a new Workbook. Workbook workbook = new Workbook(); //Get all the worksheets in the book. WorksheetCollection worksheets = workbook.Worksheets; //Get the first worksheet in the worksheets collection. Worksheet worksheet = workbook.Worksheets[0]; //Create a range of cells. Range range1 = worksheet.Cells.CreateRange("E12", "I12"); //Name the range. range1.Name = "MyRange"; //Set the outline border to the range. range1.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)); range1.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)); range1.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)); range1.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)); //Input some data with some formattings into //a few cells in the range. range1[0, 0].PutValue("Test"); range1[0, 0].Style.Font.Color = Color.Red; range1[0, 0].Style.Font.IsBold = true; range1[0, 4].PutValue("123"); range1[0, 4].Style.Font.Color = Color.Blue; range1[0, 4].Style.Font.IsBold = true; //Create another range of cells. Range range2 = worksheet.Cells.CreateRange("B3", "F3"); //Name the range. range2.Name = "testrange"; //Copy the first range into second range. range2.Copy(range1); //Remove the previous named range (range1) with its contents. worksheet.Cells.ClearRange(11, 4, 11, 8); worksheets.Names.RemoveAt(0); //Save the excel file. workbook.Save("d:\\test\\copyranges.xls");
[VB.NET]
'Instantiate a new Workbook. Dim workbook As Workbook = New Workbook() 'Get all the worksheets in the book. Dim worksheets As WorksheetCollection = workbook.Worksheets 'Get the first worksheet in the worksheets collection. Dim worksheet As Worksheet = workbook.Worksheets(0) 'Create a range of cells. Dim range1 As Range = worksheet.Cells.CreateRange("E12","I12") 'Name the range. range1.Name = "MyRange" 'Set the outline border to the range. range1.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)) range1.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)) range1.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)) range1.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.FromArgb(0, 0, 128)) 'Input some data with some formattings into 'a few cells in the range. range1(0,0).PutValue("Test") range1(0,0).Style.Font.Color = Color.Red range1(0,0).Style.Font.IsBold = True range1(0,4).PutValue("123") range1(0,4).Style.Font.Color = Color.Blue range1(0,4).Style.Font.IsBold = True 'Create another range of cells. Dim range2 As Range = worksheet.Cells.CreateRange("B3","F3") 'Name the range. range2.Name = "testrange" 'Copy the first range into second range. range2.Copy(range1) 'Remove the previous named range (range1) with its contents. worksheet.Cells.ClearRange(11,4,11,8) worksheets.Names.RemoveAt(0) 'Save the excel file. workbook.Save("d:\test\copyranges.xls")
Rename a Named Range
Aspose.Cells allows you to rename a named range for your need. You may get the named range and rename it by using Name.Text attribute.
Example:
The following example shows how to rename a named range.
[C#]
//Open an existing Excel file that has a (global) named range "MyRange" in it
Workbook workbook = new Workbook("e:\\test\\Book1.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the Cells of the sheet
Cells cells = sheet.Cells;
//Get the named range "MyRange"
Name name = workbook.Worksheets.Names["MyRange"];
//Rename it
name.Text = "NewRange";
//Save the Excel file
workbook.Save("e:\\test\\RenamingRange.xlsx");
[VB.NET]
'Open an existing Excel file that has a (global) named range "MyRange" in it
Dim workbook As New Workbook("e:\test\Book1.xlsx")
'Get the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Get the Cells of the sheet
Dim cells As Cells = sheet.Cells
'Get the named range "MyRange"
Dim name As Name = workbook.Worksheets.Names("MyRange")
'Rename it
name.Text = "NewRange"
'Save the Excel file
workbook.Save("e:\test\RenamingRange.xlsx")
Aspose.Cells allows you to rename a named range for your need. You may get the named range and rename it by using Name.Text attribute.
Example:
The following example shows how to rename a named range.
[C#]
//Open an existing Excel file that has a (global) named range "MyRange" in it Workbook workbook = new Workbook("e:\\test\\Book1.xlsx"); //Get the first worksheet Worksheet sheet = workbook.Worksheets[0]; //Get the Cells of the sheet Cells cells = sheet.Cells; //Get the named range "MyRange" Name name = workbook.Worksheets.Names["MyRange"]; //Rename it name.Text = "NewRange"; //Save the Excel file workbook.Save("e:\\test\\RenamingRange.xlsx");
[VB.NET]
'Open an existing Excel file that has a (global) named range "MyRange" in it Dim workbook As New Workbook("e:\test\Book1.xlsx") 'Get the first worksheet Dim sheet As Worksheet = workbook.Worksheets(0) 'Get the Cells of the sheet Dim cells As Cells = sheet.Cells 'Get the named range "MyRange" Dim name As Name = workbook.Worksheets.Names("MyRange") 'Rename it name.Text = "NewRange" 'Save the Excel file workbook.Save("e:\test\RenamingRange.xlsx")
Intersection of Ranges
Aspose.Cells provides Range.Intersect method to intersect two ranges, the method returns a Range object. To check whether a range intersects other range, you may use Range.IsIntersect method that returns a Boolean value.
Example:
The following example shows how to intersect the ranges.
[C#]
//Instantiate a workbook object.
//Open an existing excel file.
Workbook workbook = new Workbook(@"F:\test\rngBook2.xls");
//Get the named ranges.
Range[] ranges = workbook.Worksheets.GetNamedRanges();
//Check whether the first range intersect the second range.
bool isintersect = ranges[0].IsIntersect(ranges[1]);
//Create a style object.
Style style = workbook.Styles[workbook.Styles.Add()];
//Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
//Create a styleflag object.
StyleFlag flag = new StyleFlag();
//Apply the cellshading.
flag.CellShading = true;
//If first range intersects second range.
if (isintersect)
{
//Create a range by getting the intersection.
Range intersection = ranges[0].Intersect(ranges[1]);
//Name the range.
intersection.Name = "Intersection";
//Apply the style to the range.
intersection.ApplyStyle(style, flag);
}
//Save the excel file.
workbook.Save("f:\\test\\rngIntersection.xls");
[VB.NET]
'Instantiate a workbook object.
'Open an existing excel file.
Dim workbook As New Workbook("F:\test\rngBook2.xls")
'Get the named ranges.
Dim ranges() As Range = workbook.Worksheets.GetNamedRanges()
'Check whether the first range intersect the second range.
Dim isintersect As Boolean = ranges(0).IsIntersect(ranges(1))
'Create a style object.
Dim style As Style = workbook.Styles(workbook.Styles.Add())
'Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow
style.Pattern = BackgroundType.Solid
'Create a styleflag object.
Dim flag As New StyleFlag()
'Apply the cellshading.
flag.CellShading = True
'If first range intersects second range.
If isintersect Then
'Create a range by getting the intersection.
Dim intersection As Range = ranges(0).Intersect(ranges(1))
'Name the range.
intersection.Name = "Intersection"
'Apply the style to the range.
intersection.ApplyStyle(style, flag)
End If
'Save the excel file.
workbook.Save("f:\test\rngIntersection.xls")
Aspose.Cells provides Range.Intersect method to intersect two ranges, the method returns a Range object. To check whether a range intersects other range, you may use Range.IsIntersect method that returns a Boolean value.
Example:
The following example shows how to intersect the ranges.
[C#]
//Instantiate a workbook object. //Open an existing excel file. Workbook workbook = new Workbook(@"F:\test\rngBook2.xls"); //Get the named ranges. Range[] ranges = workbook.Worksheets.GetNamedRanges(); //Check whether the first range intersect the second range. bool isintersect = ranges[0].IsIntersect(ranges[1]); //Create a style object. Style style = workbook.Styles[workbook.Styles.Add()]; //Set the shading color with solid pattern type. style.ForegroundColor = Color.Yellow; style.Pattern = BackgroundType.Solid; //Create a styleflag object. StyleFlag flag = new StyleFlag(); //Apply the cellshading. flag.CellShading = true; //If first range intersects second range. if (isintersect) { //Create a range by getting the intersection. Range intersection = ranges[0].Intersect(ranges[1]); //Name the range. intersection.Name = "Intersection"; //Apply the style to the range. intersection.ApplyStyle(style, flag); } //Save the excel file. workbook.Save("f:\\test\\rngIntersection.xls");
[VB.NET]
'Instantiate a workbook object.
'Open an existing excel file.
Dim workbook As New Workbook("F:\test\rngBook2.xls")
'Get the named ranges.
Dim ranges() As Range = workbook.Worksheets.GetNamedRanges()
'Check whether the first range intersect the second range.
Dim isintersect As Boolean = ranges(0).IsIntersect(ranges(1))
'Create a style object.
Dim style As Style = workbook.Styles(workbook.Styles.Add())
'Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow
style.Pattern = BackgroundType.Solid
'Create a styleflag object.
Dim flag As New StyleFlag()
'Apply the cellshading.
flag.CellShading = True
'If first range intersects second range.
If isintersect Then
'Create a range by getting the intersection.
Dim intersection As Range = ranges(0).Intersect(ranges(1))
'Name the range.
intersection.Name = "Intersection"
'Apply the style to the range.
intersection.ApplyStyle(style, flag)
End If
'Save the excel file.
workbook.Save("f:\test\rngIntersection.xls")
Union of Ranges
Aspose.Cells provides Range.Union method to take the union for ranges, the method returns an ArrayList object.
Example:
The following example shows how to take union for ranges.
[C#]
//Instantiate a workbook object.
//Open an existing excel file.
Workbook workbook = new Workbook(@"F:\test\rngBook2.xls");
//Get the named ranges.
Range[] ranges = workbook.Worksheets.GetNamedRanges();
//Create a style object.
Style style = workbook.Styles[workbook.Styles.Add()];
//Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
//Create a styleflag object.
StyleFlag flag = new StyleFlag();
//Apply the cellshading.
flag.CellShading = true;
//Creates an arraylist.
ArrayList al = new ArrayList();
//Get the arraylist collection apply the union operation.
al = ranges[1].Union(ranges[2]);
//Define a range object.
Range rng;
int frow, fcol, erow, ecol;
for (int i = 0; i < al.Count; i++)
{
//Get a range.
rng = (Range)al[i];
frow = rng.FirstRow;
fcol = rng.FirstColumn;
erow = rng.RowCount;
ecol = rng.ColumnCount;
//Apply the style to the range.
rng.ApplyStyle(style, flag);
}
//Save the excel file.
workbook.Save("f:\\test\\rngUnion.xls");
[VB.NET]
'Instantiate a workbook object.
'Open an existing excel file.
Dim workbook As New Workbook("F:\test\rngBook2.xls")
'Get the named ranges.
Dim ranges() As Range = workbook.Worksheets.GetNamedRanges()
'Create a style object.
Dim style As Style = workbook.Styles(workbook.Styles.Add())
'Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow
style.Pattern = BackgroundType.Solid
'Create a styleflag object.
Dim flag As New StyleFlag()
'Apply the cellshading.
flag.CellShading = True
'Creates an arraylist.
Dim al As New ArrayList()
'Get the arraylist collection apply the union operation.
al = ranges(1).Union(ranges(2))
'Define a range object.
Dim rng As Range
Dim frow, fcol, erow, ecol As Integer
For i As Integer = 0 To al.Count - 1
'Get a range.
rng = CType(al(i), Range)
frow = rng.FirstRow
fcol = rng.FirstColumn
erow = rng.RowCount
ecol = rng.ColumnCount
'Apply the style to the range.
rng.ApplyStyle(style, flag)
Next i
'Save the excel file.
workbook.Save("f:\test\rngUnion.xls")
Aspose.Cells provides Range.Union method to take the union for ranges, the method returns an ArrayList object.
Example:
The following example shows how to take union for ranges.
[C#]
//Instantiate a workbook object. //Open an existing excel file. Workbook workbook = new Workbook(@"F:\test\rngBook2.xls"); //Get the named ranges. Range[] ranges = workbook.Worksheets.GetNamedRanges(); //Create a style object. Style style = workbook.Styles[workbook.Styles.Add()]; //Set the shading color with solid pattern type. style.ForegroundColor = Color.Yellow; style.Pattern = BackgroundType.Solid; //Create a styleflag object. StyleFlag flag = new StyleFlag(); //Apply the cellshading. flag.CellShading = true; //Creates an arraylist. ArrayList al = new ArrayList(); //Get the arraylist collection apply the union operation. al = ranges[1].Union(ranges[2]); //Define a range object. Range rng; int frow, fcol, erow, ecol; for (int i = 0; i < al.Count; i++) { //Get a range. rng = (Range)al[i]; frow = rng.FirstRow; fcol = rng.FirstColumn; erow = rng.RowCount; ecol = rng.ColumnCount; //Apply the style to the range. rng.ApplyStyle(style, flag); } //Save the excel file. workbook.Save("f:\\test\\rngUnion.xls");
[VB.NET]
'Instantiate a workbook object.
'Open an existing excel file.
Dim workbook As New Workbook("F:\test\rngBook2.xls")
'Get the named ranges.
Dim ranges() As Range = workbook.Worksheets.GetNamedRanges()
'Create a style object.
Dim style As Style = workbook.Styles(workbook.Styles.Add())
'Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow
style.Pattern = BackgroundType.Solid
'Create a styleflag object.
Dim flag As New StyleFlag()
'Apply the cellshading.
flag.CellShading = True
'Creates an arraylist.
Dim al As New ArrayList()
'Get the arraylist collection apply the union operation.
al = ranges(1).Union(ranges(2))
'Define a range object.
Dim rng As Range
Dim frow, fcol, erow, ecol As Integer
For i As Integer = 0 To al.Count - 1
'Get a range.
rng = CType(al(i), Range)
frow = rng.FirstRow
fcol = rng.FirstColumn
erow = rng.RowCount
ecol = rng.ColumnCount
'Apply the style to the range.
rng.ApplyStyle(style, flag)
Next i
'Save the excel file.
workbook.Save("f:\test\rngUnion.xls")