xlwt

xlwt란?

• xlwt는 데이터를 기록하고 excel 파일에 정보 형식을 지정하기 위한 라이브러리
  • .xls 형식만 지원

xlwt 사용법

설치 방법

• pip install xlwt

시트 생성

import xlwt

wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('시트명')

내용 작성

ws.write(0,0,"순위")
ws.write(0,1,"이름")
ws.write(1,0,"1")
ws.write(1,1,"Ronaldo")
ws.write(2,0,"2")
ws.write(2,1,"Rashford")
ws.write(3,0,"3")
ws.write(3,1,"Isco")

파일 저장

wb.save('player_list.xls')

결과

• player_list.xls

Tip! 추가 예시: 엑셀 파일 생성

import xlwt


# 유저의 이름과 이메일을 엑셀 파일로 추출한다고 가정했을 때
def excel_export(request):
	
    response = HttpResponse(content_type="application/vnd.ms-excel")
    response["Content-Disposition"] = 'attachment; filename=' + str(datetime.date.today()) + '.xls'
   
    # 인코딩 설정
    wb = xlwt.Workbook(encoding='utf-8') 
    # 시트 추가
    ws = wb.add_sheet('User List') 
   
    row_num = 0
    col_names = ['name', 'email']
   
    # 열 이름을 첫번째 행에 추가
    for idx, col_name in enumerate(col_names):
    	ws.write(row_num, idx, col_name)        
   
    # 데이터베이스에서 유저 정보 지정
    rows = User.objects.all().values_list('name', 'email') 
   
    # 유저 정보를 한줄씩 작성
    for row in rows:
    	row_num +=1
        for col_num, attr in enumerate(row):
        	ws.write(row_num, col_num, attr)
           
    wb.save(response)
   
    return reponse

Tip! 스타일 설정

• 스타일 설정을 위한 색깔 코드 모음
  • Python xlwt Colors: Sheet

import xlwt


# 새로운 워크북, 시트 생성
wb = xlwt.Workbook(encoding='utf-8')
ws = wbwt.add_sheet('Sheet1')


# < 1. Font Style >
# Normal
ws.write(0, 0, 'Normal')

# Bold
style1 = xlwt.easyxf('font: bold on')
ws.write(1, 0, 'Bold', style1)

# Italic
style2 = xlwt.easyxf('font: italic on')
ws.write(2, 0, 'Italic', style2)

# Underline
style3 = xlwt.easyxf('font: underline on')
ws.write(3, 0, 'Underline', style3)

# Shadow
style4 = xlwt.easyxf('font: shadow on')
ws.write(4, 0, 'Shadow', style4)


# < 2. Font Size >
# Default
ws.write(0, 1, 'Default')

# 8pts
style5 = xlwt.easyxf('font: height 160')
ws.write(1, 1, '8pts', style5)

# 10pts
style6 = xlwt.easyxf('font: height 200')
ws.write(2, 1, '10pts', style6)

# 12pts
style7 = xlwt.easyxf('font: height 240')
ws.write(3, 1, '12pts', style7)

# 16pts
style8 = xlwt.easyxf('font: height 320')
ws.write(4, 1, '16pts', style8)


# < 3. Font Color >
# Black
ws.write(0, 2, 'Black')

# Red
style9 = xlwt.easyxf('font: color_index red')
ws.write(1, 2, 'Red', style9)

# Blue
style10 = xlwt.easyxf('font: color_index blue')
ws.write(2, 2, 'Blue', style10)

# Green
style11 = xlwt.easyxf('font: color_index green')
ws.write(3, 2, 'Green', style11)

# Violet
style12 = xlwt.easyxf('font: color_index violet')
ws.write(4, 2, 'Violet', style12)


# < 4. Background color >
# None
ws.write(0, 3, 'None')

# Yellow
style13 = xlwt.easyxf('pattern: pattern solid, fore_color yellow')
ws.write(1, 3, 'Yellow', style13)

# Light Green
style14 = xlwt.easyxf('pattern: pattern solid, fore_color light_green')
ws.write(2, 3, 'Light Green', style14)

# Ice Blue
style15 = xlwt.easyxf('pattern: pattern solid, fore_color ice_blue')
ws.write(3, 3, 'Ice Blue', style15)

# Rose
style16 = xlwt.easyxf('pattern: pattern solid, fore_color rose')
ws.write(4, 3, 'Rose', style16)


# < 5. Alignment >
# General
ws.write(0, 4, 'General')

# Center
style17 = xlwt.easyxf('align: horizontal center')
ws.write(1, 4, 'Center', style17)

# Left
style18 = xlwt.easyxf('align: horizontal left')
ws.write(2, 4, 'Left', style18)

# Right
style19 = xlwt.easyxf('align: horizontal right')
ws.write(3, 4, 'Right', style19)

# Justified
style20 = xlwt.easyxf('align: horizontal justified')
ws.write(4, 4, 'Justified', style20)


# < 6. Border >
# None
ws.write(0, 5, 'Border')

# Thin
style21 = xlwt.easyxf('border: top thin, right thin, bottom thin, left thin')
ws.write(2, 5, 'Thin', style21)

# Thick
style22 = xlwt.easyxf('border: top thick, right thick, bottom thick, left thick')
ws.write(4, 5, 'Thick', style22)


# < 7. Font Name >
# Default
ws.write(0, 6, 'Default')

# Arial
style23 = xlwt.easyxf('font: name arial')
ws.write(1, 6, 'Arial', style23)

# Courier New
style24 = xlwt.easyxf('font: name courier new')
ws.write(2, 6, 'Courier New', style24)

# Courier New
style25 = xlwt.easyxf('font: name verdana')
ws.write(3, 6, 'Verdana', style25)

# Times New Roman
style26 = xlwt.easyxf('font: name times new roman')
ws.write(4, 6, 'Times New Roman', style26)


# 파일 저장하기
wb.save('result_style.xls')