-
Notifications
You must be signed in to change notification settings - Fork 7
/
jsonToExcel.py
executable file
·173 lines (142 loc) · 4.84 KB
/
jsonToExcel.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
#!/usr/bin/env python3
import json
import os
from argparse import ArgumentParser
import pandas as pd
parser = ArgumentParser(description='Create an Excel file from a list of json translation files.')
parser.add_argument(
"-p",
"--primary",
dest="primary",
help="Primary lang (default '%(default)s')",
default="en"
)
parser.add_argument(
"-i",
"--input-dir",
dest="input_dir",
help="Dir of the json files (default '%(default)s')",
default="translations/"
)
parser.add_argument(
"-n",
"--name",
dest="name",
help="Name of the excel sheet (default '%(default)s')",
default="Translations"
)
parser.add_argument(
"-l",
"--locales",
dest="locales",
help="(Optional) Comma separated list of the locales to consider (es. it,en,de,fr..)",
default="*"
)
parser.add_argument(
"-o",
"--output-file",
dest="output_file",
help="Name of the output file (default '%(default)s')",
default="output/translations.xlsx"
)
parser.add_argument(
"-k",
"--key-name",
dest="key_name",
help="Name of the Excel column storing the keys (default: '%(default)s') ",
default="key"
)
# Setup variables from arguments
args = parser.parse_args()
primary = args.primary
input_dir = args.input_dir
output_file = args.output_file.strip(' ')
name = args.name
# Get list of translation files in the input folder
cwd = os.getcwd()
os.chdir(input_dir)
files = os.listdir('.')
# Filter out the file not in the locales argument (if provided)
if args.locales != '*':
locales = args.locales.split(',')
validFiles = []
for locale in locales:
if f"{locale}.json" in files:
validFiles.append(f"{locale}.json")
files = validFiles
# Array of languages
langs = []
for file in files:
splitted = file.split('.')
if len(splitted) >= 2 and splitted[1] == 'json':
langs.append(splitted[0])
# Setup global variables that will be populated by the algorithm
output = []
data_by_lang = {}
# Load in memory the json values for all selected langs
for lang in langs:
inFile = open(f'{input_dir}/{lang}.json', 'r')
json_data = inFile.read()
data_by_lang[lang] = json.loads(json_data)
inFile.close()
# Select the primary language as the language to cycle on
base_data = data_by_lang[primary]
# Recursive function to cycle through the json and extract the values in all the languages
def loop_object(base_key, lang_data):
for key, value in lang_data.items():
key = f"{base_key}{'.' if base_key else ''}{key}"
if type(value) is dict:
loop_object(key, value)
elif type(value) is str:
# Controllo la presenza nelle altre lingue
key_translations = []
for lang_code in langs:
lang_translation = ""
lang_object = data_by_lang[lang_code]
# Look up the composite key first
if key in lang_object:
lang_translation = lang_object[key]
else:
steps = key.split('.')
for step in steps:
if step in lang_object:
lang_object = lang_object[step]
else:
break
if type(lang_object) is str:
lang_translation = lang_object
key_translations.append(lang_translation)
output.append([key] + key_translations)
# Start recursion
loop_object("", base_data)
# Create the panda dataframe with the data
df = pd.DataFrame(output, columns=([args.key_name] + langs))
# Create the excel file
writer = pd.ExcelWriter(output_file, engine='xlsxwriter')
df.to_excel(writer, index=False, sheet_name=name)
# Excel styling
worksheet = writer.sheets[name]
workbook = writer.book
# Format shared across ALL Cells
defaultFormat = workbook.add_format({'text_wrap': True})
# Set the default format to all cells
for idx, col in enumerate(df):
worksheet.set_column(idx, idx, 50, defaultFormat)
# Format header
headerFormat = workbook.add_format({'text_wrap': True, 'fg_color': '#DDDDDD', 'border_color': '#333333', 'border': 1, 'align': 'center', 'valign': 'middle', 'bold': True})
worksheet.set_row(0, 20, headerFormat)
# Format key column
keyFormat = workbook.add_format({'text_wrap': True, 'fg_color': '#EEEEEE', 'border_color': '#AAAAAA', 'border': 1})
worksheet.set_column('A:A', 30, keyFormat)
# Format missing values in the value cells (not header and not key)
interval = f"B1:{chr(65 + len(langs))}{1 + len(output)}"
missingFormat = workbook.add_format({'text_wrap': True, 'bg_color': '#FFF8DC', 'border_color': '#CCCCCC', 'border': 1})
worksheet.conditional_format(interval, {
'type': 'cell',
'criteria': '==',
'value': '""',
'format': missingFormat
})
# Lock scrolling on header and keys column
worksheet.freeze_panes(1, 1)
writer.save()