手把手教你将Excel转json并将其转换为数据集使用

一、思路梳理

         对于一个包含各类Excel表（只含有表头和填写说明、下拉列表信息，不含有数据）的文件夹，想将其转换为数据集供大模型训练，用于对表格信息进行提问。

        除了需要将表中内容描述清楚，还需要将路径来源信息也描述到位。

        其中每张表按照sheet进行分解，每个sheet中包含data和merged_cells，data指的是单元格内容，merged_cells指的是合并单元格信息。

        路径信息也就是从文件夹0开始到本文件的完整路径。

        json对象共分为3类，sheet、table、dir。

        其中sheet包含类型（type）、文件名(name)、路径(path)、表信息(info)，其中info包含data和merged_cells。

        table包含类型（type）、序号(index)、文件名(name)、路径(path)、子文件(chirdren)，其中chirdren包含的是excel中的sheet数组。

        dir包含类型（type）、序号(index)、文件名(name)、路径(path)、子文件(chirdren)，其中chirdren包含的是子文件夹和子表数组。

二、文件结构

|--0 root

   |--1 文件夹1

        |--1 表1（sheet1、sheet2）

        |--2 表2（sheet1、sheet2、sheet3）

   |--2 文件夹2

三、下载所需依赖（pandas、openpyxl）

四、转换的具体代码如下（excel_to_json.py）

import os
import re
import json
import pandas as pd
from openpyxl import load_workbook

# 含有dir、table、sheet信息的json数组
jsons = []

def split_string_to_in_and_name(input_string):
    """拆分文件名中的索引和名称"""
    match = re.search(r"(\d+)\s*[-－]\s*(.+)", input_string)
    number_part = match.group(1)  # 提取数字部分
    text_part = match.group(2)    # 提取文本部分
    return {'index': number_part, 'name': text_part}


def get_merged_cells_info(sheet, hidden_cols, hidden_rows):
    """获取工作表中的合并单元格信息"""
    merged_cells_info = []
    for merged_range in sheet.merged_cells.ranges:
        start_row = merged_range.min_row - 1
        start_col = merged_range.min_col - 1
        end_row = merged_range.max_row - 1
        end_col = merged_range.max_col - 1

        # 计算大于当前start_row、start_col的隐藏行/列的个数
        hidden_rows_count_start = sum(1 for row in hidden_rows if row < start_row)
        hidden_cols_count_start = sum(1 for col in hidden_cols if col < start_col)

        # 更新去掉隐藏行列后的start_row、start_col
        fixed_start_row = start_row - hidden_rows_count_start
        fixed_start_col = start_col - hidden_cols_count_start

        # 计算去掉隐藏行列后的合并单元格的长宽
        len_row = end_row - start_row + 1 - sum(1 for row in hidden_rows if row <= end_row and row >= start_row)
        len_col = end_col - start_col + 1 - sum(1 for col in hidden_cols if col <= end_col and col >= start_col)

        if (len_row != 0 and len_col != 0):
            fixed_end_row = fixed_start_row + len_row - 1
            fixed_end_col = fixed_start_col + len_col - 1
            merged_cells_info.append({
                "start_row": fixed_start_row if fixed_start_row != -1 else 0,
                "start_col": fixed_start_col if fixed_start_col != -1 else 0,
                "end_row": fixed_end_row,
                "end_col": fixed_end_col,
                "value": sheet.cell(row=merged_range.min_row, column=merged_range.min_col).value
            })

    return merged_cells_info

