命名范围

通常，列和行标签用于引用单个单元格。可以创建描述性名称来表示单元格、单元格范围、公式或常量值。名称可能指代表示单元格、单元格范围、公式或常量值的一系列字符。为范围分配名称意味着可以通过其名称引用该单元格范围。使用易于理解的名称，例如“产品”，来引用难以理解的范围，例如“销售！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文件的类Workbook。Workbook类包含一个WorksheetCollection，允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供了一个Cells集合。

可以通过调用Cells集合的重载createRange方法来创建命名范围。createRange方法的典型版本接受以下参数：

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

调用createRange方法后，它会将新创建的命名范围返回为Range类的实例。

以下示例显示了如何创建一个跨越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");

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

调用WorksheetCollection集合的getRangeByName方法，通过名称获取指定的范围。典型的getRangeByName方法需要命名范围的名称，并将指定的命名范围返回为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(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);

访问特定的命名范围

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

使用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(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());

识别命名范围中的单元格

• getFirstRow返回命名范围中第一行的索引。

• getFirstColumn返回命名范围中第一列的索引。

• getFirstColumn返回命名范围中第一列的索引。

以下示例显示如何向指定范围的单元格输入一些值。

// 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());

将数据输入到命名范围内的单元格

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

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

以下示例显示如何向指定范围的单元格输入一些值。

// 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");

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

要应用格式化，定义一个Style对象来指定样式设置，并将其应用到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(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");

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

可以向单元格范围添加边框，而不仅仅是一个单元格。Range对象提供了setOutlineBorders方法，该方法接受以下参数来向单元格范围添加边框：

• borderStyle：边框类型，从CellBorderType枚举中选择。
• borderColor：边框的线颜色，从Color枚举中选择。

以下示例显示如何将范围设置为轮廓边框。

// 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");

执行以上代码后将生成以下输出:

在Range中为单元格应用样式

有时，您想要在Range中为单元格应用样式。为此，您可以迭代范围中的单元格，并使用Cell.setStyle方法将样式应用于单元格。

以下示例展示了如何在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(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提供NameCollection.RemoveAt()方法来擦除范围的名称。要清除范围的内容，使用Cells.ClearRange()方法。 以下示例展示了如何移除命名范围及其内容。

// 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");

borderColors