python word excel ppt自动化办公教程

模块导入

import openpyxl

读取Excel文件

打开Excel文件

workbook = openpyxl.load_workbook('test.xlsx')

输出表单名字

# 输出工作簿中所有表单名字

print(workbook.sheetnames)

# 遍历所有表单并输出其名字

for sheet in workbook:

print(sheet.title)

创建表单

newSheet = workbook.create_sheet('newSheetName')

获取表单对象

# 根据表单名获取表单

sheet3 = workbook.get_sheet_by_name('Sheet3')

sheet4 = workbook['newSheetName']

# 获取当前活跃的表单

worksheet = workbook.active

获取当前表单数据行列数

# 获取当前表单数据行数

row_count = worksheet.max_row

# 获取当前表单数据列数

row_count = worksheet.max_column

获取单元格对象

selectcell = worksheet['A1']

selectcell = worksheet.cell(row=1, column=2) # 行列号从1开始

输出单元格信息

单元格所在的行、列

print(selectcell.row, selectcell.column)

单元格的坐标

print(selectcell.coordinate)

单元格的值

print(selectcell.value)

获取列对象

selectcol = worksheet['C']

获取列对象中某一单元格

selectcell = selectcol['2']

获取连续多列组成的对象

selectcols = worksheet['B:C']

获取所有列组成的对象

allcol = worksheet.cols

获取行对象

selectrow = worksheet['2']

获取行对象中某一单元格

selectcell = selectcol['C']

获取连续多行组成的对象

selectcols = worksheet['1:4']

获取所有行组成的对象

allrow = worksheet.rows

获取一定范围内的单元格组成的对象

cell_range = worksheet['A1:C3']

遍历一定范围内的单元格

for row_range in cell_range: # 先行再列

for cell in row_range:

print(cell.value)

行的字母表示与数字换算

from openpyxl.utils import get_column_letter, column_index_from_string

print(get_column_letter(2))) # 2 => B

print(column_index_from_string('C')) # C => 3

编辑Excel文件

创建workbook对象

workbook = openpyxl.Workbook()

创建表单

workbook.create_sheet(index=1, title='第二张表')

删除表单

workbook.remove_sheet(workbook.get_sheet_by_name('Sheet3'))

获取当前活跃的表单

sheet = workbook.active

修改表单名称

sheet.title = '设置的表单名称'

修改单元格的值

sheet['A1'] = 'New Value'

批量写入数据

方法一

import openpyxl

from openpyxl.utils import get_column_letter, column_index_from_string

workbook = openpyxl.Workbook()

ws1 = workbook.create_sheet('第一页')

for row in range(40):

ws1.append(range(17))

方法二

import openpyxl

from openpyxl.utils import get_column_letter, column_index_from_string

workbook = openpyxl.Workbook()

ws2 = workbook.create_sheet('第二页')

rows = [

['Number', 'Batch1', 'Batch2'],

[2,40,30],[3,50,25],

[4,30,30],[5,60,10]

]

for row in rows:

ws2.append(row)

方法三

import openpyxl

from openpyxl.utils import get_column_letter, column_index_from_string

workbook = openpyxl.Workbook()

ws3 = workbook.create_sheet('第三页')

for row in range(5,30):

for col in range(15,24):

ws3.cell(column=col, row=row, value=123)

修改完毕保存到文件

workbook.save('created.xlsx')

> 想进一步了解编程开发相关知识，与我一同成长进步，请关注我的公众号“松果仓库”，共同分享宅&程序员的各类资源，谢谢！！！