def excel_to_json(file_path, dir_path):
    """将单个Excel文件转换为JSON，并保存到指定的输出文件夹"""
    # 加载Excel文件
    workbook = load_workbook(file_path, data_only=True)
    sheet_names = [sheet.title for sheet in workbook.worksheets if sheet.sheet_state == 'visible']

    # 构造输出JSON文件的路径
    base_name = os.path.basename(file_path)  # 获取文件名（带扩展名）
    base_name = os.path.splitext(base_name)[0] # 无扩展名

    names = split_string_to_in_and_name(base_name)
    table_path = dir_path + '/' + names['name']
    table = {
        'type': 'table',
        'index': names['index'],
        'name': names['name'],
        'path': table_path,
        'chirdren': sheet_names

    }
    jsons.append({'text': json.dumps(table, ensure_ascii=False)})
    print(table)
    # 读取所有Sheet的数据
    all_sheets_data = []
    for sheet_name in sheet_names:
        sheet = workbook[sheet_name]
        # 获取隐藏行和隐藏列的信息
        hidden_rows = {row - 1 for row in range(1, sheet.max_row + 1) if sheet.row_dimensions[row].hidden}
        hidden_cols = {ord(col) - ord('A') for col in sheet.column_dimensions if sheet.column_dimensions[col].hidden}

        # 使用Pandas读取Sheet数据（首行当做数据）
        df = pd.read_excel(file_path, sheet_name=sheet_name, header=None)

        # 去掉隐藏的行和列
        df = df.drop(list(hidden_rows), axis=0)  # 去掉隐藏的行
        df = df.drop(list(hidden_cols), axis=1)  # 去掉隐藏的列
        # 将列号重新排序
        df.columns = range(len(df.columns))

        # 将每一行数据转为字典
        sheet_data = df.to_dict(orient="records")

        # 清理字典中的NaN值
        cleaned_sheet_data = []
        for record in sheet_data:
            cleaned_record = {key: value for key, value in record.items() if not pd.isna(value)}
            cleaned_sheet_data.append(cleaned_record)

        # 获取合并单元格信息
        merged_cells_info = get_merged_cells_info(sheet, hidden_cols, hidden_rows)

        sheet_path = table_path + '/' + sheet_name
        # 将数据和合并单元格信息存储到字典中
        sheet = {
            'type': 'sheet',
            'name': sheet_name,
            'path': sheet_path,
            'info': {
                "data": cleaned_sheet_data,
                "merged_cells": merged_cells_info
            }
        }
        jsons.append({'text': json.dumps(sheet, ensure_ascii=False)})
        print(sheet)
        all_sheets_data.append(sheet)

    # print(f"Excel文件 {file_path} 已成功转换为JSON文件 {output_json_file}")
    return names['name']

def process_folder_recursive(folder_path):
    """递归处理指定文件夹及其子文件夹中的所有Excel文件，并将JSON文件保存到对应的输出文件夹"""
    if not os.path.exists(folder_path):
        print(f"文件夹 {folder_path} 不存在，请检查路径！")
        return

    # 遍历文件夹及其子文件夹
    for root, dirs, files in os.walk(folder_path):
        # 计算当前文件夹相对于输入根文件夹的相对路径
        relative_path = os.path.relpath(root, folder_path).replace('\\','/')
        # 构造输出文件夹路径
        cleaned_path = '/' + re.sub(r"\d+\s*[-－]\s*", "", relative_path)
        path = '/root' + cleaned_path.replace('.','') if cleaned_path != '/.' else '/root'
        names = split_string_to_in_and_name(root.split('\\')[-1])
        new_files = []
        new_dirs = []
        for dir_name in dirs:
            new_dir_names = split_string_to_in_and_name(dir_name)
            new_dirs.append(new_dir_names['name'])
        for file_name in files:
            if file_name.lower().endswith('.xlsx'):
                file_path = os.path.join(root, file_name)
                # print(f"正在处理文件：{file_path}")
                # 调用函数将Excel文件转换为JSON，并保存到对应的输出文件夹
                table_name = excel_to_json(file_path, path)
                new_files.append(table_name)
        
        dir = {
            'type': 'dir',
            'index': names['index'],
            'name': names['name'],
            'path': path,
            'children': new_files + new_dirs
        }
        jsons.append({'text': json.dumps(dir, ensure_ascii=False)})
    print("格式转换完成！")

if __name__ == '__main__':
    input_folder_path = input("输入文件夹路径：")
    process_folder_recursive(input_folder_path)
    
    # 输出文件名
    output_file = os.path.join(input_folder_path, 'output.json')

    # 将每个 JSON 对象写入到 JSON 文件
    with open(output_file, "w", encoding="utf-8") as file:
        json.dump(jsons, file, ensure_ascii=False, indent=4)

    print(f"JSON 数组已成功保存到 {output_file}")

五、执行excel_to_json.py

python excel_to_json.py

输入0 root文件夹的路径，待代码执行完毕后，即可在0 root文件夹下看到输出文件output.json