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

一、组件要求

实现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'中的前若干行内容如下：

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

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'的内容是下列没有表头的表格：

运行下列代码：

# -*- 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