Excel to JSON，如果有价值的话 - 总和IT Python

发布于 2025-02-11 05:29:02 字数 1461 浏览 2 评论 0原文

我有带有数据的.xlsx文件。

我需要从.xlsx制作JSON文件。将来，此JSON将用于TG_BOT的功能。机器人会问用户“哪个品牌？” - “ bqq”，“哪个名字？” - “ C”，“哪个月？” - “ 2020年11月”，并且机器人给出了用户价格和总结。

我做了这样的smth ..但是它无法正常工作此外，它可以在device_name中重复，并且如果是脚本，则必须总结他们的device_price和device_summ（仅当月份相同时）

from openpyxl import Workbook, load_workbook
import json

wb = load_workbook('data.xlsx')
ws = wb.active
maxrows = ws.max_row
print(maxrows)

data = {}

for i in range(2, maxrows):



    device_name = ws[f'A{i}'].value
    device_brand = ws[f'B{i}'].value
    device_price_usd = ws[f'C{i}'].value
    device_summ_usd = ws[f'D{i}'].value
    month = ws[f'E{i}'].value

    datetime_date = month
    date_object = datetime_date.date()
    string_date = date_object.isoformat()
    if string_date == "2020-10-01":
        string_date = "oct_2020"
    elif string_date == "2020-11-01":
        string_date = "nov_2020"


    data[i] = {
        # "device_name": device_name,
        "device_brand": device_brand,
        "device_price_uah": device_price_uah,
        "device_price_usd": device_price_usd,
        "device_summ_uah": device_summ_uah,
        "device_summ_usd": device_summ_usd,
        "month": string_date,
        "number": i

    }




with open("data.json", "w") as file:
    json.dump(data, file, indent=4, ensure_ascii=False, default=str)

    enter code here

helps help plz）

原文

I have .xlsx file with data.

screenshot

I need to make json file from .xlsx.
In future this json will be used in func of TG_bot. Bot will ask user "Which brand?" - "BQQ", "Which name?" - "C", "Which month?" - "November 2020" and the bot gives user price and summ.

I made smth like this.. but it doesn't work correctly
Also it can be duplicates in device_name, and if it is script have to sum up their device_price and device_summ (only if month are the same)

from openpyxl import Workbook, load_workbook
import json

wb = load_workbook('data.xlsx')
ws = wb.active
maxrows = ws.max_row
print(maxrows)

data = {}

for i in range(2, maxrows):



    device_name = ws[f'A{i}'].value
    device_brand = ws[f'B{i}'].value
    device_price_usd = ws[f'C{i}'].value
    device_summ_usd = ws[f'D{i}'].value
    month = ws[f'E{i}'].value

    datetime_date = month
    date_object = datetime_date.date()
    string_date = date_object.isoformat()
    if string_date == "2020-10-01":
        string_date = "oct_2020"
    elif string_date == "2020-11-01":
        string_date = "nov_2020"


    data[i] = {
        # "device_name": device_name,
        "device_brand": device_brand,
        "device_price_uah": device_price_uah,
        "device_price_usd": device_price_usd,
        "device_summ_uah": device_summ_uah,
        "device_summ_usd": device_summ_usd,
        "month": string_date,
        "number": i

    }




with open("data.json", "w") as file:
    json.dump(data, file, indent=4, ensure_ascii=False, default=str)

    enter code here

Help plz )

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情场扛把子 2025-02-18 05:29:02

我认为，如果您将大熊猫用于这项工作，这很容易：
您可以使用以下代码之类的内容：

import pandas

excel_data_df = pandas.read_excel('records.xlsx', sheet_name='Employees')

json_str = excel_data_df.to_json()

print('Excel Sheet to JSON:\n', json_str)

此代码从此URL获取： https://www.journaldev.com/33335/33335/python-excel-to-json-conversion

在上面的URL谈论Python软件包中，称为“ Excel2json-3” JSON。
这是PYPI地址： https：//pypi.org/project/project/excelcelcelcelcelcelcelectel2json-3/

i think if you use pandas for this work, it is very easy:
you can use the something like the below code:

import pandas

excel_data_df = pandas.read_excel('records.xlsx', sheet_name='Employees')

json_str = excel_data_df.to_json()

print('Excel Sheet to JSON:\n', json_str)

this code get from this url :https://www.journaldev.com/33335/python-excel-to-json-conversion

in the above url talk about python package called "excel2json-3", you can also use this to convert excel to json.
this is pypi address: https://pypi.org/project/excel2json-3/

回复收藏 0 原文

~没有更多了~