开发前置环境

• 安装Python（如Python 3.7）
• 安装Office办公软件（或WPS Office）
• 安装开发工具（如PyCharm，下载地址：https://www.jetbrains.com/pycharm/download）

1 xlrd模块读取Excel

xlrd为Python第三方模块，用来读取Excel表格数据（仅支持.xls格式）。

1.1 xlrd常用函数

import xlrd

# 读取Excel文件
data = xlrd.open_workbook("data1.xls")
# print(data.sheet_loaded(0)) # 加载索引为0的工作表  True
# data.unload_sheet(0) # 卸载
# print(data.sheet_loaded(0))  # 未被加载，False
# print(data.sheet_loaded(1))  # True
print(data.sheets()) # 获取全部sheet  [Sheet  0:<Sheet1>, Sheet  1:<Sheet2>, Sheet  2:<Sheet3>]
print(data.sheets()[0]) # 获取指定索引的工作表对象  Sheet  0:<Sheet1>
print(data.sheet_by_index(0)) # 根据索引获取工作表 Sheet  0:<Sheet1>
print(data.sheet_by_name('Sheet1'))  # 根据名字sheetname（区分大小写）获取工作表 Sheet  0:<Sheet1>
print(data.sheet_names()) # 获取所有工作表的name ['Sheet1', 'Sheet2', 'Sheet3']
# 查看共有多少个工作表
print(len(data.sheet_names())) # 返回所有工作表的名称组成的list的长度 3
print(data.nsheets) # 返回excel工作表的数量 3

data1.xls——Sheet1：

1.2 xlrd操作Excel行

import xlrd

data = xlrd.open_workbook("data1.xls")

# 操作excel行
sheet1 = data.sheet_by_index(0) # 获取第一个工作表
print(sheet1.nrows) # 获取sheet下的有效行数  11
sheet2 = data.sheet_by_index(1) # 获取第2个工作表
print(sheet2.nrows) # 获取sheet下的有效行数  10
print(sheet1.row(0)) # 输出第一行数据
# [text:'1月份销售额明细表', empty:'', empty:'', empty:'', empty:'', empty:'', empty:'']
print(sheet1.row(1)) # 输出第2行数据，返回该行单元格对象组成的列表
# [text:'日期', text:'货号', text:'颜色', text:'尺码', text:'原价', text:'折扣', text:'备注']
print(sheet1.row_types(1)) # 获取单元格的数据类型，返回指定行数据的数据类型
# （xlrd单元格数据类型表示：0：empty，1：string，2：number，3：date，4：boolean,5:error）
# array('B', [1, 1, 1, 1, 1, 1, 1])
print(sheet1.row(1)[2]) # 获取单元格的数据类型 text:'颜色'
print(sheet1.row(1)[2].value) # 获取单元格value 颜色
print(sheet1.row_values(1)) # 得到指定行单元格的值 ['日期', '货号', '颜色', '尺码', '原价', '折扣', '备注']
print(sheet1.row_len(1)) # 得到单元格的长度 7

1.3 xlrd操作Excel列

import xlrd

data = xlrd.open_workbook("data1.xls")

# 操作excel列
sheet1 = data.sheet_by_index(0) # 获取第一个工作表
print(sheet1.ncols) # 获取工作表有效列数
print(sheet1.col(1)) # 该列单元格对象组成的列表
# [empty:'', text:'货号', text:'X0001', text:'X0002', text:'X0003', text:'X0004', text:'X0005', text:'X0006', text:'X0007', text:'X0008', text:'X0009']
print(sheet1.col(1)[2]) # text:'X0001'
print(sheet1.col(1)[2].value) # X0001
print(sheet1.col_values(1)) # 返回该列所有单元格的value组成的列表
# ['', '货号', 'X0001', 'X0002', 'X0003', 'X0004', 'X0005', 'X0006', 'X0007', 'X0008', 'X0009']
print(sheet1.col_types(1)) # 获取该列单元格的数据类型 [0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]

1.4 xlrd操作Excel单元格

import xlrd

data = xlrd.open_workbook("data1.xls")

