Python常用Excell操作 ---- 基于xlrd、xlwt、xlutils

一. 介绍

Python常用于Excell操作的模块有Pandas、XlsxWriter、Xlrd与Xlwt等。每个模块都有自己的优劣势，具体使用哪个模块也因人而异。这里主要讲的是Xlrd、Xlwt、Xlutils的常规使用方法。

Xlrd，Xlwt 和 Xlutils是用Python处理Excel文档(.xls)的高效率工具。其中，Xlrd只能读取Xls；Xlwt只能新建Xls（不可以修改）；Xlutils能将xlrd.Book转为xlwt.Workbook，从而得以在现有xls的基础上修改数据，并创建一个新的xls，实现修改。

二. 安装与导入

pip install xlrd
pip install xlwt
pip install xlutils

详情参考博客：Python3中第三方模块库的安装

import  xlrd
import xlwt
from xlutils.copy import copy

三. Xlrd 读取数据

Xlrd常用操作：

#1.打开工作簿
workBook = xlrd.open_workbook('test.xlsx')

# 2.获取所有sheet名称，
sheet_name = workBook.sheet_names()  
print(sheet_name)           # 打印出各表名，如：['sheet1', 'sheet2']

# 3.根据sheet索引获取sheet名称
sheet1_name = workBook.sheet_names()[0]  
print(sheet1_name)          # 打印出该sheet的名称，如：‘sheet1’

# 4.获取sheet
Worksheet1 = workBook.sheet_by_name('sheet1') #根据sheet名字获取sheet内容
Worksheet2 = workBook.sheet_by_index(1)       #根据sheet索引获取sheet内容

# 5.返回指定单元格的数据
#             (行,列)
workSheet.cell(3,3)         #返回指定单元格的类型+数据, 如number：96.0 、 text:'字符串'
workSheet.cell(3,3).value   #返回指定单元格的数值, 如96.0
Worksheet.cell(1,0).ctype  #返回指定单元格的数据类型  ctype说明：empty为0、string为1、number为2、date为3、boolean为4、error为5

# 6.获取该工作表中的有效行数或列数
nrows = workSheet.nrows  #获取有效行数
ncols = workSheet.ncols  #获取有效列数

# 7.根据sheet名称获取整行和整列的值
Worksheet = data.sheet_by_name('sheet')
print(Worksheet.row_values(3))  #打印出sheet1中第3行的值 ,如： [1,2,3,4,'东','西','南','北']
print(Worksheet.col_values(3))  #打印出sheet1中第3列的值，如： [1,2,3,4,'东','西','南','北']

四. Xlwt 写入数据

Xlwt 常用操作：

# 1.创建一个xlwt工作簿
workbook= xlwt.Workbook()   
#   创建一个xlwt工作簿,编码为utf-8
workbook= xlwt.Workbook(encoding = 'utf-8')   

# 2.添加一个sheet表格 cell_overwrite_ok表示是否可以复写
worksheet= wb.add_sheet('表格1',cell_overwrite_ok=True)  

# 3.根据索引获取sheet表格
worksheet = worksheet.get_sheet(0)

# 3.向sheet表的1行1列中写入1
worksheet.write(0, 0, 1)   
#   向sheet表的1行1列中写入文字：“字符串”
worksheet.write(0, 0, '字符串')  

# 4.工作蒲另存为 “xlwt_test1.xls”
workbook.save('xlwt_test1.xls') 

若需要以一定格式写入某内容时可以参考以下代码：

# 1.为样式创建字体
def get_font():
    font = xlwt.Font()

    # 字体类型
    font.name = 'name Times New Roman'
    # 字体颜色
    font.colour_index = 4
    # 字体大小，11为字号，20为衡量单位
    font.height = 20 * 11
    # 字体加粗
    font.bold = False
    # 下划线
    font.underline = True
    # 斜体字
    font.italic = True

    return font

# 2.设置单元格对齐方式
def get_alignment():
    alignment = xlwt.Alignment()

    # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    alignment.horz = 0x02
    # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    alignment.vert = 0x01
    # 设置自动换行
    alignment.wrap = 1

    return alignment

# 3.设置边框
def get_borders():
    borders = xlwt.Borders()

    # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
    # 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
    borders.left = 1
    borders.right = 2
    borders.top = 3
    borders.bottom = 4
    borders.left_colour = 1
    borders.right_colour = 1
    borders.top_colour = 1
    borders.bottom_colour = 1

    return borders


# 4.主函数：

#创建工作薄
wb= xlwt.Workbook()
sheet= wb.add_sheet('页面1')

# 初始化样式
style_font = xlwt.XFStyle()
style_alignment = xlwt.XFStyle()
style_borders = xlwt.XFStyle()

# 获取设置字体格式
style_font.font = get_font()
# 获取设置对齐格式
style_alignment.alignment = get_alignment()
# 获取设置边框格式
style_borders.borders = get_borders()

# 将三种样式定义的格式作用与三个单元格
sheet.write(1, 0, '字体样式', style_font)
sheet.write(1, 2, '对齐方式', style_alignment)
sheet.write(1, 4, '边框样式', style_borders)

wb.save('style.xls')

若对写入内容的字体样式、对齐方式、边框样式同时有要求，只需要将上面的前三条函数合并即可。

五. Xlutils 读写转换

使用Xlrd是可以读取xls的数据，但是在读的同时无法对数据进行修改。
使用Xlwt是可以新建xls然后添加数据，但是不能对已有xls进行修改。
引入Xlutils解决此类问题，将xlrd.Book转为xlwt.Workbook。

Xlutils 常用操作：

# 1.打开已有xlsx文件
Old_Excel = xlrd.open_workbook('test.xlsx', formatting_info=True)

# 2.将操作文件对象拷贝，变成可写的workbook对象
New_Excel = copy(Old_Excel)

# 3.获得第一个sheet的对象
Worksheet = New_Excel.get_sheet(0)

# 4.写入数据
Worksheet.write(0, 0, '1行1列')
Worksheet.write(0, 1, '1行2列')

# 5.保存数据
New_Excel.save('new_test.xlsx')