Python自动化：文件、excel

 

---

操作文件

获取文件下所有文件名

获取文件下所有文件名：确认文件夹位置 -> 获取所有文件名称 -> 显示所有文件名称

import os                          # 引入os库
path = './工作文件夹/'               # 将文件夹的路径赋值给path
filename = os.listdir( path )      # 获取文件夹下全部名称，并将结果赋值给filename
print(filename)                    # 输出这些文件

 

---

txt文件筛选与关键词匹配

文件管理很混乱，很多不同格式的文件都混在同一个文件里，目标是筛选出txt文件。

• 一、公司的会议记录是保存在txt文件里的，我们需要从这一堆不同格式的文件里找出txt文件。

• 二、找到txt文件后，还要看看它的内容是否包含“项目管理”这个关键词。

• 三、如果txt文件中包含关键词，我们就需要把它的路径写入到新的txt文件里。

import os

# 设置文件夹路径，获取文件夹下的所有文件名
path = './工作文件夹/'
files_list = os.listdir(path)

# 设置需要查找的关键词
key_word = '项目管理'

# 打开demo文件，追加写入
result_file = open('./demo.txt', 'a', encoding='utf-8')

# 循环处理每一个文件
for file_name in files_list:
    # 判断文件类型是否在文件名中
    if '.txt' in file_name:
        # 找到文件时先打印提示
        print("找到了文件：" + file_name)

        # 将文件夹路径和文件名拼接成该文件的相对路径
        target_file = path + file_name

        # 打开文件，读取文件内容，而后关闭文件
        file = open(target_file, 'r', encoding='utf-8')
        content = file.read()
        file.close()

        # 判断关键词是否在文件内容中
        if key_word in content:
            # 匹配到关键词时先打印提示
            print("文件**{}**包含了关键词：{}".format(target_file, key_word))

            # 将包含关键词的文档的文件路径，写入结果文件。
            result_file.write(target_file + '\n')

# 关闭结果文件
result_file.close()

输出结果：

找到了文件：Easter_Egg_2.txt

找到了文件：06_01_2020会议记录.txt
文件**./工作文件夹/06_01_2020会议记录.txt**包含了关键词：项目管理

找到了文件：05_20_2020会议记录.txt
文件**./工作文件夹/05_20_2020会议记录.txt**包含了关键词：项目管理

找到了文件：05_21_2020会议记录.txt
文件**./工作文件夹/05_21_2020会议记录.txt**包含了关键词：项目管理

找到了文件：04_11_2020会议记录.txt

找到了文件：Easter_Egg_1.txt

 

---

单文件拆分为多文件

公司将推出员工发展基金这一福利，基金分三档：100元、200元和300元，这笔基金用在员工的成长上。

任务目标是让我们把这张确认表按照员工的姓名拆分成一个个单独的表。

import csv

# 设置员工发展基金确认表路径
source_path = './员工发展基金确认表.csv'
# 设置存放拆分结果文件的文件夹路径
result_path = './员工发展基金文件夹_demo/'

# 打开员工发展基金确认表
with open(source_path, 'r', encoding='utf-8', newline='') as source_file:
    # 将文件对象转换为DictReader对象
    source_csv = csv.DictReader(source_file)
    # 将csv对象的表头读取出来
    headers = source_csv.fieldnames

    # 循环处理确认表中除表头外的每一行数据
    for csv_row in source_csv:
        # 根据获取的员工名字拼接新文件名
        file_name = csv_row['姓名'] + '.csv'
        # 拼接新文件路径
        file_path = result_path + file_name

        # 创建新文件并添加数据
        with open(file_path, 'w', encoding='utf-8', newline='') as target_file:
            # 将文件对象转换为DictWriter对象
            target_writer = csv.DictWriter(target_file, fieldnames=headers)
            # 写入表头
            target_writer.writeheader()
            # 写入数据
            target_writer.writerow(csv_row)

 

---

操作excel：openpyxl库

我们自己操作excel的顺序：打开工作簿 -> 确认工作表 -> 操作单元格

工作簿对象

根据openpyxl的定义，一个.xlsx格式的Excel文件就代表了一个工作簿对象。

# 获取工作簿对象
from openpyxl import load_workbook

# 打开【公司人员名单.xlsx】工作簿
staff_wb = load_workbook('./codes/material/公司人员名单.xlsx')
# 打印工作簿对象
print(staff_wb)

# 创建工作簿对象
from openpyxl import Workbook

# 新建工作簿
new_wb = Workbook()

