继续浏览精彩内容
慕课网APP
程序员的梦工厂
打开
继续
感谢您的支持,我会继续努力的
赞赏金额会直接到老师账户
将二维码发送给自己后长按识别
微信支付
支付宝支付

Python处理Excel表格——基于强大又好玩的Openpyxl库

Coder_zheng
关注TA
已关注
手记 71
粉丝 23
获赞 45

首先看一下官网例程:
图片描述
运行结果:
图片描述

为了理解上面的代码,请往下看,😎

一.创建一个工作簿

1.引入Workbook类,然后进行实例化:
from openpyxl import Workbook
wb = Workbook()
2.一个工作簿(workbook)在创建的时候同时至少也新建了一张工作表(worksheet)。
Workbook.active()调用得到正在运行的工作表
ws = wb.active
该函数调用工作表的索引(_active_sheet_index),默认是0。
除非修改了这个值,否则使用该函数一直是在对第一张工作表进行操作,
而第一张表的默认表名是"Sheet"
可以通过:ws.title = Sheet_name 修改默认的表名。

3.使用Workbook.create_sheet()新建一张表
ws1 = wb.create_sheet() --> 默认插在工作簿末尾
ws2 = wb.create_sheet(0) --> 插入在工作簿的第一个位置

4.在创建工作表的时候系统自动命名。他们按照序列依次命名 (Sheet, Sheet1, Sheet2, …)

5.一旦获取工作表的名字,可以通过workbook的key或者Workbook.get_sheet_by_name()方法得到该工作表
ws3 = wb[“New Title”]
ws3 = wb.get_sheet_by_name(“New Title”)

也可以通过Workbook.get_sheet_names()方法得到工作簿的所有工作表。

print(wb.get_sheet_names())
[‘Sheet2’, ‘New Title’, ‘Sheet1’]

也可以for循环得到所有的工作表:
for sheet in wb:
print(sheet.title)

二,对单元格的基本操作

1.单元格可以直接根据他们的索引直接获得: c = ws[‘A4’]
通过上述的语句,将返回在A4处的单元格,如果不存在将在A4新建一个

2.单元格的值也可以直接赋值:ws[‘A4’] = 4

3.使用Worksheet.cell()方法获取单元格: c = ws.cell(‘A4’)

4.根据行列值获取单元格:d = ws.cell(row = 4, column = 2)

5.使用切片获取多个单元格:cell_range = ws[‘A1’:‘C2’]

6.使用Worksheet.iter_rows()方法获得多个单元格

7.将工作表转换为Dataframe,如果工作表没有标题或索引:df = DataFrame(ws.values)

三,保存和导入文件

1.保存文件 : Workbook.save()
2.导入文件 : openpyxl.load_workbook()

四,应用实例

Demo1:

from openpyxl import Workbook
wb = Workbook()
ws1 = wb.active
ws2 = wb.create_sheet()
ws3 = wb.create_sheet(0)

print(wb.get_sheet_names())

图片描述

Demo2:

from openpyxl import Workbook
wb = Workbook()

ws1 = wb.active
ws1.title = "New Title"
ws2 = wb["New Title"]
ws3 = wb.get_sheet_by_name("New Title")

print(ws1 is ws2 is ws3)
for sheet in wb:
	print(sheet.title)

图片描述

Demo3:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

for i in range(1,3):
	for j in range(1,3):
		ws.cell(row = i,column = j)

for row in ws:
	for cell in row:
		print(cell)

图片描述

如果按常规,行和列索引从0开始,而不是1

图片描述

报错辽!
图片描述

Demo4.比较两种访问方式:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

for i in range(1,3):
	for j in range(1,3):
		ws.cell(row = i,column = j)

#逐行遍历
for row in ws.iter_rows(min_row = 1,max_col = 3,max_row = 3):
	for cell in row:
		print(cell)

图片描述

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

for i in range(1,3):
	for j in range(1,3):
		ws.cell(row = i,column = j)

