python把excel文件中的内容写入mysql

一、安装依赖包
pip3 install xlrd        # 读excel文档库，不支持.xlsx格式
pip3 install openpyxl       # excel文档库，支持.xlsx格式
pip install docx-mailmerge      # world文档库
pip install python-docx           # 支持.docx格式全解析
pip3 install pymysql
apt update
apt install ffmpeg

二、提取ppt文本

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ =
# python-pptx提取文本，Spire处理图片
# pip install python-pptx spire.presentation

from pptx import Presentation

def extract_ppt_text(file_path):
    prs = Presentation(file_path)
    text_content = []
    for slide in prs.slides:
        for shape in slide.shapes:
            if hasattr(shape, 'text_frame'):
                text_content.append(shape.text_frame.text)
    return '\n'.join(text_content)

# 示例用法
if __name__ == "__main__":
    path = "example.pptx"  # 替换为你的 PPTX 文件路径
    try:
        text = extract_ppt_text(path)
        print(text)
    except Exception as e:
        print(f"错误：{e}")

三、提取excel表格数据并把数据存到数据库

#!/usr/bin/python3
# -*- coding:utf-8 -*-

import xlrd
import pymysql
import os


def condb(sql):
    '''
    :param sql: 要执行的sql语句
    :return: 一条记录和多条记录
    '''
    conn = pymysql.connect(host='x.x.x.x', user='root', password='123456',
                           database='mysql', charset='utf8', port=3306)
    # 得到一个可以执行SQL语句并且将结果作为字典返回的游标(默认返回的结果为元组)
    local_cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    if 'SELECT' in sql:
        local_cursor.execute(sql)
        # 返回结果为字典格式,fetchone()返回一条记录
        local_one = local_cursor.fetchone()
        if local_one:
            # 返回结果为字典格式,fetchall()返回多条记录
            local_all = local_cursor.fetchall()
            local_all.insert(0, local_one)
        else:
            local_all = []
        local_cursor.close()
        conn.close()
        return local_one, local_all
    elif 'UPDATE' or 'INSERT' in sql:
        try:
            local_one = local_cursor.execute(sql)
            local_all = None
            conn.commit()
            local_cursor.close()
            conn.close()
            return local_one, local_all
        except Exception as f:
            # Rollback in case there is any error
            print(f)
            conn.rollback()
        local_cursor.close()
        conn.close()


# 指定excel文件
wb = xlrd.open_workbook('video.xls')
# 指定使用哪个表
sh = wb.sheet_by_name('youtube')
# 输出有效数据行数
# print(sh.nrows)
# 输出有效数据列数
# print(sh.ncols)
# 输出第一行第一列的值
# print(sh.cell(0,0).value)
# 输出第一行的所有值
# print(sh.row_values(0))
# 将数据和标题组合成字典
# print(dict(zip(sh.row_values(0),sh.row_values(1))))
# 遍历excel，打印所有数据
for i in range(sh.nrows):
    if i > 0:
        print(str(i).split('.')[0] + ' ###################')
        #    print(sh.row_values(i))
        res = dict(zip(sh.row_values(0), sh.row_values(i)))
        condb(
            'INSERT INTO `video` (title,youtube_id,category,tag,username,testurl,ctime,status,description) VALUES ("%s","%s","%s","%s","%s","%s",NOW(),1,"%s");' % (
                res.get('title'), res.get('youtube_id'), res.get('category'), res.get('tag'), '李四',
                'http://www.video.com/{}.mp4'.format(res.get('youtube_id')), '批量添加'))
        os.system(
            "ffmpeg -i '{}.mov' -s 1280*720 -b:v 1.5M zip/{}.mp4".format(str(i).split('.')[0], res.get('youtube_id')))

四、使用模板文件生成新的excel文件，并保留单元格样式

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ =

from openpyxl import load_workbook
from copy import copy
import time


def write_excel(Sheet, data, merge_target, wb):
    # ws = wb.active
    ws = wb[Sheet]
    insert_at = 2  # 从第3行开始插入
    count = 1
    for i in data:
        for col in range(1, ws.max_column + 1):
            old_cell = ws.cell(row=3, column=col)
            new_cell = ws.cell(row=insert_at + count, column=col)
            # 复制第三行的单元格样式
            if old_cell.has_style:
                new_cell.font = copy(old_cell.font)
                new_cell.border = copy(old_cell.border)
                new_cell.fill = copy(old_cell.fill)
                new_cell.number_format = copy(old_cell.number_format)
                new_cell.protection = copy(old_cell.protection)
                new_cell.alignment = copy(old_cell.alignment)
            if col == 1:
                new_cell.value = count
            else:
                if isinstance(i, list):
                    new_cell.value = i[col - 2]
                if isinstance(i, dict):
                    for k in i:
                        new_cell.value = i[k]
                        i.pop(k)
                        break
        count += 1

    # 遍历所有合并单元格区域
    for merged_range in ws.merged_cells.ranges:
        # 判断是否是我们要修改的合并区域
        if str(merged_range) == merge_target:
            # 获取合并区左上角单元格坐标
            min_col, min_row, max_col, max_row = merged_range.bounds
            start_cell = ws.cell(row=min_row, column=min_col)
            # 修改内容，保留原单元格样式（只变value）
            start_cell.value = f"学员信息表   {time.strftime('%Y.%m.%d  %H:%M')}"
            break


if __name__ == '__main__':
    # 指定要修改的合并单元格区域，例如 "A1:L1"
    merge_target = "A1:F1"
    # data = [
    #     {'name': '张三', 'sex': '男 ', 'age': 22, '专业': '计算机', '学制': 4},
    #     {'name': '李四', 'sex': '女', 'age': 22, '专业': '会计', '学制': 3},
    #     {'name': '王五', 'sex': '男', 'age': 23, '专业': '金融', '学制': 5},
    #     {'name': '赵六', 'sex': '女 ', 'age': 22, '专业': '计算机', '学制': 4}
    # ]
    data = [['张三', '男', 22, '计算机', 4], ['李四', '女', 22, '会计', 4], ['王五', '男', 23, '金融', 4],
            ['赵六', '女', 22, '计算机', 4]]
    wb = load_workbook('template.xlsx')
    # 在多个sheet中插入数据
    write_excel('Sheet1', data, merge_target, wb)
    write_excel('Sheet2', data, merge_target, wb)
    # 保存文件
    wb.save('change.xlsx')

参考链接：
        xlrd 和openpyxl