xlwt excel 스타일 설정

🚫xlwt 구현 방법 참고용 (권장X) 이유는 👉 https://carpet-part1.tistory.com/470 참고🚫

excel 스타일 설정하기

 

views.py excel_export 전체 코드

import xlwt

def excel_export(request):

    locale.setlocale(locale.LC_ALL,'')
    today = datetime.today().strftime('%Y-%m-%d')
    todayValue = datetime.today().strftime('%Y-%m-%d (%a)')
	
    response = HttpResponse(content_type="application/vnd.ms-excel")
    response["Content-Disposition"] = 'attachment;filename*=UTF-8\'\'example.xls' 
    wb = xlwt.Workbook(encoding='ansi') #encoding은 ansi로 해준다.
    ws = wb.add_sheet('출입 신청', cell_overwrite_ok=True) #시트 추가 및 overwrite true 설정
    ws.col(0).width = 256 * 25

    # 스타일 추가
    style = xlwt.XFStyle()
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    style.alignment = alignment
    font = xlwt.Font()
    font.bold = True
    style.font = font
    
    # 첫번째 줄 추가
    col_names = ['출입 신청']

    for idx, col_name in enumerate(col_names):
        ws.write_merge(0, 0, 0, 5, col_name, style)
        
    # 두번째 줄 추가
    row_num = 1
    col_names = ['순번', '기간', '업체명', '직급', '성명', '비고']
    
    # 테두리 설정
    style = xlwt.XFStyle()
    borders = xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    style.borders = borders
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    style.alignment = alignment

    # 배경 색상 설정
    pattern = xlwt.Pattern() 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 22
    # background = xlwt.easyxf('pattern: pattern solid, fore_color light_green')
    pattern_style = xlwt.XFStyle()
    pattern_style.pattern = pattern
    pattern_style.borders = borders
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    pattern_style.alignment = alignment

    #열이름을 두번째 행에 추가 시켜준다.
    for idx, col_name in enumerate(col_names):
    	ws.write(row_num, idx, col_name, pattern_style)
        
    #데이터 베이스에서 유저 정보를 불러온다.
    rows = Board.objects.filter(start_date__lte = today, end_date__gte = today).values_list('start_date', 'end_date', 'company', 'position', 'guest_name', 'guest_name')
    
    # 날짜 서식으로 변경
    date_format = xlwt.XFStyle()
    date_format.borders = borders
    date_format.num_format_str = 'yyyy-mm-dd (aaa)'
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    date_format.alignment = alignment

    #유저정보를 한줄씩 작성한다.
    for row in rows:
        row_num +=1
        for col_num in range(len(row)):
            ws.write(row_num, col_num, row[col_num], style)
            ws.write(row_num, 0, row_num-1, style)
            ws.write(row_num, 1, todayValue, date_format)
            # ws.write(row_num, 1, todayValue, date_format)
            ws.write(row_num, 5, '', style)
            ws.col(1).width = 20*255
            ws.col(2).width = 20*255
            ws.col(3).width = 15*255
            ws.col(4).width = 15*255
            ws.col(5).width = 20*255
            
            
    wb.save(response)
    
    return response