241206 강의내용

from openpyxl import Workbook
import random

wb = Workbook()
ws = wb.active
ws.title = 'data'

ws.append(['순번','제품명','가격','수량','합계'])

name_list = ['기계식 키보드','게이밍 마우스','32인치 모니터','마우스 패드','헤드폰','게이밍 의자']

for i in range(random.randint(5,10)):
    name = random.choice(name_list)
    if name == '기계식 키보드':
        price = 120000
    elif name == '게이밍 마우스':
        price = 40000
    elif name == '32인치 모니터':
        price = 350000
    elif name == '마우스 패드':
        price = 20000
    elif name == '헤드폰':
        price = 80000
    elif name == '게이밍 의자':
        price = 200000
    quantity = random.randint(1,5)

    ws.append([i+1,name,price,quantity,f'=C{i+2}*D{i+2}'])

#wb.save('엑셀자동화/스마트스토어.xlsx')
#wb.save('엑셀자동화/11번가.xlsx')
wb.save('엑셀자동화/쿠팡.xlsx')
wb.close()

import win32com.client
import os
from openpyxl import Workbook, load_workbook

# file_path = "엑셀자동화/스마트스토어.xlsx"
# file_path = "엑셀자동화/11번가.xlsx"
file_path = "엑셀자동화/쿠팡.xlsx"


excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False
ewb = excel.Workbooks.Open(os.path.abspath(file_path))
ewb.Application.Calculate()
ewb.Save()
ewb.Close()
excel.Quit()

wb = load_workbook(file_path, data_only=True)  # 수식의 계산 결과만 읽음
ws = wb['data']

# # 행과 열 개수를 모두 아는 경우
# for x in range(1, 6+1):
#     for y in (range(1, 5 +1)):
#         print(ws.cell(row=x,column=y).value,end=' ')
#     print()



#행과 열 개수를 모를 경우
for x in range(1, ws.max_row+1):
    for y in (range(1, ws.max_column +1)):
        print(ws.cell(row=x,column=y).value,end=' ')
    print()

순번 제품명 가격 수량 합계 
1 32인치 모니터 350000 3 1050000 
2 32인치 모니터 350000 1 350000 
3 기계식 키보드 120000 3 360000 
4 기계식 키보드 120000 1 120000 
5 게이밍 의자 200000 2 400000 
6 기계식 키보드 120000 1 120000 
7 32인치 모니터 350000 4 1400000 

# for row in ws.rows:
#     print(row)
    
# print("="*100)

# iter_rows()가 메모리 효율이 더 좋음
for row in ws.iter_rows():
    # for cell in row:
    #     print(cell.value)
    print([cell.value for cell in row])

['순번', '제품명', '가격', '수량', '합계']
[1, '32인치 모니터', 350000, 3, 1050000]
[2, '32인치 모니터', 350000, 1, 350000]
[3, '기계식 키보드', 120000, 3, 360000]
[4, '기계식 키보드', 120000, 1, 120000]
[5, '게이밍 의자', 200000, 2, 400000]
[6, '기계식 키보드', 120000, 1, 120000]
[7, '32인치 모니터', 350000, 4, 1400000]

for row in ws.iter_rows(min_row=2, max_row=6):
    print([cell.value for cell in row])

[1, '32인치 모니터', 350000, 3, 1050000]
[2, '32인치 모니터', 350000, 1, 350000]
[3, '기계식 키보드', 120000, 3, 360000]
[4, '기계식 키보드', 120000, 1, 120000]
[5, '게이밍 의자', 200000, 2, 400000]

# 조건이 필요할때 사용하면 좋음
for row in ws.iter_rows(min_row=2, max_row=6, min_col=2, max_col=4):
    print([cell.value for cell in row])

['32인치 모니터', 350000, 3]
['32인치 모니터', 350000, 1]
['기계식 키보드', 120000, 3]
['기계식 키보드', 120000, 1]
['게이밍 의자', 200000, 2]

for row in ws.iter_rows():
    print(row)

