openpyxl 기본사항 정리

업무상 하는 일중에 testcase 문서를 작성해야 하는 일이 있는데 노가다 작업이 심하여 python automation script 를 작성해보았다.

몇년전에 MFC application에서 비슷한 작업을 할것이 있어서 MFC에서 c++ excel automation으로 작업을 했었는데 비교하여 훨씬 수월하게 작업이 가능하였다.

python에서 excel을 다룰수 있게 해주는 패키지는 몇몇가지가 있어 보였는데 openpyxl을 택하여 작업하였다.

기억해야 할만한 것들만 몇가지 추려서 정리함.

 

#import한 것들

#import for using openpyxl

import openpyxl
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color
from openpyxl.styles.borders import Border,Side
from openpyxl.drawing.image import Image

 

#폰트생성

#create font object

ft = Font(name='fontname', size=10, color='FFFFFFFF', bold=True, italic=True) #색상은 흰색 볼드 이탤릭 모두 적용(color is white and bold/itaclic both applied)

 

#border 생성

#create border object

#상하좌우 가는선(upper lower left right thin border)

thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) 

#아래쪽만 점선(lower dotted border)

dashdot_border_down = Border(bottom=Side(style='dotted'))

 

#pattern fill 생성

#create pattern fill object

blackfill = PatternFill(start_color='00000000', end_color='00000000', fill_type='solid')

 

#workbook 및 worksheet  관련

#woorkbood/worksheet manipulation

wb = Workbook()
ws = wb.active

ws.title="" #worksheet name

ws1 = wb.create_sheet("Test", 0)  #name and sheet position

ws2 = wb["Test"]  #get sheet by name

wb.active = 1 #set the active sheet

ws.freeze_panes = ws['A10']  #A9 까지 틀고정 (freeze panes)

ws.auto_filter.ref = "A1:B10" #범위안의 값들로 자동필터 지정, set auto filter by range

wb.save('./test.xlsx') #save excel file

 

#cell 관련

#cell manipulation

ws['A1'] = value #값지정, set value to cell

ws['A1'].value #값 얻어오기, get cell value

ws['A1'].font = ft  #폰트지정, apply font object to cell

ws['A1'].border = thin_border  #border 지정, apply border object to cell

ws['A1'].fill = blackfill  #채움 지정, apply fill object to cell

ws.merge_cells('A1:F1') #셀병합, merge cells

ws.column_dimensions['A'].width = 10  #컬럼너비 지정, set column width

ws.row_dimensions[1].height = 10 #행 높이 지정, set row height

ws.sheet_view.zoomScale = 100  #시트 배율 조정, set sheet zoom scale

ws['A1'].alignment = Alignment(wrap_text=True)  #셀속성 자동줄바꿈 지정, set auto text wrap attribute

