名前付き範囲の書式および変更

範囲の書式設定

名前付き範囲に背景色とフォント属性を設定する方法

書式を適用するには、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 は 2 つの範囲の交差を求めるための 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")