格式和修改命名区域

格式化区域

如何为命名区域设置背景颜色和字体属性

要应用格式设置,定义一个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")