快速掌握用python处理Excel

今天要记录的学习主题是 —— Excel表处理神器：openpyxl库

1 写在前面

之前我想过要不要写一篇关于如何处理excle表的博客，可是网上一搜都是有太多的文章了。python处理excel表的库，常见的有：xlrd、xlwt、xlwings、openpyxl、win32com、pandas等。

针对python各类第三方库，支持情况如下：

介于网上有这么多精彩的博客，我就一直懒的再去做总结。不过昨天小杰找到我，让我帮他处理下excel表数据。我说你自己百度下不就行了吗？他说网上的看不懂。（虽然我觉得他肯定是没认真看）

好吧，既然如此，我就为你写篇博客，让你能快速掌握excel表吧！

2 openpyxl库

这里我主要介绍openpyxl库的几类常见方法。至于为什么是openpyxl，主要是我自己平时习惯用openpyxl，其他库已经不熟悉了。

1、打开/创建excel文件

from openpyxl import *
# 打开已有test.xlsx表
wb1 = load_workbook('test.xlsx')
# 创建xlsx表,结束后以test.xlsx表保存
wb2 = Workbook()
ws = wb2.active
wb2.save('test.xlsx')

2、选择/创建sheet表

、# 创建test1表
ws1 = wb1.create_sheet('test1')
# 选择test1表
ws2 = wb2['test1']

3、单一单元格访问

# 方法一：
value1 = ws['A4'].value
# 方法二
value2 = ws.cell(row=4, column=2).value

这里一般获取单元格都是为了获取表格中的内容，即cell.value。如果是为了获取其他内容，如行、列、坐标值，可通过以下命令：

# 行
ws.cell(row=4, column=2).row
# 列
ws.cell(row=4, column=2).column
# 坐标
ws.cell(row=4, column=2).coordinate

4、多个单元格访问

# 切片
for row in ws['A1':'C3']:
    for cell in row:
        print(cell.value)
# 通过行或者列，下方是行
for row in ws[1:3]:
    for cell in row:
        print(cell.value)
# 遍历
for row in ws.iter_rows(min_row=1,max_row=3,min_col=1,max_col=3):
    for cell in row:
        print(cell.value)
# 列表转化，下方是行
for row in list(ws.rows)[0:3]:
    for cell in row:
        print(cell.value)

初学时，我以为当只取一列或者一行值时，可以只用一个for循环，后来发现不行，当只有一个循环会报：AttributeError: 'tuple' object has no attribute 'value'

for row in ws['A1':'A4']:
    print(row.value)
报错：AttributeError: 'tuple' object has no attribute 'value'

另外，在通过行列取值时，ws[1:4]表示第1行开始；但用列表转化后，[1：4]表示从第2行开始。这个细节，需要关注下。

5、单元格数据写入

# 单一单元格写入
ws['A1']='test'
# 一行数据写入
ws.append([1,2,3,4,5])

6、获取表最大行、最大列

ws.max_column
ws.max_row

7、删除工作表

# 方式一
wb.remove(sheet)
# 方式二
del wb[sheet]

8、简单的单元格设置格式

from openpyxl.styles import Font, colors, Alignment

# 设置字体、颜色：等线24号，加粗斜体，字体颜色红色
myfont = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
ws['A1'].font = myfont
# 设置对齐：垂直居中和水平居中
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
# 设置行高 ：第1行行高
ws.row_dimensions[1].height = 10
# 设置列宽：A列列宽
ws.column_dimensions['A'].width = 10
# 合并单元格
ws.merge_cells('A1:C3')

不过，我觉得单元格格式设置这种，还是直接在excel表上处理来着方便，因为效果直观嘛。

3 小杰的实际问题

【背景】：原始表格内的数据是按月分将所有人数据统计的，如1月将A/B/C等人的信息进行汇总。

【诉求】：将所有数据按人名进行统计，即将A的1-12月数据汇总到一张sheet表上；并且要保留原表中1-3行的表头。

【困难】：由于人名有上百个，总不能手动复制粘贴每人的数据吧？

4 示例代码

# coding=utf-8
# @公众号 : "鹏哥贼优秀"
# @Date : 2020/4/9
# @Software : PyCharm 
# @Python version: Python 3.7.2

from openpyxl import *

# 将ws1的i-j行数据写入ws2表
def get_value(ws1,ws2,i,j):
    for row in list(ws1.rows)[i:j]:
        res = []
        for cell in row:
            res.append(cell.value)
        ws2.append(res)

print('start!')
wb1 = load_workbook('原始数据.xlsx')
wb2 = load_workbook('test.xlsx')
sheets = ['1月','2月','3月','4月','5月','6月','7月','8月','9月','10月','11月','12月']
ws_first = wb1[sheets[0]]
names = []
# 获取所有人名
for row in ws_first['C4':'C22']:
    for cell in row:
        names.append(row.value)

for i,name in enumerate(names):
    # 保留每张sheet表1-3行的表头
    ws2 = wb2.create_sheet(name)
    get_value(ws_first,ws2,0,3)
    # 获取单人所有月分数据，并写入ws2
    for sheet in sheets:
        ws1 = wb1[sheet]
        get_value(ws1,ws2,i+3,i+4)

wb2.save('test.xlsx')
print('end!')

作者：华为云特约供稿开发者鹏哥贼厉害