Python Excel

xlsxWriter : 쓰기 전용
openpyXl : 읽기 쓰기 모두 가능

import openpyxl

wb = openpyxl.Workbook()
ws1 = wb.active
ws1.title = "sheet1"
ws2 = wb.create_sheet('sheet2')

ws1['A1'] = 10ws1['A2'] = 20ws1['A3'] = '=sum(A1:A2)'
ws1.append([10,200,30])
ws1.append([40,50,60])

ws2['A1'] = 'korea'wb.save('my..xlsx')

import openpyxl

wb = openpyxl.load_workbook('my..xlsx')
ws1 = wb['sheet1']
a1 = ws1['A1'].value
print(a1)


print(wb['sheet1']['A1'].value)
wb['sheet1']['A1'].value = 'hello'wb['sheet1']['A2'].value = 'hi'
wb.save('my.xlsx')




import openpyxl

wb = openpyxl.load_workbook("my1.xlsx")
ws = wb['Sheet']
print(ws.max_row, ws.max_column)
print(ws.rows)

for c1, c2, c3, c4 in ws.rows:
    print (c1.value, c2.value, c3.value, c4.value)

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

count = 0while True:
    name = input("이름:")
    age  = int(input("나이:"))
    ws.append([name, age])
    yn = input("계속입력(y/n)? ")
    if yn == 'n':
        breakwb.save("my2.xlsx")

wb2 = openpyxl.load_workbook("my2.xlsx")
ws2 = wb2['Sheet']

for c1, c2 in ws2.rows:
    print(c1.value, c2.value)





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

wb = openpyxl.Workbook()
ws = wb.active

ws.append(["이름", "국어", "영어"])
ws.append(['순신', 50, 30])
ws.append(['길동', 80, 50])
ws.append(['꺽정', 30, 70])
ws.append(['영수', 10, 20])

chart = BarChart()
chart.style = 10 # 색상chart.title = "점수"chart.x_axis.title = "이름"chart.y_axis.title = "점수"
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=5) #데이터 영역cat = Reference(ws, min_col=1, min_row=2, max_row=5) # x 축의 이름 : category
chart.add_data(data, titles_from_data=True)
chart.set_categories(cat)
ws.add_chart(chart, "F1")
wb.save("char.xlsx")