xlsxWriter

tutorial

install

sudo pip install XlsxWriter

아나콘다 설치시 기본적으로 내장되어 있음



In [3]:

    
import xlsxwriter



In [8]:

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

worksheet.write('A1', 'Hello world')

workbook.close()

tutorial 1: Created a Simple XLSX file



In [9]:

    
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)



In [12]:

    
workbook = xlsxwriter.Workbook('Expenses01.xlsx')

# worksheet은 sheet1, sheet2, ... 기본 이름이지만 이름을 별도로 붙일 수 있음!
worksheet = workbook.add_worksheet()

# start from the first cell! zero index
row = 0
col = 0


for item, cost in (expenses):
    worksheet.write(row, col, item)
    worksheet.write(row, col+1, cost)
    row += 1
    
# 데이터 입력할 경우 write 사용
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close() # 항상 닫아줘야함

XlsxWriter can only create new files. It cannot read or modify existing files.
수정하진 못함 주륵..!



In [11]:

    
# default는 sheet1, sheet2 .... 임
worksheet1 = workbook.add_worksheet() # sheet1
worksheet2 = workbook.add_worksheet('Data')
worksheet3 = workbook.add_worksheet() # sheet3

Tutorial 2 : Adding formatting to the XLSX file

특정 포맷..! bold처리 같은 것들



In [14]:

    
workbook = xlsxwriter.Workbook("Expenses02.xlsx")
worksheet = workbook.add_worksheet("Sheet1")

# bold 처리
bold = workbook.add_format({"bold": True})

# format for cells
money = workbook.add_format({"num_format": "$#,##0"})

# header 설정
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Cost', bold)

expenses = (
     ['Rent', 1000],
     ['Gas',   100],
     ['Food',  300],
     ['Gym',    50],
 )

row = 1 # header를 작성했기에 row가 1부터 시작
col = 0

for item, cost in (expenses):
    worksheet.write(row, col, item)
    worksheet.write(row, col+1, cost, money) 
    # write(row, col, 넣을 값(token), 형식[format])
    row += 1
    
worksheet.write(row, 0, "Total", bold)
worksheet.write(row, 1, "=sum(B2:B5)", money)

workbook.close()

Tutorial 3: Writing different typs of data to the XLSX file

다른 타입의 데이터를 넣기!



In [16]:

    
from datetime import datetime

workbook = xlsxwriter.Workbook("Expenses03.xlsx")
worksheet = workbook.add_worksheet()

bold = workbook.add_format({"bold":1})

money_format = workbook.add_format({"num_format": "$#,##0"})

date_format = workbook.add_format({"num_format": "mmmm d yyyy"})

worksheet.set_column(1, 1, 15) # column의 width 조절

worksheet.write("A1", "Item", bold)
worksheet.write("B1", "Date", bold)
worksheet.write("C1", "Cost", bold)

expenses = (
     ['Rent', '2013-01-13', 1000],
     ['Gas',  '2013-01-14',  100],
     ['Food', '2013-01-16',  300],
     ['Gym',  '2013-01-20',   50],
 )

 # Start from the first cell below the headers.
row = 1
col = 0

for item, date_str, cost in (expenses):
    date = datetime.strptime(date_str, "%Y-%m-%d")

    worksheet.write_string  (row, col, item)
    worksheet.write_datetime(row, col + 1, date, date_format )
    worksheet.write_number  (row, col + 2, cost, money_format)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total', bold)
worksheet.write(row, 2, '=SUM(C2:C5)', money_format)

workbook.close()

write() method

여기에 더 많은 정보가 있어요

write_string()

write_number()

write_blank()

write_formula()

write_datetime()

write_boolean()

write_url()

workbook class

constant_memory : 메모리에 있는 데이터를 효율적으로 관리

workbook = xlsxwriter.Workbook(filename, {"constant_memory":True})

tmpdir : 임시 파일을 저장할 장소..!

workbook = xlsxwriter.Workbook(filename, {'tmpdir': '/home/user/tmp'})

working with python pandas and xlsxwriter

xlwt와 openpyxl or xlsxWriter 모듈사용



In [17]:

    
import pandas as pd



In [18]:

    
df = pd.DataFrame({"Data": [10, 20, 30, 20, 15, 30, 45]})



In [19]:

    
df



In [30]:

    
## pandas로 xlsxwriter 접근하기
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

writer = pd.ExcelWriter("pandas_simple.xlsx", engine="xlsxwriter")

df.to_excel(writer, sheet_name="Sheet1")


workbook = writer.book
worksheet = writer.sheets["Sheet1"]

# chart 객체 생성
chart = workbook.add_chart({"type":'column'})


# dataframe 데이터에서 chart 범위설정
chart.add_series({"values":"=Sheet1!$B$2:$B$8"})

# worksheet에 chart 삽입
worksheet.insert_chart("D2", chart)

writer.save()



In [31]:

    
# 차트 속성을 수정하려면 어떻게 해야할까..!



In [ ]:

    
# dataframe formatting 색깔 설정
worksheet.conditional_format('B2:B8', {'type': '3_color_scale'})

# 이걸 하면 알록달록 아래처럼 나옴



In [32]:

    
df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})
df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})
df4 = pd.DataFrame({'Data': [41, 42, 43, 44]})

# writer 설정
writer = pd.ExcelWriter('pandas_positioning.xlsx', engine='xlsxwriter')

# df1을 넣는 위치 설정
df1.to_excel(writer, sheet_name='Sheet1')  # 기초 A1
df2.to_excel(writer, sheet_name='Sheet1', startcol=3) # col 4번째(3)부터 시작
df3.to_excel(writer, sheet_name='Sheet1', startrow=6) # row 7번째부터 시작

# header와 index 없이 설정가능! 단 시작 위치는 고려해야함
df4.to_excel(writer, sheet_name='Sheet1',
             startrow=7, startcol=4, header=False, index=False)

# writer를 닫음
writer.save()

pandas + xlsx writer + vincent 로 차트 그리기



In [35]:

    
list_data = [10, 20, 30, 20, 15, 30, 45]
df = pd.DataFrame(list_data)



In [38]:

    
excel_file = 'column.xlsx'
sheet_name = 'Sheet1'

writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')

df.to_excel(writer, sheet_name=sheet_name)

workbook = writer.book
worksheet = writer.sheets[sheet_name]

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

chart.add_series({
        'values': '=Sheet1!$B$2:$B$8',
        'gap': 2 # 여백
    })

# chart : y axis 설정
chart.set_y_axis({'major_gridlines': {'visible': False}})

# chart legend(범주) 해제
chart.set_legend({'position': 'none'})

# insert the chart into the worksheet
worksheet.insert_chart('D2', chart) # d2는 에러나고 D2라고 해야되요

writer.save()



In [ ]: