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_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 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 write_xlsx(self, file: Union[str, BinaryIO]) -> None:
"""
Write the contents in XLSX (Excel) format to a file.
Args:
file: filename or file-like object
"""
if XLSX_VIA_PYEXCEL: # use pyexcel_xlsx
data = self._get_pyexcel_data(convert_for_openpyxl)
pyexcel_xlsx.save_data(file, data)
else: # use openpyxl
# Marginal performance gain with write_only. Does not automatically
# add a blank sheet
wb = XLWorkbook(write_only=True)
valid_name_dict = self.get_pages_with_valid_sheet_names()
for page, title in valid_name_dict.items():
ws = wb.create_sheet(title=title)
page.write_to_openpyxl_xlsx_worksheet(ws)
wb.save(file)
def build_excel_response(field_names,data,filename):
try:
wb = Workbook(write_only=True)
ws = wb.create_sheet()
ws.append(field_names)
for item in data:
ws.append(item.values())
s = StringIO()
wb.save(s)
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = u'attachment; filename={}.xlsx'.format(filename)
response['Content-Length'] = s.len
response.write(s.getvalue())
s.close()
return response
except Exception, exc:
return build_exception_response()
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
# 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 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)
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)
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 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()
