import xlrdsheet = xlrd.open_workbook('報(bào)價(jià)單.xlsx').sheet_by_index(1)def get_text(row,col):    # 判斷該坐標(biāo)是否是被合并的單元格    合并單元格的數(shù)據(jù)都在合并區(qū)域的第一個(gè)位置     for ces in sheet.merged_cells:        if (row >= ces[0] and row < ces[1]) and (col >= ces[2] and col < ces[3]):            return sheet.cell(ces[0],ces[2]).value # 取出合并區(qū)域的第一個(gè)數(shù)據(jù)    return sheet.cell(row,col).value #正常取出對(duì)應(yīng)數(shù)據(jù)keys = sheet.row_values(1) # 獲取所有的列標(biāo)題data = []for row in range(2,sheet.nrows):    dic = {}    for col in range(sheet.ncols):        k = keys[col] #確定key        res = get_text(row,col)        dic[k] = res # 確定值 并存儲(chǔ)    data.append(dic)print(data)# 序列化為jsonimport jsonjson.dump(data,open('test.json','wt'),ensure_ascii=False)

# 讀取文件work_book = xlrd.open_workbook('/xxx/xxx.xlsx')# 選取一個(gè)表sheet = work_book.sheet_by_index(0)# 遍歷表格數(shù)據(jù)datas = []for row in range(1,sheet.nrows):    temp_list =[]    for col in range(sheet.ncols):        value = sheet.cell_value(row,col)        temp_list.append(value)    datas.append(temp_list)# 打開(kāi)數(shù)據(jù)庫(kù)連接db = pymysql.connect(host='localhost', port=3306,                     user='username', passwd='password', db='database_name', charset='utf8')# 使用cursor()方法獲取操作游標(biāo)cursor = db.cursor()# SQL 插入語(yǔ)句sql = 'INSERT INTO SHOP(shop_code, shop_name, month) VALUES (%s,%s,%s)'try:    # 執(zhí)行sql語(yǔ)句    cursor.executemany(sql, datas)    # 提交到數(shù)據(jù)庫(kù)執(zhí)行    db.commit()except :    # 如果發(fā)生錯(cuò)誤則回滾    db.rollback()# 關(guān)閉游標(biāo)cursor.close()# 關(guān)閉數(shù)據(jù)庫(kù)連接db.close()