Python openpyxl常用操作

1、加载EXCEL文件：

import openpyxl
from openpyxl import load_workbook

filename = r'D:\test.xlsx'
wb = load_workbook(filename)

ws = wb['Sheet1']

2、修改单元格的值

ws['A1'] = 'test'
wb.save(filename)

操作实例：

# 将A列单元格的值复制到B列

import openpyxl
from openpyxl import load_workbook

filename = r'D:\test.xlsx'
wb = load_workbook(filename)
ws = wb['Sheet1']
source_column = 'A'
target_column = 'B'
for i in range(1,ws.max_row + 1):
    ws[f'{target_column}{i}'] = ws[f'{source_column}{i}'].value

wb.save(filename)

# 将文件A.xlsx的某一区域单元格复制到文件B.xlsx

import openpyxl
from openpyxl import load_workbook

filename_source = r'D:\A.xlsx'
wb_source = load_workbook(filename_source)
ws_source = wb_source['Sheet1']

filename_target = r'D:\B.xlsx'
wb_target = load_workbook(filename_target)
ws_target = wb_target['Sheet1']

for row in ws_source.iter_rows(min_row=1, max_row=ws_source.max_row+1, min_col=1, max_col=10):
    for cell in row:
        ws_target.cell(row=cell.row, column=cell.column).value = cell.value

wb_target.save(filename_target)

3、删除整列

delete_column = 14
ws.delete_cols(delete_column) # delete_cols函数只支持按列的索引号删除，索引号从1开始

4、插入公式

import openpyxl
from openpyxl import load_workbook

filename = r'D:\test.xlsx'
wb = load_workbook(filename)
ws = wb['Sheet1']
ws['A1'] = '=B1+C1'

wb.save(filename)

5、注意事项：

在 Python 中使用 openpyxl 库处理 Excel 文件时，如果你从一个单元格中获取到的内容是公式而不是计算结果，这通常是因为 openpyxl 不直接计算公式。这时可以使用 openpyxl 的加载工作簿时的 data_only 参数来尝试获取已经计算好的值（如果文件是由 Excel 保存的，并且包含了这些计算后的值）

workbook = openpyxl.load_workbook('file.xlsx', data_only=True)