python读取excel文件并存入mysql数据库中

简介

---

常用的读写Excel的库：

• pandas
• openpyxl
• xlrd/xlwt/xlutils

使用它们都能够达到读写Excel的目的，但它们的侧重点又略有不同。

具体如下：

• pandas：数据处理最常用的分析库之一，可以读取各种各样格式的数据文件，一般输出dataframe格式，功能强大
• openpyxl：主要针对xlsx格式的excel进行读取和编辑
• xlrd库：从excel中读取数据，支持xls、xlsx
• xlwt库：对excel进行修改操作，不支持对xlsx格式的修改
• xlutils库：在xlwt和xlrd中，对一个已存在的文件进行修改
• xlwings：对xlsx、xls、xlsm格式文件进行读写、格式修改等操作
• xlsxwriter：用来生成excel表格，插入数据、插入图标等表格操作，不支持读取
• Microsoft Excel API：需安装pywin32，直接与Excel进程通信，可以做任何在Excel里可以做的事情，但比较慢

参考资料中对它们的性能进行了对比，如下：

这些模块的安装都简单，直接使用pip安装即可。

Excel文件

---

为了测试，建立demo.xls，内容如下：

本文主要实现文件的读写操作。

pandas读Excel

---

Pandas的名称来自于面板数据（panel data）和python数据分析（data analysis）。

pandas是一个数据处理的包，本身提供了许多读取文件的函数，像read_csv（读取csv文件），read_excel（读取excel文件）等，只需一行代码就能实现文件的读取。

pandas提供的数据结构DataFrame极大的简化了数据分析过程中一些繁琐操作, DataFrame是一张多维的表，可以把它想象成一张Excel表单。

读取代码如下：

#-- coding: utf-8 --

import pandas as pd

file_path = r'./demo.xls'
df = pd.read_excel(file_path, sheet_name = "Sheet1") # sheet_name不指定时默认返回全表数据

# 打印表数据，如果数据太多，会略去中间部分
print(df)

# 打印头部数据，仅查看数据示例时常用
print(df.head())

# 打印列标题
print(df.columns)

# 打印行
print(df.index)

# 打印指定列
print(df["name"])

# 描述数据
print(df.describe())

上面代码可以看到相应的输出，格式化后如下：

其中，read_excel的原型：read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None)

• io：excel文件，如果命名为中文，在python2.7中，需要使用decode()来解码成unicode字符串，例如： pd.read_excel(‘示例’.decode('utf-8))
• sheet_name：返回指定的sheet，如果将sheet_name指定为None，则返回全表，如果需要返回多个表，可以将sheet_name指定为一个列表，例如[‘sheet1’, ‘sheet2’]
• header：指定数据表的表头，默认值为0，即将第一行作为表头。
• usecols：读取指定的列，例如想要读取第一列和第二列数据：pd.read_excel(“example.xlsx”, sheet_name=None, usecols=[0, 1])

pandas写Excel

---

写一个全新的文件也很简单：

from pandas import DataFrame

data = { 'name': ['zs', 'ls', 'ww'], 'age': [11, 12, 13], 'gender': ['man', 'man', 'woman']}
df = DataFrame(data)
df.to_excel('new.xlsx')

生成的文件内容如下：

pandas修改Excel

---

以修改原Excel文件中gender列数据为例，把girl修改为female，boy修改为male：

#-- coding: utf-8 --

import pandas as pd
from pandas import DataFrame

file_path = r'./demo.xls'
df = pd.read_excel(file_path)

df['gender'][df['gender'] == 'girl'] = 'female'
df['gender'][df['gender'] == 'boy'] = 'male'

print(df)

DataFrame(df).to_excel(file_path, sheet_name='Sheet1', index=False, header=True)

注意，该脚本执行前要关闭操作的Excel文件，否则可能导致写入失败：

写入成功后的结果如下：

pandas增删Excel

---

当需要增加一行时或一列时，可以使用下面的方法：

• 新增行：df.loc[row_index] = [val1, val2, val3]
• 新增列：df[colo_name] = None

还是以上述文件为例，代码如下：

# 新增一行
df.loc[6] = [5, 'Eric', 'male', 20, '2021-5-18']

# 新增一列
df['favorite'] = None

# 写入数据文件
DataFrame(df).to_excel(file_path, sheet_name='Sheet1', index=False, header=True)

写入成功后的文件内容如下：

参考资料

---

https://cloud.tencent.com/developer/article/1661483