[Python] Excel, Word 다루기

 

Excel

openpyxl

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

class Copy_excel:
    def __init__(self, src):
        self.wb = load_workbook(src)

    def select_sheet(self, sheet_nm):
        self.ws = self.wb[sheet_nm]

    # Write the value in the cell defined by row_dest+column_dest         
    def write_workbook(self, row_dest, column_dest, value):
        c = self.ws.cell(row = row_dest, column = column_dest)
        c.value = value

    def write_workbook2(self, cell, value):
        self.ws[cell].value = value

        grFill = PatternFill(start_color='00cd99',
                        end_color='00cd99',
                        fill_type='solid')

        self.ws[cell].fill = grFill

    def write_yellow_fill(self, cell):
        yellowFill = PatternFill(start_color='FFFFFF00',
                   end_color='FFFFFF00',
                   fill_type='solid')
        self.ws[cell].fill = yellowFill          

    # Save excel file
    def save_excel(self, out_file):
        # active sheet
        self.wb.active = 0
        self.wb.save(out_file)
        

out_file = os.path.join(file_path, f'New_{time}.xlsx')
e = Copy_excel(os.path.join(file_path, 'demo.xlsx'))
e.select_sheet("Sh1")
e.write_workbook2('F12', 'test')
e.write_yellow_fill('F12')
e.save_excel(out_file)

 

Tuple to coordinate

행, 열 넘버 -> 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

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

How to manipulate excel workbook by Python?

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

How to set column width to bestFit in openpyxl

 

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

openpyxl.worksheet.dimensions module — openpyxl 3.0.7 documentation

 

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

openpyxl - adjust column width size

 

 

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

openpyxl - adjust column width size

https://stackoverflow.com/a/60801712

openpyxl - adjust column width size

 

 

 

https://openpyxl.readthedocs.io/en/stable/tutorial.html

Tutorial — openpyxl 3.0.1 documentation

https://openpyxl.readthedocs.io/en/stable/usage.html

Simple usage — openpyxl 3.0.1 documentation

https://gaussian37.github.io/python-etc-openpyxl/

openpyxl을 이용하여 python으로 엑셀 다루기

https://technote.kr/18

python excel xls xlsx

https://xlwt.readthedocs.io/en/latest/api.html

API Reference — xlwt 1.3.0 documentation

 

Word

from docx import Document
from docx.enum.table import WD_ALIGN_VERTICAL
from docx.enum.text import WD_ALIGN_PARAGRAPH

out_file = os.path.join(file_path, 'New_{time}.docx')
document = Document(os.path.join(file_path, 'demo.docx'))
tables = document.tables

# 0, 0 셀에 입력
tables[1].cell(0, 0).text = 'test'

# 0, 0 셀 - 2번째 paragraphs에 입력
tables[1].cell(0, 0).paragraphs[1].text = 'test'

 

python-docx

https://python-docx.readthedocs.io/en/latest/#user-guide

python-docx — python-docx 0.8.10 documentation