Python convert Excel into py file

Python convert Excel into py file

The file structure is as follows:

Originexcelfolder the Excel file to be converted.

The Targetpyfolder is used to store the last-produced py file.

setting.py is used to configure the correspondence between Excel tables and py.

Excel2py.py is the main processing file.

　　　　

Excel file, a note table name, b note sheet name.

　　　　

The code is as follows:

setting.py

#!/usr/bin/env python#-*-coding:utf-8-*-# setting.py # Defines the format of the generated PY table 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# processing Excel table generating corresponding py file from OPENPYXL import Workbookfrom ope NPYXL import load_workbookimport sysimport osimport timefrom setting import Dict as Trandicttarget_py_path = "Targetpyfold er/"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 s Elf.tempfilename = None self.target_dict = {} def handlefiles (self): # switch to the path where Handleexcel is located os.ch Dir (sys.path[0]) # Loads an already existing Excel, (at this time Excel with a. py file in the same directory) Self.tempfilename = Origin_excel_path + "test.xlsx         WB = Load_workbook (self.tempfilename) # All the pages of the name sheetnames = wb.sheetnames # Here only the first page is removed to handle, Self.tempsheetname = str (sheetnames[0]) ws = Wb[self.tempsheetname] # Table of keywords Key_dict = {} # Target dictionary selF.target_dict = {} # remove keyword for column_index in range (2,ws.max_column+1): val = Ws.cell (row=2, col Umn=column_index). Value val = str (val) if val:key_dict[column_index] = val #                Traverse each row of the table 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 # type processing val = Self.handletype (self.te Mpsheetname,key_dict[index],val) item_id = Int (Ws.cell (row=row_index,column=1). Value) temp_d Ict[key_dict[index]] = val self.target_dict[item_id] = temp_dict self.writetopy () def handletype ( Self,sheetname,stype,value): "" "Data type Processing" "" Typedict = Trandict[sheetname].get (stype) r        Tnvalue = typedict[' default '] if value is None or value = = "None": Rtnvalue = typedict[' Default '] Elif Not Isinstance (ValUE, typedict[' type ']): Rtnvalue = (typedict[' type ') "(value) # exception handling--todo # Write processing log-todo        Else:rtnvalue = value return Rtnvalue def writetopy (self,): "" "written as py file" "" filename = target_py_path + Self.tempsheetname + ". PY" if os.path.exists (filename): Os.remove (fil ename) Pyfile = open (FileName, ' a ') IDs = Self.target_dict.keys () ids.sort () pyfile.write ("\ndi ct = {\ 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): "" "Write Conversion log "" "LOgfile = "Log.log" pyfile = open (LogFile, ' a ') logmsg = ' \ n ' +loglevel+ "__" +str (Time.time ()) + "__" +excelname.sp Lit ('/') [ -1]+ "_" +sheetname+ ":" +loginfo Pyfile.write (logmsg) # Pyfile.flush () pyfile.close () if __name   __ = = "__main__": Excel2pyobj = Excel2py () excel2pyobj.handlefiles ()

　　

Note: Only one sheet of a table is processed here, and if you want to work with multiple tables multiple sheet can be modified slightly.

　　

Python convert Excel into py file