Formattare e modificare intervalli con nome
Formattare intervalli
Come impostare il colore di sfondo e gli attributi del carattere a un intervallo con nome
Per applicare la formattazione, definire un oggetto Style per specificare le impostazioni dello stile e applicarlo all’oggetto Range.
Nell’esempio seguente viene mostrato come impostare il colore di riempimento solido (colore ombreggiatura) con impostazioni del carattere a un intervallo.
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") |
Come aggiungere i bordi a un intervallo con nome
È possibile aggiungere i bordi a un intervallo di celle invece che a una singola cella. L’oggetto Range fornisce un metodo set_outline_border che accetta i seguenti parametri per aggiungere un bordo all’intervallo di celle:
- Tipo di bordo, il tipo di bordo, selezionato dall’enumerazione BorderType.
- Stile della linea, lo stile della linea, selezionato dall’enumerazione CellBorderType.
- Colore, il colore della linea, selezionato dall’enumerazione Colore.
L’esempio seguente mostra come impostare un bordo di contorno a un intervallo.
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") |
Come rinominare un intervallo con nome
Aspose.Cells ti consente di rinominare un intervallo con nome secondo le tue esigenze. Puoi ottenere l’intervallo con nome e rinominarlo usando l’attributo Name.text. Nell’esempio seguente viene mostrato come rinominare un intervallo con nome.
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") |
Come eseguire l’unione degli intervalli
Aspose.Cells fornisce il metodo Range.union per eseguire l’unione degli intervalli. L’esempio seguente mostra come eseguire l’unione degli intervalli.
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") |
Come intersecare gli intervalli
Aspose.Cells fornisce il metodo Range.intersect per intersecare due intervalli. Il metodo restituisce un oggetto Range. Per verificare se un intervallo interseca un altro intervallo, utilizzare il metodo Range.intersect che restituisce un valore booleano. L’esempio seguente mostra come intersecare gli intervalli.
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") |
Come unire le celle nell’intervallo con nome
Aspose.Cells fornisce il metodo Range.merge() per unergere le celle nell’intervallo. Nell’esempio seguente viene mostrato come unire le celle individuali di un intervallo nominato.
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") |