def xexport_dynamic_data(dict_data):
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
organization = Organization.get(id=1)
file_name = "{}.xlsx".format(dict_data.get("file_name"))
headers = dict_data.get("headers")
sheet_name = str(dict_data.get("sheet"))
title = str(dict_data.get("title"))
data = dict_data.get("data")
widths = dict_data.get("widths")
date_ = str(dict_data.get("date"))
extend_rows = dict_data.get("extend_rows")
others = dict_data.get("others")
footers = dict_data.get("footers")
exclude_row = dict_data.get("exclude_row")
format_money = dict_data.get("format_money")
# add column headings. NB. these must be strings
ws.append(headers)
for row in data:
print(row)
ws.append(row)
dict_alph = {
1: "A",
2: "C",
3: "D",
4: "E",
5: "F",
6: "G",
7: "H",
8: "I",
}
REF = "A1:{}{}".format(dict_alph.get(len(headers)), len(data) + 1)
print(REF)
tab = Table(displayName="Table1", ref=REF)
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save(file_name)
try:
wb.close()
openFile(file_name)
except Exception as e:
print(e)
def write_order_xls(file_name, order):
com_date, order = order
book = xlwt.Workbook(encoding='ascii')
sheet = book.add_sheet(u"%s COMMANDE" % Config.NAME_ORGA)
sheet.col(0).width = 0x0d00 * 2
sheet.col(1).width = 0x0d00 * 4
sheet.col(2).width = 0x0d00 * 2
rowx = 0
sheet.write_merge(rowx, rowx + 1, 0, 2,
u"%s COMMANDE" % Config.NAME_ORGA, style_title)
rowx += 2
sheet.write_merge(rowx, rowx, 0, 2, u"DRAMANE KOUREKAMA ET FILS", style_)
rowx += 1
sheet.write_merge(rowx, rowx, 0, 2,
u"B.P.: 177–Tél. Bout. N°: 20229776/76429471/76422142",
style_)
rowx += 1
sheet.write_merge(rowx, rowx, 0, 2,
u"E-mail: [email protected]–Bamako-Rép. Du Mali",
style_)
rowx += 2
date_com = u"Bko le %s" % com_date.strftime("%d/%m/%Y")
sheet.write_merge(rowx, rowx, 2, 2, date_com, style_)
title = [u"QUANTITE", u"DESCRIPTION"]
rowx += 2
colx = 0
sheet.write(rowx, colx, title[0], style_t_table)
colx += 1
sheet.write_merge(rowx, rowx, colx, colx + 1, title[1], style_t_table)
rowx += 1
for prod in order:
col = 0
sheet.write_merge(rowx, rowx, col, col, prod[0], int_style)
col += 1
sheet.write_merge(rowx, rowx, col, col + 1, prod[1], style)
rowx += 1
book.save(file_name)
openFile(file_name)
def write_report_xls(file_name, data):
''' Export data '''
# Principe
# write((nbre ligne - 1), nbre colonne, "contenu", style(optionnel).
# write_merge((nbre ligne - 1), (nbre ligne - 1) + nbre de ligne
# à merger, (nbre de colonne - 1), (nbre de colonne - 1) + nbre
# de colonne à merger, u"contenu", style(optionnel)).
book = xlwt.Workbook(encoding='ascii')
sheet = book.add_sheet(u"Rapports")
rowx = 0
sheet.write_merge(rowx, rowx + 1, 0, 3,
u"Rapports de gestion de stock %s" % Config.NAME_ORGA, style_title)
rowx += 3
sheet.write_merge(rowx, rowx, 1, 2, u"Date du rapport: ", style)
date_com = "Bko le %s" % date.today().strftime(u"%d/%m/%Y")
sheet.write_merge(rowx, rowx, 3, 3, date_com)
sheet.col(1).width = 0x0d00 * 3
sheet.col(2).width = 0x0d00 * 1.5
sheet.col(4).width = 0x0d00 * 2
# title = [u"Type", u"Produit", u"Nbre Carton", u"Restant", u"Date"]
for rap in Reports.all():
if int(rowx) % 2 == 0:
style_row_table = style1
else:
style_row_table = style2
sheet.write(rowx, 0, rap.type_, style_row_table)
sheet.write(rowx, 1, "%s (%s)" % (rap.product.name,
rap.product.code_prod), style_row_table)
sheet.write(rowx, 2, rap.nbr_carton, style_row_table)
sheet.write(rowx, 3, rap.remaining, style_row_table)
sheet.write(
rowx, 4, rap.date.strftime(u'%x %Hh:%Mmn'), style_row_table)
rowx += 1
book.save(file_name)
openFile(file_name)
def write_invoice_xls(file_name, invoice):
book = xlwt.Workbook(encoding='ascii')
sheet = book.add_sheet(u"Facture %s" % Config.NAME_ORGA)
sheet.col(1).width = 0x0d00 * 3
sheet.col(4).width = 0x0d00 * 2
rowx = 0
sheet.write_merge(rowx, rowx + 1, 0, 3,
u"Facture de %s" % Config.NAME_ORGA, style_title)
rowx += 3
date = "Bko le %s" % invoice.date.strftime("%d/%m/%Y")
sheet.write_merge(rowx, rowx, 2, 3, date)
hheaders = [_(u"Quantité"), _(u"Désignation"), _(u"Prix Unitaire"),
_(u"Montant")]
rowx += 2
for colx, val_center in enumerate(hheaders):
sheet.write(rowx, colx, val_center, style_t_table)
rowx += 1
data = [(item.quantity, item.description.name, item.price,
item.quantity * item.price)
for item in InvoiceItem.filter(invoices=invoice)]
for prod in data:
col = 0
for val_center in prod:
if isinstance(val_center, str):
style_ = style
else:
style_ = int_style
sheet.write_merge(rowx, rowx, col, col, val_center, style_)
col += 1
rowx += 1
book.save(file_name)
openFile(file_name)
def export_dynamic_data(dict_data):
date = dict_data.get("date")
data = dict_data.get("data")
headers = dict_data.get("headers")
file_name = "{}.pdf".format(dict_data.get("file_name"))
title = str(dict_data.get("title"))
el = []
# htable = headers
hdata = [(title, "", "", ""), (date, "", "", "")]
htable = Table(hdata)
htable.hAlign = "LEFT"
ldata = []
ldata.append(headers)
# style_ = ParagraphStyle(
# name='Normal',
# )
for r in data:
ldata.append(r)
btable = Table(ldata)
# btable = Table(ldata, colWidths=[(inch) for i in range(1, len(ldata) + 1)])
# btable = Table(ldata)
btable.hAlign = "LEFT"
btable.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), HexColor("#C0C0C0")),
('GRID', (0, 1), (-1, -1), 0.01 * inch, (0, 0, 0,)),
('FONT', (0, 0), (-1, 0), 'Helvetica-Bold')]))
el.append(htable)
el.append(btable)
doc = SimpleDocTemplate(file_name, pagesize=A4)
doc.build(el)
openFile(file_name)
def export_dynamic_data(dict_data):
'''
- Export params
dict = {
'file_name': "prod",
'data' : [1, 3, ...],
'headers': ["ff", "kkk", "ooo"],
'sheet': "Les produits",
'extend_rows': [(row1, col1, val), (row2, col2, val), ]
'widths': [col, ..]
'date': object date
'format_money': ['D:D',]
}
- Principe
write((nbre ligne - 1), nbre colonne, "contenu", style(optionnel).
merge_range((nbre ligne - 1), (nbre ligne - 1) + nbre de ligne à merger, (nbre de colonne - 1), (nbre de colonne - 1) + nbre
de colonne à merger, u"contenu", style(optionnel)).
'''
organization = Organization.get(id=1)
file_name = "{}.xlsx".format(dict_data.get("file_name"))
headers = dict_data.get("headers")
sheet_name = str(dict_data.get("sheet"))
title = str(dict_data.get("title"))
data = dict_data.get("data")
widths = dict_data.get("widths")
date_ = str(dict_data.get("date"))
extend_rows = dict_data.get("extend_rows")
others = dict_data.get("others")
footers = dict_data.get("footers")
exclude_row = dict_data.get("exclude_row")
format_money = dict_data.get("format_money")
# print(data)
dict_alph = {
1: "A",
2: "C",
3: "D",
4: "E",
5: "F",
6: "G",
7: "H",
8: "I",
}
if date_ == "None":
date_ = datetime.now()
workbook = xlsxwriter.Workbook(
file_name, {'default_date_format': 'dd/mm/yy'})
worksheet = workbook.add_worksheet(sheet_name)
# worksheet.fit_num_pages = 1
# worksheet.set_h_pagebreaks([4])
date_format = workbook.add_format({'num_format': 'd-mmm-yy'})
format1 = workbook.add_format()
format1.set_num_format('0.000')
money = workbook.add_format({'num_format': '#,## '})
style_def = workbook.add_format({})
rowx = 1
end_colx = len(headers) - 1
if Config.ORG_LOGO:
worksheet.insert_image(
'A1:B2', os.path.join(Config.img_media, Config.ORG_LOGO),
{'x_offset': 1.5, 'y_offset': 0.5})
rowx += 6
else:
worksheet.merge_range('A{}:E{}'.format(
rowx, rowx), organization.name_orga, workbook.add_format(
style_org))
rowx += 1
worksheet.merge_range(
'A{}:E{}'.format(rowx, rowx), "Adresse : {}".format(
organization.adress_org), style_def)
rowx += 1
worksheet.merge_range('A{}:B{}'.format(
rowx, rowx), "BP : {}".format(organization.bp), style_def)
worksheet.merge_range(
'{}{}:{}{}'.format(dict_alph.get(end_colx - 1),
rowx, dict_alph.get(end_colx), rowx),
"E-mail : {}".format(organization.email_org), style_def)
rowx += 1
worksheet.merge_range(
'A{}:{}{}'.format(rowx, dict_alph.get(end_colx - 1), rowx),
"Tel : {}".format(organization.phone), style_def)
rowx += 2
for col in widths:
w = (120 / len(headers))
worksheet.set_column(col, col, w)
columns = [({'header': item}) for item in headers]
end_row_table = len(data) + rowx + 3
if format_money:
for col_str in format_money:
worksheet.set_column(col_str, 18, money)
rowx += 1
worksheet.merge_range("D{}:{}{}".format(
rowx, dict_alph.get(end_colx), rowx), date_, date_format)
rowx += 2
worksheet.add_table(
'A{}:{}{}'.format(rowx, dict_alph.get(end_colx), end_row_table),
{'autofilter': 0, 'data': data, 'columns': columns})
rowx = end_row_table
# rowx += 1
if extend_rows:
for elt in extend_rows:
col, val = elt
worksheet.write(rowx, col, val, money)
rowx += 1
if footers:
rowx += 1
for s_col, e_col, val in footers:
worksheet.merge_range('{}{}:{}{}'.format(s_col, rowx, e_col, rowx),
val, workbook.add_format(style_label))
rowx += 1
rowx += 1
if others:
for pos, pos2, val in others:
worksheet.merge_range(
'{}:{}'.format(pos, pos2), val, workbook.add_format(
style_label))
try:
workbook.close()
# workbook.save(file_name)
openFile(file_name)
except Exception as e:
print(e)