#逐列遍历
for col in ws.iter_cols(min_row = 1,max_col = 3,max_row = 3):
	for cell in col:
		print(cell)

图片描述

Demo5.增加筛选条件

from openpyxl import Workbook

wb = Workbook()
ws = wb.active 

data = [
	["Fruit","Quantity"],
	["Kiwi",3],
	["Grape",15],
	["Apple",3],
	["Peach",3],
	["Pomegranate",3],
	["Pear",3],
	["Tangerine",3],
	["Blueberry",3],
	["Mango",3],
	["Watermelon",3],
	["Blackberry",3],
	["Orange",3],
	["Raspberry",3],
	["Banana",3]
]

for r in data:
	ws.append(r)

wb.save("unfiltered.xlsx")

图片描述

from openpyxl import Workbook

wb = Workbook()
ws = wb.active 

data = [
	["Fruit","Quantity"],
	["Kiwi",3],
	["Grape",15],
	["Apple",3],
	["Peach",3],
	["Pomegranate",3],
	["Pear",3],
	["Tangerine",3],
	["Blueberry",3],
	["Mango",3],
	["Watermelon",3],
	["Blackberry",3],
	["Orange",3],
	["Raspberry",3],
	["Banana",3]
]

for r in data:
	ws.append(r)

ws.auto_filter.ref = "A1:B15"
ws.auto_filter.add_filter_column(0,["Kiwi","Apple","Mango"])
ws.auto_filter.add_sort_condition("B2:B15")

wb.save("filtered.xlsx")

图片描述

图片描述

Demo6.折叠功能

import openpyxl
wb = openpyxl.Workbook()

ws = wb.create_sheet()
ws.column_dimensions.group('A','D',hidden = True)
ws.row_dimensions.group(1,10,hidden = True)
wb.save('fold.xlsx')

折叠前:
图片描述
折叠后:
图片描述

Demo7:

from openpyxl import Workbook
from openpyxl.worksheet.table import Table,TableStyleInfo 

wb = Workbook()
ws = wb.active

data = [
	['Apples',10000,5000,8000,6000],
	['Pears',  2000,3000,4000,5000],
	['Bananas',6000,6000,6500,6000],
	['Oranges', 500, 300, 200, 700],
]

#add column headings.NB.these must be strings
ws.append(['Fruit','2011','2012','2013','2014'])

for row in data:
	ws.append(row)

tab = Table(displayName = "Table1",ref = "A1:E5")

# Add a default style with striped rows and banded columns
style = TableStyleInfo(name = "TableStyleMedium9",showFirstColumn = False,
	                  showLastColumn = False,showRowStripes = True,showColumnStripes = True)

tab.tableStyleInfo = style
ws.add_table(tab)
wb.save("table.xlsx")

图片描述

Demo8:


from openpyxl import Workbook

wb = Workbook()

dest_filename = "empty_book.xlsx"

ws1 = wb.active
ws1.title = "range names"
for row in range(1,6):
	ws1.append(range(6))

ws2 = wb.create_sheet(title = "Pi")
ws2['C5'] = 3.14

ws3 = wb.create_sheet(title = "Data")
for row in range(5,10):
	for col in range(5,10):
		_ = ws3.cell(column = col,row = row,value = "[%s,%s]"%(row,col))


wb.save(filename = dest_filename)

图片描述

图片描述

图片描述

参考教程:

打开App,阅读手记
0人推荐
发表评论
随时随地看视频慕课网APP

热门评论

#补充一个实例,openpyxl与pandas的交互


import numpy as np 

import pandas as pd 

from openpyxl import Workbook

from openpyxl.utils.dataframe import dataframe_to_rows  


wb = Workbook()

ws = wb.active


df = pd.DataFrame({

'A':['A1','A2','A3','A4'],

'B':['B1','B2','B3','B4'],

'C':['C1','C2','C3','C4'],

'D':['D1','D2','D3','D4']

     })



for r in dataframe_to_rows(df,index = True,header = True):

ws.append(r)


wb.save("pandas_openpyxl.xlsx")


查看全部评论