# 将新建的工作簿保存为【new_excel.xlsx】
new_wb.save('./new_excel.xlsx')

工作表对象

现实中的工作表，在openpyxl中对应着工作表对象（Worksheet对象）。

一个工作表对象（Worksheet对象）就表示工作簿中的一张工作表。那么如何用代码来获取工作表对象呢？

常用的获取工作表的方式有两种：

• 单张表时，通过工作簿对象的属性active，会获取到活动的工作表（当前正在操作的工作表）

from openpyxl import load_workbook

# 打开【公司人员名单.xlsx】工作簿
staff_wb = load_workbook('./codes/material/公司人员名单.xlsx')
# 获取活动工作表
active_ws = staff_wb.active

# 打印工作簿对象
print(staff_wb)

• 多张表时，按表名取表，如果我们已知工作表的名称，就可以以表名为索引，用工作簿对象[‘表名’]的方式取到指定的工作表对象。

from openpyxl import load_workbook

# 打开【公司人员名单.xlsx】工作簿
staff_wb = load_workbook('./codes/material/公司人员名单.xlsx')
# 按表名取表
fhy_ws = staff_wb['上半年公司名单']  # fhy为first half year（上半年）的缩写
shy_ws = staff_wb['下半年公司名单']  # shy为second half year（下半年）的缩写

# 打印工作簿对象
print(staff_wb)
# 打印工作表对象
print(fhy_ws)
print(shy_ws)

有了工作表对象后，我们就可以学习一些它的基本操作了。

• 获取单行或单列

在Excel表格中，使用数字表示行数，用英文字母表示列名。

在openpyxl中，我们可以通过工作表对象[行数]或工作表对象[‘列名’]的方式获取到一个元组，这个元组中包含了指定行或列中的所有数据。

from openpyxl import load_workbook

# 打开【公司人员名单.xlsx】工作簿
staff_wb = load_workbook('./codes/material/公司人员名单.xlsx')
# 获取活动工作表
active_ws = staff_wb.active

# 打印获取到的第五行数据
print(active_ws[5])
# 打印获取到的第二列数据
print(active_ws['B'])

• 获取多行数据

我们可以借助工作表对象的方法iter_rows()来得到表格中指定范围内的多行数据。

参数min_row和max_row分别表示最小行索引和最大行索引，最小行索引的值默认为1，最大行索引的值默认为表格中有数据的最下面一行的行数；

参数min_col和max_col分别表示最小列索引和最大列索引，最小列索引的值默认为1，最大列索引的值默认为表格中有数据的最右面一列的列数;

参数values_only决定是否返回单元格的值，如果为True则返回单元格的值，如果为False则返回单元格对象。通常情况下，只读数据时，需要将该参数设置为True，要写入数据时，保持其为默认的False就好。

工作表对象的方法iter_rows()会返回一个可迭代对象，该对象中有n个元组，n为参数中指定的行数，每一个元组都代表了表格中的一行。

因此，通常情况下，iter_rows()会和for循环结合使用，从而使得我们取出其返回的可迭代对象中的每一个元组，即表格中指定范围内的每一行数据。

from openpyxl import load_workbook

# 打开【公司人员名单.xlsx】工作簿
staff_wb = load_workbook('./codes/material/公司人员名单.xlsx')
# 获取【'上半年公司名单'】工作表
fhy_ws = staff_wb['上半年公司名单']

# 返回第2行至第12行，第2列（B列）至第3列（C列）这个范围的单元格内的所有数据（值）
for row in fhy_ws.iter_rows(min_row=2, max_row=12, min_col=2, max_col=3, values_only=True):
    print(row)

• 添加数据

from openpyxl import load_workbook

# 打开【公司人员名单.xlsx】工作簿
staff_wb = load_workbook('./公司人员名单.xlsx')
# 获取活动工作表
active_ws = staff_wb.active

info_list = ['S1911', '萧爵瑟', 3000, '内容']
info_tuple = ('S1912', '吴琐薇', 5000, '销售')

active_ws.append(info_list)
active_ws.append(info_tuple)

# 保存工作簿为【append_demo.xlsx】
staff_wb.save('./append_demo.xlsx')

我们可以使用工作表对象的append()来添加一行数据，相信这个方法你一点都不陌生。

该方法可将部分可迭代对象（常见的如列表、元组）添加到工作表对象中，即给表格的末尾追加一行数据。

语法也很简单，写为工作表对象.append(列表/元组)即可。

不过需要注意的是，使用append()添加完数据后，如果想要在本地的Excel文件中看到添加后的数据，就一定要将工作簿保存下来，即使用工作簿对象的方法save()。