(<Cell 'data'.A1>, <Cell 'data'.B1>, <Cell 'data'.C1>, <Cell 'data'.D1>, <Cell 'data'.E1>)
(<Cell 'data'.A2>, <Cell 'data'.B2>, <Cell 'data'.C2>, <Cell 'data'.D2>, <Cell 'data'.E2>)
(<Cell 'data'.A3>, <Cell 'data'.B3>, <Cell 'data'.C3>, <Cell 'data'.D3>, <Cell 'data'.E3>)
(<Cell 'data'.A4>, <Cell 'data'.B4>, <Cell 'data'.C4>, <Cell 'data'.D4>, <Cell 'data'.E4>)
(<Cell 'data'.A5>, <Cell 'data'.B5>, <Cell 'data'.C5>, <Cell 'data'.D5>, <Cell 'data'.E5>)
(<Cell 'data'.A6>, <Cell 'data'.B6>, <Cell 'data'.C6>, <Cell 'data'.D6>, <Cell 'data'.E6>)
(<Cell 'data'.A7>, <Cell 'data'.B7>, <Cell 'data'.C7>, <Cell 'data'.D7>, <Cell 'data'.E7>)
(<Cell 'data'.A8>, <Cell 'data'.B8>, <Cell 'data'.C8>, <Cell 'data'.D8>, <Cell 'data'.E8>)

for row in ws.iter_rows(min_row=2, values_only=True):
    # print(list(row))
    print(row)

(1, '32인치 모니터', 350000, 3, 1050000)
(2, '32인치 모니터', 350000, 1, 350000)
(3, '기계식 키보드', 120000, 3, 360000)
(4, '기계식 키보드', 120000, 1, 120000)
(5, '게이밍 의자', 200000, 2, 400000)
(6, '기계식 키보드', 120000, 1, 120000)
(7, '32인치 모니터', 350000, 4, 1400000)

for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=2, max_col=ws.max_column):
    # print(tuple([cell.value for cell in row]))
    print([cell.value for cell in row])

['32인치 모니터', 350000, 3, 1050000]
['32인치 모니터', 350000, 1, 350000]
['기계식 키보드', 120000, 3, 360000]
['기계식 키보드', 120000, 1, 120000]
['게이밍 의자', 200000, 2, 400000]
['기계식 키보드', 120000, 1, 120000]
['32인치 모니터', 350000, 4, 1400000]

## 문서 취합

from openpyxl import Workbook, load_workbook
import win32com.client
import os

total_wb = Workbook()
total_ws = total_wb.active
total_ws.title = 'data'

total_ws.append(['순번','제품명','가격','수량','합계'])

file_list = ['스마트스토어','11번가','쿠팡']

excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False

for file in file_list:
    file_path = f'엑셀자동화/{file}.xlsx'


    ewb = excel.Workbooks.Open(os.path.abspath(file_path))
    ewb.Application.Calculate()
    ewb.Save()
    ewb.Close()

    wb = load_workbook(file_path, data_only=True)
    ws = wb.active

    for row in ws.iter_rows(min_row=2):
        # data = []
        # for cell in row:
        #     data.append(cell.value)
        # total_ws.append(data)
        total_ws.append([cell.value for cell in row])

excel.Quit()


for i, cell in enumerate(total_ws['A'][1:], start = 1):
    cell.value = i

total_wb.save('엑셀자동화/total.xlsx')
total_wb.close()

## 제품 분리 (탭별로)
from openpyxl import Workbook, load_workbook

wb = load_workbook('엑셀자동화/total.xlsx')
ws = wb.active


tab = []

# 제품이름 리스트 가져오기
for i, cell in enumerate(ws['B'][1:], start = 1):
    tab.append(cell.value)

sheet = list(set(tab))

for i in range(len(sheet)):
    wb.create_sheet(sheet[i]) # 제품명에 따른 시트 추가
    for row in ws.iter_rows(min_row=2,min_col=2):
        data = []
        if row[0].value == sheet[i]: # 시트와 같은 제품명을 가진 행만 뽑고, 그 행들을 시트에 추가
            for cell in row: 
                data.append(cell.value) 
            wb[sheet[i]].append(data) 
wb.save('엑셀자동화/total_1.xlsx')
wb.close()      


'''## 강사님 코딩 1
name_list = []
# 제품이름 리스트(시트탭 생성)
for row in ws.iter_rows(min_row=2,min_col=2):
    name = row[0].value
    if name not in name_list:
        name_list.append(name)
        wb.create_sheet(name)

    wb[name].append([cell.value for cell in row])

wb.save('엑셀자동화/total_2.xlsx')
wb.close()'''