ws['A1'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')  #셀정렬 지정, set cell alignment attribute

 

#image 삽입

#create image object and apply to cell

img = openpyxl.drawing.image.Image('./test.jpg')
img.anchor = 'A1'
ws.add_image(img)

 

#작성완료된 엑셀파일을 바로 엑셀로 열고 싶을때, open excel file by excel application

#win32 package를 이용하거나 os.startfile 이용

os.startfile(file_path_name)

혹은

excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = True
excel.Workbooks.Open(file_path_name)

 

* Openpyxl 상세사항은 아래 페이지를 참조

openpyxl.readthedocs.io/en/stable/

openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.0.6 documentation

 

* 아래 코드는 openpyxl을 이용하여 실제로 구현했었던 코드로 문제가 될만한 스트링을 모두 제거하고 템플릿 정도만 만들어지는 것을 볼 수 있는 코드이다.

수행을 해보면 엑셀파일이 하나 생성되는데 열어보면 어떤식으로 만들어지는지 확인가능하다.

중간부분에 db_name, testcase_name, aggregate_name 들은 VectorCAST에서 unit test 를 돌리고 나서 만들 수 있는 산출물들인데 이를 잘 활용하면 testcase 문서를 자동으로 만들어낼 수 있다.(참고용으로 남겨두었다..)

#import packages
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color
from openpyxl.styles.borders import Border,Side
from openpyxl.drawing.image import Image

import sqlite3
import os
import os.path
from string import ascii_uppercase

#set font/border/fill varable
ft = Font(name='맑은 고딕', size=10)
ft_italic = Font(name='맑은 고딕', size=10, italic=True)
ft2 = Font(name='맑은 고딕', size=10, color='FFFFFFFF', bold=True)
ft3 = Font(name='맑은 고딕', size=13, bold=True, underline='single')
ft4 = Font(name='맑은 고딕', size=10, color='00000000', bold=True)
ft5 = Font(name='맑은 고딕', size=24, color='00808080', bold=True)
ft6 = Font(name='맑은 고딕', size=24, color='00000000', bold=True)
ft7 = Font(name='맑은 고딕', size=14, color='00000000', bold=True)


thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
thin_border_right = Border(right=Side(style='thin'))
thin_border_updown = Border(top=Side(style='thin'), bottom=Side(style='thin'))
thin_border_up = Border(top=Side(style='thin'))
dashdot_border_down = Border(bottom=Side(style='dotted'))
dash_border_down2 = Border(bottom=Side(style='dotted'),right=Side(style='thin'))

none_border = Border(left=Side(style='thin', color='00FFFFFF'), right=Side(style='thin', color='00FFFFFF'), top=Side(style='thin', color='00FFFFFF'), bottom=Side(style='thin', color='00FFFFFF'))

blackfill = PatternFill(start_color='00000000', end_color='00000000', fill_type='solid')
greyfill = PatternFill(start_color='00808080', end_color='00808080', fill_type='solid')
greyfill2 = PatternFill(start_color='00D0D0D0', end_color='00D0D0D0', fill_type='solid')
redfill = PatternFill(start_color='00D60057', end_color='00D60057', fill_type='solid')

#workbook and worksheet create
wb = Workbook()
ws = wb.active
ws.title = "Test OpenpyXl Excel creation"

ws1 = wb.create_sheet("SheetA", 0)
ws2 = wb.create_sheet("SheetB", 2)
ws3 = wb.create_sheet("SheetC", 3)

ws = wb["Test OpenpyXl Excel creation"]
wb.active = 1

cells = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P']

ws['A3'].border = thin_border;ws['B3'].border = thin_border;ws['C3'].border = thin_border
ws['D3'].border = thin_border;ws['E3'].border = thin_border;ws['A4'].border = thin_border
ws['A5'].border = thin_border;ws['A6'].border = thin_border;ws['B4'].border = thin_border
ws['B5'].border = thin_border;ws['B6'].border = thin_border;ws['F3'].border = thin_border

ws.merge_cells('A1:F1')
ws.merge_cells('B4:F4')
ws.merge_cells('B5:F5')
ws.merge_cells('B6:F6')
ws.merge_cells('A8:G8')
ws.merge_cells('H8:M8')
ws.merge_cells('N8:P8')

for cell in cells:
    ws.column_dimensions[cell].width = 22

ws.column_dimensions['D'].width = 15
ws.column_dimensions['G'].width = 20
ws.column_dimensions['I'].width = 20

ws.row_dimensions[6].height = 60
ws.sheet_view.zoomScale = 90
ws1.sheet_view.zoomScale = 100
ws2.sheet_view.zoomScale = 90
ws3.sheet_view.zoomScale = 90

ws['B6'].alignment = Alignment(wrap_text=True)

ws['A1'] = "TestCellA1"
ws['A1'].font = ft3
ws['A3'] = "TestCell"
ws['B3'] = "TestCell"
ws['C3'] = "TestCell"
ws['D3'] = "TestCell"
ws['E3'] = "TestCell"
ws['A4'] = "TestCell"
ws['A5'] = "TestCell"
ws['A6'] = "TestCell"
ws['B4'] = "TestCell"
ws['B5'] = "TestCell"
ws['B6'] = "TestCell"

ws['A3'].fill = greyfill;ws['A4'].fill = greyfill;ws['A5'].fill = greyfill;
ws['A6'].fill = greyfill;ws['C3'].fill = greyfill;ws['E3'].fill = greyfill

ws['A3'].font = ft2;ws['A4'].font = ft2;ws['A5'].font = ft2
ws['A6'].font = ft2;ws['C3'].font = ft2;ws['E3'].font = ft2

ws['A8'] = "TestCell"
ws['H8'] = "TestCell"
ws['N8'] = "TestCell"

ws['A8'].fill = blackfill;ws['H8'].fill = blackfill;ws['N8'].fill = blackfill
ws['A8'].font = ft2;ws['H8'].font = ft2;ws['N8'].font = ft2


ws['A9'] = "TestCellA9"
ws['B9'] = "TestCellB9"
ws['C9'] = "TestCellC9"
ws['D9'] = "TestCellD9"
ws['E9'] = "TestCellE9"
ws['F9'] = "TestCellF9"
ws['G9'] = "TestCellG9"
ws['H9'] = "TestCellH9"
ws['I9'] = "TestCellI9"
ws['J9'] = "TestCellJ9"
ws['K9'] = "TestCellK9"
ws['L9'] = "TestCellL9"
ws['M9'] = "TestCellM9"
ws['N9'] = "TestCellN9"
ws['O9'] = "TestCellO9"
ws['P9'] = "TestCellP9"
ws.row_dimensions[8].height = 20
ws.row_dimensions[9].height = 20

ws.freeze_panes = ws['A10']

for cell in cells:
    cell = cell+str(9)
    ws[cell].font=ft2
    ws[cell].fill=greyfill


ws2['A1'] = "TestCell"
ws2['A3'] = "TestCell"
ws2['A4'] = "TestCell"
ws2['B4'] = "TestCell"
ws2['C4'] = "TestCell"
ws2['D4'] = "TestCell"
ws2['E4'] = "TestCell"
ws2['F4'] = "TestCell"

ws2['A1'].font = ft4;ws2['A3'].font = ft4;ws2['A4'].font = ft4
ws2['B4'].font = ft4;ws2['C4'].font = ft4;ws2['D4'].font = ft4
ws2['E4'].font = ft4;ws2['F4'].font = ft4

ws2['A3'].border = thin_border;ws2['A4'].border = thin_border
ws2['B4'].border = thin_border;ws2['C4'].border = thin_border
ws2['D4'].border = thin_border;ws2['E4'].border = thin_border
ws2['F4'].border = thin_border


ws2.column_dimensions['A'].width = 15
ws2.column_dimensions['B'].width = 30
ws2.column_dimensions['C'].width = 40
ws2.column_dimensions['D'].width = 55


ws3['A1'] = "TestCell"
ws3['B3'] = "TestCell"
ws3['A4'] = "TestCell"
ws3['B4'] = "TestCell"
ws3['B5'] = "TestCell"
ws3['C4'] = "TestCell"
ws3['G4'] = "TestCell"
ws3['H4'] = "TestCell"
ws3['C5'] = "TestCell"
ws3['D5'] = "TestCell"
ws3['E5'] = "TestCell"
ws3['F5'] = "TestCell"

ws3['A4'].font=ft4;ws3['B4'].font=ft4;ws3['B5'].font=ft4
ws3['C4'].font=ft4;ws3['G4'].font=ft4;ws3['H4'].font=ft4
ws3['C5'].font=ft4;ws3['D5'].font=ft4;ws3['E5'].font=ft4
ws3['F5'].font=ft4

ws3['A4'].border = thin_border;ws3['B4'].border = thin_border;ws3['B5'].border = thin_border
ws3['C4'].border = thin_border;ws3['G4'].border = thin_border;ws3['H4'].border = thin_border
ws3['C5'].border = thin_border;ws3['D5'].border = thin_border;ws3['E5'].border = thin_border
ws3['F5'].border = thin_border

ws3['A4'].fill = greyfill2;ws3['B4'].fill = greyfill2;ws3['B5'].fill = greyfill2
ws3['C4'].fill = greyfill2;ws3['G4'].fill = greyfill2;ws3['H4'].fill = greyfill2
ws3['C5'].fill = greyfill2;ws3['D5'].fill = greyfill2;ws3['E5'].fill = greyfill2
ws3['F5'].fill = greyfill2

ws3['A4'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['B4'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['B5'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['C4'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['G4'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['H4'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['C5'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['D5'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['E5'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws3['F5'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')

ws3.column_dimensions['B'].width = 52
ws3.column_dimensions['C'].width = 12
ws3.column_dimensions['D'].width = 12
ws3.column_dimensions['E'].width = 12
ws3.column_dimensions['F'].width = 12

ws3.merge_cells('A4:A5')
ws3.merge_cells('C4:F4')
ws3.merge_cells('G4:G5')
ws3.merge_cells('H4:H5')


cell_row=10
ws2_cell_row=5
ws3_cell_row=6

class_count = 0
method_count = 0
each_class_method_count = 0
tc_count_each_method = 0
old_method_name =""
old_class_id_name = ""
old_filename = ""

files = os.listdir("./")
for filename in files:
    if os.path.isdir(filename):
        filename_upper = filename.upper()
        target_path="./"+filename+"/"+filename_upper+"/"
        db_name = "./"+filename+"/"+filename_upper+"/cover.db"
        testcase_name = "./"+filename+"/"+filename+".testcase"
        aggregate_name = "./"+filename+"/"+filename+"_aggregate_coverage_report.txt"
        
        print(db_name)

#get cover.db data(each testcase result)
        if os.path.isfile(db_name):
            con = sqlite3.connect(db_name)
            cur = con.cursor()
            cur.execute("SELECT name,path FROM results")
            item_list = cur.fetchall()
            con.close()

#get testcase data(for get test value)

        if os.path.isfile(testcase_name):
            ftc=open(testcase_name, 'r')
            testcase_lines = ftc.readlines()
            ftc.close()

#get aggregate result data(for get src line)
        if os.path.isfile(aggregate_name):
            agg=open(aggregate_name, 'r', encoding='UTF8')
            aggregate_lines = agg.readlines()
    #        print(aggregate_lines[3])
            agg.close()

#temporary code(to save each env testcase result)
        fw=open(filename_upper+"TestCell.txt", 'w')
        cr='\n'
        line_list = list()
        line_list2 = list()
        tf_list = list()

        if filename.find("TestCell") != -1:
            filename2="TestCell"

       

        for row in item_list:
            #print(row)
            fw.write(row[0])
            fw.write(cr)

            class_id = row[0]
            if class_id.find(':') != -1:
                sp_result = class_id.split("::")
                if len(sp_result) == 3:
                    class_id = sp_result[1]
                else:
                    class_id = class_id[4:class_id.find(':')]
            else:
                class_id = filename

            method_name = row[0]
            if method_name.find(':') != -1:
                method_name = method_name[method_name.rfind(':')+1:len(method_name)-4]
            else:
                method_name = method_name[0:len(method_name)-4]

            testcase_id = row[0]
            if testcase_id.find(':') != -1:
                testcase_id = class_id+"_"+testcase_id[testcase_id.rfind(':')+1:len(testcase_id)]
            else:
                testcase_id = class_id+"_"+testcase_id[0:len(testcase_id)]

            tcid = row[0]
            if tcid.find(':') != -1:
                tcid = tcid[tcid.rfind(':')+1:len(tcid)]
            else:
                tcid = tcid[0:len(tcid)]


            if cell_row == 10:
                old_method_name = method_name
                old_class_id_name = class_id
                old_filename = filename
                
            if old_method_name != method_name:
                method_count = method_count + 1
                target_row = ws2_cell_row-tc_count_each_method
                ws2['A'+str(target_row)] = method_count
                ws2['B'+str(target_row)] = old_class_id_name
                ws2['C'+str(target_row)] = old_method_name
                ws2['E'+str(target_row)] = tc_count_each_method

                ws2['A'+str(target_row)].alignment = openpyxl.styles.Alignment(horizontal='left', vertical='top')
                ws2['B'+str(target_row)].alignment = openpyxl.styles.Alignment(horizontal='left', vertical='top')
                ws2['C'+str(target_row)].alignment = openpyxl.styles.Alignment(horizontal='left', vertical='top')
                ws2['E'+str(target_row)].alignment = openpyxl.styles.Alignment(horizontal='left', vertical='top')

                ws2['A'+str(target_row)].border = thin_border
                ws2['B'+str(target_row)].border = thin_border
                ws2['C'+str(target_row)].border = thin_border
                ws2['E'+str(target_row)].border = thin_border
                
                if tc_count_each_method > 1:
                    ws2.merge_cells('A'+str(target_row)+':'+'A'+str(ws2_cell_row-1))
                    ws2.merge_cells('B'+str(target_row)+':'+'B'+str(ws2_cell_row-1))
                    ws2.merge_cells('C'+str(target_row)+':'+'C'+str(ws2_cell_row-1))
                    ws2.merge_cells('E'+str(target_row)+':'+'E'+str(ws2_cell_row-1))
                
                tc_count_each_method = 1
            else:
                tc_count_each_method = tc_count_each_method + 1


            if old_class_id_name != class_id and old_filename != filename:
#            if old_filename != filename:
                class_count = class_count + 1
                ws3['A'+str(ws3_cell_row)] = class_count
                if old_class_id_name != class_id:
                    ws3['B'+str(ws3_cell_row)] = old_class_id_name
                else:
                    ws3['B'+str(ws3_cell_row)] = old_filename
                ws3['C'+str(ws3_cell_row)] = each_class_method_count
                ws3['D'+str(ws3_cell_row)] = each_class_method_count
                ws3['E'+str(ws3_cell_row)] = 0
                ws3['F'+str(ws3_cell_row)] = "TestCell"
                ws3['G'+str(ws3_cell_row)] = "TestCell"

                ws3['A'+str(ws3_cell_row)].border = thin_border
                ws3['B'+str(ws3_cell_row)].border = thin_border
                ws3['C'+str(ws3_cell_row)].border = thin_border
                ws3['D'+str(ws3_cell_row)].border = thin_border
                ws3['E'+str(ws3_cell_row)].border = thin_border
                ws3['F'+str(ws3_cell_row)].border = thin_border
                ws3['G'+str(ws3_cell_row)].border = thin_border
                ws3['H'+str(ws3_cell_row)].border = thin_border

                ws3_cell_row = ws3_cell_row+1
                each_class_method_count = 0
            else:
                each_class_method_count = each_class_method_count + 1


            old_filename = filename
            if filename.find("TestCell") != -1:
                old_filename = filename2
            old_method_name = method_name
            old_class_id_name = class_id


#if there is no expected value, treat it as return 0 function
            ret_str = row[0]
            ret_str = class_id + ret_str[0:len(ret_str)-4] + ".return:0"
#if there is no expected value, treat it as return 0 function
    
            tc_found = False
            tc_expected_found = False
            expect_result = ""
            input_lines = ""
            
            for tc_line in testcase_lines:
                if tc_line.find("TEST.END") != -1:
                    tc_found = False

                if tc_found == True:
                    input_lines = input_lines+tc_line+"\n"
                    if tc_line.find("TEST.EXPECTED") != -1:
#                        print(tc_line)
                        expect_result = tc_line[14:len(tc_line)]
                        ws['M'+str(cell_row)] = expect_result
                        tc_expected_found = True
                        

                if tc_line.find("TEST.NAME") != -1 and tc_line.find(tcid) != -1:
                    tc_found = True
                    

            ws['L'+str(cell_row)] = input_lines
            ws['A'+str(cell_row)] = filename
            if filename.find("TestCell") != -1:
                ws['A'+str(cell_row)] = filename2
            ws['B'+str(cell_row)] = class_id
            ws['C'+str(cell_row)] = method_name
            ws['D'+str(cell_row)] = "TestCell"
            ws['I'+str(cell_row)] = "TestCell"
            ws['H'+str(cell_row)] = testcase_id
            precond = "TestCell "+filename+" TestCell"
            if filename.find("TestCell") != -1:
                precond = "TestCell "+filename2+" TestCell"
            ws['K'+str(cell_row)] = precond

            ws2['D'+str(ws2_cell_row)] = testcase_id
            ws2['D'+str(ws2_cell_row)].border = thin_border
            
            target_file=target_path+row[1]
            f=open(target_file, 'r')
            
            lines=f.readlines()
            for line in lines:
                if line.find('T') != -1 or line.find('F') != -1:
                    line_split = line.split()
                    src_find_str = line_split[1] + " " + line_split[2]

                    if line_split[2] != '0':
                        if line_split[3] == 'T':
                            #print("func src line " + line_split[2] + "   TRUE")
                            data = "func src line " + line_split[2]
                            tf_list.append("TestCell")
                            line_list.append(data)
                            fw.write(data)
                            fw.write(cr)
                            for agg_line in aggregate_lines:
                                if agg_line.find(src_find_str) != -1:
                                    data2 = agg_line[14:len(agg_line)]
                                    line_list2.append(data2)
                                
                        else:
                            #print("func src line " + line_split[2] + "   FALSE")
                            data = "func src line " + line_split[2]
                            tf_list.append("TestCell")
                            line_list.append(data)
                            fw.write(data)
                            fw.write(cr)
                            for agg_line in aggregate_lines:
                                if agg_line.find(src_find_str) != -1:
                                    data2 = agg_line[14:len(agg_line)]
                                    line_list2.append(data2)                            
                            
            for tf in tf_list:
                fw.write(tf)
                fw.write(cr)

            data = ""
            for line in line_list:
                data=data+line
                data=data+"\n"

            data2 = ""
            for line in line_list2:
                data2=data2+line
#                data2=data2+"\n"
                
            ws['F'+str(cell_row)] = data2

            data = ""
            for tf in tf_list:
                data=data+tf
                data=data+"\n"
            
            ws['G'+str(cell_row)] = data
            ws['O'+str(cell_row)] = "NA"

            if not line_list:
                ws['E'+str(cell_row)] = "NA"
                ws['F'+str(cell_row)] = "NA"
                ws['G'+str(cell_row)] = "NA"
                ws['J'+str(cell_row)] = "NA"
                ws['M'+str(cell_row)] = "NA"
            else:
                ws['E'+str(cell_row)] = "NA"
                ws['J'+str(cell_row)] = "NA"

                if tc_expected_found == False:
                    ws['M'+str(cell_row)] = ret_str

            for cell in cells:
                ws[cell+str(cell_row)].alignment = Alignment(wrap_text=True)
                ws[cell+str(cell_row)].font = ft

            ws.row_dimensions[cell_row].height = 40
            
            line_list = []
            line_list2 = []            
            tf_list = []        
            f.close()
            cell_row = cell_row+1
            ws2_cell_row = ws2_cell_row + 1

        fw.close()

ws3['B'+str(ws3_cell_row)] = "TestCell"
ws3['C'+str(ws3_cell_row)] = method_count
ws3['D'+str(ws3_cell_row)] = method_count
ws3['E'+str(ws3_cell_row)] = 0
ws3['F'+str(ws3_cell_row)] = "TestCell"

ws3['A'+str(ws3_cell_row)].border = thin_border
ws3['B'+str(ws3_cell_row)].border = thin_border
ws3['C'+str(ws3_cell_row)].border = thin_border
ws3['D'+str(ws3_cell_row)].border = thin_border
ws3['E'+str(ws3_cell_row)].border = thin_border
ws3['F'+str(ws3_cell_row)].border = thin_border
ws3['G'+str(ws3_cell_row)].border = thin_border
ws3['H'+str(ws3_cell_row)].border = thin_border


for col in cells:
    for row in range(1,60):
        ws1[col+str(row)].border = none_border
        ws1[col+str(row)].font = ft

if os.path.isfile("./TestCell.jpg"):
    img = openpyxl.drawing.image.Image('./TestCell.jpg')
    img.anchor = 'B1'
    ws1.add_image(img)
        
ws1.merge_cells('B4:F7')
ws1.merge_cells('D9:D10')
ws1.merge_cells('B14:C14')
ws1.merge_cells('B15:C15')
ws1.merge_cells('B16:C16')
ws1.merge_cells('D14:F14')
ws1.merge_cells('D15:F15')
ws1.merge_cells('D16:F16')
ws1.merge_cells('B37:F37')

ws1.row_dimensions[1].height = 18
ws1.row_dimensions[2].height = 18

ws1.merge_cells('B8:F8')
ws1['B8'].fill = greyfill2
ws1.row_dimensions[8].height = 9

ws1.merge_cells('B3:F3')
ws1['B3'].fill = greyfill
ws1.row_dimensions[3].height = 9


ws1.column_dimensions['A'].width = 4
ws1.column_dimensions['B'].width = 12
ws1.column_dimensions['C'].width = 12
ws1.column_dimensions['D'].width = 55
ws1.column_dimensions['E'].width = 12
ws1.column_dimensions['F'].width = 12
ws1.row_dimensions[37].height = 86


ws1['B4'] = "TestCell"
ws1['B4'].font = ft4
ws1['B4'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['D9'] = "TestCell"
ws1['D9'].font = ft4
ws1['D9'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['B13'] = "TestCell"
ws1['B13'].font = ft4
ws1['B14'] = "TestCell"
ws1['B15'] = "TestCell"
ws1['B16'] = "TestCell"
ws1['B14'].font = ft4
ws1['B15'].font = ft4
ws1['B16'].font = ft4
ws1['B14'].fill = greyfill2
ws1['B15'].fill = greyfill2
ws1['B16'].fill = greyfill2

ws1['B14'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['B15'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['B16'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['D14'] = "TestCell"
ws1['D15'] = "TestCell"
ws1['D16'] = "TestCell"
ws1['D14'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['D15'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['D16'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')

for col in ['B','C','D','E','F']:
    for row in range(14,17):
        ws1[col+str(row)].border = thin_border_updown


ws1['B18'] = "TestCell"
ws1['B18'].font = ft7
ws1['B19'] = "TestCell"
ws1['C19'] = "TestCell"
ws1['D19'] = "TestCell"
ws1['E19'] = "TestCell"
ws1['F19'] = "TestCell"
ws1['B19'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['C19'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['D19'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['E19'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['F19'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
ws1['B19'].border = thin_border_updown
ws1['C19'].border = thin_border_updown
ws1['D19'].border = thin_border_updown
ws1['E19'].border = thin_border_updown
ws1['F19'].border = thin_border_updown

ws1['B19'].fill = greyfill2
ws1['C19'].fill = greyfill2
ws1['D19'].fill = greyfill2
ws1['E19'].fill = greyfill2
ws1['F19'].fill = greyfill2


ws1['B19'].font = ft4
ws1['C19'].font = ft4
ws1['D19'].font = ft4
ws1['E19'].font = ft4
ws1['F19'].font = ft4

ws1['B20'] = float(99.99)
ws1['C20'] = "TestCell"
ws1['D20'] = "TestCell"

ws1['B21'] = float(99.99)
ws1['C21'] = "TestCell"
ws1['D21'] = "TestCell"

ws1['B22'] = float(99.99)
ws1['C22'] = "TestCell"
ws1['D22'] = "TestCell"

ws1['B23'] = float(99.99)
ws1['C23'] = "TestCell"
ws1['D23'] = "TestCell"
ws1['F23'] = "TestCell"

ws1['B24'] = float(99.99)
ws1['C24'] = "TestCell"
ws1['D24'] = "TestCell"
ws1['F24'] = "TestCell"

ws1['B25'] = float(99.99)
ws1['C25'] = "TestCell"
ws1['D25'] = "TestCell"
ws1['F25'] = "TestCell"

ws1['B26'] = float(99.99)
ws1['C26'] = "TestCell"
ws1['D26'] = "TestCell"
ws1['F26'] = "TestCell"

ws1['B27'] = float(99.99)
ws1['C27'] = "TestCell"
ws1['D27'] = "TestCell"
ws1['F27'] = "TestCell"

ws1['B28'] = float(99.99)
ws1['C28'] = "TestCell"
ws1['D28'] = "TestCell"
ws1['F28'] = "TestCell"

ws1['B29'] = float(99.99)
ws1['C29'] = "TestCell"
ws1['D29'] = "TestCell"
ws1['F29'] = "TestCell"

ws1['B30'] = float(99.99)
ws1['C30'] = "TestCell"
ws1['D30'] = "TestCell"
ws1['F30'] = "TestCell"

ws1['B31'] = float(99.99)
ws1['C31'] = "TestCell"
ws1['D31'] = "TestCell"
ws1['F31'] = "TestCell"

ws1['B32'] = float(99.99)
ws1['C32'] = "TestCell"
ws1['D32'] = "TestCell"
ws1['F32'] = "TestCell"

ws1['B33'] = float(99.99)
ws1['C33'] = "TestCell"
ws1['D33'] = "TestCell"
ws1['F33'] = "TestCell"

ws1['B34'] = float(99.99)
ws1['C34'] = "TestCell"
ws1['D34'] = "TestCell"
ws1['F34'] = "TestCell"

ws1['B35'] = float(99.99)
ws1['C35'] = "TestCell"
ws1['D35'] = "TestCell"
ws1['F35'] = "TestCell"


for col in ['B','C','D','E','F']:
    for row in range(20,36):
        ws1[col+str(row)].border = dash_border_down2
        ws1[col+str(row)].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center', wrap_text=True)
        if col == 'F':
            ws1[col+str(row)].border= dashdot_border_down

for col in ['B','C','D','E','F']:
    ws1[col+str(36)].border = thin_border_up


ws1['B37'] = "TestCell"
#ws1['B37'].alignment = Alignment(wrap_text=True)
ws1['B37'].font = ft_italic
ws1['B37'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center', wrap_text=True)
ws1['B37'].fill = greyfill2

wb.save('./openpyxl_Test.xlsx')