파이썬에서 엑셀로 저장할 때 보기좋게 정리하는 코드

데이터프레임을 엑셀 파일로 저장하기:

import pandas as pd

df = pd.DataFrame({'col1': [1, 2, 3],
                   'col2': [4, 5, 6],
                   'col3': [7, 8, 9]})

df.to_excel("test.xlsx", index=False, engine="openpyxl", 
            header=True, startrow=1, freeze_panes=(1, 1))

import pandas as pd

df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})

# 데이터프레임을 엑셀 파일로 저장
df.to_excel("output.xlsx", index=False)

 

열 이름 바꾸기:

df = df.rename(columns={'A': 'a', 'B': 'b', 'C': 'c'})
df.to_excel("output.xlsx", index=False)

 

데이터프레임 열의 순서 바꾸기 :

df = df[['c', 'b', 'a']]
df.to_excel("output.xlsx", index=False)

 

엑셀 파일에 새로운 시트 추가하기:

df2 = pd.DataFrame({'D': [10, 20, 30], 'E': [40, 50, 60], 'F': [70, 80, 90]})

# 엑셀 파일에 새로운 시트 추가
with pd.ExcelWriter("output.xlsx") as writer:
    df.to_excel(writer, index=False, sheet_name='Sheet1')
    df2.to_excel(writer, index=False, sheet_name='Sheet2')

 

엑셀 파일에서 여러 개의 시트 읽기:

# 엑셀 파일에서 여러 개의 시트 읽기
with pd.ExcelFile("output.xlsx") as xlsx:
    df1 = pd.read_excel(xlsx, "Sheet1")
    df2 = pd.read_excel(xlsx, "Sheet2")

데이터프레임의 칼럼의 너비를 조절하는 방법

import pandas as pd

# 데이터프레임 생성
df = pd.DataFrame({
    'column1': ['A', 'B', 'C'],
    'column2': [1, 2, 3],
    'column3': [4, 5, 6],
    'column4': [7, 8, 9]
})

# 데이터프레임 저장
df.to_excel('example.xlsx', engine='openpyxl', index=False)

# 엑셀 파일 열기
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active

# 칼럼 너비 조절
for col in ws.columns:
    max_length = 0
    column = col[0].column
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(cell.value)
        except TypeError:
            pass
    adjusted_width = (max_length + 2) * 1.2
    ws.column_dimensions[column].width = adjusted_width

# 엑셀 파일 저장
wb.save('example.xlsx')

엑셀 파일의 셀 스타일 지정

import pandas as pd
import openpyxl

# 데이터프레임 생성
df = pd.DataFrame({
    'column1': ['A', 'B', 'C'],
    'column2': [1, 2, 3],
    'column3': [4, 5, 6],
    'column4': [7, 8, 9]
})

# 데이터프레임 저장
df.to_excel('example.xlsx', engine='openpyxl', index=False)

# 엑셀 파일 열기
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active

# 셀 스타일 지정
for row in ws.iter_rows():
    for cell in row:
        cell.font = openpyxl.styles.Font(name='Arial', size=12, bold=True)
        cell.alignment = openpyxl.styles.Alignment(horizontal='center')
        cell.border = openpyxl.styles.Border(left=

 

헤더 정렬

import pandas as pd

df = pd.DataFrame({'col1': [1, 2, 3],
                   'col2': [4, 5, 6],
                   'col3': [7, 8, 9]})

df.to_excel("test.xlsx", index=False, engine="openpyxl", 
            header=True, startrow=1, freeze_panes=(1, 1))

 

열 폭 조절

import pandas as pd
import openpyxl

df = pd.DataFrame({'col1': [1, 2, 3],
                   'col2': [4, 5, 6],
                   'col3': [7, 8, 9]})

writer = pd.ExcelWriter('test.xlsx', engine='openpyxl')
df.to_excel(writer, index=False)

# Access the worksheet
sheet = writer.sheets['Sheet1']

# Set the column width
for i, col in enumerate(df.columns):
    sheet.column_dimensions[col].width = 15

writer.save()

 

셀 스타일 설정

import pandas as pd
import openpyxl

df = pd.DataFrame({'col1': [1, 2, 3],
                   'col2': [4, 5, 6],
                   'col3': [7, 8, 9]})

writer = pd.ExcelWriter('test.xlsx', engine='openpyxl')
df.to_excel(writer, index=False)

# Access the worksheet
sheet = writer.sheets['Sheet1']

# Set font and color
font = openpyxl.styles.Font(color='red')

for cell in sheet["A1:C3"]:
    for c in cell:
        c.font = font

writer.save()

 

엑셀 파일에 폰트, 배경색, 테두리를 설정하는 파이썬 코드를 만들기 위해서는 Openpyxl 라이브러리를 사용할 수 있습니다. 다음은 엑셀 파일에 폰트, 배경색, 테두리를 설정하는 예제 코드입니다

import openpyxl

# 데이터프레임 생성
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})

