命名范围

通常，列和行标签用于引用单个单元格。可以创建描述性名称来表示单元格、单元格区域、公式或常量值。这个单词姓名可以指表示单元格、单元格区域、公式或常量值的字符串。为范围指定名称意味着可以通过其名称引用该单元格范围。使用易于理解的名称（例如 Products）来指代难以理解的范围，例如 Sales!C20:C30。标签可用于引用同一工作表上数据的公式中；如果你想在另一个工作表上表示一个范围，你可以使用一个名称。 *命名区域是 Microsoft Excel 最强大的功能之一，尤其是用作列表控件、数据透视表、图表等的源区域时。

创建命名范围

使用 Microsoft Excel

以下步骤描述了如何使用 Microsoft Excel 命名一个单元格或单元格区域。此方法适用于Microsoft Office Excel 2003、Microsoft Excel 97、2000、2002。

1. 选择要命名的单元格、单元格区域。
2. 单击编辑栏左端的名称框。
3. 键入单元格的名称。
4. 按 ENTER。

在更改单元格的内容时不能命名单元格。

使用 Aspose.Cells

在这里，我们使用 Aspose.Cells API 来完成任务。

Aspose.Cells提供了一个类，工作簿，代表一个 Microsoft Excel 文件。这工作簿类包含一个工作表集合允许访问 Excel 文件中的每个工作表。工作表由工作表班级。这工作表类提供了Cells收藏。

