OPENPYXL Archives - icodebroker

[PYTHON/OPENPYXL] 엑셀 컬럼 위치 변환하기

■ 엑셀 컬럼 위치를 변환하는 방법을 보여준다. ▶ main.py


from string import ascii_uppercase

def getIndex(coordinate):
    columnString = "".join(filter(str.isalpha, coordinate.upper()))
    rowString    = "".join(filter(str.isdigit, coordinate))

    row    = int(rowString)
    column = 0

    for i, character in enumerate(reversed(columnString)):
        column += (ascii_uppercase.index(character) + 1) * (26 ** i)

    return (row, column)

def getCoordinate(y, x):
    columnString = ""
    while x > 0:
        x -= 1
        columnString = ascii_uppercase[x % 26] + columnString
        x //= 26
    return f"{columnString}{y}"

print(getIndex("A1"   )) # (1, 1)
print(getIndex("B2"   )) # (2, 2)
print(getIndex("AA1"  )) # (1, 27)
print(getIndex("ZZ100")) # (100, 702)

print(getCoordinate(  1,   1)) # "A1"
print(getCoordinate(  2,   2)) # "B2"
print(getCoordinate(  1,  27)) # "AA1"
print(getCoordinate(100, 702)) # "ZZ100"

from string import ascii_uppercase

def getIndex(coordinate):

columnString = "".join(filter(str.isalpha, coordinate.upper()))

rowString = "".join(filter(str.isdigit, coordinate))

row = int(rowString)

column = 0

for i, character in enumerate(reversed(columnString)):

column += (ascii_uppercase.index(character) + 1) * (26 ** i)

return (row, column)

def getCoordinate(y, x):

columnString = ""

while x > 0:

x -= 1

columnString = ascii_uppercase[x % 26] + columnString

x //= 26

return f"{columnString}{y}"

print(getIndex("A1" )) # (1, 1)

print(getIndex("B2" )) # (2, 2)

print(getIndex("AA1" )) # (1, 27)

print(getIndex("ZZ100")) # (100, 702)

print(getCoordinate( 1, 1)) # "A1"

print(getCoordinate( 2, 2)) # "B2"

print(getCoordinate( 1, 27)) # "AA1"

print(getCoordinate(100, 702)) # "ZZ100"

[PYTHON/OPENPYXL] Workbook 클래스 : save 메소드를 사용해 엑셀 파일 저장하기

■ Workbook 클래스의 save 메소드를 사용해 엑셀 파일을 저장하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

workbook.save("example.xlsx")

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

workbook.save("example.xlsx")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Cell 클래스 : border 속성을 사용해 테두리 선 설정하기

■ Cell 클래스의 border 속성을 사용해 테두리 선을 설정하는 방법을 보여준다. ▶ main.py


from openpyxl        import Workbook
from openpyxl.styles import Side
from openpyxl.styles import Border

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

side1 = Side(border_style = "medium")
side2 = Side(border_style = "thin"  )

borderSideDictionary = {
    "top"    : side1,
    "bottom" : side2,
    "left"   : side2,
    "right"  : side1
}

border = Border(**borderSideDictionary)

cell.border = border

from openpyxl import Workbook

from openpyxl.styles import Side

from openpyxl.styles import Border

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

side1 = Side(border_style = "medium")

side2 = Side(border_style = "thin" )

borderSideDictionary = {

"top" : side1,

"bottom" : side2,

"left" : side2,

"right" : side1

}

border = Border(**borderSideDictionary)

cell.border = border

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Border 클래스 : 생성자를 사용해 Border 객체 만들기

■ Border 클래스의 생성자를 사용해 Border 객체를 만드는 방법을 보여준다. ▶ main.py


from openpyxl.styles import Side
from openpyxl.styles import Border

side1 = Side(border_style = "medium")
side2 = Side(border_style = "thin"  )

borderSideDictionary = {
    "top"    : side1,
    "bottom" : side2,
    "left"   : side2,
    "right"  : side1
}

border = Border(**borderSideDictionary)

from openpyxl.styles import Side

from openpyxl.styles import Border

side1 = Side(border_style = "medium")

side2 = Side(border_style = "thin" )

borderSideDictionary = {

"top" : side1,

"bottom" : side2,

"left" : side2,

"right" : side1

}

border = Border(**borderSideDictionary)

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을 실행했다.

[PYTHON/OPENPYXL] Side 클래스 : 생성자에서 border_style 인자를 사용해 Side 객체 만들기

■ Side 클래스의 생성자에서 border_style 인자를 사용해 Side 객체를 만드는 방법을 보여준다. ▶ main.py


from openpyxl.styles import Side

side = Side(border_style = "medium")

from openpyxl.styles import Side

side = Side(border_style = "medium")

※ border_style 인자 : "dashed", "mediumDashed", "dashDot",

[PYTHON/OPENPYXL] Cell 클래스 : fill 속성을 사용해 셀 배경색 설정하기

■ Cell 클래스의 fill 속성을 사용해 셀 배경색을 설정하는 방법을 보여준다. ▶ main.py


from openpyxl        import Workbook
from openpyxl.styles import PatternFill

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

cell.fill = PatternFill(start_color = "ffff00", end_color = "ffff00", fill_type = "solid")

from openpyxl import Workbook

from openpyxl.styles import PatternFill

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

cell.fill = PatternFill(start_color = "ffff00", end_color = "ffff00", fill_type = "solid")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] PatternFill 클래스 : 생성자에서 start_color/end_color/fill_type 인자를 사용해 PatternFill 객체 만들기

