Using the XLRD module for Excel reading, Excel to JSON, Excel to MySQL INSERT statement

#-*-coding:utf-8-*-#working with area in Excel as a Mysql INSERT statementImportxlrd, JSON, codecs, OS#data = Xlrd.open_workbook (' 101.xls ')data = Xlrd.open_workbook ('C:\Users\Administrator\Desktop\changanyiyuan.xlsx')#table = data.sheets () [0]Table =data.sheet_by_index (0)#TryHospitaljson = Codecs.open ('C:\Users\Administrator\Desktop\changanyiyuan.json','A +','Utf-8') hospitaljson.truncate (0) Hospitaljson.write ('[')#write the array header firstHospitaljsoninsert= Codecs.open ('C:\Users\Administrator\Desktop\hospitalJsonInsert.json','A +','Utf-8') hospitaljsoninsert.truncate (0) Hospitaljsoninsert.write ('INSERT INTO department (HOSPITAL_ID, PID, name, Rank, create_time, modify_time) values')#Remove the first row of the table as the JSON key (JavaScript Object notation)attribute =table.row_values (0)#data except the first line is put into a dictJsonall =[]contents= {} forIndex_rinchRange (1, table.nrows): forIndex_cinchRange (Table.ncols): Contents[attribute[index_c]]= Table.cell (Index_r, index_c). Value#a property of cell cells is not a method        #Print ContentsHospitaljson.write ("%s,\n"%json.dumps (Contents,ensure_ascii=false,sort_keys=true, indent=4)) Hospitaljsoninsert.write ('(2265,-1, \ '%s\ ',%d, now (), now ()),'% (Table.cell (Index_r, 3). Value, Table.cell (Index_r, 5). Value))#jsonall.append (Json.dumps (Contents,ensure_ascii=false,sort_keys=true, indent=4))#print Hospitaljson.tell ()#hospitaljson.seek (0)#print Hospitaljson.tell ()Hospitaljson.seek (-2, OS. Seek_end) hospitaljson.truncate () Hospitaljson.write (']')#End of Array#Hospitaljson.write (Json.dumps (Contents,ensure_ascii=false,sort_keys=true, indent=4)) # less Ensure_ascii=False parameter can not be written in Chinese file? Hospitaljsoninsert.seek (-1, OS. Seek_end) hospitaljsoninsert.truncate () Hospitaljsoninsert.write (';')#hospitaljson.write ("%s"% Jsonall)hospitaljson.close () hospitaljsoninsert.close ( )#(' id ', ' hospital_id ', ' pid ', ' Name ', ' Rank ', ' create_time ', ' modify_time ')#write a MySQL statement#hospitaljsoninsert.write (' (2265,-1, \ '%s\ ',%s, now (), now ()) '% (Table.cell (Index_r, 3), Table.cell (Index_r, 5))) /c8>

Using the XLRD module for Excel reading, Excel to JSON, Excel to MySQL INSERT statement