■ openpyxl 패키지를 사용해 엑셀 파일을 생성하는 방법을 보여준다.
▶ main.py
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
from openpyxl import Workbook from openpyxl.worksheet.worksheet import Worksheet from openpyxl.styles import Font from typing import Any from openpyxl.styles import PatternFill from openpyxl.styles import Side from openpyxl.styles import Border # 워크북 생성하기 def createWorkbook() -> Workbook: workbook = Workbook() return workbook # 워크시트 추가하기 def addWorksheet(workbook : Workbook, sheetName : str) -> Worksheet: worksheet = workbook.create_sheet(sheetName) return worksheet # 워크시트 구하기 def getWorksheet(workbook : Workbook, sheetName : str) -> Worksheet: try: return workbook[sheetName] except KeyError: return None # 범위 구하기 def getRange(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> str: mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}" return mergeRange # 셀 폰트 워크시트 설정하기 def setCellFontWorksheet(worksheet : Worksheet, fontName : str, fontSize : int, isBold : bool = False, isItalic : bool = False) -> None: font = Font(name = fontName, size = fontSize, bold = isBold, italic = isItalic) for row in worksheet.rows: for cell in row: cell.font = font # 셀 폰트 범위 설정하기 def setCellFontRange(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int, fontName : str, fontSize : int, isBold : bool = False, isItalic : bool = False) -> None: font = Font(name = fontName, size = fontSize, bold = isBold, italic = isItalic) for y in range(y1, y2 + 1): for x in range(x1, x2 + 1): worksheet.cell(row = y, column = x).font = font # 셀 병합하기 def mergeCells(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> None: mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}" worksheet.merge_cells(mergeRange) # 셀 병합 취소하기 def unmergeCells(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int) -> None: mergeRange = f"{worksheet.cell(row = y1, column = x1).coordinate}:{worksheet.cell(row = y2, column = x2).coordinate}" worksheet.unmerge_cells(mergeRange) # 셀 값 설정하기 def setCellValue(worksheet : Worksheet, y : int, x : int, value : str) -> None: worksheet.cell(row = y, column = x, value = value) # 셀 값 구하기 def getCellValue(worksheet : Worksheet, y : int, x : int) -> Any: return worksheet.cell(row = y, column = x).value # 셀 범위 스타일 설정하기 def setCellRangeStyle(worksheet : Worksheet, y1 : int, x1 : int, y2 : int, x2 : int, backgroundColor : str, borderThickness : float = 1.0, drawInnerBorder : bool = True) -> None: patternFill = PatternFill(start_color = backgroundColor, end_color = backgroundColor, fill_type = "solid") borderStyle = "thin" if borderThickness <= 1 else "medium" for y in range(y1, y2 + 1): for x in range(x1, x2 + 1): cell = worksheet.cell(row = y, column = x) cell.fill = patternFill isTop = y == y1 isBottom = y == y2 isLeft = x == x1 isRight = x == x2 borderSideDictionary = { "top" : Side(border_style = borderStyle) if isTop or (drawInnerBorder and y > y1) else Side(border_style = None), "bottom" : Side(border_style = borderStyle) if isBottom or (drawInnerBorder and y < y2) else Side(border_style = None), "left" : Side(border_style = borderStyle) if isLeft or (drawInnerBorder and x > x1) else Side(border_style = None), "right" : Side(border_style = borderStyle) if isRight or (drawInnerBorder and x < x2) else Side(border_style = None) } cell.border = Border(**borderSideDictionary) # 엑셀 파일 저장하기 def saveEXCELFile(filePath : str, workbook : Workbook) -> None: workbook.save(filePath) workbook = createWorkbook() addWorksheet(workbook, "Sheet1") addWorksheet(workbook, "Sheet2") worksheet1 = getWorksheet(workbook, "Sheet1") setCellValue(worksheet1, 2, 2, "내부선 있음") setCellValue(worksheet1, 2, 3, "예제1" ) setCellValue(worksheet1, 3, 2, "Excel" ) setCellValue(worksheet1, 3, 3, "테스트" ) setCellRangeStyle(worksheet1, 2, 2, 3, 3, "ffff00", borderThickness = 1.0, drawInnerBorder = True) setCellValue(worksheet1, 5, 2, "내부선 없음") setCellValue(worksheet1, 5, 3, "예제2" ) setCellValue(worksheet1, 6, 2, "Excel" ) setCellValue(worksheet1, 6, 3, "테스트" ) setCellRangeStyle(worksheet1, 5, 2, 6, 3, "90ee90", borderThickness = 1.0, drawInnerBorder = False) setCellFontWorksheet(worksheet1, "나눔고딕코딩", 12) setCellFontRange(worksheet1, 2, 2, 3, 3, "나눔고딕코딩", 14) worksheet2 = getWorksheet(workbook, "Sheet2") setCellValue(worksheet2, 1, 1, "병합된 셀") setCellValue(worksheet2, 1, 2, "테스트1" ) mergeCells(worksheet2, 1, 1, 1, 2) setCellValue(worksheet2, 3, 1, "병합 후 해제될 셀") setCellValue(worksheet2, 3, 2, "테스트2" ) mergeCells(worksheet2, 3, 1, 3, 2) unmergeCells(worksheet2, 3, 1, 3, 2) range = getRange(worksheet2, 3, 1, 3, 2) print(range) # A3:B3 saveEXCELFile("example.xlsx", workbook) |
▶ requirements.txt
|
1 2 3 4 |
et_xmlfile==2.0.0 openpyxl==3.1.5 |
※ pip install openpyxl 명령을 실행했다.