Python 使用 openpyxl 和 pandas 处理 Excel 详解

Python 使用 openpyxl 和 pandas 处理 Excel 详解 | 极客日志

pip install openpyxl

pip install openpyxl[charts]

from openpyxl import Workbook

# 创建一个新工作簿
wb = Workbook()
# 获取活动的工作表
ws = wb.active
# 设置工作表标题
ws.title = "第一个工作表"
# 创建新的工作表
ws1 = wb.create_sheet("第二个工作表")
# 默认插入到最后
ws2 = wb.create_sheet("第三个工作表", 0)
# 插入到第一个位置
# 保存工作簿
wb.save("新工作簿.xlsx")

from openpyxl import load_workbook

# 打开一个已存在的工作簿
wb = load_workbook('example.xlsx')
# 获取所有工作表名称
print(wb.sheetnames)
# 通过名称获取工作表
ws = wb['Sheet1']
# 检查工作表是否存在
if 'Sheet1' in wb.sheetnames:
    print("Sheet1 存在")

# 复制工作表
source = wb['Sheet1']
target = wb.copy_worksheet(source)
target.title = "Sheet1 的副本"
# 删除工作表
del wb['Sheet1 的副本']
# 遍历所有工作表
for sheet in wb:
    print(sheet.title)

# 获取单元格
cell = ws['A1']
# 写入值
ws['A1'] = "Hello"
ws['B1'] = "World"
# 使用单元格坐标
ws.cell(row=1, column=3, value="!")
# 读取值
print(ws['A1'].value)  # 输出：Hello
# 单元格坐标
print(cell.row, cell.column)  # 输出：1, 1
print(cell.coordinate)  # 输出：A1

# 批量写入数据
for row in range(1, 6):
    for col in range(1, 5):
        ws.cell(row=row, column=col, value=f"R{row}C{col}")

# 批量读取数据
for row in ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
    for cell in row:
        print(cell.value, end="\t")
    print()

# 使用 values_only 参数只获取值
for row in ws.iter_rows(values_only=True):
    print(row)

# 合并单元格
ws.merge_cells('A1:D1')
ws['A1'] = "合并的标题"
# 取消合并
ws.unmerge_cells('A1:D1')
# 设置行高和列宽
ws.row_dimensions[1].height = 30
ws.column_dimensions['A'].width = 20
# 隐藏行列
ws.column_dimensions['B'].hidden = True
ws.row_dimensions[2].hidden = True

from openpyxl.styles import Font, Color

# 设置字体
bold_font = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000')
ws['A1'].font = bold_font

# 修改现有单元格字体
for row in ws.iter_rows(min_row=2, max_row=5):
    for cell in row:
        cell.font = Font(size=10, color=Color(rgb='0000FF'))

from openpyxl.styles import Alignment

# 设置对齐方式
center_aligned = Alignment(horizontal='center', vertical='center', wrap_text=True)
ws['A1'].alignment = center_aligned

# 应用到范围
for row in ws.iter_rows(min_row=1, max_row=10):
    for cell in row:
        cell.alignment = Alignment(horizontal='center')

from openpyxl.styles import Border, Side

# 定义边框样式
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))

# 应用边框
for row in ws.iter_rows(min_row=1, max_row=5):
    for cell in row:
        cell.border = thin_border

from openpyxl.styles import PatternFill, GradientFill

# 纯色填充
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
ws['A1'].fill = yellow_fill

# 渐变填充
gradient_fill = GradientFill(stop=('FFFFFF','0000FF'))
ws['B1'].fill = gradient_fill

from openpyxl.styles import numbers

# 设置数字格式
ws['C1'].value = 3.1415926
ws['C1'].number_format = numbers.FORMAT_NUMBER_00  # 显示两位小数

# 自定义格式
ws['D1'].value = 0.85
ws['D1'].number_format = '0.00%'  # 显示为百分比

