python读写excel模块pandas_python3 基于pandas读写Excel

基于pandas读取execl

读取文件类似

image.png

保存json文件：

image.png

具体文件

image.png

# -*- coding: UTF-8 -*-

import pandas as pd

import json

import os

#读取execl，保存为字典数组

def read_excel(file_path,sheet_name='lan'):

file = pd.read_excel(file_path, sheet_name=sheet_name)

# 设置空值

file = file.fillna("")

lan_list = [[] for i in range(2)]

for row in range(len(file.values)):

for colu in range(len(file.values[row])):

colu_data = file.values[row][colu]

# 空值跳过

if colu_data == "":

continue

if colu == 0:

code = colu_data

else:

dict_data = {}

dict_data['code'] = int(code)

dict_data['triggerMessage'] = colu_data.replace("\r", "")

#扩充数组

if colu>=len(lan_list):

lan_list.append([])

#每读取一个语言保存在list中

lan_list[colu-1].append(dict_data)

return lan_list

#保存为json本地文件

def write_json(lan_source_list,lan_title,save_path):

print("save_path", save_path)

if not os.path.exists(save_path): # 判断当前路径是否存在，没有则创建文件夹

os.makedirs(save_path)

#创建语言

for index in range(len(lan_title)):

json_file = save_path+os.sep+lan_title[index]+".json"

lan_data = lan_source_list[index]

# 保存json

with open(json_file, 'w', encoding="utf-8") as f:

json.dump(lan_data, f, ensure_ascii=False)

if __name__ == '__main__':

lan_title = ['zh', 'zh_tw', 'en', 'korean']

file_path = os.path.abspath('.')+os.sep+"lan.xlsx"

sheet_name = "lan"

save_path = os.path.abspath('.')+os.sep+"result"

write_json(read_excel(file_path=file_path),lan_title=lan_title,save_path=save_path)