12: xlrd 处理Excel文件

1.1 xlrd处理.xlsx 文件

　　1、xlrd常用方法

#!/usr/bin/python
# coding:utf-8
# 用xlrd读取Excel文件基本用法
import sys
import xlrd
import json

# 设置编码格式
reload(sys)
sys.setdefaultencoding('utf-8')

# 1. 从Excel文件中读取出Book对象
data = xlrd.open_workbook('./data.xlsx')

# 2. 获取sheet页对象
sheet1 = data.sheet_by_index(0)

# 3. 获取sheet页的行数和列数
nrows = sheet1.nrows
ncols = sheet1.ncols

# 4. 获取单元格第0行的值（是一个列表）
row_data = sheet1.row_values(0)

# 5. 获取单元格第0列的值（是一个列表）
col_data = sheet1.col_values(0)

# 6. 获取单元格第0行第0列数据
cell_A1 = sheet1.cell(0, 0)

　　2、xlrd将 .xlsx读取成json格式

#!/usr/bin/python
# coding:utf-8
# 用xlrd读取Excel文件基本用法
import sys
import xlrd
import json

reload(sys)
sys.setdefaultencoding('utf-8')  # 设置编码格式
data = xlrd.open_workbook('./data.xlsx')   # 从Excel文件中读取出Book对象
sheet1 = data.sheet_by_index(0)
nrows = sheet1.nrows
idx = sheet1.row_values(0)  # 索引（即第一列数据：表头）
data = []  # 最终的数据列表
for i in range(1, nrows):  # 从第1行开始遍历循环所有行，获取每行的数据
    row_data = sheet1.row_values(i)
    row_data_dict = {}    # 组建每一行数据的字典
    for j in range(len(row_data)):  # 遍历行数据的每一项，赋值进行数据字典
        item = row_data[j]
        row_data_dict[idx[j]] = item
    data.append(row_data_dict)

print json.dumps(data, indent=4)


'''
[{
        "状态": "在职",
        "角色（负责人、员工）": "负责人",
        "手机号": 15134345678,
        "汇报人": "tom",
        "离职交接人": "",
        "二级部门": "AI实验室",
        "姓名": "闫峻",
        "三级部门": "",
        "工号": 406.0,
        "一级部门": "基础数据平台部",
        "性别": "男",
        "备注": ""
    },
    {
        "状态": "在职",
        "角色（负责人、员工）": "员工",
        "手机号": 15223457654,
        "汇报人": "jack",
        "离职交接人": "",
        "二级部门": "AI实验室",
        "姓名": "丁浩洋",
        "三级部门": "",
        "工号": 207.0,
        "一级部门": "基础数据平台部",
        "性别": "男",
        "备注": ""
    }
]
'''

　　3、自定义索引 转换 .xlsx文件为json

#!/usr/bin/python
# coding:utf-8
import sys
import xlrd
import json

reload(sys)
sys.setdefaultencoding('utf-8')  # 设置编码格式


def xlsx_to_json(filename, idx):
    '''
    :param filename: .xlsx 文件 路径
    :param idx:  字典 key 值（可以为表头）
    '''
    data = xlrd.open_workbook(filename)  # 从Excel文件中读取出Book对象
    sheet1 = data.sheet_by_index(0)
    nrows = sheet1.nrows
    data = []  # 最终的数据列表
    for i in range(1, nrows):  # 从第1行开始遍历循环所有行，获取每行的数据
        row_data = sheet1.row_values(i)
        row_data_dict = {}  # 组建每一行数据的字典
        for j in range(len(row_data)):  # 遍历行数据的每一项，赋值进行数据字典
            item = row_data[j]
            if idx[j] == 'jobid' or idx[j] == 'phone':  # 电话/工号 浮点数改成整数
                if isinstance(item, float):
                    item = int(item)
            row_data_dict[idx[j]] = item
        data.append(row_data_dict)
    return json.dumps(data)


idx = ['jobid','name','sex','level1','level2','level3','role','report_line','phone','usable','hand_people','tag']
ret = xlsx_to_json('data.xlsx', idx)
print ret

11111111111111