import xlsxwriter
workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()
expenses = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
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() # 항상 닫아줘야함
# default는 sheet1, sheet2 .... 임
worksheet1 = workbook.add_worksheet() # sheet1
worksheet2 = workbook.add_worksheet('Data')
worksheet3 = workbook.add_worksheet() # sheet3
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()
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_string()
write_number()
write_blank()
write_formula()
write_datetime()
write_boolean()
write_url()
import pandas as pd
df = pd.DataFrame({"Data": [10, 20, 30, 20, 15, 30, 45]})
df
Data | |
---|---|
0 | 10 |
1 | 20 |
2 | 30 |
3 | 20 |
4 | 15 |
5 | 30 |
6 | 45 |
## 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()
# 차트 속성을 수정하려면 어떻게 해야할까..!
# dataframe formatting 색깔 설정
worksheet.conditional_format('B2:B8', {'type': '3_color_scale'})
# 이걸 하면 알록달록 아래처럼 나옴
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()
list_data = [10, 20, 30, 20, 15, 30, 45]
df = pd.DataFrame(list_data)
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()