添加了两行数据，两行数据分别为列表和元组，并且它们确实是被添加到了工作表的最后。如果你在本地运行了上面的代码，就会生成对应的xlsx文件【append_demo.xlsx】。

单元格对象

单元格对象代表工作表中的一个单元格。

目前为止，基本上所有对于行列的操作，最终都可以回归为对单元格的操作。

三种常见的获取单元格对象的方式：

• 第一种方式：for row in 工作表对象.iter_rows()来获取指定范围的行，当参数values_only为默认的False时，我们得到的row就是一个个由单元格对象组成的元组，可以通过索引或者for循环遍历的方式来获取单独的单元格对象。

• 第二种方式：通过行数或者列名来指定具体的行或列，然后通过for循环遍历获取指定行或列中的每一个单元格对象。

• 第三种方法：直接通过工作表对象['单元格坐标']来获取具体的单元格对象。

表格读写

# 从openpyxl库导入load_workbook函数
from openpyxl import load_workbook

# 打开【10月员工绩效表】的工作簿，获取活动工作表
performance_wb  = load_workbook('./material/10月员工绩效表.xlsx')
performance_ws = performance_wb.active

# 打开【江宇工资信息表】的工作簿，获取活动工作表
info_wb = load_workbook('./material/江宇工资信息表.xlsx')
info_ws = info_wb.active

# 获取【绩效】值
performance = performance_ws['D14'].value
# 获取【奖金】值
bonus = performance_ws['E14'].value
# 获取【基本工资】值
base = performance_ws['F14'].value

# 写入【绩效】值
info_ws['E11'].value = performance
# 写入【奖金】值
info_ws['F11'].value = bonus
# 写入【基本工资】值
info_ws['G11'].value = base

# 保存对【江宇工资信息表】工作簿的写入
info_wb.save('./material/江宇工资信息表.xlsx')

# 从openpyxl库导入load_workbook和Workbook
from openpyxl import load_workbook, Workbook

# 打开【10月员工绩效表.xlsx】工作簿
performance_wb = load_workbook('./material/10月员工绩效表.xlsx')
# 获取活动工作表
performance_ws = performance_wb.active

# 获取performance_ws中除表头外的数据
for row in performance_ws.iter_rows(min_row=2, values_only=True):
    # 读取【工号】
    staff_id = row[0]
    # 读取【员工姓名】
    staff_name = row[1]
    # 读取【绩效】
    performance = row[3]
    # 读取【提成】
    bonus = row[4]
    # 计算“奖金”
    award = performance + bonus
    # 打印结果
    print('工号：{}，姓名：{}，本月奖金为：{}'.format(staff_id, staff_name, award))

# 从openpyxl库导入load_workbook函数
from openpyxl import load_workbook

# 打开【10月员工绩效表.xlsx】工作簿
performance_wb = load_workbook('./material/10月员工绩效表.xlsx')
# 获取活动工作表
performance_ws = performance_wb.active

# 创建员工信息字典
staff_info = {}

# 从第二行开始读取工作表中的信息
for row in performance_ws.iter_rows(min_row=2, values_only=True):
    # 取出工号
    member_number = row[0]
    # 将信息存入员工信息字典
    staff_info[member_number] = {
         '姓名': row[1],
         '部门': row[2],
         '绩效': row[3],
         '奖金': row[4],
         '基本工资': row[5],
         '是否确认': row[6]
     }
print(staff_info)

数据筛选

from openpyxl import load_workbook, Workbook

# 打开【10月考勤统计.xlsx】工作簿
wb = load_workbook('./material/10月考勤统计.xlsx')
# 获取活动工作表
ws = wb.active

# 获取表头
late_header = []
for cell in ws[1]:
    late_header.append(cell.value)

# 新建工作簿
new_wb = Workbook()
# 获取新工作簿中的工作表
new_ws = new_wb.active

# 将表头写入新工作簿的工作表中
new_ws.append(late_header)

# 从第二行开始遍历表格
for row in ws.iter_rows(min_row=2, values_only=True):
    # 取出姓名，迟到时间和迟到次数
    name = row[1]
    time = row[3]
    number = row[-1]
    # 判断是否迟到
    if time > 45 and number > 3:
        print('{}迟到了{}分钟，迟到了{}次'.format(name, time, number))
        # 将迟到人员信息写入新工作簿的工作表中
        new_ws.append(row)

# 将新工作簿保存为【10月迟到人员信息.xlsx】
new_wb.save('./material/10月迟到人员信息.xlsx')

