python转json到excel_【Python】EXCEL转Json

在网上查阅了好几个EXCEL转Json的代码，有的是仅支持一个层级的Json(这个就不支持多层级的json)，有的太过复杂看的不是很懂，所以就自己写了一个目前符合自己使用的代码。

我先讲下实现的方式。

如果遇到一个如下的json格式，我该怎么存到excel方便读取出来呢？

{

"name": "haha",

"value": 12,

"floor_area": 43.5,

"categories": [

{

"id": 1,

"extra_property": "xixi",

"renovation_type": [

1,

2

],

"subcategories": [

{

"subcategory_id": 1,

"subcategory_value": 2

}

]

}

]

}

这是个多维json，存在excel中不是很好存放，读取出来也得花点心思，毕竟你不知道后期又会有什么格式的json出现。为了应对千奇百怪的json格式，我想到如下方式

上图中特别标注的，可以划分为三个部分，以下对这三个部分进行讲解。

区域一(功能校验区域)：

user：登陆的用户或操作的用户

real_code：预计接口执行操作后需要返回的code值，如200、401等

real_message：预计接口执行操作后需要返回的message，如“请求成功‘、”无操作权限“等

区域二(参数类型区域)：

传递参数的类型无外乎这几种：int、str、float、boolean。同样我们需要告诉代码这个参数是什么类型的参数，以免传递数据的时候发生参数类型错误。在代码中我做了处理，可以识别常用的四种类型(不区分大小写)。

区域三(参数区域)：

如果是数组类型的数据，用"[]"来标记，不同的数据存储在不同的单元格中，如：

看到图的时候你会问：为什么sheet里面会有参数的名称？

我们可以看出“categories”是个哈希，底下还存在不同的参数。为了知道哪些参数是在“categories”下的，我们可以用一张sheet去存储“categories”参，这样我们可以通过代码方便去找到。

有可能“categories”存在多组数据，所以我们需要用"[]"来告知代码。要读取那些数据，我们可以存储行号，不同的行号可以用顿号分隔

附上代码：

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

# !/usr/bin/python3

import os

import re

import xlrd

import json

import unicodedata

_author_ = 'garcia'

class DataCenter:

def __init__(self):

# Default File Path:

self.data_dir = os.getenv('AUTO_DATACENTER', '存放excel的文件夹地址')

self.filename = ''

self.path = ''

self.sheetNames = ''

@staticmethod

def is_number(val):

"""判断val是否是str"""

try:

return True

except ValueError:

pass

try:

unicodedata.numeric(val)

return True

except (TypeError, ValueError):

pass

def keep_integer_type_from_excel(self, value):

# Keep integer number as integer type. When reading from excel it has been changed to float type.

if self.is_number(value) and type(value) != str and value % 1 == 0:

return int(value)

else:

return value

def change_field_type(self, table, col, val):

# 字段类型转换

field_type = table.cell(0, col).value

val = self.keep_integer_type_from_excel(val)

try:

if val == '' or val is None:

pass

elif field_type.lower() == 'int':

return int(val)

elif field_type.lower() == 'float':

return float(val)

elif field_type.lower() == 'boolean':

return int(bool(val))

elif field_type.lower() == 'str' or field_type == '' or field_type is None:

return str(val)

else:

return '字段类型错误！'

except Exception as e:

print(e)

@staticmethod

def unic(item):

# Resolved Chinese mess code.

try:

item = json.dumps(item, ensure_ascii=False, encoding='UTF-8')

except UnicodeDecodeError:

try:

item = json.dumps(item, ensure_ascii=False, encoding='UTF-8')

except:

pass

except:

pass

# Convert json data string back

item = json.loads(item, encoding='UTF-8')

return item

@staticmethod

def get_sheet_names(wb):

"""

Returns the names of all the worksheets in the current workbook.

"""

sheet_names = wb.sheet_names()

return sheet_names

@staticmethod

def __convert_to_list(val):

"""转换字符串为list"""

value_list = re.split(',|，|、', val)

for i in range(len(value_list)):

value_list[i] = int(value_list[i])

return value_list

def get_table(self, sheet_name):

if self.path is None:

# Default Data Directory

file = os.path.join(self.data_dir, self.filename)

else:

file = os.path.join(self.path, self.filename)

try:

excel_date = xlrd.open_workbook(file)

