python 解析excel并生成json格式数据保存到js文件中

需求

读取excel文件并生成json格式的js文件

代码

import json
import xlrd
#保存生成的js文件名列表
language_file_list = []

def parse_excel_to_language_js(file_path):

    """
    #!brief: convert to different language js files
    #!param: file path
    """
    # open excel
    excel = xlrd.open_workbook(file_path)
    # get first table
    table = excel.sheet_by_index(0)
    print(table.name, table.nrows, table.ncols)
    num_cols = table.ncols  # 列数
    num_rows = table.nrows  # 行数
    # 获取第一列的元素值列表，并且添加'TID_'
    tid = 'TID_'
    col_tid_list = table.col_values(0)
    col_tid_list = [tid + str(e) for e in col_tid_list]
    print(col_tid_list)
    # 获取第2列English的元素值的列表,用于填充其他单元格空白内容
    col_english_list = table.col_values(1)

    json_string = ''
    # 循环获取各列内容并输出到对应的js文件
    for col_idx in range(1, num_cols):
        col_elements_list = table.col_values(col_idx)  # 获取某列元素的列表
        for line_idx in range(1, num_rows):
            cell_type = table.cell(line_idx, col_idx).ctype  # 判断单元格类型 cell(rowx, colx)
            if cell_type == 0:
                col_elements_list[line_idx] = col_english_list[line_idx]

        if len(col_tid_list) == len(col_elements_list):
            # 两个列表合并组成字典类型
            dict_data = dict(zip(col_tid_list, col_elements_list))
            print(dict_data)
            # 输出json格式字符串
            json_string = json.dumps(dict_data, sort_keys=False, indent=4, separators=(',', ':'))
            print(json_string)

        # 创建js文件
        language_file_name = 'Language_'
        js_file_suffix = '.js'
        language_type = col_elements_list[0]
        language_file_name = language_file_name + str(language_type) + js_file_suffix
        language_file_list.append(language_file_name)
        print(language_file_list)
        with open(language_file_name, 'w', encoding='UTF-8') as f:
            f.write(json_string)
            f.close()

    return language_file_list

# test
# file_path = "D:\Tool\Python\PythonProjects\ExtractCharacter\Texts.xlsx"
# parse_excel_to_language_js(file_path)