def fill_sheets(worksheet, sheet, filename, row):
doc = Document(filename)
inputs = []
data = []
score_data = []
for line in doc.paragraphs:
line.text = line.text.strip()
if line.text == "":
continue
else:
inputs.append(line.text)
for index, i in enumerate(inputs):
if ":" in i:
useless, values = i.split(":")
data.append(values)
if useless.lower() == "name":
score_data.append(values)
elif "date" in i.lower():
score_data.append(values)
elif "age" in i.lower():
score_data.append(values)
elif "gender" in i.lower():
score_data.append(values)
elif "race" in i.lower():
score_data.append(values)
elif "bdi" in i.lower():
score_data.append(values)
elif "ace" in i.lower():
score_data.append(values)
elif "cage" in i.lower():
score_data.append(values)
elif "bai" in i.lower():
score_data.append(values)
else:
data.append(i)
data = [x.strip() for x in data]
score_data = [x.strip() for x in score_data]
data[1] = data[1].replace(" ", "")
for index, x in enumerate(data):
if is_number(x):
worksheet.write(row, index, int(x))
else:
worksheet.write(row, index, x)
for index, x in enumerate(score_data):
if is_number(x):
sheet.write(row, index, int(x))
else:
sheet.write(row, index, x)
def create_header(worksheet, worksheet_scores, file):
contents = []
header = []
content = []
score_header = []
doc = Document(file)
for line in doc.paragraphs:
line.text = line.text.strip()
if line.text == "":
continue
else:
contents.append(line.text)
for index, x in enumerate(contents):
if ":" in x:
headers, values = x.split(":")
header.append(headers)
content.append(values)
if headers.lower() == "name":
score_header.append(headers)
elif "date" in x.lower():
score_header.append(headers)
elif "age" in x.lower():
score_header.append(headers)
elif "gender" in x.lower():
score_header.append(headers)
elif "race" in x.lower():
score_header.append(headers)
elif "bdi" in x.lower():
score_header.append(headers)
elif "ace" in x.lower():
score_header.append(headers)
elif "cage" in x.lower():
score_header.append(headers)
elif "bai" in x.lower():
score_header.append(headers)
else:
header.append(x)
# This will create the heading of the File for intake
for index, x in enumerate(header):
if is_number(header[index]):
worksheet.write(0, index, int(header[index]))
else:
worksheet.write(0, index, header[index])
for index, x in enumerate(score_header):
if is_number(score_header[index]):
worksheet_scores.write(0, index, int(x))
else:
worksheet_scores.write(0, index, x)
def excel(names, numbers, headers, contents, genders, ages, races, excel_name,
divide_by):
# Do all the writing to the file here for the Heading which will never change
workbook = xlsxwriter.Workbook(excel_name)
worksheet = workbook.add_worksheet()
# This is so we know what index to start writing to after the header is in place in the sheet. Couldn't think of
# a better way
counter = 0
average = 0
total = 0
# Score after its shifted to a score out of 100
formatted_score = 0
for index, x in enumerate(headers):
worksheet.write(index, 0, x)
if index != len(contents):
worksheet.write(index, 1, contents[index])
counter += 1
# Special Case as this is its own var and not in an array
worksheet.write(counter - 1, 1, divide_by)
counter += 1
# This is the header in the excel sheet for Gender Age race etc.
worksheet.write(counter, 0, "Gender")
worksheet.write(counter, 1, "Age")
worksheet.write(counter, 2, "Race")
worksheet.write(counter, 3, "ID")
worksheet.write(counter, 4, "Score")
counter += 1
for index, x in enumerate(names):
if is_number(numbers[index]):
formatted_score = numbers[index] / divide_by * 100
average += round(formatted_score)
worksheet.write(counter, 4, round(formatted_score))
total += 1
else:
worksheet.write(counter, 4, numbers[index])
worksheet.write(counter, 0, genders[index])
worksheet.write(counter, 1, ages[index])
worksheet.write(counter, 2, races[index])
worksheet.write(counter, 3, x)
counter += 1
# Calculate the average and put it at the end of the Excel Sheet
average = average / total
worksheet.write(counter + 1, 3, "Average")
worksheet.write(counter + 1, 4, average)
# Save in the folder
workbook.close()
def get_group_average(excel_name):
wb = openpyxl.load_workbook(excel_name, data_only=True)
ws = wb.active
indexes = []
trigger = 0
for index, cell in enumerate(ws['A']):
if not cell.value:
indexes.append(index)
print(indexes)
sum = [0] * (len(indexes) + 1)
divisor = [0] * (len(indexes) + 1)
rows = [None] * (len(indexes) + 1)
# Auto fill the last row cause it wont read none from the sheet it just ends at the last row.
rows[len(indexes)] = ws.max_row
max_col = ws.max_column + 3
for col in ws.columns:
# this will start iterating only after it hits the ID section
if trigger == 1:
idx = 0
for index, cell in enumerate(col):
if not cell.value and cell.value != 0:
print(idx)
print("F**K")
rows[idx] = index
idx += 1
elif is_number(cell.value):
print(cell.value)
sum[idx] += cell.value
divisor[idx] += 1
print(cell.value)
if col[0].value == "ID":
trigger = 1
idx = 0
for index, i in enumerate(sum):
if divisor[index] == 0:
average = 0
else:
average = math.floor(i / divisor[index])
ws.cell(row=rows[index], column=max_col - 1).value = "Average"
ws.cell(row=rows[index], column=max_col).value = average
wb.save(excel_name)
def read_doc(filename):
doc = Document(filename)
# This will create an array for scores then fulllist which will seperate names from scores
lines = []
full_list = []
numbers = []
names = []
headers = []
contents = []
genders = []
ages = []
races = []
divide_by = None
group = " "
# Reads into the scores array through docx.
for para in doc.paragraphs:
# If its an empty line do this
if para.text == "":
continue
elif para.text.isspace():
continue
else:
lines.append(para.text)
# Creates a seperate value for Non name values and numbers etc
for index, x in enumerate(lines):
if "date" in x.lower():
header, content = x.split(":")
content = content.strip()
headers.append(header)
contents.append(content)
elif "facilitator" in x.lower():
header, content = x.split(":")
content = content.strip()
headers.append(header)
contents.append(content)
elif "topic" in x.lower():
header, content = x.split(":")
content = content.strip()
headers.append(header)
contents.append(content)
elif "week" in x.lower():
header, content = x.split(":")
content = content.strip()
headers.append(header)
if is_number(content):
contents.append(int(content))
else:
contents.append(content)
elif "group" in x.lower():
header, content = x.split(":")
# we need to keep the group, it is important for remaking the file.
group = content
content = content.strip()
headers.append(header)
contents.append(content)
# Gotta change the var to divide by since its total questions asked per person
elif "questions" in x.lower():
header, div = x.split(":")
headers.append(header)
div = div.strip()
try:
divide_by = int(div)
except ValueError:
raise Exception(
"Please enter a valid number (Digit not word) For Questions"
)
else:
name, score = x.split(":")
if is_number(score):
# Do the math for right vs total
score_num = int(score) / divide_by
numbers.append(int(score))
else:
score = score.strip()
numbers.append(score)
gender, age, race, ID = name.split(",")
genders.append(gender)
ages.append(int(age))
races.append(race)
names.append(ID)
newpath = 'C:/VantagePoint/Quizzes' + "/" + str(contents[4].strip())
if not os.path.exists(newpath):
os.makedirs(newpath)
excel_name = os.path.join(newpath + "/" + str(contents[2]) + "-" +
str(contents[4]) + ".xlsx")
docx_name = os.path.join(newpath + "/" + str(contents[2]) + "-" +
str(contents[4]) + ".docx")
# Copy the file so we can reset the original to make it more user friendly
if not filename == docx_name:
copyfile(filename, docx_name)
# remove all whitespace just in case
names = [x.strip(' ') for x in names]
# Call all the functions
excel(names, numbers, headers, contents, genders, ages, races, excel_name,
divide_by)
resize_columns(excel_name)
if not filename == docx_name:
remake_template(names, headers, contents, genders, ages, races,
filename, group)