可以通过调用重载来创建命名范围[创建范围](https://reference.aspose.com/cells/java/com.aspose.cells/cells#createRange(java.lang.String,%20java.lang.String) 的方法Cells收藏。一个典型的版本[创建范围](https://reference.aspose.com/cells/java/com.aspose.cells/cells#createRange(java.lang.String,%20java.lang.String)方法采用以下参数：

• 左上角单元格的名称，区域中左上角单元格的名称。
• 右下角单元格的名称，范围内右下角单元格的名称。

当。。。的时候[创建范围](https://reference.aspose.com/cells/java/com.aspose.cells/cells#createRange(java.lang.String,%20java.lang.String) 方法被调用，它返回新创建的命名范围作为实例范围班级。

下面的示例演示如何创建扩展到 B4:G14 的命名单元格区域。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(CreateNamedRangeofCells.class) + "data/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

WorksheetCollection worksheets = workbook.getWorksheets();

// Accessing the first worksheet in the Excel file
Worksheet sheet = worksheets.get(0);
Cells cells = sheet.getCells();

// Creating a named range
Range namedRange = cells.createRange("B4", "G14");
namedRange.setName("TestRange");

// Saving the modified Excel file in default (that is Excel 2000) format
workbook.save(dataDir + "CNROfCells_out.xls");

// Print message
System.out.println("Process completed successfully");

访问电子表格中的所有命名范围

打电话给[获取命名范围](https://reference.aspose.com/cells/java/com.aspose.cells/worksheetcollection#getNamedRanges() 的方法工作表集合获取电子表格中的所有命名范围。这[获取命名范围](https://reference.aspose.com/cells/java/com.aspose.cells/worksheetcollection#getNamedRanges() 方法返回一个包含所有命名范围的数组工作表集合.

下面的示例演示如何访问工作簿中的所有命名范围。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AccessAllNamedRanges.class) + "data/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

WorksheetCollection worksheets = workbook.getWorksheets();

// Accessing the first worksheet in the Excel file
Worksheet sheet = worksheets.get(0);
Cells cells = sheet.getCells();

// Getting all named ranges
Range[] namedRanges = worksheets.getNamedRanges();

// Print message
System.out.println("Number of Named Ranges : " + namedRanges.length);

访问特定的命名范围

打电话给工作表集合收藏的[获取范围名称](https://reference.aspose.com/cells/java/com.aspose.cells/worksheetcollection#getRangeByName(java.lang.String) 方法按名称获取指定范围。一个典型的[获取范围名称](https://reference.aspose.com/cells/java/com.aspose.cells/worksheetcollection#getRangeByName(java.lang.String) 方法采用命名范围的名称，并将指定的命名范围作为范围班级。

以下示例显示如何通过名称访问指定范围。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AccessSpecificNamedRange.class) + "data/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

WorksheetCollection worksheets = workbook.getWorksheets();

// Accessing the first worksheet in the Excel file
Worksheet sheet = worksheets.get(0);
Cells cells = sheet.getCells();

// Getting the specified named range
Range namedRange = worksheets.getRangeByName("TestRange");

// Print message
System.out.println("Named Range : " + namedRange.getRefersTo());

在命名范围内识别 Cells

使用 Aspose.Cells，您可以将数据插入范围的各个单元格中。假设您有一个命名的单元格区域，即 A1:C4。因此矩阵将生成 4 * 3 = 12 个单元格，并且各个范围单元格按顺序排列。 Aspose.Cells 为您提供了 Range 类的一些有用属性，用于访问范围内的各个单元格。您可以使用以下方法来识别区域中的单元格：

• 获取第一行返回命名范围中第一行的索引。
• 获取第一列返回命名范围中第一列的索引。

下面的示例演示如何将一些值输入到指定区域的单元格中。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(IdentifyCellsinNamedRange.class) + "data/";

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

WorksheetCollection worksheets = workbook.getWorksheets();

// Accessing the first worksheet in the Excel file
Worksheet sheet = worksheets.get(0);
Cells cells = sheet.getCells();

// Getting the specified named range
Range range = worksheets.getRangeByName("TestRange");

// Identify range cells.
System.out.println("First Row : " + range.getFirstRow());
System.out.println("First Column : " + range.getFirstColumn());
System.out.println("Row Count : " + range.getRowCount());
System.out.println("Column Count : " + range.getColumnCount());

在命名范围内输入数据到 Cells

使用 Aspose.Cells，您可以将数据插入范围的各个单元格中。假设您有一个命名的单元格区域，即 H1:J4。因此矩阵将生成 4 * 3 = 12 个单元格，并且各个范围单元格按顺序排列。 Aspose.Cells 为您提供了 Range 类的一些有用属性，用于访问范围内的各个单元格。您可以使用以下属性来识别范围内的单元格：

• 获取第一行返回命名范围中第一行的索引。
• 获取第一列返回命名范围中第一列的索引。

下面的示例演示如何将一些值输入到指定区域的单元格中。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(InputDataInCellsInRange.class) + "data/";

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet in the workbook.
Worksheet worksheet1 = workbook.getWorksheets().get(0);

// Create a range of cells and specify its name based on H1:J4.
Range range = worksheet1.getCells().createRange("H1:J4");
range.setName("MyRange");

// Input some data into cells in the range.
range.get(0, 0).setValue("USA");
range.get(0, 1).setValue("SA");
range.get(0, 2).setValue("Israel");
range.get(1, 0).setValue("UK");
range.get(1, 1).setValue("AUS");
range.get(1, 2).setValue("Canada");
range.get(2, 0).setValue("France");
range.get(2, 1).setValue("India");
range.get(2, 2).setValue("Egypt");
range.get(3, 0).setValue("China");
range.get(3, 1).setValue("Philipine");
range.get(3, 2).setValue("Brazil");

// Save the excel file.
workbook.save(dataDir + "IDICInRange_out.xls");

// Print message
System.out.println("Process completed successfully");

格式化范围…将背景颜色和字体属性设置为命名范围

要应用格式，定义一个风格对象指定样式设置并将其应用于范围目的。

以下示例显示如何使用字体设置将纯色填充颜色（底纹颜色）设置为一个范围。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(FormatRanges1.class) + "data/";

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet in the book.
Worksheet WS = workbook.getWorksheets().get(0);

// Create a named range of cells.
com.aspose.cells.Range range = WS.getCells().createRange(1, 1, 1, 17);
range.setName("MyRange");

// Declare a style object.
Style stl;

// Create the style object with respect to the style of a cell.
stl = WS.getCells().get(1, 1).getStyle();

// Specify some Font settings.
stl.getFont().setName("Arial");
stl.getFont().setBold(true);

// Set the font text color
stl.getFont().setColor(Color.getRed());

// To Set the fill color of the range, you may use ForegroundColor with
// solid Pattern setting.
stl.setBackgroundColor(Color.getYellow());
stl.setPattern(BackgroundType.SOLID);

// Apply the style to the range.
for (int r = 1; r < 2; r++) {
	for (int c = 1; c < 18; c++) {
		WS.getCells().get(r, c).setStyle(stl);
	}

}

// Save the excel file.
workbook.save(dataDir + "FormatRanges1_out.xls");

// Print message
System.out.println("Process completed successfully");

格式化范围…向命名范围添加边框

可以为一系列单元格而不是单个单元格添加边框。这范围对象提供了一个[设置轮廓边框](https://reference.aspose.com/cells/java/com.aspose.cells/range#setOutlineBorders(int,%20com.aspose.cells.Color)方法，它采用以下参数为单元格区域添加边框：

• borderStyle：边框的类型，从单元格边框类型枚举。
• borderColor：边框的线条颜色，从颜色枚举。

下面的示例演示如何将轮廓边框设置为范围。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(FormatRanges2.class) + "data/";

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Workbook object
// Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);

// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.getCells().get("A1");

// Adding some value to the "A1" cell
cell.setValue("Hello World From Aspose");

// Creating a range of cells starting from "A1" cell to 3rd column in a
// row
Range range = worksheet.getCells().createRange("A1:C1");
range.setName("MyRange");

// Adding a thick outline border with the blue line
range.setOutlineBorders(CellBorderType.THICK, Color.getBlue());

// Saving the Excel file
workbook.save(dataDir + "FormatRanges2_out.xls");

// Print message
System.out.println("Process completed successfully");

执行上述代码后会产生如下输出：

将样式应用于范围内的单元格

有时，您想要创建将样式应用于范围.为此，您可以遍历范围内的单元格并使用[Cell.setStyle](https://reference.aspose.com/cells/java/com.aspose.cells/cell#setStyle(com.aspose.cells.Style)方法将样式应用于单元格。

下面的示例演示如何将样式应用于范围中的单元格。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConvertCellsAddresstoRangeorCellArea.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);

// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.getCells().get("A1");

// Adding some value to the "A1" cell
cell.setValue("Hello World!");

// Creating a range of cells based on cells Address.
Range range = worksheet.getCells().createRange("A1:F10");

// Specify a Style object for borders.
Style style = cell.getStyle();
// Setting the line style of the top border

style.setBorder(BorderType.TOP_BORDER, CellBorderType.THICK, Color.getBlack());

style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THICK, Color.getBlack());

style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THICK, Color.getBlack());

style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THICK, Color.getBlack());

