Benannte Bereiche formatieren und ändern
Bereiche formatieren
Wie man Hintergrundfarbe und Schriftattributen zu einem benannten Bereich festlegt
Um die Formatierung anzuwenden, definieren Sie ein Style-Objekt, um die Formatierungseinstellungen anzugeben, und wenden Sie es auf das Range-Objekt an.
Das folgende Beispiel zeigt, wie die Füllfarbe (Hintergrundfarbe) mit Schrifteinstellungen für einen Bereich festgelegt wird.
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") |
Wie man einem benannten Bereich Rahmen hinzufügt
Es ist möglich, Rahmen zu einem Bereich von Zellen hinzuzufügen, anstatt nur zu einer einzelnen Zelle. Das Range-Objekt bietet eine set_outline_border-Methode, die die folgenden Parameter verwendet, um einen Rahmen zum Zellenbereich hinzuzufügen:
- Rahmenart, die Art des Rahmens, ausgewählt aus der BorderType-Aufzählung.
- Linienstil, der Linienstil, ausgewählt aus der CellBorderType-Aufzählung.
- Farbe, die Linienfarbe, ausgewählt aus der Farb-Aufzählung.
Im folgenden Beispiel wird gezeigt, wie einem Bereich ein Umrissrahmen gesetzt wird.
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") |
Wie man einen benannten Bereich umbenennt
Aspose.Cells ermöglicht es Ihnen, einen benannten Bereich nach Bedarf umzubenennen. Sie können den benannten Bereich abrufen und umbenennen, indem Sie das Name.text-Attribut verwenden. Das folgende Beispiel zeigt, wie ein benannter Bereich umbenannt wird.
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") |
Wie man Schnittmenge von Bereichen erstellt
Aspose.Cells bietet die Range.union-Methode zum Erstellen der Schnittmenge von Bereichen. Das folgende Beispiel zeigt, wie man die Schnittmenge von Bereichen erstellt.
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") |
Wie man die Bereiche schneidet
Aspose.Cells bietet die Range.intersect-Methode, um zwei Bereiche zu schneiden. Die Methode gibt ein Range-Objekt zurück. Um zu überprüfen, ob ein Bereich einen anderen Bereich schneidet, verwenden Sie die Range.intersect-Methode, die einen booleschen Wert zurückgibt. Das folgende Beispiel zeigt, wie die Bereiche geschnitten werden.
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") |
Wie man Zellen im benannten Bereich zusammenführt
Aspose.Cells bietet die Range.merge()-Methode, um die Zellen im Bereich zusammenzuführen. Das folgende Beispiel zeigt, wie die einzelnen Zellen eines benannten Bereichs zusammengeführt werden.
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") |