Formatear y modificar rangos con nombre

Formato de rangos

Cómo configurar el color de fondo y los atributos de fuente a un rango con nombre

Para aplicar formato, define un objeto Style para especificar la configuración del estilo y aplícalo al objeto Range .

El siguiente ejemplo muestra cómo establecer el color de relleno sólido (color de sombreado) con la configuración de fuente a un rango.

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")

Cómo agregar bordes a un rango con nombre

Es posible agregar bordes a un rango de celdas en lugar de solo una celda. El objeto Range proporciona un método set_outline_border que toma los siguientes parámetros para agregar un borde al rango de celdas:

• Tipo de borde, el tipo de borde, seleccionado de la enumeración BorderType.
• Estilo de línea, el estilo de línea, seleccionado de la enumeración CellBorderType.
• Color, el color de la línea, seleccionado de la enumeración Color.

El siguiente ejemplo muestra cómo establecer un borde de contorno en un rango.

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")

Cómo cambiar el nombre a un rango con nombre

Aspose.Cells te permite renombrar un rango con nombre según tus necesidades. Puedes obtener el rango con nombre y renombrarlo usando el atributo Name.text. El siguiente ejemplo muestra cómo renombrar un rango con nombre.

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")

Cómo realizar la unión de rangos

Aspose.Cells proporciona el método Range.union para realizar la unión de rangos. El siguiente ejemplo muestra cómo realizar la unión de rangos.

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")

Cómo intersectar los rangos

Aspose.Cells proporciona el método Range.intersect para intersectar dos rangos. El método devuelve un objeto Range. Para comprobar si un rango se interseca con otro, usa el método Range.intersect que devuelve un valor booleano. El siguiente ejemplo muestra cómo intersectar los rangos.

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")

Cómo combinar celdas en el rango con nombre

Aspose.Cells proporciona el método Range.merge() para combinar las celdas en el rango. El siguiente ejemplo muestra cómo combinar las celdas individuales de un rango con nombre.

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")