引言

在之前文章中，有一篇《这五个办公室常用自动化工具我用python帮你写好了，复制代码就能用》，没想到受到了广大读者的喜爱。

其中进行了一个投票，总结发现很多读者对于 excel 的自动化需求非常高，

投票结果

因此，本次再推出五个实用的、针对表格的代码，直接复制可以用！

1、excel提取图片

完整代码

import os
import zipfile
import shutil
import argparse


def extract_images_from_xlsx(xlsx_file_path, output_folder):
    if not os.path.exists(output_folder) and not os.path.isdir(output_folder):
        os.makedirs(output_folder)

    extend = os.path.splitext(xlsx_file_path)[1]
    if extend != '.xlsx' and extend != '.xls':
        return

    file_name = os.path.basename(xlsx_file_path)
    new_xlsx_file_path = os.path.join(output_folder, file_name)
    shutil.copy(xlsx_file_path, new_xlsx_file_path)

    zip_name = f"{file_name.split('.')[0]}.zip"
    new_zip_file_path = os.path.join(output_folder, zip_name)
    os.rename(new_xlsx_file_path, new_zip_file_path)

    extract_folder = os.path.join(output_folder, 'files')

    with zipfile.ZipFile(new_zip_file_path, 'r') as f:
        for files in f.namelist():
            f.extract(files, extract_folder)

    os.remove(new_zip_file_path)
    media_path = f'{extract_folder}/xl/media/'
    image_file_list = os.listdir(media_path)
    for image_file in image_file_list:
        image_path = os.path.join(media_path, image_file)
        new_image_path = os.path.join(output_folder, image_file)
        shutil.copy(image_path, new_image_path)

    shutil.rmtree(extract_folder)


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        prog='excel图片提取',
        description='提取excel中的所有图片',
    )
    parser.add_argument('-p', '--path', type=str, help='excel文件路径')
    parser.add_argument('-o', '--output', type=str, help='导出图片文件夹')

    args = parser.parse_args()

    path = args.path
    output = args.output if args.output is not None else '未命名文件夹'

    if path is None:
        print('缺失excel路径')
    elif not path.endswith('.xlsx') and not path.endswith('.xls'):
        print('不是excel文件')
    else:
        print('开始提取...')
        extract_images_from_xlsx(path, output)

调用信息

usage: excel图片提取 [-h] [-p PATH] [-o OUTPUT]

提取excel中的所有图片

options:
  -h, --help            show this help message and exit
  -p PATH, --path PATH  excel文件路径
  -o OUTPUT, --output OUTPUT 导出图片文件夹

调用示例

python xx.py -p excel路径

2、通过txt修改指定内容

安装库

pip install openpyxl

完整代码

import argparse
import openpyxl


def update_excel_with_txt(excel_path, txt_path):
    with open(txt_path, 'r', encoding='utf-8') as file:
        updates = dict(line.strip().split(':') for line in file if ':' in line)

    wb = openpyxl.load_workbook(excel_path)
    sheet_names = wb.sheetnames

    for sheet_name in sheet_names:
        sheet = wb[sheet_name]

        for row in sheet.iter_rows():
            for cell in row:
                if cell.value in updates:
                    cell.value = updates[cell.value]

    wb.save(excel_path)


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        prog='excel替换关键字',
        description='通过txt将excel中相应关键字做替换',
    )
    parser.add_argument('-p', '--path', type=str, help='excel文件路径')
    parser.add_argument('-t', '--txt', type=str, help='关键字txt文件')

    args = parser.parse_args()

    path = args.path
    txt_path = args.txt

    if path is None:
        print('缺失excel路径')
    elif not path.endswith('.xlsx'):
        print('不是excel文件')
    elif not txt_path.endswith('.txt'):
        print('关键字不是txt文件')
    else:
        print('开始替换...')
        update_excel_with_txt(path, txt_path)

调用信息

usage: excel替换关键字 [-h] [-p PATH] [-t TXT]

通过txt将excel中相应关键字做替换

options:
  -h, --help            show this help message and exit
  -p PATH, --path PATH  excel文件路径
  -t TXT, --txt TXT     关键字txt文件

调用示例

python xx.py -p excel路径 -t 关键字txt路径

关键字文本样例

关键字txt

注意：请使用英文的冒号。

3、excel内容导入mysql

本案例代码不直接导入数据库，而是输出一份 导入文件，可以通过 导入文件 快捷导入到数据库中。

安装库

pip install openpyxl

完整代码

import argparse
import openpyxl


def excel_sql_output(excel_path, table_name, output_path):
    wb = openpyxl.load_workbook(excel_path)
    sheet = wb.active

    columns = []
    columns_names = ''
    with open(output_path, 'w', encoding='utf-8') as f:
        for i, row in enumerate(sheet.iter_rows(values_only=True)):
            if i == 0:
                columns = [v for v in row]
                columns_names = ','.join(columns)
            else:
                values = []
                for v in row:
                    if isinstance(v, str):
                        values.append(f"'{v}'")
                    elif v is None:
                        continue
                    else:
                        values.append(str(v))

                if len(values) != len(columns):
                    continue

                values = ','.join(values)
                insert_sql = f'INSERT INTO {table_name} ({columns_names}) VALUES ({values});\n'
                f.write(insert_sql)

    wb.close()


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        prog='excel导出sql插入文件',
        description='通过excel导出mysql数据库的插入sql文件，进行数据库快速导入',
    )
    parser.add_argument('-p', '--path', type=str, help='excel文件路径')
    parser.add_argument('-t', '--table', type=str, help='对应数据库的表名')
    parser.add_argument('-o', '--output', type=str, help='导出sql文件')

    args = parser.parse_args()

    path = args.path
    table = args.table
    output = args.output if args.output is not None else 'import.sql'

    if path is None:
        print('缺失excel路径')
    elif not path.endswith('.xlsx'):
        print('不是excel文件')
    elif table is None:
        print('表名必填')
    else:
        print('开始导出...')
        excel_sql_output(path, table, output)

