python读取xlsx文件并转化为 json 数据

from openpyxl import load_workbook
import os
from re import findall
import json


# 读取所有的sheet目录
def read_xlsx(path="./"):
    lis = os.listdir(path)
    base_xlsx = []
    for i in lis:
        result = findall('.xlsx', i)
        if len(result):
            base_xlsx.append(i)
    return base_xlsx


# 获取sheet的行
def get_sheet_columns(sheet):
    index = 1
    ret = []
    col = sheet.cell(1, index)
    while col.value:
        ret.append(col.value)
        index += 1
        col = sheet.cell(1, index)
    return ret


# 处理单个单元格
def handle(filepath):
    ret_obj = {}
    wb = load_workbook(filepath)
    sheet0 = wb.worksheets[0]
    columns = get_sheet_columns(sheet0)
    for sheet in wb.worksheets:
        sheet_obj = []
        for row in sheet.iter_rows(min_row=2):
            row_obj = {}
            for k in range(0, len(columns)):
                row_obj[columns[k]] = str(row[k].value)
            sheet_obj.append(row_obj)
        ret_obj[sheet.title] = sheet_obj

    return ret_obj


if __name__ == '__main__':
    # 读取到所有的sheet文件
    xlsx_filelist = read_xlsx()
    # 处理单个sheet
    for i in xlsx_filelist:
        obj = handle(i)
        result = json.dumps(obj)
        print(result)

读取结果为

{
  "Sheet1": [
    {
      "id": "1",
      "id_order": "123124",
      "id_user": "1",
      "money": "1200",
      "refund_money": "400",
      "time_create": "2022-09-04 00:00:00",
      "time_finish": "2022-09-05 00:00:00"
    },
    {
      "id": "1",
      "id_order": "123124",
      "id_user": "1",
      "money": "1200",
      "refund_money": "400",
      "time_create": "2022-09-04 00:00:00",
      "time_finish": "2022-09-05 00:00:00"
    },
    {
      "id": "1",
      "id_order": "123124",
      "id_user": "1",
      "money": "1200",
      "refund_money": "400",
      "time_create": "2022-09-04 00:00:00",
      "time_finish": "2022-09-05 00:00:00"
    },
    {
      "id": "1",
      "id_order": "123124",
      "id_user": "1",
      "money": "1200",
      "refund_money": "400",
      "time_create": "2022-09-04 00:00:00",
      "time_finish": "2022-09-05 00:00:00"
    },
    {
      "id": "1",
      "id_order": "123124",
      "id_user": "1",
      "money": "1200",
      "refund_money": "400",
      "time_create": "2022-09-04 00:00:00",
      "time_finish": "2022-09-05 00:00:00"
    },
    {
      "id": "1",
      "id_order": "123124",
      "id_user": "1",
      "money": "1200",
      "refund_money": "400",
      "time_create": "2022-09-04 00:00:00",
      "time_finish": "2022-09-05 00:00:00"
    },
    {
      "id": "1",
      "id_order": "123124",
      "id_user": "1",
      "money": "1200",
      "refund_money": "400",
      "time_create": "2022-09-04 00:00:00",
      "time_finish": "2022-09-05 00:00:00"
    }
  ]
}

 

# 有一个使用前提是， 无论是 sheet 名称 还是 表格头的名称， 都不建议是 中文， 因为 dumps 会对中文进行编码