# 操作excel单元格
sheet1 = data.sheet_by_index(0) # 获取第一个工作表
print(sheet1.cell(1,2)) # text:'颜色'
# 获取单元格数据类型
print(sheet1.cell_type(1,2)) # 1
print(sheet1.cell(1,2).ctype) # 1
# 获取单元格的值
print(sheet1.cell(1,2).value) # 获取第二行，第三列单元格的值 颜色
print(sheet1.cell_value(1,2)) # 颜色

2 xlwt写入Excel

xlwt为Python第三方模块，用来写入Excel表格数据（仅支持.xls格式）。

2.1 xlwt写入Excel步骤

• 创建工作簿
• 创建工作表
• 填充工作表内容
• 保存文件

import xlwt

# 第一步：创建工作簿
wb = xlwt.Workbook()
# 第二步：创建工作表
ws = wb.add_sheet("CNY")
# 第三步：填充数据
ws.write_merge(0, 1, 0, 5, "2019年货币兑换表")

# 写入货币数据
data = (("Date", "英镑", "人民币", "港币", "日元", "美元"),
        ("01/01/2019", 8.722551, 1, 0.877885, 0.062722, 6.8759),
        ("02/01/2019", 8.634922, 1, 0.875731, 0.062773, 6.8601))
for i,item in enumerate(data):
    for j,val in enumerate(item):
        ws.write(i+2, j, val)

# 创建第二个工作表
wsImage = wb.add_sheet("image")
# 写入图片
wsImage.insert_bitmap("2021-07-01_172956.bmp", 0, 0)
# 第四步：保存
wb.save("2019-CNY.xls")

执行效果：

2.2 写入格式化（添加样式）

import xlwt

titleStyle = xlwt.XFStyle() # 初始化样式
titleFont = xlwt.Font()
titleFont.name = "宋体"
titleFont.bold = True # 加粗
titleFont.height = 11*20 # 字号
titleFont.colour_index = 0x08 # 字体颜色
titleStyle.font = titleFont

# 单元格对齐方式
cellAlign = xlwt.Alignment()
cellAlign.horz = 0x02 # 水平居中
cellAlign.vert = 0x01 # 垂直居中
titleStyle.alignment = cellAlign

# 边框
borders = xlwt.Borders()
borders.right = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DottED
titleStyle.borders = borders

# 背景颜色
dataStyle = xlwt.XFStyle()
bgColor = xlwt.Pattern()
bgColor.pattern = xlwt.Pattern.soLID_PATTERN
bgColor.pattern_fore_colour = 22 # 背景颜色
dataStyle.pattern = bgColor

# 第一步：创建工作簿
wb = xlwt.Workbook()
# 第二步：创建工作表
ws = wb.add_sheet("CNY")
# 第三步：填充数据
ws.write_merge(0, 1, 0, 5, "2019年货币兑换表", titleStyle) # 合并单元格，1~2行，1~6列

# 写入货币数据
data = (("Date", "英镑", "人民币", "港币", "日元", "美元"),
        ("01/01/2019", 8.722551, 1, 0.877885, 0.062722, 6.8759),
        ("02/01/2019", 8.634922, 1, 0.875731, 0.062773, 6.8601))
for i,item in enumerate(data):
    for j,val in enumerate(item):
        if j==0:
            ws.write(i+2, j, val, dataStyle)
        else:
            ws.write(i+2, j, val)

# 创建第二个工作表
wsImage = wb.add_sheet("image")
# 写入图片
wsImage.insert_bitmap("2021-07-01_172956.bmp", 0, 0)
# 第四步：保存
wb.save("2019-CNY.xls")

执行效果：

3. 实战：基于xlrd模块实现考试系统题库管理

3.1 Excel导入试题到数据库的操作步骤

• 通过xlrd模块读取Excel数据
• 通过pyMysqL模块连接数据库
• 组装数据、执行插入操作
• 关闭数据库连接

建表sql：

DROP TABLE IF EXISTS `question`;
CREATE TABLE `question`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) DEFAULT NULL COMMENT '题目',
  `questionType` varchar(255) DEFAULT NULL COMMENT '题型',
  `optionA` varchar(255) DEFAULT NULL COMMENT '选项A',
  `optionB` varchar(255) DEFAULT NULL COMMENT '选项B',
  `optionC` varchar(255) DEFAULT NULL COMMENT '选项C',
  `optionD` varchar(255) DEFAULT NULL COMMENT '选项D',
  `score` int(11) DEFAULT NULL COMMENT '分值',
  `answer` varchar(255) DEFAULT NULL COMMENT '正确答案',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHaraCTER SET = utf8;