调用信息

usage: excel导出sql插入文件 [-h] [-p PATH] [-t TABLE] [-o OUTPUT]

通过excel导出mysql数据库的插入sql文件，进行数据库快速导入

options:
  -h, --help            show this help message and exit
  -p PATH, --path PATH  excel文件路径
  -t TABLE, --table TABLE
                        对应数据库的表名
  -o OUTPUT, --output OUTPUT
                        导出sql文件

调用示例

python xx.py -p excel文件 -t 表名

excel示例

excel样例

请将数据放于 第一个数据表，并根据数据库的样式 修正标题。

4、excel双表查重

安装库

pip install pandas

完整代码

import argparse
import pandas as pd


def merge(excel_1_path, excel_2_path, column_1='', colum_2='', join='left'):
    df1 = pd.read_excel(excel_1_path)
    df2 = pd.read_excel(excel_2_path)

    if join == 'left':
        duplicates = df1[df1[column_1].isin(df2[colum_2])]
    elif join == 'right':
        duplicates = df2[df2[colum_2].isin(df1[column_1])]
    else:
        duplicates = pd.merge(df1, df2, on=[column_1, colum_2], how='inner')

    output_file = 'export.xlsx'
    duplicates.to_excel(output_file, index=True)


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        prog='双表查重',
        description='通过两个表格进行重复数据查询',
    )
    parser.add_argument('-p1', '--path1', type=str, help='excel表1路径')
    parser.add_argument('-p2', '--path2', type=str, help='excel表2路径')
    parser.add_argument('-c1', '--column1', type=str, help='表1列名')
    parser.add_argument('-c2', '--column2', type=str, help='表2列名')
    parser.add_argument('-j', '--join', type=str, help='输出结果 left 左表 right 右表 merge 合并，默认left')

    args = parser.parse_args()

    path1 = args.path1
    path2 = args.path2
    column1 = args.column1
    column2 = args.column2
    join = args.join if args.join is not None else 'left'

    if path1 is None or path2 is None:
        print('缺失excel路径')
    elif not path1.endswith('.xlsx') and not path2.endswith('.xlsx'):
        print('不是excel文件')
    elif column1 is None and column2 is None:
        print('列名必填')
    else:
        print('开始查询...')
        merge(path1, path2, column1, column2, join)

调用信息

usage: 双表查重 [-h] [-p1 PATH1] [-p2 PATH2] [-c1 COLUMN1] [-c2 COLUMN2] [-j JOIN]

通过两个表格进行重复数据查询

options:
  -h, --help            show this help message and exit
  -p1 PATH1, --path1 PATH1
                        excel表1路径
  -p2 PATH2, --path2 PATH2
                        excel表2路径
  -c1 COLUMN1, --column1 COLUMN1
                        表1列名
  -c2 COLUMN2, --column2 COLUMN2
                        表2列名
  -j JOIN, --join JOIN  输出结果 left 左表 right 右表 merge 合并，默认left

调用示例

python xx.py -p1 表1 -p2 表2 -c1 表1列名 -c2 表2列名

请确保数据在 第一个数据表。

5、excel多表指定列求和

安装库

pip install pandas

完整代码

import argparse
import os
import pandas as pd


def table_sum(excel_folder, column):
    file_list = os.listdir(excel_folder)
    total = 0
    for file in file_list:
        if not file.endswith('.xlsx') and not file.endswith('.xls'):
            continue
        excel_path = os.path.join(excel_folder, file)
        df = pd.read_excel(excel_path)
        sum_value = df[column].sum()
        total += sum_value

    print(f'计算结果：{total}')


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        prog='多表求和',
        description='通过放置excel的文件夹，求出相应列的总和',
    )
    parser.add_argument('-p', '--path', type=str, help='excel表文件夹')
    parser.add_argument('-c', '--column', type=str, help='列名')

    args = parser.parse_args()

    path = args.path
    column = args.column

    if path is None:
        print('缺失excel文件夹路径')
    elif not os.path.isdir(path):
        print('不是文件夹')
    elif column is None:
        print('列名必填')
    else:
        print('开始计算...')
        table_sum(path, column)

调用信息

usage: 多表求和 [-h] [-p PATH] [-c COLUMN]

通过放置excel的文件夹，求出相应列的总和

options:
  -h, --help            show this help message and exit
  -p PATH, --path PATH  excel表文件夹
  -c COLUMN, --column COLUMN
                        列名

调用示例

python xx.py -p 表格文件夹 -c 列名

请确保数据在 第一个数据表。

结尾

今天分享的五个表格自动化代码已经全部在上面啦！如果你喜欢本文，请点赞告诉我哦！