# 得到excel的全部sheet标签值

self.sheetNames = self.get_sheet_names(excel_date)

my_sheet_index = self.sheetNames.index(sheet_name)

table = excel_date.sheet_by_index(my_sheet_index)

except Exception as e:

print(e)

return table

@staticmethod

def get_row_and_col(table):

"""获取列数、行数"""

total_row = table.nrows

total_col = table.ncols

return total_row, total_col

@staticmethod

def get_param(table, start_col, total_col):

param_list = [] # 获取参数

for col in range(start_col, total_col):

param = table.cell(1, col).value # 获取字段名

if param is None or param == '':

param_list.append(param_list[-1])

else:

param_list.append(param)

return param_list

def get_child_param(self, param, row, includeEmptyCells):

if param in self.sheetNames:

table = self.get_table(param)

child_total_row, child_total_col = self.get_row_and_col(table)

child_param = self.get_param(table, 1, child_total_col)

data_dic = {}

count = 0

for col in range(1, child_total_col):

# Solve issue that get integer data from Excel file would be auto-changed to float type.

val = self.change_field_type(table, col, table.cell(row, col).value)

param = child_param[count]

count += 1

if '[]' in param:

if val == '' or val is None:

pass

else:

param = param[:param.index('[')]

data_dic[param] = [] if param not in data_dic.keys() else data_dic[param]

if param in self.sheetNames:

val_list = self.__convert_to_list(val)

for i in range(len(val_list)):

data_dic[param].append(

self.get_child_param(param, val_list[i] - 1, includeEmptyCells))

else:

data_dic[param].append(val)

else:

if param in self.sheetNames:

if val is not None and val != '':

val_list = self.__convert_to_list(val)

for i in range(len(val_list)):

data_dic[param] = self.get_child_param(param, val_list[i] - 1, includeEmptyCells)

elif (val == '' or val is None) and includeEmptyCells == 'False':

pass

else:

data_dic[param] = val

return data_dic

def param_to_json(self, filename, sheet_name, includeEmptyCells, path=None):

"""

获取指定sheet中接口参数

:param filename: 文件名

:param sheet_name: 读取excel的sheet名称

:param path:文件路径

:return:

"""

try:

self.filename = filename

self.path = path

table = self.get_table(sheet_name)

total_row, total_col = self.get_row_and_col(table)

function_point_list = []

check_list = []

user_list = []

all_data_list = []

param_list = self.get_param(table, 4, total_col)

for row in range(2, total_row):

data_dic = {}

get_check_list = []

count = 0

for col in range(4, total_col):

# Solve issue that get integer data from Excel file would be auto-changed to float type.

val = self.change_field_type(table, col, table.cell(row, col).value)

param = param_list[count]

count += 1

if '[]'in param:

if val == '' or val is None:

pass

else:

param = param[:param.index('[')]

data_dic[param] = [] if param not in data_dic.keys() else data_dic[param]

if param in self.sheetNames:

val_list = self.__convert_to_list(val)

for i in range(len(val_list)):

data_dic[param].append(

self.get_child_param(param, val_list[i] - 1, includeEmptyCells))

else:

data_dic[param].append(val)

else:

if param in self.sheetNames:

if val is not None and val != '':

val_list = self.__convert_to_list(val)

for i in range(len(val_list)):

data_dic[param] = self.get_child_param(param, val_list[i] - 1, includeEmptyCells)

else:

pass

elif (val == '' or val is None) and includeEmptyCells == 'False':

pass

else:

data_dic[param] = val

print(data_dic)

get_check_list.append(self.keep_integer_type_from_excel(table.cell(row, 2).value))

get_check_list.append(self.keep_integer_type_from_excel(table.cell(row, 3).value))

check_list.append(get_check_list)

all_data_list.append(data_dic)

user_list.append(table.cell(row, 1).value)

function_point_list.append(table.cell(row, 0).value)

except Exception as e:

print(e)

# return all_data_list, function_point_list

return user_list, all_data_list, function_point_list, check_list

if __name__ == '__main__':

dc = DataCenter()

userlist, allList, FunctionPoint, checklist = dc.param_to_json('存放数据的excel名称', 'sheet名', 'False')

print(userlist, allList, FunctionPoint, checklist)

说到这，我们来讲讲她的缺点：

1、如果存在多张表的嵌套，代码执行的时间比较长