[Python] 엑셀의 Chart 읽고 Categories(카테고리) 출력

 

엑셀의 차트의 카테코리 정보를 출력.

아래와 같이 data가 아닌, categories를 출력하는 코드.

결과

 

반응형

 

코드

import openpyxl
from openpyxl.chart import Reference, BarChart, LineChart

def read_composite_chart(chart):
    bar_chart = None
    line_chart = None

    for ch in chart._charts:
        if isinstance(ch, BarChart):
            bar_chart = ch
        elif isinstance(ch, LineChart):
            line_chart = ch

    return bar_chart, line_chart

def print_chart_series_and_categories(chart):
    # Access all chart types within the composite chart
    bar_chart, line_chart = read_composite_chart(chart)

    # Print Bar Chart series titles and categories
    if bar_chart:
        print("Bar Chart Series Titles:")
        for i, series in enumerate(bar_chart.series):
            barTitle = series.tx.v if series.tx and series.tx.v else series.title
            bar_val_ref = series.val.numRef.f if series.val and series.val.numRef else None
            print(f"title: {barTitle}")
            print(f"Bar Chart Series Values Reference: {bar_val_ref}")

        # Print Bar Chart categories
        if bar_chart.series[0].cat:
            print("Bar Chart Categories:")
            for label in bar_chart.series[0].cat.strRef.strCache.pt:
                print(label.v)

    # Print Line Chart series titles and categories
    if line_chart:
        print("Line Chart Series Titles:")
        for i, series in enumerate(line_chart.series):
            lineTitle = series.tx.v if series.tx and series.tx.v else series.title
            line_val_ref = series.val.numRef.f if series.val and series.val.numRef else None
            print(f"title: {lineTitle}")
            print(f"Line Chart Series Values Reference: {line_val_ref}")

        # Print Line Chart categories
        if line_chart.series[0].cat:
            print("Line Chart Categories:")
            for label in line_chart.series[0].cat.strRef.strCache.pt:
                print(label.v)

# 엑셀 파일 열기
wb = openpyxl.load_workbook('C:\\temp\\test.xlsx')
sheet = wb['Sheet1']  # 원하는 시트 이름으로 변경


# 차트 객체 가져오기 (첫 번째 차트로 가정)
chart = sheet._charts[0]

# 시리즈 와 카테고리 출력
print_chart_series_and_categories(chart)

# 엑셀 파일 닫기
wb.close()