# 写入公式
ws['E1'] = '=SUM(A1:D1)'
ws['E2'] = '=AVERAGE(A2:D2)'
ws['E3'] = '=IF(A3>B3, "A 大", "B 大")'

# 读取公式
print(ws['E1'].value)  # 输出：=SUM(A1:D1)

# 计算数据
ws['F1'] = '结果'
ws['F2'] = '=E2*100'

from openpyxl.chart import BarChart, Reference

# 准备数据
for i in range(1, 6):
    ws[f'A{i}'] = i
    ws[f'B{i}'] = i * i

# 创建柱状图
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# 添加图表到工作表
ws.add_chart(chart, "D1")

from openpyxl.drawing.image import Image

# 插入图像
img = Image('logo.png')
ws.add_image(img, 'A10')

# 调整图像大小
img.width = 100
img.height = 100

from openpyxl.worksheet.datavalidation import DataValidation

# 创建数据验证
dv = DataValidation(type="list", formula1='"男，女"', allow_blank=True)
dv.add('A1:A10')  # 应用到 A1:A10 范围
ws.add_data_validation(dv)

# 数字范围验证
dv_num = DataValidation(type="whole", operator="between", formula1=1, formula2=100)
dv_num.error = "输入必须在 1 到 100 之间"
dv_num.add('B1:B10')
ws.add_data_validation(dv_num)

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule

# 色阶条件格式
color_scale_rule = ColorScaleRule(start_type='min', start_color='FF0000', mid_type='percentile', mid_value=50, mid_color='FFFF00', end_type='max', end_color='00FF00')
ws.conditional_formatting.add('C1:C10', color_scale_rule)

# 公式条件格式
formula_rule = FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True, font=Font(color='FF0000'))
ws.conditional_formatting.add('C1:C10', formula_rule)

from openpyxl.styles import Protection

# 保护工作表
ws.protection.sheet = True
ws.protection.password = 'password'
ws.protection.enable()

# 解锁特定单元格
for row in ws.iter_rows(min_row=1, max_row=5):
    for cell in row:
        cell.protection = Protection(locked=False)

# 冻结第一行和第一列
ws.freeze_panes = 'B2'
# 解冻
ws.freeze_panes = None

pip install pandas openpyxl xlrd

import pandas as pd

# 从字典创建
data = {'姓名': ['张三', '李四', '王五'], '年龄': [25, 30, 35], '城市': ['北京', '上海', '广州']}
df = pd.DataFrame(data)

# 从列表创建
data = [{'姓名': '张三', '年龄': 25, '城市': '北京'}, {'姓名': '李四', '年龄': 30, '城市': '上海'}, {'姓名': '王五', '年龄': 35, '城市': '广州'}]
df = pd.DataFrame(data)

# 显示 DataFrame
print(df)

# 查看前几行
print(df.head(2))
# 查看后几行
print(df.tail(1))
# 查看形状
print(df.shape)  # 输出：(3, 3)
# 查看列名
print(df.columns)  # 输出：Index(['姓名', '年龄', '城市'], dtype='object')
# 查看索引
print(df.index)  # 输出：RangeIndex(start=0, stop=3, step=1)
# 查看数据类型
print(df.dtypes)

# 选择列
print(df['姓名'])
# 选择单列
print(df[['姓名', '年龄']])
# 选择多列
# 选择行
print(df.iloc[0])  # 通过位置选择
print(df.loc[0])   # 通过索引选择
print(df[1:3])     # 切片选择
# 条件选择
print(df[df['年龄'] > 28])
print(df[(df['年龄'] > 25) & (df['城市'] == '上海')])

# 添加列
df['性别'] = ['男', '女', '男']
# 修改值
df.loc[0, '年龄'] = 26
df['年龄'] = df['年龄'] + 1
# 删除列
df = df.drop('性别', axis=1)
# 删除行
df = df.drop(0, axis=0)

