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 validate_sheet_names(
*,
wb: Workbook,
filename: Union[Path, str],
sheet_name: str,
sheet_names: List[str],
start_time: float,
original_stdout: io.TextIOWrapper,
output_url: str
) -> Workbook:
"""
Parameters
----------
wb : Workbook
A workbook.
filename : Union[Path, str]
The file containing the workbook.
sheet_name : str
A sheet name in the workbook.
sheet_names : List[str]
The sheet names in the workbook.
start_time : float
The start time of the script.
original_stdout : io.TextIOWrapper
The buffered text stream for the html output.
output_url : str
The html filename.
Returns
-------
wb : Workbook
The workbook with a revised sheetname.
Example
-------
>>> import datasense as ds
>>> wb = validate_sheet_names(
>>> wb=wb,
>>> filename=filename,
>>> sheet_name=sheet_name,
>>> sheet_names=sheet_names,
>>> start_time=start_time,
>>> original_stdout=original_stdout,
>>> output_url=output_url
>>> )
"""
if sheet_name not in sheet_names and len(sheet_names) != 1:
print('Manually rename one of these sheets:')
print(wb.sheetnames)
print('Then re-run script')
print('XXX File NOT OK XXX')
stop_time = time.time()
report_summary(
start_time=start_time,
stop_time=stop_time
)
exit_script(
original_stdout=original_stdout,
output_url=output_url
)
elif sheet_name not in sheet_names and len(sheet_names) == 1:
print('One sheet found and it was re-named.')
ws = wb.active
ws.title = sheet_name
wb.save(filename=filename)
elif sheet_name in sheet_names and len(sheet_names) != 1:
sheet_names_removed = [x for x in sheet_names if x != sheet_name]
for sheet in sheet_names_removed:
wb.remove(worksheet=wb[sheet])
print('Sheet names removed:')
print(sheet_names_removed)
wb.save(filename=filename)
return wb
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']
# 작업할 workbook 내 sheet 활성화
#write_ws = write_ws.active
write_ws['A1'] = '날짜'
write_ws['B1'] = '티커' # 암호화폐 구분
write_ws['C1'] = '구분' # 매도, 매수 구분
write_ws['D1'] = '매수금액' # 매수 금액
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()