样式和图形设置

我们平时在使用Excel的时候，需要经常设置样式或者根据选中的数据来画图，这些都可以使用openpyxl库来实现。

import os
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Alignment, Side, Border

# 定义表头颜色样式为橙色
header_fill = PatternFill('solid', fgColor='FF7F24')
# 定义表中颜色样式为淡黄色
content_fill = PatternFill('solid', fgColor='FFFFE0')
# 定义表尾颜色样式为淡桔红色
bottom_fill = PatternFill('solid', fgColor='EE9572')

# 定义对齐样式横向居中、纵向居中
align = Alignment(horizontal='center', vertical='center')

# 定义边样式为细条
side = Side('thin')
# 定义表头边框样式，有底边和右边
header_border = Border(bottom=side, right=side)
# 定义表中、表尾边框样式，有左边
content_border = Border(left=side)

# 设置文件夹路径
path = './各部门利润表汇总_副本/'
# 返回当前目录下所有文件名
files = os.listdir(path)

# 循环文件名列表
for file in files:
    # 拼接文件路径
    file_path = path + file
    # 打开工作簿
    wb = load_workbook(file_path)
    # 打开工作表
    ws = wb.active

    # 调整列宽
    ws.column_dimensions['A'].width = 10
    ws.column_dimensions['B'].width = 25
    ws.column_dimensions['C'].width = 50
    ws.column_dimensions['D'].width = 10
    ws.column_dimensions['E'].width = 20
    ws.column_dimensions['F'].width = 15

    # 循环第一行单元格，调整表头样式
    for cell in ws[1]:
        # 设置单元格填充颜色
        cell.fill = header_fill
        # 设置单元格对齐方式
        cell.alignment = align
        # 设置单元格边框
        cell.border = header_border

    # 获取最后一行行号
    row_num = ws.max_row

    # 从第二行开始，循环到倒数第二行
    for row in ws.iter_rows(min_row=2, max_row=(row_num-1)):
        # 循环取出单元格，调整表中样式
        for cell in row:
            cell.fill = content_fill
            cell.alignment = align
            cell.border = content_border

    # 循环最后一行单元格，调整表尾样式
    for cell in ws[row_num]:
        cell.fill = bottom_fill
        cell.alignment = align
        cell.border = content_border

    # 保存
    wb.save(file_path)

import os
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Alignment, Side, Border

# 定义表头颜色样式为橙色
header_fill = PatternFill('solid', fgColor='FF7F24')
# 定义表中颜色样式为淡黄色
content_fill = PatternFill('solid', fgColor='FFFFE0')
# 定义表尾颜色样式为淡桔红色
bottom_fill = PatternFill('solid', fgColor='EE9572')

# 定义对齐样式横向居中、纵向居中
align = Alignment(horizontal='center', vertical='center')

# 定义边样式为细条
side = Side('thin')
# 定义表头边框样式，有底边和右边
header_border = Border(bottom=side, right=side)
# 定义表中、表尾边框样式，有左边
content_border = Border(left=side)

# 设置文件夹路径
path = './各部门利润表汇总/'
# 返回当前目录下所有文件名
files = os.listdir(path)

# 循环文件名列表
for file in files:
    # 拼接文件路径
    file_path = path + file
    # 打开工作簿
    wb = load_workbook(file_path)
    # 打开工作表
    ws = wb.active

    # 调整列宽
    ws.column_dimensions['A'].width = 10
    ws.column_dimensions['B'].width = 25
    ws.column_dimensions['C'].width = 50
    ws.column_dimensions['D'].width = 10
    ws.column_dimensions['E'].width = 20
    ws.column_dimensions['F'].width = 15

    # 循环第一行单元格，调整表头样式
    for cell in ws[1]:
          # 设置单元格填充颜色
          cell.fill = header_fill
          # 设置单元格对齐方式
          cell.alignment = align
          # 设置单元格边框
          cell.border = header_border

    # 获取最后一行行号
    row_num = ws.max_row

    # 从第二行开始，循环到倒数第二行
    for row in ws.iter_rows(min_row=2, max_row=(row_num-1)):
        # 循环取出单元格，调整表中样式
        for cell in row:
            cell.fill = content_fill
            cell.alignment = align
            cell.border = content_border

    # 循环最后一行单元格，调整表尾样式
    for cell in ws[row_num]:
          cell.fill = bottom_fill
          cell.alignment = align
          cell.border = content_border

    # 保存
    wb.save(file_path)