xlrd模块

​ xlrd是python中一个第三方的用于读取excle表格的模块,很多企业在没有使用计算机管理前大多使用表格来管理数据,所以导入表格还是非常常用的!

安装xlrd

<pre spellcheck="false" style="box-sizing: border-box; margin: 5px 0px; padding: 5px 10px; border: 0px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-weight: 400; font-stretch: inherit; font-size: 16px; line-height: inherit; font-family: inherit; vertical-align: baseline; cursor: text; counter-reset: list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; background-color: rgb(240, 240, 240); border-radius: 3px; white-space: pre-wrap; color: rgb(34, 34, 34); letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">pip install xlrd
</pre>

exlce结构分析

​ 一个excle表格包含多个sheet

​ 一个sheet中包含多行多列

​ 每个单元格具备唯一的行号和列号

常用函数

<pre spellcheck="false" style="box-sizing: border-box; margin: 5px 0px; padding: 5px 10px; border: 0px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-weight: 400; font-stretch: inherit; font-size: 16px; line-height: inherit; font-family: inherit; vertical-align: baseline; cursor: text; counter-reset: list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; background-color: rgb(240, 240, 240); border-radius: 3px; white-space: pre-wrap; color: rgb(34, 34, 34); letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">import xlrd Python学习交流群：1004391443

读取文件

work_book = xlrd.open_workbook("/Users/jerry/Desktop/公司机密数据.xlsx")

选取一个表

获取所有所有表格名称

print(work_book.sheet_names())

选择第2个 索引从0开始

sheet = work_book.sheet_by_index(1)

表格名称

print(sheet.name)

行数

print(sheet.nrows)

列数

print(sheet.ncols)

批量读取行数据

取出第6行的全部内容包含数据类型

print(sheet.row(6))

取出第6行的内容包含数据类型 从第3列开始获取

print(sheet.row_slice(6,start_colx=3))

取出第6行的内容包含数据类型 从第3列开始获取

print(sheet.row_slice(6,start_colx=4,end_colx=5))

获取该行所有数据类型 一数字表示

print(sheet.row_types(6))

print(sheet.row_values(6))

单元格的处理

print(sheet.cell(0,0).value) # 取值
print(sheet.cell(0,0).ctype) # 取类型
print(sheet.cell_value(2,0)) # 直接取值
print(sheet.row(0)[0]) # 先取行再取单元格
print(sheet.col(0)) # 第0列所有数据
print(sheet.col(0)) # 先取列再取单元格
print(sheet.cell_type(0,0))

单元格位置转换

print(xlrd.cellname(2,1))
print(xlrd.cellnameabs(0,2))
print(xlrd.colname(5))

时间类型转换

print(sheet.cell(6,5).value)

print(xlrd.xldate_as_datetime(sheet.cell(6,5).value,1))

</pre>

案例:

读取一个报价单 其第二个sheet包含合并单元格

文件地址: https://share.weiyun.com/5GaLY2m

<pre spellcheck="false" style="box-sizing: border-box; margin: 5px 0px; padding: 5px 10px; border: 0px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-weight: 400; font-stretch: inherit; font-size: 16px; line-height: inherit; font-family: inherit; vertical-align: baseline; cursor: text; counter-reset: list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; background-color: rgb(240, 240, 240); border-radius: 3px; white-space: pre-wrap; color: rgb(34, 34, 34); letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">import xlrd
sheet = xlrd.open_workbook("报价单.xlsx").sheet_by_index(1)
def get_text(row,col):
# 判断该坐标是否是被合并的单元格 合并单元格的数据都在合并区域的第一个位置
for ces in sheet.merged_cells:
if (row >= ces[0] and row < ces[1]) and (col >= ces[2] and col < ces[3]):
return sheet.cell(ces[0],ces[2]).value # 取出合并区域的第一个数据
return sheet.cell(row,col).value #正常取出对应数据
keys = sheet.row_values(1) # 获取所有的列标题
data = []
for row in range(2,sheet.nrows):
dic = {}
for col in range(sheet.ncols):
k = keys[col] #确定key
res = get_text(row,col)
dic[k] = res # 确定值 并存储
data.append(dic)
print(data)

