This is my code but my code is not working on my button to export XLSX data from Odoo
# coding: utf-8
from odoo import fields, models, api, _
import time
from datetime import datetime, date, timedelta
from odoo.tools import DEFAULT_SERVER_DATE_FORMAT as DATE_FORMAT, DEFAULT_SERVER_DATETIME_FORMAT as DATETIME_FORMAT
import xlsxwriter
from io import BytesIO
class AccountDailyBook(models.TransientModel):
_name = "account.daily.book"
date_start = fields.Date("Fecha desde", required=True, default=date.today().replace(day=1).strftime('%Y-%m-%d'))
date_end = fields.Date("Fecha hasta", required=True, default=time.strftime('%Y-%m-%d'))
company_id = fields.Many2one('res.company', required=True, default=lambda self: self.env.company)
def imprimir_pdf(self):
for rec in self:
format_new = "%d/%m/%Y"
data = {
'ids': 0,
'form': {
'date_from': self.date_start,
'date_to': self.date_end,
'date_from_str': self.date_start.strftime(format_new),
'date_to_str': self.date_end.strftime(format_new),
'company': self.company_id.id
}
}
return self.env.ref('l10n_ve_full.report_daily_book').report_action(self, data=data) # , config=False
def imprimir_xlsx(self):
for rec in self:
format_new = "%d/%m/%Y"
data = {
'ids': 0,
'form': {
'date_from': self.date_start,
'date_to': self.date_end,
'date_from_str': self.date_start.strftime(format_new),
'date_to_str': self.date_end.strftime(format_new),
'company': self.company_id.id
}
}
# Crear el archivo
output = BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet('Reporte')
# Escribir los encabezados
sheet.write(0, 0, 'Código')
sheet.write(0, 1, 'Nombre')
sheet.write(0, 2, 'Débito')
sheet.write(0, 3, 'Crédito')
# Escribir los datos
row = 1
for doc in data['docs']:
sheet.write(row, 0, doc['code'])
sheet.write(row, 1, doc['name'])
sheet.write(row, 2, doc['debit'])
sheet.write(row, 3, doc['credit'])
row += 1
# Escribir los totales
sheet.write(row, 1, 'Total')
sheet.write(row, 2, data['debit_total'])
sheet.write(row, 3, data['credit_total'])
# Cerrar el archivo
workbook.close()
output.seek(0)
return self.env.ref('l10n_ve_full.report_daily_book').report_action(self, data=data)
# Devolver el archivo como una respuesta HTTP
return {
'name': 'report_daily_book.xlsx',
'type': 'binary',
'data': output.read(),
'context': self.env.context,
}
class AccountInventoryBookReport(models.AbstractModel):
_name = 'report.l10n_ve_full.report_daily_book_template'
@api.model
def _get_report_values(self, docids, data=None):
date_start = datetime.strptime(data['form']['date_from'], DATE_FORMAT)
date_end = datetime.strptime(data['form']['date_to'], DATE_FORMAT)
company_id = self.env['res.company'].search([('id','=',data['form']['company'])])
move_line_ids = self.env['account.move.line'].search([])
datos = []
debit_total = 0
credit_total = 0
self._cr.execute("select aa.code, aa.name, sum(aml.debit) as debit, sum(aml.credit) as credit "
"from account_move_line as aml "
"left join account_account as aa on aml.account_id = aa.id "
"where date >= '%s' and date <= '%s' "
"group by aa.code, aa.name "
"order by aa.code " % (date_start,date_end))
for r in self.env.cr.fetchall():
datos.append({
'code': r[0],
'name': r[1],
'debit': r[2],
'credit': r[3]
})
debit_total += r[2]
credit_total += r[3]
return {
'company_id': company_id,
'company': company_id,
'currency': company_id.currency_id,
'date_start': data['form']['date_from_str'],
'date_end': data['form']['date_to_str'],
'fecha_actual': datetime.now().strftime("%d/%m/%Y"),
'hora_actual': datetime.now().strftime("%H:%M:%S"),
'doc': move_line_ids[0],
'docs': datos,
'debit_total': debit_total,
'credit_total': credit_total,
}
I tried with this code
def imprimir_xlsx(self):
for rec in self:
format_new = "%d/%m/%Y"
data = {
'ids': 0,
'form': {
'date_from': self.date_start,
'date_to': self.date_end,
'date_from_str': self.date_start.strftime(format_new),
'date_to_str': self.date_end.strftime(format_new),
'company': self.company_id.id
}
}
# Crear el archivo
output = BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet('Reporte')
# Escribir los encabezados
sheet.write(0, 0, 'Código')
sheet.write(0, 1, 'Nombre')
sheet.write(0, 2, 'Débito')
sheet.write(0, 3, 'Crédito')
# Escribir los datos
row = 1
for doc in data['docs']:
sheet.write(row, 0, doc['code'])
sheet.write(row, 1, doc['name'])
sheet.write(row, 2, doc['debit'])
sheet.write(row, 3, doc['credit'])
row += 1
# Escribir los totales
sheet.write(row, 1, 'Total')
sheet.write(row, 2, data['debit_total'])
sheet.write(row, 3, data['credit_total'])
# Cerrar el archivo
workbook.close()
output.seek(0)
return self.env.ref('l10n_ve_full.report_daily_book').report_action(self, data=data)
# Devolver el archivo como una respuesta HTTP
return {
'name': 'report_daily_book.xlsx',
'type': 'binary',
'data': output.read(),
'context': self.env.context,
}
You can use the report_xlsx module. You will need to pass the result of the query defined inside the qweb report parser to the XLSX report.
Example:
Define a report action
<record id="report_daily_book_xlsx" model="ir.actions.report">
<field name="name">Print to XLSX</field>
<field name="model">account.daily.book</field>
<field name="report_type">xlsx</field>
<field name="report_name">report_daily_book</field>
<field name="report_file">report_daily_book</field>
</record>
Add a report parser (the update_datas function is used as an example so you can test this report parser)
class ReportDailyBook(models.AbstractModel):
_name = "report.report_daily_book"
_inherit = "report.report_xlsx.abstract"
_description = "Daily Books XLSX Report"
def generate_xlsx_report(self, workbook, data, books):
self.update_datas(data)
sheet = workbook.add_worksheet("Reporte")
# Escribir los encabezados
sheet.write(0, 0, 'Código')
sheet.write(0, 1, 'Nombre')
sheet.write(0, 2, 'Débito')
sheet.write(0, 3, 'Crédito')
# Escribir los datos
row = 1
for doc in data['docs']:
sheet.write(row, 0, doc['code'])
sheet.write(row, 1, doc['name'])
sheet.write(row, 2, doc['debit'])
sheet.write(row, 3, doc['credit'])
row += 1
# Escribir los totales
sheet.write(row, 1, 'Total')
sheet.write(row, 2, data['debit_total'])
sheet.write(row, 3, data['credit_total'])
def update_datas(self, data):
date_start = datetime.strptime(data['form']['date_from'], DATE_FORMAT)
date_end = datetime.strptime(data['form']['date_to'], DATE_FORMAT)
company_id = self.env['res.company'].search([('id', '=', data['form']['company'])])
move_line_ids = self.env['account.move.line'].search([])
datos = []
debit_total = 0
credit_total = 0
self._cr.execute("select aa.code, aa.name, sum(aml.debit) as debit, sum(aml.credit) as credit "
"from account_move_line as aml "
"left join account_account as aa on aml.account_id = aa.id "
"where date >= '%s' and date <= '%s' "
"group by aa.code, aa.name "
"order by aa.code " % (date_start, date_end))
for r in self.env.cr.fetchall():
datos.append({
'code': r[0],
'name': r[1],
'debit': r[2],
'credit': r[3]
})
debit_total += r[2]
credit_total += r[3]
data['docs'] = datos
data['debit_total'] = debit_total
data['credit_total'] = credit_total
Return the report action with predefined data in the imprimir_xlsx function
class AccountDailyBook(models.TransientModel):
_name = "account.daily.book"
date_start = fields.Date("Fecha desde", required=True, default=date.today().replace(day=1).strftime('%Y-%m-%d'))
date_end = fields.Date("Fecha hasta", required=True, default=time.strftime('%Y-%m-%d'))
company_id = fields.Many2one('res.company', required=True, default=lambda self: self.env.company)
def imprimir_xlsx(self):
self.ensure_one()
format_new = "%d/%m/%Y"
data = {
'ids': 0,
'form': {
'date_from': self.date_start,
'date_to': self.date_end,
'date_from_str': self.date_start.strftime(format_new),
'date_to_str': self.date_end.strftime(format_new),
'company': self.company_id.id
}
}
return self.env.ref('l10n_ve_full.report_daily_book_xlsx').report_action(self, data=data)