pandas的Excel文件读写（一）——组件要求与文件读取

一、组件要求

实现pandas的Excel文件读写，除了安装pandas外，还需要安装下列组件：

（1）xlrd：从指定的xls格式文件中读取数据

（2）xlwt：写入数据到指定的xls格式文件

（3）openpyxl：支持xlsx格式文件的读写

上述组件在Windows上的常用安装方法是在命令行中输入以下命令：

pip install ****

其中****即代表上述组件（库）的名字xlrd，xlwt，openpyxl。

此外，如果安装了Anaconda等打包了各种扩展库的Python，也可不安装上述库，直接用Anaconda等作为工程文件中所使用的Python环境。

二、read_excel()函数

2.1  read_excel()函数声明与调用

2.1.1  函数声明

def read_excel(io: {engine, parse},
               sheet_name: int = 0,
               header: int = 0,
               names: Any = None,
               index_col: Any = None,
               usecols: Any = None,
               squeeze: bool = False,
               dtype: ExtensionDtype | str | dtype | Type[str] | Type[float] | Type[int] | Type[complex] | Type[bool] | Type[object] | Dict[Hashable, ExtensionDtype | str | dtype | Type[str] | Type[float] | Type[int] | Type[complex] | Type[bool] | Type[object]] | None = None,
               engine: {__ne__} = None,
               converters: Any = None,
               true_values: Any = None,
               false_values: Any = None,
               skiprows: Any = None,
               nrows: Any = None,
               na_values: Any = None,
               keep_default_na: bool = True,
               na_filter: bool = True,
               verbose: bool = False,
               parse_dates: bool = False,
               date_parser: Any = None,
               thousands: Any = None,
               comment: Any = None,
               skipfooter: int = 0,
               convert_float: Any = None,
               mangle_dupe_cols: bool = True,
               storage_options: Dict[str, Any] | None = None) -> Any

2.1.2  函数调用

导入pandas库，从pandas库中调用read_excel()函数：

import pandas
data = pandas.read_excel('input.xls',...)
# data为存储读取结果的DataFrame对象变量，input.xls为读取的Excel文件，“...”表示选项

read_excel()函数的返回值为一个DataFrame对象，其结构类似于Excel中的表格形式。

2.2  read_excel函数参数详解

这里只列出一些比较常用的参数的说明。更具体的说明请参阅官方文档：pandas.read_excel — pandas 1.3.2 documentation

2.2.1  io

要读取的Excel文件及所在路径（相对于当前py文件的路径或绝对路径）。例如：

# -*- coding: utf-8 -*-
import pandas as pd
# 为简便起见，以下所有代码段均假定已作出上述声明
buff = pd.read_excel('data/input.xls')
                    # 相对路径，无论Windows还是Linux都建议用正斜杠
buff = pd.read_excel(r'C:\Users\Administrator\Desktop\input.xls')
                    # Windows下的绝对路径，字符串前要加字母r，否则反斜杠会被处理为转义字符

2.2.2  sheet_name

指定要读取的工作表序号（从0开始计算）或者名称，默认为0；None表示读取所有工作表。例如：

buff = pd.read_excel('input.xls')     # 读取第一张表（Excel下的默认名称为'Sheet1'）
buff = pd.read_excel('input.xls',sheet_name=2) 
                                      # 读取第三张表
buff = pd.read_excel('input.xls',sheet_name='Sheet3')
                                      # 读取名称为'Sheet3'的工作表
buff = pd.read_excel('input.xls',sheet_name=[0,2,'Sheet4',5])
                                      # 读取第1、3张表，名称为'Sheet3'的工作表和第6张表
buff = pd.read_excel('input.xls',sheet_name=None)
                                      # 读取'input.xls'下的全部工作表

2.2.3  header

指定某一行（从0开始计算，即Excel中显示的行号减1）作为表头，默认为0，即取第一行作为表头。数据为该行以下的数据。

如果工作表中的数据从第一行就开始而没有表头，则应该设定header=None。此时生成的DataFrame会自动将从0开始从左到右计算的列号（即0，1，2，……）作为表头。

例如，文件'result.xls'中的前若干行内容如下：

五年一班学生成绩
学号	姓名	语文	数学	英语	总分
50101	张三	95	98	98	291
50102	李四	93	100	96	289
50103	王五	90	96	95	281
……	……	……	……	……	……

表格中出现了标题行“五年一班学生成绩”，读取该表格时应去掉标题行从第二行开始，按照下列方式读取：

buff = pd.read_excel('result.xls',header=1)

2.2.4  index_col

