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

首先看一下官网例程：

运行结果：

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

一.创建一个工作簿

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)

参考教程：

https://www.jianshu.com/p/642456aa93e2
https://www.jianshu.com/p/50b075c5f5e7
https://openpyxl.readthedocs.io/en/stable/