Using pandas and openpyxl to Create, Merge, and Style Excel Files with DataFrames

Install Pandas and openpyxl

pip install pandas openpyxl

Create DataFrames

import pandas as pd
# Create DataFrame
df1 = pd.DataFrame({
    '姓名': ['张三', '李四', '王五'],
    '年龄': [25, 30, 35],
    '城市': ['北京', '上海', '广州']
})

df2 = pd.DataFrame({
    '姓名': ['赵六', '孙七', '周八'],
    '年龄': [40, 45, 50],
    '城市': ['深圳', '成都', '杭州']
})
print(df1)
print(df2)

Merge Cells with openpyxl

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# Create DataFrame
df1 = pd.DataFrame({
    '姓名': ['张三', '李四', '王五'],
    '年龄': [25, 30, 35],
    '城市': ['北京', '上海', '广州']
})

wb = Workbook()
ws = wb.active
for r_idx, row in enumerate(dataframe_to_rows(df1, index=False, header=True)):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx+1, column=c_idx, value=value)

ws.merge_cells('A1:B1')  # Merge A1 and B1
wb.save('merged_cells.xlsx')

Write Multiple DataFrames to Different Sheets

import pandas as pd

# Create DataFrames df1 and df2 as above
with pd.ExcelWriter('combined_sheets.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

Merge Multiple Excel Files into One Workbook

import pandas as pd
files = ['file1.xlsx', 'file2.xlsx']
dfs = []
for file in files:
    dfs.append(pd.read_excel(file))
combined = pd.concat(dfs, ignore_index=True)
combined.to_excel('combined_files.xlsx', index=False)

Merge Multiple DataFrames into Different Sheets

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# Create df1 and df2 as above
wb = Workbook()
ws1 = wb.create_sheet(title='Sheet1')
ws2 = wb.create_sheet(title='Sheet2')

for r_idx, row in enumerate(dataframe_to_rows(df1, index=False, header=True)):
    for c_idx, value in enumerate(row, 1):
        ws1.cell(row=r_idx+1, column=c_idx, value=value)
for r_idx, row in enumerate(dataframe_to_rows(df2, index=False, header=True)):
    for c_idx, value in enumerate(row, 1):
        ws2.cell(row=r_idx+1, column=c_idx, value=value)
wb.save('multiple_sheets.xlsx')

Apply Styles to Merged Cells

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font, Alignment

# Create DataFrame df1 as above
wb = Workbook()
ws = wb.active
font_style = Font(bold=True, color="FF0000")
alignment = Alignment(horizontal="center", vertical="center")
for r_idx, row in enumerate(dataframe_to_rows(df1, index=False, header=True)):
    for c_idx, value in enumerate(row, 1):
        cell = ws.cell(row=r_idx+1, column=c_idx, value=value)
        if r_idx == 0:
            cell.font = font_style
            cell.alignment = alignment
ws.merge_cells('A1:B1')
ws['A1'].font = font_style
ws['A1'].alignment = alignment
wb.save('merged_cells_with_styles.xlsx')

Conditional Formatting on Merged Cells

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font, PatternFill
from openpyxl.formatting.rule import CellIsRule

# Create DataFrame df1 as above
wb = Workbook()
ws = wb.active
font_style = Font(bold=True, color="FF0000")
fill_style = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
for r_idx, row in enumerate(dataframe_to_rows(df1, index=False, header=True)):
    for c_idx, value in enumerate(row, 1):
        cell = ws.cell(row=r_idx+1, column=c_idx, value=value)
        if r_idx == 0:
            cell.font = font_style
ws.merge_cells('A1:B1')
rule = CellIsRule(operator='equal', formula=['"北京"'], stopIfTrue=True, fill=fill_style)
ws.conditional_formatting.add('C1:C3', rule)
wb.save('merged_cells_with_conditional_formatting.xlsx')

Merge DataFrames and Remove Duplicates

import pandas as pd
# Create df1 and df2 as above
combined = pd.concat([df1, df2], ignore_index=True)
unique_combined = combined.drop_duplicates()
unique_combined.to_excel('unique_combined_files.xlsx', index=False)