格式和修改命名区域
格式化区域
如何为命名区域设置背景颜色和字体属性
要应用格式设置,定义一个Style对象以指定样式设置,并将其应用于Range对象。
以下示例显示如何为范围设置实填充颜色(着色颜色)并带有字体设置。
from aspose.cells import BackgroundType, StyleFlag, 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 the first worksheet in the book. | |
WS = workbook.worksheets[0] | |
# Create a range of cells. | |
range = WS.cells.create_range(1, 1, 1, 18) | |
# Name the range. | |
range.name = "MyRange" | |
# Create/add the style object. | |
stl = workbook.create_style() | |
# Specify some Font settings. | |
stl.font.name = "Arial" | |
stl.font.is_bold = 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.foreground_color = Color.yellow | |
stl.pattern = BackgroundType.SOLID | |
# Create a StyleFlag object. | |
flg = StyleFlag() | |
# Make the corresponding attributes ON. | |
flg.font = True | |
flg.cell_shading = True | |
# Apply the style to the range. | |
range.apply_style(stl, flg) | |
# Save the excel file. | |
workbook.save(dataDir + "rangestyles.out.xls") |
如何为命名区域添加边框
可以为一系列的单元格添加边框,而不仅仅是单个单元格。Range对象提供了一个set_outline_border方法,以及以下参数来向单元格范围添加边框:
- 边框类型,边框的类型,从BorderType枚举中选择。
- 线条样式,线条的样式,从CellBorderType枚举中选择。
- 颜色,线条颜色,从Color枚举中选择。
以下示例显示如何将范围设置为轮廓边框。
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) | |
# Instantiating a Workbook object | |
workbook = 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 = workbook.worksheets[0] | |
# Accessing the "A1" cell from the worksheet | |
cell = worksheet.cells.get("A1") | |
# Adding some value to the "A1" cell | |
cell.put_value("Hello World From Aspose") | |
# Creating a range of cells starting from "A1" cell to 3rd column in a row | |
range = worksheet.cells.create_range(0, 0, 1, 3) | |
# Adding a thick top border with blue line | |
range.set_outline_border(BorderType.TOP_BORDER, CellBorderType.THICK, Color.blue) | |
# Adding a thick bottom border with blue line | |
range.set_outline_border(BorderType.BOTTOM_BORDER, CellBorderType.THICK, Color.blue) | |
# Adding a thick left border with blue line | |
range.set_outline_border(BorderType.LEFT_BORDER, CellBorderType.THICK, Color.blue) | |
# Adding a thick right border with blue line | |
range.set_outline_border(BorderType.RIGHT_BORDER, CellBorderType.THICK, Color.blue) | |
# Saving the Excel file | |
workbook.save(dataDir + "book1.out.xls") |
如何重命名已命名范围
Aspose.Cells允许您根据需要重命名已命名范围。您可以通过使用Name.text属性获取命名范围并将其重命名。以下示例显示如何重命名已命名范围。
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(".") | |
# Open an existing Excel file that has a (global) named range "TestRange" in it | |
workbook = Workbook(dataDir + "book1.xls") | |
# Get the first worksheet | |
sheet = workbook.worksheets[0] | |
# Get the Cells of the sheet | |
cells = sheet.cells | |
# Get the named range "MyRange" | |
name = workbook.worksheets.names.get("TestRange") | |
# Rename it | |
name.text = "NewRange" | |
# Save the Excel file | |
workbook.save(dataDir + "RenamingRange.out.xlsx") |
如何取并集范围
Aspose.Cells提供Range.union方法以获取范围的并集。以下示例显示如何获取范围的并集。
from aspose import pycore | |
from aspose.cells import BackgroundType, Range, StyleFlag, Workbook | |
from aspose.pydrawing import Color | |
# 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 workbook object. | |
# Open an existing excel file. | |
workbook = Workbook(dataDir + "book1.xls") | |
# Get the named ranges. | |
ranges = workbook.worksheets.get_named_ranges() | |
# Create a style object. | |
style = workbook.create_style() | |
# Set the shading color with solid pattern type. | |
style.foreground_color = Color.yellow | |
style.pattern = BackgroundType.SOLID | |
# Create a styleflag object. | |
flag = StyleFlag() | |
# Apply the cellshading. | |
flag.cell_shading = True | |
# Creates an arraylist. | |
al = [] | |
# Get the arraylist collection apply the union operation. | |
al = ranges[0].union(ranges[1]) | |
for i in range(len(al)): | |
# Get a range. | |
rng = pycore.cast(Range, al[i]) | |
frow = rng.first_row | |
fcol = rng.first_column | |
erow = rng.row_count | |
ecol = rng.column_count | |
# Apply the style to the range. | |
rng.apply_style(style, flag) | |
# Save the excel file. | |
workbook.save(dataDir + "rngUnion.out.xls") |
如何求范围的交集
Aspose.Cells提供Range.intersect方法以求两个范围的交集。该方法返回Range对象。要检查一个范围是否与另一个范围相交,请使用返回布尔值的Range.intersect方法。以下示例显示如何求范围的交集。
from aspose.cells import BackgroundType, StyleFlag, Workbook | |
from aspose.pydrawing import Color | |
# 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 workbook object. | |
# Open an existing excel file. | |
workbook = Workbook(dataDir + "book1.xls") | |
# Get the named ranges. | |
ranges = workbook.worksheets.get_named_ranges() | |
# Check whether the first range intersect the second range. | |
isintersect = ranges[0].is_intersect(ranges[1]) | |
# Create a style object. | |
style = workbook.create_style() | |
# Set the shading color with solid pattern type. | |
style.foreground_color = Color.yellow | |
style.pattern = BackgroundType.SOLID | |
# Create a styleflag object. | |
flag = StyleFlag() | |
# Apply the cellshading. | |
flag.cell_shading = True | |
# If first range intersects second range. | |
if isintersect: | |
# Create a range by getting the intersection. | |
intersection = ranges[0].intersect(ranges[1]) | |
# Name the range. | |
intersection.name = "Intersection" | |
# Apply the style to the range. | |
intersection.apply_style(style, flag) | |
# Save the excel file. | |
workbook.save(dataDir + "rngIntersection.out.xls") |
如何合并命名范围中的单元格
Aspose.Cells提供Range.merge()方法以合并范围中的单元格。以下示例显示如何合并命名范围的单元格。
from aspose.cells import StyleFlag, TextAlignmentType, 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. | |
wb1 = Workbook() | |
# Get the first worksheet in the workbook. | |
worksheet1 = wb1.worksheets[0] | |
# Create a range. | |
mrange = worksheet1.cells.create_range("A18", "J18") | |
# Name the range. | |
mrange.name = "Details" | |
# Merge the cells of the range. | |
mrange.merge() | |
# Get the range. | |
range1 = wb1.worksheets.get_range_by_name("Details") | |
# Define a style object. | |
style = wb1.create_style() | |
# Set the alignment. | |
style.horizontal_alignment = TextAlignmentType.CENTER | |
# Create a StyleFlag object. | |
flag = StyleFlag() | |
# Make the relative style attribute ON. | |
flag.horizontal_alignment = True | |
# Apply the style to the range. | |
range1.apply_style(style, flag) | |
# Input data into range. | |
range1.worksheet.cells.get(range1.first_row, range1.first_column).put_value("Aspose") | |
# Save the excel file. | |
wb1.save(dataDir + "mergingrange.out.xls") |