PythonからExcel→JSON生成方法について

下記理想のJSONに向けて、Pythonを作りましたが
思うように作成できません。対応方法をご教示願います。
問題点は以下の通りです。
・returnの上位の{}が入っている。
・returnに""がついている。
・セルの中には、計算式を入れておりまして
そのまま表示される。ex)=$B$7&$S8
・Idが同じときには、Listの配列に追加したい。
↑実際のjsonでは、return自体が2つ出力されています。理想のjsonは、Listオブジェクトに2つ出力されています
↑"Id"ごとに（同じid）に理想のjsonが出力されます

理想のJSON

JSON
1    return {
2        "TableName": "human",
3        "Item": {
4            "Id": {"S":"12345"}, 
5            "List": {"L": [{"S":"book"}, {"S":"food"}]}, 
6        }
7    }

実際のJSON

JSON
1
2{
3    "return": {
4        "TableName": "human",
5        "Item": {
6            "Id": "=$B$7&$S7",
7            "List": [
8                {
9                    "S": "=$S7&\"-\"&$E7"
10                }
11            ]
12        }
13    }
14}{
15    "return": {
16        "TableName": "human",
17        "Item": {
18            "Id": "=$B$7&$S8",
19            "List": [
20                {
21                    "S": "=$S8&\"-\"&$E8"
22                }
23            ]
24        }
25    }
26}

python
1book = openpyxl.load_workbook('C:\temp\abc.xlsx')
2sheets = book['sheet1']
3path_w = 'output'
4
5
6for i in range(195):
7	Id = sheets.cell(row=i + 7, column=19 + 1).value
8	S = sheets.cell(row=i + 7, column=19 + 2).value
9
10	dictA = dict()
11	dictA.setdefault("return",{})
12
13	dict1 = {
14		"TableName" : "human"
15	}
16	dictA["return"].update(dict1)
17
18	dictB = dict()
19	dictB.setdefault("Item",{})
20	dictA["return"].update(dictB)
21
22	dictC = dict()
23	dictC.setdefault("Id",Id)
24	dictC.setdefault("List",[])
25	dictB["Item"].update(dictC)
26
27	dictD = {
28		"S": S 
29	}
30	dictC["List"].append(dictD)
31
32	with open(path_w, mode='a') as f:
33		f.write(json.dumps(dictA, sort_keys=False, ensure_ascii=False,indent=4))

t_obara

2020/11/12 06:31

エクセルを利用せずにすべてをPythonで行うという選択肢は取れないのでしょうか？

pokemonta

2020/11/12 07:27

やっぱこれは無理なのでしょうか？ Excelで文字列を生成していますので、これができれば省力化できると考えています。 =$B$7&$S7　部分をExcelで値に置き換えることは可能です

行動規範の内容に同意します

回答1件

ベストアンサー

openpyxlでブックを開くときにdata_only=Trueをつけると、計算の結果が得られるようになります。

また、理想のJSONとして書かれているものは、正しいJSONではないのでjson.dumps()では出力できません。
returnを含まないものをJSON文字列として作成して、'return 'を前にくっつければいいのではないでしょうか。

・Idが同じときには、Listの配列に追加したい。

Idごとに出力したいということならば、全データを読み込んだ後、
Idごとに出力する別のループが必要です。
(dict.setdefaultはわかりにくいので使わずに書きました)

python
1book = openpyxl.load_workbook('C:\temp\abc.xlsx', data_only=True)
2sheets = book['sheet1']
3path_w = 'output'
4
5data_dict = dict()
6
7for i in range(195):
8    Id = sheets.cell(row=i + 7, column=19 + 1).value
9    S = sheets.cell(row=i + 7, column=19 + 2).value
10
11    if Id not in data_dict:
12        data_dict[Id] = {
13            "TableName": "human",
14            "Item": {
15                "Id": {"S": Id}, 
16                "List": {"L": []}
17            } 
18        }
19
20    data_dict[Id]['Item']['List']['L'].append({"S": S})
21
22# ファイル出力
23with open(path_w, mode='a') as f:
24    for Id in data_dict.keys():
25        json_str = json.dumps(data_dict[Id], sort_keys=False, ensure_ascii=False, indent=4)
26        f.write('return ' + json_str + '\n')
27

投稿2020/11/12 08:25

編集2020/11/12 12:11

bsdfan

総合スコア4574

pokemonta

2020/11/12 08:51

ありがとうございます。 >理想のJSONとして書かれているものは、正しいJSONではないのでこれは、どういう意味でしょうか？（何をもって正しいjsonというのでしょうか？ >こちらについては、いまいちやりたいことがわからないため、回答できません。 Listにオブジェクトを2つ追加したいのに対して、実際にはreturnオブジェクト自体が2つ出力されます

bsdfan

2020/11/12 09:21

https://docs.python.org/3/library/json.html こちらの最初にも書かれていますが、JSONはRFCやECMAで規格が決まっているので、前にreturnとかがついたものはJSONとは違うものです。jsonのライブラリではそう言うものは直接扱えないです。 > Listにオブジェクトを2つ追加したいのに対して、実際にはreturnオブジェクト自体が2つ出力されますループごとにファイルに出力してますが、最後に一回出力するイメージですか？

pokemonta

2020/11/12 10:36

>ループごとにファイルに出力してますが、最後に一回出力するイメージですか？ "Id":ごとにファイル出力するイメージですね。私の質問がわるいですね

pokemonta

2020/11/12 12:25

これ、すげーですねありがとうございます。自分じゃこの発想なかっです

bsdfan

2020/11/12 13:10

よく使われるパターンだと思いますので、覚えておくといいかもです。私もどっかで見た似たようなのに、ちょこっと手を加えただけです。

pokemonta

2020/11/13 06:25

あれ、同じIDで複数JSONが生成されます