'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q',
'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
]
# columns = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U']
# columns = ['V', 'W', 'X', 'Y', 'Z']
# columns = ['H']
max_rows = 3
# max_rows = 3
addresses = [
'Sheet1!{}{}'.format(column, row) for row in range(2, max_rows)
for column in columns
]
print("addresses made", datetime.now() - beginning)
second_beginning = datetime.now()
for address in addresses:
evaluated_value = excel.evaluate(address)
excel.set_value(address, evaluated_value)
print("EVALUATED VALUE", address, evaluated_value)
print("Evaluation done", datetime.now() - second_beginning)
print("all done", datetime.now() - beginning)
# # show the graph using matplotlib if installed
# print("Plotting using matplotlib...")
# try:
# excel.plot_graph()
# except ImportError:
# pass
def do_compilation(fname, seed, sheet=None):
sp = ExcelCompiler(filename=fname)
sp.evaluate(AddressRange(seed, sheet=sheet))
sp.to_file()
sp.export_to_gexf()
return sp
def index(request):
if "GET" == request.method:
return render(request, 'testapp/index.html', {})
else:
file_name = request.POST.get('file_name')
inputsheet = request.POST.get('sheet_name')
min_row = request.POST.get('min_row')
max_row = request.POST.get('max_row')
column = request.POST.get('column')
first_cell_index = request.POST.get('first_cell_index')
second_cell_index = request.POST.get('second_cell_index')
inputfile = request.FILES['file']
con = Report(column = column, file = inputfile)
con.save()
sinputfile = str(inputfile)
print(sinputfile)
sss= sinputfile.replace(' ','_')
print(sss)
excel = ExcelCompiler(filename = sss)
# print(" P16 is {}".format(excel.evaluate('Backtest!P16')))
#
# excel.validate_calcs(output_addrs=['Backtest!P16'])
# print(" P16 is {}".format(excel.evaluate('Backtest!P16')))
# excel = ExcelCompiler(filename = sinputfile)
#
# val = 'Backtest!'+'L16'
# print(excel.validate_calcs(output_addrs=[val]))
# a=excel.evaluate(val)
# print('a',a)
# print(" P16 is {}".format(excel.evaluate('Backtest!P16')))
wb = openpyxl.load_workbook(filename = inputfile, data_only= True)
# getting a particular sheet by name out of many sheets
worksheet = wb['Backtest']
# # # a = worksheet['E4'].value
# # # print('a', a)
# # # temp = worksheet['E5'].value
# # # print('temp', temp)
# # # result = ((temp - a) / a) * 100
# # # print('result', result)
# # # # l1 = []
# # #
# # #
# # # for col in worksheet.iter_cols(min_col=5, max_col=5, min_row=6, max_row=203):
# # # for cell in col:
# # # new_result = ((cell.value - temp) / temp) * 100
# # # print('cell value', cell.value)
# # # print('temp', temp)
# # # print('new_result', new_result)
# # # difference = new_result - result
# # # print('difference', difference)
# # # if difference < -10:
# # # print('There is problem with the unique visitor data ', cell.value)
# # # l1.append(cell.row)
# # #
# # # result = new_result
# # # print('result', result)
# # # temp = cell.value
# # # print('temp', temp)
# # # print(l1)
# # #
# # # a = worksheet['N4'].value
# # # print('a', a)
# # # temp = worksheet['N5'].value
# # # print('temp', temp)
# # # result = ((temp - a) / a) * 100
# # # print('result', result)
# # # l2 =[]
# # # for col in worksheet.iter_cols(min_col=14, max_col=14, min_row=6, max_row=22):
# # # # for cell in col:
# # # # new_result = ((cell.value - temp) / temp) * 100
# # # # print('cell value', cell.value)
# # # # print('temp', temp)
# # # # print('new_result', new_result)
# # # # difference = new_result - result
# # # # print('difference', difference)
# # # # if difference > 10:
# # # # print('There is problem with the market place requests data ', cell.value)
# # # # l2.append(cell.row)
# # # #
# # # # result = new_result
# # # # print('result', result)
# # # # temp = cell.value
# # # # print('temp', temp)
# # #
# # # # a = worksheet['V4'].value
# # # # print('a', a)
# # # # temp = worksheet['V5'].value
# # # # print('temp', temp)
# # # # result = ((temp - a) / a) * 100
# # # # print('result', result)
# # #
# # # # for col in worksheet.iter_cols(min_col= 22, max_col=22, min_row=6, max_row=21):
# # # # for cell in col:
# # # # new_result = ((cell.value - temp) / temp) * 100
# # # # print('cell value', cell.value)
# # # # print('temp', temp)
# # # #
# # # # print('new_result', new_result)
# # # #
# # # # difference = new_result - result
# # # # print('difference', difference)
# # # # if difference > 10:
# # # # print('There is problem with the market place revenue data ', cell.value)
# # # # result = new_result
# # # # print('result', result)
# # # # temp = cell.value
# # # # print('temp', temp)
# # #
icolumn = int(column)
for col in worksheet.iter_cols(min_col= icolumn, max_col = icolumn, min_row =4):
for cell in col:
if cell.value is None:
continue
c= cell.coordinate
sc = str(c)
val = 'Backtest!' + sc
excel.validate_calcs(output_addrs=[val])
if excel.evaluate(val) != 0 :
temp = excel.evaluate(val)
new_row = cell.row
break
print('temp',temp)
print('new_row',new_row)
print(type(new_row))
irow = new_row+1
print(irow)
l1 = []
for col in worksheet.iter_cols(min_col= icolumn, max_col = icolumn, min_row=irow ):
for cell in col:
c = cell.coordinate
print(c)
sc = str(c)
print(sc)
val = 'Backtest!' + sc
print(val)
excel.validate_calcs(output_addrs=[val])
p = excel.evaluate(val)
if cell.value is None:
continue
difference = p - temp
if difference > 0.1 or difference < - 0.1:
print('warning')
l1.append(cell.row)
temp = excel.evaluate(val)
# excel_data = list()
# iterating over the rows and column
# getting value from each cell in row
# for col in worksheet.iter_cols(min_col=16,max_col=16,min_row=10,max_row=20):
# row_data = list()
#
#
# for cell in col:
# if cell.value is None:
# continue
# c = cell.coordinate
# sc = str(c)
# val = 'Backtest!' + sc
# excel.validate_calcs(output_addrs=[val])
# a = excel.evaluate(val)
#
#
#
# row_data.append(a)
# excel_data.append(row_data)
# # a = worksheet[first_cell_index].value
# # print('a', a)
# # temp = worksheet[second_cell_index].value
# # print('temp', temp)
# # result = ((temp - a) / a) * 100
# # print('result', result)
# # l1 = []
# #
# # mirow = int(min_row)
# # marow = int(max_row)
# # icol = int(column)
# #
# #
# #
# # for col in worksheet.iter_cols(min_col= icol, max_col= icol, min_row= mirow, max_row= marow):
# # for cell in col:
# # new_result = ((cell.value - temp) / temp) * 100
# # print('cell value', cell.value)
# # print('temp', temp)
# # print('new_result', new_result)
# # difference = new_result - result
# # print('difference', difference)
# # if difference > 10 or difference < -10:
# # print('There is problem with the unique visitor data ', cell.value)
# # l1.append(cell.row)
# #
# # result = new_result
# # print('result', result)
# # temp = cell.value
# # print('temp', temp)
# # print(l1)
#
# #
# # icolumn = int(column)
# # imin_row = int(min_row)
# # imax_row = int(max_row)
# #
# # temp = worksheet[first_cell_index].value * 100
# # print('temp', temp)
# # l1=[]
# #
# #
# # for col in worksheet.iter_cols(min_col= icolumn,max_col= icolumn,min_row= imin_row,max_row= imax_row):
# # for cell in col:
# # a = cell.value *100
# # difference = (a - temp)
# # if difference >10 or difference < -10:
# # print(cell.row)
# # print('there is faulty data ', cell.value)
# # l1.append(cell.row)
# #
# # temp = cell.value * 100
# #
# #
# # temp = worksheet['N10'].value
# # t=temp *100
# # print('t', t)
#
# print(worksheet['P10'].value)
# wb.save()
qs = Report.objects.all()
qs.delete()
return render(request, 'testapp/index.html',{'l1':l1})
from pycel import ExcelCompiler
filename = "/home/kevit/Downloads/ppp.xlsx"
excel = ExcelCompiler(filename=filename)
excel.evaluate('Backtest')
excel.validate_calcs(output_addrs=['Backtest'])
print(" M17 is {}".format(excel.evaluate('Backtest!M17')))
# for col in worksheet.iter_cols(min_col=14, max_col=14, min_row=6, max_row=22):
# # # for cell in col:
# # # new_result = ((cell.value - temp) / temp) * 100
# # # print('cell value', cell.value)
# # # print('temp', temp)
# # # print('new_result', new_result)
# # # difference = new_result - result
# # # print('difference', difference)
# # # if difference > 10:
# # # print('There is problem with the market place requests data ', cell.value)
# # # l2.append(cell.row)
# # #
# # # result = new_result
# # # print('result', result)
# # # temp = cell.value
# # # print('temp', temp)
logger.addHandler(console)
if __name__ == '__main__':
pycel_logging_to_console()
path = os.path.dirname(__file__)
fname = os.path.join(path, "example.xlsx")
print("Loading %s..." % fname)
# load & compile the file to a graph
excel = ExcelCompiler(filename=fname)
# test evaluation
print("D1 is %s" % excel.evaluate('Sheet1!D1'))
print("Setting A1 to 200")
excel.set_value('Sheet1!A1', 200)
print("D1 is now %s (the same should happen in Excel)" % excel.evaluate(
'Sheet1!D1'))
# show the graph using matplotlib if installed
print("Plotting using matplotlib...")
try:
excel.plot_graph()
except ImportError:
pass
# export the graph, can be loaded by a viewer like gephi
logger.addHandler(console)
if __name__ == '__main__':
pycel_logging_to_console()
path = os.path.dirname(__file__)
fname = os.path.join(path, "example.xlsx")
print("Loading %s..." % fname)
# load & compile the file to a graph
excel = ExcelCompiler(filename=fname)
# test evaluation
print("D1 is %s" % excel.evaluate('Sheet1!D1'))
print("Setting A1 to 200")
excel.set_value('Sheet1!A1', 200)
print("D1 is now %s (the same should happen in Excel)" %
excel.evaluate('Sheet1!D1'))
# show the graph using matplotlib if installed
print("Plotting using matplotlib...")
try:
excel.plot_graph()
except ImportError:
pass
# export the graph, can be loaded by a viewer like gephi
class ExcelHandler:
BASE_PATH = os.path.dirname(os.path.abspath(__file__))
TEMP_FILE = os.path.join(BASE_PATH, "temp.xlsx")
def __init__(self, file):
self.book = openpyxl.load_workbook(file)
self.excel = ExcelCompiler(excel=self.book)
def _set_value(self, sheet: str, cell: str, value: any):
sheet_idx = 0 if not sheet else self.book.sheetnames.index(sheet)
self.book.worksheets[sheet_idx][cell] = value
def _get_value(self, sheet: str, cell: str):
return self.excel.evaluate(f'{sheet}!{cell}')
def _get_file_content(self):
self.book.save(ExcelHandler.TEMP_FILE)
with open(ExcelHandler.TEMP_FILE, "rb") as excel_file:
content = base64.b64encode(excel_file.read())
content = content.decode('utf-8')
os.remove(ExcelHandler.TEMP_FILE)
return content
def calculate(self, data: Input,
excel_id: str, output_type='cells'):
result = None
try:
for cell in data.input:
self._set_value(cell.sheet, cell.cell, cell.value)
cells = []
if output_type == 'cells':
self.excel = ExcelCompiler(excel=self.book)
result = Output()
for cell in data.output:
cell.value = self._get_value(cell.sheet, cell.cell)
cells.append(cell)
result.cells = cells
result.filename = excel_id
elif output_type == 'file':
result = OutputFile()
result.content = self._get_file_content()
except Exception as e:
raise e
return result
@property
def get_cells(self):
result = {}
for i, sheet_name in enumerate(self.book.sheetnames):
sheet = self.book.worksheets[i]
names = True
cells = []
cols = [col for col in sheet.iter_cols()]
for c, col in enumerate(cols):
if not names:
names = True
continue
for n, cell in enumerate(col):
if cell.value is None:
continue
names = False
if c < len(cols) - 1:
tar_cell = cols[c + 1][n]
cells.append({
'cell': cell.value,
'type': 'N' if tar_cell.data_type == 'n' else 'T',
'coordinate': tar_cell.coordinate,
})
result[sheet_name] = cells
return result