# 读取整个 Excel 文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# 读取指定范围
df = pd.read_excel('data.xlsx', sheet_name='Sheet1', usecols='A:C', nrows=10)
# 读取多个工作表
with pd.ExcelFile('data.xlsx') as xls:
    df1 = pd.read_excel(xls, 'Sheet1')
    df2 = pd.read_excel(xls, 'Sheet2')
# 处理缺失值
df = pd.read_excel('data.xlsx', na_values=['NA', 'N/A', '缺失'])

# 写入单个 DataFrame
df.to_excel('output.xlsx', sheet_name='数据', index=False)
# 写入多个 DataFrame
with pd.ExcelWriter('output.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')
# 追加模式写入
with pd.ExcelWriter('output.xlsx', mode='a') as writer:
    df3.to_excel(writer, sheet_name='Sheet3')
# 设置格式
df.to_excel('output.xlsx', sheet_name='数据', index=False, float_format="%.2f", freeze_panes=(1, 0))  # 冻结首行

# CSV 文件
df.to_csv('data.csv', index=False)
df = pd.read_csv('data.csv')
# JSON
df.to_json('data.json', orient='records')
df = pd.read_json('data.json')
# SQL 数据库
from sqlalchemy import create_engine
engine = create_engine('sqlite:///data.db')
df.to_sql('table_name', engine, if_exists='replace')
df = pd.read_sql('SELECT * FROM table_name', engine)

# 检测缺失值
print(df.isnull().sum())
# 删除缺失值
df_cleaned = df.dropna()  # 删除任何包含缺失值的行
df_cleaned = df.dropna(subset=['年龄'])  # 只删除年龄列有缺失的行
# 填充缺失值
df_filled = df.fillna(0)  # 用 0 填充
df_filled = df.fillna(df.mean())  # 用均值填充数值列
df_filled = df.fillna(method='ffill')  # 用前一个值填充

# 检测重复行
print(df.duplicated().sum())
# 删除重复行
df_unique = df.drop_duplicates()
# 基于某些列删除重复
df_unique = df.drop_duplicates(subset=['姓名', '城市'])

# 查看数据类型
print(df.dtypes)
# 转换数据类型
df['年龄'] = df['年龄'].astype('float64')
df['日期'] = pd.to_datetime(df['日期'])
# 分类数据
df['城市'] = df['城市'].astype('category')

# 字符串方法
df['姓名'] = df['姓名'].str.upper()  # 转为大写
df['城市'] = df['城市'].str.replace('京', '都')  # 替换
# 提取信息
df['姓氏'] = df['姓名'].str[0]  # 提取第一个字符
df['名字'] = df['姓名'].str[1:]  # 提取第二个字符之后
# 拆分列
df[['姓', '名']] = df['姓名'].str.split(expand=True)

# 解析日期
df['日期'] = pd.to_datetime(df['日期'], format='%Y-%m-%d')
# 提取日期部分
df['年'] = df['日期'].dt.year
df['月'] = df['日期'].dt.month
df['日'] = df['日期'].dt.day
df['星期'] = df['日期'].dt.day_name()
# 日期运算
df['年龄天数'] = (pd.to_datetime('today') - df['出生日期']).dt.days
df['年龄'] = df['年龄天数'] // 365

# 应用简单函数
df['年龄加 10'] = df['年龄'].apply(lambda x: x + 10)

# 应用复杂函数
def age_group(age):
    if age < 20:
        return '少年'
    elif age < 40:
        return '青年'
    else:
        return '中年'

df['年龄段'] = df['年龄'].apply(age_group)

# 向量化操作
df['BMI'] = df['体重'] / (df['身高'] / 100) ** 2

# 基本分组
grouped = df.groupby('城市')
# 聚合函数
print(grouped['年龄'].mean())  # 每个城市的平均年龄
print(grouped.agg({'年龄': ['mean', 'min', 'max'], '收入': 'sum'}))
# 多级分组
grouped = df.groupby(['城市', '性别'])
print(grouped['年龄'].mean())

