Formatage du tableau croisé dynamique
Contents
[
Hide
]
Apparence du tableau croisé dynamique
Comment créer un tableau croisé dynamique explique comment créer un tableau croisé dynamique simple. Cet article décrit comment personnaliser l’apparence d’un tableau croisé dynamique en définissant diverses propriétés :
- Options de formatage du tableau croisé dynamique
- Options de formatage des champs de tableau croisé dynamique
- Options de formatage des champs de données
Comment définir les options de formatage du tableau croisé dynamique
La classe PivotTable contrôle le tableau croisé dynamique global et peut être formatée de plusieurs manières.
Comment définir le type de mise en forme automatique
Microsoft Excel propose différents formats de rapport prédéfinis. Aspose.Cells for Python via .NET prend également en charge ces options de mise en forme. Pour y accéder :
- Définissez PivotTable.is_auto_format sur true.
- Attribuer une option de mise en forme de l’énumération PivotTableAutoFormatType.
This file contains 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 | |
| from aspose.cells.pivot import PivotTableAutoFormatType | |
| # 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(".") | |
| # Load a template file | |
| workbook = Workbook(dataDir + "Book1.xls") | |
| pivotindex = 0 | |
| # Get the first worksheet | |
| worksheet = workbook.worksheets[0] | |
| # Accessing the PivotTable | |
| pivotTable = worksheet.pivot_tables[pivotindex] | |
| # Setting the PivotTable report is automatically formatted | |
| pivotTable.is_auto_format = True | |
| # Setting the PivotTable atuoformat type. | |
| pivotTable.auto_format_type = PivotTableAutoFormatType.REPORT5 | |
| # Saving the Excel file | |
| workbook.save(dataDir + "output.xls") |
Comment définir les options de mise en forme
L’exemple de code ci-dessous montre comment formater le tableau croisé dynamique pour afficher les totaux généraux pour les lignes et les colonnes, et comment définir l’ordre des champs du rapport. Il montre également comment définir une chaîne personnalisée pour les valeurs nulles.
This file contains 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 PrintOrderType, 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(".") | |
| # Load a template file | |
| workbook = Workbook(dataDir + "Book1.xls") | |
| # Get the first worksheet | |
| worksheet = workbook.worksheets[0] | |
| pivotindex = 0 | |
| # Accessing the PivotTable | |
| pivotTable = worksheet.pivot_tables[pivotindex] | |
| # Setting the PivotTable report shows grand totals for rows. | |
| pivotTable.row_grand = True | |
| # Setting the PivotTable report shows grand totals for columns. | |
| pivotTable.column_grand = True | |
| # Setting the PivotTable report displays a custom string in cells that contain null values. | |
| pivotTable.display_null_string = True | |
| pivotTable.null_string = "null" | |
| # Setting the PivotTable report's layout | |
| pivotTable.page_field_order = PrintOrderType.DOWN_THEN_OVER | |
| # Saving the Excel file | |
| workbook.save(dataDir + "output.xls") |
Aspect et sensation du formatage manuel
Pour formater manuellement l’aspect du rapport de table de données, au lieu d’utiliser des formats de rapport prédéfinis, utilisez les méthodes PivotTable.format_all(style) et PivotTable.format(row, column, style). Créez un objet de style pour le format souhaité, par exemple :
This file contains 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, Workbook | |
| from aspose.cells.pivot import PivotTableStyleType | |
| 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(".") | |
| # Load a template file | |
| workbook = Workbook(dataDir + "Book1.xls") | |
| # Get the first worksheet | |
| worksheet = workbook.worksheets[0] | |
| pivot = workbook.worksheets[0].pivot_tables[0] | |
| pivot.pivot_table_style_type = PivotTableStyleType.PIVOT_TABLE_STYLE_DARK1 | |
| style = workbook.create_style() | |
| style.font.name = "Arial Black" | |
| style.foreground_color = Color.yellow | |
| style.pattern = BackgroundType.SOLID | |
| pivot.format_all(style) | |
| # Saving the Excel file | |
| workbook.save(dataDir + "output.xls") |
Comment définir les options de format de champ de table de données
La classe PivotField représente un champ dans une table de données et peut être formatée de plusieurs manières. L’exemple de code ci-dessous montre comment :
- Accéder aux champs de lignes.
- Définir les sous-totaux.
- Définir l’autotri.
- Définir l’auto-affichage.
Comment définir le format des champs de lignes/colonnes/page
This file contains 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 | |
| from aspose.cells.pivot import PivotFieldSubtotalType | |
| # 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(".") | |
| # Load a template file | |
| workbook = Workbook(dataDir + "Book1.xls") | |
| # Get the first worksheet | |
| worksheet = workbook.worksheets[0] | |
| pivotindex = 0 | |
| # Accessing the PivotTable | |
| pivotTable = worksheet.pivot_tables[pivotindex] | |
| # Setting the PivotTable report shows grand totals for rows. | |
| pivotTable.row_grand = True | |
| # Accessing the row fields. | |
| pivotFields = pivotTable.row_fields | |
| # Accessing the first row field in the row fields. | |
| pivotField = pivotFields[0] | |
| # Setting Subtotals. | |
| pivotField.set_subtotals(PivotFieldSubtotalType.SUM, True) | |
| pivotField.set_subtotals(PivotFieldSubtotalType.COUNT, True) | |
| # Setting autosort options. | |
| # Setting the field auto sort. | |
| pivotField.is_auto_sort = True | |
| # Setting the field auto sort ascend. | |
| pivotField.is_ascend_sort = True | |
| # Setting the field auto sort using the field itself. | |
| pivotField.auto_sort_field = -5 | |
| # Setting autoShow options. | |
| # Setting the field auto show. | |
| pivotField.is_auto_show = True | |
| # Setting the field auto show ascend. | |
| pivotField.is_ascend_show = False | |
| # Setting the auto show using field(data field). | |
| pivotField.auto_show_field = 0 | |
| # Saving the Excel file | |
| workbook.save(dataDir + "output.xls") |
Comment définir le format des champs de données
L’exemple de code ci-dessous montre comment définir les formats d’affichage et de nombres pour les champs de données.
This file contains 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 | |
| from aspose.cells.pivot import PivotFieldDataDisplayFormat, PivotItemPosition | |
| # 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(".") | |
| # Load a template file | |
| workbook = Workbook(dataDir + "Book1.xls") | |
| # Get the first worksheet | |
| worksheet = workbook.worksheets[0] | |
| pivotindex = 0 | |
| # Accessing the PivotTable | |
| pivotTable = worksheet.pivot_tables[pivotindex] | |
| # Accessing the data fields. | |
| pivotFields = pivotTable.data_fields | |
| # Accessing the first data field in the data fields. | |
| pivotField = pivotFields[0] | |
| # Setting data display format | |
| pivotField.data_display_format = PivotFieldDataDisplayFormat.PERCENTAGE_OF | |
| # Setting the base field. | |
| pivotField.base_field_index = 1 | |
| # Setting the base item. | |
| pivotField.base_item_position = PivotItemPosition.NEXT | |
| # Setting number format | |
| pivotField.number = 10 | |
| # Saving the Excel file | |
| workbook.save(dataDir + "output.xls") |
Comment effacer les champs de table de données
La classe PivotFieldCollection possède une méthode nommée clear() qui vous permet d’effacer les champs de table de données. Utilisez-la lorsque vous voulez effacer tous les champs de table de données dans les zones, par exemple, les pages, les colonnes, les lignes ou les données. L’exemple de code ci-dessous montre comment effacer tous les champs de table de données dans une zone de données.
This file contains 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 | |
| from aspose.cells.pivot import PivotFieldType | |
| # 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(".") | |
| # Load a template file | |
| workbook = Workbook(dataDir + "Book1.xls") | |
| # Get the first worksheet | |
| sheet = workbook.worksheets[0] | |
| # Get the pivot tables in the sheet | |
| pivotTables = sheet.pivot_tables | |
| # Get the first PivotTable | |
| pivotTable = pivotTables[0] | |
| # Clear all the data fields | |
| pivotTable.data_fields.clear() | |
| # Add new data field | |
| pivotTable.add_field_to_area(PivotFieldType.DATA, "Betrag Netto FW") | |
| # Set the refresh data flag on | |
| pivotTable.refresh_data_flag = False | |
| # Refresh and calculate the pivot table data | |
| pivotTable.refresh_data() | |
| pivotTable.calculate_data() | |
| # Saving the Excel file | |
| workbook.save(dataDir + "output.xls") |