#-*- 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)))