Format and Modify Named Ranges
Format Ranges
Setting Background Color and Font Attributes to a Named Range
To apply formatting, define a Style object to specify the style settings and apply it to the Range object.
The following example shows how to set the solid fill color (shading color) with font settings to a range.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// 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.CreateStyle(); | |
// 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(dataDir + "rangestyles.out.xls"); |
Adding Borders to a Named Range
It is possible to add borders to a range of cells instead of just a single cell. The Range object provides a SetOutlineBorder method that takes the following parameters to add a border to the range of cells:
- Border type, the type of border, selected from the BorderType enumeration.
- Line style, the line style, selected from the CellBorderType enumeration.
- Color, the line color, selected from the Color enumeration.
The following example shows how to set an outline border to a range.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// 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(dataDir + "book1.out.xls"); |
The following example shows how to set borders around each cell in the range.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// 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.CreateStyle(); | |
// 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 settings to the range. | |
range.ApplyStyle(stl, flg); | |
// Save the excel file. | |
workbook.Save( dataDir + "output.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. The following example shows how to rename a named range.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Open an existing Excel file that has a (global) named range "TestRange" in it | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// 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["TestRange"]; | |
// Rename it | |
name.Text = "NewRange"; | |
// Save the Excel file | |
workbook.Save(dataDir + "RenamingRange.out.xlsx"); |
Union of Ranges
Aspose.Cells provides Range.Union method to take the union for ranges, the method returns an ArrayList object. The following example shows how to take union for ranges.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Instantiate a workbook object. | |
// Open an existing excel file. | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Get the named ranges. | |
Range[] ranges = workbook.Worksheets.GetNamedRanges(); | |
// Create a style object. | |
Style style = workbook.CreateStyle(); | |
// 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[0].UnionRanges(ranges[1]); | |
// 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(dataDir + "rngUnion.out.xls"); |
Intersection of Ranges
Aspose.Cells provides the Range.Intersect method to intersect two ranges. The method returns a Range object. To check whether a range intersects another range, use the Range.Intersect method that returns a Boolean value. The following example shows how to intersect the ranges.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Instantiate a workbook object. | |
// Open an existing excel file. | |
Workbook workbook = new Workbook(dataDir + "book1.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.CreateStyle(); | |
// 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(dataDir + "rngIntersection.out.xls"); |
Merge Cells in the Named Range
Aspose.Cells provides Range.Merge() method to merge the cells in the range. The following example shows how to merge the individual cells of a named range.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// 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"); | |
// Define a style object. | |
Style style = wb1.CreateStyle(); | |
// 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(dataDir + "mergingrange.out.xls"); |
Remove a Named Range
Aspose.Cells provides the NameCollection.RemoveAt() method to erase the name of the range. To clear the contents of the range, use Cells.ClearRange() method. The following example shows how to remove a named range with its contents.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// 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, 4].PutValue("123"); | |
// 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(dataDir + "copyranges.out.xls"); |