Python操作Excel

一、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的电子表格文件。

​ openpyxl并不支持操作Office 2007以前版本的Excel文件。

1.1 xls和xlsx和csv有什么区别

• 文件格式不同
  • xls是一个特有的二进制格式，其核心结构是复合文档类型的结构，而xlsx的核心结构是XML类型的结构，采用的是基于XML的压缩方式，使其占用的空间更小。xlsx中最后一个x的意义就在于此。
• 版本不同
  • xls是Excel 2007及以前版本生成的文件格式。
  • xlsx是Excel 2007及以后版本生成的文件格式。
• 兼容性不同
  • xlsx格式是向下兼容的，可兼容xls格式。
• csv是文本文件，用记事本就能打开。

1.2 安装第三方库

1.2.1 库的分类

• 系统模块

  • 是Python自带的模块，无需安装可以直接导入使用，如：math,random,csv,json,pickle,string…

• 自定义模块

  • 自定义的py文件就是一个模块，需要先创建文件，再导入

• 第三方模块

  • 需要先安装，然后再去使用，如:xlwt,xlrd,openpyxl…

1.2.2 Pycharm中安装

• 按住Ctrl+Shift+S，打开菜单，然后再左侧的菜单中选择 Project：xxx，然后选择Project Interpreter，再点击右侧的+，在新窗口中输入要安装的库名，并选择，最后点击Install Package，然后OK

1.2.3 命令行安装

打开cmd窗口，输入pip install xxx，安装，该方式默认安装最新版，

制定版本安装：pip install xxx==1.4.1

二、操作xls格式的Excel文件

做了解

2.1 读取Excel文件

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

• 导入xlrd包

import xlrd

• 打开Excel文件，得到一个工作簿对象

wb = xlrd.open_workbook(r"data/阿里巴巴2020年股票数据.xls")
print(wb)  # <xlrd.book.Book object at 0x0000024CBAEE5190>

打印结果是一个工作簿对象

• 获取工作簿中的工作表名

sheet_names = wb.sheet_names()
print(sheet_names)  # ['股票数据', 'test1', 'test2']

• 获取工作表对象

  • 方法一：根据工作表名称获取对象

  sheet1 = wb.sheet_by_name("股票数据")
  print(sheet1)  # Sheet  0:<股票数据>
  print(type(sheet1))  # <class 'xlrd.sheet.Sheet'>,类型为工作表对象
  

  • 方法二：根据索引获取对象

  sheet2 = wb.sheet_by_index(0)
  print(sheet2)  # Sheet  0:<股票数据>
  

• 获取单元格对象

  • xlrd和xlwt的操作中：
    • row（行），在Excel中，索引为1、2、3、4。。。；对应Python中索引为0、1、2、3。。。
    • col（列），在Excel中，索引为A、B、C、D。。。；对应Python中索引为0、1、2、3。。。
    • 语法：sheet.cell(row,col)

cell1 = sheet1.cell(0, 0)  # 对应单元格A1
print(cell1)  # text:'Date'
print(type(cell1))  # <class 'xlrd.sheet.Cell'>,类型为单元格对象

• 获取单元格的值

  • 方式一：先获取单元格对象，在通过Value属性访问单元格的值

  cell = sheet1.cell(0, 0)
  v1 = cell.value
  print(v1)  # Date
  print(type(v1))  # <class 'str'>
  

  • 方式二：直接调用cell_value(row,col)方法取值

  v2 = sheet1.cell_value(0, 0)
  print(v2)  # Date
  print(type(v2))  # <class 'str'>
  

• 获取某行的某几列的数据

  • 方法一：调用row_values(row,col1,col2)方法，获取row行的col1到col2的数据，左闭右开，返回结果是一个列表，其中的元素是字符串

  result1 = sheet1.row_values(0,