如何编写Excel写入脚本

wen 实用脚本 2026-07-01 21

如何编写Excel写入脚本：从零到精通的完整指南

📖 目录导读

什么是Excel写入脚本？ —— 基础概念与适用场景
常见实现语言对比 —— Python vs VBA vs PowerShell
Python openpyxl库实战 —— 从安装到写入10行代码
数据清洗与格式化技巧 —— 单元格样式、合并与公式
批量写入与性能优化 —— 处理10万行数据的秘诀
常见错误与解决方案 —— 索引越界、内存溢出、编码问题
问答环节 —— 新手最常犯的5个错误及修复

什么是Excel写入脚本？

Excel写入脚本是指通过编程语言自动向Excel文件（.xlsx/.xls）中添加、修改或格式化数据的过程，它解决了手动复制粘贴效率低、易出错的痛点,尤其适合以下场景：

如何编写Excel写入脚本

每日报表自动生成（如销售数据汇总）
数据库导出为Excel
批量填充模板（如工资单、成绩单）
动态图表数据源更新

❓ 问：为什么不直接用Excel公式或宏？
答：公式适合简单计算，但无法处理复杂逻辑（如从API获取数据），宏（VBA）依赖Excel环境，而脚本（如Python）更灵活,可跨平台运行。

常见实现语言对比

语言/工具	适用场景	学习成本	性能
Python + openpyxl	数据分析、复杂逻辑	低	中
Python + pandas	大数据处理	中	高
VBA (Excel宏)	快速录入、简单循环	中	低
PowerShell	Windows环境自动化	高	中
Java + Apache POI	企业级应用	高	高

强烈推荐：Python + openpyxl（入门简单，社区活跃，支持.xlsx格式）。

Python openpyxl库实战（核心干货）

步骤1：安装库

pip install openpyxl

若遇到网络问题,使用镜像源：

pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

步骤2：创建并写入数据

import openpyxl
# 创建工作簿
wb = openpyxl.Workbook()
ws = wb.active= "销量报表"
# 写入表头
headers = ["商品名称", "销量", "单价", "总金额"]
ws.append(headers)
# 写入多行数据
data = [
    ["iPhone 15", 120, 6999, 120*6999],
    ["MacBook Air", 45, 9499, 45*9499],
    ["AirPods Pro", 200, 1999, 200*1999],
]
for row in data:
    ws.append(row)
# 保存文件
wb.save("demo.xlsx")
print("Excel文件已生成！")

❓ 问：append()与索引写入的区别？
答：append()自动从第一列开始写入下一行，适合顺序数据；而ws.cell(row=1, column=1, value="标题")可精确控制位置。

数据清洗与格式化技巧

1 设置单元格样式

from openpyxl.styles import Font, PatternFill, Alignment
# 设置表头格式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
ws.cell(row=1, column=1).font = header_font
ws.cell(row=1, column=1).fill = header_fill
ws.cell(row=1, column=1).alignment = Alignment(horizontal="center")

2 合并单元格

ws.merge_cells('A1:B2')  # 合并A1到B2
ws.cell(1,1).value = "综合标题"

3 插入公式

ws.cell(row=2, column=4).value = "=B2*C2"  # 自动计算总金额

❓ 问：如何处理日期格式？
答：写入时直接传datetime对象，
ws.cell(1,1).value = datetime.now()
然后设置列格式：ws.cell(1,1).number_format = 'YYYY-MM-DD'

批量写入与性能优化

当数据量超过 1万行 时，逐行append()会变慢，推荐使用批量写入：

import openpyxl
from openpyxl.worksheet import Worksheet
def batch_write_data(ws: Worksheet, data_list: list, chunk_size=5000):
    """分批写入，防止内存溢出"""
    for i in range(0, len(data_list), chunk_size):
        chunk = data_list[i:i+chunk_size]
        for row in chunk:
            ws.append(row)
# 生成10万条模拟数据
big_data = [["商品{}".format(i), i] for i in range(1, 100001)]
batch_write_data(ws, big_data)
wb.save("big_sales.xlsx")

高级优化技巧：

使用ws.cell()代替ws.append()可提升10%速度
关闭自动计算：wb.calculation.properties.calcMode = "manual"
写入完成后手动设置列宽：ws.column_dimensions['A'].width = 20

常见错误与解决方案

错误现象	原因	修复方法
`PermissionError`	文件被Excel打开锁定	关闭Excel文件，或使用不同文件名
`TypeError: cannot concatenate`	数字与字符串混合	确保`value`参数类型一致，如`str(value)`
`MemoryError`	写入20万行以上数据	改用CSV格式或分批写入
中文乱码	文件编码问题	保存时指定编码：`wb.save("demo.xlsx", encoding="utf-8")`

❓ 问：如何读取已有Excel并追加写入？
答：先将文件覆盖写入,数据追加在后面。

问答环节：新手最容易犯的5个错误

Q1：为什么我的数据写入后Excel打开是空白的？
A：检查是否调用了wb.save()，且路径无特殊字符，例如wb.save("C:/Users/用户名/文档/报表.xlsx")。

Q2：如何写入图片到Excel？
A：从openpyxl导入Image：

from openpyxl.drawing.image import Image
img = Image("logo.png")
ws.add_image(img, "E1")  # 放置在E1单元格

Q3：可以设置单元格保护（拒绝修改）吗？
A：可以，但需先解锁整个工作表：

ws.protection.sheet = False  # 允许修改
ws.cell(1,1).protection = Protection(locked=True)  # 锁住特定单元格

Q4：Python脚本如何定时自动运行？
A：Windows可用任务计划程序，macOS/Linux用crontab，直接调用python your_script.py。

Q5：Excel公式出现#REF!错误如何避免？
A：在写入公式前确保引用的单元格已存在，例如公式=SUM(A:A)需确保A列有数据。

通过本指南，你已掌握Excel写入脚本的核心技法：从环境搭建到性能优化，再到错误排查，当你能够流畅地用Python在几分钟内生成原本需要人工2小时的报表时，你一定会感慨自动化脚本的神奇力量，下次面对堆积如山的Excel任务，不妨打开终端,用几行代码解放双手吧！