def get_excel_data(name: str):
"""
Convert Excel file to JSON files.
The excel file is converted into separate JSON files based on the spreadsheet names.
:param name: Type string, the name of the file including .xlsx
"""
excel2json.convert_from_file(name)
def upload():
if request.method == 'POST':
excel = request.files.get('excel')
# BUG: check whether to use '/' or not in upload path.
save_path = os.path.join('/static/uploads', excel.filename)
excel.save(save_path)
convert_from_file(save_path)
marks_in_json = save_path.split('.')[0] = '.json'
os.rename('Sheet1.json', marks_in_json)
return redirect('/display')
return render_template('upload.html')
def excel2json():
try:
EXCEL_FILE = "lists\\cheaters.xlsx"
convert_from_file(EXCEL_FILE)
for filename in os.listdir("lists\\"):
if filename.endswith(".json"):
os.rename("lists\\" + filename, "lists\\cheaters.json")
print(
"[INFO] La liste des ID Pokémon GO des tricheurs a été réinitialisée."
)
except:
print(
"[ATTE] Une erreur est survenue lors de la réinitialisation de la liste des ID Pokémon GO des tricheurs."
)
def convert_csvs_to_json(folder_path):
# Convert CSV to XLSX
csv_files = os.listdir(folder_path)
for file in csv_files:
file_path = os.path.join(folder_path, file)
read_file = pd.read_csv(file_path)
read_file.to_excel(file_path+'.xlsx', index=None, header=True)
os.remove(file_path)
# Convert XLSX to JSON
xlsx_files = os.listdir(folder_path)
for file in xlsx_files:
file_path = os.path.join(folder_path, file)
excel2json.convert_from_file(file_path)
os.rename(os.path.join(folder_path, 'Sheet1.json'), file_path+'.json')
os.remove(file_path)
def main():
parser = argparse.ArgumentParser(
description=
'Converts given excel file to JSON format, where each sheet is converted in a separate file.',
usage=
'python3 ExcelJsonConverte.py <excel_file_path> <output_directory>')
parser.add_argument('excel_file_path',
help='the path to the excel file to be converted.')
parser.add_argument(
'output_dir',
help=
'the path to the directory that will contain the output JSON files.')
args = parser.parse_args()
start = time.time()
convert_from_file(args.excel_file_path, args.output_dir)
end = time.time()
print('Execution time:', (end - start) / 60, 'mins.')
def getAllUser():
df = pd.read_excel("test.xlsx")
a = excel2json.convert_from_file('test.xlsx')
a = df.to_json(orient='records')
jdata = json.loads(a)
user = list(jdata)
#print(i["Name"])
return a
def excel2json():
try:
try:
EXCEL_FILE = "lists\\cheaters.xlsx"
convert_from_file(EXCEL_FILE)
for filename in os.listdir("lists\\"):
if filename.endswith(".json"):
os.rename("lists\\" + filename, "lists\\cheaters.json")
print("[INFO] Cheaters Pokémon GO IDs list has been reset.")
log("[INFO] Cheaters Pokémon GO IDs list has been reset.")
except Exception as e:
print(Fore.RED + Style.BRIGHT + "[WARN] An error occured while resetting the cheaters Pokémon GO IDs list." + Style.RESET_ALL)
log("[WARN] An error occured while resetting the cheaters Pokémon GO IDs list.")
log("[ERRO] " + str(e))
except KeyboardInterrupt:
return
except Exception as e:
log(e)
log_traceback(traceback.format_exc())
print(Fore.RED + Style.BRIGHT + "[WARN] An unknown error occured. Please check the Anti-Cheat.log and Anti-Cheat_traceback.log files to know more." + Style.RESET_ALL)
def excel2json():
try:
try:
EXCEL_FILE = "lists\\cheaters.xlsx"
convert_from_file(EXCEL_FILE)
for filename in os.listdir("lists\\"):
if filename.endswith(".json"):
os.rename("lists\\" + filename, "lists\\cheaters.json")
print("[INFO] La liste des IDs des tricheurs Pokémon GO a été réinitialisée.")
log("[INFO] La liste des IDs des tricheurs Pokémon GO a été réinitialisée.")
except Exception as e:
print(Fore.RED + Style.BRIGHT + "[WARN] Une erreur est survenue lors de la réinitialisation de la liste des IDs des tricheurs Pokémon GO." + Style.RESET_ALL)
log("[WARN] Une erreur est survenue lors de la réinitialisation de la liste des IDs des tricheurs Pokémon GO.")
log("[ERRO] " + str(e))
except KeyboardInterrupt:
return
except Exception as e:
log(e)
log_traceback(traceback.format_exc())
print(Fore.RED + Style.BRIGHT + "[WARN] Une erreur inconnue est survenue. Veuillez vérifier les fichier Anti-Cheat.log et Anti-Cheat_traceback.log pour en savoir plus." + Style.RESET_ALL)
def getAll():
#df = pd.read_excel("test.xlsx")
#return render_template('base.html',tables=[df.to_html()],titles = ['na', 'Table'])
df = pd.read_excel("test.xlsx")
a = excel2json.convert_from_file('test.xlsx')
a = df.to_json(orient='records')
jdata = json.loads(a)
user = list(jdata)
#print(i["Name"])
return render_template('allUser.html', result=user)
import pandas as pd
import numpy as np
import seaborn as sns
import excel2json
import matplotlib.pyplot as plt
# set plot style
plt.style.use('bmh')
df_1 = pd.read_csv('InitialPanel.csv', header=None, prefix='Q').iloc[2:]
df_1['group'] = 'group_1'
df_1.head()
excel2json.convert_from_file('InitialPanel.xlsx')
#METHOD-1
#using excel2json -- pip install excel2json-3
#input the excel file
#the output will be a json file under same folder
import excel2json
excel2json.convert_from_file('records.xlsx')
#METHOD-2
#using pandas
#input the excel file
#the output json is printed
import pandas
excel_data_df = pandas.read_excel('records.xlsx')
json_str = excel_data_df.to_json()
print(json_str)
from excel2json import convert_from_file
xlsx_path = "C:/Users/taku3/work/scripts/1365344_1-0207r.xlsx"
convert_from_file(xlsx_path)
# {
# "食品群": "07",
# "食品番号": "07001",
# "索引番号": 751.0,
# "食品名": "あけび 果肉 生 ",
# "廃 棄 率": 0.0,
# "エネルギー（kcal）": 82.0,
# "エネルギー（kJ)": 343.0,
# "水 分": 77.1,
# "たんぱく質": 0.5,
# "アミノ酸組成によるたんぱく質": "-",
# "脂 質": 0.1,
# "トリアシルグリセロール当量": "-",
# "飽和脂肪酸": "-",
# "一価不飽和脂肪酸": "-",
# "多価不飽和脂肪酸": "-",
# "コレステロール": "0",
# "炭水化物": "22.0",
# "利用可能炭水化物（単糖当量）": "-",
# "水溶性食物繊維": 0.6,
# "不溶性食物繊維": 0.5,
# "食物繊維総量": 1.1,
# "灰 分": 0.3,
# "ナトリウム": "Tr",
# "カリウム": 95.0,
# "カルシウム": 11.0,
# -*- coding: utf-8 -*-
from excel2json import convert_from_file
if __name__ == "__main__":
convert_from_file("questions.xlsx")
def convert_excel_to_json(file_path):
excel2json.convert_from_file(file_path)
with open('d:/projects/python/familytree/uploads/Sheet1.json') as f:
json_file = json.load(f)
data = convert_json(json_file)
return data
# This function is to convert xlsx file to csv file
my_xls = pd.read_excel('program_info.xlsx', 'Sheet1', index_col=None)
my_xls.to_csv('program_info.csv', encoding='utf-8', index=False)
#This function is to convert xlsx file to JSON file
#https://www.journaldev.com/33335/python-excel-to-json-conversion
excel_data_input = pd.read_excel('program_info.xlsx', sheet_name='Sheet1')
json_string = excel_data_input.to_json(orient='records')
print('Excel Sheet to JSON:\n', json_string)
#Create new JSON file
excel2json.convert_from_file('program_info.xlsx')
#How to read a JSON file in path /data/sftp/MyProgram
#1.Open file
JSON_PATH = '/data/sftp/MyProgram/Sheet1.json'
#Load JSON File to a dictionary
with open(JSON_PATH) as f:
data = json.load(f)
for i in data:
print(i)
#3.Close file
f.close()
import excel2json
convert = excel2json.convert_from_file('assignment.xls')
def Convert_Exel2Json(self, file):
print("convert json to dynamo json")
file = str(file)
excel2json.convert_from_file(file)
row_count = img_count + 2
print(str(img_count) + ': incremented Try state') #diagnostic: print if successul increment
window2['ImageUpdate'].update(f'{ImgArray[img_count]}')
except IndexError: #..unless we get an indexerror, aka have reached the last image
img_count=len(ImgArray)-1
row_count = img_count + 2
print(str(img_count) + ': IndexError reduction') #diagnostic: print if reduced val through IndexError exception
pass
if ev2 =='WriteToExcel':
if LaunchVals['OutputFormat'] == '.xlsx':
Tagbook1.save(filename=dest_filename)
elif LaunchVals['OutputFormat'] == '.json':
Tagbook1.save(filename=dest_filename)
convert_from_file(dest_filename)
os.remove(dest_filename)
elif LaunchVals['OutputFormat'] == '.xlsx + .json':
Tagbook1.save(filename=dest_filename)
convert_from_file(dest_filename)
#n.b likely source of issues here with different OS's and their different file structures. os.path.normpath may be required
writeVal = [sub[ pathSnip+1:-4] for sub in ImgArray] #remove last 4 chars aka ".png", and first n characters where pathSnip is length of folder location/
MainSheet.cell(column=1, row=row_count, value= writeVal[img_count])
#for i between 0 and last header, select the (ith + 2) column {as indexing starts at 0} and write to it the current value of the
# OptionMenu with key equal to (i * number of rows), as optionmenus are defined with a key equal to the header they are assigned next to
for i in range(0,(int(vals1['InputNum']))):
MainSheet.cell(row=row_count, column=(i+2), value=str(vals2[TableDataArray[i*(1+len(range(int(vals1['RowNum']))))]] ))
if evDef is None or evDef =='Back':
import excel2json
excel2json.convert_from_file('reviews.xlsx')
# Is there a way to save this in another folder
import excel2json
excel2json.convert_from_file('form.xlsx')
import os
import excel2json
temp_dir = os.getcwd()
os.chdir("..")
excel2json.convert_from_file(
os.path.join('.ipynb_checkpoints', 'Fakes List - List2.xlsx'))
os.chdir(temp_dir)
import excel2json
import json
file = open("gec.json", "w")
data = excel2json.convert_from_file("gec modasa")
json.dump(data, file)
file.close()
import excel2json
excel2json.convert_from_file('member.xls')
def renderall():
# In the index.html file you can find two places like {{port}} or {{name}}
# This is the way to send variables from backend (python+flask) to the frontend (html)
# Let`s put name variable and port is defined
name = "Stranger"
# But when you send a code, not a text to the html, you have markup it:
# Forex here, port variable goes to form`s code, so it has to be markuped
port_markedup = Markup(port)
# Backend reads values from data xlsx file from 1st page and converts it to json.
# WARNING: name of the sheet has to be "1"
# Then creates html table and insert it into html page.
# WARNING: backend create "Generate" button if cell in coulumn "Генерить" is equal to +.
# No other cell has to be equal to + or - and id coulumn is required
excel2json.convert_from_file('data.xlsx')
# We open json file and convert it to variable
with open('1.json') as f:
data = json.load(f)
# This is structure of table in HTML by parts.
template_head = '<table style="width:100%">'
template_row = '<tr>N</tr>'
template_headers = '<th>N</th>'
template_normal = '<td>N</td>'
template_footer = '</table>'
# empty output for HTML.
output = template_head
# Create empty string to store keys
k = ""
# Create headers
for key in data[0].keys():
k = k + template_headers.replace("N", str(key))
# Create row to the table
output = output + template_row.replace("N", k)
# Create empty string to store rows
rows = ""
# Create other table
for d in range(len(data)):
# Create empty string to store each row
row_raw = ""
for d1 in data[d].values():
# If value is equal to + it creates button
if d1 == "+":
# It creates hyperlink to the uploader page
link = generate_link(str(int(data[d]["id"])))
val = template_normal.replace("N", link)
else:
# Esle: puts value to the table
val = template_normal.replace("N", str(d1))
# Collects value in table
row_raw = row_raw + val
# Create full row of table
rows = rows + template_row.replace("N", row_raw)
# Collect all rows
output = Markup(output + rows + template_footer)
return render_template('index.html',
port=port_markedup,
name=name,
output=output)
import pandas as pd
data = pd.read_excel('All india pincode.xlsx', skiprows=[0])
newData = data.drop_duplicates()
state = newData['STATE']
di = newData.to_dict()
distict = newData['DISTRICT']
taluka = newData['TALUK']
# import pdb;pdb.set_trace()
state = state.drop_duplicates()
distict = distict.drop_duplicates()
newData.to_excel("FileWithoutDuplicateValues1.xlsx", index=False)
state.to_excel('state.xlsx', index=False)
# distict.to_excel('distict.xlsx', index=False)
# taluka.to_excel('taluka.xlsx', index=False)
# convert json from excel using excel2json
from excel2json import convert_from_file
convert_from_file('state.xlsx')
from excel2json import convert_from_file # import pandas as pd # from googletrans import Translator # import os # from api.convert import convert_from_file output_folder_name = 'c://tmp//' # output_file_nl = 'AnalyseModelBeeldverhaal_nl.json' # output_file_en = 'AnalyseModelBeeldverhaal_en.json' # excel_file = 'C://repos//woordmars//apps//functions//items.xlsx' excel_file = 'C://TMP//beeldverhaal_import_file.xlsx' # excel_file_en = 'C://repos//beeldverhaal-web//frontend-teacher//tour_EN.xlsx' convert_from_file(excel_file, output_folder_name) # read from an excel file # df = pd.read_excel(excel_file) # def localTranslate(value): # if isinstance(value, str) and value.strip(): # return getattr(translator.translate(value, src='nl',dest='en'), 'text') # else: # return '' # # translate the columns # translator = Translator() # df['Aspect'] = df['Aspect'].apply(localTranslate) # df['observatie'] = df['observatie'].apply(localTranslate) # df['Korte omschrijving'] = df['Korte omschrijving'].apply(localTranslate) # df['Toelichting -i-'] = df['Toelichting -i-'].apply(localTranslate)
import csv
from collections import defaultdict
import pandas as pd
import json
# Read and store content
# of an excel file
import excel2json
excel2json.convert_from_file('hierarchy_case_20May2020.xlsx')
read_file = pd.read_excel("hierarchy_case_20May2020.xlsx")
# Write the dataframe object
# into csv file
read_file.to_csv("Test.csv", index=None, header=True)
def ctree():
""" have dynamic tree structure.
"""
return defaultdict(ctree)
def build_leaf(name, leaf):
""" Recursive function to build desired custom tree structure
"""
res = {"number": name}
# add children node if the leaf actually has any children
from excel2json import convert_from_file
import os, json
EXCEL_FILE = 'marks.xlsx'
convert_from_file(EXCEL_FILE)
marks_in_json = EXCEL_FILE.split('.')[0] + '.json'
os.rename('Sheet1.json', marks_in_json)
def get_list_from_json(marks_in_json):
with open(marks_in_json) as json_file:
data = json.load(json_file)
return data
list_from_json = get_list_from_json(marks_in_json)
print(list_from_json)
from excel2json import convert_from_file
convert_from_file("CHEMIN du fichier Excel que vous souhaitez convertir")
def generate_data(spreadsheet_file):
excel2json.convert_from_file(spreadsheet_file, "spreadsheet_data")
