Python操作Excel(一)

Excel是Microsoft（微软）为使用Windows和macOS操作系统开发的一款电子表格软件。Excel凭借其直观的界面、出色的计算功能和图表工具，再加上成功的市场营销，一直以来都是最为流行的个人计算机数据处理软件。当然，Excel也有很多竞品，例如Google  Sheets、LibreOffice 、Calc、Numbers等， 这些竞品基本上也能够兼容Excel，至少能够读写较新版本的Excel文件，当然这些不是我们讨论的重点。

掌握用Python程序操作Excel文件，可以让日常办公自动化的工作更加轻松愉快，而且在很多商业 项目中，导入导出Excel文件都是特别常见的功能。

Python操作Excel需要三方库的支持，如果要兼容Excel 2007以前的版本，也就是 xls 格式的Excel文件，可以使用三方库xlrd 和xlwt ，前者用于读Excel文件，后者用于写Excel文件。如果使用较新版本的Excel，即操作 xlsx 格式的Excel文件，可以使用 openpyxl 库，当然这个库不仅仅可以操作Excel， 还可以操作其他基于Office Open XML的电子表格文件。

本章我们先讲解基于xlwt 和xlrd 操作Excel文件，大家可以先使用下面的命令安装这两个三方库以及配合使用的工具模块xlutils 。

pip install xlwt xlrd xlutils

1、读Excel文件

例如在当前文件夹下有一个名为“阿里巴巴2020年股票数据.xls”的Excel文件，如果想读取并显示该文件的内容，可以通过如下所示的代码来完成。

import xlrd

# 使用xlrd模块的open_workbook函数打开指定Excel文件并获得Book对象（工作簿）

wb = xlrd.open_workbook('阿里巴巴2020年股票数据.xls')

# 通过Book对象的sheet_names方法可以获取所有表单名称

sheetnames = wb.sheet_names()

print(sheetnames)

# 通过指定的表单名称获取Sheet对象（工作表）

sheet = wb.sheet_by_name(sheetnames[0])

# 通过Sheet对象的nrows和ncols属性获取表单的行数和列数

print(sheet.nrows, sheet.ncols)

for row in range(sheet.nrows):

     for col in range(sheet.ncols):

          # 通过Sheet对象的cell方法获取指定Cell对象（单元格）

          # 通过Cell对象的value属性获取单元格中的值

          value = sheet.cell(row, col).value 

          # 对除首行外的其他行进行数据格式化处理

          if row > 0:

               # 第1列的xldate类型先转成元组再格式化为“年月日”的格式

               if col == 0:

                  # xldate_as_tuple函数的第二个参数只有0和1两个取值

                  # 其中0代表以1900-01-01为基准的日期，1代表以1904-01-01为基准的日期

                   value = xlrd.xldate_as_tuple(value, 0)

                   value = f'{value[0]}年{value[1]:>02d}月{value[2]:>02d}日'

               # 其他列的number类型处理成小数点后保留两位有效数字的浮点数

              else:

                     value = f'{value:.2f}'

         print(value, end='\t')

    print()

# 获取最后一个单元格的数据类型

# 0 - 空值，1 - 字符串，2 - 数字，3 - 日期，4 - 布尔，5 - 错误

last_cell_type = sheet.cell_type(sheet.nrows - 1, sheet.ncols - 1)

print(last_cell_type) # 获取第一行的值（列表）

print(sheet.row_values(0))

# 获取指定行指定列范围的数据（列表）

# 第一个参数代表行索引，第二个和第三个参数代表列的开始（含）和结束（不含）索引

print(sheet.row_slice(3, 0, 5))

相信通过上面的代码，大家已经了解到了如何读取一个Excel文件，如果想知道更多关于 xlrd 模块的知识，可以阅读它的官方文档。

​​​​​​2、写Excel文件