Iterator cellArray = range.iterator();
while (cellArray.hasNext()) {
	Cell temp = (Cell) cellArray.next();
	// Saving the modified style to the cell.
	temp.setStyle(style);
}

// Saving the Excel file
workbook.save(dataDir + "CCAToROrCArea_out.xls");

删除命名范围

Aspose.Cells 提供了名称集合.RemoveAt() 方法来擦除范围的名称。要清除范围的内容，请使用[Cells.ClearRange()](https://reference.aspose.com/cells/java/com.aspose.cells/cells#clearRange(com.aspose.cells.CellArea)） 方法。 以下示例显示如何删除命名范围及其内容。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(RemoveANamedRange.class) + "data/";

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Get all the worksheets in the book.
WorksheetCollection worksheets = workbook.getWorksheets();

// Get the first worksheet in the worksheets collection.
Worksheet worksheet = workbook.getWorksheets().get(0);

// Create a range of cells.
Range range1 = worksheet.getCells().createRange("E12", "I12");

// Name the range.
range1.setName("MyRange");

// Set the outline border to the range.
range1.setOutlineBorder(BorderType.TOP_BORDER, CellBorderType.MEDIUM, Color.fromArgb(0, 0, 128));
range1.setOutlineBorder(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.fromArgb(0, 0, 128));
range1.setOutlineBorder(BorderType.LEFT_BORDER, CellBorderType.MEDIUM, Color.fromArgb(0, 0, 128));
range1.setOutlineBorder(BorderType.RIGHT_BORDER, CellBorderType.MEDIUM, Color.fromArgb(0, 0, 128));

// Input some data with some formattings into
// a few cells in the range.
range1.get(0, 0).setValue("Test");
range1.get(0, 4).setValue("123");

// Create another range of cells.
Range range2 = worksheet.getCells().createRange("B3", "F3");

// Name the range.
range2.setName("testrange");

// Copy the first range into second range.
range2.copy(range1);

// Remove the previous named range (range1) with its contents.
worksheet.getCells().clearRange(11, 4, 11, 8);
worksheets.getNames().removeAt(0);

// Save the excel file.
workbook.save(dataDir + "RANRange_out.xls");

// Print message
System.out.println("Process completed successfully");

边框颜色