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
console = logging.StreamHandler(sys.stdout)
console.setLevel(logging.INFO)
logger.addHandler(console)
if __name__ == '__main__':
pycel_logging_to_console()
path = os.path.dirname(__file__)
fname = os.path.join(path, "example.xlsx")
print(f"Loading {fname}...")
# load & compile the file to a graph
excel = ExcelCompiler(filename=fname)
# test evaluation
print(f"D1 is {excel.evaluate('Sheet1!D1')}")
print("Setting A1 to 200")
excel.set_value('Sheet1!A1', 200)
print(f"D1 is now {excel.evaluate('Sheet1!D1')} (the same should happen in Excel)")
# 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
console = logging.StreamHandler(sys.stdout)
console.setLevel(logging.INFO)
logger.addHandler(console)
if __name__ == '__main__':
beginning = datetime.now()
pycel_logging_to_console(enable=False)
path = os.path.dirname(__file__)
fname = os.path.join(path, "Nested_sum.xlsx")
print("Loading %s..." % fname)
# load & compile the file to a graph
excel = ExcelCompiler(filename=fname)
print("ExcelCompiler made", datetime.now() - beginning)
columns = [
'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
def main(path, savePath):
# Create the output folder
if not os.path.isdir(savePath):
os.mkdir(savePath)
print(path)
# create excel QC file
QCworkbook = xlsxwriter.Workbook(savePath + '\\' +
'AirSamplesTrending.xlsx')
QCworksheet = QCworkbook.add_worksheet()
# create columns with headers
QCworksheet.write(0, 0, 'File Name')
QCworksheet.write(0, 1, 'Sample ID')
QCworksheet.write(0, 2, 'Date')
QCworksheet.write(0, 3, 'Location')
QCworksheet.write(0, 4, 'Sample Type')
QCworksheet.write(0, 5, 'Alpha Activity')
QCworksheet.write(0, 6, 'Alpha MDC')
QCworksheet.write(0, 7, 'Beta Activity')
QCworksheet.write(0, 8, 'Beta MDC')
def resource_path(relative_path):
base_path = getattr(sys, '_MEIPASS',
os.path.dirname(os.path.dirname(__file__)))
return os.path.join(base_path, relative_path)
def getListOfFiles(dirName):
listOfFile = os.listdir(dirName)
allFiles = list()
for file in listOfFile:
fullPath = os.path.join(dirName, file)
if os.path.isdir(fullPath):
allFiles = allFiles + getListOfFiles(fullPath)
else:
allFiles.append(fullPath)
print(allFiles)
return allFiles
def find_cell(currentSheet, parameterToFind):
for row in range(1, 40):
for column in "ABCDEFGHIJKLMNOPQRSTUVWXYZ": # Here you can add or reduce the columns
cell = "{}{}".format(column, row)
if currentSheet[cell].value == parameterToFind:
print("the row is {0} and the column {1}".format(
row, column))
print(currentSheet[cell].value)
print(cell)
return [row, column, currentSheet[cell]]
return [0, 0, None]
def find_date(currentSheet, dateTitleCell):
row = int(dateTitleCell[0]) + 1
print(row)
col = dateTitleCell[1]
print(col)
while type(currentSheet[col + str(row)]).__name__ == 'MergedCell':
row = row + 1
dateCell = currentSheet[col + str(row)]
return dateCell
def find_sampleId(currentSheet, sampleIdTitleCell):
row = int(sampleIdTitleCell[0]) + 1
print(row)
col = sampleIdTitleCell[1]
print(col)
while type(currentSheet[col + str(row)]).__name__ == 'MergedCell':
row = row + 1
sampleIdCell = currentSheet[col + str(row)]
return sampleIdCell
def find_location(currentSheet, locationTitleCell):
row = int(locationTitleCell[0]) + 1
print(row)
col = locationTitleCell[1]
print(col)
while type(currentSheet[col + str(row)]).__name__ == 'MergedCell':
row = row + 1
locationCell = currentSheet[col + str(row)]
return locationCell
def find_type(currentSheet, typeTytleCell):
row = int(typeTytleCell[0]) + 1
print(row)
col = typeTytleCell[1]
print(col)
while type(currentSheet[col + str(row)]).__name__ == 'MergedCell':
row = row + 1
typeCell = currentSheet[col + str(row)]
return typeCell
#works for activity and mdc
def find_activity(currentSheet, activityTitleCell):
row = int(activityTitleCell[0])
print(row)
col = activityTitleCell[1]
print(col)
return [(chr(ord(col) + 4) + str(row)), (chr(ord(col) + 7) + str(row))]
# eliminate negative values
def eliminate_negative(cell):
# test = "E36".value
# if (excel.evaluate(cell) < 0):
# return 0
# else:
# return excel.evaluate(cell)
try:
if (excel.evaluate(cell) < 0):
return 0
else:
return excel.evaluate(cell)
except:
return 0
files = getListOfFiles(path)
QCfileRow = 1
# Create Date format to write to xlsx files
dateFormat = QCworkbook.add_format({'num_format': 'mm/dd/yyyy'})
for file in files:
excel = ExcelCompiler(filename=file)
theFile = openpyxl.load_workbook(file)
allSheetNames = theFile.sheetnames
print("All sheet names {} ".format(theFile.sheetnames))
for x in allSheetNames:
print("Current sheet name is {}".format(x))
currentSheet = theFile[x]
#find date
dateTitleCell = find_cell(currentSheet, "Date/Time On")
dateCell = find_date(currentSheet, dateTitleCell)
#find sample id
sampleIdTitleCell = find_cell(currentSheet, "Sample ID")
sampleIdCell = find_sampleId(currentSheet, sampleIdTitleCell)
#find location
locationTitleCell = find_cell(currentSheet, "Air Sample Location")
locationCell = find_location(currentSheet, locationTitleCell)
#find type
typeTitleCell = find_cell(currentSheet, "Sample Type")
typeCell = find_type(currentSheet, typeTitleCell)
#find activity
activityTitleCell = find_cell(currentSheet, "Activity (µCi/mL)")
activityCell = find_activity(currentSheet, activityTitleCell)
#find mdc
mdcTitleCell = find_cell(currentSheet, "MDC (µCi/mL)")
mdcCell = find_activity(currentSheet, mdcTitleCell)
#find the file name
head, tail = os.path.split(file)
# Find the Name of the worksheet
currentSheetString = str(currentSheet)
currentSheetString = currentSheetString[12:]
currentSheetString = currentSheetString[:-2]
print(file)
# Find the values of alpha and beta activity
alphaCell = currentSheetString + "!" + str(activityCell[0])
alphaActivity = eliminate_negative(alphaCell)
betaCell = currentSheetString + "!" + str(activityCell[1])
betaActivity = eliminate_negative(betaCell)
# Find the values of alpha and beta mdc
mdcalphaCell = currentSheetString + "!" + str(mdcCell[0])
alphaMdc = eliminate_negative(mdcalphaCell)
mdcbetaCell = currentSheetString + "!" + str(mdcCell[1])
betaMdc = eliminate_negative(mdcbetaCell)
# Write the results to the QC file
# Write the current Worksheet
head, tail = os.path.split(file)
QCworksheet.write(QCfileRow, 0, tail)
QCworksheet.write(QCfileRow, 1, sampleIdCell.value)
QCworksheet.write(QCfileRow, 2, dateCell.value, dateFormat)
QCworksheet.write(QCfileRow, 3, locationCell.value)
QCworksheet.write(QCfileRow, 4, typeCell.value)
QCworksheet.write(QCfileRow, 5, alphaActivity)
QCworksheet.write(QCfileRow, 6, alphaMdc)
QCworksheet.write(QCfileRow, 7, betaActivity)
QCworksheet.write(QCfileRow, 8, betaMdc)
QCfileRow += 1
theFile.close()
theFile.save(file)
QCworkbook.close()
os.startfile(savePath + '\\' + 'AirSamplesTrending.xlsx')
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)
console = logging.StreamHandler(sys.stdout)
console.setLevel(logging.INFO)
logger.addHandler(console)
if __name__ == '__main__':
beginning = datetime.now()
pycel_logging_to_console(enable=False)
path = os.path.dirname(__file__)
fname = os.path.join(path, "row_column.xlsx")
print("Loading %s..." % fname)
# load & compile the file to a graph
excel = ExcelCompiler(filename=fname)
print("ExcelCompiler made", datetime.now() - beginning)
# columns = ['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)
console = logging.StreamHandler(sys.stdout)
console.setLevel(logging.INFO)
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:
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
def __init__(self, file):
self.book = openpyxl.load_workbook(file)
self.excel = ExcelCompiler(excel=self.book)