python之Excel的处理

python之Excel的处理

之前在公司有一些自动化的任务，主要是从几个excel中找数据，然后记录到新的excel中。说真的学到了很多，感叹python的强大，各个领域用python真是能省不少的事。
之前就想记录来着，一直给忙忘了。现在在这里回忆也巩固以下之前的知识，希望也能帮助刚接触的朋友更快的上手。OK，让我们开始吧！

1、Python处理Excel的库

处理excel的库还是有不少的，我在这里给列出来：

• xlrd和xlwt库：这两个库分别用于读写xls类型的excel文件，其实操作起来还是挺方便的，只是不支持xlsx文件，所以不在本次探究范围，有兴趣的小伙伴可以研究研究。
• openpyxl库：这是很常用的一个excel处理库，用起来体验感极好。也是本次介绍的重点。它不会占用文件，不管文件是否处于打开状态都能读取。
• pandas库：很强大的一个库，呃…好吧！其实我对这个库不是特别了解。不管怎么说，咱们能从这个库中找到自己想用的东西就行。
• xlwings库：这个库的操作跟VBA很像似，据说可以代替VBA集成到excel中，但我去试了一下，未果，可能是自己功力确实还不够。我去试了一下，这个库会驱动excel打开文件然后再操作，所以可以看得到数据的写入。

2、Openpyxl基本用法

import openpyxl
# 加载excel文件
wb = openpyxl.load_workbook(excel_path) 
# 1.获取sheet
sheets = wb.worksheets # 返回一个sheet对象列表，然后根据索引可以拿到sheet
sheet = wb['sheet1'] # 根据sheet名拿到工作表
# 2.获取单元格
a1 = sheet['A1'].value # 切片方式获取A1单元格的值
a1 = sheet.cell(row=1, column=1).value # 指定行列获取A1单元格的值
# 3.读取范围内的单元格
cells = sheet['A1':'B5'] # 获取A1到B5范围内的单元格
# 4.遍历单元格
# 当接收遍历有多个时，个数应与列差个数保持一致，否则会报错。例如：A2-C9有三列，所以需要三个变量接收。此时按行赋值
for c1, c2, c3 in sheet['A2':'C9']:  
    print(f'{c1.value}, {c2.value}, {c3.value}')
# 挨个读取单元格，需要注意的是外层循环是以行为单位，需要再次循环拿到值，或者通过索引取值。
for cells in sheet['A2':'C9']:
    for cell in cells:
        print(cell.value)
# 5. 获取包含数据的最大行号和列号,但是这个还是有些不足，如果单元格有样式就不太准确了。
max_row = sheet.max_row
max_column = sheet.max_column
# 6. 单元格赋值
sheet['A1'].value = 'A1' # 将A1单元格的值设置为A1
sheet.cell(row=2, column=1).value = 'A1'
# 7. 批量赋值, 以元组或列表嵌套的形式配合for循环添加。追加到有数据的最后一行
rows = (
    ('name', 'age', 'number'),
    ('张三', 24, '001'),
    ('李四', 22, '002'),
    ('王二', 23, '003'),
)
for row in rows:
    sheet.append(row)
# 8. 过滤和排序
data = [
    ['Item', 'Colour'],
    ['pen', 'brown'],
    ['book', 'black'],
    ['plate', 'white'],
    ['chair', 'brown'],
    ['coin', 'gold'],
    ['bed', 'brown'],
    ['notebook', 'white'],
]
for r in data:
    sheet.append(r)
sheet.auto_filter.ref = 'A1:B8' # 设置A1-B8为过滤列
sheet.auto_filter.add_filter_column(1, ['brown', 'white']) # 设置第一列过滤数据，'brown', 'white'
sheet.auto_filter.add_sort_condition('B2:B8') # 按B2-B8进行自动排序
# 9. 合并单元格
sheet.merge_cells('A1:B2') # 合并A1-B2单元格
# 10. 冻结窗格
sheet.freeze_panes = 'B2' # 以B2单元格为基础冻结工作表

更多openpyxl的操作可以参考这一篇，总结的特别全面。https://geek-docs.com/python/python-tutorial/python-openpyxl.html。

3、pandas操作excel

这个库我主要用到了它的查询功能，当你需要在工作表中查询某个值所在的行号时就能用得到。目前我只用了这一个功能。
下面给个例子，感觉使用起来稍微有一点点复杂，需要自己多动手试试才能找到规律。

import pandas as pd
# sheet_name指定excel的sheet名，header指定以哪一行作为标题行
inner_excel = pd.read_excel(inner_path, sheet_name='インシデント台帳', header=1)
# 上面指定标题行之后，可以使用下面的方法查找某一标题所在列的值的行号
rows = inner_excel[inner_excel["案件番号"] == "20290208-001"].index.tolist() # 查找20290208-001在案件番号列所在的行号列表
infos = []
if len(rows) == 0:
    indexs = excel_event[excel_event["案件番号"] == case_num].index.tolist()
    if len(indexs) > 0:
        infos.append(excel_event['分類'].values[indexs[0]])
        infos.append('日野 浩')
        date64 = excel_event['クローズ\n日付'].values[indexs[0]]
        infos.append(str(date64.astype("datetime64[D]")).replace('-', '/'))
        return infos
    return []
infos.append(inner_excel['分類'].values[rows[0]])  # 获取分类列指定行号的值
infos.append(inner_excel['担当者'].values[rows[0]])
date64 = inner_excel['完了日'].values[rows[0]]
infos.append(str(date64.astype("datetime64[D]")).replace('-', '/'))  # 日期的处理，这里也是一个大坑。需要特别注意。

这里只列出来部分内容，确实我觉得还挺复杂的，大家加油！有没看懂的地方欢迎留言探讨！有不对的地方还望指正。