将excel转为python的字典_python 读取excel 数据，并转为dict

代码如下

# -*- coding:utf-8 -*-

import xlrd

from datetime import datetime

from xlrd import xldate_as_tuple

class XlrdOp:

def __init__(self, fileName=None):

if fileName is None:

return

self.readbook = xlrd.open_workbook(fileName) #打开Excel文件，实例化为readbook

def get_sheet_names(self):

return self.readbook.sheet_names()

def get_dict(self):

data = {}

for i in self.get_sheet_names():

table = self.readbook.sheet_by_name(i)

row_num = table.nrows

col_num = table.ncols

s = []

key = table.row_values(0) # 这是第一行数据，作为字典的key值

if row_num <= 1:

print("没数据")

continue

j = 1

for z in range(row_num-1):

d = {}

for x in range(col_num): # 把key值对应的value赋值给key，每行循环

ctype = table.cell(j, x).ctype

cell = table.cell_value(j, x)

if ctype == 2 and cell % 1 == 0.0: # ctype为2且为浮点

cell = int(cell) # 浮点转成整型

cell = str(cell) # 转成整型后再转成字符串，如果想要整型就去掉该行

elif ctype == 3: # 日期类型转换，否则会显示为int

date = datetime(*xldate_as_tuple(cell, 0))

cell = date.strftime('%Y/%m/%d')

elif ctype == 4:

cell = True if cell == 1 else False

d[key[x]] = cell

j += 1

# 把字典加到列表中

s.append(d)

data[i] = s

return data

if __name__ == "__main__":

xp = XlrdOp('/Users/qinshixu/cmdb/static/files/NetDevice_3.xlsx')

print(xp.get_sheet_names())

print(xp.get_dict())

NetDevice_3.xlsx 文件内容

执行结果