Python 利用xlrd读取Excel文件

红太狼 2023-07-04 12:56 22阅读 0赞

## 基本步骤 ##

### 模块导入 ###

测试是否已经有了 `xlrd` 模块：

`import xlrd`

不报错说明模块安装成功，否则，需要先安装：

`pip install xlrd`

### 打开文件/获取文件数据 ###

现有一个关于保险行业术语的 Excel 文档：

*  文件名 `Insurance_terms.xlsx`
 *  文件中两个 sheet，名称为’GBT 36687~2018’和’JRT 0032-2015’

`excel = xlrd.open_workbook("GBT.xlsx")`

### 解析工作表 sheet ###

##### 获取所有 Sheet 的数量 #####

语法：

`excel.nsheets`

测试:

`print(excel.nsheets)` \-> 2

##### 获取所有 Sheet 的名称 #####

语法：

`excel.sheet_names()`

测试：

`print(excel.sheet_names())` \-> `['GBT 36687~2018','JRT 0032-2015']`

##### 获取某个 Sheet 对象 #####

语法1：

`excel.sheet_by_name('GBT 36687~2018')`

测试1：

`print(excel.sheet_by_name('GBT 36687~2018'))` \-> `<xlrd.sheet.Sheet object at 0x000001DF1F423BC8>`

语法2：

`excel.sheet_by_index(0)`

测试2：

`print(excel.sheet_by_index(0))` \-> `<xlrd.sheet.Sheet object at 0x000001DF1F505B48>`

##### 解析 sheet 中的汇总数据 #####

获取第一个Sheet：`sheetA = excel.sheet_by_index(0);`  
第一个Sheet name：`sheetA.name` \-> ‘JRT 0032-2015’  
第一个Sheet 行数：`sheetA.nrows` \-> 66  
第一个Sheet 列数：`sheetA.ncols` \-> 5

### 解析单元格 ###

#### 解析某一行数据 row ####

获取一行数据，包含数据类型和内容，语法：

`sheet.row(index)`

测试：

`print(sheetB.row(4))` \-> `[empty:'', text:'基础术语', text:'财产保险', text:'property insurance', empty:'', text:'以财产及其有关利益为保险标的保险。', text:'财产保险包括财产损失保险、责任保险、信用保险、保证保险等。']`

获取一行数据，只包含数据内容，语法：

`sheet.row_values(index)`

测试：

`print(sheetB.row_values(4))` \-> `['', '基础术语', '财产保险', 'property insurance', '', '以财产及其有关利益为保险标的保险。', '财产保险包括财产损失保险、责任保险、信用保险、保证保险等。']`

获取一行数据，只包含数据类型，语法：

`sheet.row_types(index)`

测试：

`print(sheetB.row_types(6))` \-> `array('B', [0, 1, 1, 1, 0, 1, 0])`

获取一行数据，第`start`列到第`end`列(start~end列)\[不含第end列\]，语法：

`sheet.row_values(index, start, end)`

测试，获取第 8 行前 3 列数据：

`print(sheetB.row_values(7, 0, 3))` \-> `['', '基础术语', '投保人']`

测试，获取第 158 行 第 2~5 列数据：

`print(sheetB.row_values(157, 2, 5))` \-> `['日常生活能力', 'activities of daily living', 'ADLs']`

可以通过行方法获取单元格数据，获取第5行第3个格子里的数据与类型：

`print(sheetB.row_slice(4,3,4))` \-> `[text:'property insurance']`

#### 解析某一列数据 col ####

语法：

`sheet.col_values(index)`

测试，获取第3列所有数据：

`sheetB.col_values(2)` \-> \[‘保险’,‘财产保险’,‘人身保险’…\]

#### 解析特定单元格 ####

获取单元格里的数据：

*  sheet.cell\_value(1, 2)
 *  sheet.cell(1, 2).value
 *  sheet.row(1)\[2\].value

`sheetB.cell_value(4,3)` \-> ‘property insurance’  
`sheetB.cell(4,3).value` \-> ‘property insurance’  
`sheetB.row(4)[3].value` \-> ‘property insurance’

获取单元格里的数据类型：

*  sheet.cell(1, 2).ctype
 *  sheet.cell\_type(1, 2)
 *  sheet.row(1)\[2\].ctype

