표만들기

import xlsxwriter

workbook = xlsxwriter.Workbook ('tutorial_3_1.xlsx')
worksheet = workbook.add_worksheet()

chart = workbook.add_chart({'type':'column'})
bold = workbook.add_chart({'bold': True})

# 데이터 만들기

chart.set_x_axis({
    'name': 'Student Score',
    'name_font': {'size': 14, 'bold': True},
})

data = [[70,64,30],
        [45, 33, 90],
        [25, 44, 30]]

worksheet.write(0, 1, '국', bold)
worksheet.write(0, 2, '영', bold)
worksheet.write(0, 3, '수', bold)

worksheet.write(1, 0, '동은', bold)
worksheet.write(2, 0, '연진', bold)
worksheet.write(3, 0, '사라', bold)

worksheet.write_column('B2', data[0])
worksheet.write_column('C2', data[1])
worksheet.write_column('D2', data[2])

chart.add_series({
    'categories' : '=Sheet1!$A$2:$A$4', #동은, 연진, 사라
    'values' : '=Sheet1!$B$2:$B$4',#국어
    'name' : '=Sheet1!$B$1'
})

chart.add_series({
    'values' : '=Sheet1!$C$2:$C$4',# 영어
    'name' : '=Sheet1!$C$1'
})

chart.add_series({
    'values' : '=Sheet1!$D$2:$D$4',# 수학
    'name' : '=Sheet1!$D$1'
})

worksheet.insert_chart('A7', chart)

workbook.close()

bar차트

import xlsxwriter

workbook = xlsxwriter.Workbook ('tutorial_3_2.xlsx')
worksheet = workbook.add_worksheet()

chart = workbook.add_chart({'type':'bar'})
bold = workbook.add_chart({'bold': True})

# 데이터 만들기

chart.set_x_axis({
    'name': 'Student Score',
    'name_font': {'size': 14, 'bold': True},
})

data = [[70,64,30],
        [45, 33, 90],
        [25, 44, 30]]

worksheet.write(0, 1, '국', bold)
worksheet.write(0, 2, '영', bold)
worksheet.write(0, 3, '수', bold)

worksheet.write(1, 0, '동은', bold)
worksheet.write(2, 0, '연진', bold)
worksheet.write(3, 0, '사라', bold)

worksheet.write_column('B2', data[0])
worksheet.write_column('C2', data[1])
worksheet.write_column('D2', data[2])

worksheet.write(4, 1, '=round(AVERAGE(B2:B4),2)')
worksheet.write(4, 2, '=round(AVERAGE(C2:C4),2)')
worksheet.write(4, 3, '=round(AVERAGE(D2:D4),2)')

chart.add_series({
    'categories' : '=Sheet1!$A$2:$A$4', #동은, 연진, 사라
    'values' : '=Sheet1!$B$2:$B$4',#국어
    'name' : '=Sheet1!$B$1'
})

chart.add_series({
    'values' : '=Sheet1!$C$2:$C$4',# 영어
    'name' : '=Sheet1!$C$1'
})

chart.add_series({
    'values' : '=Sheet1!$D$2:$D$4',# 수학
    'name' : '=Sheet1!$D$1'
})

worksheet.insert_chart('A7', chart)

workbook.close()

판다스로 line chat 그리기

import pandas as pd
import xlsxwriter

data = {
    '이름' : ['동은','연진','사라'],
    '국' :[70,64,30],
    '영' :[45, 33, 90],
    '수' : [25, 44, 30]
}

df = pd.DataFrame(data)
df = df.set_index('이름')

writer = pd.ExcelWriter('test_3_3_pd.xlsx', engine='xlsxwriter')

df.to_excel(writer, sheet_name = 'First')

workbook = writer.book
worksheet = writer.sheets['First']

chart = workbook.add_chart({'type':'line'})

chart.set_x_axis({
    'name' : 'Student Score',
    'name_font' : {'size': 14, 'bold':True},
})

cell_format = workbook.add_format()
cell_format.set_align('center')
cell_format.set_bold()

worksheet.write(4,0, '평균', cell_format)
worksheet.write(4, 1, '=round(AVERAGE(B2:B4),2)')
worksheet.write(4, 2, '=round(AVERAGE(C2:C4),2)')
worksheet.write(4, 3, '=round(AVERAGE(D2:D4),2)')


chart.add_series({
    'categories' : '=First!$A$2:$A$5', #동은, 연진, 사라
    'values' : '=First!$B$2:$B$5',#국어
    'name' : '=First!$B$1'
})

chart.add_series({
    'values' : '=First!$C$2:$C$5',# 영어
    'name' : '=First!$C$1'
})

chart.add_series({
    'values' : '=First!$D$2:$D$5',# 수학
    'name' : '=First!$D$1'
})

worksheet.insert_chart('A7', chart)

workbook.close()
writer.save()

도넛

df = pd.read_excel('./test_3_3_pd.xlsx')
df = df.set_index('이름')
df = df['동은':'사라']

writer = pd.ExcelWriter('test_3_5_1.xlsx', engine='xlsxwriter')

df.to_excel(writer, sheet_name = 'sheet')

workbook = writer.book
worksheet = writer.sheets['sheet']

chart = workbook.add_chart({'type': 'doughnut'})

chart.add_series({'categories': '=sheet!$A$2:$A$5',
                  'values': '=sheet!$B$2:$B$5',
                  'name': '=sheet!$B$1'})

chart.add_series({
    'values' : '=sheet!$C$2:$C$5',# 영어
    'name' : '=sheet!$C$1'
})

chart.add_series({
    'values' : '=sheet!$D$2:$D$5',# 수학
    'name' : '=sheet!$D$1'
})

worksheet.insert_chart('A7', chart)

workbook.close()

엑셀에 이미지 넣기

import xlsxwriter

workbook = xlsxwriter.Workbook('test_4_2.xlsx')
worksheet = workbook.add_worksheet()

worksheet.insert_image('B2', r'./도넛1.png', 
{'x_scale': 0.1,'y_scale':0.1,
'x_offset' : 20, 'y_offset': 10  
 })

workbook.close()

셀과 로우와 변경해도 이미지 사이즈 고정 시키기

import xlsxwriter

workbook = xlsxwriter.Workbook('test_4_3.xlsx')
worksheet = workbook.add_worksheet()

worksheet.insert_image('B2', r'./도넛1.png',
                       {'x_scale': 0.5,'y_scale':0.5,
                        'positioning' :3
                        })

workbook.close()