xlrd MySQL_使用 xlrd 模块实现对excel 的读取、excel转json 、excel 转 mysql insert 语句...

#-*- coding:utf-8 -*-#处理 excel 中的 area 为 Mysql insert 语句

importxlrd, 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)#try

hospitalJson = codecs.open('C:\Users\Administrator\Desktop\changanyiyuan.json','a+','utf-8')

hospitalJson.truncate(0)

hospitalJson.write('[') #先写入数组头

hospitalJsonInsert= 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')#取出表格的第一行作为 json 的 key (javascript object notation)

attribute =table.row_values(0)#除第一行以外的数据放入一个 dict

jsonAll =[]

contents={}for index_r in range(1,table.nrows):for index_c inrange(table.ncols):

contents[attribute[index_c]]= table.cell(index_r, index_c).value #cell 单元格的一个属性并非方法

#print contents

hospitalJson.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(']') #数组结束

#hospitalJson.write(json.dumps(contents,ensure_ascii=False,sort_keys=True, indent=4)) # 少了 ensure_ascii=False 参数就不能写中文进文件？

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`)#写成 mysql 语句#hospitalJsonInsert.write('( 2265, -1, \'%s\' ,%s, now(), now())'%(table.cell(index_r, 3), table.cell(index_r, 5)))