Establecer formatos condicionales de archivos Excel y ODS.
Introducción
El formato condicional es una característica avanzada de Microsoft Excel que te permite aplicar formatos a una celda o rango de celdas y que ese formato cambie dependiendo del valor de la celda o del valor de una fórmula. Por ejemplo, puedes hacer que una celda aparezca en negrita solo cuando el valor de la celda sea mayor que 500. Cuando el valor de la celda cumple la condición, se aplica el formato especificado a la celda. Si el valor de la celda no cumple la condición del formato, se utiliza el formato predeterminado de la celda. En Microsoft Excel, selecciona Formato, luego Formato condicional para abrir el cuadro de diálogo Formato condicional.
Aspose.Cells para Python via .NET soporta aplicar formato condicional a celdas en tiempo de ejecución. Este artículo explica cómo. También explica cómo calcular el color que Excel usa para formatos condicionales con escala de colores.
Aplicar formato condicional
Aspose.Cells para Python via .NET soporta el formato condicional de varias maneras:
- Usando una hoja de cálculo de diseñador
- Usando el método de copia.
- Creando formato condicional en tiempo de ejecución.
Usar la Hoja de Cálculo de Diseñador
Los desarrolladores pueden crear una hoja de cálculo de diseño que contenga formato condicional en Microsoft Excel y luego abrir esa hoja con Aspose.Cells para Python via .NET. Aspose.Cells para Python via .NET carga y guarda la hoja de diseño, conservando cualquier configuración de formato condicional.
Usando el Método de Copia
Aspose.Cells para Python via .NET permite a los desarrolladores copiar configuraciones de formato condicional de una celda a otra en la hoja de cálculo llamando al método Range.copy().
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(".") | |
# Creating a file stream containing the Excel file to be opened | |
fstream = open(dataDir + "Book1.xlsx", "rb") | |
# Opening the Excel file through the file stream | |
workbook = Workbook(fstream) | |
# Accessing the first worksheet in the Excel file | |
worksheet = workbook.worksheets[0] | |
# Copying conditional format settings from cell "A1" to cell "B1" | |
# worksheet.CopyConditionalFormatting(0, 0, 0, 1); | |
TotalRowCount = 0 | |
for i in range(len(workbook.worksheets)): | |
sourceSheet = workbook.worksheets[i] | |
sourceRange = sourceSheet.cells.max_display_range | |
destRange = worksheet.cells.create_range(sourceRange.first_row + TotalRowCount, sourceRange.first_column, sourceRange.row_count, sourceRange.column_count) | |
destRange.copy(sourceRange) | |
TotalRowCount = sourceRange.row_count + TotalRowCount | |
# Saving the modified Excel file | |
workbook.save(dataDir + "output.xls") | |
# Closing the file stream to free all resources | |
fstream.close() |
Aplicar formato condicional en tiempo de ejecución
Aspose.Cells para Python via .NET permite agregar y eliminar formato condicional en tiempo de ejecución. Los ejemplos de código a continuación muestran cómo configurar formato condicional:
- Instanciar un libro de trabajo.
- Agregar un formato condicional vacío.
- Establecer el rango al que debe aplicarse el formato.
- Definir las condiciones de formato.
- Guarde el archivo.
Después de este ejemplo vienen varios ejemplos más pequeños que muestran cómo aplicar configuraciones de fuente, configuraciones de bordes y patrones.
Microsoft Excel 2007 agregó un formato condicional más avanzado que Aspose.Cells para Python via .NET también soporta. Los ejemplos aquí ilustran cómo usar formatos simples; los ejemplos de Microsoft Excel 2007 muestran cómo aplicar formatos condicionales más avanzados.
from aspose.cells import CellArea, FormatConditionType, OperatorType, 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(".") | |
filePath = dataDir + "Book1.xlsx" | |
# Instantiating a Workbook object | |
workbook = Workbook() | |
sheet = workbook.worksheets[0] | |
# Adds an empty conditional formatting | |
index = sheet.conditional_formattings.add() | |
fcs = sheet.conditional_formattings[index] | |
# Sets the conditional format range. | |
ca = CellArea() | |
ca.start_row = 0 | |
ca.end_row = 0 | |
ca.start_column = 0 | |
ca.end_column = 0 | |
fcs.add_area(ca) | |
ca = CellArea() | |
ca.start_row = 1 | |
ca.end_row = 1 | |
ca.start_column = 1 | |
ca.end_column = 1 | |
fcs.add_area(ca) | |
# Adds condition. | |
conditionIndex = fcs.add_condition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "=A2", "100") | |
# Adds condition. | |
conditionIndex2 = fcs.add_condition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100") | |
# Sets the background color. | |
fc = fcs[conditionIndex] | |
fc.style.background_color = Color.red | |
# Saving the Excel file | |
workbook.save(dataDir + "output.xls") |
Establecer fuente
from aspose.cells import SaveFormat, 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) | |
# Instantiating a Workbook object | |
workbook = Workbook() | |
# Adding a new worksheet to the Excel object | |
i = workbook.worksheets.add() | |
# Obtaining the reference of the newly added worksheet by passing its sheet index | |
worksheet = workbook.worksheets[i] | |
# Accessing the "A1" cell from the worksheet | |
cell = worksheet.cells.get("A1") | |
# Adding some value to the "A1" cell | |
cell.put_value("Hello Aspose!") | |
# Obtaining the style of the cell | |
style = cell.get_style() | |
# Setting the font weight to bold | |
style.font.is_bold = True | |
# Applying the style to the cell | |
cell.set_style(style) | |
# Saving the Excel file | |
workbook.save(dataDir + "book1.out.xls", SaveFormat.EXCEL_97_TO_2003) |
Establecer borde
from aspose.cells import BorderType, CellArea, CellBorderType, FormatConditionType, OperatorType, 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(".") | |
# Instantiating a Workbook object | |
workbook = Workbook() | |
sheet = workbook.worksheets[0] | |
# Adds an empty conditional formatting | |
index = sheet.conditional_formattings.add() | |
fcs = sheet.conditional_formattings[index] | |
# Sets the conditional format range. | |
ca = CellArea() | |
ca.start_row = 0 | |
ca.end_row = 5 | |
ca.start_column = 0 | |
ca.end_column = 3 | |
fcs.add_area(ca) | |
# Adds condition. | |
conditionIndex = fcs.add_condition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100") | |
# Sets the background color. | |
fc = fcs[conditionIndex] | |
fc.style.borders.get(BorderType.LEFT_BORDER).line_style = CellBorderType.DASHED | |
fc.style.borders.get(BorderType.RIGHT_BORDER).line_style = CellBorderType.DASHED | |
fc.style.borders.get(BorderType.TOP_BORDER).line_style = CellBorderType.DASHED | |
fc.style.borders.get(BorderType.BOTTOM_BORDER).line_style = CellBorderType.DASHED | |
fc.style.borders.get(BorderType.LEFT_BORDER).color = Color.from_argb(0, 255, 255) | |
fc.style.borders.get(BorderType.RIGHT_BORDER).color = Color.from_argb(0, 255, 255) | |
fc.style.borders.get(BorderType.TOP_BORDER).color = Color.from_argb(0, 255, 255) | |
fc.style.borders.get(BorderType.BOTTOM_BORDER).color = Color.from_argb(255, 255, 0) | |
workbook.save(dataDir + "output.xlsx") |
Establecer patrón
from aspose.cells import BackgroundType, CellArea, FormatConditionType, OperatorType, 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(".") | |
# Instantiating a Workbook object | |
workbook = Workbook() | |
sheet = workbook.worksheets[0] | |
# Adds an empty conditional formatting | |
index = sheet.conditional_formattings.add() | |
fcs = sheet.conditional_formattings[index] | |
# Sets the conditional format range. | |
ca = CellArea() | |
ca.start_row = 0 | |
ca.end_row = 5 | |
ca.start_column = 0 | |
ca.end_column = 3 | |
fcs.add_area(ca) | |
# Adds condition. | |
conditionIndex = fcs.add_condition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100") | |
fc = fcs[conditionIndex] | |
fc.style.pattern = BackgroundType.REVERSE_DIAGONAL_STRIPE | |
fc.style.foreground_color = Color.from_argb(255, 255, 0) | |
fc.style.background_color = Color.from_argb(0, 255, 255) | |
workbook.save(dataDir + "output.xlsx") |
Temas avanzados
- Agregar escalas de colores de 2 colores y 3 colores con formato condicional
- Aplicar formato condicional en hojas de cálculo
- Aplicar sombreado a filas y columnas alternas con formato condicional
- Generar imágenes de barras de datos de formato condicional
- Obtener conjuntos de iconos, barras de datos o escalas de colores utilizados en el formato condicional