'''## 강사님 코딩 2
name_list = list(set([cell.value for cell in ws['B'][1:]]))
for name in name_list:
    wb.create_sheet(name)

for row in ws.iter_rows(min_row=2,min_col=2):
    wb[row[0].value].append([cell.value for cell in row])

wb.save('엑셀자동화/total_3.xlsx')
wb.close()'''

"## 강사님 코딩 2\nname_list = list(set([cell.value for cell in ws['B'][1:]]))\nfor name in name_list:\n    wb.create_sheet(name)\n\nfor row in ws.iter_rows(min_row=2,min_col=2):\n    wb[row[0].value].append([cell.value for cell in row])\n\nwb.save('엑셀자동화/total_3.xlsx')\nwb.close()"

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Side, Border, Alignment

file_path = '엑셀자동화/total_1.xlsx'
save_path = '엑셀자동화/total_last.xlsx'

wb = load_workbook(file_path)
#ws = wb.active
ws = wb['data']

## 열 너비 변경
ws.column_dimensions['B'].width = 20  # 문자수 기준
# ws.column_dimensions['B'].hidden = True  # 열 숨기기
# ws.column_dimensions['B'].bestfit = True  # 열 너비 자동 조정
## 행 높이 변경
ws.row_dimensions[1].height = 30 # 포인트 기준 (글자크기)

## 폰트 변경
ft = Font(size=12, color='0077ff', bold=True, name='돋움')
ws['A1'].font = ft

## 선 속성
border_type1 = Side(color='000000', border_style='thin')
border_type2 = Side(color='000000', border_style='thick')

## 선 지정
border_bottom = Border(bottom = border_type1)  # left, right, top, bottom
ws['A4'].border = border_bottom

border_all = Border(left = border_type2, right = border_type2, top = border_type2, bottom = border_type2)
ws['B4'].border = border_all

## 정렬 지정
alignment_type = Alignment(horizontal='center',vertical='center')
## horizontal(수평정렬) : left, center, right, justify
## vertical(수직정렬) : top, center, bottom
ws['B5'].alignment = alignment_type

for row in ws.iter_rows(max_row=1):
    for cell in row:
        cell.alignment = alignment_type
        cell.border = border_bottom
        cell.font = ft

wb.save(save_path)
wb.close

<bound method Workbook.close of <openpyxl.workbook.workbook.Workbook object at 0x0000020709833DD0>>

from openpyxl import Workbook
import random
from datetime import datetime, timedelta


wb = Workbook()
ws = wb.active
ws.title = 'data'

ws.append(['날짜','상품명','수량','금액'])