指定某一列为索引列。默认为None，表示以从0开始编号的行号（即0，1，2，……）作为虚拟索引。指定索引列后，可以用这一列的具体数据作为行号检索。

index_col的值可以是从0开始编号的列号，也可以是具体的列名字符串。

当指定了某一列作为索引列时，这一列会被移动到DataFrame中最左侧的一列。

解释器不会检查索引列数据的唯一性，但是为了方便后续操作，建议索引列中的数据应该具有唯一性，即该一列中任何两项数据不能相同。

例如2.2.3节的表格中如需要以学号索引，可设定index_col='学号'。

如果索引列的具体数据是数字，在检索时要以数字格式检索。例如2.2.3节的表格中，在index_col='学号'时，如果要获取学号为50102的学生的语文成绩，应该使用下列语句：

print(buff['语文'][50102])

如果将50102加上引号，会提示无法匹配。

2.2.5  usecols

usecols用来指定读取的列。默认为None，表示读取所有列。有三种形式：

（1）以表头中指定的列名索引，对header=None的无表头表格不适用。例如2.2.3节的表格中，只读取学号和总成绩，则usecols=['学号','总分']。

（2）以数字列表形式的列序号索引，指定从0开始计算的列数，例如usecols=[1,3,5]表示读入第2、4、6列；

（3）以字符串形式的列序号索引，字符串格式为Excel中表示列序号的字母，连续的列范围用冒号连接，不连续的列用逗号分隔。例如usecols='B,D,F'表示读入第2、4、6列，usecols='B,D:F'表示读入第2和4~6列。

以2.2.3节的表格为例，需要读取学号和各学生语文、数学、英语三科的成绩，则下列语句等价：

buff = pd.read_excel('result.xls',header=1,usecols=['学号','语文','数学','英语'])
buff = pd.read_excel('result.xls',header=1,usecols=[0,2,3,4])
buff = pd.read_excel('result.xls',header=1,usecols='A,C:E')

2.2.6  names

names用于重命名指定的列。如果设定了header = None，则names用于定义列名。

names中的列表元素个数不能多于读入表格的列数（不含索引列）。

如果names中的列表元素个数（设为n）少于读入表格的列数（设为m），则对靠右的n列重命名，其余(m-n)列名字为空。

例如文件'result.xls'的内容是下列没有表头的表格：

50101	95	98	98
50102	93	100	96
50103	90	96	95
……	……	……	……

运行下列代码：

# -*- coding: utf-8 -*-
import pandas as pd

buff = pd.read_excel('result.xls',header=None,names=['学号','语文','数学','英语'])
print(buff)

输出为：（实际输出各列可能不对齐）

    学号  语文  数学  英语
0  50101   95    98   98
1  50102   93   100   96
2  50103   90    96   95
…     ……   ……    ……   ……

如果将names改成['语文','数学','英语']，则输出为：（实际输出各列可能不对齐）

          语文  数学  英语
0  50101   95    98   98
1  50102   93   100   96
2  50103   90    96   95
…     ……   ……    ……   ……

2.2.7  dtype

指定某些列的数据类型。例如dtype={'a':np.float64, 'b':np.int32}表示将名为'a'的一列设为64位浮点类型，将名为'b'的一列设为32位整数类型。

2.2.8  skiprows和skipfooter

skiprows：从第一行开始省略指定行数的数据。如果没有设定header（包括header=None），则skiprows=n与header=n等价。

skipfooter：从工作表有效范围的最后一行开始省略指定行数的数据。工作表的有效范围，是指以第一行第一列为左上角，覆盖所有非空单元格的最小矩形区域（可从Excel中的“分页预览”视图中看到）。

三、读取一个目录下的全部文件

（1）用os.listdir()读取指定目录file_dir下的文件列表。

（2）对文件列表下的每个文件single_file，依次用os.path.join(file_dir,single_file)获取文件名，作为pd.read_excel()的参数，依次进行处理。

目录下的文件应该全部为Excel文件（xls或xlsx格式），否则会报错。

例如，在当前目录内有一个目录temp，里面有若干个xls文件，现需要依次读取里面的所有文件，并分别原样写入文件'1.xls'、'2.xls'、……。代码如下：

# -*- coding: utf-8 -*-
import os
import pandas as pd

file_dir="temp"    #目录
all_file_list=os.listdir(file_dir)
i = 1
for single_file in all_file_list:
    single_data_frame=pd.read_excel(os.path.join(file_dir,single_file)) # 逐个读取
    single_data_frame.to_excel(str(i)+'.xls',index=False)   #写入
    i+=1