python操作Exce文件
修改表格内容
向某个格子写入数据并保存
sheet[‘A1’] = ‘你好啊’
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
sheet['A1'] = '你好呀'
workbook.save(filename='这是一个表格.xlsx')
向某个cell实例写入内容并保存
cell.value = ‘你好呀’
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
cell = sheet['A1']
cell.value = '你好呀hello'
workbook.save(filename='这是一个表格.xlsx')
用Python列表插入行数据
sheet.append(Python列表) 插入的数据会接在表格内已有数据后面
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
data = [
['张三',1],
['李四',2],
['王五',3],
['赵六',4],
]
for row in data:
sheet.append(row)
workbook.save(filename='这是一个表格.xlsx')
**```
插入公式
直接赋值公式字符串**
```python
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
sheet['B17'] = '=AVERAGE(B13:B16)'
workbook.save(filename='这是一个表格.xlsx')
查看openpyxl支持的公式
from openpyxl.utils import FORMULAE
print(FORMULAE)
插入多列
.insert_cols(idx = 数字编号,amount = 要插入的列数) 在idx数字编号的列左边插入几列
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
sheet.insert_cols(idx = 2,amount =3)
workbook.save(filename='这是一个表格.xlsx')
插入多行
.insert_rows(idx = 数字编号,amount = 要插入的列数) 在idx数字编号的行上边插入几行
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
sheet.insert_rows(idx = 2,amount =3)
workbook.save(filename='这是一个表格.xlsx')
删除列
.delete_cols(idx = 数字编号,amount = 要插入的列数) 从idx这一行开始,包括idx这一行
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
sheet.delete_rows(idx = 2,amount =3)
workbook.save(filename='这是一个表格.xlsx')
删除行
.delete_rows(idx = 数字编号,amount = 要插入的列数) 从idx这一列开始,包括idx这一列
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
sheet.delete_cols(idx = 2,amount =3)
workbook.save(filename='这是一个表格.xlsx')
移动格子
.move_range(‘C1:D4’,rows=2,cols=-2) 正整数 为向下或者向右,负整数为向左或向上
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
sheet = workbook['Sheet1']
sheet.move_range('C1:D4', rows =2, cols=-2)
workbook.save(filename='这是一个表格.xlsx')
创建新的sheet
workbook.create_sheet(sheet名称)
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
print(workbook.sheetnames)
workbook.create_sheet('表格2')
print(workbook.sheetnames)
workbook.save(filename='这是一个表格.xlsx')
删除一个sheet
workbook.remove(sheet实例)
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
print(workbook.sheetnames)
sheet = workbook['表格2'] #sheet实例
workbook.remove(sheet)
print(workbook.sheetnames)
workbook.save(filename='这是一个表格.xlsx')
复制一个sheet
workbook.copy_worksheet(sheet实例)
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename= '这是一个表格.xlsx')
print(workbook.sheetnames)
sheet = workbook['Sheet1'] #sheet实例
workbook.copy_worksheet(sheet)
print(workbook.sheetnames)
workbook.save(filename='这是一个表格.xlsx')
创建新的Excel表格文件
sheet.title 修改表格的名称
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = '表格1'
workbook.save(filename='这是一个新表格.xlsx')
特别注意,这里从openpyxl模块引入的不再是load_workbook方法,而是Workbook方法,上述代码运行后会创建一个名为’这是一个新表格.xlsx’的Excel文件
冻结窗格
sheet.freeze_panes = ‘G2’
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename = '这是一个表格.xlsx')
sheet = workbook.active
sheet.freeze_panes = 'G2'
workbook.save(filename='这是一个表格.xlsx')
冻结的结果是,在这个窗格的左上都是不动的,当移动滑块时,变化的只有窗格的右下方数据。也就是当运行此程序代码后,Excel表格里面会在G2窗格(左上方)处出现’十字’坐标线,在第二象限的数据没有办法移动,改变的只有其它象限的数据
添加筛选
sheet.auto_filter.ref = sheet.dimensions
import os
os.chdir('C:\\Users\\Administrator\\Desktop\\test')
from openpyxl import load_workbook
workbook = load_workbook(filename = '这是一个表格.xlsx')
sheet = workbook.active
sheet.auto_filter.ref = sheet.dimensions
workbook.save(filename='这是一个表格.xlsx')