MysqLhelper.py：

import pyMysqL

class dbhelper():
    def __init__(self,host,port,user,passwd,db,charset="utf8"):
        self.host=host
        self.port=port
        self.user=user
        self.passwd=passwd
        self.db=db
        self.charset=charset
    #创建一个链接
    def connection(self):
        #1. 创建连接
        self.conn = pyMysqL.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db,charset=self.charset)
        #2. 创建游标
        self.cur = self.conn.cursor()
    #关闭链接
    def closeconnection(self):
        self.cur.close()
        self.conn.close()
    #查询一条数据
    def getonedata(self,sql):
        try:
            self.connection()
            self.cur.execute(sql)
            result=self.cur.fetchone()
            self.closeconnection()
        except Exception:
            print(Exception)
        return result
    #查询多条数据
    def getalldata(self,sql):
        try:
            self.connection()
            self.cur.execute(sql)
            result=self.cur.fetchall()
            self.closeconnection()
        except Exception:
            print(Exception)
        return result
    #添加/修改/删除
    def executedata(self,sql):
        try:
            self.connection()
            self.cur.execute(sql)
            self.conn.commit()
            self.closeconnection()
        except Exception:
            print(Exception)
    #批量插入
    def executemanydata(self,sql,vals):
        try:
            self.connection()
            self.cur.executemany(sql,vals)
            self.conn.commit()
            self.closeconnection()
        except Exception as e:
            print(e)

data2.xls：

excelproject.py：

import xlrd

data = xlrd.open_workbook("data2.xls")
sheet = data.sheet_by_index(0) # 获取到工作表
questionList = []  # 构建试题列表

# 试题类
class Question:
    pass

for i in range(sheet.nrows):
    if i>1:
        obj = Question() # 构建试题对象
        obj.subject = sheet.cell(i, 1).value # 题目
        obj.questionType = sheet.cell(i, 2).value # 题型
        obj.optionA = sheet.cell(i, 3).value # 选项A
        obj.optionB = sheet.cell(i, 4).value # 选项B
        obj.optionC = sheet.cell(i, 5).value # 选项C
        obj.optionD = sheet.cell(i, 6).value # 选项D
        obj.score = sheet.cell(i, 7).value # 分值
        obj.answer = sheet.cell(i, 8).value # 正确答案
        questionList.append(obj)

print(questionList)
# 导入操作 pyMysqL pip install
from MysqLhelper import *
# 1.连接到数据库
db = dbhelper('127.0.0.1', 3306, "root", "123456", "test")
# 插入语句
sql = "insert into question(subject, questionType, optionA, optionB, optionC, optionD, score, answer) values (%s, %s, %s, %s, %s, %s, %s, %s)"
val = [] # 空列表来存储元组
for item in questionList:
    val.append((item.subject, item.questionType, item.optionA, item.optionB, item.optionC, item.optionD, item.score, item.answer))
#print(val)
db.executemanydata(sql, val)

执行效果：

Python Excel 操作 | xlrd+xlwt 模块笔记

Python 的pandas模块使用xlrd作为读取 excel 文件的默认引擎。但是，xlrd在其最新版本（从 2.0.1 版本开始）中删除了对 xls 文件以外的任何文件的支持。

xlsx files are made up of a zip file wrapping an xml file.


Both xml and zip have well documented security issues, which xlrd was not doing a good job of handling. In particular, it appeared that defusedxml and xlrd did not work on Python 3.9, which lead people to uninstall defusedxml as a solution, which is absolutely insane, but then so is sticking with xlrd 1.2 when you could move to openpyxl.

从官方的邮件中，说的应该是 xlsx 本身是由一个 zip 文件和 xml 的头文件构成的，但是 xml 和 zip 都有详细记录的安全问题，特别是，defusedxml和xlrd似乎在 Python 3.9 上不起作用，这导致人们卸载defusedxml作为解决方案，这绝对是疯了，但是，当然了，您也可以转移到openpyxl，或者仍然坚持使用xlrd 1.2。

