python操作csv文件第7行开始的数据_[Python办公自动化（第6阶段）]7。Python操作Excel文件、插入数据、公式、插入和删除行和列、冻结窗格和复制表单,python,期,7pyth...

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')