`sheetB.cell(4,3).ctype` \-> 1  
`sheetB.cell_type(4,3)` \-> 1  
`sheetB.row(4)[3].ctype` \-> 1

### 完整示例代码 ###

上面都是基础代码，用来单个获取数据，如果要获取整个Excel中的数据，可以参照下面的完整代码：

import xlrd;
    book = xlrd.open_workbook("Insurance_terms.xlsx")
    sheet = book.sheets()[0]
    list = []
    for i in range(0, sheet.nrows):
        row = sheet.row_values(i)
        print(row);

['', '', '', '', '', '', '']
    ['', '保险术语中华人民共和国国家标准GB/T 36687—2018（2019-04-01 实施）', '', '', '', '', '']
    ['', '术语分类', '中国語', '英語', '', '用语解释', '备注']
    ['', '基础术语', '财产保险', 'property insurance', '', '以财产及其有关利益为保险标的保险。', '财产保险包括财产损失保险、责任保险、信用保险、保证保险等。']
    ['', '基础术语', '主险', 'main coverage  ', '', '可单独投保的保险产品。', '']

结果集里面，有空数据和标题，可以在循环里去掉：

import xlrd;
    book = xlrd.open_workbook("Insurance_terms.xlsx")
    sheet = book.sheets()[0]          # 第 1 个 sheet
    for i in range(3, sheet.nrows):   # 从第4行开始
        row = sheet.row_values(i,1)   # 从第2列开始
        print(row);

['基础术语', '财产保险', 'property insurance', '', '以财产及其有关利益为保险标的保险。', '财产保险包括财产损失保险、责任保险、信用保险、保证保险等。']
    ['基础术语', '基本险', 'basic policy', '', '可单独投保的保险产品。', '']
    ['合同要素', '保险合同关系人', 'privity to an insurance contract', '', '保险合同当事人之外的对于保险合同约定的利益享有独立请求权的人。', '注：包括被保险人和受益人。']

一般，我们可能需要将结果包装成 json 格式，于是：

import xlrd;
    book = xlrd.open_workbook("Insurance_terms.xlsx")
    sheet = book.sheets()[0]
    list = []
    for i in range(3, sheet.nrows):
        row = sheet.row_values(i)
        dic = { "type":row[1], "chinese":row[2],"english":row[3], "shortname":row[4],"description":row[5]}
        list.append(dic);
    print(list);

结果：

[{'type': '基础术语', 'chinese': '保险', 'english': 'insurance', 'shortname': '', 'description': '投保人根据合同约定，向保险人支付保险...'}, {'type': '基础术语', 'chinese': '财产保险', 'english': 'property insurance', 'shortname': '', 'description': '以财产及其有关利益为保险标的保险。'}, {'type': '基础术语', 'chinese': '人身保险', 'english': 'personal insurance', 'shortname': '', 'description': '以人的寿命和身体为保险标的保险。'}...]

单引号的 json 有点讨厌，可以处理一下：

import xlrd;
    import json;
    book = xlrd.open_workbook("Insurance_terms.xlsx")
    sheet = book.sheets()[0]
    list = []
    for i in range(3, sheet.nrows):
        row = sheet.row_values(i)
        dic = { "type":row[1], "chinese":row[2],"english":row[3], "shortname":row[4],"description":row[5]}
        list.append(dic);
    print(json.dumps(list));

如果文档是纯英文数据，那基本已经满足需要，如果含有中文，又想输出，你可能看到下面的结果：

`[{"type": "\u57fa\u7840\u672f\u8bed", "chinese": "\u4fdd\u9669", "english": "insurance", "shortname": "", "description": "\u6295\u4fdd\u4eba\u6839\u636e\u5408\u540c\u7ea6\u5b9a\uff0c\u5411\u4fdd ...`

还需要最后一步的处理，来阻止编码：

`json.dumps(list,ensure_ascii=False)`

完整的代码：

最终效果

[
    { 
    	"type": "基础术语",
    	"chinese": "保险",
    	"english": "insurance",
    	"shortname": "",
    	"description": "投保人根据合同约定..."
    }, { 
    	"type": "代位追偿",
    	"chinese": "权益转让书",
    	"english": "subrogation receipt",
    	"shortname": "",
    	"description": "被保险人岀具的证明已获得保险..."
    },
    ...
    ]