$ conda search xlrd
Loading channels: done
# Name                       Version           Build  Channel
xlrd                           1.0.0          py27_0  conda-forge
xlrd                           1.0.0          py27_1  conda-forge
xlrd                           1.0.0          py35_0  conda-forge
xlrd                           1.0.0          py35_1  conda-forge
xlrd                           1.0.0          py36_0  conda-forge
xlrd                           1.0.0          py36_1  conda-forge
xlrd                           1.1.0          py27_1  pkgs/main
xlrd                           1.1.0  py27ha77178f_1  pkgs/main
xlrd                           1.1.0          py35_1  pkgs/main
xlrd                           1.1.0  py35h45a0a2a_1  pkgs/main
xlrd                           1.1.0          py36_1  pkgs/main
xlrd                           1.1.0  py36h1db9f0c_1  pkgs/main
xlrd                           1.1.0          py37_1  pkgs/main
xlrd                           1.1.0            py_2  conda-forge
xlrd                           1.2.0          py27_0  pkgs/main
xlrd                           1.2.0          py36_0  pkgs/main
xlrd                           1.2.0          py37_0  pkgs/main
xlrd                           1.2.0            py_0  conda-forge
xlrd                           1.2.0            py_0  pkgs/main
xlrd                           1.2.0    pyh9f0ad1d_1  conda-forge
xlrd                           2.0.1    pyhd3eb1b0_0  pkgs/main
xlrd                           2.0.1    pyhd8ed1ab_3  conda-forge

上面的问题将导致您在使用pandas调用 xlsx excel 上的read_excel函数时收到一个错误，即不再支持 xlsx filetype。

为了解决这个问题，你可以：

1. 安装 openpyxl 模块：这是另一个仍然支持 xlsx 格式的 excel 处理包。

2. 在pandas中把默认的 engine 由原来的xlrd替换成openpyxl。

   
   

# Install openyxl
pip install openpyxl

# set engine parameter to "openpyxl"
pd.read_excel(path, engine = ''openpyxl'')

接下来，介绍一下 Python 读写 Excel 需要导入的xlrd(读)，xlwd（写）模块的一些常用操作。

1. xlrd 模块

1.1 Excel 文件处理

• 打开 excel 文件

import xlrd
excel = xlrd.open_workbook("data.xlsx")  

• 获取并操作 sheet 工作表

sheet_names = excel.sheet_names()            # 返回book中所有工作表的名字, [''Sheet1'', ''Sheet2'', ''Sheet3'']    
excel.sheet_loaded(sheet_name or indx)   # 检查某个sheet是否导入完毕

# 以下三个函数都会返回一个 xlrd.sheet.Sheet() 对象
sheet = excel.sheet_by_index(0)             # 通过索引获取，例如打开第一个 sheet 表格
sheet = excel.sheet_by_name("sheet1")  # 通过名称获取，如读取 sheet1 表单
sheet = excel.sheets()[0]                        # 通过索引顺序获取

sheet.row_values(0) #获取第一行的数据
sheet.col_values(0) #获取第一列的数据
sheet.nrows                #获取总共的行数
sheet.ncols                #获取总共的列数

• 遍历所有行

for i in range(0, sheet.nrows):
    row_list = sheet.row_values(i) # 每一行的数据在row_list数组里

1.2 日期处理

import datetime
from xlrd import xldate_as_datetime

xldate_as_datetime(43346.0, 0).strftime(''%Y/%m/%d'') 
# ''2018/09/03''

2. xlwt 模块

2.1 创建 Book 工作簿(即 excel 工作簿)

import xlwt
workbook = xlwt.Workbook(encoding = ''utf-8'')    # 创建一个workbook并设置编码形式

2.2 添加 sheet 工作表

worksheet = workbook.add_sheet(''My Worksheet'')  # 创建一个worksheet

2.3 向工作表中添加数据并保存

worksheet.write(1,0, label = ''this is test'') # 参数对应行, 列, 值

workbook.save(''save_excel.xls'')  # 保存

Python 中常见的 TypeError 是什么？

2021-04-16

Python 列表、字典、元组的一些小技巧

2021-03-30

如何卸载 python setup.py install 安装的包？

2020-05-15

生物信息学 Python 入门之源码安装

2019-09-29

Python 文件与目录操作方法总结

2019-02-17