写入Excel文件可以通过 xlwt 模块的Workbook 类创建工作簿对象，通过工作簿对象add_sheet 方法可以添加工作表，通过工作表对象的write 方法可以向指定单元格中写入数据，最后通过工作簿对象的save 方法将工作簿写入到指定的文件或内存中。下面的代码实现了将 5 个学生 3 门课程的考试成绩写入Excel文件的操作。

import random import xlwt

student_names = ['关羽', '张飞', '赵云', '马超', '黄忠']

scores = [[random.randrange(50, 101) for _ in range(3)] for _ in range(5)]

# 创建工作簿对象（Workbook）

wb = xlwt.Workbook()

# 创建工作表对象（Worksheet）

sheet = wb.add_sheet('一年级二班')

# 添加表头数据

titles = ('姓名', '语文', '数学', '英语')

for index, title in enumerate(titles):

      sheet.write(0, index, title)

# 将学生姓名和考试成绩写入单元格

for row in range(len(scores)):

     sheet.write(row + 1, 0, student_names[row])

     for col in range(len(scores[row])):

           sheet.write(row + 1, col + 1, scores[row][col])

# 保存Excel工作簿

wb.save('考试成绩表.xls')

3、调整单元格样式

在写Excel文件时，我们还可以为单元格设置样式，主要包括字体（Font）、对齐方式（Alignment）、边框（Border）和背景（Background）的设置， xlwt 对这几项设置都封装了对应的类来支持。要设置单元格样式需要首先创建一个XFStyle 对象，再通过该对象的属性对字体、对齐方式、边框等进行设定，例如在上面的例子中，如果希望将表头单元格的背景色修改为黄色，可以按照如下的方式进行操作。

header_style = xlwt.XFStyle()

pattern = xlwt.Pattern()

pattern.pattern = xlwt.Pattern.SOLID_PATTERN

# 0 - 黑色、1 - 白色、2 - 红色、3 - 绿色、4 - 蓝色、5 - 黄色、6 - 粉色、7 - 青色

pattern.pattern_fore_colour = 5

header_style.pattern = pattern

titles = ('姓名', '语文', '数学', '英语')

for index, title in enumerate(titles):

     sheet.write(0, index, title, header_style)

如果希望为表头设置指定的字体，可以使用Font 类并添加如下所示的代码。

font = xlwt.Font() # 字体名称

font.name = '华文楷体'

# 字体大小（20是基准单位，18表示18px）

font.height = 20 * 18

# 是否使用粗体

font.bold = True

# 是否使用斜体

font.italic = False 

# 字体颜色

font.colour_index = 1

header_style.font = font

如果希望表头垂直居中对齐，可以使用下面的代码进行设置。

align = xlwt.Alignment()

# 垂直方向的对齐方式

align.vert = xlwt.Alignment.VERT_CENTER

# 水平方向的对齐方式

align.horz = xlwt.Alignment.HORZ_CENTER

header_style.alignment = align

如果希望给表头加上黄色的虚线边框，可以使用下面的代码来设置。

borders = xlwt.Borders()

props = (

         ('top', 'top_colour'), ('right', 'right_colour'),

         ('bottom', 'bottom_colour'), ('left', 'left_colour')

)

# 通过循环对四个方向的边框样式及颜色进行设定

for position, color in props:

# 使用setattr内置函数动态给对象指定的属性赋值

     setattr(borders, position, xlwt.Borders.DASHED)

     setattr(borders, color, 5)

header_style.borders = borders

如果要调整单元格的宽度（列宽）和表头的高度（行高），可以按照下面的代码进行操作。

# 设 置 行 高 为 40px

sheet.row(0).set_style(xlwt.easyxf(f'font:height {20 * 40}'))

titles = ('姓名', '语文', '数学', '英语')

for index, title in enumerate(titles):

     # 设 置 列 宽 为 200px

     sheet.col(index).width = 20 * 200

     # 设置单元格的数据和样式

     sheet.write(0, index, title, header_style)

 需要资料的同学快快入群，免费资料拿到手软！