name_list = ['마우스','모니터','노트북','헤드폰']
k=0
for i in range(random.randint(5,10)):
    name = random.choice(name_list)
    day = datetime(2024,1,1)+timedelta(days=i)
    date = day.strftime('%Y-%m-%d')
    if name == '마우스':
        price = random.randint(10000,100000)
    elif name == '모니터':
        price = random.randint(10000,100000)
    elif name == '노트북':
        price = random.randint(10000,100000)
    elif name == '헤드폰':
        price = (random.randint(10000,10000000) // 10) * 10
    k+=1
    quantity = random.randint(1,5)

    ws.append([date,name,quantity,price])

# wb.save('엑셀자동화/sample_file_1.xlsx')
#wb.save('엑셀자동화/sample_file_2.xlsx')
# wb.save('엑셀자동화/sample_file_3.xlsx')
wb.close()

## 문서 취합 후 정렬

from openpyxl import Workbook, load_workbook
import win32com.client
import os

total_wb = Workbook()
total_ws = total_wb.active
total_ws.title = 'data'

file_list = ['sample_file_1','sample_file_2','sample_file_3']

excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False
total_ws.append(['날짜','상품명','수량','금액'])
for file in file_list:
    file_path = f'엑셀자동화/{file}.xlsx'


    ewb = excel.Workbooks.Open(os.path.abspath(file_path))
    ewb.Application.Calculate()
    ewb.Save()
    ewb.Close()

    wb = load_workbook(file_path, data_only=True)
    ws = wb.active
    
    for row in ws.iter_rows(min_row=2):
        # data = []
        # for cell in row:
        #     data.append(cell.value)
        # total_ws.append(data)
        total_ws.append([cell.value for cell in row])
    data.sort(key=lambda row:row[0])
excel.Quit()




#total_wb.save('엑셀자동화/merged_files.xlsx')
total_wb.save('엑셀자동화/merged_files1.xlsx')
total_wb.close()

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[113], line 14
     11 file_list = ['sample_file_1','sample_file_2','sample_file_3']
     13 excel = win32com.client.Dispatch("Excel.Application")
---> 14 excel.Visible = False
     15 total_ws.append(['날짜','상품명','수량','금액'])
     16 for file in file_list:

File ~\anaconda\Lib\site-packages\win32com\client\dynamic.py:707, in CDispatch.__setattr__(self, attr, value)
    705         except pythoncom.com_error:
    706             pass
--> 707 raise AttributeError(
    708     "Property '%s.%s' can not be set." % (self._username_, attr)
    709 )

AttributeError: Property 'Excel.Application.Visible' can not be set.

from openpyxl import Workbook
import random
from datetime import datetime, timedelta


wb = Workbook()
ws = wb.active
ws.title = 'data'

ws.append(['날짜','매출액','비용','이익'])

for i in range(365+1):

    day = datetime(2024,1,1)+timedelta(days=i)
    date = day.strftime('%Y-%m-%d')
    money1 = (random.randint(10000,500000)//10)*10
    money2 = (random.randint(10000,100000)//10)*10
    money3 = money1-money2
    ws.append([date,money1,money2,money3])



wb.save('엑셀자동화/sales_data_sample.xlsx')

wb.close()

## 월별 계산

from openpyxl import Workbook, load_workbook
import win32com.client
import os

total_wb = Workbook()
total_ws = total_wb.active
total_ws.title = 'data'

excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False
total_ws.append(['월','매출액','비용','이익'])

file_path = f'엑셀자동화/sales_data_sample.xlsx'


ewb = excel.Workbooks.Open(os.path.abspath(file_path))
ewb.Application.Calculate()
ewb.Save()
ewb.Close()

wb = load_workbook(file_path, data_only=True)
ws = wb.active
total1 = 0
total2 = 0
total3 = 0
for j in range(1,13):
    for row in ws.iter_rows():  
        if f'2024-{j:02d}' in row[0].value:
            total1 += row[1].value
            total2 += row[2].value
            total3 += row[3].value
    total_ws.append([f'2024-{j:02d}',total1,total2,total3])
excel.Quit()





total_wb.save('엑셀자동화/sales_report.xlsx')
total_wb.close()

## 문서 스타일 적용  == > 함수스타일로 만들어보기
from openpyxl import Workbook
import random
from datetime import datetime, timedelta
from openpyxl.styles import Font, PatternFill, Alignment

def create_format_sample():
    wb = Workbook()
    ws = wb.active
    headers = ['이름','부서','직급','입사일','급여']
    ws.append(headers)

    sample_data = [
        ['김철수','영업부','대리','2020-01-01','3500000'],
        ['이영희','개발팀','과장','2018-03-15','4500000'],
        ['박지성','인사팀','사원','2022-08-01','3000000'],
        ['최민수','영업부','차장','2015-12-01','5500000'],
        ['정수연','개발팀','대리','2021-06-15','3800000']
    ]
    for row in sample_data:
        ws.append(row)

    header_fill = PatternFill(start_color='DDDDDD', end_color='DDDDDD', fill_type='solid')
    ws['A1'].fill = header_fill
    ws['B1'].fill = header_fill
    ws['C1'].fill = header_fill
    ws['D1'].fill = header_fill
    ws['E1'].fill = header_fill
    
    ## 폰트 변경
    ft = Font(size=12, color='000000', bold=True, name='돋움')
    ws['A1'].font = ft
    ws['B1'].font = ft
    ws['C1'].font = ft
    ws['D1'].font = ft
    ws['E1'].font = ft
    
    ## 정렬 지정
    alignment_type = Alignment(horizontal='center',vertical='center')
    
    ws['A1'].alignment = alignment_type
    ws['B1'].alignment = alignment_type
    ws['C1'].alignment = alignment_type
    ws['D1'].alignment = alignment_type
    ws['E1'].alignment = alignment_type

    wb.save('엑셀자동화/format_sample.xlsx')    
    wb.close()




create_format_sample()

from openpyxl import Workbook
import random
from datetime import datetime, timedelta


wb = Workbook()
ws = wb.active
ws.title = 'data'

ws.append(['상품코드','상품명','재고수량','단가'])
products = ['노트북', '태블릿', '스마트폰', '모니터', '키보드', '마우스']

for i in range(1,101):
    # code = f"p{str(i).zfill(3)}"
    code = f"P{i:03d}"
    name = random.choice(products)
    quantity = random.randint(10,1000)
    price = random.randint(10000,2000000)
    ws.append([code,name,quantity,price])



wb.save('엑셀자동화/code_data_sample.xlsx')

wb.close()

## 필터링

from openpyxl import Workbook, load_workbook
import win32com.client
import os


def filter_data(filename,x,y):
    filter_wb = Workbook()
    filter_ws = filter_wb.active
    filter_ws.title = 'filter'
    
    wb = load_workbook(filename, data_only=True)
    ws = wb.active
    
    for cell in ws[1]:
        filter_ws[cell.coordinate] = cell.value # 현재 셀 좌표 ('A1')

    row_idx =2
    for row in ws.iter_rows(min_row=2):
        if row[x].value >= y:
            for col_idx, cell in enumerate(row,1):
                filter_ws.cell(row=row_idx,column=col_idx,value=cell.value)
            row_idx += 1

    
    filter_wb.save(f"엑셀자동화/filter_data.xlsx")
    filter_wb.close()

filter_data('엑셀자동화/code_data_sample.xlsx',2,100)

from openpyxl import load_workbook, Workbook
from openpyxl.utils import column_index_from_string

def filter_data(input_file, column_letter, min_value):
    wb = load_workbook(input_file)
    ws = wb.active
    
    new_wb = Workbook()
    new_ws = new_wb.active
    
    # 헤더 복사
    for cell in ws[1]:
        new_ws[cell.coordinate] = cell.value
    
    # 조건에 맞는 데이터만 필터링
    column_index = column_index_from_string(column_letter)  # 문자로 된 열을 인덱스로 변환
    row_idx = 2
    for row in ws.iter_rows(min_row=2):
        if row[column_index - 1].value >= min_value:  # 특정 열의 값이 기준값 이상인 경우
            for col_idx, cell in enumerate(row, start=1):
                new_ws.cell(row=row_idx, column=col_idx, value=cell.value)
            row_idx += 1
    
    new_wb.save('엑셀자동화/filtered_data2.xlsx')

filter_data('엑셀자동화/code_data_sample.xlsx', 'C', 100)

from openpyxl import Workbook

def create_validation_sample():
    wb = Workbook()
    ws = wb.active

헤더 추가
    ws.append(['주문번호', '고객명', '주문금액', '주문일자'])

일부러 오류가 포함된 데이터 생성
    sample_data = [
        ['ORD001', '김철수', '50000', '2024-01-01'],
        ['ORD002', '', '60000', '2024-01-02'],  # 빈 고객명
        ['ORD003', '박지성', 'invalid', '2024-01-03'],  # 잘못된 금액
        ['ORD004', '이영희', '70000', ''],  # 빈 날짜
        ['ORD005', '최민수', '80000', '2024-01-05']
    ]

    for row in sample_data:
        ws.append(row)

    wb.save('validation_sample.xlsx')

## 에러 데이터 표시

from openpyxl import load_workbook

def validate_data(filename):
    
    wb = load_workbook(filename)
    ws = wb.active

    errors = []
    for row_idx, row in enumerate(ws.iter_rows(min_row = 2),2):
        # 빈 셀 확인
        for col_idx, cell in enumerate(row,1):
            if cell.value is None:
                errors.append(f"빈 셀 발견: {row_idx}행 {col_idx}열")

        try:
            float(row[2].value)    
        except ValueError:
            errors.append(f"잘못된 숫자 형식: {row_idx}행")

    if errors:
        with open('validation_report.txt','w',encoding='utf-8') as f:
            for error in errors:
                f.write(f"{error}\n")

                

validate_data('엑셀자동화/validation_sample.xlsx')

from openpyxl import Workbook
import random
from datetime import datetime, timedelta

def create_chart_sample():
    wb = Workbook()
    ws = wb.active
    
    # 헤더 추가
    ws.append(['월', '매출액', '목표매출'])
    
    # 2024년 월별 데이터
    for month in range(1, 13):
        sales = random.randint(80000000, 150000000)
        target = 100000000  # 목표매출 1억으로 고정
        
        ws.append([f'2024-{str(month).zfill(2)}', sales, target])
    
    wb.save('chart_sample.xlsx')

create_chart_sample()

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

def create_chart(filename):
    wb = load_workbook(filename)
    ws = wb.active
    
    # 차트 생성
    chart = BarChart()
    chart.title = "매출 현황"
    chart.x_axis.title = "날짜"
    chart.y_axis.title = "매출액"
    
    # 데이터 범위 설정
    data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
    cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
    
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    # 차트 크기
    chart.width = 500 (포인트)
    chart.height = 400 (포인트)
    
    # 차트 추가
    ws.add_chart(chart, "E1")
    
    wb.save('chart_report.xlsx')

create_chart('chart_sample.xlsx')

from openpyxl import load_workbook
from openpyxl.chart import (BarChart, LineChart, PieChart, ScatterChart, 
                           Reference, Series)

def create_various_charts(filename):
    wb = load_workbook(filename)
    ws = wb.active
    
    # "차트 모음" 시트 생성
    chart_sheet = wb.create_sheet(title="차트 모음")
    
    # 데이터 범위 설정
    data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
    cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)

    # 1. 세로 막대 차트
    bar_chart = BarChart()
    bar_chart.title = "매출 현황 (세로 막대)"
    bar_chart.x_axis.title = "날짜"
    bar_chart.y_axis.title = "매출액"
    bar_chart.add_data(data, titles_from_data=True)
    bar_chart.set_categories(cats)
    chart_sheet.add_chart(bar_chart, "A1")

    # 2. 가로 막대 차트
    horizontal_bar = BarChart()
    horizontal_bar.type = "bar"
    horizontal_bar.title = "매출 현황 (가로 막대)"
    horizontal_bar.x_axis.title = "매출액"
    horizontal_bar.y_axis.title = "날짜"
    horizontal_bar.add_data(data, titles_from_data=True)
    horizontal_bar.set_categories(cats)
    chart_sheet.add_chart(horizontal_bar, "A15")

    # 3. 선 그래프
    line_chart = LineChart()
    line_chart.title = "매출 추이"
    line_chart.x_axis.title = "날짜"
    line_chart.y_axis.title = "매출액"
    line_chart.add_data(data, titles_from_data=True)
    line_chart.set_categories(cats)
    line_chart.style = 10
    chart_sheet.add_chart(line_chart, "I1")

    # 4. 파이 차트 (첫 6개 데이터만)
    pie_chart = PieChart()
    pie_chart.title = "매출 비중 (상위 6개)"
    pie_data = Reference(ws, min_col=2, min_row=1, max_row=7)
    pie_labels = Reference(ws, min_col=1, min_row=2, max_row=7)
    pie_chart.add_data(pie_data, titles_from_data=True)
    pie_chart.set_categories(pie_labels)
    chart_sheet.add_chart(pie_chart, "I15")

    # 5. 복합 차트 (매출 vs 목표)
    combo_chart = LineChart()
    combo_chart.title = "매출 vs 목표"
    combo_chart.x_axis.title = "날짜"
    combo_chart.y_axis.title = "금액"
    
    sales_data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
    target_data = Reference(ws, min_col=3, min_row=1, max_row=ws.max_row)
    combo_chart.add_data(sales_data, titles_from_data=True)
    combo_chart.add_data(target_data, titles_from_data=True)
    combo_chart.set_categories(cats)
    chart_sheet.add_chart(combo_chart, "Q1")

    # 6. 분산형 차트
    scatter_chart = ScatterChart()
    scatter_chart.title = "매출-목표 상관관계"
    scatter_chart.x_axis.title = "매출액"
    scatter_chart.y_axis.title = "목표매출"
    
    xvalues = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)
    yvalues = Reference(ws, min_col=3, min_row=2, max_row=ws.max_row)
    
    series = Series(yvalues, xvalues, title="매출 vs 목표")
    scatter_chart.series.append(series)
    chart_sheet.add_chart(scatter_chart, "Q15")

    # 원본 워크시트에도 차트 추가
    # 1. 세로 막대 차트 (원본용)
    orig_bar_chart = BarChart()
    orig_bar_chart.title = "매출 현황 (세로 막대)"
    orig_bar_chart.x_axis.title = "날짜"
    orig_bar_chart.y_axis.title = "매출액"
    orig_bar_chart.add_data(data, titles_from_data=True)
    orig_bar_chart.set_categories(cats)
    ws.add_chart(orig_bar_chart, "E1")

    wb.save('various_charts.xlsx')

# 함수 실행
create_various_charts('chart_sample.xlsx')

Home 241206python