使用Openpyxl更改Excel中折线图的样式

Question

我有一段代码，用来导出一组数据表，然后把它们转换成Excel里的表格，并应用颜色格式。接着，它会为每个表格创建折线图，一切都运行得很好，但我在以下几个方面遇到了问题：

应用样式12
把数据标签放到“上方”
去掉主要的网格线
去掉标题的粗体字
调整图表的大小
改变数据标签的颜色
去掉图表区域的背景

这是我得到的结果

这是我需要的结果！样式12，没有主要网格线，数据标签在上方

        # Load the existing workbook
        workbook = openpyxl.load_workbook(output_path + '\\' + output_file)

        # Get the 'USA (TM)' sheet or create it if it doesn't exist
        sheet_name = s
        if sheet_name not in workbook.sheetnames:
            workbook.create_sheet(sheet_name)
        sheet = workbook[sheet_name]

        # Assuming 'df' is your DataFrame

        # Write headers to the specified sheet starting from column F, row 1
        headers = merged_cc.columns.tolist()
        for idx, header in enumerate(headers, start=1):
            sheet.cell(row=1, column=idx + columns_position[e] - 1, value=header)

        # Write the DataFrame values to the specified sheet starting from column F, row 2
        for r_idx, row in enumerate(merged_cc.iterrows(), start=2):
            for c_idx, value in enumerate(row[1], start=1):
                sheet.cell(row=r_idx, column=c_idx + columns_position[e] -1 , value=value)
                if isinstance(value, (int, float)):
                    sheet.cell(row=r_idx, column=c_idx + columns_position[e] -1).number_format = '0%'

        # Adjust cell width of the table to specific width
        for idx, column_width in enumerate([10.71, 10.71,10.71, 10.71, 10.71], start=columns_position[e]):  # Example widths
            column_letter = openpyxl.utils.get_column_letter(idx)
            sheet.column_dimensions[column_letter].width = column_width


        # Replace 'A1:B10' with the range you want to convert
        table_range = w

        # Check if the table name already exists
        existing_tables = sheet.tables
        if table_name in existing_tables:
            del sheet._tables[table_name]
        
        # Format the range as a table
        table = openpyxl.worksheet.table.Table(displayName=table_name, ref=table_range)
        table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium13", showFirstColumn=False,
               showLastColumn=False, showRowStripes=True, showColumnStripes=False)
        # Add the table to the worksheet
        sheet.add_table(table)

        # Create Graph *

        # Create a new LineChart object
        chart = LineChart()

        # Add data to the chart
        data = Reference(sheet, min_col=columns_position[e]+2, min_row=1, max_col=columns_position[e] + 4, max_row=sheet.max_row)
        chart.add_data(data, titles_from_data=True)

        # Set the categories (X-axis labels)
        categories = Reference(sheet, min_col=columns_position[e] + 1, min_row=2, max_row=len(merged_cc)+1)
        chart.set_categories(categories)

        # Set the title of the chart
        chart.title = companies[e]
        chart.style = 12
        '''
        # Create a DataLabelList object
        data_labels = DataLabelList()
        data_labels.showVal = True  # Show the values of the data points

        # Set the data labels for the chart
        chart.dLbls = data_labels

        # Iterate through each series in the chart
        for series in chart.series:
            # Set data labels for each data point in the series
            for point in series:
                data_label = DataLabel(idx=point.index, showVal=True, position='above')  # Position data label above the data point
                point.dataLabel = data_label

        # Add the chart to the worksheet
        '''
        sheet.add_chart(chart, graph_coordenades[e])  # Adjust the cell reference as needed
        # Save the workbook
        workbook.save(output_path + '\\' + output_file)

excel data visualization openpyxl line chart chart styling data labels gridlines chart formatting

使用Openpyxl更改Excel中折线图的样式

1 个回答

撰写回答