python读取exlce并转为json的学习笔记

# -*- coding: UTF-8 -*-  
# @Time :2021-08-14 10:40
# @File :index.py
# @Author : Poleung

'''
Excle表格的读写
'''
# 导入模块
import json
import xlrd

# print(dir(xlrd))
# print(xlrd.__version__)
# print(xlrd.__path__)

# 转义符的应用 r
# name = r"Poleung,\n数据可视化专著人。"
# print(name)

# 打开Excel文件读取数据
filename = r'C:\Users\Administrator\Desktop\客户推进表.xls'
data = xlrd.open_workbook(filename)  # 文件名以及路径，如果路径或者文件名有中文给前面加一个r不转义。
# print(data)

# 获取你要sheet的名称
sheet_name = data.sheet_names()
print(sheet_name)

# 根据sheet索引或者名称获取sheet内容
sheet = data.sheet_by_index(0)
print(sheet)

# 获取指定单元格里面的值
rs = sheet.cell_value(1, 0)  # 获取第2行,1列内容
print(rs)

# 获取整行和整列的值（数组）
rows = sheet.row_values(1)  # 获取第2行内容
cols = sheet.col_values(2)  # 获取第3列内容
print(rows)
print(cols)

# 获取sheet的名称，行数，列数
print(sheet.name, sheet.nrows, sheet.ncols)

# 获取整行和整列的值（列表）
rows = sheet.row_values(1)  # 获取第2行内容
print(rows)

tables = []
data = {}

for rown in range(sheet.nrows):
    array = {}
    array['name'] = sheet.cell_value(rown, 0)
    array['contacts'] = sheet.cell_value(rown, 1)
    array['mobile'] = sheet.cell_value(rown, 2)
    array['address'] = sheet.cell_value(rown, 3)
    array['category'] = sheet.cell_value(rown, 4)
    array['scale'] = sheet.cell_value(rown, 5)
    array['visit'] = sheet.cell_value(rown, 6)
    array['capital'] = sheet.cell_value(rown, 7)
    tables.append(array)

# print(tables)
# 删除标题头
del tables[0]
data['code'] = 0
data['msg'] = 'OK'
data['data'] = tables

# 转为JSON
res = json.dumps(data, ensure_ascii=False)
print(res)