python向Excel表格中写入内容（三）

2023-04-20 20:55| 来源: 网络整理| 查看: 265

参考文档： python——向Excel读取或写入数据

1、写入内容的最基本方法 from openpyxl import load_workbook workbook = load_workbook(filename='./2012.xlsx') print(workbook.sheetnames) sheet = workbook.active cell = sheet['A3'] cell.value = "你好啊" workbook.save(filename="./2012.xlsx") 2、用python列表数据插入一行

sheet.append(python列表)-----会在表格中插入这些列表数据

from openpyxl import load_workbook workbook = load_workbook(filename='./2012.xlsx') data = [ ["小样", 23], ["叼毛", 45] ] sheet = workbook.active for data_value in data: sheet.append(data_value) workbook.save(filename="./2012.xlsx") 3、插入公式----直接赋值公式字符串 from openpyxl import load_workbook workbook = load_workbook(filename='./2012.xlsx') sheet = workbook.active sheet['B8'] = '=AVERAGE(B1:B7)' workbook.save(filename='./2012.xlsx')

查看openpyxl支持的公式

from openpyxl.utils import FORMULAE print(FORMULAE) 4、用python插入一列数据

.insert_cols(idx=数字编号,amount=要插入的列数（几列）)--------在idx列的左边插入一列

from openpyxl import load_workbook workbook = load_workbook(filename='./2012.xlsx') sheet = workbook.active sheet.insert_cols(idx=2) workbook.save(filename='./2012.xlsx') 5、用python插入一行数据—（用法同4）

.insert_rows(idx=数字编号,amount=要插入的行数（几行）)--------在idx行的上边插入一列

6、用python删除列

.delete_cols(idx=数字编号,amount=要删除的列数（几列）)----从idx这一列开始，包括idx这一列

from openpyxl import load_workbook workbook = load_workbook(filename='./2012.xlsx') sheet = workbook.active sheet.delete_cols(idx=2) workbook.save(filename='./2012.xlsx') 6、用python删除行（同5操作）

.delete_rows(idx=数字编号,amount=要删除的行数（几行）)----从idx这一行开始，包括idx这一行

7、移动格子

.move_range(“C1:D4”, rows=2,cols=-2)-----正整数为向下或向右、负整数向左或向上(C1:D4是一组格子)

8、创建新的sheet

workbook.create_sheet(sheet名称)

from openpyxl import load_workbook workbook = load_workbook(filename='./2012.xlsx') sheet = workbook.active workbook.create_sheet("你好吗？") workbook.save(filename='./2012.xlsx') print(workbook.sheetnames) 9、删除一个sheet

workbook.remove(sheet实例)

from openpyxl import load_workbook workbook = load_workbook(filename='./2012.xlsx') sheet = workbook.active sheet1 =workbook["你好吗？"] workbook.remove(sheet1) workbook.save(filename='./2012.xlsx') print(workbook.sheetnames) 10、复制一个sheet

workbook.copy_worksheet(sheet实例)

from openpyxl import load_workbook workbook = load_workbook(filename='./2012.xlsx') sheet = workbook.active sheet1 =workbook["表格1"] workbook.copy_worksheet(sheet1) workbook.save(filename='./2012.xlsx') print(workbook.sheetnames) 11、修改sheet名称

sheet.title='想修改成的名字’

12、创建新的Excel表格文件 from openpyxl import load_workbook,Workbook workbook = Workbook() sheet = workbook.active sheet.title = "测试表格" workbook.save(filename="new.xlsx") 13、冻结窗格

sheet.freeze_panes = “G2”—就不会移动和修改

14、添加筛选

sheet.auto_filter.ref = sheet.dimensions

from openpyxl import load_workbook,Workbook workbook = load_workbook(filename='./2012.xlsx') sheet = workbook.active data_size = sheet.dimensions sheet.auto_filter.ref = data_size workbook.save(filename='./2012.xlsx')

【本文地址】

公司简介

联系我们