Python实现动态读取Excel表树形结构内容并存储到数据库⽬录
⼀、概述
⼯作需要，需要从Execl读取⼤量的数据并保存到Mysql数据库，数据类型成树状图结构(只有三级)，⼀级⼆级 只需要名称，三级是具体的数据。废话不多说，直接上货。
开发环境：window10
Python版本：3.6
⼆、依赖库安装
pip install openpyxl
三、Execl内容
python怎么读入excel
数据格式如下所⽰：
四、内容分析
分析Excel内容的结构如下：
五、代码
1. ImportBaseCheckInfo()和 manyImportThreeInfo() 两个接⼝ 把读取到的数据分批保存到数据库。
# coding:utf-8
import openpyxl
from service.admin import BaseCheckItemsManageService as manageService
import os
sheeldata=None
rootList = []
_crow=0
def readExel(filepath):
try:
# filename = r'C:\Users\user\Desktop\tmp\⼯作簿1.xlsx'
inwb = openpyxl.load_workbook(filepath)  # 读⽂件
global sheeldata
sheetnames = _sheet_names()  # 获取读⽂件中所有的sheet，通过名字的⽅式
sheeldata = _sheet_by_name(sheetnames[0])  # 获取第⼀个sheet内容
# sheeldata = _sheet_by_name('Sheet1')  # 获取第⼀个sheet内容
# 获取sheet的最⼤⾏数和列数
rows = sheeldata.max_row
cols = sheeldata.max_column
for r in range(1,rows+1):
if 1 == len(ll(r, 1).value)):
# ll(r, 2).value,
itemlsit=getTwoNodeInfo(r)
data_dic={
'base':ll(r, 2).value,
'items': itemlsit
}
rootList.append(data_dic)
if len(rootList)>0:
writeDatabase()
print("完成数据读取")
except Exception as e:
print("ReadWriteData readExel()  Exception: %s" % e)
print("数据读取失败")
# 读取⼆级节点数据
def getTwoNodeInfo(cr):
row=cr + 1
tow_lists = []
for r in range(row, sheeldata.max_row+1):
if 3 == len(ll(r, 1).value)):
crow,itemlist=addNodes(r)
data_dic={
'node': ll(r, 2).value,
'items': itemlist
}
tow_lists.append(data_dic)
elif r>crow:
return tow_lists
return tow_lists
# 读取三级节点数据
def addNodes(row):
r=row+1
node_lists = []
global _crow
for cr in range(r, sheeldata.max_row+1):
if 5 == len(ll(cr, 1).value)):
node_dic = {}
node_dic["itemname"] = ll(cr, 2).value
node_dic["score"] = ll(cr, 3).value
node_dic["criteria"] = ll(cr, 4).value
node_dic["check"] = ll(cr, 5).value
node_dic["evaluate"] = ll(cr, 6).value
node_lists.append(node_dic)
_crow =cr
else:
return _crow,node_lists
return _crow,node_lists
def writeDatabase():
try:
for root in rootList:
if 'base' in root and 'items' in root:
base_uuid=writeData(root['base'],"")
if len(root['items'])>0 and base_uuid!="" and base_uuid !=None:                    witeTwoData(root['items'],base_uuid)
print("导⼊数据成功")
except Exception as e:
print("ReadWriteData writeDatabase()  Exception: %s" % e)
print("数据导⼊失败")
print("数据导⼊失败")
def writeData(name,uuid):
params = {}
params["name"] = name
params["sceneId"] = 1
params["parentUUID"]=uuid
c,uuid= manageService.ImportBaseCheckInfo(params)
return uuid
def witeTwoData(itemlist,base_uuid):
flg=False
for item in itemlist:
if 'node' in item and 'items' in item:
mg_uuid = writeData(item['node'], base_uuid)
if len(item['items']) > 0 and mg_uuid != "" and mg_uuid != None:                flg = witeThreeData(item['items'], mg_uuid)
return flg
def witeThreeData(itemlist,uuid):
paramslist=[]
if len(itemlist)>0:
for item in itemlist:
params = {}
params["name"] = item["itemname"]
params["parentUUID"] = uuid
params["desc"] = item["evaluate"]
params["score"] = item["score"]
params["according"] = item["check"]
params["solution"] = item["criteria"]
paramslist.append(params)
return manageService.manyImportThreeInfo(paramslist)
if __name__ == '__main__':
filename = r'C:\Users\user\Desktop\tmp\⼯作簿1.xlsx'
# readExel(filename)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。