# 엑셀 파일 생성
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl')
df.to_excel(writer, index=False)

# 생성한 엑셀 파일을 수정 가능한 상태로 열기
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# 셀 스타일 설정
font = openpyxl.styles.Font(name='Arial', size=14, bold=True)
fill = openpyxl.styles.PatternFill(fill_type='solid', start_color='FFBBBBBB')
border = openpyxl.styles.Border(left=openpyxl.styles.Side(style='thin'), 
                                right=openpyxl.styles.Side(style='thin'), 
                                top=openpyxl.styles.Side(style='thin'), 
                                bottom=openpyxl.styles.Side(style='thin'))

for row in worksheet.iter_rows():
    for cell in row:
        cell.font = font
        cell.fill = fill
        cell.border = border

# 수정 완료한 엑셀 파일 저장
writer.save()

 

워크시트에 머리글 및 바닥글 설정 - 1

df = pd.DataFrame({'Name':['John','Tom','Harry','Jane','Ashley'],
                   'Age':[32,45,34,22,36],
                   'Country':['USA','UK','Australia','Canada','Brazil']})

writer = pd.ExcelWriter('formatted_df.xlsx', engine='openpyxl')
df.to_excel(writer, index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet1']

header = 'Report on People'
footer = 'Generated on ' + str(datetime.now().date())
worksheet.oddHeader.center

 

워크시트에 머리글 및 바닥글 설정 - 2
이 코드를 실행하면 sample.xlsx 파일이 생성되며, 워크시트의 머리글 및 바닥글이 추가됩니다.

import pandas as pd

# 샘플 데이터프레임 생성
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})

# 새로운 엑셀 파일 생성
writer = pd.ExcelWriter('sample.xlsx', engine='openpyxl')
df.to_excel(writer, sheet_name='Sheet1', index=False)

# 워크시트에 머리글 및 바닥글 추가
worksheet = writer.sheets['Sheet1']
worksheet.header_str = "&L&B&20MyHeader&RMyHeader&B&20"
worksheet.footer_str = "&LMyFooter&R&B&20Page &P of &N&B&20"

# 엑셀 파일 저장
writer.save()

 

값을 기준으로 특정 셀 형식 지정 - 1

df = pd.DataFrame({'Name':['John','Tom','Harry','Jane','Ashley'],
                   'Age':[32,45,34,22,36],
                   'Country':['USA','UK','Australia','Canada','Brazil']})

writer = pd.ExcelWriter('formatted_df.xlsx', engine='openpyxl')
df.to_excel(writer, index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet1']

red_font = Font(color='FF0000')
for row in worksheet.iter_rows():
    for cell in row:
        if cell.value == 'USA':
            cell.font = red_font

writer.save()

 

값을 기준으로 특정 셀 형식 지정 - 2
Pandas와 openpyxl을 이용하여 특정 셀 형식 지정하는 것은 다음과 같습니다:

import pandas as pd
from openpyxl.styles import PatternFill, Font, Border, Side

# sample data
df = pd.DataFrame({'A': [1, 2, 3, 4], 'B': [10, 20, 30, 40]})

# write to excel
writer = pd.ExcelWriter("sample.xlsx", engine='openpyxl')
df.to_excel(writer, index=False)

# access the worksheet
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# set cell format based on values
for row in worksheet.iter_rows(min_row=1, max_col=2, max_row=4):
    for cell in row:
        if cell.value >= 30:
            fill = PatternFill("solid", fgColor="FFC7CE")
            font = Font(bold=True)
            border = Border(left=Side(style='thin'), 
                            right=Side(style='thin'), 
                            top=Side(style='thin'), 
                            bottom=Side(style='thin'))
            cell.fill = fill
            cell.font = font
            cell.border = border

writer.save()

위 코드는 B열의 값이 30 이상인 경우, 값을 포함한 셀의 배경색을 노란색, 폰트를 볼드체, 테두리를 설정합니다.

 

각 셀의 텍스트 정렬
아래의 코드는 pandas 라이브러리를 사용하여 엑셀 파일을 저장하고 각 셀의 텍스트 정렬을 지정하는 예제입니다.

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})