# 简单透视表
pivot = pd.pivot_table(df, values='年龄', index='城市', aggfunc='mean')
# 复杂透视表
pivot = pd.pivot_table(df, values=['年龄', '收入'], index=['城市', '性别'], columns=['教育程度'], aggfunc={'年龄': 'mean', '收入': ['sum', 'count']}, fill_value=0, margins=True)

# 合并两个 DataFrame
df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'value': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['A', 'B', 'D'], 'value': [4, 5, 6]})
# 内连接
pd.merge(df1, df2, on='key', how='inner')
# 左连接
pd.merge(df1, df2, on='key', how='left')
# 外连接
pd.merge(df1, df2, on='key', how='outer')
# 纵向合并
pd.concat([df1, df2], axis=0)

import numpy as np

# 创建时间序列
date_rng = pd.date_range(start='1/1/2020', end='1/10/2020', freq='D')
df = pd.DataFrame(date_rng, columns=['date'])
df['data'] = np.random.randint(0, 100, size=(len(date_rng)))

# 设置为索引
df = df.set_index('date')
# 重采样
df.resample('W').mean()  # 按周平均
df.resample('M').sum()   # 按月求和
# 滚动窗口
df.rolling(window=3).mean()  # 3 天移动平均

# 描述性统计
print(df.describe())
# 相关性
print(df.corr())
# 协方差
print(df.cov())
# 唯一值计数
print(df['城市'].value_counts())
# 交叉表
pd.crosstab(df['城市'], df['性别'])

import matplotlib.pyplot as plt

# 线图
df.plot.line()
# 柱状图
df.plot.bar()
# 直方图
df['年龄'].plot.hist(bins=20)
# 箱线图
df.plot.box()
# 散点图
df.plot.scatter(x='年龄', y='收入')
plt.show()

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

# 创建 DataFrame
df = pd.DataFrame({'产品': ['A', 'B', 'C', 'D'], '销量': [120, 150, 90, 200], '单价': [25.5, 32.0, 18.0, 40.5]})

# 加载现有工作簿或创建新工作簿
try:
    wb = load_workbook('report.xlsx')
except FileNotFoundError:
    wb = Workbook()

# 选择或创建工作表
if '销售报告' in wb.sheetnames:
    ws = wb['销售报告']
else:
    ws = wb.create_sheet('销售报告')

# 清空现有内容
ws.delete_rows(1, ws.max_row)

# 写入 DataFrame 数据
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 添加公式
ws['E1'] = '销售额'
for row in range(2, ws.max_row + 1):
    ws[f'E{row}'] = f'=B{row}*C{row}'

# 设置格式
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# 保存工作簿
wb.save('report.xlsx')

from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook('data.xlsx')
ws = wb.active

# 将工作表数据转换为列表
data = []
for row in ws.iter_rows(values_only=True):
    data.append(row)

# 转换为 DataFrame
df = pd.DataFrame(data[1:], columns=data[0])

# 处理数据
df['日期'] = pd.to_datetime(df['日期'])
df['销售额'] = df['数量'] * df['单价']
print(df.head())

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image
from datetime import datetime

# 1. 准备数据
sales_data = {'月份': ['1 月', '2 月', '3 月', '4 月', '5 月', '6 月'], '产品 A': [1200, 1500, 1800, 2100, 2400, 2700], '产品 B': [800, 950, 1100, 1250, 1400, 1550], '产品 C': [500, 600, 700, 800, 900, 1000]}
df = pd.DataFrame(sales_data)

# 2. 创建 Excel 工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售报告"

# 3. 写入标题
ws['A1'] = "2023 年上半年销售报告"
ws['A1'].font = Font(size=16, bold=True, name='微软雅黑')
ws.merge_cells('A1:D1')

# 4. 写入数据
# 写入列标题
columns = list(df.columns)
for col_num, column_title in enumerate(columns, 1):
    cell = ws.cell(row=3, column=col_num, value=column_title)
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal='center')

# 写入数据
for row_num, row_data in enumerate(df.values, 4):
    for col_num, cell_value in enumerate(row_data, 1):
        ws.cell(row=row_num, column=col_num, value=cell_value)

