openpyxl 的用法

@[TOC] openpyxl 的用法

openpyxl 简介

openpyxl 是 Python 的一个第三方库，用于操作 excel 文件（只能读写 .xlsx 文件）。

关于 Excel 的一些基本定义

• Excel 文件被称为 workbook。
• 每个 workbook 包含多个 sheets(也叫 worksheets，折页)
• 用户正在使用的 sheet 叫做 active sheet.
• 每个 sheet 都有 columns (列，用字母表示，A 开头）和 rows（行，用数字表示，1 开头）。
• 行和列对应的小格子叫做 cell（单元格）。
• 每个 cell 里可以是文本也可以是数字。
• 这些带有数据的单元格一起构成一个折页。

openpyxl 的基本用法

1、读取已有 xlsx 文件，无法打开 xls 文件

 wb = openpyxl.load_workbook(filename='safety.xlsx')

2、读取当前活动页

>>> ws = wb.active

获取指定名称的折页：

 >>> ws = wb['Sheet1']
 >>> ws
<Worksheet "Sheet1">

查看折页的名称：

>>> ws.title
'Sheet1'

3、读取 cell 里的内容：

>>> ws['a1']
<Cell 'Sheet1'.A1>
>>> a = ws['a1'].value
>>> a
'试题题号'

当有合并的单元格时，只读取第一个单元（左上角）的内容，其他的单元内容为空：

>>> b= ws['a2'].value
>>> b
1
>>> c=ws['a3'].value
>>> c
>>>

也可以使用 cell() 来访问单元格：

>>> ws.cell(row=1, column=2)
<Cell 'Sheet1'.B1>
>>> c = ws.cell(row=1, column=2)
>>> c.value
'试题类型'

也可以用索引来读取单元格：

>>> ws[1][1].value
'试题类型'

注：这里第一 [1] 表示第一行，第二个 [1] 表示第二列，行以 1 开头，列以 0 开头 -_-’’。

4、查看行数和列数：

# 查看多少列
>>> ws.max_row
3753
#查看多少列：
>>> ws.max_column
10
#把列的字母号转换成数字：
>>> ws['b3'].column
2
#显示单元格的列号：
>>> ws.cell(row=1, column=3).column_letter
'C'

列号可以用字母也可以用数字，它们之间的转换用下面两个函数：

>>> from openpyxl.utils import get_column_letter
>>> from openpyxl.utils import column_index_from_string
>>> get_column_letter(1)
'A'
>>> column_index_from_string('A')
1

5、查看页面共多少行和列

>>> ws.dimensions
'A1:J3753'

6、查看单元格类型：

>>> ws['a1'].style
'常规'

7、 查看单元格坐标：

>>> ws['a1'].coordinate
'A1'

8、创建一个 workbook

>>> wb1 = openpyxl.Workbook()

更改折页名称：
>>> sheet =wb1.active
>>> sheet
<Worksheet "Sheet">
>>> sheet.title='test'
>>> sheet
<Worksheet "test">

9、插入行：

>> sheet.append([1,2, 3])
>>> sheet.append([1,2, 3])
>>> sheet.insert_rows(2)
# append() 在 sheet 最后追加一行
>>> sheet.append([4,5,6])

10、删除行：

>>> sheet.delete_rows(3)

11、移动行列到新的位置：

>>> sheet.move_range("A3:C3", rows=-1, cols=2)

表示向上移动 1 行，向右移动 2 列。

12、公式：

>>> sheet['A2']="=SUM(1,1)"
>>> sheet['D1']="=SUM(A1:C1)"
>>> wb1.save('test.xlsx')

13、删除列：

>>> sheet.delete_cols(4, 2)

14、带公式移动单元格：

>>> sheet['D1']="=SUM(A1:C1)"
>>> sheet.move_range("D1",rows=1, cols=0, translate=True)

>>> sheet.append([3, 5, 7])
>>> sheet.move_range("D2", rows=1, cols=0)

注：在使用 append 添加行时要注意。虽然 第三行的数据在前面被移走了，但是 append 却把新的数据添加到了第四行，第三行即使数据为空，但还是被占用了。

move_range() 里 translate = True ，会把公式的引用数据进行重新定义，如果不写，就表示公式直接复制过去，引用的还是第二行的数据。

给单元格添加公式，要给cell().value 赋值，不能直接给cell() 赋值。

>>> sheet.cell(row=5, column=4).value="=SUM(A5:C5)"
>>> for cell in sheet[5]:
...     print(cell.value)
...
3
5
7
=SUM(A5:C5)

>>> sheet.cell(row=5, column=4)="=SUM(A5:C5)"
  File "<stdin>", line 1
SyntaxError: can't assign to function call

>> for i in range(4):
...     sheet[4][i].value=sheet[5][i].value
...
>>> wb1.save('test.xlsx')

用上面的方法赋值第 5 行到 第 4 行，公式是直接原样复制过去，不会改公式的引用。

15、复制折页

>>> wb1=openpyxl.Workbook()
>>> ws1=wb1.create_sheet("question")
>>> ws1
<Worksheet "question">
>>> ws1=ws

16、合并单元格

>>> wb = openpyxl.load_workbook('test.xlsx')
>>> ws = wb.active
>>> ws.max_row
5
>>> ws[4][0].value
3
>>> ws.merge_cells('A3:A4')
>>> ws[4][0].value

合并 A3, A4 后，A4 单元格里的数值没有了。

17、取消合并单元格：

>>> ws.unmerge_cells('A3:A4')
>>> ws[4][0].value
>>>

取消之后数值也回不来了。

18、删除有合并的行

删除之后，下面的行自动上移，但上移的只是数值不含格式，例如公式并没有随着行的改变而改变。A5 单元格里的 3 也消失了。

注：这里删除行或列，只删除内容，不会删除格式。这样导致删除带有合并单元格时可能会出现问题。如果删除的合并单元格和下面的合并单元格格数不一样，就会出错，导致下面单元格的内容出现错位等问题。如下面的，判断题两个选项变成四个了（之前这个位置是多选选择，有 4 个选项）：

19、styles:
styles 用于改变数据的显示样式。主要用于：

• 设置字体的大小、颜色等
• 设置填充的样式或颜色
• 设置单元格边框
• 单元格的对齐
• 保护

>>> import openpyxl
>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
>>> wb = openpyxl.Workbook()
>>> ws = wb.active
>>> ft = Font(color='00FF00')
>>> ws['a1'].font=ft
>>> ft1=Font(size=20)
>>> ft2=Font(bold=True)
>>> ft3=Font(italic=True)
>>> ws['B1].font=ft1
>>> ws['B1'].font=ft1
>>> ws['c1'].font=ft2
>>> ws['d1'].font=ft3
>>> wb.save('styles.xlsx')

效果是这样的：

设置行高和列宽

>>>ws.row_dimensions[1].height = 15
>>>ws.column_dimensions[get_column_letter(1)].width = 12