python转化成excel_python转换excel成py文件

python转换excel成py文件

文件结构如下：

originExcelFolder放用来转换的excel文件。

targetPyFolder用来存放最后生产的py文件。

setting.py用来配置excel表到py的对应关系。

excel2py.py是主要的处理文件。

Excel文件，A注意表名字，B注意sheet名字。

代码如下：

setting.py

#!/usr/bin/env python

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

# setting.py # 定义生成py表的格式

Dict ={

"student":{

"id": {'type':int, 'default': 0},

"name":{'type':str, 'default': None},

"age":{'type':int, 'default': 0},

"sex":{'type':str, 'default': 'F'},

}

}

excel2.py

#!/usr/bin/env python

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

# transfer excel to py

# 处理excel表格生成对应的py文件

from openpyxl import Workbook

from openpyxl import load_workbook

import sys

import os

import time

from setting import Dict as tranDict

TARGET_PY_PATH = "targetPyFolder/"

ORIGIN_EXCEL_PATH = "./originExcelFolder/"

LOG_LEVEL_INFO = "INFO"

LOG_LEVEL_DEBUG = "DEBUG"

LOG_LEVEL_ERR = "ERROR"

class Excel2py:

def __init__(self):

self.init()

def init(self):

self.tempSheetName = None

self.tempFileName = None

self.target_dict = {}

def handleFiles(self):

# 切换到handleExcel所在的那个路径

os.chdir(sys.path[0])

# 加载已经存在的excel，(这时候excel与.py文件在同一级目录下)

self.tempFileName = ORIGIN_EXCEL_PATH + "test.xlsx"

wb = load_workbook(self.tempFileName)

# 所有的页的名字

sheetNames = wb.sheetnames

# 这里只取出了第一个页来处理，

self.tempSheetName = str(sheetNames[0])

ws = wb[self.tempSheetName]

# 表的关键字

key_dict = {}

# 目标字典

self.target_dict = {}

# 取出关键字

for column_index in range(2,ws.max_column+1):

val = ws.cell(row=2, column=column_index).value

val = str(val)

if val:

key_dict[column_index] = val

# 遍历表的每行

for row_index in range(3, ws.max_row+1):

temp_dict = {}

for index in key_dict:

val = ws.cell(row=row_index,column=index).value

# 类型处理

val = self.handleType(self.tempSheetName,key_dict[index],val)

item_id = int(ws.cell(row=row_index,column=1).value)

temp_dict[key_dict[index]] = val

self.target_dict[item_id] = temp_dict

self.writeToPy()

def handleType(self,sheetName,stype,value):

"""

数据类型处理

"""

typeDict = tranDict[sheetName].get(stype)

rtnValue = typeDict['default']

if value is None or value == "None":

rtnValue = typeDict['default']

elif not isinstance(value, typeDict['type']):

rtnValue = (typeDict['type'])(value)

# 异常处理--Todo

# 写处理日志-Todo

else:

rtnValue = value

return rtnValue

def writeToPy(self,):

"""

写成py文件

"""

fileName = TARGET_PY_PATH + self.tempSheetName + ".py"

if os.path.exists(fileName):

os.remove(fileName)

pyFile = open(fileName,'a')

ids = self.target_dict.keys()

ids.sort()

pyFile.write("\nDict = {\n\n")

for id in ids:

pyFile.write(str(id)+":"+str(self.target_dict[id]))

pyFile.write(",\n")

pyFile.write("\n}\n")

#pyFile.flush()

pyFile.close()

logInfo = '=========transfer sheet:' + self.tempSheetName + " success"

print '=========logInfo:', logInfo

self.tranlog(self.tempFileName, self.tempSheetName,LOG_LEVEL_INFO, logInfo)

def tranlog(self, excelName, sheetName, logLevel, logInfo):

"""

写转换日志

"""

logFile = "log.log"

pyFile = open(logFile,'a')

logMsg = '\n'+logLevel+"__"+str(time.time())+"__"+excelName.split('/')[-1]+"_"+sheetName+": "+logInfo

pyFile.write(logMsg)

# pyFile.flush()

pyFile.close()

if __name__ == "__main__":

Excel2pyObj = Excel2py()

Excel2pyObj.handleFiles()

注意：这里只处理了一个表的一个sheet，如果想要处理多个表多个sheet可稍作修改即可。