openpyxl可以通过编写Python脚本实现自动化Excel操作,包括自动填充数据、格式化单元格、生成图表等操作。
以下是一个常见的自动化Excel操作示例:
自动填充数据:
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
# 创建新的Excel工作簿
wb = Workbook()
# 选择活动工作表
ws = wb.active
# 填充表头数据
for col_num in range(1, 10):
col_letter = get_column_letter(col_num)
ws["{}1".format(col_letter)] = "Column {}".format(col_letter)
ws["{}1".format(col_letter)].font = Font(bold=True)
# 填充数据
for row_num in range(2, 20):
for col_num in range(1, 10):
col_letter = get_column_letter(col_num)
ws["{}{}".format(col_letter, row_num)] = "{}{}".format(col_letter, row_num)
# 保存Excel文件
wb.save("example.xlsx")