def test_rewrite_styles(self):
"""Test to verify Bugfix # 46"""
self.worksheet['A1'].value = 'Value'
self.worksheet['B2'].value = '14%'
saved_wb = save_virtual_workbook(self.workbook)
second_wb = load_workbook(BytesIO(saved_wb))
assert isinstance(second_wb, Workbook)
ws = second_wb.get_sheet_by_name('Sheet1')
assert ws.cell('A1').value == 'Value'
ws['A2'].value = 'Bar!'
saved_wb = save_virtual_workbook(second_wb)
third_wb = load_workbook(BytesIO(saved_wb))
assert third_wb
def publishexcel(request):
if request.method == 'GET':
workbook = Workbook()
exceltype = request.GET.get('exceltype')
filterby = request.GET.get('filterby')
filterval = request.GET.get('filterval')
all_tabs = ["program", "institution", "fundedsite", "group", "committee", "protocol", "person", "publication", "organ", "species", "specimentype", "discipline", "disease", "degree"]
if exceltype != "all":
all_tabs = [exceltype]
for tab in all_tabs:
worksheet = workbook.create_sheet(tab)
excel = StringIO()
call_command('publishexcel', tab, filterby, filterval, stdout=excel)
excel.seek(0)
excel_resp = excel.read()
excel_resp = excel_resp.strip()
if excel_resp.startswith("b'"):
excel_resp = excel_resp[2:]
if excel_resp.endswith("'"):
excel_resp = excel_resp[:-1]
excel_resp = json.loads(excel_resp)
for row in excel_resp:
worksheet.append(row)
response = HttpResponse(content=save_virtual_workbook(workbook), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=ksdb.xlsx'
return response
def get(self, request, year, month, day):
try:
day = date(int(year), int(month), int(day))
except Exception as exc:
print(exc)
return redirect(self.url("list"))
self.scope_statement = None
if request.GET.get("scope_statement"):
try:
self.scope_statement = ScopeStatement.objects.get(
is_active=True, pk=request.GET["scope_statement"]
)
except Exception as exc:
print(exc)
return redirect(self.url("list"))
if request.GET.get("xlsx"):
response = HttpResponse(
save_virtual_workbook(create_groups_xlsx(day)),
content_type=(
"application/vnd.openxmlformats-officedocument."
"spreadsheetml.sheet"
),
)
response["Content-Disposition"] = 'attachment; filename="%s"' % (
"wochenrapport-%s.xlsx" % day.isoformat(),
)
return response
form = AssignDrudgesToGroupsForm(day=day, scope_statement=self.scope_statement)
return self.render_to_response(self.get_context_data(form=form))
def _cache_workbook(workbook, year, file_name_str="public_export_{} {}.xslx", workbook_is_public=True):
for filename in os.listdir(_cache_dir_path()):
if ".xslx" in filename:
if (workbook_is_public == True and filename.startswith("public")) or (workbook_is_public == False and filename.startswith("survey")):
os.remove("%s%s" % (_cache_dir_path(), filename))
with open(_cache_path(year, file_name_str, datetime.datetime.utcnow().strftime(DATE_FORMAT)), "w") as f:
File(f).write(save_virtual_workbook(workbook))
def get_excel_document(html, filename):
tables = get_tables(html)
wb = Workbook()
for _ in range(len(tables) - len(wb.worksheets)):
wb.create_sheet()
style = Style(
font=Font(bold=True),
fill=PatternFill(start_color='EEEEEE', end_color='EEEEEE',
fill_type='solid'),
border=Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin')),
alignment=Alignment(horizontal='center', vertical='center'),
)
for table, sheet in zip(tables, wb.worksheets):
sheet.title = table.title
for row in table.rows:
for cell in row.cells:
sheet.merge_cells(
start_row=cell.row.idx,
start_column=cell.col_idx,
end_row=cell.row.idx + cell.rowspan,
end_column=cell.col_idx + cell.colspan)
col_letter = get_column_letter(cell.col_idx)
sheet_cell = sheet[col_letter + str(cell.row.idx)]
sheet_cell.value = cell.text
if cell.tag == 'th':
sheet_cell.style = style
resp = Response(save_virtual_workbook(wb), mimetype=MIMETYPE)
resp.headers.add('Content-Disposition',
'attachment; filename={}.xlsx'.format(filename))
return resp
def create_workbook(workbook, template, data, filename, delimiter):
wb = load_workbook(workbook)
ws = wb.active
# this could all be accomplished by a list of lists but building a list
# for each row would be ugly. this seems more pythonic, and we can reuse
# for CSV export if need be.
t = loader.get_template(template)
rendered = smart_bytes(
t.render(data), encoding='utf-8', strings_only=False, errors='strict'
)
# reader requires an object which supports the iterator protocol and
# returns a string each time its next() method is called. StringIO
# provides an in-memory, line by line stream of the template data.
#reader = csv.reader(io.StringIO(rendered), delimiter="|")
reader = csv.reader(BytesIO(rendered), delimiter="|")
for row in reader:
ws.append(row)
# in memory response instead of save to file system
response = HttpResponse(
save_virtual_workbook(wb), content_type='application/ms-excel'
)
response['Content-Disposition'] = 'attachment;filename={}.xlsx'.format(
filename
)
return response
def test_save_vba():
path = os.path.join(DATADIR, 'reader', 'vba-test.xlsm')
wb = load_workbook(path, keep_vba=True)
buf = save_virtual_workbook(wb)
files1 = set(zipfile.ZipFile(path, 'r').namelist())
files2 = set(zipfile.ZipFile(StringIO.StringIO(buf), 'r').namelist())
assert files1.issubset(files2), "Missing files: %s" % ', '.join(files1 - files2)
def augmentlist(request):
newsequences=request.POST['oligos']
sequence_list=json.loads(newsequences)
try:
client=dropbox.client.DropboxClient(request.session['access_token'])
folder_metadata=client.metadata('/')
for obj in folder_metadata['contents']:
if 'mime_type' in obj and obj['mime_type'] == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
absolute_path=obj['path']
f=client.get_file(absolute_path)
s=io.BytesIO(f.read())
book=load_workbook(filename=s)
for sheet in book:
row=2
index=0
while sheet.cell(row=row,column=1).value!=None:
index=sheet.cell(row=row,column=1).value
row+=1
for seq in sequence_list:
index+=1
sheet.cell(row=row,column=1).value=index
sheet.cell(row=row,column=2).value=seq['name']
sheet.cell(row=row,column=3).value=seq['nonComplementary']+seq['complementary']
sheet.cell(row=row,column=5).value=seq['nonComplementary']+seq['complementary']
sheet.cell(row=row,column=6).value=seq['nonComplementary']
sheet.cell(row=row,column=7).value=seq['complementary']
row+=1
client.put_file(absolute_path,save_virtual_workbook(book),overwrite=True)
except Exception as e:
print(str(e))
traceback.print_exc()
raise e
return HttpResponse("success", content_type='application/text')
def download_field_map_by_field(request, field_id):
plot_loader = ObsTracker.objects.filter(obs_entity_type="plot", field_id=field_id)
field = Field.objects.get(id=field_id)
if len(plot_loader) == 0:
wb = field_map_generator.empty_field()
else:
plot_objects = []
for obs in plot_loader:
row_num = obs.obs_plot.row_num
range_num = field_map_generator._get_column_letter(int(obs.obs_plot.range_num))
plot_objects.append(
field_map_generator.PlotCell(
range_num=range_num,
row_num=row_num,
experiment=obs.experiment,
plot_id=obs.obs_plot.plot_id,
field=obs.field,
)
)
wb = field_map_generator.compile_info(plot_objects, field)
response = HttpResponse(save_virtual_workbook(wb), content_type="application/vnd.ms-excel")
response["Content-Disposition"] = 'attachment; filename="{}-map.xlsx"'.format(field.field_name)
return response
def export(self, customer):
work_book = Workbook()
work_sheet = work_book.active
work_sheet.title = "Boxs"
self._write_customer_title(work_sheet, customer)
self._write_customer_total_weight(work_sheet, customer)
work_sheet.row_dimensions[1].height = 30
work_sheet.row_dimensions[2].height = 30
self._write_empty_line(work_sheet)
line = 4
percent_top = 236
num_box = 1
for box in customer.box_set.all():
self._write_box_title(work_sheet, box)
self._write_product_table_header(work_sheet)
work_sheet.row_dimensions[3].height = 30
work_sheet.row_dimensions[4].height = 30
work_sheet.row_dimensions[line + 1].height = 30
work_sheet = column_dement(work_sheet)
line += 3
for product in box.productpurchase_set.all():
top = self._write_product_table_row(work_book, work_sheet, product, line, percent_top)
work_sheet.row_dimensions[line].height = 60
line = line + 2
percent_top = percent_top + 100
self._write_empty_line(work_sheet)
line = line + 1
percent_top = percent_top + 100
num_box = num_box + 1
return save_virtual_workbook(work_book)
def test_save_without_vba(datadir):
datadir.join("reader").chdir()
fname = "vba-test.xlsm"
vbFiles = set(
[
"xl/activeX/activeX2.xml",
"xl/drawings/_rels/vmlDrawing1.vml.rels",
"xl/activeX/_rels/activeX1.xml.rels",
"xl/drawings/vmlDrawing1.vml",
"xl/activeX/activeX1.bin",
"xl/media/image1.emf",
"xl/vbaProject.bin",
"xl/activeX/_rels/activeX2.xml.rels",
"xl/worksheets/_rels/sheet1.xml.rels",
"customUI/customUI.xml",
"xl/media/image2.emf",
"xl/ctrlProps/ctrlProp1.xml",
"xl/activeX/activeX2.bin",
"xl/activeX/activeX1.xml",
"xl/ctrlProps/ctrlProp2.xml",
"xl/drawings/drawing1.xml",
]
)
wb = load_workbook(fname, keep_vba=False)
buf = save_virtual_workbook(wb)
files1 = set(zipfile.ZipFile(fname, "r").namelist())
files2 = set(zipfile.ZipFile(BytesIO(buf), "r").namelist())
difference = files1.difference(files2)
assert difference.issubset(vbFiles), "Missing files: %s" % ", ".join(difference - vbFiles)
def get(self, request, *args, **kwargs):
all_ec_annuel = AllEcAnnuel.objects.all()
response = HttpResponse(save_virtual_workbook(self.create_spreadsheet(all_ec_annuel)),
content_type='application/vnd.ms-excel')
date = datetime.today().strftime('%d-%m-%Y')
response['Content-Disposition'] = 'attachment; filename={}_{}.xlsx'.format('etat_heure', date,)
return response
def construir_reporte(self, datos):
hoja = self.wb.create_sheet(0, "Consumo")
cont_col = ord("B")
for producto in self.productos:
str_col = str(chr(cont_col)+"1")
hoja.cell(str_col).value = producto.nombre
self.matriz[producto.id] = str(chr(cont_col))
cont_col += 1
cont_fila = 2
for nombre in self.clientes:
str_fila = "A" + str(cont_fila)
hoja.cell(str_fila).value = nombre
self.matriz[nombre] = str(cont_fila)
cont_fila += 1
for consumo in datos:
columna = self.matriz[consumo.id_producto]
fila = self.matriz[consumo.nombre]
hoja.cell(columna + fila).value = str(consumo.suma_monto)
# Metodo especial hecho para los response django
return save_virtual_workbook(self.wb)
def render_to_response(self, context, **response_kwargs):
if self.render_format == 'xlsx':
from openpyxl import Workbook
from openpyxl.writer.excel import save_virtual_workbook
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'Inventaire'
# Headers
headers = ['Matériel (salle %s, armoire %s)' % (self.object.room.number, self.object.code), 'Quantité', 'Unité']
for col_idx, header in enumerate(headers, start=1):
ws.cell(row=1, column=col_idx).value = header
ws.cell(row=1, column=col_idx).style.font.bold = True
# Data
for row_idx, tr in enumerate(context['quant_items'], start=2):
ws.cell(row=row_idx, column=1).value = str(tr.material)
ws.cell(row=row_idx, column=2).value = tr.quantity
ws.cell(row=row_idx, column=3).value = tr.material.unit
ws.cell(row=row_idx+2, column=1).value = "État au %s" % date.today()
ws.cell(row=row_idx+2, column=1).style.font.italic = True
ws.column_dimensions['A'].width = 60
response = HttpResponse(save_virtual_workbook(wb), content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=exportation_%s_%s.xlsx' % (
self.object.code.replace(' ', '_'), date.strftime(date.today(), '%Y-%m-%d'))
return response
else:
return super(StorageView, self).render_to_response(context, **response_kwargs)
def test_save_vba(datadir):
path = str(datadir.join('reader').join('vba-test.xlsm'))
wb = load_workbook(path, keep_vba=True)
buf = save_virtual_workbook(wb)
files1 = set(zipfile.ZipFile(path, 'r').namelist())
files2 = set(zipfile.ZipFile(BytesIO(buf), 'r').namelist())
assert files1.issubset(files2), "Missing files: %s" % ', '.join(files1 - files2)
def export_xls_zip_all_classes(StudentAdmin, request, queryset):
import openpyxl
from django.utils.encoding import smart_str
from openpyxl.utils import get_column_letter
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=journal.xlsx'
wb = openpyxl.Workbook()
wb.remove(wb.active)
pos = 1
columns = [
(u"ID", 15),
(u"ФИО", 20),
(u"Класс", 20),
(u"Оценка", 10),
(u"Комментарий", 40),
]
dv = DataValidation(type="decimal",
operator="between",
formula1=0,
formula2=10,
allow_blank=True)
for parallel in Parallel.objects.all():
for group in parallel.class2_set.all():
isinstance(group, Class2)
ws = wb.create_sheet(str(parallel) + str(group.name), pos)
ws.protection.sheet = True
ws.protection.password = '******'
pos += 1
row_num = 0
for col_num in xrange(len(columns)):
c = ws.cell(row=row_num + 1, column=col_num + 1)
c.value = columns[col_num][0]
#c.style.font.bold = True
# set column width
ws.column_dimensions[get_column_letter(col_num + 1)].width = columns[col_num][1]
for student in group.student_set.all():
row_num += 1
row = [
student.pk,
smart_str(student.fname + " " + student.lname + ' ' + student.fathers_name),
smart_str(str(parallel.name) + str(group.name)),
smart_str(''),
smart_str(''),
]
for col_num in xrange(len(row)):
c = ws.cell(row=row_num + 1, column=col_num + 1)
c.alignment = Alignment(wrap_text=True)
lock = False
if col_num + 1 <= 3:
lock = True
c.protection = Protection(locked=lock)
c.value = row[col_num]
response.write(save_virtual_workbook(wb))
return response
def download_sample_sheet(request, uuid):
wb = create_sample_sheet(uuid)
response = HttpResponse(
content=save_virtual_workbook(wb),
content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=microbesng_sample_sheet.xlsx'
return response
def get_report(self):
wb = Workbook()
ws = wb.active
fieldnames = [
'PK',
'Title',
'Arrangers',
'Composers',
'Lyricists',
'Holders',
'Status',
]
ws.append(fieldnames)
charts = self.order_by('title', 'arrangers')
for chart in charts:
pk = str(chart.pk)
title = chart.title
arrangers = chart.arrangers
composers = chart.composers
lyricists = chart.lyricists
holders = chart.holders
status = chart.get_status_display()
row = [
pk,
title,
arrangers,
composers,
lyricists,
holders,
status,
]
ws.append(row)
file = save_virtual_workbook(wb)
content = ContentFile(file)
return content
def generate_xls():
workbook = Workbook(encoding='utf-8')
worksheet = workbook.active
worksheet.title = "mandates"
worksheet.append([_(entity_type.SECTOR),
_(entity_type.FACULTY),
_(entity_type.LOGISTICS_ENTITY),
_(entity_type.INSTITUTE),
_("matricule"),
_("name"),
_("firstname"),
_("email"),
"FGS",
_("age"),
_("status"),
_("renewal_type"),
_("assistant_type"),
_("fulltime_equivalent"),
_("contract_duration_fte"),
_("contract_duration"),
_("entry_date_contract"),
_("end_date"),
_("comment"),
_("absences"),
_("sector_vice_rector_review"),
_("justification"),
_("comment"),
_("confidential"),
])
mandates = assistant_mandate.find_by_academic_year(academic_year.current_academic_year())
for mandate in mandates:
line = construct_line(mandate)
worksheet.append(line)
return save_virtual_workbook(workbook)
def create_xlsx(object_id, creative_ids):
wb = openpyxl.Workbook()
ws = wb.active
ws.append(['creative_id', 'object_id'])
for creative_id in creative_ids:
ws.append([creative_id, object_id])
return save_virtual_workbook(wb)
def export_xls(modeladmin, request, inscriptions):
print("export_xls")
wb = Workbook()
ws = wb.active
ws.append(HEADER)
ws.column_dimensions["A"].width = 30
ws.column_dimensions["C"].width = 30
ws.column_dimensions["D"].width = 30
ws.column_dimensions["G"].width = 30
ws.column_dimensions["H"].width = 30
cptr = 1
for inscription in inscriptions:
ws.append(
[
inscription.nom_equipe,
inscription.categorie,
inscription.nom_participant_1,
inscription.nom_participant_2,
inscription.taille_t_shirt_1,
inscription.taille_t_shirt_2,
inscription.responsable_email,
inscription.responsable_telephone,
inscription.reglement_ok,
inscription.challenge_raid_trophy,
]
)
cptr = cptr + 1
response = HttpResponse(content=save_virtual_workbook(wb))
response["Content-Disposition"] = "attachment; filename=inscriptions.xlsx"
response["Content-type"] = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
return response
def excel_export(self, request, queryset):
# Create the HttpResponse object with the appropriate CSV header.
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename="chemicals.xslx"'
template_path = os.path.abspath(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'chemistry_template.xlsx'))
wb = load_workbook(template_path)
ws1 = wb.active
cis = queryset
row_offset = 2
i = 0
for ci in cis:
_ = ws1.cell(column=1, row=i+row_offset, value='{}'.format(ci.chemical.name))
_ = ws1.cell(column=2, row=i+row_offset, value='{}'.format(ci.chemical.cas))
_ = ws1.cell(column=4, row=i+row_offset, value='{}'.format(ci.company))
_ = ws1.cell(column=5, row=i+row_offset, value='{}'.format(ci.item_number))
amount, unit = '', ''
for char in ci.quantity:
if char.isalpha():
unit += char
else:
amount += char
_ = ws1.cell(column=6, row=i+row_offset, value='{}'.format(amount))
_ = ws1.cell(column=7, row=i+row_offset, value='{}'.format(unit))
_ = ws1.cell(column=8, row=i+row_offset, value='1')
_ = ws1.cell(column=9, row=i+row_offset, value='{}'.format(ci.chemical.state_of_matter))
_ = ws1.cell(column=10, row=i+row_offset, value='{}'.format(ci.storage_location))
_ = ws1.cell(column=11, row=i+row_offset, value='{}'.format(ci.group))
i += 1
response.write(save_virtual_workbook(wb))
return response
def export(self, customer):
work_book = Workbook()
work_sheet = work_book.active
work_sheet.column_dimensions['A'].width = 35
work_sheet.column_dimensions['B'].width = 60
work_sheet.column_dimensions['C'].width = 15
work_sheet.title = 'Boxs'
self._write_customer_title(work_sheet, customer)
self._write_customer_total_weight(work_sheet, customer)
self._write_empty_line(work_sheet)
work_sheet.row_dimensions[1].height = 30
work_sheet.row_dimensions[2].height = 30
work_sheet.row_dimensions[3].height = 30
line = 5
for box in customer.box_set.all():
self._write_box_title(work_sheet, box)
self._write_product_table_header(work_sheet)
for product in box.productpurchase_set.all():
self._write_product_table_row(work_book, work_sheet, product, line)
work_sheet.row_dimensions[line].height = 100
line = line+1
self._write_empty_line(work_sheet)
line = line+2
file_name = '%s.xlsx' % str(customer)
work_book.save(file_name)
return save_virtual_workbook(work_book)
def download_all_refvals(page):
refvals = get_refvals(page)
hd_table = LuHabitattypeCodes if page == 'habitat' else LuHdSpecies
code_to_name = {str(lu.code): lu.display_name for lu in hd_table.query}
data_struct = get_struct(refvals)
wb = Workbook()
wb.remove_sheet(wb.get_sheet_by_name('Sheet'))
for group, fields in data_struct.iteritems():
ws = wb.create_sheet()
ws.title = group
ws.append(fields)
sorted_keys = sorted(refvals.keys())
for key in sorted_keys:
code, region = key.split('-')
for group, values in refvals[key].iteritems():
xls_group = REFGROUPS[group]
ws = wb[xls_group]
values = {k.upper(): v for k, v in values.iteritems()}
values['COD'] = code
values['BIOREGIUNE'] = region
values['NUME'] = code_to_name.get(code, None)
to_insert = [values.get(k, None) for k in data_struct[xls_group]]
ws.append(to_insert)
response = Response(save_virtual_workbook(wb), mimetype=MIMETYPE)
response.headers.add('Content-Disposition',
'attachment; filename={}.xlsx'.format(page))
return response
def test_save_with_vba(datadir):
datadir.join('reader').chdir()
fname = 'vba-test.xlsm'
wb = load_workbook(fname, keep_vba=True)
buf = save_virtual_workbook(wb)
files = set(zipfile.ZipFile(BytesIO(buf), 'r').namelist())
expected = set(['xl/drawings/_rels/vmlDrawing1.vml.rels',
'xl/worksheets/_rels/sheet1.xml.rels',
'[Content_Types].xml',
'xl/drawings/vmlDrawing1.vml',
'xl/ctrlProps/ctrlProp1.xml',
'xl/vbaProject.bin',
'docProps/core.xml',
'_rels/.rels',
'xl/theme/theme1.xml',
'xl/_rels/workbook.xml.rels',
'customUI/customUI.xml',
'xl/styles.xml',
'xl/worksheets/sheet1.xml',
'xl/sharedStrings.xml',
'docProps/app.xml',
'xl/ctrlProps/ctrlProp2.xml',
'xl/workbook.xml',
'xl/activeX/activeX2.bin',
'xl/activeX/activeX1.bin',
'xl/media/image2.emf',
'xl/activeX/activeX1.xml',
'xl/activeX/_rels/activeX2.xml.rels',
'xl/media/image1.emf',
'xl/activeX/_rels/activeX1.xml.rels',
'xl/activeX/activeX2.xml',
])
assert files == expected
def export_xls(request, session_id, learning_unit_year_id, academic_year_id, is_fac):
academic_year = AcademicYear.find_academic_year(academic_year_id)
session_exam = SessionExam.find_session(session_id)
academic_calendar = AcademicCalendar.find_academic_calendar_by_event_type(academic_year_id,session_exam.number_session)
wb = Workbook()
ws = wb.active
__columns_ajusting(ws)
# masquage de la colonne avec l'id exam enrollment
ws.append(HEADER)
dv = __create_data_list_for_justification(is_fac)
ws.add_data_validation(dv)
cptr=1
for rec_exam_enrollment in ExamEnrollment.find_exam_enrollments(session_exam):
student = rec_exam_enrollment.learning_unit_enrollment.student
o = rec_exam_enrollment.learning_unit_enrollment.offer
person = Person.find_person(student.person.id)
if not rec_exam_enrollment.learning_unit_enrollment.learning_unit_year.credits is None:
credits = rec_exam_enrollment.learning_unit_enrollment.learning_unit_year.credits
if academic_calendar.end_date is None:
end_date="-"
else:
end_date = academic_calendar.end_date.strftime('%d/%m/%Y')
score=None
if not rec_exam_enrollment.score_final is None:
if rec_exam_enrollment.session_exam.learning_unit_year.decimal_scores :
score = "{0:.2f}".format(rec_exam_enrollment.score_final)
else:
score = "{0:.0f}".format(rec_exam_enrollment.score_final)
justification = ""
if rec_exam_enrollment.justification_final:
justification = dict(JUSTIFICATION_TYPES)[rec_exam_enrollment.justification_final]
ws.append([str(academic_year),
str(session_exam.number_session),
session_exam.learning_unit_year.acronym,
o.acronym,
student.registration_id,
person.last_name,
person.first_name,
score,
str(justification),
end_date,
rec_exam_enrollment.id
])
cptr = cptr+1
__coloring_non_editable(ws,cptr, rec_exam_enrollment.encoding_status,score,rec_exam_enrollment.justification_final)
dv.ranges.append('I2:I'+str(cptr+100))#Ajouter 100 pour si on ajoute des enregistrements
response = HttpResponse(content=save_virtual_workbook(wb))
response['Content-Disposition'] = 'attachment; filename=score_encoding.xlsx'
response['Content-type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
return response
def test_save_with_saved_comments(datadir):
datadir.join('reader').chdir()
fname = 'vba-comments-saved.xlsm'
wb = load_workbook(fname, keep_vba=True)
buf = save_virtual_workbook(wb)
files = set(zipfile.ZipFile(BytesIO(buf), 'r').namelist())
expected = set(zipfile.ZipFile(fname, 'r').namelist())
assert files == expected
def writeToXls(self, json_dict): self.recurseWriter(json_dict) response = HttpResponse(save_virtual_workbook(self.wb), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename="' + self.file_name + '"' print self.surveyHeaders return response
def get(self, request, *args, **kwargs):
b = Bordereau.unfiltered.get(pk=kwargs['bordereau'])
response = HttpResponse(save_virtual_workbook(self.create_spreadsheet(b)), content_type='application/vnd.ms-excel')
date = datetime.datetime.today().strftime('%d-%m-%Y')
response['Content-Disposition'] = 'attachment; filename={}_{}_{}_{}_{}.xlsx'.format('bordereau', b.type_paiement,
b.num_paiement, b.num_bordereau,
date,)
return response
def surveys_export(request):
if request.method == "POST":
survey_ids = request.POST.getlist("survey-response-ids", [])
filename = u"Exporterade enkätsvar ({}).xlsx".format(strftime("%Y-%m-%d %H.%M.%S"))
workbook = surveys_to_excel_workbook(survey_ids)
response = HttpResponse(save_virtual_workbook(workbook), content_type='application/vnd.ms-excel')
response['Content-Disposition'] = u'attachment; filename="{}"'.format(filename)
return response
def get(self, request, id, *args, **kwargs):
def update_status(current: Dict[str, int], total: Dict[str, int], status: str):
current[status.lower()] += 1
total[status.lower()] += 1
if status in ('Skipped', 'Blocked', 'Canceled'):
current['notrun'] += 1
total['notrun'] += 1
# main body
mapping_ids = request.GET.get('fmt_id').split(',')
# ensure that mode has one of two possible values
mode = request.GET.get('mode', 'single')
if mode not in ('single', 'combined'):
mode = 'single'
do_excel = False
if 'report' in request.GET and request.GET['report'] == 'excel':
do_excel = True
data = defaultdict(dict)
total_counters = {
'passed': 0,
'failed': 0,
'error': 0,
'blocked': 0,
'skipped': 0,
'canceled': 0,
'passrate': 0,
'execrate': 0,
'notrun': 0,
'total': 0
}
results = Result.objects.filter(validation_id=id)
# get mappings with preserved ids order from request
original_order = Case(*[When(pk=pk, then=position)
for position, pk in enumerate(mapping_ids)])
mappings = FeatureMapping.objects.filter(pk__in=mapping_ids).order_by(original_order)
for mapping in mappings:
mapping_data = defaultdict(dict)
mapping_total = {
'passed': 0,
'failed': 0,
'error': 0,
'blocked': 0,
'skipped': 0,
'canceled': 0,
'passrate': 0,
'execrate': 0,
'notrun': 0,
'total': 0
}
for milestone, scenario_id, feature, ids, total_value in \
FeatureMappingRule.objects.filter(mapping_id=mapping.id) \
.values_list('milestone__name', 'scenario_id', 'feature__name', 'ids', 'total'):
if mode == 'combined':
feature_name = f'{feature} ({mapping.codec.name})'
else:
feature_name = feature
if ids is not None:
total_value = len(ids.split(','))
# show Indicator report even if FMT is incorrect i.e. it does
# not have total value set
if total_value is None:
total_value = 0
mapping_data[milestone][feature_name] = {
'passed': 0,
'failed': 0,
'error': 0,
'blocked': 0,
'skipped': 0,
'canceled': 0,
'passrate': 0,
'execrate': 0,
'notrun': 0,
'total': total_value
}
mapping_total['total'] += total_value
if ids is not None:
ids = ids.split(',')
executed = results.filter(item__scenario_id=scenario_id,
item__test_id__in=ids).count()
for status in results.filter(item__scenario_id=scenario_id,
item__test_id__in=ids) \
.values_list('status__test_status', flat=True):
update_status(mapping_data[milestone][feature_name], mapping_total, status)
else:
executed = results.filter(item__scenario_id=scenario_id).count()
for status in results.filter(item__scenario_id=scenario_id) \
.values_list('status__test_status', flat=True):
update_status(mapping_data[milestone][feature_name], mapping_total, status)
not_executed = total_value - executed
# protect ourselves from broken FMTs which have None
# in total column and total = 0
if not_executed > 0:
mapping_data[milestone][feature_name]['notrun'] += not_executed
mapping_total['notrun'] += not_executed
passed = mapping_data[milestone][feature_name]['passed']
total = mapping_data[milestone][feature_name]['total']
not_run = mapping_data[milestone][feature_name]['notrun']
mapping_data[milestone][feature_name]['passrate'] = passed / total
mapping_data[milestone][feature_name]['execrate'] = (total - not_run) / total
if mode == 'single' and do_excel:
# split data by mappings
data[mapping.id]['items'] = copy.deepcopy(mapping_data)
data[mapping.id]['total'] = copy.deepcopy(mapping_total)
else:
# merge to one dict
for key, value in mapping_data.items():
for subkey, subvalue in value.items():
data[key][subkey] = subvalue
for key in ['total',
'passed',
'failed',
'error',
'blocked',
'skipped',
'canceled',
'notrun']:
total_counters[key] += mapping_total[key]
total_counters['passrate'] = total_counters['passed'] / total_counters['total']
total_counters['execrate'] = (total_counters['total'] -
total_counters['notrun']) / total_counters['total']
if not data:
if do_excel:
return Response()
return Response({'headers': [], 'items': []})
if not do_excel:
# Data-table formatting
headers, items = [], []
for label in ('Milestone',
'Feature',
'Total',
'Passed',
'Failed',
'Error',
'Blocked',
'Skipped',
'Canceled',
'Not Run',
'Pass Rate',
'Exec Rate'):
headers.append({
'text': label,
'value': label.replace(' ', '').lower(),
'groupable': True if label == 'Milestone' else False,
'width': 140 if label not in ('Milestone', 'Feature') else None,
})
for milestone, m_data in data.items():
for feature, f_data in m_data.items():
items.append({'milestone': milestone, 'feature': feature, **f_data})
items.append(total_counters)
return Response({'headers': headers, 'items': items})
else:
# Excel part
validation = Validation.objects.get(id=id)
if mode == 'combined':
excel_data = {'items': data, 'total': total_counters}
else:
excel_data = data
workbook = excel.do_indicator_report(excel_data, validation, mappings, mode)
filename = f'indicator_report_{datetime.now():%Y-%m-%d_%H:%M:%S}.xlsx'
response = HttpResponse(save_virtual_workbook(workbook),
content_type='application/ms-excel')
response['Content-Disposition'] = f'attachment; filename="{filename}"'
return response
def retrieve(self, request, *args, **kwargs): # pragma: no cover
repository_version = self.get_object()
serializer = RepositoryTranslatedImportSerializer(
data=request.query_params)
serializer.is_valid(raise_exception=True)
with_translation = serializer.data.get("with_translation")
of_the_language = serializer.data.get("of_the_language")
for_the_language = serializer.data.get("for_the_language")
queryset = RepositoryExample.objects.filter(
repository_version_language__repository_version=repository_version)
examples = repository_version.repository.examples(
queryset=queryset, version_default=repository_version.is_default
).annotate(translation_count=Count(
"translations", filter=Q(translations__language=for_the_language)))
if with_translation:
examples = examples.filter(
repository_version_language__language=of_the_language)
else:
examples = examples.filter(
repository_version_language__language=of_the_language,
translation_count=0,
)
examples = examples.order_by("created_at")
workbook = openpyxl.load_workbook(
f"{settings.STATIC_ROOT}/bothub/exporter/example.xlsx")
worksheet = workbook.get_sheet_by_name("Translate")
img = Image(f"{settings.STATIC_ROOT}/bothub/exporter/bothub.png")
img.width = 210.21
img.height = 60.69
worksheet.add_image(img, "B1")
entities_list = []
for count, example in enumerate(examples, start=14):
worksheet.insert_rows(count)
worksheet.cell(row=count, column=2, value=str(example.pk))
worksheet.cell(
row=count,
column=3,
value=str(
example.repository_version_language.repository_version.pk),
)
worksheet.cell(
row=count,
column=4,
value=str(example.repository_version_language.language),
)
text = example.text
entities = RepositoryExampleEntity.objects.filter(
repository_example=example).order_by("start")
count_entity = 0
for entity in entities:
if entity.entity.value not in entities_list:
entities_list.append(entity.entity.value)
text = utils.format_entity(
text=text,
entity=entity.entity.value,
start=entity.start + count_entity,
end=entity.end + count_entity,
)
count_entity += len(entity.entity.value) + 4
worksheet.cell(row=count, column=5, value=str(text))
translated = RepositoryTranslatedExample.objects.filter(
original_example=example.pk, language=for_the_language)
if translated:
translated = translated.first()
text_translated = translated.text
entities_translate = RepositoryTranslatedExampleEntity.objects.filter(
repository_translated_example=translated).order_by("start")
count_entity = 0
for entity in entities_translate:
text_translated = utils.format_entity(
text=text_translated,
entity=entity.entity.value,
start=entity.start + count_entity,
end=entity.end + count_entity,
)
count_entity += len(entity.entity.value) + 4
worksheet.cell(row=count, column=6, value=str(text_translated))
for count, entity in enumerate(entities_list, start=4):
worksheet.insert_rows(count)
worksheet.cell(row=count, column=2, value=str(entity))
response = HttpResponse(
content=save_virtual_workbook(workbook),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
)
response["Content-Disposition"] = "attachment; filename=bothub.xlsx"
return response
def export_xls(cohort, organization, affections_by_specialities, file_name):
if not affections_by_specialities:
redirect_url = reverse('place_detail_student_affectation',
kwargs={
'cohort_id': cohort.id,
'organization_id': organization.id,
})
return HttpResponseRedirect(redirect_url)
periods = mdl_internship.period.search(cohort=cohort)
workbook = Workbook()
if workbook.worksheets:
workbook.remove_sheet(workbook.worksheets[0])
for speciality, affectations in affections_by_specialities:
pattern = re.compile('Stage en|Intenship in', re.IGNORECASE)
sheet_title = pattern.sub('', speciality.name.strip())[0:30]
worksheet = workbook.create_sheet(title=sheet_title)
worksheet.append([str(organization.name) if organization else ''])
worksheet.append([str(speciality.name)])
worksheet.append([str('')])
date_format = str(_('date_format'))
printing_date = timezone.now()
printing_date = printing_date.strftime(date_format)
worksheet.append(
[str('%s: %s' % (_('file_production_date'), printing_date))])
worksheet.append([str('')])
worksheet.append([str(affectations[0].master) if affectations else ''])
worksheet.append([str('')])
worksheet.append([str('')])
__columns_resizing(worksheet)
row_number = 9
for period in periods:
worksheet.append([
str(period.name),
period.date_start.strftime("%d-%m-%Y"),
period.date_end.strftime("%d-%m-%Y")
])
__coloring_non_editable(worksheet, row_number)
row_number += 1
for affectation in affectations:
if affectation.period.name == period.name:
student = affectation.student
worksheet.append([
str(student.person.last_name),
str(student.person.first_name),
student.registration_id, affectation.email,
affectation.adress, student.person.gender,
student.person.birth_date, affectation.phone_mobile
])
row_number += 1
worksheet.append([str('')])
row_number += 1
#filename_speciality = str(affectations[0].speciality.acronym).strip()
filename = "affectation_%s_%s.xlsx" % (str(
organization.reference), file_name)
response = HttpResponse(
save_virtual_workbook(workbook),
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=%s' % filename
return response
def do_import(request, import_log_id):
""" Import the data! """
import_log = get_object_or_404(ImportLog, id=import_log_id)
if import_log.import_type == "N" and 'undo' in request.GET and request.GET['undo'] == "True":
import_log.undo()
return HttpResponseRedirect(reverse(
'simple_import:simple_import-do_import',
kwargs={'import_log_id': import_log.id}) + '?success_undo=True')
if 'success_undo' in request.GET and request.GET['success_undo'] == "True":
success_undo = True
else:
success_undo = False
model_class = import_log.import_setting.content_type.model_class()
import_data = import_log.get_import_file_as_list()
header_row = import_data.pop(0)
header_row_field_names = []
header_row_default = []
header_row_null_on_empty = []
error_data = [header_row + ['Error Type', 'Error Details']]
create_count = 0
update_count = 0
fail_count = 0
if 'commit' in request.GET and request.GET['commit'] == "True":
commit = True
else:
commit = False
key_column_name = None
if import_log.update_key and import_log.import_type in ["U", "O"]:
key_match = import_log.import_setting.columnmatch_set.get(column_name=import_log.update_key)
key_column_name = key_match.column_name
key_field_name = key_match.field_name
for i, cell in enumerate(header_row):
match = import_log.import_setting.columnmatch_set.get(column_name=cell)
header_row_field_names += [match.field_name]
header_row_default += [match.default_value]
header_row_null_on_empty += [match.null_on_empty]
if key_column_name != None and key_column_name.lower() == cell.lower():
key_index = i
with transaction.atomic():
sid = transaction.savepoint()
for row in import_data:
try:
with transaction.atomic():
is_created = True
if import_log.import_type == "N":
new_object = model_class()
elif import_log.import_type == "O":
filters = {key_field_name: row[key_index]}
new_object = model_class.objects.get(**filters)
is_created = False
elif import_log.import_type == "U":
filters = {key_field_name: row[key_index]}
new_object = model_class.objects.filter(**filters).first()
if new_object == None:
new_object = model_class()
is_created = False
new_object.simple_import_m2ms = {} # Need to deal with these after saving
for i, cell in enumerate(row):
if header_row_field_names[i]: # skip blank
if not import_log.is_empty(cell) or header_row_null_on_empty[i]:
set_field_from_cell(import_log, new_object, header_row_field_names[i], cell)
elif header_row_default[i]:
set_field_from_cell(import_log, new_object, header_row_field_names[i], header_row_default[i])
new_object.save()
for i, cell in enumerate(row):
if header_row_field_names[i]: # skip blank
if not import_log.is_empty(cell) or header_row_null_on_empty[i]:
set_method_from_cell(import_log, new_object, header_row_field_names[i], cell)
elif header_row_default[i]:
set_method_from_cell(import_log, new_object, header_row_field_names[i], header_row_default[i])
new_object.save()
for key in new_object.simple_import_m2ms.keys():
value = new_object.simple_import_m2ms[key]
m2m = getattr(new_object, key)
m2m_model = type(m2m.model())
related_field_name = RelationalMatch.objects.get(import_log=import_log, field_name=key).related_field_name
m2m_object = m2m_model.objects.get(**{related_field_name:value})
m2m.add(m2m_object)
if is_created:
LogEntry.objects.log_action(
user_id = request.user.pk,
content_type_id = ContentType.objects.get_for_model(new_object).pk,
object_id = new_object.pk,
object_repr = smart_text(new_object),
action_flag = ADDITION
)
create_count += 1
else:
LogEntry.objects.log_action(
user_id = request.user.pk,
content_type_id = ContentType.objects.get_for_model(new_object).pk,
object_id = new_object.pk,
object_repr = smart_text(new_object),
action_flag = CHANGE
)
update_count += 1
ImportedObject.objects.create(
import_log = import_log,
object_id = new_object.pk,
content_type = import_log.import_setting.content_type)
except IntegrityError:
exc = sys.exc_info()
error_data += [row + ["Integrity Error", smart_text(exc[1])]]
fail_count += 1
except ObjectDoesNotExist:
exc = sys.exc_info()
error_data += [row + ["No Record Found to Update", smart_text(exc[1])]]
fail_count += 1
except ValueError:
exc = sys.exc_info()
if str(exc[1]).startswith('invalid literal for int() with base 10'):
error_data += [row + ["Incompatible Data - A number was expected, but a character was used", smart_text(exc[1])]]
else:
error_data += [row + ["Value Error", smart_text(exc[1])]]
fail_count += 1
except:
error_data += [row + ["Unknown Error"]]
fail_count += 1
if not commit:
transaction.savepoint_rollback(sid)
if fail_count:
from io import StringIO
from django.core.files.base import ContentFile
from openpyxl.workbook import Workbook
from openpyxl.writer.excel import save_virtual_workbook
wb = Workbook()
ws = wb.worksheets[0]
ws.title = "Errors"
filename = 'Errors.xlsx'
for row in error_data:
ws.append(row)
buf = StringIO()
# Not Python 3 compatible
#buf.write(str(save_virtual_workbook(wb)))
import_log.error_file.save(filename, ContentFile(save_virtual_workbook(wb)))
import_log.save()
return render(
request,
'simple_import/do_import.html',
{
'error_data': error_data,
'create_count': create_count,
'update_count': update_count,
'fail_count': fail_count,
'import_log': import_log,
'commit': commit,
'success_undo': success_undo,},
)
def render(self, data, accepted_media_type=None, renderer_context=None):
"""
Render `data` into XLSX workbook, returning a workbook.
"""
if not self._check_validatation_data(data):
return self._json_format_response(data)
if data is None:
return bytes()
wb = Workbook()
self.ws = wb.active
results = data["results"] if "results" in data else data
# Take header and column_header params from view
header = get_attribute(renderer_context["view"], "header", {})
self.ws.title = header.get("tab_title", "Report")
header_title = header.get("header_title", "Report")
img_addr = header.get("img")
if img_addr:
img = Image(img_addr)
self.ws.add_image(img, "A1")
header_style = get_style_from_dict(header.get("style"), "header_style")
column_header = get_attribute(renderer_context["view"],
"column_header", {})
column_header_style = get_style_from_dict(column_header.get("style"),
"column_header_style")
column_count = 0
row_count = 1
if header:
row_count += 1
# Make column headers
column_titles = column_header.get("titles", [])
# If we have results, pull the columns names from the keys of the first row
if len(results):
if isinstance(results, ReturnDict):
column_names_first_row = results
elif isinstance(results, ReturnList) or type(results) is list:
column_names_first_row = self._flatten(results[0])
elif type(results) is dict:
column_names_first_row = results
for column_name in column_names_first_row.keys():
if column_name == "row_color":
continue
column_count += 1
if column_count > len(column_titles):
column_name_display = column_name
else:
column_name_display = column_titles[column_count - 1]
self.ws.cell(
row=row_count,
column=column_count,
value=column_name_display).style = column_header_style
self.ws.row_dimensions[row_count].height = column_header.get(
"height", 45)
# Set the header row
if header:
last_col_letter = "G"
if column_count:
last_col_letter = get_column_letter(column_count)
self.ws.merge_cells("A1:{}1".format(last_col_letter))
cell = self.ws.cell(row=1, column=1, value=header_title)
cell.style = header_style
self.ws.row_dimensions[1].height = header.get("height", 45)
# Set column width
column_width = column_header.get("column_width", 20)
if isinstance(column_width, list):
for i, width in enumerate(column_width):
col_letter = get_column_letter(i + 1)
self.ws.column_dimensions[col_letter].width = width
else:
for self.ws_column in range(1, column_count + 1):
col_letter = get_column_letter(self.ws_column)
self.ws.column_dimensions[col_letter].width = column_width
# Make body
self.body = get_attribute(renderer_context["view"], "body", {})
self.body_style = get_style_from_dict(self.body.get("style"),
"body_style")
if isinstance(results, ReturnDict):
self._make_body(results, row_count)
elif isinstance(results, ReturnList) or type(results) is list:
for row in results:
self._make_body(row, row_count)
row_count += 1
return save_virtual_workbook(wb)
def excel_results(self):
def profiles_str(p_list):
return u", ".join([
u"{} <{}>".format(i.user.username, i.user.email)
for i in p_list
])
wb = Workbook()
ws = wb.active
for cqs in self.competitionquestionsets.all():
# t0 = datetime.datetime.now()
profiles_by_code = defaultdict(list)
schools_by_code = defaultdict(list)
schools_by_teacher = defaultdict(list)
for code in self.competition.competitor_code_generator.codes.filter(
value__startswith=cqs.slug_str()):
for p in code.creator_set.all().select_related('user'):
profiles_by_code[code.value].append(p)
for sct in code.schoolteachercode_set.all():
schools_by_code[code.value].append(sct.school)
schools_by_teacher[sct.teacher_id].append(sct.school)
ws.title = cqs.name
questions = cqs.questionset.questions.order_by('id')
keys = [
'Attempt ID', 'Start', 'Finish', 'Duration', 'Code',
'Competition', 'Possible schools', 'Confirmed schools',
'Confirmed by', 'No. of confirmations', 'Possible mentors',
'Group', 'First name', 'Last name', 'Awards', 'Revoked awards',
'Score'
]
question_none_scores = dict()
for q in questions:
keys.append(str(q))
question_none_scores[q.id] = q.none_score
ws.append(keys)
gradedanswers = dict()
for attempt_id, question_id, score in GradedAnswer.objects.filter(
attempt__competitionquestionset__id=cqs.id).distinct(
).values_list('attempt_id', 'question_id', 'score'):
gradedanswers[(attempt_id, question_id)] = score
confirmations = defaultdict(list)
for by_id, attempt_id, username, email in AttemptConfirmation.objects.filter(
attempt__competitionquestionset__id=cqs.id).distinct(
).values_list('by_id', 'attempt_id', 'by__user__username',
'by__user__email'):
confirmations[attempt_id].append(
(by_id, u"{} <{}>".format(username, email)))
awards = defaultdict(list)
revoked_awards = defaultdict(list)
for attempt_id, revoked_by, award_name, award_serial in AttemptAward.objects.filter(
attempt__competitionquestionset__id=cqs.id).distinct(
).values_list('attempt_id', 'revoked_by', 'award__name',
'serial'):
if revoked_by is not None:
revoked_awards[attempt_id].append("{}: {}".format(
award_name, award_serial))
else:
awards[attempt_id].append("{}: {}".format(
award_name, award_serial))
attempts = cqs.attempt_set.all()
for (
attempt_id,
attempt_start,
attempt_finish,
attempt_duration,
access_code,
first_name,
last_name,
date_of_birth,
attempt_score,
) in attempts.values_list('id', 'start', 'finish', 'duration',
'access_code', 'competitor__first_name',
'competitor__last_name',
'competitor__date_of_birth',
'score').distinct():
# print " attempt:", attempt.id
mentors = profiles_by_code[access_code]
schools = schools_by_code[access_code]
confirmed_schools = set()
confirmed_by = confirmations[attempt_id]
for p in confirmed_by:
for s in schools_by_teacher[p[0]]:
confirmed_schools.add(s)
confirmed_by_schools = set(schools).intersection(
confirmed_schools)
attempt_start = attempt_start.replace(tzinfo=None)
if attempt_finish is not None:
attempt_finish = attempt_finish.replace(tzinfo=None)
l1 = [
attempt_id,
attempt_start,
attempt_finish,
attempt_duration,
access_code,
self.competition.slug,
u", ".join([i.name for i in schools]),
u", ".join([i.name for i in confirmed_by_schools]),
", ".join([i[1] for i in confirmed_by]),
len(confirmed_by),
profiles_str(mentors),
cqs.name,
first_name,
last_name,
u", ".join(awards[attempt_id]),
u", ".join(revoked_awards[attempt_id]),
attempt_score,
]
for q in questions:
l1.append(
gradedanswers.get((attempt_id, q.id),
question_none_scores[q.id]))
ws.append(l1)
ws = wb.create_sheet()
return save_virtual_workbook(wb)
def excel_report(request, pk):
client = get_object_or_404(Client, pk=pk)
file = 'D:/Python/changan_journal/keylist.xlsx'
key_book = load_workbook(file)
worksheet = key_book['list']
worksheet_graphic = key_book['graphic']
customer_name = worksheet.cell(8, 5)
customer_name.value = client.fio_klienta
officer_name = worksheet.cell(10, 5)
officer_name.value = client.credit_user.name
date_pay = [1, 5, 10, 15, 20, 25]
day = int(client.date_posted.strftime('%d'))
if day not in date_pay:
pay_day = min(date_pay, key=lambda x: abs(x - day))
delta = pay_day - day
count_date = client.date_posted + timedelta(days=delta)
else:
count_date = client.date_posted
date_count = worksheet_graphic.cell(7, 3)
date_count.value = count_date
date = worksheet_graphic.cell(4, 4)
date.value = client.date_posted
commission = worksheet.cell(24, 5)
comm = client.get_commission() / 100
commission.value = comm
obnal = worksheet.cell(25, 5)
ob = client.product.commission_cash / 100
obnal.value = ob
zalog = worksheet.cell(35, 5)
zalog.value = client.zalog
sum = worksheet_graphic.cell(1, 4)
sum.value = client.summa_zayavki
stavka = worksheet_graphic.cell(2, 4)
stavka.value = client.get_interest_rate()
srok = worksheet_graphic.cell(3, 4)
srok.value = client.srok_kredita
key_book.save(file)
def splitpart(value):
if len(value.split()) == 3:
return f'{value.split()[0]}{value.split()[1][0]}.{value.split()[2][0]}.'
elif len(value.split()) == 2:
return f'{value.split()[0]}{value.split()[1][0]}.'
else:
return f'{value.split()[0]}'
file_name = splitpart(client.fio_klienta)
file_name = translit(file_name, "ru", reversed=True)
response = HttpResponse(save_virtual_workbook(key_book),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = "attachment; filename={}.xlsx".format(
file_name)
return response
def general_report(request, xls=False):
from td.models import Car, TO, Mileage, Item, Proxy
cars = []
for car in Car.objects.filter(active=True).order_by('name', 'number'):
cars.append({
'car':
car,
'to':
TO.objects.filter(car=car,
fact_amount__gt=0).order_by('-date').first(),
'mileage':
Mileage.objects.filter(car=car).order_by('-year',
'-month').first(),
'items':
Item.objects.filter(car=car,
active=True).order_by('type', 'end_date'),
'proxies':
Proxy.objects.filter(car=car, active=True).order_by('type'),
})
if not xls:
context = {'cars': cars}
return render(request, 'td/reports/general.html', context)
else:
from main import settings
from datetime import date
import openpyxl
import os
from openpyxl.writer.excel import save_virtual_workbook
wb = openpyxl.load_workbook(
os.path.join(settings.TEMPLATES[0]['DIRS'][0], 'td', 'reports',
'general.xlsx'))
ws = wb.active
row = 2
for n, car in enumerate(cars):
drivers = ''
fuel_card = ''
osago = ''
kasko = ''
pts = ''
sts = ''
drivers_state = 'ok'
for proxy in car['proxies']:
if proxy.type == 0 and proxy.end_date >= date.today():
if drivers != '':
drivers += ', '
drivers += str(proxy.driver)
if (proxy.end_date - date.today()).days < 30:
drivers_state = 'warning'
osago_state = 'ok'
kasko_state = 'ok'
for item in car['items']:
if item.type == 'fuel_card':
fuel_card = item.number
if item.type == 'osago':
osago = item.end_date.strftime('%d.%m.%Y')
if (item.end_date - date.today()).days < 30:
osago_state = 'warning'
if item.type == 'kasko':
kasko = item.end_date.strftime('%d.%m.%Y')
if (item.end_date - date.today()).days < 30:
kasko_state = 'warning'
if item.type == 'pts':
pts = item.number
if item.type == 'sts':
sts = item.number
ws.cell(column=1, row=row, value=n + 1).style = 'cell'
ws.cell(column=2, row=row, value=car['car'].name).style = 'cell'
ws.cell(column=3, row=row, value=car['car'].number).style = 'cell'
ws.cell(column=4, row=row, value=drivers).style = 'cell'
if drivers_state == 'warning' or drivers == '':
ws.cell(column=4, row=row).style = 'cell-warning'
ws.cell(column=5, row=row, value=str(car['car'].owner
or '')).style = 'cell'
ws.cell(column=6, row=row, value=fuel_card).style = 'cell'
ws.cell(column=7, row=row, value=osago).style = 'cell'
if osago_state == 'warning':
ws.cell(column=7, row=row).style = 'cell-warning'
ws.cell(column=8, row=row, value=kasko).style = 'cell'
if kasko_state == 'warning':
ws.cell(column=8, row=row).style = 'cell-warning'
ws.cell(column=9, row=row, value=pts).style = 'cell'
ws.cell(column=10, row=row, value=sts).style = 'cell'
ws.cell(column=11,
row=row,
value=str(car['mileage'].value) + ' км').style = 'cell'
row += 1
response = HttpResponse(
save_virtual_workbook(wb),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response['Content-Disposition'] = "attachment; filename=report.xlsx"
return response
def save_virtual_workbook(self):
self.sort_worksheets()
return save_virtual_workbook(self.workbook)
def to_report(request, xls=False):
from td.models import TO
from td.forms import CarPeriodForm
from datetime import datetime
if request.method != 'POST':
return redirect(to='td:to_report_form')
form = CarPeriodForm(request.POST)
car_id = int(form.data['car'])
if form.data['start_date']:
start_date = datetime.strptime(form.data['start_date'], '%d.%m.%Y')
else:
start_date = datetime(2000, 1, 1, 0, 0)
if form.data['end_date']:
end_date = datetime.strptime(form.data['end_date'], '%d.%m.%Y')
else:
end_date = datetime(2099, 12, 31, 0, 0)
if car_id > 0:
to_list = TO.objects.filter(car_id=car_id, date__gte=start_date, date__lte=end_date).order_by('date')
else:
to_list = TO.objects.filter(date__gte=start_date, date__lte=end_date).order_by('date')
budget_total = 0
fact_total = 0
for to in to_list:
budget_total += to.budget_amount
fact_total += to.fact_amount
if not xls:
context = {
'to_list': to_list,
'budget_total': budget_total,
'fact_total': fact_total
}
return render(request, 'td/reports/to.html', context)
else:
from main import settings
import openpyxl
from openpyxl.styles import Alignment
import os
from openpyxl.writer.excel import save_virtual_workbook
wb = openpyxl.load_workbook(os.path.join(settings.TEMPLATES[0]['DIRS'][0], 'td', 'reports', 'to.xlsx'))
ws = wb.active
wrap_alignment = Alignment(wrap_text=True)
row = 5
for to in to_list:
ws.cell(column=1, row=row, value=to.car.name+' '+to.car.number).style = 'cell'
ws.cell(column=1, row=row).alignment = wrap_alignment
ws.cell(column=2, row=row, value=to.date.strftime('%d.%m.%Y')).style = 'cell'
ws.cell(column=3, row=row, value=to.name).style = 'cell'
ws.cell(column=4, row=row, value=to.budget_amount).style = 'cell'
ws.cell(column=5, row=row, value=to.fact_amount).style = 'cell'
ws.cell(column=6, row=row, value=to.comment).style = 'cell'
ws.cell(column=6, row=row).alignment = wrap_alignment
row += 1
ws.cell(column=1, row=row, value='Итого:').style = 'totals'
ws.cell(column=2, row=row).style = 'totals'
ws.cell(column=3, row=row).style = 'totals'
ws.cell(column=4, row=row, value=budget_total).style = 'totals'
ws.cell(column=5, row=row, value=fact_total).style = 'totals'
ws.cell(column=6, row=row).style = 'totals'
response = HttpResponse(
save_virtual_workbook(wb),
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename=report.xlsx"
return response
def export_xlsx(modeladmin, request, queryset):
wb = Workbook()
ws = wb.active
ws.title = "Products"
number_alignment = Alignment(horizontal="right")
wb.add_named_style(
NamedStyle("Identifier",
alignment=number_alignment,
number_format=FORMAT_NUMBER))
wb.add_named_style(
NamedStyle("Normal Wrapped", alignment=Alignment(wrap_text=True)))
number_headline_1 = copy(builtins.styles["Headline 1"])
number_headline_1.name = "Number Headline 1"
number_headline_1.alignment = number_alignment
wb.add_named_style(number_headline_1)
class Config():
def __init__(self,
heading,
width=None,
heading_style="Headline 1",
style="Normal Wrapped"):
self.heading = heading
self.width = width
self.heading_style = heading_style
self.style = style
column_config = {
"A":
Config("ID",
width=10,
heading_style="Number Headline 1",
style="Identifier"),
"B":
Config("Title", width=30),
"C":
Config("Description", width=60),
"D":
Config("Price ($)",
width=15,
heading_style="Number Headline 1",
style="Currency"),
"E":
Config("Preview", width=100, style="Hyperlink"),
}
# Set up column widths, header values and styles
for col, conf in column_config.items():
ws.column_dimensions[col].width = conf.width
column = ws[f"{col}1"]
column.value = conf.heading
column.style = conf.heading_style
# Add products
for obj in queryset.order_by("pk"):
project_photos = obj.productphoto_set.all()[:1]
url = ""
if project_photos:
url = project_photos[0].photo.url
data = [obj.pk, obj.title, obj.description, obj.price, url]
ws.append(data)
row = ws.max_row
for row_cells in ws.iter_cols(min_row=row, max_row=row):
for cell in row_cells:
cell.style = column_config[cell.column].style
mimetype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
charset = "utf-8"
response = HttpResponse(content=save_virtual_workbook(wb),
content_type=f"{mimetype}; charset={charset}",
charset=charset)
response["Content-Disposition"] = "attachment; filename=products.xlsx"
return response
def test_save_xl_as_template(datadir, tmpl, keep_vba, wb_type):
datadir.chdir()
wb = save_virtual_workbook(load_workbook(tmpl, keep_vba=keep_vba),
as_template=True)
check_content_type(wb_type, ZipFile(BytesIO(wb)))
def close(self):
return save_virtual_workbook(self.wb)
def excel_ranking(request):
wb = Workbook()
ws = wb.active
bold_font = Font(bold=True, size=18)
ws.title = "Instalaciones"
titulo = "Ranking de técnicos al %s " % (
datetime.now().strftime('%d-%m-%Y %H:%M:%S'))
ws["A1"] = titulo
ws["A1"].font = bold_font
ws.column_dimensions['A'].width = 25
ws.column_dimensions['B'].width = 60
ws.column_dimensions['C'].width = 60
ws.column_dimensions['D'].width = 60
ws.column_dimensions['E'].width = 60
ws.column_dimensions['F'].width = 60
ws.column_dimensions['G'].width = 60
colors = ['FF000000', '0000FF', '00FF00', 'FF00FF', 'FF6600']
rowNum = 3
ws.cell(row=rowNum, column=1).value = "Posición"
ws.cell(row=rowNum, column=1).font = bold_font
ws.cell(row=rowNum, column=2).value = "Nombre"
ws.cell(row=rowNum, column=2).font = bold_font
ws.cell(row=rowNum, column=3).value = "Número de cliente"
ws.cell(row=rowNum, column=3).font = bold_font
ws.cell(row=rowNum, column=4).value = "Nombre de cliente"
ws.cell(row=rowNum, column=4).font = bold_font
ws.cell(row=rowNum, column=5).value = "Fecha de liquidación"
ws.cell(row=rowNum, column=5).font = bold_font
ws.cell(row=rowNum, column=6).value = "Cantidad de ventas"
ws.cell(row=rowNum, column=6).font = bold_font
ws.cell(row=rowNum, column=7).value = "Comisión"
ws.cell(row=rowNum, column=7).font = bold_font
tecnicos = ranking_tecs()
for c, t in enumerate(tecnicos, start=1):
color = random.choice(colors)
border = Border(left=Side(border_style='medium', color=color),
right=Side(border_style='medium', color=color),
top=Side(border_style='medium', color=color),
bottom=Side(border_style='medium', color=color))
rowNum += 1
ws.cell(row=rowNum, column=1).value = c
ws.cell(row=rowNum, column=1).border = border
ws.cell(row=rowNum, column=2).value = t.nombre
ws.cell(row=rowNum, column=2).border = border
ws.cell(row=rowNum, column=6).value = t.ventas
ws.cell(row=rowNum, column=6).border = border
ws.cell(row=rowNum, column=7).value = t.comision
ws.cell(row=rowNum, column=7).border = border
clientes = t.clientes.filter(estado="LI") | t.clientes_comp.filter(
estado="LI")
for cli in clientes:
ws.cell(row=rowNum, column=2).border = border
ws.cell(row=rowNum, column=6).border = border
ws.cell(row=rowNum, column=7).border = border
ws.cell(row=rowNum, column=3).value = cli.clientenro
ws.cell(row=rowNum, column=3).border = border
ws.cell(row=rowNum, column=4).value = cli.nombre
ws.cell(row=rowNum, column=4).border = border
ws.cell(row=rowNum, column=5).value = cli.fecha_liq
ws.cell(row=rowNum, column=5).border = border
rowNum += 1
name_file = 'Ranking_{0}.xlsx'.format(
datetime.now().strftime('%d-%m-%Y %H:%M:%S'))
response = HttpResponse(
content=save_virtual_workbook(wb),
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename={0}'.format(
name_file)
return response
def convert(self, schema_content: SchemaContent) -> bytes:
workbook = Workbook()
self._create_main_sheet(workbook, schema_content)
self._create_option_sheets(workbook, schema_content)
return save_virtual_workbook(workbook)
def create_competition_status_excel(now_date, token):
try:
xlsx = load_workbook(f"samoyed/static/competition_status.xlsx")
sheet = xlsx.get_active_sheet()
result = requests.get(STATUS_URL, headers={"Authorization": token})
response = json.loads(result.text)
sheet.cell(4, 6,
response['daejeon']['meister_applicant']['applicant_count'])
sheet.cell(5, 6,
response['daejeon']['social_applicant']['applicant_count'])
sheet.cell(6, 6,
response['daejeon']['common_applicant']['applicant_count'])
sheet.cell(
4, 7,
response['nationwide']['meister_applicant']['applicant_count'])
sheet.cell(
5, 7,
response['nationwide']['social_applicant']['applicant_count'])
sheet.cell(
6, 7,
response['nationwide']['common_applicant']['applicant_count'])
sheet.cell(7, 6, response['daejeon']['total_applicant_count'])
sheet.cell(7, 7, response['nationwide']['total_applicant_count'])
sheet.cell(8, 6, response['total_applicant_count'])
sheet.cell(
4, 8, response['daejeon']['meister_applicant']['competition_rate'])
sheet.cell(5, 8,
response['daejeon']['social_applicant']['competition_rate'])
sheet.cell(6, 8,
response['daejeon']['common_applicant']['competition_rate'])
sheet.cell(
4, 9,
response['nationwide']['meister_applicant']['competition_rate'])
sheet.cell(
5, 9,
response['nationwide']['social_applicant']['competition_rate'])
sheet.cell(
6, 9,
response['nationwide']['common_applicant']['competition_rate'])
sheet.cell(7, 8, response['daejeon']['total_competition_rate'])
sheet.cell(7, 9, response['nationwide']['total_competition_rate'])
sheet.cell(8, 8, response['total_competition_rate'])
sheet.cell(12, 4,
response['nationwide']['common_applicant']['141-150'])
sheet.cell(13, 4,
response['nationwide']['common_applicant']['131-140'])
sheet.cell(14, 4,
response['nationwide']['common_applicant']['121-130'])
sheet.cell(15, 4,
response['nationwide']['common_applicant']['111-120'])
sheet.cell(16, 4,
response['nationwide']['common_applicant']['101-110'])
sheet.cell(17, 4, response['nationwide']['common_applicant']['91-100'])
sheet.cell(18, 4, response['nationwide']['common_applicant']['81-90'])
sheet.cell(19, 4, response['nationwide']['common_applicant']['71-80'])
sheet.cell(20, 4, response['nationwide']['common_applicant']['-70'])
sheet.cell(12, 5,
response['nationwide']['meister_applicant']['141-150'])
sheet.cell(13, 5,
response['nationwide']['meister_applicant']['131-140'])
sheet.cell(14, 5,
response['nationwide']['meister_applicant']['121-130'])
sheet.cell(15, 5,
response['nationwide']['meister_applicant']['111-120'])
sheet.cell(16, 5,
response['nationwide']['meister_applicant']['101-110'])
sheet.cell(17, 5,
response['nationwide']['meister_applicant']['91-100'])
sheet.cell(18, 5, response['nationwide']['meister_applicant']['81-90'])
sheet.cell(19, 5, response['nationwide']['meister_applicant']['71-80'])
sheet.cell(20, 5, response['nationwide']['meister_applicant']['-70'])
sheet.cell(12, 6,
response['nationwide']['social_applicant']['141-150'])
sheet.cell(13, 6,
response['nationwide']['social_applicant']['131-140'])
sheet.cell(14, 6,
response['nationwide']['social_applicant']['121-130'])
sheet.cell(15, 6,
response['nationwide']['social_applicant']['111-120'])
sheet.cell(16, 6,
response['nationwide']['social_applicant']['101-110'])
sheet.cell(17, 6, response['nationwide']['social_applicant']['91-100'])
sheet.cell(18, 6, response['nationwide']['social_applicant']['81-90'])
sheet.cell(19, 6, response['nationwide']['social_applicant']['71-80'])
sheet.cell(20, 6, response['nationwide']['social_applicant']['-70'])
sheet.cell(12, 7, response['daejeon']['common_applicant']['141-150'])
sheet.cell(13, 7, response['daejeon']['common_applicant']['131-140'])
sheet.cell(14, 7, response['daejeon']['common_applicant']['121-130'])
sheet.cell(15, 7, response['daejeon']['common_applicant']['111-120'])
sheet.cell(16, 7, response['daejeon']['common_applicant']['101-110'])
sheet.cell(17, 7, response['daejeon']['common_applicant']['91-100'])
sheet.cell(18, 7, response['daejeon']['common_applicant']['81-90'])
sheet.cell(19, 7, response['daejeon']['common_applicant']['71-80'])
sheet.cell(20, 7, response['daejeon']['common_applicant']['-70'])
sheet.cell(12, 8, response['daejeon']['meister_applicant']['141-150'])
sheet.cell(13, 8, response['daejeon']['meister_applicant']['131-140'])
sheet.cell(14, 8, response['daejeon']['meister_applicant']['121-130'])
sheet.cell(15, 8, response['daejeon']['meister_applicant']['111-120'])
sheet.cell(16, 8, response['daejeon']['meister_applicant']['101-110'])
sheet.cell(17, 8, response['daejeon']['meister_applicant']['91-100'])
sheet.cell(18, 8, response['daejeon']['meister_applicant']['81-90'])
sheet.cell(19, 8, response['daejeon']['meister_applicant']['71-80'])
sheet.cell(20, 8, response['daejeon']['meister_applicant']['-70'])
sheet.cell(12, 9, response['daejeon']['social_applicant']['141-150'])
sheet.cell(13, 9, response['daejeon']['social_applicant']['131-140'])
sheet.cell(14, 9, response['daejeon']['social_applicant']['121-130'])
sheet.cell(15, 9, response['daejeon']['social_applicant']['111-120'])
sheet.cell(16, 9, response['daejeon']['social_applicant']['101-110'])
sheet.cell(17, 9, response['daejeon']['social_applicant']['91-100'])
sheet.cell(18, 9, response['daejeon']['social_applicant']['81-90'])
sheet.cell(19, 9, response['daejeon']['social_applicant']['71-80'])
sheet.cell(20, 9, response['daejeon']['social_applicant']['-70'])
file = BytesIO(save_virtual_workbook(xlsx))
return send_file(
file,
as_attachment=True,
attachment_filename=f"competition_status_{now_date}.xlsx")
except Exception as e:
return abort(500, e)
def print_report(self):
self.ensure_one()
account_obj = self.env['account.account']
wb = Workbook()
ws1 = wb.active
ws1.append({
'A': _('Account'),
'B': _('Journal Entry'),
'C': _('Name'),
'D': _('Reference'),
'E': _('Date'),
'F': _('Partner'),
'G': _('Debit'),
'H': _('Credit'),
'I': _('Balance'),
'J': _('Tax'),
'K': _('RFC'),
})
res, dictio_keys = self.prepare_data()
# Loop the sorted dictionary keys and fill the xlsx file
for key in dictio_keys:
account_id = account_obj.search([('code', '=', key)])
balance = 0.0
ws1.append({'A': account_id.code + ' ' + account_id.name})
for item in res[key]:
if item['C'] == _('Initial Balance'):
ws1.append(item)
balance = item['I']
continue
balance += (item['G'] - item['H'])
item['I'] = balance
ws1.append(item)
ws1.append({
'G': sum([x['G'] for x in res[key]]),
'H': sum([x['H'] for x in res[key]]),
'I': balance,
})
# Apply styles to the xlsx file
for row in ws1.iter_rows():
if row[0].value and not row[1].value:
ws1[row[0].coordinate].font = Font(bold=True, color='7CB7EA')
if not row[1].value and row[8].value and not row[2].value:
ws_range = row[6].coordinate + ':' + row[8].coordinate
for row_cell in enumerate(ws1[ws_range]):
for cell in enumerate(row_cell[1]):
cell[1].font = Font(bold=True)
if not row[1].value and row[8].value and row[2].value:
row[8].font = Font(bold=True)
xlsx_file = save_virtual_workbook(wb)
self.xlsx_file = base64.encodestring(xlsx_file)
self.xlsx_filename = _('TMS General Ledger.xlsx')
self.state = 'print'
return {
'type': 'ir.actions.act_window',
'res_model': 'account.general.ledger.wizard',
'view_mode': 'form',
'view_type': 'form',
'res_id': self.id,
'views': [(False, 'form')],
'target': 'new',
}
def _genResponse(workbook):
response = HttpResponse(content=save_virtual_workbook(workbook), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=myexport.xlsx'
return response
def get_workbook_content(workbook):
return save_virtual_workbook(workbook)
def progressPage(request):
try:
idnumber = request.POST['id']
countryList = request.POST.getlist('countryList[]')
except:
return redirect("../iTunesReviews")
try:
seshId = (request.POST['seshId'])
#Generate session id
except:
random.seed()
seshId = str(random.randrange(10001, 20000, 1))
#print >>sys.stderr, seshId
try:
if (len(countryList) > 0):
if (int(idnumber) < 10000000000 and int(idnumber) >= 100000000):
#get the next 4 countries
workingCountryList = []
for x in range(0, 1):
try:
workingCountryList.append(countryList[0])
countryList.pop(0)
except:
break
runReport(idnumber, workingCountryList, seshId)
template = loader.get_template('itunesreviews/progress.html')
context = RequestContext(
request, {
'country_list': countryList,
'Ident': idnumber,
'seshId': seshId,
})
return HttpResponse(template.render(context))
else:
#now we're going to returnt he actual report and delete the internal records
rep = ReviewReport.objects.get(name=seshId)
newPath = path.abspath(path.dirname(__file__))
chdir(newPath)
returnBook = load_workbook(rep.book)
returnBook = save_virtual_workbook(returnBook)
#delete the actual file
newPath = path.abspath(path.dirname(__file__))
chdir(newPath)
remove(rep.book)
#delete database entry
rep.delete()
#return as attachment
response = HttpResponse(returnBook,
content_type='application/vnd.ms-excel')
response[
'Content-Disposition'] = 'attachment; filename="iTunesReviews.xls"'
return response
except:
return redirect("../iTunesReviews")
def xuat_file():
data = {}
params = request.args.to_dict(flat=False)
mssv = params.get('mssv')[0]
for field in params.get('dulieu'):
if field == 'thongtin':
data[field] = du_lieu_sinh_vien(mssv)
if field == 'diemthi':
data[field] = du_lieu_diem_thi(mssv)
if field == 'khenthuong':
data[field] = du_lieu_khen_thuong(mssv)
if field == 'kyluat':
data[field] = du_lieu_thoi_hoc(mssv)
if field == 'hocphi':
data[field] = du_lieu_hoc_phi(mssv)
wb = Workbook()
mainsheet = wb.create_sheet("Sinh vien")
for k, v in data.items():
if k == 'thongtin':
if v != None:
ws = mainsheet
ws['A1'] = 'Ma so sinh vien'
ws['B1'] = v[0]
ws['A2'] = 'Lop'
ws['B2'] = v[1]
ws['A3'] = 'Ho va ten'
ws['B3'] = v[2]
ws['A4'] = 'PortalID'
ws['B4'] = v[7]
ws['A5'] = 'Gioi tinh'
ws['B5'] = "Nam" if v[6] else "Nu"
ws['A6'] = 'Ngay sinh'
ws['B6'] = str(v[3]) + '/' + str(v[4]) + '/' + str(v[5]) if (
v[3] and v[4] and v[5]) else ''
ws['A7'] = 'Dien thoai'
ws['B7'] = v[8]
ws['A8'] = 'Zalo'
ws['B8'] = v[20]
ws['A9'] = 'Email'
ws['B9'] = v[10]
ws['A10'] = 'Dia chi'
ws['B10'] = v[9]
ws['A11'] = 'CMND'
ws['B11'] = v[15]
ws['A12'] = 'Ngay cap'
ws['B12'] = v[17]
ws['A13'] = 'Dia chi'
ws['B13'] = v[16]
ws['A14'] = 'Ho ten nguoi than'
ws['B14'] = v[12]
ws['A15'] = 'Moi lien he'
ws['B15'] = v[18]
ws['A16'] = 'Email nguoi than'
ws['B16'] = v[13]
ws['A17'] = 'So dien thoai nguoi than'
ws['B17'] = v[14]
ws['A18'] = 'Zalo nguoi than'
ws['B18'] = v[19]
ws['A19'] = 'Ghi chu'
ws['B19'] = v[11]
if k == 'khenthuong':
if v != None:
mainsheet['A20'] = 'Khen thuong'
mainsheet['B20'] = v
if k == 'kyluat':
if v != None:
mainsheet['A21'] = 'Ky luat'
mainsheet['B21'] = v
if k == 'diemthi':
if v != None:
ws = wb.create_sheet("Diem thi")
ws['A1'] = 'Mon hoc'
ws['B1'] = 'Loai thi'
ws['C1'] = 'Ngay thi'
ws['D1'] = 'Diem thi'
ws['E1'] = 'Lan thi'
for i in range(len(v)):
for j in range(5):
ws.cell(row=2 + i, column=1 + j, value=v[i][j])
if k == 'hocphi':
if v != None:
ws = wb.create_sheet("Hoc phi")
ws['A1'] = 'Ngay dong'
ws['B1'] = 'So tien'
for i in range(len(v)):
ws.cell(row=2 + i, column=1, value=v[i][0])
ws.cell(row=2 + i, column=2, value=v[i][1])
if k == 'diemdanh':
if v != None:
ws = wb.create_sheet('Diem danh')
for i in range(12):
ws.cell(row=2, column=i, value=v[i + 3])
content = save_virtual_workbook(wb)
resp = make_response(content)
resp.headers[
"Content-Disposition"] = 'attachment; filename=' + mssv + '.xlsx'
resp.headers['Content-Type'] = 'application/x-xlsx'
return resp
def create_world_colors_export():
workbook = Workbook()
workbook.active.title = "Homeworlds"
workbook.create_sheet("Exoworlds")
workbook.create_sheet("Sovereign")
homeworlds = workbook["Homeworlds"]
exoworlds = workbook["Exoworlds"]
sovereign = workbook["Sovereign"]
sovereign.append(["Name", "Avaiable Colors"])
worlds = World.objects.filter(owner__isnull=True,
is_creative=False).order_by("id")
logger.info("Generating Sovereign Color...")
items = Item.objects.filter(
game_id__in=get_world_block_color_item_ids()).order_by("game_id")
headers = [
"Name",
"ID",
]
for item in items:
headers.append(item.english)
sovereign_columns = [item.english]
wbcs = (WorldBlockColor.objects.filter(
item=item, is_default=True).filter(
Q(world__isnull=True)
| Q(world__end__isnull=True, world__is_creative=False)
| Q(world__owner__isnull=False, world__is_creative=False)).
select_related("item").order_by("color__game_id").distinct(
"color__game_id"))
for wbc in wbcs:
sovereign_columns.append(
f"{wbc.color.default_name} ({wbc.color.game_id})")
sovereign.append(sovereign_columns)
logger.info("Generating World Rows...")
homeworlds.append(headers)
exoworlds.append(headers)
for world in worlds:
columns = [world.display_name, world.id]
for wbc in world.worldblockcolor_set.filter(
is_default=True).order_by("item__game_id"):
columns.append(f"{wbc.color.default_name} ({wbc.color.game_id})")
if world.is_exo:
exoworlds.append(columns)
else:
homeworlds.append(columns)
set_column_widths(homeworlds)
set_column_widths(exoworlds)
set_column_widths(sovereign)
create_export_file(
COLOR_EXPORT_FILENAME,
"xlsx",
COLOR_EXPORT_DESCRIPTION,
save_virtual_workbook(workbook),
)
def get_drcj(self):
Entry = apps.get_model('api.entry')
Group = apps.get_model('api.group')
Member = apps.get_model('api.member')
wb = Workbook()
ws = wb.active
fieldnames = [
'OA',
'Group Name',
'Representing',
'Evaluation?',
'Score/Eval-Only?',
'BHS ID',
'Group Status',
'Repertory Count',
'Estimated MOS',
'Members Expiring',
'Tenor',
'Lead',
'Baritone',
'Bass',
'Director/Participant(s)',
'Award(s)',
'Chapter(s)',
]
ws.append(fieldnames)
entries = self.entries.filter(status__in=[
Entry.STATUS.approved,
]).order_by('draw')
for entry in entries:
oa = entry.draw
group_name = entry.group.name
representing = entry.representing
evaluation = entry.is_evaluation
private = entry.is_private
bhs_id = entry.group.bhs_id
repertory_count = entry.group.repertories.filter(
status__gt=0, ).count()
group_status = entry.group.get_status_display()
repertory_count = entry.group.repertories.filter(
status__gt=0, ).count()
participant_count = entry.pos
members = entry.group.members.filter(status__gt=0, )
expiring_count = members.filter(
person__user__current_through__lte=self.convention.close_date,
).count()
participants = entry.participants
awards_list = []
contestants = entry.contestants.filter(
status__gt=0, ).order_by('contest__award__name')
for contestant in contestants:
awards_list.append(contestant.contest.award.name)
awards = "\n".join(filter(None, awards_list))
parts = {}
part = 1
while part <= 4:
try:
member = members.get(part=part, )
except Member.DoesNotExist:
parts[part] = None
part += 1
continue
except Member.MultipleObjectsReturned:
parts[part] = None
part += 1
continue
member_list = []
member_list.append(member.person.nomen, )
member_list.append(member.person.email, )
member_list.append(member.person.phone, )
member_detail = "\n".join(filter(None, member_list))
parts[part] = member_detail
part += 1
if entry.group.kind == entry.group.KIND.quartet:
persons = members.values_list('person', flat=True)
cs = Group.objects.filter(
members__person__in=persons,
members__status__gt=0,
kind=Group.KIND.chorus,
).distinct().order_by('parent__name', ).values_list(
'parent__name', flat=True)
chapters = "\n".join(cs)
elif entry.group.kind == entry.group.KIND.chorus:
try:
chapters = entry.group.parent.name
except AttributeError:
chapters = None
row = [
oa,
group_name,
representing,
evaluation,
private,
bhs_id,
group_status,
repertory_count,
participant_count,
expiring_count,
parts[1],
parts[2],
parts[3],
parts[4],
participants,
awards,
chapters,
]
ws.append(row)
file = save_virtual_workbook(wb)
content = ContentFile(file)
return content
def downloadfile1(allfile, facebookdata):
panjang = len(allfile)
if panjang == 3:
##do something
f = StringIO()
wb = Workbook()
ws = wb.active
for row in facebookdata:
f.write(row.name + ',' + row.status + ',' + str(row.like) + ',' +
str(row.comment) + ',' + str(row.share))
f.write('\n')
ws.append([row.name, row.status, row.like, row.comment, row.share])
f.flush()
f.seek(0)
##make json
g = StringIO()
data = []
for row in facebookdata:
data1 = {}
data1["NAME"] = row.name
data1["STATUS"] = row.status
data1["LIKE"] = row.like
data1["COMMENT"] = row.comment
data1["SHARE"] = row.share
data.append(data1)
json.dump(data, g, indent=3)
g.flush()
g.seek(0)
output = BytesIO()
zip = ZipFile(output, 'w')
zip.writestr("Data_facebook.csv", f.getvalue())
zip.writestr("Data_facebook.JSON", g.getvalue())
zip.writestr("Data_facebook.xlsx", save_virtual_workbook(wb))
zip.close()
response = HttpResponse(output.getvalue(),
content_type='application/octet-stream')
response[
'Content-Disposition'] = 'attachment; filename=Data_facebook.zip'
return response
elif panjang == 2:
##read list
if '0' and '1' in allfile:
##return csv and json
print('csv and jsn')
#make csv
f = StringIO()
for row in facebookdata:
f.write(row.name + ',' + row.status + ',' + str(row.like) +
',' + str(row.comment) + ',' + str(row.share))
f.write('\n')
f.flush()
f.seek(0)
##make json
g = StringIO()
data = []
for row in facebookdata:
data1 = {}
data1["NAME"] = row.name
data1["STATUS"] = row.status
data1["LIKE"] = row.like
data1["COMMENT"] = row.comment
data1["SHARE"] = row.share
data.append(data1)
json.dump(data, g, indent=3)
g.flush()
g.seek(0)
output = BytesIO()
zip = ZipFile(output, 'w')
zip.writestr("Data_facebook.csv", f.getvalue())
zip.writestr("Data_facebook.JSON", g.getvalue())
zip.close()
response = HttpResponse(output.getvalue(),
content_type='application/octet-stream')
response[
'Content-Disposition'] = 'attachment; filename=Data_facebook.zip'
return response
elif '0' and '2' in allfile:
#return csv and xls
print('csv and xls')
f = StringIO()
for row in facebookdata:
f.write(row.name + ',' + row.status + ',' + str(row.like) +
',' + str(row.comment) + ',' + str(row.share))
f.write('\n')
f.flush()
f.seek(0)
g = StringIO()
wb = Workbook()
ws = wb.active
for row in facebookdata:
ws.append(
[row.name, row.status, row.like, row.comment, row.share])
output = BytesIO()
zip = ZipFile(output, 'w')
zip.writestr("Data_facebook.csv", f.getvalue())
zip.writestr("Data_facebook.xlsx", save_virtual_workbook(wb))
zip.close()
response = HttpResponse(output.getvalue(),
content_type='application/octet-stream')
response[
'Content-Disposition'] = 'attachment; filename=Data_facebook.zip'
return response
else:
#return json xls
#make json file
f = StringIO()
data = []
for row in facebookdata:
data1 = {}
data1["NAME"] = row.name
data1["TWEET"] = row.tweet
data1["RETWEET_USER"] = row.Retweet_user
data1["HASHTAGS"] = row.hashtag
data1["DATE"] = str(row.date)
data.append(data1)
json.dump(data, f, indent=3)
f.flush()
f.seek(0)
#make xls file
g = StringIO()
wb = Workbook()
ws = wb.active
for row in facebookdata:
ws.append(
[row.name, row.status, row.like, row.comment, row.share])
output = BytesIO()
zip = ZipFile(output, 'w')
zip.writestr("Data_facebook.csv", f.getvalue())
zip.writestr("Data_facebook.xlsx", save_virtual_workbook(wb))
zip.close()
response = HttpResponse(output.getvalue(),
content_type='application/octet-stream')
response[
'Content-Disposition'] = 'attachment; filename=Data_facebook.zip'
return response
elif panjang == 0:
##asli
print('do nothing')
return HttpResponseRedirect('../search2')
else:
if '0' in allfile:
f = StringIO()
writer = csv.writer(f)
for row in facebookdata:
writer.writerow(
[row.name, row.status, row.like, row.comment, row.share])
f.flush()
f.seek(0)
response = HttpResponse(FileWrapper(f), content_type='text/csv')
response[
'Content-Disposition'] = 'attachment; filename=Data_facebook.csv'
return response
elif '1' in allfile:
f = StringIO()
data = []
for row in facebookdata:
data1 = {}
data1["NAME"] = row.name
data1["STATUS"] = row.status
data1["LIKE"] = row.like
data1["COMMENT"] = row.comment
data1["SHARE"] = row.share
data.append(data1)
json.dump(data, f, indent=3)
f.flush()
f.seek(0)
response = HttpResponse(FileWrapper(f), content_type='js/json')
response[
'Content-Disposition'] = 'attachment; filename=Data_facebook.json'
return response
else:
f = StringIO()
wb = Workbook()
ws = wb.active
for row in facebookdata:
ws.append(
[row.name, row.status, row.like, row.comment, row.share])
response = HttpResponse(save_virtual_workbook(wb),
content_type='application/vnd.ms-excel')
response[
'Content-Disposition'] = 'attachment; filename=Data_facebook.xlsx'
return response
def update(self, request, *args, **kwargs): # pragma: no cover
serializer = RepositoryTranslatedExporterSerializer(data=request.data)
serializer.is_valid(raise_exception=True)
for_language = serializer.data.get("language", None)
workbook = openpyxl.load_workbook(filename=request.data.get("file"))
worksheet = workbook.get_sheet_by_name("Translate")
columns = ["ID", "Repository Version", "Language"]
examples_success = []
find = False
for count, row in enumerate(worksheet.iter_rows(), start=1):
if (row[1].value in columns and row[2].value in columns
and row[3].value in columns):
find = True
continue
if find:
example_id = int(row[1].value)
repository_version = int(row[2].value)
text_translated = row[5].value
if not int(kwargs.get("pk")) == repository_version:
raise APIException( # pragma: no cover
{
"detail": "Import version is different from the selected version"
},
code=400,
)
if text_translated:
example = RepositoryExample.objects.filter(
pk=example_id,
repository_version_language__repository_version=
repository_version,
repository_version_language__repository_version__repository
=kwargs.get("repository__uuid"),
)
if example.count() == 0:
worksheet.cell(row=count,
column=7,
value="Sentence does not exist")
continue
example = example.first()
entity_validation = False
for entity in utils.find_entities_in_example(
text_translated):
original_text_count_entity = RepositoryExampleEntity.objects.filter(
repository_example=example,
entity__repository_version=kwargs.get("pk"),
entity__value=entity.get("entity"),
).count()
if original_text_count_entity == 0:
entity_validation = True
worksheet.cell(row=count,
column=7,
value="Entities must match")
break
if entity_validation:
continue
translated_examples = RepositoryTranslatedExample.objects.filter(
original_example=example, language=for_language)
if translated_examples.count() > 0:
translated_examples.delete()
translated = RepositoryTranslatedExample.objects.create(
repository_version_language=example.
repository_version_language,
original_example=example,
language=for_language,
text=utils.get_without_entity(text_translated),
clone_repository=True,
)
for translated_entity in utils.find_entities_in_example(
text_translated):
RepositoryTranslatedExampleEntity.objects.create(
repository_translated_example=translated,
start=translated_entity["start"],
end=translated_entity["end"],
entity=translated_entity["entity"],
)
examples_success.append(count)
for r in reversed(examples_success):
worksheet.delete_rows(r)
response = HttpResponse(
content=save_virtual_workbook(workbook),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
)
response["Content-Disposition"] = "attachment; filename=bothub.xlsx"
return response
def test_write_virtual_workbook():
old_wb = Workbook()
saved_wb = save_virtual_workbook(old_wb)
new_wb = load_workbook(BytesIO(saved_wb))
assert new_wb
def call(request):
token_key = request._get_get().get("tk")
token_data = manager.get_parameter(request, token_key)
if token_data.get('data', None) is None:
return HttpResponse(content="<h3>" + token_data["error"] + "</h3>",
mimetype='text/html')
collection_name = token_data["data"]["collection_name"]
wb = Workbook()
ws = wb.active
#ws["A1"].value = "TEST"
ws.title = KEY.WS_PREFIX_NAME + "1"
#add sheet mapping
wb.create_sheet(KEY.WS_MAPPING_SHEET)
ws_mapping = wb[KEY.WS_MAPPING_SHEET]
#add sheet init data
wb.create_sheet(KEY.WS_INIT_DATA)
ws_init = wb[KEY.WS_INIT_DATA]
#Get column config
ret_columns = db.get_collection("HCSSYS_CollectionInfo").aggregate([{
'$match': {
'$and': [
{
'parent_field': None
},
{
'field_path': {
'$regex': '^,' + collection_name + ','
}
},
{
'is_parent': False
},
]
}
}, {
'$project': {
'_id': 0
}
}])
columns = list(ret_columns)
#Get export data
ret_data = db.get_collection(collection_name).find({})
data_items = list(ret_data)
if (len(data_items) == 0):
raise "Collection '" + collection_name + "' has not been declared in the database"
return null
ref_accessmode = [{
'value': 1,
'caption': u'Toàn quyền'
}, {
'value': 2,
'caption': u'Từ vùng được chọn đến các cấp con'
}, {
'value': 3,
'caption': u'Chỉ trên vùng được chọn'
}]
#create reference sheet
ref_columns = [a for a in columns if a["field_name"] == "access_mode"]
for col in ref_columns:
wb.create_sheet(KEY.WS_PREFIX_REFERENCE + col["field_name"])
ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]]
for iData, vData in enumerate(ref_accessmode):
ws_ref.cell(row=iData + 1, column=1).value = vData["value"]
ws_ref.cell(row=iData + 1, column=2).value = vData["caption"]
ws_ref.cell(row=iData + 1, column=3).value = vData["value"]
#create header title
header_fields = []
for c in columns:
if (c["field_name"] == 'access_mode'):
header_fields.append({
'field_name': c["field_name"],
'key': KEY.PREFIX_INIT,
'display_name': c["description"],
'is_ref': True,
'is_data_field': False,
'is_hidden': True
})
header_fields.append({
'field_name': c["field_name"],
'key': KEY.PREFIX_REF,
'display_name': c["description"],
'is_ref': True, #List of reference data
'is_data_field': False,
'is_hidden': False
})
header_fields.append({
'field_name': c["field_name"],
'key': c["field_name"],
'display_name': c["description"],
'is_ref': True,
'is_data_field': True, #data_field in database
'is_hidden': True
})
else:
header_fields.append({
'field_name': c["field_name"],
'key': c["field_name"],
'display_name': c["description"],
'is_ref': False,
'is_data_field': True,
'is_hidden': False
})
#Create header worksheet (row 1)
idx_mapping_row_column = 1
ws_mapping.cell(row=1, column=1).value = KEY.BEGIN_MAPPING
ws_mapping.cell(row=1, column=2).value = collection_name
ws_mapping.cell(row=1, column=3).value = ws.title
idx_mapping_row_column += 1
idx_init_column = 0
for iCol, vCol in enumerate(header_fields):
if not (vCol["key"] == KEY.PREFIX_INIT):
cell = ws.cell(row=1, column=iCol + 1 - idx_init_column)
cell.value = vCol["display_name"]
ws.column_dimensions[cell.column].width = len(
vCol["display_name"]) if len(
vCol["display_name"]) > 20 else 20 #20 characters
if (vCol["is_hidden"]):
ws.column_dimensions[cell.column].hidden = True
if (vCol["is_data_field"] == True):
#create mapping data
ws_mapping.cell(row=idx_mapping_row_column,
column=1).value = cell.column
ws_mapping.cell(row=idx_mapping_row_column,
column=2).value = vCol["field_name"]
idx_mapping_row_column += 1
else:
cell = ws_init.cell(row=1, column=idx_init_column + 1)
cell.value = vCol["field_name"]
idx_init_column += 1
ws_mapping.cell(row=idx_mapping_row_column,
column=1).value = KEY.END_MAPPING
#Render content to worksheet
#if (len(data_items) > 0):
for iItem, vItem in enumerate(data_items):
num_init_column = 0
for iCol, vCol in enumerate(header_fields):
idx_col = iCol - num_init_column
curr_cell = ws.cell(row=iItem + 2, column=idx_col + 1)
if (vCol["is_data_field"] and not vCol["is_ref"]):
curr_cell.value = vItem[vCol["field_name"]]
elif (vCol["is_ref"] and vCol["key"] == KEY.PREFIX_INIT):
init_cell = ws_init.cell(row=iItem + 2,
column=num_init_column + 1)
init_cell.value = vItem[vCol["field_name"]]
num_init_column += 1
elif (vCol["is_ref"] and vCol["key"] == KEY.PREFIX_REF):
#curr_cell: value list
#vlookup column 1-2 in reference data
ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]]
#ref_beg_cell = ws_ref.cell(row=1, column=1)
ref_end_cell = ws_ref.cell(row=ws_ref.max_row, column=2)
ref_address = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \
'$A$1:$B$' + str(ws_ref.max_row)
# "$" + ref_beg_cell.column + "$" + str(ref_beg_cell.col_idx) + ":" + \
# "$" + ref_end_cell.column + "$" + str(ref_end_cell.col_idx)
init_cell = ws_init.cell(row=iItem + 2, column=num_init_column)
curr_cell.value = "=VLOOKUP(" + KEY.WS_INIT_DATA + "!" + init_cell.coordinate + "," + ref_address + ",2, FALSE)"
ref_address_title = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \
"$B$1:" + "$B$" + str(ws_ref.max_row)
# Create a data-validation object with list validation
dv = DataValidation(type="list",
formula1="=" + ref_address_title,
allow_blank=True)
# Optionally set a custom error message
dv.error = 'Your entry is not in the list'
dv.errorTitle = 'Invalid Entry'
# Optionally set a custom prompt message
dv.prompt = 'Please select from the list'
dv.promptTitle = 'List Selection'
ws.add_data_validation(dv)
dv.add(curr_cell)
elif (vCol["is_data_field"] and vCol["is_ref"]):
pre_cell = ws.cell(row=iItem + 2, column=idx_col)
#vlookup column 2-3 in reference data
ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]]
#ref_beg_cell = ws_ref.cell(row=1, column=2)
ref_end_cell = ws_ref.cell(row=ws_ref.max_row,
column=ws_ref.max_column)
ref_address = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \
'$B$1:$C$' + str(ws_ref.max_row)
#"$" + ref_beg_cell.column + "$" + str(ref_beg_cell.col_idx) + ":" + \
#"$" + ref_end_cell.column + "$" + str(ref_end_cell.col_idx)
curr_cell.value = "=VLOOKUP(" + pre_cell.coordinate + "," + ref_address + ",2, FALSE)"
#format worksheet
ws = format_style(worksheet_style["NORMAL"]).format(ws)
#wb.defined_names.append(defined_name.DefinedName(attr_text="HCSSYS_DataDomain!A$1", name="TEST_NAME_0000000"))
response = HttpResponse(
content=save_virtual_workbook(wb),
mimetype=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
time_export = datetime.datetime.now().strftime("%Y.%m.%dT%H.%M.%S")
response[
'Content-Disposition'] = 'attachment; filename=' + 'export-' + time_export + '.xlsx'
return response
def test_change_existing_styles(datadir):
wb = load_workbook("complex-styles.xlsx")
ws = wb.get_active_sheet()
ws.column_dimensions['A'].width = 20
i_style = ws.get_style('I')
ws.set_style('I', i_style.copy(fill=PatternFill(fill_type='solid',
start_color=Color('FF442200')),
font=Font(color=Color('FF002244'))))
ws.cell('A2').style = ws.cell('A2').style.copy(font=Font(name='Times New Roman',
size=12,
bold=True,
italic=True))
ws.cell('A3').style = ws.cell('A3').style.copy(font=Font(name='Times New Roman',
size=14,
bold=False,
italic=True))
ws.cell('A4').style = ws.cell('A4').style.copy(font=Font(name='Times New Roman',
size=16,
bold=True,
italic=False))
ws.cell('A5').style = ws.cell('A5').style.copy(font=Font(color=Color('FF66FF66')))
ws.cell('A6').style = ws.cell('A6').style.copy(font=Font(color=Color(theme='1')))
ws.cell('A7').style = ws.cell('A7').style.copy(fill=PatternFill(fill_type='solid',
start_color=Color('FF330066')))
ws.cell('A8').style = ws.cell('A8').style.copy(fill=PatternFill(fill_type='solid',
start_color=Color(theme='2')))
ws.cell('A9').style = ws.cell('A9').style.copy(alignment=Alignment(horizontal='center'))
ws.cell('A10').style = ws.cell('A10').style.copy(alignment=Alignment(horizontal='left'))
ws.cell('A11').style = ws.cell('A11').style.copy(alignment=Alignment(horizontal='right'))
ws.cell('A12').style = ws.cell('A12').style.copy(alignment=Alignment(vertical='bottom'))
ws.cell('A13').style = ws.cell('A13').style.copy(alignment=Alignment(vertical='top'))
ws.cell('A14').style = ws.cell('A14').style.copy(alignment=Alignment(vertical='center'))
ws.cell('A15').style = ws.cell('A15').style.copy(number_format=NumberFormat('0.00%'))
ws.cell('A16').style = ws.cell('A16').style.copy(number_format=NumberFormat('0.00'))
ws.cell('A17').style = ws.cell('A17').style.copy(number_format=NumberFormat('mm-dd-yy'))
ws.unmerge_cells('A18:B18')
ws.cell('A19').style = ws.cell('A19').style.copy(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color('FF006600')),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color('FF006600')),
left=Side(border_style=borders.BORDER_THIN,
color=Color('FF006600')),
right=Side(border_style=borders.BORDER_THIN,
color=Color('FF006600'))))
ws.cell('A21').style = ws.cell('A21').style.copy(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(theme=7)),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color(theme=7)),
left=Side(border_style=borders.BORDER_THIN,
color=Color(theme=7)),
right=Side(border_style=borders.BORDER_THIN,
color=Color(theme=7))))
ws.cell('A23').style = ws.cell('A23').style.copy(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(theme=6))),
fill=PatternFill(fill_type='solid',
start_color=Color('FFCCCCFF')))
ws.unmerge_cells('A23:B24')
ws.cell('A25').style = ws.cell('A25').style.copy(alignment=Alignment(wrap_text=False))
ws.cell('A26').style = ws.cell('A26').style.copy(alignment=Alignment(shrink_to_fit=False))
saved_wb = save_virtual_workbook(wb)
new_wb = load_workbook(BytesIO(saved_wb))
ws = new_wb.get_active_sheet()
assert ws.column_dimensions['A'].width == 20.0
style = partial(ws.get_style)
assert ws.get_style('I').fill.start_color.value == 'FF442200'
assert ws.get_style('I').font.color.value == 'FF002244'
assert style('A2').font.name == 'Times New Roman'
assert style('A2').font.size == 12
assert style('A2').font.bold
assert style('A2').font.italic
assert style('A3').font.name == 'Times New Roman'
assert style('A3').font.size == 14
assert not style('A3').font.bold
assert style('A3').font.italic
assert style('A4').font.name == 'Times New Roman'
assert style('A4').font.size == 16
assert style('A4').font.bold
assert not style('A4').font.italic
assert style('A5').font.color.value == 'FF66FF66'
assert style('A6').font.color.value == 1
assert style('A7').fill.start_color.value == 'FF330066'
assert style('A8').fill.start_color.value == 2
assert style('A9').alignment.horizontal == 'center'
assert style('A10').alignment.horizontal == 'left'
assert style('A11').alignment.horizontal == 'right'
assert style('A12').alignment.vertical == 'bottom'
assert style('A13').alignment.vertical == 'top'
assert style('A14').alignment.vertical == 'center'
assert style('A15').number_format == '0.00%'
assert style('A16').number_format == '0.00'
assert style('A17').number_format == 'mm-dd-yy'
assert 'A18:B18' not in ws._merged_cells
assert not ws.cell('B18').merged
assert style('A19').border.top.color.value == 'FF006600'
assert style('A19').border.bottom.color.value == 'FF006600'
assert style('A19').border.left.color.value == 'FF006600'
assert style('A19').border.right.color.value == 'FF006600'
assert style('A21').border.top.color.value == 7
assert style('A21').border.bottom.color.value == 7
assert style('A21').border.left.color.value == 7
assert style('A21').border.right.color.value == 7
assert style('A23').fill.start_color.value == 'FFCCCCFF'
assert style('A23').border.top.color.value == 6
assert 'A23:B24' not in ws._merged_cells
assert not ws.cell('A24').merged
assert not ws.cell('B23').merged
assert not ws.cell('B24').merged
assert not style('A25').alignment.wrap_text
assert not style('A26').alignment.shrink_to_fit
# Verify that previously duplicate styles remain the same
assert ws.column_dimensions['C'].width == 31.1640625
assert style('C2').font.name == 'Arial'
assert style('C2').font.size == 10
assert not style('C2').font.bold
assert not style('C2').font.italic
assert style('C3').font.name == 'Arial'
assert style('C3').font.size == 12
assert style('C3').font.bold
assert not style('C3').font.italic
assert style('C4').font.name == 'Arial'
assert style('C4').font.size == 14
assert not style('C4').font.bold
assert style('C4').font.italic
assert style('C5').font.color.value == 'FF3300FF'
assert style('C6').font.color.value == 9
assert style('C7').fill.start_color.value == 'FFFFFF66'
assert style('C8').fill.start_color.value == 8
assert style('C9').alignment.horizontal == 'left'
assert style('C10').alignment.horizontal == 'right'
assert style('C11').alignment.horizontal == 'center'
assert style('C12').alignment.vertical == 'top'
assert style('C13').alignment.vertical == 'center'
assert style('C14').alignment.vertical == 'bottom'
assert style('C15').number_format == '0.00'
assert style('C16').number_format == 'mm-dd-yy'
assert style('C17').number_format == '0.00%'
assert 'C18:D18' in ws._merged_cells
assert ws.cell('D18').merged
assert style('C19').border.top.color.value == 'FF006600'
assert style('C19').border.bottom.color.value == 'FF006600'
assert style('C19').border.left.color.value == 'FF006600'
assert style('C19').border.right.color.value == 'FF006600'
assert style('C21').border.top.color.value == 7
assert style('C21').border.bottom.color.value == 7
assert style('C21').border.left.color.value == 7
assert style('C21').border.right.color.value == 7
assert style('C23').fill.start_color.value == 'FFCCCCFF'
assert style('C23').border.top.color.value == 6
assert 'C23:D24' in ws._merged_cells
assert ws.cell('C24').merged
assert ws.cell('D23').merged
assert ws.cell('D24').merged
assert style('C25').alignment.wrap_text
assert style('C26').alignment.shrink_to_fit
def createResponse(self):
response = Response(content_type='application/vnd.ms-excel')
response.body = save_virtual_workbook(self.w)
return response
def _get_response(self, workbook):
response = HttpResponse(content=save_virtual_workbook(workbook),
content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = f'attachment; filename={self.filename}'
return response
