[Python] openpyxl - 엑셀 다루기

jen·2021년 5월 11일
1

Python

목록 보기
7/16

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

openpyxl - adjust column width size
https://stackoverflow.com/a/60801712

0개의 댓글