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

-*- coding:utf-8 -*- # 处理 excel 中的 area 为 Mysql insert 语句 import xlrd, 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 in range(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)))

 

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

标签：