python处理表格很厉害么_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)

参考教程：