创建访问并复制已命名区域
Contents
[
Hide
]
介绍
通常，列和行标签用于引用单个单元格。可以创建描述性名称来表示单元格、单元格范围、公式或常量值。名称可能指代表示单元格、单元格范围、公式或常量值的一系列字符。为范围分配一个名称意味着可以通过其名称引用该单元格范围。使用易于理解的名称，例如“产品”，来引用难以理解的范围，例如“销售！C20:C30”。标签可以用在引用同一工作表上的数据的公式中；如果要表示另一个工作表上的范围，可以使用名称。*已命名区域是Microsoft Excel的最强大的功能之一，特别是在作为列表控件、数据透视表、图表等的源范围时。
使用Microsoft Excel处理已命名区域
创建已命名范围
以下步骤描述了使用MS Excel为单元格或单元格范围命名的方法。此方法适用于Microsoft Office Excel 2003、Microsoft Excel 97、2000和2002。
- 选择要命名的单元格或单元格范围。
- 单击公式栏左端的名称框。
- 输入单元格的名称。
- 按ENTER。
在更改单元格内容时，不能为单元格命名。
使用Aspose.Cells for Python Excel Library处理已命名区域
在这里，我们使用Aspose.Cells for Python via .NET API来完成任务。 Aspose.Cells for Python via .NET提供了一个代表Microsoft Excel文件的类，Workbook。Workbook类包含一个worksheets集合，允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供了一个cells集合。
创建已命名范围
通过调用 Cells 集合的重载 create_range 方法，可以创建命名范围。 create_range 方法的典型版本使用以下参数：
- 左上角单元格的名称，范围中左上角单元格的名称。
- 右下角单元格的名称，范围中右下角单元格的名称。
调用 create_range 方法时，它将返回新创建的范围，作为 Range 类的实例。使用此 Range 对象来配置命名范围。例如，使用 name 属性设置范围的名称。以下示例展示了如何创建跨越 B4:G14 的单元格命名范围。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from aspose.cells import Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Opening the Excel file through the file stream | |
| workbook = Workbook(dataDir + "book1.xls") | |
| # Accessing the first worksheet in the Excel file | |
| worksheet = workbook.worksheets[0] | |
| # Creating a named range | |
| range = worksheet.cells.create_range("B4", "G14") | |
| # Setting the name of the named range | |
| range.name = "TestRange" | |
| # Saving the modified Excel file | |
| workbook.save(dataDir + "output.out.xls") |
将数据输入到命名范围内的单元格
您可以按照模式将数据插入到范围内单个单元格中
- C#: Range[row,column]
- VB: Range(row,column)
假设您有一个跨越 A1:C4 的命名范围。该矩阵包含 4 * 3 = 12 个单元格。单个范围单元按顺序排列：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]。
使用以下属性来识别范围中的单元格:
- FirstRow 返回命名范围中第一行的索引。
- FirstColumn 返回命名范围中第一列的索引。
- RowCount 返回命名范围中的总行数。
- ColumnCount 返回命名范围中的总列数。
以下示例显示如何向指定范围的单元格输入一些值。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from aspose.cells import Workbook | |
| from os import os, path | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Create directory if it is not already present. | |
| IsExists = path.isdir(dataDir) | |
| if notIsExists: | |
| os.makedirs(dataDir) | |
| # Instantiate a new Workbook. | |
| workbook = Workbook() | |
| # Get the first worksheet in the workbook. | |
| worksheet1 = workbook.worksheets[0] | |
| cells = worksheet1.cells | |
| # Create a range of cells based on H1:J4. | |
| range = cells.create_range("H1", "J4") | |
| # Name the range. | |
| range.name = "MyRange" | |
| startRow = range.first_row | |
| startCol = range.first_column | |
| # Input some data into cells in the range. | |
| cells.get(startRow, startCol).put_value("USA") | |
| cells.get(startRow, startCol + 1).put_value("SA") | |
| cells.get(startRow, startCol + 2).put_value("Israel") | |
| cells.get(startRow + 1, startCol).put_value("UK") | |
| cells.get(startRow + 1, startCol + 1).put_value("AUS") | |
| cells.get(startRow + 1, startCol + 2).put_value("Canada") | |
| cells.get(startRow + 2, startCol).put_value("France") | |
| cells.get(startRow + 2, startCol + 1).put_value("India") | |
| cells.get(startRow + 2, startCol + 2).put_value("Egypt") | |
| cells.get(startRow + 3, startCol).put_value("China") | |
| cells.get(startRow + 3, startCol + 1).put_value("Philipine") | |
| cells.get(startRow + 3, startCol + 2).put_value("Brazil") | |
| # Save the excel file. | |
| workbook.save(dataDir + "rangecells.out.xls") |
标识命名范围中的单元格
您可以按照以下模式向范围内的单个单元格插入数据：
- C#: Range[row,column]
- VB: Range(row,column)
如果您有一个跨越 A1:C4 的命名范围。该矩阵包含 4 * 3 = 12 个单元格。单个范围单元按顺序排列：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]。
使用以下属性来识别范围中的单元格:
- FirstRow 返回命名范围中第一行的索引。
- FirstColumn 返回命名范围中第一列的索引。
- RowCount 返回命名范围中的总行数。
- ColumnCount 返回命名范围中的总列数。
以下示例显示如何向指定范围的单元格输入一些值。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from aspose.cells import Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Instantiate a new Workbook. | |
| workbook = Workbook(dataDir + "book1.xls") | |
| # Getting the specified named range | |
| range = workbook.worksheets.get_range_by_name("TestRange") | |
| # Identify range cells. | |
| print("First Row : " + str(range.first_row)) | |
| print("First Column : " + str(range.first_column)) | |
| print("Row Count : " + str(range.row_count)) | |
| print("Column Count : " + str(range.column_count)) |
访问命名范围
访问特定的命名范围
调用 Worksheet 集合的 get_range_by_name 方法，以按指定名称获取范围。典型的 get_range_by_name 方法接受命名范围的名称，并将所指定的命名范围作为 Range 类的实例返回。以下示例显示如何通过名称访问指定的范围。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from aspose.cells import Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Opening the Excel file through the file stream | |
| workbook = Workbook(dataDir + "book1.xls") | |
| # Getting the specified named range | |
| range = workbook.worksheets.get_range_by_name("TestRange") | |
| if range != None: | |
| print("Named Range : " + range.refers_to) |
访问电子表格中的所有命名范围
调用 Worksheet 集合的 get_named_ranges 方法，获取电子表格中的所有命名范围。get_named_ranges 方法返回 Worksheet 集合中所有命名范围的数组。
以下示例显示如何访问工作簿中的所有命名范围。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from aspose.cells import Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Opening the Excel file through the file stream | |
| workbook = Workbook(dataDir + "book1.xls") | |
| # Getting all named ranges | |
| range = workbook.worksheets.get_named_ranges() | |
| if range != None: | |
| print("Total Number of Named Ranges: " + str(len(range))) |
复制命名范围
Aspose.Cells for Python via .NET 提供了 Range.copy() 方法，用于将带有格式的单元格范围复制到另一个范围。
以下示例显示如何将源单元格范围复制到另一个命名范围。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from aspose.cells import BorderType, CellBorderType, Workbook | |
| from aspose.pydrawing import Color | |
| from os import os, path | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Create directory if it is not already present. | |
| IsExists = path.isdir(dataDir) | |
| if notIsExists: | |
| os.makedirs(dataDir) | |
| # Instantiate a new Workbook. | |
| workbook = Workbook() | |
| # Get all the worksheets in the book. | |
| worksheets = workbook.worksheets | |
| # Get the first worksheet in the worksheets collection. | |
| worksheet = workbook.worksheets[0] | |
| cells = worksheet.cells | |
| # Create a range of cells. | |
| range1 = cells.create_range("E12", "I12") | |
| # Name the range. | |
| range1.name = "MyRange" | |
| # Set the outline border to the range. | |
| range1.set_outline_border(BorderType.TOP_BORDER, CellBorderType.MEDIUM, Color.from_argb(0, 0, 128)) | |
| range1.set_outline_border(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.from_argb(0, 0, 128)) | |
| range1.set_outline_border(BorderType.LEFT_BORDER, CellBorderType.MEDIUM, Color.from_argb(0, 0, 128)) | |
| range1.set_outline_border(BorderType.RIGHT_BORDER, CellBorderType.MEDIUM, Color.from_argb(0, 0, 128)) | |
| startRow = range1.first_row | |
| startCol = range1.first_column | |
| # Input some data with some formattings into | |
| # A few cells in the range. | |
| cells.get(startRow, startCol).put_value("Test") | |
| cells.get(startRow, startCol + 4).put_value("123") | |
| # Create another range of cells. | |
| range2 = cells.create_range("B3", "F3") | |
| # Name the range. | |
| range2.name = "testrange" | |
| # Copy the first range into second range. | |
| range2.copy(range1) | |
| # Save the excel file. | |
| workbook.save(dataDir + "copyranges.out.xls") |