# Create an ExcelWriter object
writer = pd.ExcelWriter('aligned_data.xlsx', engine='openpyxl')

# Write the DataFrame to the ExcelWriter object
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Load the workbook
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Set the alignment for each column
worksheet.set_column("A:A", 20, workbook.add_format({'align': 'center'}))
worksheet.set_column("B:B", 20, workbook.add_format({'align': 'right'}))
worksheet.set_column("C:C", 20, workbook.add_format({'align': 'left'}))

# Save the workbook
writer.save()

위 코드에서, set_column 메서드를 사용하여 각 열(column)에 대한 텍스트 정렬을 지정합니다. 텍스트 정렬은 center, right, left 중 하나를 지정할 수 있습니다.

각 열의 특정 너비 설정
각 열의 특정 너비를 설정하는 예제입니다:

import pandas as pd

# create a sample dataframe
df = pd.DataFrame({'A': ['foo', 'bar', 'baz'],
                   'B': [1, 2, 3],
                   'C': [4, 5, 6],
                   'D': [7, 8, 9]})

# set the width of columns
df.style.set_properties(**{'width': '200px'})

# write to excel
df.to_excel("output.xlsx", engine='openpyxl', index=False)

위 코드는 데이터프레임 df의 너비를 200px로 설정하고, 엑셀 파일로 저장하는 예제입니다. 파이썬의 style.set_properties 함수를 사용하여 특정 열의 너비를 설정할 수도 있습니다.

Excel 파일에 로고 추가

from openpyxl import Workbook
from openpyxl.drawing.image import Image

# ...

wb = Workbook()
ws = wb.active

for row in dataframe_to_rows(df, index=True, header=True):
    ws.append(row)

img = Image(r'path\to\image.jpg')
img.width = img.width / 10
img.height = img.height / 10
ws.column_dimensions['A'].width = img.width
ws.row_dimensions[1].height = img.height
ws.row_dimensions[1].height = img.height
ws['A1'].append(img)

wb.save(r'path\to\file.xlsx')

머리글 색상 변경
엑셀 머리글의 색상을 변경하는 파이썬 코드를 작성하려면 openpyxl 라이브러리를 사용할 수 있습니다.

import openpyxl

# 엑셀 파일 열기
wb = openpyxl.load_workbook('file.xlsx')

# 활성 워크시트 선택
ws = wb.active

# 머리글을 표시하는 행 (첫 번째 행)
header_row = ws[1]

# 머리글의 모든 셀을 반복
for cell in header_row:
    cell.fill = openpyxl.styles.PatternFill(start_color="00B0F0", end_color="00B0F0", patternType="solid")

# 엑셀 파일 저장
wb.save('file.xlsx')

위 코드는 file.xlsx 파일의 머리글을 파란색으로 변경합니다. start_color와 end_color의 값을 변경하여 다른 색상으로도 지정할 수 있습니다.

엑셀 특정열 행 고정하는 파이썬 코드

import pandas as pd

# sample dataframe
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]})

# write the dataframe to an excel file
writer = pd.ExcelWriter('fixed_col.xlsx', engine='openpyxl')
df.to_excel(writer, index=False, freeze_panes=(1, 1))
writer.save()

위 코드는 pandas 라이브러리를 사용하여 데이터프레임을 엑셀 파일로 저장하고, freeze_panes 옵션을 이용하여 특정 열을 고정하는 것입니다. 위 코드에서 freeze_panes은 (1,1)으로 설정되어 있어서 A열이 고정되는 것입니다.

 

엑셀 필터를 생성하는 파이썬 코드

import openpyxl

# Open the workbook
workbook = openpyxl.load_workbook("file.xlsx")

# Select the worksheet
worksheet = workbook["Sheet1"]

# Add filter to the first row
worksheet.auto_filter.ref = "A1:D1"

# Save the changes to the workbook
workbook.save("file.xlsx")

이 코드는 file.xlsx 엑셀 파일을 열고, 그 중에서 "Sheet1" 워크시트에 필터를 추가하고, 수정된 내용을 저장합니다. 여기서, A1:D1은 필터가 적용될 행의 범위를 나타냅니다.