[Python] openpyxl - 엑셀 다루기

Jen Y·2021년 5월 11일

COLUMN Size auto auto_size bestfit openpyxl python width 수정 엑셀 저장 파이썬

Python

목록 보기

7/17

Image 사이즈 조절

https://www.wikitechy.com/tutorial/openpyxl/openpyxl-adding-image

height, width 는 pixel
cm * 37.7952755906 = pixel
- 15 cm = 15 × 37.7952755906 pixel (X) = 566.9291338583 pixel (X)

from openpyxl import load_workbook
from openpyxl.drawing.image import Image  

# Let's use the hello_world spreadsheet since it has less data  
workbook = load_workbook(filename="merging.xlsx")  
sheet = workbook.active

logo = Image(r"logo.png")  

# A bit of resizing to not fill the whole spreadsheet with the logo  
logo.height = 150  
logo.width = 300  

sheet.add_image(logo, "E2")  
workbook.save(filename="logo.xlsx")

Tuple to coordinate

def get_coordinate(row, col):
    return ws.cell(row = row, column = col).coordinate

get_coordinate(1, 1) # 'A1'

Utils

from openpyxl.utils import get_column_letter, coordinate_to_tuple

coordinate_to_tuple('B4') # (4, 2)

get_column_letter(2) # 'B'

Hide grid and set zoom scale

ws_test.sheet_view.showGridLines = False
ws_test.sheet_view.zoomScale = 80

Styles

PatternFill, Border, Side, Alignment, Protection, Font

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
font = Font(name='Calibri',
                size=11,
                bold=False,
                italic=False,
                vertAlign=None,
                underline='none',
                strike=False,
                color='FF000000')
fill = PatternFill(fill_type=None,
                start_color='FFFFFFFF',
                end_color='FF000000')
border = Border(left=Side(border_style=None,
                          color='FF000000'),
                right=Side(border_style=None,
                           color='FF000000'),
                top=Side(border_style=None,
                         color='FF000000'),
                bottom=Side(border_style=None,
                            color='FF000000'),
                diagonal=Side(border_style=None,
                              color='FF000000'),
                diagonal_direction=0,
                outline=Side(border_style=None,
                             color='FF000000'),
                vertical=Side(border_style=None,
                              color='FF000000'),
                horizontal=Side(border_style=None,
                               color='FF000000')
               )
alignment=Alignment(horizontal='general',
                    vertical='bottom',
                    text_rotation=0,
                    wrap_text=False,
                    shrink_to_fit=False,
                    indent=0)
number_format = 'General'
protection = Protection(locked=True,
                        hidden=False)

셀에서 multi-line string 입력 하고 싶을 경우

alignment.wrap_text = True

from openpyxl import Workbook

workbook = Workbook()
worksheet = workbook.worksheets[0]
worksheet.title = "Sheet1"

worksheet.cell('A1').style.alignment.wrap_text = True
worksheet.cell('A1').value = "Line 1\nLine 2\nLine 3"

workbook.save('wrap_text1.xlsx')

Creating a Named Style

from openpyxl.styles import NamedStyle, Font, Border, Side

# highlight style 생성
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
highlight.alignment = Alignment(horizontal='right', vertical='cen ter')
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight)

# title style 생성
title = NamedStyle(name="title")
title.font = Font(bold=True, size=20)
title.alignment = highlight.alignment
wb.add_named_style(title)

# A1에 hightlight style 적용
ws['A1'].style = highlight

ws['D5'].style = 'highlight'

Border

Border(left=None,
	right=None,
    	top=None,
    	bottom=None,
    	diagonal=None,
    	diagonal_direction=None,
    	vertical=None,
    	horizontal=None,
    	diagonalUp=False,
    	diagonalDown=False,
    	outline=True,
    	start=None,
    	end=None)

Side(style=None, color=None, border_style=None)

Side

border_style or style

hair
dashDotDot, dashDot
thin 보통 굵기
'mediumDashed’, ‘slantDashDot’, ‘dotted’, ‘dashed’
‘medium’ 약간 굵음
‘thick’ 굵음
‘mediumDashDot’
‘double’ 2줄
‘mediumDashDotDot’

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.merge_cells('B2:F4')

top_left_cell = ws['B2']
top_left_cell.value = "My Cell"

thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")

top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)
top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD")
top_left_cell.fill = fill = GradientFill(stop=("000000", "FFFFFF"))
top_left_cell.font  = Font(b=True, color="FF0000")
# 본문에 있을경우 자동 비공해 전환되서 cen ter 공백 넣음............
top_left_cell.alignment = Alignment(horizontal="cen ter", vertical="cen ter")

wb.save("styled.xlsx")

바깥쪽 border 설정 출처1

BORDER_LIST = ['C4:F4', 'C5:F6']

def set_border(ws, cell_range):
    rows = ws[cell_range]
    side = Side(border_style='thin', color="FF000000")

    rows = list(rows)
    max_y = len(rows) - 1  # index of the last row
    for pos_y, cells in enumerate(rows):
        max_x = len(cells) - 1  # index of the last cell
        for pos_x, cell in enumerate(cells):
            border = Border(
                left=cell.border.left,
                right=cell.border.right,
                top=cell.border.top,
                bottom=cell.border.bottom
            )
            if pos_x == 0:
                border.left = side
            if pos_x == max_x:
                border.right = side
            if pos_y == 0:
                border.top = side
            if pos_y == max_y:
                border.bottom = side

            # set new border only if it's one of the edge cells
            if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
                cell.border = border

# top range
def set_top_border_range(ws, cell_range):
    rows = ws[cell_range]
    side = Side(border_style='thin')
    
    rows = list(rows)
    for pos_y, cells in enumerate(rows):
        for pos_x, cell in enumerate(cells):
            border = Border(
                left=cell.border.left,
                right=cell.border.right,
                top=side,
                bottom=cell.border.bottom
            )
            cell.border = border



# border
for pos in BORDER_LIST:
    set_border(ws_test, pos)

How to set column width to bestFit in openpyxl

from openpyxl.worksheet.dimensions import ColumnDimension

wb = Workbook()
ws = wb.active
ColumnDimension(ws, bestFit=True)

# or

for i in range(1, ws.max_column+1):
# Since in openpyxl 2.6.1, it requires the column letter,
# not the column number, when setting the width.
  #ws.column_dimensions[get_column_letter(i)].width = 19.5
  ws.column_dimensions[get_column_letter(i)].bestFit = True
  ws.column_dimensions[get_column_letter(i)].auto_size = True

https://stackoverflow.com/questions/60248319/how-to-set-column-width-to-bestfit-in-openpyxl

https://stackoverflow.com/questions/13197574/openpyxl-adjust-column-width-size

https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.dimensions.html?highlight=ColumnDimension#openpyxl.worksheet.dimensions.ColumnDimension

bestFit 작동이 잘 안됨 ㅠ 그래서 string length*1.23로 설정

final code

for column_cells in ws.columns:
    new_column_length = max(len(str(cell.value)) for cell in column_cells)
    new_column_letter = (get_column_letter(column_cells[0].column))
    if new_column_length > 0:
        ws.column_dimensions[new_column_letter].width = new_column_length*1.23