Ställa in villkorliga format i Excel och ODS filer.
Contents
[
Hide
]
Introduktion
Villkorlig formatering är en avancerad funktion i Microsoft Excel som gör att du kan tillämpa format på en cell eller en cellintervall och få den formateringen att ändras beroende på cellens värde eller värdet av en formel. Till exempel kan du låta en cell visas fetstilad endast när cellens värde är större än 500. När cellens värde uppfyller villkoret tillämpas det angivna formatet på cellen. Om cellens värde inte uppfyller formatvillkoret används cellens standardformatering. I Microsoft Excel väljer du Format, sedan Villkorlig formatering för att öppna dialogrutan för villkorlig formatering.
Aspose.Cells för Python via .NET stödjer att tillämpa villkorlig formatering på celler vid körning. Denna artikel förklarar hur. Den förklarar också hur man beräknar vilken färg Excel använder för färgskala villkorlig formatering.
Tillämpa villkorlig formatering
Aspose.Cells för Python via .NET stöder villkorlig formatering på flera sätt:
- Använda designerkalkylblad
- Använda kopieringsmetoden
- Skapa villkorlig formatering vid körning
Använda designerkalkylblad
Utvecklare kan skapa ett designat kalkylblad som innehåller villkorlig formatering i Microsoft Excel och sedan öppna det kalkylbladet med Aspose.Cells för Python via .NET. Aspose.Cells för Python via .NET laddar och sparar det designade kalkylbladet och behåller eventuella villkorliga formateringsinställningar.
Använda kopieringsmetoden
Aspose.Cells för Python via .NET tillåter utvecklare att kopiera inställningar för villkorsstyrd formatering från en cell till en annan i kalkylbladet genom att anropa Range.copy()-metoden.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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() |
Tillämpa villkorlig formatering under körning
Aspose.Cells för Python via .NET låter dig både lägga till och ta bort villkorsstyrd formatering vid körning. Följande kodexempel visar hur man ställer in villkorsstyrd formatering:
- Skapa en arbetsbok.
- Lägg till en tom villkorlig formatering.
- Ange det intervall som formateringen ska tillämpas på.
- Definiera formateringsvillkoren.
- Spara filen.
Efter det här exemplet följer ett antal mindre exempel som visar hur man tillämpar teckensnittsinställningar, kantlinjeinställningar och mönster.
Microsoft Excel 2007 tillförde mer avancerad villkorsstyrd formatering som Aspose.Cells för Python via .NET också stöder. Exemplen här illustrerar hur man använder enkel formatering, de Microsoft Excel 2007-exemplen visar hur man tillämpar mer avancerad villkorsstyrd formatering.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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") |
Ange font
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) |
Du kan bara ändra teckensnittsstil, textfärg, understrykningsstil och överstrykningsstil.
Ange ram
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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") |
Du kan bara använda tunna linjestilar till ytterkanten. Diagonala linjer är inte tillåtna.
Ange mönster
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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") |
Fortsatta ämnen
- Lägga till 2-färgskala och 3-färgskala villkorliga formateringar
- Tillämpa villkorlig formatering i arbetsblad
- Tillämpa skuggning på alternerande rader och kolumner med villkorlig formatering
- Generera bilder för databarformat i villkorlig formatering
- Hämta ikonsatser, databarer eller färgskalor som används i villkorlig formatering