def createExcelFile(Path, sheetName):
try:
wb = Workbook(Path)
ws = wb[sheetName]
wb.close()
except:
wb = Workbook()
ws = wb.create_sheet(sheetName)
wb.save(Path)
wb = openpyxl.load_workbook(Path)
wb.remove(wb['Sheet'])
wb.save(Path)
wb.close()
def write(self):
self.workbook = Workbook()
self.sheet = self.workbook.get_active_sheet()
if not len(self.header):
raise BRException("Header not provided")
if self.data and len(self.header) != len(self.data[0]):
raise BRException("Header and Data must be in same format")
# Write headers
row = 1
col = 1
for col_header in self.header:
self.sheet.cell(row=row, column=col, value=col_header)
col += 1
row += 1
# Now write data
for data_row in self.data:
col = 1
for col_value in data_row:
col_value = col_value if col_value else ''
self.sheet.cell(row=row, column=col, value=col_value)
col += 1
row += 1
if self.response:
self.workbook.save(self.response)
else:
if self.file_name:
self.workbook.save(self.file_name)
def generate(self):
self.activate()
repBook = Workbook()
currentSheet = repBook.active
for row in self.sheet.values:
if row[0] == "Tiempo":
currentSheet.append(["Tiempo", "Sensor 1" , "Sensor 2" , "Sensor 3" , "Sensor 4" ])
else:
a = row[0].split()[0].split('-')
print(self.fmes.get())
if int(a[0]) < int(self.fanio.get())-2000:
pass
elif int(a[0]) > int(self.sanio.get())-2000:
break
elif int(a[1]) < int(self.fmes.get()):
pass
elif int(a[1]) > int(self.smes.get()):
break
elif int(a[2]) < int(self.fdia.get()):
pass
elif int(a[2]) > int(self.sdia.get()):
break
else:
currentSheet.append(row)
repBook.save("Reporte.xlsx")
def createOutputExcel(outputData):
wb = Workbook()
ws = wb.active
ws.title = 'Budget Projection'
columnNames = [
"Name", "SA Rate", "Deltek Rate", "Avg Run", "Hours", "Dollars"
]
for w in range(1, nTotalNumWeeks + 1):
nThYear = int(w / 54)
nYear = 2020 + int(nThYear)
nWeek = w - (53 * nThYear)
columnNames.append(
"Week " + str(nWeek) + " \n[" +
date.fromisocalendar(nYear, nWeek, 7).strftime("%m/%d/%y") + "]")
ws.freeze_panes = 'G3'
headerCnt = len(columnNames)
for cells in ws.iter_rows(1, 1, 1, headerCnt):
for cell in cells:
cell.value = columnNames[cell.column - 1]
cell.alignment = Alignment(wrapText=True, horizontal='center')
rowNum = 2
rowCnt = len(outputData) + 1
for name, stats in outputData.items():
for cells in ws.iter_rows(rowNum, rowNum, 1, headerCnt):
cells[0].value = name
ws.column_dimensions[cells[0].column_letter].width = 25
cells[1].value = stats['SpringAhead Rate']
cells[1].number_format = '"$"#'
ws.column_dimensions[cells[1].column_letter].width = 8
cells[2].value = stats['Deltek Rate']
cells[2].number_format = '"$"#'
ws.column_dimensions[cells[2].column_letter].width = 8
cells[3].value = stats['Avg Run']
cells[3].number_format = '0.00'
ws.column_dimensions[cells[3].column_letter].width = 7
cells[4].value = stats['Hours']
cells[4].number_format = '#,##0.00'
ws.column_dimensions[cells[4].column_letter].width = 9
cells[5].value = stats['Dollars']
cells[5].number_format = '"$"#,##0.00'
ws.column_dimensions[cells[5].column_letter].width = 14
for w in range(1, nTotalNumWeeks + 1):
cells[w + 5].value = float(stats[w])
if rowNum > rowCnt:
cells[w + 5].number_format = '"$"#,##0.00'
else:
cells[w + 5].number_format = '#,##0.00'
ws.column_dimensions[cells[w + 5].column_letter].width = 12
ws.column_dimensions[cells[w + 5].column_letter].height = 29
rowNum = rowNum + 1
setExcelFormulas(ws, rowCnt, headerCnt)
wb.save(outputFile)
def _xlsx_save(self, result: [dict], file_name: str):
"""data is saved in excel format"""
wb = Workbook()
sheet = wb.active
for i, ldict in enumerate(result, 1):
if i == 1:
title_list: list = list(ldict.keys())
sheet.append(title_list)
sheet.append(list(ldict.values()))
wb.save(fr"{file_name}.xlsx")
def btnDownloadClicked(self):
result_file = Workbook()
sheet1 = result_file.active
sheet1.title = 'result'
idx = ["업무(결과값)", "수행 내용", "이름", "직급", "직급 NO."]
for i in range(5):
sheet1.cell(1, i+1).value = idx[i]
for cell in self.result:
sheet1.append(cell)
result_file.save('./result' + datetime.today().strftime("%Y%m%d%H%M%S") + '.xlsx')
QtWidgets.QMessageBox.about(self, "저장완료", "파일이 저장되었습니다.")
def create(cls) -> ExcelSheet:
"""Creates an excel worksheet from nothing
Factory method to initialize an object
Returns
-------
excel worksheet:
an opened new and empty excel worksheet object
"""
wb = Workbook()
return cls(wb)
def checkHolds(port, driver):
driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ0087_00000:_idsc00001:_idsc00003_6:_idsc00007").click()
# driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ00Q4_00000:_idsc00001:portId").send_keys(port)
driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ00Q4_00000:_idsc00001:_idsc00020").click()
onHold = []
bonds = []
done = False
j=2
driver.implicitly_wait(0)
try:
driver.find_element_by_css_selector("span[title='Page 1']").click()
except:
pass
driver.implicitly_wait(60)
while not done:
cells = driver.find_elements_by_css_selector("table[class='datat']>tbody>tr>td")
i=0
while i<min(1500, len(cells)):
driver.execute_script("arguments[0].scrollIntoView();", cells[i])
# bonds.append(cells[i+3].text)
# if not todaysDate in cells[i+10].text:
if "Y" in cells[i+14].text:
onHold.append(cells[i+3].text)
# cells[i+3].find_element_by_tag_name("input").send_keys(port)
i+=15
# else:
# break
driver.implicitly_wait(0)
try:
driver.find_element_by_css_selector("span[title='Page " +str(j)+ "']").click()
j+=1
except:
done=True
driver.implicitly_wait(60)
if len(bonds)>0:
filepath = r"J:\INBOND CLOSURE TRACKING\Open Bonds - " + datetime.now().strftime("%Y-%m-%d %H-%M-%S") + ".xlsx"
wb = Workbook()
ws = wb.active
for i in range(len(bonds)):
ws.cell(row=i+1, column=1).value = bonds[i]
wb.save(filepath)
if len(onHold)>0:
print("IN-BOND #S ON HOLD:")
holdString = "IN-BOND #S ON HOLD:\n"
for bond in onHold:
print(bond)
holdString = holdString+bond + "\n"
HelperFunctions.popUpOK(holdString)
def __write_wb(self, wb_name, worksheet_data):
dest_wb = Workbook(write_only=True)
for ws_name, ws_data in worksheet_data.iteritems():
if ws_data == []:
continue
ws = dest_wb.create_sheet(title=ws_name)
ws_data_transposed = zip(*ws_data)
for row in ws_data_transposed:
ws.append(row)
dest_wb.save(os.path.join(self.output_dir, wb_name))
def store2xlsx(self):
'''
select company info and store to xlsx
'''
odb = OperateDB()
# current date
datee = datetime.datetime.now().strftime("%Y-%m-%d")
fileAbsPath = self.filePath.replace('.xlsx', datee + '.xlsx')
wb = Workbook()
sheet = wb.active
# print sheet.max_row
# 设置单元格所有框线
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# 写标题
for i in range(self.__indexName.__len__()):
_ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i])
stockInfos = odb.selectFromCompany(
MyUtil.selectCompany.replace('%s', MyUtil.indexs))
keyList = stockInfos.keys()
# 按照公司代码排序
keyList.sort()
# print keyList
# 第一行为标题，从第二行开始为实际数据
rowNum = 2
for i in keyList:
for j in range(len(stockInfos[i])):
_ = sheet.cell(column=j + 1,
row=rowNum,
value=stockInfos[i][j])
sheet.cell(column=j + 1, row=rowNum).border = thin_border
rowNum += 1
# 每100条保存一下
if int(i) % 100 == 0:
wb.save(fileAbsPath)
# 最后保存一下
wb.save(fileAbsPath)
def convert_to_xlxs(content: Any) -> Workbook:
"""Convert old workbook formats xls into xlxs"""
xlsBook = xlrd.open_workbook(file_contents=content)
workbook = Workbook()
for i in range(0, xlsBook.nsheets):
xlsSheet = xlsBook.sheet_by_index(i)
sheet = workbook.active if i == 0 else workbook.create_sheet()
sheet.title = xlsSheet.name
for row in range(0, xlsSheet.nrows):
for col in range(0, xlsSheet.ncols):
sheet.cell(row=row + 1,
column=col + 1).value = xlsSheet.cell_value(
row, col)
return workbook
def convert_to_xlxs(fh: BinaryIO) -> Optional[Workbook]:
"""Convert old workbook formats xls into xlxs"""
if not _HAVE_XLRD:
logger.error("xlrd module not installed. Cannot convert XLS file.")
return None
file_content = fh.read()
fh.seek(0)
xlsBook = xlrd.open_workbook(file_contents=file_content)
workbook = Workbook()
for i in range(0, xlsBook.nsheets):
xlsSheet = xlsBook.sheet_by_index(i)
sheet = workbook.active if i == 0 else workbook.create_sheet()
sheet.title = xlsSheet.name
for row in range(0, xlsSheet.nrows):
for col in range(0, xlsSheet.ncols):
sheet.cell(row=row + 1, column=col + 1).value = xlsSheet.cell_value(row, col)
return workbook
def __init__(self, archive, workbook_part_name):
self.archive = archive
self.workbook_part_name = workbook_part_name
self.wb = Workbook()
self.sheets = []
# if no from_loc given, assume last sheet
if from_loc is None:
from_loc = len(sheets) - 1
# if no to_loc given, assume first
if to_loc is None:
to_loc = 0
sheet = sheets.pop(from_loc)
sheets.insert(to_loc, sheet)
if __name__ == "__main__":
try:
ato_report = filedialog.askopenfilename(title="ato_report",
filetypes=[("All files", "*")])
storage_report = filedialog.askopenfilename(title="storage_report",
filetypes=[("All files",
"*")])
summary_workbook = Workbook()
ws = summary_workbook.active
ws.title = "Summary"
summary_workbook.save("Summary.xlsx")
ato = ATO_check(ato_report, storage_report)
ato.start_to_run()
input("Press <Enter>")
except Exception as err:
messagebox.showerror("Warning!", err)
with open(os.path.join(os.getcwd(), "error.txt"), "w+") as f:
traceback.print_exc(file=f)
print(err)
exit()
def save_trade(self, order: OrderData, grid: CtaGrid):
"""
将交易费用保存于excel
"""
if self.get_engine_type() == EngineType.BACKTESTING:
return
trade_save_path = get_folder_path('data')
xlsx_name = f"{self.strategy_name}_trade.xlsx"
xlsx_file = str(trade_save_path.joinpath(xlsx_name))
if os.path.exists(xlsx_file):
workbook = load_workbook(xlsx_file)
my_sheet = workbook.get_sheet_by_name("Mysheet")
row = my_sheet.max_row
row += 1
else:
workbook = Workbook()
my_sheet = workbook.create_sheet("Mysheet", index=0)
row = 1
head = [
'品种', '开仓时间', '平仓时间', '方向', '数量', '目标开仓价格', '目标平仓价格', '实际开仓价格',
'实际平仓价格', '开仓手续费', '平仓手续费', '平仓收益', '净收入', '滑点'
]
for i, item in enumerate(head):
my_sheet.cell(row, i + 1, item)
my_sheet.column_dimensions["A"].width = 20
my_sheet.column_dimensions["B"].width = 20
my_sheet.column_dimensions["C"].width = 20
row += 1
my_sheet.cell(row, 1, grid.vt_symbol)
my_sheet.cell(row, 2, grid.open_time)
my_sheet.cell(row, 3, datetime.datetime.now())
my_sheet.cell(
row, 4, grid.direction.value
if isinstance(grid.direction, Direction) else '')
my_sheet.cell(row, 5, grid.volume)
netpnl = grid.netpnl
if order.price > grid.open_price:
close_price = grid.close_price
else:
close_price = grid.stop_price
my_sheet.cell(row, 6, grid.open_price)
my_sheet.cell(row, 7, close_price)
real_open_price = 0
real_open_count = 0
real_open_price_num = len(grid.open_price_list)
for i in range(real_open_price_num):
temp_open_price = grid.open_price_list[i]
temp_open_volume = grid.open_volume_list[i]
real_open_price += temp_open_price * temp_open_volume
real_open_count += temp_open_volume
real_open_price = real_open_price / real_open_count
if real_open_price == 0:
# 防止程序过渡时价格为空
real_open_price = grid.open_price
real_close_price = 0
real_close_num = len(grid.close_price_list)
real_close_count = 0
for i in range(real_close_num):
temp_close_price = grid.close_price_list[i]
temp_close_volume = grid.close_volume_list[i]
real_close_price += temp_close_price * temp_close_volume
real_close_count += temp_close_volume
real_close_price = real_close_price / real_close_count
my_sheet.cell(row, 8, real_open_price)
my_sheet.cell(row, 9, real_close_price)
my_sheet.cell(row, 10, grid.open_fee)
my_sheet.cell(row, 11, grid.close_fee)
my_sheet.cell(row, 12, netpnl)
my_sheet.cell(row, 13, netpnl - grid.open_fee - grid.close_fee)
slip_page = round(
abs(grid.open_price - real_open_price) +
abs(close_price - real_close_price), 7)
my_sheet.cell(row, 14, slip_page)
workbook.save(xlsx_file)
def __init__(self, name: str):
self.name = name
if not os.path.exists(name):
save_workbook(Workbook(), name)
self._wb = xl.load_workbook(name, keep_vba=True)
directoryDestination.mkdir(exist_ok = True)
bigDict = {}
indexDictionary = {}
columnHeaders = []
isListBuilt = False
wb: Workbook
ws: Worksheet
workbook_filename: str = 'jsonData.xlsx'
try:
wb = openpyxl.load_workbook(workbook_filename)
ws = wb['data']
except FileNotFoundError:
print('Workbook not found, creating new workbook')
wb = Workbook()
ws = wb.active
ws.title = 'data'
wb.save('jsonData.xlsx')
# program options
options = ['movement','pull data','push data']
for index, operation in enumerate(options):
print(str(index) + ': ' + operation)
# input
operationIndex: str = input("Pick operation (enter digit):")
print("\n")
print("\n")
directoryToCrawl: Path
def __init__(self):
self.wb = Workbook()
self.ws = self.wb.active
self.ws.append(["奖项类型", "获奖序号", "项目名称", "主要完成单位", "主要完成人", "获奖证书编号", "奖种", "等级"])
def write_infos(self, infos: List[Info]) -> None:
'atomically write all the infos'
def clean(descrizione: str) -> str:
for prefix in ('AD.COM.LE DA TR. NEL ', 'AD.REG.LE DA TR. NEL ',
'TICKET PASTO'):
if descrizione.startswith(prefix):
return f'{prefix}*'
return descrizione
# collect all details of all infos:
details = list(
sorted({
clean(additional_detail.descrizione)
for info in infos
for additional_detail in info.additional_details
}))
# there are 1 + len(keys)-1 + len(details) columns
header: List[Tuple[E, str]] = [('month', NUMBER_FORMAT_TEXT)]
for column_header in ColumnHeader:
if column_header is not ColumnHeader.detail:
header.append((column_header.name, NUMBER_FORMAT_TEXT))
else:
for detail in details:
header.append((detail, NUMBER_FORMAT_TEXT))
rows: List[List[Tuple[E, str]]] = [header]
for info in infos:
# group columns by column_header
columns = {column.header: column for column in info.columns}
# group additional_details by descrizione
additional_details = {
clean(additional_detail.descrizione): additional_detail
for additional_detail in info.additional_details
}
row: List[Tuple[E, str]] = [(info.when, NUMBER_FORMAT_DATE)]
for column_header in ColumnHeader:
if column_header is not ColumnHeader.detail:
row.append((columns[column_header].howmuch,
VALUE_NUMBER_FORMAT[column_header]
) if column_header in columns else (
None, NUMBER_FORMAT_TEXT))
else:
for detail in details:
# cosa esportare nell'xls?
row.append((additional_details[detail].competenze -
additional_details[detail].trattenute,
NUMBER_FORMAT_NUMBER
) if detail in additional_details else (
None, NUMBER_FORMAT_TEXT))
rows.append(row)
widths: Dict[str, int] = {}
for row in rows:
for i, cell in enumerate(row, start=1):
column_letter = get_column_letter(i)
widths[column_letter] = max(widths.get(column_letter, 0),
2 * len(str(cell[0])))
workbook = Workbook(write_only=True)
try:
# create the main sheet
sheet = workbook.create_sheet('main')
# first set the width of the columns
for column_letter, width in widths.items():
sheet.column_dimensions[column_letter].width = width
# then add the data
for row in rows:
sheet.append([
self._cell(sheet, value, number_format)
for value, number_format in row
])
finally:
workbook.save(self.name)
for origin_file in origin_folder_glob:
origin_file_name = origin_file[len(origin_folder) + 1:]
input_file_name = locals()['origin_file_name'][:-4] + "_input.txt"
input_file = origin_folder + "_inputs/" + input_file_name
prepare_tbschema_file(origin_file, input_file)
server_name = origin_file_name.split('-')[1]
database_name = origin_file_name.split('-')[2][:-4]
if server_name not in list(servers_dictionnary.keys()):
servers_dictionnary[server_name] = Server(server_name, database_name,
input_file)
else:
servers_dictionnary[server_name].add_database_to_dictionnary(
database_name, input_file)
excel_file = Workbook()
ws = excel_file.active
ws.title = "Summary"
ws.cell(row=1, column=1, value='ACTION')
ws.cell(row=1, column=2, value='COMPL.')
ws.cell(row=1, column=3, value='DB')
ws.cell(row=1, column=4, value='TABLE')
tbl_cat_dict = {}
idx_cat_dict = {}
rvk_cat_dict = {}
grt_cat_dict = {}
server_column = 5
line_number = 2
def index(request):
place = "McDonald's"
state = StateProvince.objects.get(code="MG")
# 1. Filtragem por precisão e duração.
# Eliminar registros com precisão maior que 100m e duração maior que 90min.
visits = Visit.objects.filter(
precision__lt=100, total_time_in_min__lt=90,
place__name=place, place__address__city__state__code=state)
count_visits_total = visits.count()
count_users_total = visits.values('user_id').annotate(
count=Count('user_id')).count()
infos = dict()
series_days_of_week = [
{'name': 'Sunday', 'values':[]},
{'name': 'Monday', 'values':[]},
{'name': 'Tuesday', 'values':[]},
{'name': 'Wednesday', 'values':[]},
{'name': 'Thursday', 'values':[]},
{'name': 'Friday', 'values':[]},
{'name': 'Saturday', 'values':[]}
]
series_day_periods = [
{'name': 'Manhã', 'values':[]},
{'name': 'Almoço', 'values':[]},
{'name': 'Tarde', 'values':[]},
{'name': 'Noite', 'values':[]},
{'name': 'Madrugada', 'values':[]},
]
for visit in visits:
key = "{}-{}".format(visit.place, visit.place.address.zip_code)
if not infos.get(key):
_visits=visits.filter(place=visit.place)
item = dict(
visit=visit
# 2. Contagem de visitas e usuários aos PDVs.
,count_visits=_visits.count()
,count_users=_visits.values(
'user_id').annotate(count=Count('user_id')).count()
# 3. Criação de visualização dos dias da semana das visitas aos PDVs.
,count_days_of_week=[
{week_day[1]:_visits.filter(arrival__week_day=week_day[0]).count()}
for week_day in Visit.DAYS_OF_WEEK ]
# 4. Criação de visualização dos períodos do dia das visitas aos PDVs. (Manhã, Almoço, Tarde, Noite)
,count_day_periods=[
{day_period[0]:_visits.filter(
arrival__time__range=(day_period[1], day_period[2])).count()}
for day_period in Visit.DAY_PERIOD]
# 5. Criação de visualização de visitas antes e visitas depois de visitas aos PDVs
)
infos[key] = item
# generate series to chart days of week
if item.get('count_visits') > 100:
for dayweek in item.get('count_days_of_week'):
for serie in series_days_of_week:
if serie['name'] in dayweek:
_values = list(dayweek.items())
serie['values'].append(_values[0][1])
for dayperiod in item.get('count_day_periods'):
for serie in series_day_periods:
if serie['name'] in dayperiod:
_values = list(dayperiod.items())
serie['values'].append(_values[0][1])
context = dict(
infos=infos
,count_visits=count_visits_total
,count_users=count_users_total
,series_days_of_week=series_days_of_week
,series_day_periods=series_day_periods
)
if 'reports' in request.POST:
wb = Workbook()
wb.remove_sheet(wb.worksheets[0])
sh = wb.create_sheet('vivists')
sh.append([
'_ID'
,'EUID'
,'ARRIVAL'
,'DEPARTURE'
,'PDV'
,'STREET'
,'POSTCODE'
,'SUBURB'
,'CITY'
,'VISITS'
,'CUSTOMERS'
,'COUNT_DAY_PERIODS'
,'COUNT_DAYS_OF_WEEK'
])
for item in infos.values():
# import pdb ; pdb.set_trace()
visit = item.get('visit')
sh.append([
visit.code
,visit.user_id
,visit.arrival.strftime('%d/%m/%Y')
,visit.departure.strftime('%d/%m/%Y')
,visit.pdv
,visit.place.address.street or ''
,visit.place.address.zip_code or ''
,visit.place.address.suburb or ''
,visit.place.address.city.name
,item.get('count_visits')
,item.get('count_users')
,json.dumps(item.get('count_day_periods'))
,json.dumps(item.get('count_days_of_week'))
])
response = HttpResponse(save_virtual_workbook(
wb), content_type="application/vnd.ms-excel")
response['Content-Disposition'] = "attachment; filename=visits.xlsx"
return response
return render(request, 'index.html', context)
# from openpyxl import Workbook
import openpyxl
import os.path
from openpyxl.workbook.workbook import Workbook
xlsx_file = './Coin_Trading_Bot.xlsx'
sheet = 'trading_bot_revenue'
# workbook 생성 (덮어쓰기)
# write_wb = Workbook()
if os.path.isfile(xlsx_file):
write_wb = openpyxl.load_workbook(xlsx_file)
else:
write_wb = Workbook()
write_ws = write_wb.create_sheet(sheet)
# Set write worksheet
sheet_list = write_wb.sheetnames
flag = 0
for sheet_name in sheet_list:
if sheet_name == 'Sheet':
# 기본 생성 시트 제거
write_wb.remove(write_wb[sheet_name])
if sheet_name == sheet:
flag = 1
break
if flag == 0:
write_ws = write_wb.create_sheet('trading_bot_revenue')
else:
write_ws = write_wb['trading_bot_revenue']
def __init__(self, archive):
self.archive = archive
self.wb = Workbook()
self.sheets = []
self.rels = get_dependents(self.archive, ARC_WORKBOOK_RELS)
def write_xlsx(self, filename: str) -> None:
"""
Writes the solution to an Excel XLSX file (and its problem, for data
safety).
Args:
filename:
Name of file to write.
"""
log.info(f"Writing output to: {filename}")
# Not this way -- we can't then set column widths.
# wb = Workbook(write_only=True) # doesn't create default sheet
# Instead:
wb = Workbook()
wb.remove(wb.worksheets[0])
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Allocations, by student
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ss = wb.create_sheet(SheetNames.STUDENT_ALLOCATIONS)
ss.append([
"Student",
"Project",
"Supervisor",
"Student's rank of allocated project (dissatisfaction score)",
])
for student, project in self._gen_student_project_pairs():
ss.append([
student.name,
project.title,
project.supervisor_name(),
student.dissatisfaction(project),
])
autosize_openpyxl_worksheet_columns(ss)
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Allocations, by project
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ps = wb.create_sheet(SheetNames.PROJECT_ALLOCATIONS)
ps.append([
"Project",
"Supervisor",
"Student(s)",
"Students' rank(s) of allocated project (dissatisfaction score)",
"Project supervisor's rank(s) of allocated student(s) (dissatisfaction score)", # noqa
])
for project in self.problem.sorted_projects():
student_names = [] # type: List[str]
supervisor_dissatisfactions = [] # type: List[float]
student_dissatisfactions = [] # type: List[float]
for student in self.allocated_students(project):
student_names.append(student.name)
supervisor_dissatisfactions.append(
project.dissatisfaction(student))
student_dissatisfactions.append(
student.dissatisfaction(project))
ps.append([
project.title,
project.supervisor_name(),
", ".join(student_names),
", ".join(str(x) for x in student_dissatisfactions),
", ".join(str(x) for x in supervisor_dissatisfactions),
])
autosize_openpyxl_worksheet_columns(ps)
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Popularity of projects
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
pp = wb.create_sheet(SheetNames.PROJECT_POPULARITY)
pp.append([
"Project",
"Supervisor",
"Total dissatisfaction score from all students",
"Allocated student(s)",
"Number of students expressing a preference",
"Students expressing a preference",
])
proj_to_unpop = {} # type: Dict[Project, float]
for project in self.problem.projects:
unpopularity = 0
for student in self.problem.students:
unpopularity += student.dissatisfaction(project)
proj_to_unpop[project] = unpopularity
for project, unpopularity in sorted(proj_to_unpop.items(),
key=operator.itemgetter(1, 0)):
allocated_students = ", ".join(
student.name for student in self.allocated_students(project))
student_prefs = {} # type: Dict[Student, float]
for student in self.problem.students:
if student.preferences.actively_expressed_preference_for(
project):
student_prefs[student] = student.preferences.preference(
project)
student_details = [] # type: List[str]
for student, studpref in sorted(student_prefs.items(),
key=operator.itemgetter(1, 0)):
student_details.append(f"{student.name} ({studpref})")
pp.append([
project.title,
project.supervisor_name(),
unpopularity,
allocated_students,
len(student_details),
", ".join(student_details),
])
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Software, settings, and summary information
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
zs = wb.create_sheet(SheetNames.INFORMATION)
is_stable, instability_reason = self.stability()
zs_rows = [
["SOFTWARE DETAILS"], [], ["Software", "pdn_project_allocation"],
["Version", VERSION], ["Version date", VERSION_DATE],
[
"Source code",
"https://github.com/RudolfCardinal/pdn_project_allocation"
], ["Author", "Rudolf Cardinal ([email protected])"], [],
["RUN INFORMATION"], [], ["Date/time",
datetime.datetime.now()],
[
"Overall weight given to student preferences",
1 - self.problem.config.supervisor_weight
],
[
"Overall weight given to supervisor preferences",
self.problem.config.supervisor_weight
], ["Command-line parameters",
cmdline_quote(sys.argv)], ["Config",
str(self.problem.config)], [],
["SUMMARY STATISTICS"], [],
[
"Student dissatisfaction median",
self.student_dissatisfaction_median()
],
[
"Student dissatisfaction mean",
self.student_dissatisfaction_mean()
],
[
"Student dissatisfaction variance",
self.student_dissatisfaction_variance()
],
[
"Student dissatisfaction minimum",
self.student_dissatisfaction_min()
],
[
"Student dissatisfaction minimum",
self.student_dissatisfaction_max()
], [],
[
"Supervisor dissatisfaction (with each student) median",
self.supervisor_dissatisfaction_median()
],
[
"Supervisor dissatisfaction (with each student) mean",
self.supervisor_dissatisfaction_mean()
],
[
"Supervisor dissatisfaction (with each student) variance",
self.supervisor_dissatisfaction_variance()
],
[
"Supervisor dissatisfaction (with each student) minimum",
self.supervisor_dissatisfaction_min()
],
[
"Supervisor dissatisfaction (with each student) maximum",
self.supervisor_dissatisfaction_max()
], [], ["Stable marriages?", str(is_stable)],
["If unstable, reason:", instability_reason]
]
for row in zs_rows:
zs.append(row)
autosize_openpyxl_column(zs, 0)
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Problem definition
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
self.problem.write_to_xlsx_workbook(wb)
wb.save(filename)
wb.close()