# 5. 添加汇总行
last_row = ws.max_row + 1
ws.cell(row=last_row, column=1, value="总计").font = Font(bold=True)
for col_num in range(2, 5):
    col_letter = chr(64 + col_num)
    ws.cell(row=last_row, column=col_num, value=f"=SUM({col_letter}4:{col_letter}{last_row-1})")

# 6. 设置格式
# 设置边框
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
for row in ws.iter_rows(min_row=3, max_row=last_row, min_col=1, max_col=4):
    for cell in row:
        cell.border = thin_border
        if cell.row > 3 and cell.column > 1:  # 数据单元格
            cell.number_format = '#,##0'

# 设置对齐
for row in ws.iter_rows(min_row=3, max_row=last_row, min_col=1, max_col=4):
    for cell in row:
        cell.alignment = Alignment(horizontal='center')

# 7. 创建图表
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "产品销售趋势"
chart.y_axis.title = "销售额"
chart.x_axis.title = "月份"
data = Reference(ws, min_col=2, max_col=4, min_row=3, max_row=last_row-1)
categories = Reference(ws, min_col=1, min_row=4, max_row=last_row-1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "F3")

# 8. 添加页脚
footer_row = last_row + 2
ws.cell(row=footer_row, column=1, value=f"报告生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

# 9. 调整列宽
ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12

# 10. 保存文件
filename = f"销售报告_{datetime.now().strftime('%Y%m%d')}.xlsx"
wb.save(filename)
print(f"报表已生成：{filename}")

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

# 1. 加载模板文件
template_path = 'report_template.xlsx'
wb = load_workbook(template_path)
ws = wb['Data']

# 2. 准备数据
data = {'Region': ['North', 'South', 'East', 'West'], 'Q1': [1200, 1500, 1800, 900], 'Q2': [1300, 1600, 1900, 950], 'Q3': [1400, 1700, 2000, 1000], 'Q4': [1500, 1800, 2100, 1050]}
df = pd.DataFrame(data)

# 3. 清空模板中的数据区域 (保留格式)
for row in ws.iter_rows(min_row=3, max_row=100, min_col=1, max_col=6):
    for cell in row:
        cell.value = None

# 4. 写入新数据
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=False), 3):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)

# 5. 更新公式 (假设模板中已有公式)
last_data_row = 3 + len(df) - 1
for row in range(3, last_data_row + 1):
    ws[f'F{row}'] = f'=SUM(B{row}:E{row})'

# 6. 更新汇总公式
ws['B20'] = f'=SUM(B3:B{last_data_row})'
ws['C20'] = f'=SUM(C3:C{last_data_row})'
ws['D20'] = f'=SUM(D3:D{last_data_row})'
ws['E20'] = f'=SUM(E3:E{last_data_row})'
ws['F20'] = f'=SUM(F3:F{last_data_row})'

# 7. 更新报告日期
ws['A1'] = f"销售报告 - {pd.Timestamp.today().strftime('%Y-%m-%d')}"

# 8. 保存新文件
output_path = 'quarterly_report.xlsx'
wb.save(output_path)
print(f"报告已生成：{output_path}")

wb = load_workbook('large_file.xlsx', read_only=True)

wb = Workbook(write_only=True)
ws = wb.create_sheet()
for row in data:
    ws.append(row)

wb = load_workbook('file.xlsx', data_only=False)
wb.calculation = False  # ... 写入公式 ...
wb.calculation = True
wb.save('file.xlsx')

del large_df
del wb

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, numbers
from datetime import datetime