■ PatternFill 클래스의 생성자에서 start_color/end_color/fill_type 인자를 사용해 PatternFill 객체를 만드는 방법을 보여준다. ▶ main.py


from openpyxl.styles import PatternFill

patternFill = PatternFill(start_color = "ffff00", end_color = "ffff00", fill_type = "solid")

from openpyxl.styles import PatternFill

patternFill = PatternFill(start_color = "ffff00", end_color = "ffff00", fill_type = "solid")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl

[PYTHON/OPENPYXL] Worksheet 클래스 : unmerge_cells 메소드를 사용해 셀 병합 취소하기

■ Worksheet 클래스의 unmerge_cells 메소드를 사용해 셀 병합을 취소하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

worksheet.merge_cells("B2:C2")

worksheet.unmerge_cells("B2:C2")

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

worksheet.merge_cells("B2:C2")

worksheet.unmerge_cells("B2:C2")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Worksheet 클래스 : merge_cells 메소드를 사용해 셀 병합하기

■ Worksheet 클래스의 merge_cells 메소드를 사용해 셀을 병합하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

worksheet.merge_cells("B2:C2")

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

worksheet.merge_cells("B2:C2")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을 실행했다.

[PYTHON/OPENPYXL] Cell 클래스 : font 속성을 사용해 폰트 설정하기

■ Cell 클래스의 font 속성을 사용해 폰트를 설정하는 방법을 보여준다. ▶ main.py


from openpyxl        import Workbook
from openpyxl.styles import Font

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

font = Font(name = "나눔고딕코딩", size = 14, bold = True, italic = False)

cell.font = font

from openpyxl import Workbook

from openpyxl.styles import Font

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

font = Font(name = "나눔고딕코딩", size = 14, bold = True, italic = False)

cell.font = font

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을 실행했다.

[PYTHON/OPENPYXL] Cell 클래스 : coordinate 속성을 사용해 셀 위치 구하기

■ Cell 클래스의 coordinate 속성을 사용해 셀 위치를 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

print(cell.coordinate)

"""
B2
"""

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

print(cell.coordinate)

"""

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Cell 클래스 : value 속성을 사용해 셀 값 구하기

■ Cell 클래스의 value 속성을 사용해 셀 값을 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

print(cell.value)

"""
문자열1
"""

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

print(cell.value)

"""

문자열1

"""

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Worksheet 클래스 : cell 함수에서 row/column 인자를 사용해 Cell 객체 구하기

■ Worksheet 클래스의 cell 함수에서 row/column 인자를 사용해 Cell 객체를 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

cell = worksheet.cell(row = 2, column = 2)

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install

[PYTHON/OPENPYXL] Font 클래스 : 생성자에서 name/size/bold/italic 인자를 사용해 Font 객체 만들기

■ Font 클래스의 생성자에서 name/size/bold/italic 인자를 사용해 Font 객체를 만드는 방법을 보여준다. ▶ main.py


from openpyxl.styles import Font

font = Font(name = "나눔고딕코딩", size = 12, bold = False, italic = False)

from openpyxl.styles import Font

font = Font(name = "나눔고딕코딩", size = 12, bold = False, italic = False)

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl

[PYTHON/OPENPYXL] Worksheet 클래스 : cell 함수에서 row/column/value 인자를 사용해 셀 값 설정하기

■ Worksheet 클래스의 cell 함수에서 row/column/value 인자를 사용해 셀 값을 설정하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet.cell(row = 2, column = 2, value = "문자열1")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install

[PYTHON/OPENPYXL] Workbook 클래스 : active 속성을 사용해 활성 Worksheet 객체 구하기

■ Workbook 클래스의 active 속성을 사용해 활성 Worksheet 객체를 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl

[PYTHON/OPENPYXL] Workbook 클래스 : [] 연산자를 사용해 Worksheet 객체 구하기

■ Workbook 클래스에서 [] 연산자를 사용해 Worksheet 객체를 구하는 방법을 보여준다. ▶ main.py


from openpyxl import Workbook

workbook = Workbook()

worksheet1 = workbook.create_sheet("시트1")

worksheet2 = workbook["시트1"]

print(worksheet2.title)

"""
시트1
"""

from openpyxl import Workbook

workbook = Workbook()

worksheet1 = workbook.create_sheet("시트1")

worksheet2 = workbook["시트1"]

print(worksheet2.title)

"""

시트1

"""

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을

[PYTHON/OPENPYXL] Workbook 클래스 : create_sheet 메소드를 사용해 워크시트 생성하기

■ Workbook 클래스의 create_sheet 메소드를 사용해 워크시트를 생성하는 방법을 보여준다. ▶ 예제 코드 (PY)


from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.create_sheet("시트1")

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.create_sheet("시트1")

▶ requirements.txt


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl

[PYTHON/OPENPYXL] openpyxl 패키지 : 엑셀 파일 생성하기

■ openpyxl 패키지를 사용해 엑셀 파일을 생성하는 방법을 보여준다. ▶ main.py


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)

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


et_xmlfile==2.0.0
openpyxl==3.1.5

et_xmlfile==2.0.0

openpyxl==3.1.5

※ pip install openpyxl 명령을 실행했다.

[PYTHON/OPENPYXL] openpyxl 패키지 설치하기

■ openpyxl 패키지를 설치하는 방법을 보여준다. 1. 명령 프롬프트를 실행한다. 2. 명령 프롬프트에서 아래 명령을 실행한다. ▶ 실행 명령


pip install openpyxl

pip install openpyxl