序列化为json

import json
json.dump(data,open("test.json","wt"),ensure_ascii=False)
</pre>

xlwt模块

​ 是python中一个第三方的用于写入excle数据到表格的模块

​ 用代码来编写exlce是非常低效的 所以该模块了解即可。

<pre spellcheck="false" style="box-sizing: border-box; margin: 5px 0px; padding: 5px 10px; border: 0px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-weight: 400; font-stretch: inherit; font-size: 16px; line-height: inherit; font-family: inherit; vertical-align: baseline; cursor: text; counter-reset: list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; background-color: rgb(240, 240, 240); border-radius: 3px; white-space: pre-wrap; color: rgb(34, 34, 34); letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">import xlwt

创建工作簿

work = xlwt.Workbook()

创建一个表

sheet = work.add_sheet("员工信息数据")

创建一个字体对象

font = xlwt.Font()
font.name = "Times New Roman" # 字体名称
font.bold = True # 加粗
font.italic = True # 斜体
font.underline = True # 下划线

创建一个样式对象

style = xlwt.XFStyle()
style.font = font

写入标题

for k in keys:
sheet.write(0,keys.index(k),k,style)

写入数据

for i in infos:
for k in keys:
sheet.write(1 + infos.index(i),keys.index(k),label = i[k])

保存至文件

work.save("test.xls")
</pre>

面试题:

<tt-image data-tteditor-tag="tteditorTag" contenteditable="false" class="syl1561792161796" data-render-status="finished" data-syl-blot="image" style="box-sizing: border-box; cursor: text; color: rgb(34, 34, 34); font-family: "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei", "WenQuanYi Micro Hei", "Helvetica Neue", Arial, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: pre-wrap; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-style: initial; text-decoration-color: initial; display: block;"> image

<input class="pgc-img-caption-ipt" placeholder="图片描述(最多50字)" value="" style="box-sizing: border-box; outline: 0px; color: rgb(102, 102, 102); position: absolute; left: 187.5px; transform: translateX(-50%); padding: 6px 7px; max-width: 100%; width: 375px; text-align: center; cursor: text; font-size: 12px; line-height: 1.5; background-color: rgb(255, 255, 255); background-image: none; border: 0px solid rgb(217, 217, 217); border-radius: 4px; transition: all 0.2s cubic-bezier(0.645, 0.045, 0.355, 1) 0s;"></tt-image>

<pre spellcheck="false" style="box-sizing: border-box; margin: 5px 0px; padding: 5px 10px; border: 0px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-weight: 400; font-stretch: inherit; font-size: 16px; line-height: inherit; font-family: inherit; vertical-align: baseline; cursor: text; counter-reset: list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; background-color: rgb(240, 240, 240); border-radius: 3px; white-space: pre-wrap; color: rgb(34, 34, 34); letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">import xlrd
import pymysql

读取文件

work_book = xlrd.open_workbook("/xxx/xxx.xlsx")

选取一个表

sheet = work_book.sheet_by_index(0)

遍历表格数据

datas = []
for row in range(1,sheet.nrows):
temp_list =[]
for col in range(sheet.ncols):
value = sheet.cell_value(row,col)
temp_list.append(value)
datas.append(temp_list)

打开数据库连接

db = pymysql.connect(host='localhost', port=3306,
user='username', passwd='password', db='database_name', charset='utf8')

使用cursor()方法获取操作游标

cursor = db.cursor()

SQL 插入语句

sql = "INSERT INTO SHOP(shop_code, shop_name, month) VALUES (%s,%s,%s)"
try:
# 执行sql语句
cursor.executemany(sql, datas)
# 提交到数据库执行
db.commit()
except :
# 如果发生错误则回滚
db.rollback()

关闭游标

cursor.close()

关闭数据库连接

db.close()
</pre>