更新笔记

Openpyxl的旧版和新版用法相差很多，已更新用法和思路至最新版。

excel是我们日常生活与职场中用的最多的数据处理和分析软件，而openpyxl模块让 Python 程序读取和修改 Excel 电子表格文
件变得简单，也免去了学习VBA的麻烦。

我们先安装一下这个模块：

1
pip install openpyxl
1
2
3
--snip--
Installing collected packages: jdcal, et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.0.1 jdcal-1.4.1 openpyxl-3.0.4

先演示一个简单的demo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
from openpyxl import Workbook
wb = Workbook()

# grab the active worksheet
ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = 42

# Rows can also be appended
ws.append([1, 2, 3])

# Python types will automatically be converted
import datetime
ws['A2'] = datetime.datetime.now()

# Save the file
wb.save("sample.xlsx")

这时你打开这个xlsx的sample.xlsx，大致是这丫：

A B C
1 42
2 2020-03-17 19:30:37 2 3

很简单吧！~

读取与操作表格EXCEL

我们对刚才生成的表格进行一通操作，在你的EXCEL程序里新增3个表，然后关闭。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import openpyxl 
wb = openpyxl.load_workbook('sample.xlsx') ## 读取工作表
print(type(wb))
# <class 'openpyxl.workbook.workbook.Workbook'> 
wb.sheetnames
# ['Sheet', 'Sheet1', 'Sheet2', 'Sheet3']
sheet = wb['Sheet3'] 
sheet
# <Worksheet "Sheet3">
print(type(sheet))
# <class 'openpyxl.worksheet.worksheet.Worksheet'>
sheet.title 
# 'Sheet3'
anotherSheet = wb.active  ## 当前活动表
anotherSheet
# <Worksheet "Sheet">

然后我们来进行一下单元格的操作吧。

1
2
3
4
5
6
7
8
9
10
11
12
sheet = wb['Sheet'] 
sheet['A1'] #== sheet.cell(row=1, column=1) 
# <Cell 'Sheet'.A1>
sheet['A1'].value
# 42
sheet['A2'].value 
# datetime.datetime(2020, 3, 17, 19, 30, 37)
B2 = sheet['B2']
print(B2.coordinate + "(" + str(B2.row) + "," + str(B2.column) + ")")
# B2(2,2)
[s.value for s in list(sheet.columns)[1]] ## 注意新版的生成器用法
# [None, 2]

获取当前表格的行列数

1
2
3
4
sheet.max_row
# 2
sheet.max_column
# 3

遍历表格

1
2
3
4
5
6
7
8
tuple(sheet['A1':'C3']) 
# ((<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>),
# (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>),
# (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>))
for row in sheet['A1':'C3']: 
    for cell in row: 
        print(cell.coordinate, str(cell.value).ljust(20, ' '), end =" ") 
    print("")
1
2
3
A1 42                   B1 None                 C1 None                 
A2 2020-03-17 19:30:37  B2 2                    C2 3                    
A3 None                 B3 None                 C3 None

字母和数字间的转换

1
2
3
4
5
6
7
8
9
10
import openpyxl 
from openpyxl.utils import get_column_letter, column_index_from_string 
get_column_letter(1) 
# 'A'
get_column_letter(27) 
# 'AA'
get_column_letter(900) 
# 'AHP'
column_index_from_string('AHP') 
# 900

写入 Excel 文档

我们已经在本文开头学会了关于创建与保存 Excel 文档 的简单操作，那我们再来看一下创建和删除工作表的用法：

1
2
3
4
5
6
7
8
9
10
import openpyxl 
wb = openpyxl.load_workbook('sample.xlsx') ## 读取工作表
wb.create_sheet() 
# <Worksheet "Sheet4">
wb.create_sheet(index=0, title='Sheet0') 
wb.create_sheet(index=2, title='Sheet0.75')
wb.sheetnames
# ['Sheet0', 'Sheet', 'Sheet1.5', 'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4']
del wb['Sheet0.75']
del wb['Sheet0']

再来设置一下字体吧， 注：openpyxl 的默认值 Calibri。

1
2
3
4
5
from openpyxl.styles import Font
sheet = wb['Sheet'] 
fontObj1 = Font(name='Times New Roman',size = 24, bold = True)
sheet['A1'].font = fontObj1
sheet['A1'] = 'Hello world!'

我们还可以调用EXCEL的公式，比如：

1
2
3
4
sheet['D2'] = '=SUM(B2:C2)' 
print(sheet['D2'].value)
# =SUM(B2:C2)
wb.save("sample.xlsx") ## 保存工作表

此时表中内容如下：

A B C D
1

Hello world!

2 2020-03-17 19:30:37 2 3 5

调整行和列

在 Excel 中，调整行和列的大小非常容易，而在python中，我们可用 row_dimensions 和 column_dimensions来设置单元格的宽度和高度。

行的高度可以设置为 0 到 409 之间的整数或浮点值。这个值表示高度的点数。一点等于 1/72 英寸。默认的行高是 12.75。

列宽可以设置为 0到 255 之间的整数或浮点数。这个值表示使用默认字体大小时（11 点），单元格可以显示的字符数。

默认的列宽是 8.43 个字符。列宽为零或行高为零，将使单元格隐藏。

1
2
3
4
5
6
7
8
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 'Tall row'
sheet['B2'] = 'Wide column'
sheet.row_dimensions[1].height = 70
sheet.column_dimensions['B'].width = 20
wb.save('dimensions.xlsx')

merge_cells()和unmerge_cells()工工作表方法可以让我们合并和拆分单元格 ，

1
2
3
4
5
6
7
8
9
10
11
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active

sheet.merge_cells('A1:D3')
sheet['A1'] = 'Twelve cells merged together.'
sheet.merge_cells('C5:D5')
sheet['C5'] = 'Two merged cells.'
sheet.unmerge_cells('A1:D3')
sheet.unmerge_cells('C5:D5')
wb.save('merged_example.xlsx')

对于表格内容太大的，我们可以使用冻结窗格(Freeze Panes),

freeze_panes 的设置 冻结的行和列
sheet.freeze_panes = ‘A2’ 行 1
sheet.freeze_panes = ‘B1’ 列 A
sheet.freeze_panes = ‘C1’ 列 A 和列 B
sheet.freeze_panes = ‘C2’ 行 1 和列 A 和列 B
sheet.freeze_panes = 'A1’或sheet.freeze_panes = None 没有冻结窗格

图表

openpyxl也支持创建EXCEL图表，比如条形图、折线图、散点图和饼图等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import openpyxl 
wb = openpyxl.Workbook() 
sheet = wb.active
for i in range(1, 11):     ## 写入一些数据
    sheet['A' + str(i)] = i 

refObj = openpyxl.chart.Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10) ## 从一个矩形区域选择的单元格，创建一个 Reference 对象。

seriesObj = openpyxl.chart.Series(refObj, title='First series') ## 传入 Reference 对象，创建一个 Series 对象。

chartObj = openpyxl.chart.BarChart() 
chartObj.title = 'My Chart'
chartObj.append(seriesObj)  ## 将 Series 对象添加到 Chart 对象。 
sheet.add_chart(chartObj, 'C5') ##  在C5添加Chart 对象
wb.save('sampleChart.xlsx')

REFERENCES