No data content from script report in Excel

Hi all,

I have a strange issue with a script report: the report normally opens and shows data:

grafik

When using Menu > Export > CSV, it opens as well:

grafik

However, if I do the same with Menu > Export > Excel, the Excel sheet has no data content:

grafik

I am using ERPNext: v10.1.36 (master), Frappe Framework: v10.1.36 (master). The script report is based on a code like this:

from __future__ import unicode_literals
import frappe

def execute(filters=None):
    columns, data = [], []
    
    columns = ["Meeting::100", "Block::200", "Person:Link/Person:100", "Name::200", "Role::200", "Show on Website"]
    if filters:
        data = get_actors(meeting=filters.meeting)
    else:
        data = get_actors()
            
    return columns, data

def get_actors(meeting=None):
    sql_query = """SELECT 
                `t1`.`title` AS `Meeting`,
                `t2`.`title` AS `Block`,
                `t3`.`person` AS `Person`,
                `t3`.`person_name` AS `Name`,
                `t3`.`role` AS `Role`,
                `t4`.`show_on_website` AS `Show on Website`
            FROM `tabMeeting` AS `t1`
            INNER JOIN `tabBlock` AS `t2` ON `t1`.`title` = `t2`.`meeting` 
            INNER JOIN `tabBlock Actor` AS `t3` ON `t2`.`title` = `t3`.`parent`
            LEFT JOIN `tabPerson` AS `t4` ON `t3`.`person` = `t4`.`name`"""
    if meeting:
        sql_query += """ WHERE `t1`.`title` = '{0}'""".format(meeting)
    sql_query += """ ORDER BY `t1`.`title` ASC, `t2`.`title` ASC, `t3`.`idx` ASC"""
    data = frappe.db.sql(sql_query, as_dict = True)
return data

Any ideas why this fails with format xlsx? There are no entries in the error log/console… No filters are set.

Hello lasalesi
I have been experiencing the exact same behaviour.
Basically the report is fine as a csv but when exported as Excel only the headers appear.
Have you had any luck in finding the source of the problem?

Hi @MarZah,

actually I did not figure out the reason yet, but there is a workaround: if you run the query as_dict, the Excel export will fail. But it succeeds if you run it as_list… I.e. change

data = frappe.db.sql(sql_query, as_dict = True)

to

data = frappe.db.sql(sql_query, as_list = True)

and it will work :wink:

I have created a corresponding issue: No data content from script report in Excel · Issue #6032 · frappe/frappe · GitHub

Hello lasalesi

My list is created this way
data = []
and then appended to
data.append(…

Hello lasalesi

Your tip led me to the solution.
I was inadvertently appending a map and not a list to data
Basically data.append({“fielda” : x, …
Instead of data.append([x,y,…
I am surprised it worked at all.

Thank you very much for your help

You’re welcome :wink: Good to hear that the workaround worked…

HI, is there a step-by-step to reproduce this kind of report to a custom doc_type? I’m searching in this forum but the neart answer to create an excel report from erpNext was this topic (I’m a beginner)

@totalys I believe this will be helpful to you. Please check it out [Tutorial] Script Report / Chart - #6 by peterg

1 Like