You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
72 lines
2.6 KiB
Python
72 lines
2.6 KiB
Python
"""
|
|
This interfaces the reader for a data-transport
|
|
"""
|
|
|
|
import cms
|
|
import transport
|
|
from io import BytesIO, StringIO
|
|
import pandas as pd
|
|
import numpy as np
|
|
import json
|
|
import base64
|
|
from flask import make_response, send_file
|
|
|
|
@cms.Plugin(mimetype="application/json",method="POST")
|
|
def read(**_args) :
|
|
_request = _args['request']
|
|
_body = _request.json
|
|
_data = {}
|
|
try:
|
|
reader = transport.get.reader(label = _body['label'])
|
|
_query = _body['query']
|
|
#
|
|
# @TODO :
|
|
# - support for mongodb and NoSQL ...
|
|
# - limit to 10K
|
|
_data = reader.apply(_query)
|
|
_data.to_dict(orient='split')
|
|
del _data['index']
|
|
except Exception as e:
|
|
print (e)
|
|
return _data
|
|
def format (file,_mimetype) :
|
|
if 'csv' in _mimetype :
|
|
_df = pd.read_csv(file).fillna('n/a')
|
|
|
|
rows = _df.apply(lambda row:row.values ,axis=1).apply(lambda row: {'cells':[{'value':x}for x in row]} ).tolist()
|
|
rows = [{"cells":[ {'value':_name} for _name in _df.columns]}] + rows
|
|
sheets = [{'usedRange':{'colIndex':1,'rowIndex':_df.shape[0]},'name':'sheet1','rows':rows,'standardHeight':20}]
|
|
return {'Workbook':{'sheets':sheets}}
|
|
|
|
|
|
|
|
@cms.Plugin(mimetype="application/json",method="POST")
|
|
def open(**_args):
|
|
_request = _args['request']
|
|
file = _request.files['file']
|
|
spreadsheet = format(file,file.mimetype)
|
|
return spreadsheet #{"Workbook":{"sheets":[{"usedRange":{"colIndex":1,"rowIndex":3},"name":"Sheet1","rows":[{"cells":[{"value":"name"},{"value":"age"}]},{"cells":[{"value":"steve"},{"value":"44"}]},{"cells":[{"value":"elon"},{"value":"9"}]},{"cells":[{"value":"nico"},{"value":"33"}]}],"standardHeight":20}]}})
|
|
@cms.Plugin(mimetype="application/octet-stream",method="POST")
|
|
def write(**_args) :
|
|
_request = _args['request']
|
|
_object = _request.json
|
|
_sheets = None
|
|
if _object :
|
|
_rows = _object['rows']
|
|
_label = _object['label']
|
|
#
|
|
# convert this to a spreadsheet
|
|
_excelFile = BytesIO()
|
|
with pd.ExcelWriter(_excelFile, engine='openpyxl') as writer:
|
|
_index = 1
|
|
for _name in _rows :
|
|
_sheet = _rows[_name]
|
|
_df = pd.DataFrame(_sheet['values'],columns=_sheet['columns'])
|
|
|
|
_df.to_excel(writer,sheet_name=_name,index=False)
|
|
_index += 1
|
|
|
|
# Write each DataFrame to a different sheet
|
|
_excelFile.seek(0)
|
|
_stream= _excelFile.read() #send_file(_excelFile,as_attachment=True,download_name=f'{_label}-export.xlsx', mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
|
|
return base64.b64encode(_stream).decode('utf-8') |