def generate_financial_report(input_path, output_path):
    # 1. 使用 pandas 读取和处理数据
    df = pd.read_excel(input_path, sheet_name='Transactions')
    # 数据清洗
    df = df.dropna(subset=['Amount'])
    df['Date'] = pd.to_datetime(df['Date'])
    df['Month'] = df['Date'].dt.to_period('M')
    
    # 分类汇总
    income = df[df['Type'] == 'Income'].groupby('Month')['Amount'].sum()
    expense = df[df['Type'] == 'Expense'].groupby('Month')['Amount'].sum()
    profit = income - expense
    
    # 2. 准备报表数据
    report_data = pd.DataFrame({
        'Month': income.index.astype(str),
        'Income': income.values,
        'Expense': expense.values,
        'Profit': profit.values
    })
    
    # 3. 加载模板文件
    wb = load_workbook('financial_report_template.xlsx')
    ws = wb['Report']
    
    # 4. 清空旧数据 (保留格式)
    for row in ws.iter_rows(min_row=5, max_row=100, min_col=1, max_col=4):
        for cell in row:
            cell.value = None
    
    # 5. 写入新数据
    for i, row in report_data.iterrows():
        ws.cell(row=5+i, column=1, value=row['Month'])
        ws.cell(row=5+i, column=2, value=row['Income'])
        ws.cell(row=5+i, column=3, value=row['Expense'])
        ws.cell(row=5+i, column=4, value=row['Profit'])
    
    # 6. 设置数字格式
    for row in ws.iter_rows(min_row=5, max_row=5+len(report_data), min_col=2, max_col=4):
        for cell in row:
            cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
    
    # 7. 更新汇总信息
    last_row = 5 + len(report_data) - 1
    ws['B1'] = f"财务报告 - {datetime.now().strftime('%Y-%m-%d')}"
    ws['B2'] = f"数据期间：{report_data['Month'].iloc[0]} 至 {report_data['Month'].iloc[-1]}"
    ws[f'B{last_row+2}'] = "总计:"
    ws[f'C{last_row+2}'] = f"=SUM(C5:C{last_row})"
    ws[f'D{last_row+2}'] = f"=SUM(D5:D{last_row})"
    ws[f'E{last_row+2}'] = f"=SUM(E5:E{last_row})"
    
    # 8. 设置汇总行格式
    for cell in ws[f'B{last_row+2}':f'E{last_row+2}'][0]:
        cell.font = Font(bold=True)
        if cell.column in [3, 4, 5]:
            cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
    
    # 9. 保存报告
    wb.save(output_path)
    print(f"财务报表已生成：{output_path}")

# 使用示例
generate_financial_report('transactions.xlsx', 'financial_report_Q1.xlsx')

Python 使用 openpyxl 和 pandas 处理 Excel 详解

一、Excel 处理在数据分析中的重要性

二、openpyxl 基础与核心功能

2.1 openpyxl 简介与安装

2.2 工作簿与工作表的基本操作

创建新工作簿

打开已有工作簿

工作表操作

2.3 单元格操作详解

基本单元格操作

批量操作单元格

特殊单元格操作

2.4 样式与格式设置

字体样式

对齐方式

边框设置

填充颜色

数字格式

2.5 公式与计算

2.6 图表与图像操作

创建图表

插入图像

2.7 高级功能

数据验证

条件格式

保护工作表

冻结窗格

三、pandas 基础与核心功能

3.1 pandas 简介与安装

3.2 DataFrame 基础

创建 DataFrame

基本属性

数据选择

数据修改

3.3 数据导入与导出

读取 Excel 文件

写入 Excel 文件

其他格式支持

3.4 数据清洗与预处理

处理缺失值

处理重复值

数据类型转换

字符串操作

日期处理

3.5 数据转换与计算

应用函数

分组聚合

数据透视表

合并与连接

3.6 高级数据分析功能

时间序列分析

统计函数

可视化

四、openpyxl 与 pandas 的协同使用

4.1 结合使用的优势与场景

4.2 pandas DataFrame 与 openpyxl 的转换

DataFrame 写入 Excel 并保持格式

从 openpyxl 读取数据到 DataFrame

4.3 复杂报表生成案例

4.4 模板填充技术

4.5 性能优化技巧

五、实战应用案例

5.1 财务报表自动化

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具